MySQL 优化分页查询

MySQL 优化分页查询

以 t_sys_log 表为例，3774851条数据，占用空间1.01G，24个字段

SELECT * FROM `t_sys_log` order by create_time desc limit 100000,10

耗时 88.345秒

SELECT id FROM `t_sys_log` order by create_time desc limit 100000,10

耗时 0.336秒

所以联表查询

SELECT * FROM `t_sys_log` t1 JOIN
(SELECT id FROM `t_sys_log` order by create_time desc limit 100000,10) t2
ON t1.id = t2.id

耗时 0.453秒

偏移量更大一点

SELECT * FROM `t_sys_log` order by create_time desc limit 1000000,10

耗时 12.687秒

SELECT * FROM `t_sys_log` t1 JOIN
(SELECT id FROM `t_sys_log` order by create_time desc limit 1000000,10) t2
ON t1.id = t2.id

耗时 0.617秒