Mysql大表分页查询时limit优化

Mysql大表分页查询时limit优化

说明：MySQL版本是5.7，使用的表引擎是InnoDB，表有三千多万的数据，id为主键自增，数据大小是1.8G，索引大小是1.2G，合计3G

先看一个SQL

select * from gd_visit 
where create_time between 
'2015-01-01 00:00:00' and '2022-08-30 00:00:00'
limit 5000000,50;

花费时间为30秒左右

虽然create_time字段使用了索引，但是由于limit是从结果集中取出偏移量之后的记录数，上面的SQL，需要进行（500w+50）次回表才能取出50条数据,前面的500w次回表根本不需要，完全是浪费时间和性能，故优化就是减少前面的500w次回表，只要50次回表拿数据就可以了。

优化后的SQL

select * from gd_visit a join 
(select id from gd_visit where create_time between 
'2015-01-01 00:00:00' and '2022-08-30 00:00:00'
 limit 5000000,50) b on a.id = b.id;

花费的时间为3.3秒左右

优化后的SQL，性能显著提升，第一次查询的时候只select id 会导致索引覆盖，所以不需要回表，能快速的拿到主键id；第二次查的时候只需要把这50个id回表去取数据就好。