MYSQL LIMIT 用法详解
在处理包含大量记录的数据库查询时，一次性查询所有结果可能非常耗时，尤其随着数据量的增加，查询效率会显著下降。因此，分页查询成为了一种常见的优化策略。本文将介绍几种常见的分页查询方法及其优化策略。
准备工作
为了测试这些优化方法，以下是针对一张现有表的基本信息：
- 表名:
order_history - 描述: 某个业务的订单历史表
- 主要字段:
unsigned int id,tinyint(4) int type - 字段情况: 表中共37个字段，不包含
text等大型数据，最大字段为varchar(500)。id字段为递增索引。 - 数据量: 5,709,294 条记录
- MySQL 版本: 5.7.16
以下是基本测试结果：
select count(*) from orders_history;
- 返回结果: 5,709,294 条记录
- 三次查询时间: 8903 ms, 8323 ms, 8401 ms
一般分页查询
一般的分页查询可以通过 LIMIT 子句实现。其语法如下：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset;
注意事项：
- 第一个参数指定返回记录的偏移量（从 0 开始）。
- 第二个参数指定返回的最大记录数。
- 如果只给定一个参数，则表示返回最多的记录数。
- 第二个参数为
-1表示从偏移量到记录集结束的所有记录。 - 初始记录行的偏移量是从 0 开始的，而不是 1。
示例：
select * from orders_history where type=8 limit 1000,10;
该语句将会从表 orders_history 中查询从偏移量 1000 开始的 10 条数据，也就是第 1001 条到第 1010 条数据。
- 三次查询时间: 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 条记录: 3072 ms, 3092 ms, 3002 ms
- 查询 10 条记录: 3081 ms, 3077 ms, 3032 ms
- 查询 100 条记录: 3118 ms, 3200 ms, 3128 ms
- 查询 1000 条记录: 3412 ms, 3468 ms, 3394 ms
- 查询 10000 条记录: 3749 ms, 3802 ms, 3696 ms
随着查询记录量的增加，查询时间也逐渐增加。
使用子查询优化
这种方式通过先定位偏移位置的 id，然后再进行查询，适用于 id 递增的情况。
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;
- 查询时间:
- 第 1 条语句：3674 ms
- 第 2 条语句：1315 ms
- 第 3 条语句：1327 ms
这种方式相较于原始的查询方法，查询速度提高了数倍。
使用 ID 限定优化
假设数据表的 id 是连续递增的，可以使用 id between 来优化查询：
select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;
- 查询时间: 15 ms, 12 ms, 9 ms
这种方式大幅提高了查询速度，尤其适用于明确知道 id 范围的情况。
使用临时表优化
对于某些特殊场景，如使用历史表或数据缺失的情况，可以考虑使用临时表来记录分页的 id，并利用这些 id 进行查询。这种方法在数据量极大的情况下，能显著提高分页查询的效率。
关于数据表的 ID 说明
一般情况下，在数据库中创建表时，强制为每张表添加递增的 id 字段，这样方便查询。当数据量极大时，建议使用分布式唯一 ID 生成器，而不是直接使用数据库的 id 字段。
总结
通过合理使用 LIMIT、子查询、ID 限定和临时表等优化手段，能大幅提高 MySQL 数据库在大数据量环境下的查询效率。