本文共 1741 字，大约阅读时间需要 5 分钟。

MySQL 大数据量分页查询方法及其优化

查询方法

在处理大数据量的分页查询时，选择合适的方法至关重要。以下是几种常见的分页方法及其适用场景：

方法1：直接使用数据库提供的SQL语句

适用于数据量较少的情况（元组百/千级）

SELECT * FROM 表名称 LIMIT M,N

缺点：全表扫描，速度较慢，且结果集返回不稳定。

方法2：建立主键或唯一索引，利用索引（假设每页10条）

适用于数据量多的情况（元组数上万）

SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) LIMIT M

注意：由于数据未按id_pk排序，可能会导致数据遗漏，必须结合方法3使用。

方法3：基于索引再排序

适用于数据量多的情况（元组数上万）

SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

优点：索引扫描加速，结果集稳定。

方法4：基于索引使用prepare

适用于大数据量

PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M

方法5：利用MySQL支持的ORDER操作

快速定位部分元组，避免全表扫描

SELECT * FROM your_table WHERE pk > 1000 ORDER BY pk ASC LIMIT 20

方法6：利用子查询或连接

快速定位元组的位置，再读取元组

SELECT * FROM your_table WHERE id < (SELECT id FROM your_table ORDER BY id DESC LIMIT (pageNum-1)*pageSize ORDER BY id DESC LIMIT pageSize)

SELECT * FROM your_table AS t1 JOIN (SELECT id FROM your_table ORDER BY id DESC LIMIT (pageNum-1)*pageSize AS t2 WHERE t1.id <= t2.id ORDER BY t1.id DESC LIMIT pageSize) t2

测试实验

1. 直接用LIMIT分页问题的性能测试

测试结果：

• 起始记录为10，100，1000，10000时，查询时间分别为0.016秒、0.016秒、0.047秒、0.094秒。
• 起始记录为40万时，查询时间为3.229秒。
• 最后一页（86万）查询时间为37.44秒。

结论：

• LIMIT语句的查询时间与起始记录位置成正比。
• 对大数据量表不适合直接使用LIMIT分页。

2. 对LIMIT分页问题的性能优化方法

方法：利用覆盖索引加速分页查询

• 主键索引优化：

SELECT id FROM product LIMIT 866613, 20

时间为0.2秒，相比全表扫描提升100多倍。

• 适用方法：

SELECT * FROM product WHERE id > (SELECT id FROM product LIMIT 866613, 1) LIMIT 20

时间为0.2秒。

3. 复合索引优化方法

示例：单独使用主键索引优化查询

SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000,10

时间为8-9秒。

优化建议：使用复合索引

• 建立覆盖索引：

SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000,10

时间为0.04秒。

• 尽量只查询主键：

SELECT id FROM collect WHERE vtype=1 LIMIT 90000,10

时间为0.5秒。

结论：合理设计索引可显著提升性能，但需综合考虑查询需求和索引设计。

总结

通过合理的索引设计和查询优化，可以显著提升MySQL大数据量分页查询的性能。选择合适的分页方法和优化策略，是提升数据库性能的关键。