mysql大数据量分页查询

一、什么是‌MySQL大数据量分页查？

MySQL大数据量分页查‌是指在使用MySQL数据库时，将大量数据分成多个较小的部分进行显示，以提高查询效率和用户体验。分页查询通常用于网页或应用程序中，以便用户能够逐步浏览结果集。

二、为什么要用MySQL大数据量分页？

随着业务的增长，数据库的数据也呈指数级增长，之前所写的代码mysql的分页都是采用的limit方式进行，这种方式固然代码比较简单，但数据量大了之后真的是查的慢。

所以就用到mysql大数据量后的分页查询方法及其优化技巧，不但提高性能还能增加用户体验。

1.直接使用数据库提供的SQL语句

SELECT *FROM 表名称 LIMIT M,N

Limit限制的是从结果集的M位置处取出N条输出，其余抛弃，语句的查询时间与起始记录的位置成正比，适用于数据量较少的情况(元组百/千级)，全表扫描,速度会很慢 且有的数据库结果集返回不稳定

2.建立主键或唯一索引, 利用索引

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

适用于数据量多的情况(元组数上万)，索引扫描,速度会很快，通过主键或者索引的方式去查询可能会出现一个致命的问题就是数据查询出来并不是按照主键或者索引排序的，所以会有漏掉数据的情况

3.基于索引再排序

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

适用于数据量多的情况(元组数上万). 最好ORDER BY后的列对象是主键或唯一索引,使得ORDERBY操作能利用索引被消除但结果集是稳定的,索引扫描,速度会很快,这种方式会让我们的查询效率得到更大的提升

4.基于索引使用prepare

PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk
ORDER BY id_pK ASC LIMIT M

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

适用于大数据量，索引扫描,速度会很快。prepare语句又比一般的查询语句快一点

 5.利用MySQL支持ORDER操作可以利用索引快速定位部分元组，避免全表扫描。

SELECT * FROM 表名 WHERE id>=780000 ORDER BY id ASC LIMIT 0,20

可以发现这种效率和上面方法的效率差不多，因为效率的提升的原因都是走id主键索引

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

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

效率较低

总结：

如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！