1 | select * from person_tab where age = 10 limit 40000, 10; |
mysql 在执行以上语句时(假设在age字段上设置了索引)，会在 age 索引上找到 age=10 的数据，然后把前面的 40010 个 id 都回表把记录行数据查出来之后，再筛选出第 40000~40010 条数据，因此当 offset 越大，查询就会越慢，这个就是 mysql 的深翻页问题。
两种解决方法
对于深翻页问题，一般有以下两种解决方法：
- 利用二级索引先查询满足条件的 id 集合，再利用 where id in (xxx, xxx) 来的方式来查询记录行数据
1 | select id from person_tab where age = 10 limit 40000, 10; |
这种方法主要是利用覆盖索引减少了回表的次数
- 第一条查询 id 的语句，只查询 id，利用覆盖索引的特性，mysql不会进行回表操作，mysql 把符合要求的前 40010 条数据的 id 扫描出来以后，再筛选出第 40000~40010 条的 id 后返回
- 第二条语句会利用一级索引查询满足条件的数据，
in查询的id数量在一定范围内，mysql(5.7.17版本及以后) 是会使用索引的 - 需要注意的是，把以上两条语句利用子查询组合成一条语句，mysql 会报
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'的错误（亲测mysql 5.7和8.0均会报这个错误）
1 | select * from person_tab where id in (select id from person_tab where age = 10 limit 40000, 10); |
- 使用上一页最后一条数据的某一个或某一组能唯一标识数据的字段的值作为查询下一页的过滤条件进行查询这种方法主要是利用唯一标识数据的字段作为过滤条件，利用索引过滤掉前面已经筛选过的数据，减少回表或数据遍历
1
select * from person_tab where age = 10 and id > last_id order by id asc limit 10;
像上面的例子，我们选取主键id作为查询下一页的过滤条件
针对上面的语句，我们分场景的去讨论一下
- 没有关于 age 字段作为前缀的索引的情况
这种情况，mysql 会走一级(主键)索引进行检索，mysql 会利用一级索引找到 id>last_id 的最小id所在的叶子节点，然后从大于 last_id 的最小id的位置开始按顺序的扫描数据，当找到 limit 个 age=10 的数据就结束- 这种情况，假设 age = 10 的数据占表的总数据 1/n，而且分布比较均匀，那么平均一次查询的扫描数据行数是 n*limit
- 有 index_age(age) 索引的情况
这种情况，mysql 如果是选择走 index_age 索引，mysql 会利用 index_age 索引，找到第一个满足 age = 10 的叶子结点，然后开始按顺序的扫描二级索引的叶子结点，利用索引下推，过滤掉 id <= last_id 的 id，扫描出 limit 个 id > last_id 的最小的id，再根据id回表查询数据- 这种情况，需要扫描二级索引叶子节点上的数据个数为 n + limit，n 为满足 age=10 and id <= last_id 的记录数，需要回表查询 limit 个数据
- 有 index_age_id(age, id) 索引的情况
这种情况，mysql 如果是选择走 index_age_id 索引，mysql 会利用 index_age_id 索引，找到第一个满足 age = 10 且 id > last_id 的叶子结点，然后开始按顺序的扫描二级索引的叶子节点，扫描出 limit 个满足条件的 id，- 这种情况下，需要扫描二级索引叶子节点上的数据个数也是 limit， 需要回表查询 limit 个数据
- 有 index_age_xx(age, xx) 索引的情况
- 使用
order by id asc
这种情况，mysql 如果是选择走 index_age_xx 索引，mysql 会利用 index_age_xx 索引，找到第一个满足 age = 10 的叶子节点，然后开始按顺序的扫描二级索引的叶子结点，把所有满足 age = 10 的 index_age_xx 索引数据查出来，然后再使用文件排序按照id升序进行排序，之后再根据排序的结果找到 id>last_id 最小的 limit 个 id，再根据 id 回表查询数据- 这种情况，每次都需要在二级索引上扫描所有的满足条件的数据，然后再进行文件排序，需要回表查询 limit 个数据
- 不使用
order by id asc
这种情况，mysql 如果是选择走 index_age_xx 索引，mysql会利用 index_age_xx 索引，找到第一个满足 age = 10 的叶子结点，然后开始按顺序的扫描二级索引的叶子结点，利用索引下推，过滤掉 id <= last_id 的 id，扫描出 limit 个 id > last_id 的最小的 id，再根据 id 回表查询数据- 这种情况，需要扫描二级索引叶子节点上的数据个数是 limit，需要回表查询 limit 个数据
- 但是这种情况下，不能完全扫描出所有满足要求的数据，因为 index_age_xx 这个二级索引的叶子节点的数据是先按照 age 排序，age 相同再按照 xx 字段排序，xx 字段相同再按照id排序，mysql 执行
select * from person_tab force index (index_age_xx) where age = 10 limit 10;这个语句返回的结果是按照 index_age_xx 索引的排序进行返回的，返回的结果不是按照 id 排序的，所以如果使用 id>last_id 进行过滤的话，会过滤掉一些没有被查询过的满足 age=10 的数据。如下表的例子，如果是以select * from person_tab force index (index_age_xx) where age = 10 and id > last_id limit 2;进行扫描数据，第一次扫描数据 last_id=0，返回 id 为 100 和 102 的数据，第二次扫描数 据last_id=102，返回 id 为 104 的数据，然后结束，id为 87、90、40 的数据就不会被扫描出来
- 使用
| id | age | xx |
|---|---|---|
| 100 | 10 | 0 |
| 102 | 10 | 0 |
| 104 | 10 | 0 |
| 87 | 10 | 1 |
| 90 | 10 | 1 |
| 40 | 10 | 21 |
通过设定过滤条件 id > last_id 来过滤数据，隐含的条件就是返回的数据是按照 id 升序进行排序的，因此需要加上 order by id asc，这个不是为了性能提升，是为了保证能够扫描出所有满足条件的数据，是了保证语义，告诉 mysql 我们的意图就是需要按照 id 升序来返回数据，因为在某些场景下，我们不加 order by id asc，mysql 就不会按照 id 升序来返回数据。
可以再举一个例子
1 | select id from person_tab where id > last_id limit 100; |
使用以上的语句进行遍历扫描数据，是有可能扫描不到所有的数据的，原因是 mysql 可能会只使用某个二级索引来查询数据返回(因为只需要返回 id，使用二级索引性能会更高)，返回的数据不是按照 id 升序的返回的
1 | select id from person_tab where id > last_id order by id asc limit 100; |
加上 order by id asc 之后，mysql 会选择使用一级索引来查询数据进行返回，因为使用一级索引性能更高(一级索引就是按照 id 排序的，使用二级索引的话需要进行文件排序)
总结
- 没有合适的二级索引(使用一级索引)的情况下，第二种方法(id > last_id order by id asc)在非极端情况下比第一种方法(id in (xxx,xx,xx))比要好
- 有对应合适的二级索引的情况下，第一种方法比第二种方法要好
- 有针对性优化的二级索引(index_age_id)的情况下，第二种方式比第一种方法要好