SQL分页优化

Posted on | Post modified: | In |
Words count in article: 420

一般分页查询

一般的分页查询通过limit子句来实现，语法如下

1
2
SELECT * FROM table LIMIT [offset, ] rows 
SELECT * FROM table LIMIT rows OFFSET offset

随着查询记录数量rows 的增多，查询速度会越来越慢，但是它带来的影响没有查询偏移量offset导致的变慢来得剧烈。

因为这种分页查询会从第一条记录开始扫描（没有用到索引），所以偏移量越大，速度越慢。

子查询优化

如果含有递增id的话，可以通过先定位分页id的偏移，然后往后查询。

1
2
3
4
5
6
7
8
9
10
11
select * from orders_history where type=8 limit 100000,1;

-- 如果只需要查一行一列的话，直接使用这种查询方式
select id from orders_history where type=8 limit 100000,1;

select * from orders_history where type=8 limit 100000,100;

-- 当需要查多行，可将该优化查询作为子查询
select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;

id限定优化

如果id是 【连续递增】的话，可以直接使用id来确定范围。

1
2
select * from orders_history where type=2 
and id between 1000000 and 1000100 limit 100;

或者

1
select * from orders_history where id >= 1000001 limit 100;

用其他表中确定的id号来索引查找效率也不低。

1
2
3
select * from orders_history where id in
(select order_id from trade_2 where goods = 'pen')  -- 其它表中确定的id号
limit 100;

它的原理就是先通过查找具有索引的id，然后通过索引定位数据。（select id，再select *）。

上面做法要求id是连续自增的，但是数据很有可能会出现缺失，这时可用临时表记录分页的id信息，进行in查询，可极大提高速度。