MySql深度分页慢sql原因

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

比如查询语句如下：

select * from user order by  createTime limit 500000, 10;

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

select * from user u,(select id from user order by createTime  limit 500000, 10) t where u.id=t.id;

 详细原理如下：

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

select * from user order by createTime  limit 500000 , 10;

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

explain 如下：

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

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

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

怎么解决 Using filesort 呢？答案是给order by 后面列使用索引; 

select * from user order by  id limit 500000, 10;  --270ms

 延迟关联：

延迟关联就是指先拿到主键 id，然后再根据 id 查询 select *

select id from user order by id limit 500000, 10;

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

合并在一个 sql 语句中：

select * from user u,(select id from user order by id limit 500000, 10)  t where u.id=t.id; --83ms

索引覆盖:

mysql B+树索引：

  mysql 的主键索引叶字节点存的是主键所对应行的整行的全量数据

使用索引覆盖后查询:

select * from user order by id limit 500000, 10;  --305ms  all

select id from user order by id limit 500000, 10;  --99ms   index

重排序查询:

select * from user order by id limit 500000, 10;  --305ms  all

select * from user order by createTime limit 500000, 10;  --999ms  all filesort

 可以发现在50w条数据时, 即使是全表扫描, 0.3s, 索引覆盖0.1s

如果使用非索引字段排序, 则0.9s

总结: 

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