数据库Limit语句优化

 

-- 查询第一页(前10条)，花费102ms
select * from employees limit 0,10;

-- 查询300001~300010，花费230ms
select * from employees limit 300000,10;

explain select * from employees limit 300000,10; -- type=ALL 全表扫描

 

如何优化

方案1： 覆盖索引

select emp_no from employees limit 300000,10; -- 98ms
explain select emp_no from employees limit 300000,10; -- type = Index

 

方案2： 覆盖索引 + join

select * from employees e inner join
( select emp_no from employees limit 300000,10) t on e.emp_no = t.emp_no; -- 122 ms

 

方案3： 覆盖索引+子查询

select * from employees where emp_no >=
(select emp_no from employees limit 300000,1)
limit 10

花费126ms

 

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

需要知道前面10行最大的Id数

select emp_no from employees limit 299990,10 -- 最大值为472225

select * from employees where emp_no > 472225 limit 10 -- 52ms

 

方案5   能获得起始主键值  &   结束主键值

select * from employees where  emp_no between  20000 and 20010;

将分页查询变成了范围查询。

 

方案6：

禁止传入过大的页码。如最多只能传入100页，之后的页面将不支持。(业务妥协)

 

方案2和方案3是limit优化的常用方式。如果指定范围，起始主键，可以使用方案4和方案5.