在进行分页时，一般通过创建覆盖索引，能够比较好的提高性能。

一个非常常见，而又非常头疼的分页场景就是"limit 600000,10"。

mysql使用select * limit offset, rows分页在深度分页的情况下性能急剧下降。

例如：select * 的情况下直接⽤limit 600000，10 全表扫描的是约60万条数据。

原因是MySQL在回表搜索出前600010 条记录后，仅仅需要返回第 600001 到 600010 条的10条记录。

到头来只⽤到10条数据(总共取600010条数据只留10条记录)即前600000 记录会被抛弃，查询代价非常大。

当点击比较靠后的页码时，就会出现这个问题，查询效率非常慢。

优化:先排序再回表

SQL：

select * from operation_log  where user_name = 'bob' limit 600000,10;

此时的执行流程是:先走二级索引 user_name需要先查询出主键ID, 通过主键ID查询出前6000010条数据的所有字段,然后默认以id排序取出最后10行数据,其余60w数据全部抛弃。

成本:www.taodudu.cc/news/show-4…

将上述SQL优化为 :

select * from operation_log t , 
    (select id from operation_log where create_time >= '2023-05-09 12:20:20' order by id limit 600000,10) b 
where t.id = b.id ;

此时的执行流程为:先根据二级索引create_time通过索引下推获取id排序,取出 600001 到 600010 条的10条记录的id,然后执行回表查询10条记录的完整数据返回。虽然也扫描了60w数据,但是因为走的是主键索引,所以速度会很快。

变种:其实是把连接查询改成了in查询,从理论上说join查询性能大于in查询。

select * from operation_log where id in ( 
select id from 
   (select id from operation_log where create_time>'2023-05-09 12:20:20' limit 600000,10 ) as t
);        

变种:外连接查询变内连接查询

select * from operation_log inner join ( select id from operation_log where create_time >= '2023-05-09 12:20:20' limit 10000,10) b using (id)

优化:记录上次分页的最大id,根据最大id过滤

select * from table_name Where id > 最大id limit 10000, 10;

这种方法适用于：除了主键ID等离散型字段外，也适用连续型字段datetime等。

1.最大id由上一次分页的数据获取,并且前提是id全局递增有序。 2.只能连续页查询，不能跨页查询,这个限制从需求上可以避免限制成只能一页一页往后划的场景