【MySQL优化】分页查询优化

当需要从数据库查询的表有上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时需要使用分页查询。

一、分页查询真的一直快吗？

1.1 一般分页查询

select * from table_name limit start, count

复制

MySQL
的分页语句，作为开发，再熟悉不过了吧，需注意以下几点：

1. 第一个参数指定第一个返回记录行的偏移量(从0
   开始)；
2. 第二个参数指定返回记录行的最大数目；
3. 如果只给定一个参数，表示返回最大的记录行数目；
4. 第二个参数为 -1
   表示检索从某一个偏移量到记录集的结束所有的记录行 初始记录行的偏移量是 0
   (而不是 1
   )。

• 例如

select * from orders_history where type=8 limit 1000,10;

复制

该语句将会从表 orders_history
中查询偏移量从 1000
开始之后的 10
条数据，也就是第1001
条到第1010
条数据。

1.2 偏移量测试

• 查询记录数递增

select * from orders_history where type=8 limit 10000,1;
select * from orders_history where type=8 limit 10000,10;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 10000,1000;
select * from orders_history where type=8 limit 10000,10000;

复制

• 查询偏移量递增

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

复制

1. 从查询时间来看，基本可以确定，在偏移量不大时，在查询记录量增大时时，查询时间基本没有差距；
2. 但随着查询偏移量越来越大，所花费的时间增加特别明显。

传统分页查询方式会从数据库第一条记录开始扫描，所以偏移量越大，查询速度越慢。

二、分页优化-使用子查询优化

2.1 基本思路

这种方式适用于 id
递增的情况。

先定位偏移位置的 id
，然后往后查询。

2.2 示例

• 先查询第 10w
  条数据的位置

select id from orders_history where type=8 limit 100000,1;

复制

• 然后取 ID
  值作起始标识定位下 100
  条记录

select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;

复制

• 对比下原分页语句

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

复制

这种方式相较于原始一般的查询方法，将会增快数倍。

2.3 关于数据表的id说明

一般情况下，在数据库中建立表的时候，强制为每一张表添加 id
递增字段，这样方便查询。

如果像是订单库等数据量非常庞大，一般会进行分库分表。这个时候不建议使用数据库的 id
作为唯一标识，而应该使用分布式的高并发唯一 id
生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

使用先使用范围查询定位 id
（或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id
，然后再 select *
。

三、总结

在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的 id
，使用分页的 id
来进行 in
查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

参考文章