网站建设的原则和目标建站为应用技术

最近看到一道面试题，如何优化深分页查询
最简单的例子是

select  * from web_bill_main
limit 30000,10;

分页达到30000行，需要把前面29999行都过滤掉，才能找到这10条数据
所以整体时间花了80ms(工具显示时间)

我当时的第一反应是，使用in

select * from web_bill_main 
where id in 
(select id from web_bill_main limit 30000,10)

很遗憾，报错了

然后想到使用left join

select w.* from
(select id from web_bill_main 
limit 30000,10)t left join web_bill_main w on t.id = w.id

速度提升至15ms

正当我洋洋得意时，发现问题的不对劲，速度是提升了，但是返回数据不一样了

此时我发现，以下两个sql返回数据的id不一致

select id from web_bill_main
select  * from web_bill_main

select * from web_bill_main查询到的是根据ID进行排序的数据
select id from web_bill_main查询出来的ID并不有序

百思不得其解，使用explain分析
explain select * from web_bill_main
分析如下，全表查，所以id有序

explain select id from web_bill_main
分析如下，没有使用id索引，而是使用了idx_modify_time，mysql除了主键索引，都是联合索引，利用了回表

针对使用idx_modify_time的理解是，id是聚簇索引，如果遍历ID索引的结果就是，会遍历所有数据，所以使用idx_modify_time索引的覆盖索引机制，而idx_modify_time的顺序和id不一致，所以出现查询到的结果不一

最终，改成如下sql，数据与原数据一致

select w.* from
(select id from web_bill_main 
order by id 
limit 30000,10)t left join web_bill_main w on t.id = w.id

耗时29ms