使用索引解决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。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>