2017-03-28

mysql查询优化技术之语义优化

摘要：工作中遇到的SQL语义优化案例

发现慢SQL

通过监控慢查询，执行时间在1秒以上的SQL如下：

完整SQL：

查看表结构：

查看执行计划：

EXPLAIN extended
SELECT id, content, content_type, receiver_id, TYPE, function_type, function_id,
read_status, send_time, read_time, STATUS
FROM
xxxtable
WHERE receiver_id = '1667510020'
AND STATUS = 1
AND TYPE = 2
AND send_time < '2017-03-22 08:35:50.553' 
AND DATE_SUB(CURDATE(), INTERVAL 12 MONTH ) <= DATE(send_time) 
ORDER BY send_time DESC LIMIT 1; 
/* select#1 */
select 
`xxxxdb`.`xxxxtable`.`id` AS `id`,
`xxxxdb`.`xxxxtable`.`content` AS `content`,
`xxxxdb`.`xxxxtable`.`content_type` AS `content_type`,
`xxxxdb`.`xxxxtable`.`receiver_id` AS `receiver_id`,
`xxxxdb`.`xxxxtable`.`type` AS `TYPE`,
`xxxxdb`.`xxxxtable`.`function_type` AS `function_type`,
`xxxxdb`.`xxxxtable`.`function_id` AS `function_id`,
`xxxxdb`.`xxxxtable`.`read_status` AS `read_status`,
`xxxxdb`.`xxxxtable`.`send_time` AS `send_time`,
`xxxxdb`.`xxxxtable`.`read_time` AS `read_time`,
`xxxxdb`.`xxxxtable`.`status` AS `STATUS` 
from
`xxxxdb`.`xxxxtable` 
where (
(
`xxxxdb`.`xxxxtable`.`type` = 2
) 
and (
`xxxxdb`.`xxxxtable`.`status` = 1
) 
and (
`xxxxdb`.`xxxxtable`.`receiver_id` = '1667510020'
) 
and (
`xxxxdb`.`xxxxtable`.`send_time` < '2017-03-22 08:35:50.553'
) 
and (
< cache > ((curdate() - interval 12 month)) <= cast(
`xxxxdb`.`xxxxtable`.`send_time` as date
)
)
) 
order by `xxxxdb`.`xxxxtable`.`send_time` desc 
limit 1

优化一

从表结构定义上看，已经有receiver_id和send_time的联合索引就没必要单独再需要receiver_id索引了。因此可以先drop掉索引idx_receiverid！

优化二

从SQL语义上看，最关键的是发送时间条件：

AND send_time < '2017-03-22 08:35:50.553' 
AND DATE_SUB(CURDATE(), INTERVAL 12 MONTH ) <= DATE(send_time)

初步判断业务需求是要查询发送时间小于当前时间，且发送时间在最近12个月内的数据。
Send_time数据库中的时间戳格式是：年月日时分秒毫秒，这里却对send_time字段做了date函数转换，开发者目的是为了和12个月前的年月日格式进行比对。为什么不直接让send_time和12个月前的年月日时分秒毫秒格式进行比对呢？这样不就可以去掉对索引字段做函数了吗。因此，我们可以将这2个条件改写为如下：

AND send_time < '2017-03-22 08:35:50.553' 
AND send_time >= DATE_SUB(NOW(), INTERVAL 12 MONTH )

优化后的执行计划：

这句SQL在优化前的执行计划中是：

and (
< cache > ((curdate() - interval 12 month)) <= cast(
`xxxxdb`.`xxxxtable`.`send_time` as date
)

通过对比可以看到优化后省去了mysql对字段做类型转换的工作，同时可以充分利用该字段的索引。

本文标题:mysql查询优化技术之语义优化

文章作者:一条大河

发布时间:2017-03-28, 17:04:15

最后更新:2017-06-01, 16:55:16

原始链接:http://riverdba.github.io/2017/03/28/Sematic-Optimization-of-mysql/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。