使用索引解决mysql深度分页问题

先说解决方案：覆盖索引+延迟关联。

比如查询语句如下：

select * from demo order by a ASC, b ASC, c ASC limit 1000000, 10;

给 id 列建主键，给(a,b,c)建联合索引：

create index idx_a_b_c on demo(a,b,c);

然后延迟关联，即先查主键 id，然后根据 id 查其它字段：

select a,b,c from demo,(select id from demo order by a ASC, b ASC, c ASC limit 1000000, 10) t where demo.id=t.id;

详细原理如下：

随着 mysql limit 的值越来越大，查询可能会非常慢，比如：

select * from demo order by a ASC, b ASC, c ASC limit 1000000, 10;

慢的真正原因，不是因为扫描了 1000000 行，而在于把这 1000000 万行数据重新排序。

explain 如下：

可以看到 Extra 是 Using filesort，表示外部排序，

索引有两个功能：查找和排序。

大家一般对索引的查找功能比较了解，却忽视了索引的排序功能，索引中的数据是已经排好序的，如果从索引中拿到的数据顺序跟我们需要排序的顺序是一致的，那就不要重新排序了。

怎么解决 Using filesort 呢？答案是给(a,b,c)这三列建联合索引：

create index idx_a_b_c on demo(a,b,c);

创建完索引之后，索引中的数据是按 a b c 这三列排序的，就是优先按 a 排序，当 a 相同时再按 b 排序，b 也相同就按 c 排序，大致如下：

 执行 explain 现仍然是 Using filesort，原因是查询的列是*，包括了所有列，不能在索引中直接拿到最终查询数据，还需要回表查询，实现不了索引覆盖。

延迟关联：

延迟关联就是指先拿到主键 id，然后再根据 id 查询 select *。根据主键查找差不多能达到二分查找的速度，所以非常快。

给 id 列创建主键，然后 select id：

select id from demo order by a ASC, b ASC, c ASC limit 1000000, 10;

Extra 的中 Using index 就表示“覆盖索引”，表示整个查询过程仅读取了索引中的数据而没有回表查询。

合并在一个 sql 语句中：

select a,b,c from demo,(select id from demo order by a ASC, b ASC, c ASC limit 1000000, 10) t where demo.id=t.id;

mysql B+树索引：

主键索引和辅助索引（二级索引）：

 mysql 的主键索引叶字节点存的是主键所对应行的整行的全量数据，辅助索引(除主键索引以外的索引)叶字节点上存的是主键的值。

由于前面已经给(a,b,c)三列建了联合索引，所以只要select后面的字段在 a,b,c,id这4列中，就可以直接在索引中拿到最终查询结果。由于联合索引是按(a,b,c) 这3列按优先级从高到低顺序排序的，所以 索引中的数据已经按 order by a ASC, b ASC, c ASC 排好序了，拿出来直接使用，不用再外部排序。

(a,b,c)三列的联合索引的叶子节点上是主键的值，即id，并且当不同行的a、b、c相等时，id是有序的；即(a,b,c)的联合索引可以认为是最右侧为主键的联合索引：(a,b,c,id)。

如果 order by 的字段不是按联合索引的列最左匹配的，比如跳过了a b 直接对 c 排序，因为索引中 c 列是无序的，所以需要外部重新排序。

select id from demo order by c ASC limit 1000000, 10;

查找的结果集，可以不是索引中连续的结果集(结果集为 16 17 18 25 26 27)，只要最终顺序和索引中的顺序一致，也可以实现覆盖索引，不用重排序：

对于索引的查找功能，最左匹配是指 where 条件中 and 的列；

对于索引的排序功能，最左匹配是指 order by 中的列；

总之，如果要用索引中现成的排序，order by 的字段必须按索引的顺序从左到右，中间不能中断(除非该列在where中指定了值，比如where a=1，则a可以不用参与排序)，并且必须所有字段全升序或者全降序(叶子节点的双向链表可以正向/反向读取)。

 由于 B+树是用双向链表将所有叶子节点串起来的，所以索引的范围查找会非常快，也就是说，只要走了 覆盖索引，limit 就算扫描过了 100 万条数据，耗时也会非常短。

100万条数据的扫描和排序，只需要 0.3 秒:

总结：

mysql深度分页问题的根因，不是因为扫描了大量数据，而是大量数据的重新排序太耗时，只要不重排序，就算扫描了大量数据，也不会有性能问题。

要使 深度分页 达到最优性能，explain 的 Extra 中一定不能有 Using filesort，一定要有 Using index。