前言
我们知道，Mysql分页查询中limit查询，比如如下语句：
1 | select * from my_table where index_col = xxx limit offset,limit; |
是会读取offset前面的数据的。
其表现为根据index_col = xxx条件定位到offset，再向后取limit行。
当数据量变大时，执行效率会下降。
什么意思呢？
我们通过一个例子来看下，并了解相关的优化方法。
正文
准备工作
我们准备一张student表（随便一张测试表就行），其结构如下：
1 | CREATE TABLE `student` ( |
并向其中灌入200w数据（越多越好）。
部分相关代码如下：
1 |
|
以上便完成了我们的前期数据准备工作。
测试
我们开始使用分页语句limit进行多次测试。
1 | select * from student ORDER BY stu_no LIMIT 10,10; |
我们可以看到查询还是比较快的。
我们增大offset进行测试，这次我们来查询1000000条后的10条。
1 | select * from student ORDER BY stu_no LIMIT 1000000,10; |
可以看到耗时已经达到了 3.6s。
当我们继续增大offset时，花费的时间会更多。
分析
在上面建表时，我们stu_no是有索引的，我们用 explain分析一下如下两条语句。
1 | EXPLAIN select * from student ORDER BY stu_no LIMIT 10,10; |
可以看到当offset较小时是使用到了stu_no索引。
1 | EXPLAIN select * from student ORDER BY stu_no LIMIT 1000000,10; |
可以看到，当offset较大时，该语句就会进行全表扫描，这就是效率低的原因。
为什么会这样呢？
因为我们给stu_no创建了索引，为B+Tree，普通索引其子节点上存储的是主键值，我们按照stu_no排序时，其逻辑为先根据stu_no找到主键id，再根据主键索引找到记录（回表），数据量较大时，Mysql由于需要拉出offset前的所有数据，因此查询优化器分析后，认为还不如走全表扫描，因此就会走全表扫描。
这也就是上面我们看到的结果。
优化
定位到上面问题后，我们该如何优化呢？
我们先来看下如果强制使用stu_no进行查询，结果如何？
1 | select * from student force INDEX(idx_stu_no) ORDER BY stu_no LIMIT 1000000,10; |
可以看到耗时5s，比全表扫描要慢，看来Mysql的查询优化器分析的是没错的。
我们有什么优化手段呢？
我们想要的是1000000条后的10条数据，也就是前面的数据是没用的，但是Mysql查询的时候是要分析前1000000条数据的。
因此我们可以只保留最简单的信息，即主键，如下：
1 | select id from student ORDER BY stu_no LIMIT 1000000,10; |
可以看到这条语句查询很快，也用到了索引（就相当于在stu_no的B+Tree索引中找数据，不用回表）。
这样相当于拿到了1000000条后的10条数据的主键，我们用它们作为临时表，关联一下即可拿到数据。
1 | select * from student s,(select id from student ORDER BY stu_no LIMIT 1000000,10) temp where temp.id=s.id; |
可以看到优化后的耗时大大缩短，我们使用explain可以看到分析结果。
总结
上述就是关于Mysql分页语句limit的优化总结。
其关键思路如下：
- 根据条件（默认条件字段有索引）及
limit拿到主键列表（会直接查询索引树，不用回表）； - 根据拿到的主键列表关联主表查询。
1 | select * from my_table s,(select id from my_table ORDER BY index_col LIMIT 1000000,10) temp where temp.id=s.id; |
普通的limit语句慢的原因：
1 | select * from my_table ORDER BY index_col LIMIT 1000000,10; |
Mysql需要查询前1000000行，而后再取后面10行数据，其实前1000000行我们遍历直接通过index_col建立的B+Tree来处理就行。
