【实战】MySQL百万数据优化

业务场景

一般在项目开发中会有很多的统计数据需要进行上报分析，一般在分析过后会在后台展示出来给运营和产品进行「分页查看」，「最常见的一种就是根据日期进行筛选」。这种统计数据随着时间的推移数据量会慢慢的变大，达到百万、千万条数据只是时间问题。

瓶颈再现

创建了一张user表，给create_time字段「添加了索引」。并在该表中添加了100w条数据。

我们这里使用「limit分页」的方式查询下「前5条」数据和「后5条」数据在查询时间上有什么区别。

查询「前10」条基本上不消耗什么时间

我们从第「50w」+开始取数据的时候，查询耗时1秒。

SQL_NO_CACHE
这个关键词是为了不让SQL查询走缓存。

同样的SQL语句，不同的分页条件，两者的性能差距如此之大，那么随着数据量的增长，往后页的查询所耗时间按理会越来越大。

问题分析

回表

我们一般对于查询频率比较高的字段会建立索引。索引会提高我们的查询效率。我们上面的语句使用了「SELECT * FROM user」，但是我们并不是所有的字段都建立了索引。当从「索引文件」中查询到符合条件的数据后，还需要从「数据文件」中查询到没有建立索引的字段。那么这个过程称之为「回表」。

覆盖索引

如果查询的字段正好创建了索引了，比如 「SELECT create_time FROM user」，我们查询的字段是我们创建的索引，那么这个时候就不需要再去数据文件里面查询，也就「不需要回表」。这种情况我们称之为「覆盖索引」。

IO

「回表操作通常是IO操作」，因为需要根据索引查找到数据行后，再根据数据行的主键或唯一索引去聚簇索引中查找具体的数据行。聚簇索引一般是「存储在磁盘上」的数据文件，因此在执行回表操作时需要从磁盘读取数据，而磁盘IO是相对「较慢」的操作。

LIMTI 2000,10 ？

你有木有想过「LIMIT 2000,10」会不会扫描1-2000行，你之前有没有跟我一样，觉得数据是直接从2000行开始取的，前面的根本没扫描或者不回表。其实这样的写法，一个完整的流程是查询数据，如果不能覆盖索引，那么也是要回表查询数据的。

现在你知道为什么越到后面查询越慢了吧！

问题总结

我们现在知道了LIMIT 遇到后面查询的性能越差，「性能差的原因是因为要回表」，既然已经找到了问题那么我们只需要「减少回表的次数」就可以提升查询性能了。

解决方案

既然覆盖索引可以防止数据回表，那么我们可以先查出来主键id（主键索引），然后将查出来的数据作为「临时表」然后 「JOIN」 原表就可以了，这样只需要对查询出来的5条结果进行数据回表，大幅减少了IO操作。

优化前后性能对比

我们看下执行效果：

• 「优化前」：1.4s

• 「优化后」：0.2s

查询耗时性能大幅提升。这样如果分页数据很大的话，也不会像普通的limit查询那样慢。