原文网址：MySQL--大数据量的分页优化方案_IT利刃出鞘的博客-CSDN博客

简介

        本文介绍如何优化MySQL的大表的分页查询。

问题描述

        在分页查询时，分页页数越大，查询速度越慢。

        比如：测试起始页不断增加查看SQL的执行时间：

mysql> SELECT * FROM order LIMIT 10000,10 ;
mysql> SELECT * FROM order LIMIT 100000,10 ;
mysql> SELECT * FROM order LIMIT 1000000,10 ;
mysql> SELECT * FROM order LIMIT 5000000,10 ;

结果：

mysql> SHOW PROFILES;
+----------+-------------+-------------------------------------+
| Query_ID | Duration    | Query                               |
+----------+-------------+-------------------------------------+
|       1 |  0.00861825 | SELECT * FROM order LIMIT 10000,10   |
|       2 |  0.68741175 | SELECT * FROM order LIMIT 100000,10  |
|       3 |  5.62566875 | SELECT * FROM order LIMIT 1000000,10 |
|       4 | 18.33333200 | SELECT * FROM order LIMIT 5000000,10 |
+----------+-------------+-------------------------------------+

 可以看到，随着起始位置的增加，查询时间也不断增加。

原因分析

查询流程概述

        假设查询第100000页数据，每页10条，则分页查询的语句是：

SELECT * FROM TABLE_NAME WHERE … LIMIT 1000000,10。

        本次测试，上述SQL的执行结果为：3.463s。 

        MySQL对上述的SQL的处理流程为：先排序，然后取出1000010条数据，然后舍去前100000条数据，返回10条数据。

查询慢的原因

        需要查询1000010次聚簇索引的数据，最后再将结果过滤掉前1000000条，取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上，而有1000000次随机I/O查询到的数据是不会出现在结果集当中的。

解决方案

思路是：使用覆盖索引，直接跳过无用的数据，而不是取出来之后再舍弃掉。:

方案1：范围查询 + 子查询

先通过子查询查覆盖索引的字段，确定出范围，然后再查。

SELECT * FROM order WHERE  id>(SELECT * FROM order LIMIT 1000000, 10) LIMIT 10;

 查询时间为：0.096s。

方案2：记住上次查询位置

        假设上次查询的最后一行数据的id为12345678，则使用此SQL进行查询：

SELECT * FROM order WHERE  id>12345678 LIMIT 10;

        这样就不会把前边数据查出来然后排序了，而是直接查询第100000条数据。其执行时间：0.095s。

注意事项

1. 这个方案的使用场景是：id是有序的（自增或者使用了雪花算法等）。
2. 也可以使用其他的字段作为上次查询位置的标记。比如，我们经常会使用创建时间来进行排序，完全可以将创建时间加索引，然后通过创建时间来记住上次查询位置。

缺点

        查询结果不是实时的。如果查了一次之后，数据库里又插入了数据，用这种记住位置的方法查询时查到的就不是最新数据。

方案3：延迟关联 + 子查询

        先只查询id出来，然后再拿id关联本表全部数据，进行内连接查询。id查询比较快，然后内连接的时候，只从全部数据中找条件满足的，所以效率很高。

SELECT a.* FROM order AS a JOIN (SELECT id FROM order LIMIT 1000000,10) b on a.id=b.id

查询时间为：1.231s。