MySQL百万数据深度分页优化思路分析

矫情吗；* 2023-09-27 21:05 23阅读 0赞

# 业务场景 #

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

# 瓶颈再现 #

创建了一张user表，给create\_time字段**添加了索引**。并在该表中添加了100w条数据。

![format_png][]

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

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

![format_png 1][]

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

![format_png 2][]

> 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
    
    ![format_png 3][]
 *  **优化后**：0.2s
    
    ![format_png 4][]

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

[format_png]: https://img-blog.csdnimg.cn/img_convert/d85361e1331735e5de61dad67d7079df.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/ae7df0d5184ba929266b8499750c77d4.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/4c81b8d71a203d0facd8a07064409eb7.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/7a7245ad6c7cbc4527ff0eaa92d4434c.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/770e518ccf52d0d644171195e60b113f.webp?x-oss-process=image/format,png