关于数据库:大数据量下的分页查询优化

前言

当须要从数据库查问的表有上万条记录的时候，一次性查问所有后果会变得很慢，特地是随着数据量的减少特地显著，这时须要应用分页查问。对于数据库分页查问，也有很多种办法和优化的点。上面简略说一下我晓得的一些办法。

筹备工作

为了对上面列举的一些优化进行测试，上面针对已有的一张表进行阐明。

• 表名：order_history
• 形容：某个业务的订单历史表
• 次要字段：unsigned int id，tinyint(4) int type
• 字段状况：该表一共 37 个字段，不蕴含 text 等大型数据，最大为 varchar(500)，id 字段为索引，且为递增。
• 数据量：5709294
• MySQL 版本：5.7.16

线下找一张百万级的测试表可不容易，如果须要本人测试的话，能够写 shell 脚本什么的插入数据进行测试。

以下的 sql 所有语句执行的环境没有产生扭转，上面是根本测试后果：

select count(*) from orders_history;

返回后果：5709294

三次查问工夫别离为：

• 8903 ms
• 8323 ms
• 8401 ms

个别分页查问

个别的分页查问应用简略的 limit 子句就能够实现。limit 子句申明如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句能够被用于指定 SELECT 语句返回的记录数。需注意以下几点：

• 第一个参数指定第一个返回记录行的偏移量，留神从 0 开始
• 第二个参数指定返回记录行的最大数目
• 如果只给定一个参数：它示意返回最大的记录行数目
• 第二个参数为 -1 示意检索从某一个偏移量到记录集的完结所有的记录行
• 初始记录行的偏移量是 0(而不是 1)

上面是一个利用实例：

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

该条语句将会从表 orders_history 中查问 offset: 1000 开始之后的 10 条数据，也就是第 1001 条到第 1010 条数据（1001 <= id <= 1010）。

数据表中的记录默认应用主键（个别为 id）排序，下面的后果相当于：

select * from orders_history where type=8 order by id limit 10000,10;

三次查问工夫别离为：

• 3040 ms
• 3063 ms
• 3018 ms

针对这种查问形式，上面测试查问记录量对工夫的影响：

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;

三次查问工夫如下：

• 查问 1 条记录：3072ms 3092ms 3002ms
• 查问 10 条记录：3081ms 3077ms 3032ms
• 查问 100 条记录：3118ms 3200ms 3128ms
• 查问 1000 条记录：3412ms 3468ms 3394ms
• 查问 10000 条记录：3749ms 3802ms 3696ms

另外我还做了十来次查问，从查问工夫来看，根本能够确定，在查问记录量低于 100 时，查问工夫根本没有差距，随着查问记录量越来越大，所破费的工夫也会越来越多。

针对查问偏移量的测试：

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;

三次查问工夫如下：

• 查问 100 偏移：25ms 24ms 24ms
• 查问 1000 偏移：78ms 76ms 77ms
• 查问 10000 偏移：3092ms 3212ms 3128ms
• 查问 100000 偏移：3878ms 3812ms 3798ms
• 查问 1000000 偏移：14608ms 14062ms 14700ms

随着查问偏移的增大，尤其查问偏移大于 10 万当前，查问工夫急剧减少。

这种分页查问形式会从数据库第一条记录开始扫描，所以越往后，查问速度越慢，而且查问的数据越多，也会拖慢总查问速度。

应用子查问优化

这种形式先定位偏移地位的 id，而后往后查问，这种形式实用于 id 递增的状况。

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 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;

4 条语句的查问工夫如下：

• 第 1 条语句：3674ms
• 第 2 条语句：1315ms
• 第 3 条语句：1327ms
• 第 4 条语句：3710ms

针对下面的查问须要留神：

• 比拟第 1 条语句和第 2 条语句：应用 select id 代替 select * 速度减少了 3 倍
• 比拟第 2 条语句和第 3 条语句：速度相差几十毫秒
• 比拟第 3 条语句和第 4 条语句：得益于 select id 速度减少，第 3 条语句查问速度减少了 3 倍

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

应用 id 限定优化

这种形式假如数据表的 id 是 间断递增 的，则咱们依据查问的页数和查问的记录数能够算出查问的 id 的范畴，能够应用 id between and 来查问：

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

查问工夫：15ms 12ms 9ms

这种查问形式可能极大地优化查问速度，根本可能在几十毫秒之内实现。限度是只能应用于明确晓得 id 的状况，不过个别建设表的时候，都会增加根本的 id 字段，这为分页查问带来很多便当。

还能够有另外一种写法：

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

当然还能够应用 in 的形式来进行查问，这种形式常常用在多表关联的时候进行查问，应用其余表查问的 id 汇合，来进行查问：

select * from orders_history where id in
(select order_id from trade_2 where goods = 'pen')
limit 100;

这种 in 查问的形式要留神：某些 mysql 版本不反对在 in 子句中应用 limit。

应用长期表优化

这种形式曾经不属于查问优化，这儿附带提一下。

对于应用 id 限定优化中的问题，须要 id 是间断递增的，然而在一些场景下，比方应用历史表的时候，或者呈现过数据缺失问题时，能够思考应用长期存储的表来记录分页的 id，应用分页的 id 来进行 in 查问。这样可能极大的进步传统的分页查问速度，尤其是数据量上千万的时候。

对于数据表的 id 阐明

个别状况下，在数据库中建设表的时候，强制为每一张表增加 id 递增字段，这样不便查问。

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

应用先应用范畴查问定位 id（或者索引），而后再应用索引进行定位数据，可能进步好几倍查问速度。即先 select id，而后再 select *。