MySQL 分页的几种有用的方法

MySQL 分页的几种有用的方法

方法1: 使用 LIMIT 

SELECT * FROM 表名称 LIMIT M,N

或者写成：limit start, count  　　

特点: 简单，使用方便，但，是全表扫描,数据多的清下下速度会偏慢 ，且有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

方法2: 建立主键或唯一索引, 利用索引(假设每页10条)

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

适应场景: 适用于数据量多的情况(元组数上万)
特点： 索引扫描,速度会很快. 数据是基于pk_id排序

方法3: 基于索引再排序

SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
//
SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

特点: 索引扫描,速度会很快. 但MySQL的排序操作,ASC还好，但DESC排序尚未稳定。

利用子查询示例:

SELECT * FROM your_table WHERE id <=(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc LIMIT $pagesize

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

利用 JOIN 连接示例:

SELECT * FROM your_table AS t1 JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2 WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

方法4: 基于索引使用prepare

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

第一个问号表示pageNum，第二个？表示每页元组数

特点：适应于大数据量的情况，索引扫描,速度会很快. prepare语句比一般的查询语句快一点。

方法5：复合索引优化 search( 字段1,字段2 )

select id,title from 表名 limit 1000,10;

建立 search(vtype,id) 索引，示例：

select id from 表名 where vtype=1 limit 90000,10;

注意：where 条件，走索引limit，将where 放第一位，limit用到的主键放第2位，select 主键 

 

 

补充：其他关于分页数计算：

• 总记录数：TotalRecord
• 每页记录数：PapeSize
• 总页数：totalPage = (totalRecord + PapeSize -1) / PapeSize;   

 

 

 

创建时间：2021.08.18　　更新时间：