MySQL-分页查询优化笔记

MySql 分页查询优化

limit <offset>, <size>
-   offset: 返回结果第一行的偏移量(想要跳过多少行)
-   size: 指定返回多少条

查询第一页与300000页对比
explain select * from table limit 1, 10; // 执行时间 82ms
explain select * from table limit 300000, 10; // 执行时间 168ms

方案1: 覆盖索引

select id from table limit 300000, 10; 
-- 执行时间 106ms	

方案2: 覆盖索引+join(常用方式)

select * from table t inner join
    (select id from table limit 300000, 10) ti
on t.id = ti.id
-- 两种SQL写法方式不同,using等同于join写法
select * from table t inner join
    (select id from table limit 300000, 10) ti
using(id)
-- 执行时间 107ms

(on t.id = ti.id 因为是同一张表,属性名一致,可以简写为 using(id) )

方案3: 覆盖索引+子查询(常用方式)

select * from table 
where id >=
    (select id from table limit 300000, 1)
limit 10;
-- 执行时间 126ms

此方案的思路是先找到第300000条数据,再往后取10条数据

方案4: 范围查询+limit语句

select * from table 
where id > 10010
limit 10;

此方案需要事先知道上一页最大ID,在根据上次执行的最大ID做范围取值,优点是每次都扫描的行数永远都是10行.

方案5: 如果能够获得起始主键值和结束主键值

select * from table 
where id between 20000
and 20010;

方案6: 禁止传入过大的页码