如何实现MySQL数据库的分页查询方法？

MySQL分页方法解析

一、直接使用LIMIT子句

1 适用场景

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

2 语句样式

SELECT * FROM table_name LIMIT M, N;

3 原因/缺点

全表扫描：速度会很慢，且有的数据库结果集返回不稳定（如某次返回1,2,3,另外的一次返回2,1,3）

Limit限制：从结果集的M位置处取出N条输出，其余抛弃

二、建立主键或唯一索引，利用索引

1 适用场景

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

2 语句样式

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

3 原因

索引扫描：速度会很快，有朋友提出因为数据查询出来并不是按照pk_id排序的，所以会有漏掉数据的情况，只能方法3。

三、基于索引再排序

1 适用场景

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

2 语句样式

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

3 原因

索引扫描：速度会很快，但MySQL的排序操作，只有ASC没有DESC（DESC是假的，未来会做真正的DESC，期待）。

四、基于索引使用prepare

1 适用场景

大数据量。

2 语句样式

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

3 原因

索引扫描：速度会很快，Prepare语句又比一般的查询语句快一点。

五、利用MySQL支持ORDER操作可以利用索引快速定位部分元组,避免全表扫描

1 适用场景

读第1000到1019行元组（pk是主键/唯一键）

2 语句样式

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20;

六、利用"子查询/连接+索引"快速定位元组的位置,然后再读取元组

1 适用场景

id是主键/唯一键

2 语句样式（子查询示例）

SELECT* FROMyour_table WHEREid <= (SELECTid FROMyour_table ORDER BYid descLIMIT ($page-1)*$pagesize ORDERBYid desc LIMIT $pagesize;

3 语句样式（连接示例）

SELECT* FROMyour_table ASt1 JOIN(SELECTid FROMyour_table ORDERBY id descLIMIT ($page-1)*$pagesize ASt2 WHERE t1.id <= t2.id ORDERBYt1.id descLIMIT $pagesize;

七、存储过程类（最好融合上述方法5/6）

1 适用场景

大数据量，作者推荐的方法。

2 原因

封装操作：把操作封装在服务器，相对更快一些。

八、反面方法

1 适用场景

网上有人写使用 SQL_CALC_FOUND_ROWS，没有道理，勿模仿。

九、相关问题与解答

9.1 问题1: 为什么在大数据集中使用LIMIT进行分页效率低下？

答案1:

在大数据集中使用LIMIT进行分页时，随着偏移量的增加，性能会逐渐变差，这是因为数据库需要跳过指定数量的行才能到达所需的记录，这种全表扫描的方式会导致查询时间显著增加，要获取第1000页的数据，数据库需要先扫描前999页的所有记录，然后才能返回第1000页的数据，这种方法不仅耗时长，而且容易造成系统资源紧张，对于大数据集的分页查询，建议采用基于索引或其他优化策略来提高性能。

9.2 问题2: 如何选择合适的分页方法以优化查询效率？

答案2:

选择合适的分页方法取决于数据量的大小和具体应用场景，以下是一些优化建议：

小数据集：对于数据量较小的情况，可以直接使用LIMIT子句进行分页，这种方法简单直观，适用于数据量在几百到几千条记录的场景。

建立索引：对于数据量较大的情况，可以建立主键或唯一索引，利用索引进行分页查询，通过使用WHERE条件过滤数据，可以减少扫描的记录数，从而提高查询效率，可以使用WHERE id_pk > (pageNum*10)来跳过前面的记录。

基于索引再排序：如果需要确保结果集的顺序稳定，可以在建立索引的基础上再进行排序，这种方法适用于对结果集顺序要求较高的场景。

使用子查询或连接：对于更复杂的需求，可以使用子查询或连接来快速定位元组的位置，然后再读取元组，这种方法适用于需要精确控制分页逻辑的情况。

存储过程：对于特别大的数据集，可以考虑使用存储过程将操作封装在服务器端执行，以减少网络传输的开销并提高执行效率。

小伙伴们，上文介绍了“分页方法mysql”的内容，你了解清楚吗？希望对你有所帮助，任何问题可以给我留言，让我们下期再见吧。