• 全部
  • 问答

求助:一条SQL语句优化(mysql5.7)

bwangel 2020-05-28 05:54:25
SQL:
SELECT count(*) CUSTOMERNUM FROM
crt_customer_list_check r inner join
(
SELECT grno, max(submitid) maxsubmitid from
crt_customer_list_check c inner join dup_msubmit_main d on c.submitid=d.submit_id and d.submit_result=7
group by grno
)c
on r.GRNO=c.GRNO and r.SUBMITID=c.maxsubmitid;


就只查一个统计值。
现在的情况是,主表crt_customer_list_check的记录大概有100W+条,
dup_msubmit_main表记录只有2K+条。

数据量不算太大,但是上述查询不管怎么优化,都需2-3s。
首页这种查询有很多,每个类似的都有2-3秒,加起来首页就会很慢了。

求Mysql高手支招!!!分数我有。

crt_customer_list_check 表结构:
DROP TABLE IF EXISTS `crt_customer_list_check`;
CREATE TABLE `crt_customer_list_check` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`GRNO` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属集团编码',
`GRNAME` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属集团名称',
`CORPNO` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '公司编码',
`CORPNAME` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '公司名称',
`CUSTOMERNAME` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客户名称',
`BUSINESSNO` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '营业执照号码',
`COUNTRY` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客户国别',
`CORPTYPE` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客户类型',
`CUSTOMERTAG` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客户标签',
`HOUSEELEASEDAREA` decimal(20, 2) NULL DEFAULT NULL COMMENT '本公司内合计房产租赁面积(平方米)',
`HOUSELEASEPRICE` decimal(25, 2) NULL DEFAULT NULL COMMENT '本公司内房产平均租赁单价(元/平方米/天)',
`HOUSEEPORTRENTINCOME` decimal(25, 6) NULL DEFAULT NULL COMMENT '报告期年房产租金年收入(万元)',
`HOUSENOS` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '租赁房产编码',
`HOUSENAMES` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '租赁房产名称',
`BLOCKLEASEDAREA` decimal(20, 2) NULL DEFAULT NULL COMMENT '本公司内合计土地租赁面积(平方米)',
`BLOCKLEASEPRICE` decimal(10, 2) NULL DEFAULT NULL COMMENT '本公司内土地平均租赁单价(元/平方米/天)',
`BLOCKEPORTRENTINCOME` decimal(20, 5) NULL DEFAULT NULL COMMENT '报告期年土地租金年收入(万元)',
`BLOCKNOS` varchar(400) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '租赁地块编码 ',
`BLOCKNAMES` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '租赁地块名称',
`STATDATE` date NULL DEFAULT NULL COMMENT '统计截止日期',
`COMMENTS` varchar(1500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
`ResourceID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据来源',
`SUBMITID` int(11) NULL DEFAULT NULL COMMENT '报送主键ID',
`SOURCECORPNO` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据来源单位编码',
`SDATE` datetime(0) NULL DEFAULT NULL COMMENT '报送起始日期',
`EDATE` datetime(0) NULL DEFAULT NULL COMMENT '报送截止日期',
`RPT_TYPE` int(11) NULL DEFAULT NULL COMMENT '报表类型(1.年报、2.季报、3.月报、4.日报表 5、其他)',
`RPT_YEAR` int(11) NULL DEFAULT NULL COMMENT '报表年份',
`RPT_CYCLE` int(11) NULL DEFAULT NULL COMMENT '报表周期(月(1-12)/季度(1-4) 年1 日 1',
PRIMARY KEY (`ID`) USING BTREE,
INDEX `IDX_CUSTOMERNAME`(`CUSTOMERNAME`) USING BTREE,
INDEX `IDX_CORPTYPE`(`CORPTYPE`) USING BTREE,
INDEX `IDX_HOUSENOS`(`HOUSENOS`) USING BTREE,
INDEX `IDX_COUNTRY`(`COUNTRY`) USING BTREE,
INDEX `IDX_GRNO`(`GRNO`, `SUBMITID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1715644 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'InnoDB free: 355328 kB' ROW_FORMAT = Dynamic;



dup_msubmit_main表结构:

DROP TABLE IF EXISTS `dup_msubmit_main`;
CREATE TABLE `dup_msubmit_main` (
`SUBMIT_ID` int(11) NOT NULL AUTO_INCREMENT,
`SUBMIT_USER` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`SUBMIT_TIME` datetime(0) NULL DEFAULT NULL COMMENT '上报时间',
`DT_ID` int(10) NULL DEFAULT NULL COMMENT '源数据编号',
`SUBMIT_RESULT` decimal(10, 0) NULL DEFAULT NULL COMMENT '上报结果(-3:上报中,1:未校验,2:异常数据,3:待复核,4:待审核,5:已撤销,6:退回,7:已审核入库,8: 待核对,9:清洗异常,-100:excel防止并发的状态)',
`RPT_TYPE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '1.年报、2.季报、3.月报、4.其他、5.日报',
`SDATE` datetime(0) NULL DEFAULT NULL COMMENT '开始时间',
`EDATE` datetime(0) NULL DEFAULT NULL COMMENT '结束时间',
`FILETYPE` int(10) NULL DEFAULT NULL COMMENT '上报类型(1.接口上报、2.人工上报)',
`ISHISTORY` int(10) NULL DEFAULT NULL COMMENT '是否历史版本',
`RPT_YEAR` int(10) NULL DEFAULT NULL COMMENT '报表年份',
`RPT_CYCLE` int(10) NULL DEFAULT NULL COMMENT '报表周期(月(1-12)/季度(1-4))',
`CORPNO` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`SOURCECORPNO` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`ISCHECK` int(5) NULL DEFAULT NULL COMMENT '是否审核',
`AUDIT_TIME` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
`WAREHOUSE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '入库时间',
`REPORT_TYPE` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '1.年报、2.季报、3.月报、4.其他、5.日报',
`RPT_STYLE` int(10) NULL DEFAULT NULL COMMENT '报送类型(1:数据更新,2:与上期一致)',
`PERCENT_FULL` decimal(10, 3) NULL DEFAULT NULL COMMENT '数据完整率统计,计算位置清洗存储过程最后',
`SOLID_RECORDCODE` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`COMMENTS` varchar(1500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '错误提示',
PRIMARY KEY (`SUBMIT_ID`) USING BTREE,
INDEX `IDX_CORPNO`(`CORPNO`) USING BTREE,
INDEX `IDX_SUBMIT_TIME`(`SUBMIT_TIME`) USING BTREE,
INDEX `IDX_EDATE`(`EDATE`) USING BTREE,
INDEX `IDX_RPT_TYPE`(`RPT_TYPE`) USING BTREE,
INDEX `IDX_SUBMIT_RESULT`(`SUBMIT_RESULT`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2344 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '人工上报主表' ROW_FORMAT = Dynamic;

...全文
427 点赞 收藏 14
写回复
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
richto99 2020-06-24
You can try this: https://www.tosska.cn/tosska-sql-tuning-expert-for-mysql-tsem-free-download-zh/
回复
weixin_41611100 2020-06-24
建议加点业务特性描述,仅从sql语句看,外层的inner join 没有意义,可以直接去掉,就是统计grno的数量, 赞同@大雨将至 的看法,select count(distinct grno) from crt_customer_list_check c inner join dup_msubmit_main d on c.submitid=d.submit_id and d.submit_result=7
回复
evanweng 2020-06-22
不建议在首页做这种查询,很容易整个系统垮掉。然后可以搞点数据给我测试吗,我懒的搞
回复
JesperMax 2020-06-20
What's your MySQL database version? I think you can rewrite it with over(partition by) function.
回复
大雨将至 2020-06-02
看你的意图就是计算不重复的grno个数,干嘛写这么复杂

select count(distinct grno) from crt_customer_list_check c inner join dup_msubmit_main d on c.submitid=d.submit_id and d.submit_result=7
不行吗
回复
sxq129601 2020-06-01
只能说表结构或者页面设计是失败的,首页尽量要减少这种查询和操作
回复
乐大师 2020-05-30
引用 4 楼 bwangel 的回复:
[quote=引用 2 楼 乐大师 的回复:] 优化的一个重要手段就是读取较少的数据满足你的实际需求。 100w+的数据表join2k+的表,在极端情况下可能就是100w。 即便使用了效率较高的主键作为关联条件,因为所需数据较多,也会不会太快。 可以尝试将一个大数据表分割成几部分,分开进行统计,最后在进行累加计算。 比如分割为每10万一组,用ID字段来取指定段数据进行计算。
这个查询最后只返回一个数字。所以不涉及到大量数据问题。就是一个纯优化问题。[/quote] 举个例子:给你 1kg的大米,要求统计下有多少颗米粒。 又给你10g黄豆,要求统计下有多少颗黄豆。 哪个快? 如果1kg粮食中,既有大米也有黄豆,现在只要黄豆的数量。如果有办法现将黄豆快速筛选出来(比如用筛子),然后再统计黄豆颗粒数,那么是不是比直接到一堆粮食直接去找去数快些。 缩小统计目标的整体数量对于缩短统计时间是有帮助的。
回复
tangren 2020-05-30
1、经过d.submit_result=7这个条件过滤后的d表有多少记录? 2、最后count(*)数量是多少? 3、仅为计算一个总量,需要一张100万级表做自连接?语句这样改看一下是否可以? SELECT count(distinct grno) CUSTOMERNUM from crt_customer_list_check c inner join dup_msubmit_main d on c.submitid=d.submit_id and d.submit_result=7
回复
bwangel 2020-05-29
引用 2 楼 乐大师 的回复:
优化的一个重要手段就是读取较少的数据满足你的实际需求。 100w+的数据表join2k+的表,在极端情况下可能就是100w。 即便使用了效率较高的主键作为关联条件,因为所需数据较多,也会不会太快。 可以尝试将一个大数据表分割成几部分,分开进行统计,最后在进行累加计算。 比如分割为每10万一组,用ID字段来取指定段数据进行计算。
这个查询最后只返回一个数字。所以不涉及到大量数据问题。就是一个纯优化问题。
回复
bwangel 2020-05-29
我查询了mysql官方文档,大致意思是说内查询有group操作的话,将会使得常规优化失效。 但这种业务除了用group,想不出其他的办法
回复
乐大师 2020-05-29
优化的一个重要手段就是读取较少的数据满足你的实际需求。 100w+的数据表join2k+的表,在极端情况下可能就是100w。 即便使用了效率较高的主键作为关联条件,因为所需数据较多,也会不会太快。 可以尝试将一个大数据表分割成几部分,分开进行统计,最后在进行累加计算。 比如分割为每10万一组,用ID字段来取指定段数据进行计算。
回复
带我飞的云 2020-05-29
1. crt_customer_list_check的submitid字段
2. 你的索引和语句可以再调整
明天写SQL语句给你
回复
mokylin 2020-05-29
不清楚100W的数据分布是怎么样的,光从sql上看子查询里c.submitid=d.submit_id这个条件,考虑在crt_customer_list_check的submitid字段上加个单独索引试试?
回复
宇峰科技 2020-05-28
14、Join优化:适当增大Join_buffer_size可改善连接查询性能
Sort buffer都是面向客户服务线程分配的,如果设置过大可能造成内存浪费。甚至导致内存交换,尤其是join buffer 多表关联的查询还可能会分配多个join buffer,因此最好的策略是设置较小的全局join_buffer_size,而对需要做复杂连接操作的session单独设置较大的join_buffer_size

查询大结果集是如何返回的:
innodb的数据是保存在主键索引上的,所以全表扫描实际是直接扫描表的主键索引。所以全扫描时查到的每一行都可以直接放到结果集里面, 然后返回客户端。
实际上,服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的:
1、取一行,写到net_bufffer中,这块内存的大小是由参net_buffer_length定义的,默认16k。
2、重复获取行,直到net_buffer写满,调用网络接口发出去。
3、如果发送成功,就清空net_buffer,然后继续取下一行,并写入net_buffer。
4、如果发送函数返回eagain或wsaewouldblock,就表示本地网络栈(socket send buffer)写满了,进行等待。直到网络栈重新可写,再继续发送。

从流程中可以看到
1、在一个查询发送过程中,占用的mysql内部的内存最大就是net_buffer_length这么大,并不会达到20G(需要的数据大小)
2、socket send buffer也不可能达到20G(默认定义/proc/sys/net/core/wmem_default),如果socket send buffer 写满,就会暂停读数据的流程。
也就是说mysql是边读边发的。意味着,如果客户端接收的很慢会导致mysql服务端由于结果发不出去,这个事务的执行时间变长。

如果要减少处于sending to client这种状态的话,将net_buffer_length参数设置为一个更大的值是一个可选的方案。
查询语句的状态变化是这样的(略去无关状态):
1、mysql查询语句进行执行阶段后,首先把状态设置成sending data;
2、然后发送执行结果的列相关信息(meta data)给客户端
3、再继续执行语句的流程
4、执行完成后,把状态设置成空字符串。
也就是说 sending data并不一定是指正在发送数据,而可能是处于执行器过程中的任意阶段。
仅当一个线程片于等待客户端接收结果的状态,才会显示sending to client,而如果显示sending data意思只是正在执行。
innodb_buffer_pool_size一般建议设置为可用物理内存的60%或80%。
inndob内存管理用的是最近最少使用算法(Least Recently Used,LRU)算法,这个算法的核心就是淘汰最久未使用的数据。
回复
发帖
MySQL
创建于2007-09-28

5.4w+

社区成员

MySQL相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2020-05-28 05:54
社区公告
暂无公告