记录一次mysql慢查询的优化过程_mysql dateformat 导致慢查询优化

前言

业务上线后经常报查询超时，数据库使用的是阿里云的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;
1
2
3
4
5
6

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

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

例1

表里面有810w+的数据

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`)
1
2

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;
1
2
3
4
5
6
7
8

• 修改后的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;
1
2
3
4
5
6
7
8

查询速度对比

• 原sql执行时间
  

• 修改后的sql执行时间
  

直接提升了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')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

修改后的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')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询速度对比

• 原sql执行时间
  
• 修改后的sql执行时间
  
  从28秒提升到3秒，提升了8-9倍

例3

表数据530w+

创建了联合索引

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

• 原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';
1
2
3
4
5
6
7
8
9
10

• 修改后的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));
1
2
3
4
5
6
7
8

查询速度对比

• 原sql执行时间
  
• 修改后的sql执行时间
  
  提升约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';
1
2
3
4
5
6
7
8
9
10

• 修改后的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 ); 
1
2
3
4
5
6
7
8
9
10

查询速度对比

• 原sql执行时间
  
• 修改后的sql执行时间
  
  提升约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`
1
2
3
4
5
6
7
8
9
10
11

• 修改后的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`
1
2
3
4
5
6
7
8
9
10
11

查询速度对比

• 原sql执行时间
  
• 修改后的sql执行时间
  
  有所提升。

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

例6：百万数据分页

• 原sql

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

• 修改后的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
1

查询速度对比

• 原sql执行时间
  
• 修改后的sql执行时间
  
  这里是使用了覆盖索引，避免了回表操作，查询性能提升约60倍

总结

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