通过联合索引优化MYSQL慢查询

通过联合索引优化MYSQL慢查询

 

一、慢SQL分析

慢SQL如下

SELECT SUM(t.amount) as amount FROM (SELECT game_id,room_id,is_robot,amount,record_time FROM t_statements_logs WHERE game_id = 30 AND is_robot = 0 ORDER BY record_time DESC)t
GROUP BY t.room_id ORDER BY t.record_time DESC LIMIT 20;

执行时间通常在1S以上

表索引情况如下：

查询执行计划

EXPLAIN
SELECT SUM(t.amount) as amount FROM (SELECT game_id,room_id,is_robot,amount,record_time FROM t_statements_logs WHERE game_id = 30 AND is_robot = 0 ORDER BY record_time DESC)t
GROUP BY t.room_id ORDER BY t.record_time DESC LIMIT 20;

执行计划如下

key为game_Index命中了game_Index索引

Using index condition; Using temporary; Using filesort：表示使用了索引查询，并使用了临时表和内存排序

rows：375574

 

二、SQL优化分析

从以上情况看扫描的数据过多导致执行时间变长，可根据record_time限制扫描的数据范围

修改game_Index索引，增加record_time字段

修改后的SQL

SELECT SUM(t.amount) as amount FROM (SELECT game_id,room_id,is_robot,amount,record_time FROM t_statements_logs WHERE game_id = 30 AND is_robot = 0 AND record_time > '2020-03-09 00:00:00'  ORDER BY record_time DESC)t
GROUP BY t.room_id ORDER BY t.record_time DESC LIMIT 20;

由于业务是根据record_time降序取前20条，通常一个game_id最近一个月产生的room_id会超过20条，所以查询数据时间范围限制到最近一个月；假如当前时间为：2020-04-09 00:00:00，往前推一个月为：2020-03-09 00:00:00。

新SQL执行计划结果

key_len变成了85，说明record_time也使用了索引

新SQL执行时间已经变成了8ms