mysql使用limit分页优化方案

准备数据是20000000条数据

在分页场景下，使用limit start end，我们分别看下从10000， 100000， 1000000开始分页的执行时间（每页取10条），如下图

当start较小时，查询没有性能问题，但是如上图查询时间所示，随着start增大，查询消耗时间也在递增，在start=10000000时，分页竟然消耗了2秒多，这是不能忍受的。

由此引出对limit分页的优化,首先来explain该语句，看到查询没有使用到任何的索引，进行的是全表扫描，假如limit分页用到了索引是不是会快很多呢！

explain分析一下,第一行是select * from user_innodb形成的临时表使用的是全表扫描，第二行是 （SELECT id FROM user_innodb LIMIT 10000000, 10）形成的，使用的是eq_ref，第三行是全表扫描a和bjoin形成的派生表，使用到的是index，所以速度也会快很多

mysql 5.7.28

按id增序 导出t_order_detail表数据，由于数据量过多，防止一次查询数据量大多导致异常，批量查询数据，每次查询200条数据，数据量50万，查询出的数据量5万多条。

SQL如下

Explain结果

《高性能MySql第三版》章节6.7.5 优化Limit分页中提到，在偏移量非常大的时候，例如可能是LIMIT 1000,20 这样的查询，这时候MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。要优化此种查询，要么在页面中限制分页数量，要么是优化大偏移量的性能。使用“延迟关联”，它让MySQL扫描尽可能少的页面，获取需要要访问的记录后再根据关联列回原表查询需要的所有列。

Explain结果

也没看不出来区别，直接用SQL执行看消耗的时间

这个延迟关联蛮简单的(自我感觉)，为啥MySQL不直接内部实现优化呢？

延迟关联到底节省了哪部分动作消耗的时间，如果只是如下的SQL,那就根本没必要关联，在查询了其他的字段后，才需要延迟关联。所以是节省了获取其他字段的消耗的时间？还是排序时多个字段后更加耗时？

当前SQL使用id排序，可以直接使用上一页数据最后一条数据的Id做筛选，这样直接筛选出需要的数据，查询查第49999条数据的order_id为707352，SQL如下

Explain结果

此种优化方法要求 使用唯一的字段排序。

高性能MySql

MySQL ORDER BY _ LIMIT performance_ late row lookups at EXPLAIN EXTENDED

SELECT * FROM table_name LIMIT [offset ,] rows

优化前：

原理：mysql会先查询出10000010（一千万零一十）条数据，然后丢弃前10000000（一千万）条数据，返回最后10（十）条数据，所以偏移量越大，性能就越差。

方法一、

使用 主键索引 进行关联查询

方法二、

每次记录当前页的 最后一条id ，作为下一页的查询条件

一、正常情况下没有人会翻到几千页，我们可以通过限制可以翻页的数量解决这个问题，如：百度、谷歌。

二、手机端可以使用下拉方式进行滚动翻页，每次记录当前页的最后一条id，作为下一页的查询条件。ES可以使用scroll API

---