记录一次mysql慢查询的优化过程

布满荆棘的人生 2023-10-04 20:28 65阅读 0赞

#### 前言 ####

> 业务上线后经常报查询超时，数据库使用的是阿里云的RDS，mysql版本是`5.6.16-log`，有几条统计数据的查询语句执行很慢，有的甚至执行一次需要10多秒，简直无法忍受。  
> 查看了超时时间，默认为0

show variables like '%MAX_STATEMENT_TIME%'
    show variables like '%MAX_EXECUTION_TIME%'
    # 网上说这个参数只针对5.7以上的版本生效
    SET GLOBAL MAX_EXECUTION_TIME=30000;
    # 对5.6版本生效
    SET GLOBAL MAX_STATEMENT_TIME=30000;

> 然后在 `select` 后面 加上 `/*+ max_statement_time(1000) */`。  
> 设置后也没有效果，然后在阿里云上面手动修改对应的参数，也没用。

最后在阿里云上面看到几条优化建议，参考阿里云的建议，查询速度明显提升，这里也记录一下。

#### 例1 ####

表里面有810w+的数据  
![在这里插入图片描述][595cad5aad4d44629880961f11ba9bfa.png]

1.  首先，就是建立正确的索引。  
    多创建了两个联合索引，第一个是之前创建的，虽然能命中，但不是最佳。

ALTER TABLE `user_event_record` ADD INDEX `idx_event_gmtcreate_userid` (`event`, `gmt_create`, `user_id`)	
    ALTER TABLE `user_event_record` ADD INDEX `idx_gmtcreate_event` (`gmt_create`, `event`)

![在这里插入图片描述][c795f19572764e029360ae67585de13e.png]  
2. 修改时间查询条件

*  原sql

SELECT 
    	LEFT (f.gmt_create, 10 ) `date`,
    	f.event,
    	COUNT(1) total 
    FROM user_event_record f 
    WHERE
    	date_format(f.gmt_create, '%y%m%d') >= date_format('2023-01-14', '%y%m%d') 
    GROUP BY `date`, f.event;

*  修改后的sql

SELECT 
    	LEFT (f.gmt_create, 10) `date`,
    	f.event,
    	COUNT(1) total 
    FROM user_event_record f 
    WHERE
    	f.gmt_create > DATE('2023-01-14') 
    GROUP BY `date`, f.event;

查询速度对比

*  原sql执行时间  
    ![在这里插入图片描述][16583b40b7c742cf8fdc0285b9655f1c.png]
 *  修改后的sql执行时间  
    ![在这里插入图片描述][d0f61ad2ef1c4c32b837781e3a062df6.png]

直接提升了7-8倍，查询结果一致，效果提升还是很明显的。

#### 例2 ####

同一个表  
原SQL

SELECT
    		DATE_FORMAT(gmt_create, '%m-%d') AS `time`,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' OR event = 'enterSystemSuc' THEN user_id END) AS total,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' THEN user_id END) AS success,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' THEN user_id END) AS fail,
    		COUNT(DISTINCT CASE WHEN app = '1.0.7' and (event = 'enterSystemFail' OR event = 'enterSystemSuc') THEN user_id END) AS oldCount,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' AND app = '1.0.7' THEN user_id END) AS oldSuccess,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' AND app = '1.0.7' THEN user_id END) AS oldFail,
    		COUNT(DISTINCT CASE WHEN app = '1.0.8' and (event = 'enterSystemFail' OR event = 'enterSystemSuc') THEN user_id END) AS newCount,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' AND app = '1.0.8' THEN user_id END) AS newSuccess,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' AND app = '1.0.8' THEN user_id END) AS newFail
    FROM user_event_record
    WHERE DATE_FORMAT(gmt_create, '%Y-%m-%d') > DATE_SUB(CURDATE(), INTERVAL 10 DAY)
    GROUP BY DATE_FORMAT(gmt_create, '%m-%d')

修改后的sql

SELECT
    		DATE_FORMAT(gmt_create, '%m-%d') AS `time`,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' OR event = 'enterSystemSuc' THEN user_id END) AS total,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' THEN user_id END) AS success,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' THEN user_id END) AS fail,
    		COUNT(DISTINCT CASE WHEN app = '1.0.7' and (event = 'enterSystemFail' OR event = 'enterSystemSuc') THEN user_id END) AS oldCount,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' AND app = '1.0.7' THEN user_id END) AS oldSuccess,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' AND app = '1.0.7' THEN user_id END) AS oldFail,
    		COUNT(DISTINCT CASE WHEN app = '1.0.8' and (event = 'enterSystemFail' OR event = 'enterSystemSuc') THEN user_id END) AS newCount,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemSuc' AND app = '1.0.8' THEN user_id END) AS newSuccess,
    		COUNT(DISTINCT CASE WHEN event = 'enterSystemFail' AND app = '1.0.8' THEN user_id END) AS newFail
    FROM user_event_record
    WHERE gmt_create > DATE_SUB(CURDATE(), INTERVAL 9 DAY)
    GROUP BY DATE_FORMAT(gmt_create, '%m-%d')

查询速度对比

*  原sql执行时间  
    ![在这里插入图片描述][9b177fa9db7c432db44ae3d44fe566ee.png]
 *  修改后的sql执行时间  
    ![在这里插入图片描述][ef539c78b6df466db2fb9feedfd3b63c.png]  
    从28秒提升到3秒，提升了8-9倍

#### 例3 ####

表数据530w+  
![在这里插入图片描述][a709ddc655b245589a7210f8cfc61694.png]  
创建了联合索引

ALTER TABLE `file_record_info` ADD INDEX `idx_mark_gmtcreate_userid` (`mark`, `gmt_create`, `user_id`)

![在这里插入图片描述][99422056c0d34a14b02330298035a644.png]

*  原sql

SELECT
    	'2023-01-12' AS `date`,
    	COUNT(DISTINCT CASE WHEN `mark` = '10000' THEN `user_id` ELSE NULL END) AS `upgradeFailCount`,
    	COUNT(DISTINCT CASE WHEN `mark` = '10001' THEN `user_id` ELSE NULL END) AS `enterSysFailCount`,
    	COUNT(DISTINCT CASE WHEN `mark` = '10008' THEN `user_id` ELSE NULL END) AS `dllLoadFailCount`,
    	COUNT(DISTINCT CASE WHEN `mark` = '10009' THEN `user_id` ELSE NULL END) AS `enableDeviceFailCount`	
    FROM file_record_info 
    WHERE 
    	mark IN ('10000', '10001', '10008', '10009' ) 
    	AND DATE_FORMAT(gmt_create, '%Y-%m-%d') = '2023-01-12';

*  修改后的sql

SELECT
    	'2023-01-12' AS `date`,
    	COUNT(DISTINCT CASE WHEN mark = '10000' THEN user_id END) AS upgradeFailCount,
    	COUNT(DISTINCT CASE WHEN mark = '10001' THEN user_id END) AS enterSysFailCount,
    	COUNT(DISTINCT CASE WHEN mark = '10008' THEN user_id END) AS dllLoadFailCount,
    	COUNT(DISTINCT CASE WHEN mark = '10009' THEN user_id END) AS enableDeviceFailCount
    FROM file_record_info WHERE mark IN ( '10000', '10001', '10008', '10009' )
    AND (`gmt_create` >= DATE('2023-01-12') AND `gmt_create` < DATE_ADD(DATE('2023-01-12'), INTERVAL 1 DAY));

查询速度对比

*  原sql执行时间  
    ![在这里插入图片描述][7e2d0af569ff4133bf14c1161b4c5fd0.png]
 *  修改后的sql执行时间  
    ![在这里插入图片描述][39658503f5054587aca193c3c7e276fa.png]  
    提升约30倍

#### 例4 ####

同样的例子

SELECT
    /*+ MAX_EXECUTION_TIME(20000) */
    	'2023-01-15' AS `date`,
    	COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemFail' OR `event` = 'enterOBDSystemSuc' THEN user_id END ) AS total,
    	COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemSuc' THEN user_id END ) AS success,
    	COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemFail' THEN user_id END ) AS fail 
    FROM user_event_record 
    WHERE
    	`event` IN ( 'enterOBDSystemFail', 'enterOBDSystemSuc' ) 
    	AND DATE_FORMAT( gmt_create, '%Y-%m-%d' ) = '2023-01-15';

*  修改后的sql

SELECT
    	'2023-01-15' AS `date`,
    	COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemFail' OR `event` = 'enterOBDSystemSuc' THEN `user_id` ELSE NULL END ) AS `total`,
    	COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemSuc' THEN `user_id` ELSE NULL END ) AS `success`,
    	COUNT( DISTINCT CASE WHEN `event` = 'enterOBDSystemFail' THEN `user_id` ELSE NULL END ) AS `fail` 
    FROM `user_event_record` 
    WHERE
    	`event` IN ( 'enterOBDSystemFail', 'enterOBDSystemSuc' ) 
    	AND `gmt_create` >= DATE( '2023-01-15' ) 
    	AND `gmt_create` < DATE_ADD( DATE( '2023-01-15' ), INTERVAL 1 DAY );

查询速度对比

*  原sql执行时间  
    ![在这里插入图片描述][bf8a8fce8fb349b1b3c99942856a5e15.png]
 *  修改后的sql执行时间  
    ![在这里插入图片描述][1fd2fe9905d74ceca09eb45899b5213d.png]  
    提升约40倍

#### 例5 ####

*  原sql

SELECT
    	IFNULL( DATE_FORMAT( gmt_create, '%Y-%m-%d' ), '2022-12-01' ) `date`,
    	COUNT( DISTINCT CASE WHEN mark = '10000' THEN user_id END ) AS upgradeFailCount,
    	COUNT( DISTINCT CASE WHEN mark = '10001' THEN user_id END ) AS enterSysFailCount,
    	COUNT( DISTINCT CASE WHEN mark = '10008' THEN user_id END ) AS dllLoadFailCount,
    	COUNT( DISTINCT CASE WHEN mark = '10009' THEN user_id END ) AS enableDeviceFailCount 
    FROM file_record_info 
    WHERE
    	mark IN ('10000', '10001', '10008', '10009') 
    	AND DATE_FORMAT(gmt_create, '%Y-%m-%d')>='2022-12-01'
    GROUP BY `date`

*  修改后的sql

SELECT
    	IFNULL( DATE_FORMAT( gmt_create, '%Y-%m-%d' ), '2022-12-01' ) `date`,
    	COUNT( DISTINCT CASE WHEN mark = '10000' THEN user_id END ) AS upgradeFailCount,
    	COUNT( DISTINCT CASE WHEN mark = '10001' THEN user_id END ) AS enterSysFailCount,
    	COUNT( DISTINCT CASE WHEN mark = '10008' THEN user_id END ) AS dllLoadFailCount,
    	COUNT( DISTINCT CASE WHEN mark = '10009' THEN user_id END ) AS enableDeviceFailCount 
    FROM file_record_info 
    WHERE
    	mark IN ('10000', '10001', '10008', '10009') 
    	AND gmt_create > DATE('2022-12-01') 
    GROUP BY `date`

查询速度对比

*  原sql执行时间  
    ![在这里插入图片描述][f6278122e8014f1b9e6330391c3459dd.png]
 *  修改后的sql执行时间  
    ![在这里插入图片描述][41fd4b85e2224e84b01ac5e570fff3d3.png]  
    有所提升。

\--------2023-05-13补充-------

#### 例6：百万数据分页 ####

*  原sql

SELECT  * FROM sys_oper_log WHERE  title like '%用户服务-上报用户事件%' LIMIT 1000000,10

*  修改后的sql

SELECT u.* FROM (SELECT oper_id FROM sys_oper_log WHERE title like '%用户服务-上报用户事件%' LIMIT 1000000,10) AS temp INNER JOIN sys_oper_log u ON u.oper_id = temp.oper_id

查询速度对比

*  原sql执行时间  
    ![在这里插入图片描述][1ea2ea5528da46eeb30ae5f35a25ee84.png]
 *  修改后的sql执行时间  
    ![在这里插入图片描述][1a4322c5087c49edbb633eb0bf271102.png]  
    这里是使用了覆盖索引，避免了回表操作，查询性能提升约60倍

#### 总结 ####

1.  首先索引一定要根据业务来创建，不然没办法命中，可以通过`EXPLAIN`关键字来分析。
2.  尽量避免`DATE_FORMAT`函数
3.  优化sql，避免不必要的回表操作

[595cad5aad4d44629880961f11ba9bfa.png]: https://img-blog.csdnimg.cn/595cad5aad4d44629880961f11ba9bfa.png
[c795f19572764e029360ae67585de13e.png]: https://img-blog.csdnimg.cn/c795f19572764e029360ae67585de13e.png
[16583b40b7c742cf8fdc0285b9655f1c.png]: https://img-blog.csdnimg.cn/16583b40b7c742cf8fdc0285b9655f1c.png
[d0f61ad2ef1c4c32b837781e3a062df6.png]: https://img-blog.csdnimg.cn/d0f61ad2ef1c4c32b837781e3a062df6.png
[9b177fa9db7c432db44ae3d44fe566ee.png]: https://img-blog.csdnimg.cn/9b177fa9db7c432db44ae3d44fe566ee.png
[ef539c78b6df466db2fb9feedfd3b63c.png]: https://img-blog.csdnimg.cn/ef539c78b6df466db2fb9feedfd3b63c.png
[a709ddc655b245589a7210f8cfc61694.png]: https://img-blog.csdnimg.cn/a709ddc655b245589a7210f8cfc61694.png
[99422056c0d34a14b02330298035a644.png]: https://img-blog.csdnimg.cn/99422056c0d34a14b02330298035a644.png
[7e2d0af569ff4133bf14c1161b4c5fd0.png]: https://img-blog.csdnimg.cn/7e2d0af569ff4133bf14c1161b4c5fd0.png
[39658503f5054587aca193c3c7e276fa.png]: https://img-blog.csdnimg.cn/39658503f5054587aca193c3c7e276fa.png
[bf8a8fce8fb349b1b3c99942856a5e15.png]: https://img-blog.csdnimg.cn/bf8a8fce8fb349b1b3c99942856a5e15.png
[1fd2fe9905d74ceca09eb45899b5213d.png]: https://img-blog.csdnimg.cn/1fd2fe9905d74ceca09eb45899b5213d.png
[f6278122e8014f1b9e6330391c3459dd.png]: https://img-blog.csdnimg.cn/f6278122e8014f1b9e6330391c3459dd.png
[41fd4b85e2224e84b01ac5e570fff3d3.png]: https://img-blog.csdnimg.cn/41fd4b85e2224e84b01ac5e570fff3d3.png
[1ea2ea5528da46eeb30ae5f35a25ee84.png]: https://img-blog.csdnimg.cn/1ea2ea5528da46eeb30ae5f35a25ee84.png
[1a4322c5087c49edbb633eb0bf271102.png]: https://img-blog.csdnimg.cn/1a4322c5087c49edbb633eb0bf271102.png