MySQL 分页的几种有用的方法

180it 2021-09-11 AM 1391℃ 0条

MySQL 分页的几种有用的方法

方法1: 使用 LIMIT

1
SELECT * FROM 表名称 LIMIT M,N
或者写成:limit start, count   

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

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

1
SELECT FROM 表名称 WHERE id_pk > (pageNum10) LIMIT M
适应场景: 适用于数据量多的情况(元组数上万)
特点: 索引扫描,速度会很快. 数据是基于pk_id排序

方法3: 基于索引再排序

1
2
3
SELECT FROM 表名称 WHERE id_pk > (pageNum10) ORDER BY id_pk ASC LIMIT M
//
SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20
特点: 索引扫描,速度会很快. 但MySQL的排序操作,ASC还好,但DESC排序尚未稳定。

利用子查询示例:

1
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
1
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
利用 JOIN 连接示例:

1
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;
1
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id
方法4: 基于索引使用prepare

1
PREPARE stmt_name FROM SELECT FROM 表名称 WHERE id_pk > (? ?) ORDER BY id_pk ASC LIMIT M
第一个问号表示pageNum,第二个?表示每页元组数

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

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

1
select id,title from 表名 limit 1000,10;
建立 search(vtype,id) 索引,示例:

1
select id from 表名 where vtype=1 limit 90000,10;
注意:where 条件,走索引limit,将where 放第一位,limit用到的主键放第2位,select 主键

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

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

支付宝打赏支付宝打赏 微信打赏微信打赏

如果文章或资源对您有帮助,欢迎打赏作者。一路走来,感谢有您!

标签: none

MySQL 分页的几种有用的方法