求助：一条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;

...全文

525 14 打赏收藏转发到动态举报

写回复

用AI写文章

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 5.7新增了众多特色功能，诸如Optimizer增强、支持多个触发器、P_S增强、增加sys schema、在线修改VARCHAR长度、并发复制等，对于提升SQL效率都有很大帮助，作为非专业DBA，如何利用这些特性帮助我们做好SQL...

编译安装Mysql5.7，并测试连接以及常用sql语句

前言：工作中同事使用MySQL8.0导出sql文件，我的电脑上MySQL版本为5.7，所以无论怎么导入sql文件都会失败。经过查看sql文件之后发现，sql语句都设置了排序规则，而MySQL8.0默认的排序规则就是utf8mb4_0900_ai_ci，而MySQL5.7中没有对应的排序规则。解决办法：直接卸载原有的MySQL5.7，按装MySQL8.0！！因为我个人开始在升级时出了许多问题，例如在重设密码时报错：ERROR 1045 (28000): Access denied for us

环境： window10 sqlserver2008 mysql5.7 说明：在编写后端应用程序时，为了提高响应性能，我们会将多条sql语句放在一起执行，如下： insert into t_user(id,name) values(1,'小明'); insert into t_book(id,name) values(1,'语文'); insert into t_user_book(id,userid,bookid) values(1,1,1); 但是，我们考虑下，如果上面的sql语句出现错误了怎么

SQL语句优化