错误使用索引引起的性能问题

索引真的不一定更快

1. 原查询代码，以及索引信息

SELECT
    sourceId,
    group_concat( DISTINCT targetId ORDER BY created DESC ) targetIds,
    max( created ) lastTime 
FROM
    message_table 
WHERE
    ( created >= '2018-10-12 08:00:00' AND created <= '2018-10-12 10:59:59' ) 
GROUP BY sourceId)

索引	索引列
idxSourceId	sourceId（Int）
idxTargetIds	targetId（Int）
idxSourceTarget	sourceId, targetId

执行查询，花费了170s（这是多次测试后，最低的一次）

2. 那好吧，只有explain一下了

+----+-------------+------------------+------------+-------+----------------------------+-----------+---------+------+---------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys              | key       | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------------+------------+-------+----------------------------+-----------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | message_table | NULL       | index | idxSourceTarget,idxSourceId | idxSourceId | 4       | NULL | 9783185 |    11.11 | Using where |
+----+-------------+------------------+------------+-------+----------------------------+-----------+---------+------+---------+----------+-------------+

看一下，大致看一下，居然扫描了全部的数据，而且还是在使用了索引的情况下。等一下，为什么要使用idxSourceId这个索引，根本没有减少需要扫描的行数啊

3. 强制不使用索引

......
FROM
    message_table ignore index(idxSourceTarget,idxSourceId) 
......

执行查询，花费了10s，哈哈，不使用索引之后居然降低到了10s，但是仍然不行，线上大概有1.2亿条数据，而且在不断增多（性能问题，也是因为数据不断增多才出现的）

4. 好吧，把created字段加上索引试一试

注：ignore index(idxSourceTarget,idxSourceId)

ALTER TABLE `message_table` 
ADD INDEX `idxCreated`(`created`);

执行查询，0.772s

5. 高兴的太早了

将时间范围加大，大到一个时间范围内有100万条数据的情况

WHERE created >= '2010-10-12 08:00:00' AND created <= '2019-01-05 10:59:59'

执行查询，4s

6. 使用覆盖索引

ALTER TABLE `message_table` 
DROP INDEX `idxCreated`,
ADD INDEX `idxCreated`(`created`, `sourceId`, `targetId`) USING BTREE;

执行查询，0.984s，呦西，今天加个鸡腿

7. 更多优化

1. 优化分组后的排序
   group_concat( DISTINCT targetId ORDER BY created DESC ) targetIds

这个优化可以看出来，每个GROUP BY里面都需要进行排序，那为什么不一次性完成排序呢

SELECT
    table_tmp.sourceId,
    group_concat(DISTINCT table_tmp.targetId ) targetIds,
    max( table_tmp.created ) lastTime 
FROM
    (SELECT sourceId, targetId, created 
        FROM message_table 
        WHERE created >= '2010-10-12 08:00:00' AND created <= '2019-01-05 10:59:59' 
        ORDER BY created) 
        AS table_tmp
GROUP BY table_tmp.sourceId 

执行查询：0.679s，算是更进一步

2. 优化时间索引
   因为我们的查询大多是会查询比较新的数据，所以如果索引能够是倒序索引，那么就能够更快的定位到数据，目前MYSQL8.0以上是支持的
   倒序索引

总结

1. 使用idxSource索引，引起的性能问题，因为GROUP BY且分组字段上有索引，时间（created）字段没有索引，这个时候mysql会使用分组字段的索引来查找分组数据
2. 使用覆盖索引，减少对磁盘的随机访问

我的个人博客,有空来坐坐