MySQL 百万级数据，如何做分页查询？

冷不防 2023-10-11 20:36 14阅读 0赞

随着业务的增长，数据库的数据也呈指数级增长，拿订单表为例，之前的订单表每天只有几千个，一个月下来不超过十万。而现在每天的订单大概就是2w+，目前订单表的数据已经达到了700w。这带来了各种各样的问题，今天我先从一个小问题开始。

之前所写的代码mysql的分页都是采用的limit方式进行，这种方式固然代码比较简单，但数据量大了之后真的是查的慢。所以此处涉及到mysql大数据量后的分页查询方法及其优化技巧

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

语句样式: MySQL中，可用如下方法:

SELECT * FROM 表名称 LIMIT M,N

适应场景：适用于数据量较少的情况(元组百/千级)

原因/缺点：全表扫描,速度会很慢 且有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)。

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

再具体进行测试之前，我们需要先创建100w条测试数据，推荐使用存储过程进行创建

*  创建存储过程

DROP PROCEDURE IF EXISTS create_user_tel;
    create procedure create_user_tel() 
    begin 
        declare id int; 
        set id=1;
        while id <=1000000
          do 
            INSERT INTO `user01` VALUES(id,  'test123', 'm');
            set id=id+1;
        end while;
    end;

*  执行存储过程

call create_user_tel();

下面开始测试这种方法  
![在这里插入图片描述][83e7c19844b24c8f8e790e67c61bb86f.png_pic_center]

可以看到我表里总共80w条数据，我们看看用这种方法来分页查询的耗时

select * from user01 limit 780000, 20;

![在这里插入图片描述][41ddddef846e4b619fcc0d91d6f5e38f.png_pic_center]

像这种分页最大的页码页显然这种时间是无法忍受的，同时大家可以自行测试一下limit 100 20 ,limit 1000 20,limit 10000 20等所耗费的时间，不难发现**limit语句的查询时间与起始记录的位置成正比**

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

语句样式: MySQL中,可用如下方法:

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

*  适应场景: 适用于数据量多的情况(元组数上万)
 *  原因: 索引扫描,速度会很快。

我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何。

这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：

select id from user01 limit 780000, 20;

上面这个语句只能查询id，如果我们也要查询所有列，有两种方法：

*  一种是id>=的形式
 *  另一种就是利用join

SELECT * FROM user01 
    WHERE id > =(
    select id from user01 limit 780000, 1) 
    limit 20

另一种写法

SELECT * FROM user01 a JOIN 
    (select id from user01 limit 780000, 20) b 
    ON a.id = b.id

自己可以操作一下就会发现效率会大幅度提升，如果你发现这些语句速度差别不大的话，性能的限制还有可能是你服务器或自己的电脑性能不足导致的

通过主键或者索引的方式去查询可能会出现一个致命的问题就是**数据查询出来并不是按照主键或者索引排序的，所以会有漏掉数据的情况**

这种情况可以通过方法三来解决

## 方法3：基于索引再排序 ##

语句样式: MySQL中，可用如下方法:

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

*  适应场景: 适用于数据量多的情况(元组数上万). 最好ORDER BY后的列对象是主键或唯一索引,使得ORDERBY操作能利用索引被消除但结果集是稳定的(稳定的含义,参见方法1)
 *  原因: 索引扫描,速度会很快.

SELECT * FROM user01 
    WHERE id >= 780000
    ORDER BY id ASC LIMIT 20

![在这里插入图片描述][9bbe40c1fc3b4f2b978dd9827ddd791d.png_pic_center]

这种方式会让我们的查询效率得到更大的提升

## 方法4：基于索引使用prepare ##

语句样式: MySQL中,可用如下方法:

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

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

*  适应场景: 大数据量
 *  原因: 索引扫描,速度会很快。

prepare语句又比一般的查询语句快一点。

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

比如: 读第1000到1019行数据

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

![在这里插入图片描述][c8639246ea604bf79873e3c50524fde0.png_pic_center]

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

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

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 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;

我个人实验之后发现效率极其低下

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

[83e7c19844b24c8f8e790e67c61bb86f.png_pic_center]: https://img-blog.csdnimg.cn/83e7c19844b24c8f8e790e67c61bb86f.png#pic_center
[41ddddef846e4b619fcc0d91d6f5e38f.png_pic_center]: https://img-blog.csdnimg.cn/41ddddef846e4b619fcc0d91d6f5e38f.png#pic_center
[9bbe40c1fc3b4f2b978dd9827ddd791d.png_pic_center]: https://img-blog.csdnimg.cn/9bbe40c1fc3b4f2b978dd9827ddd791d.png#pic_center
[c8639246ea604bf79873e3c50524fde0.png_pic_center]: https://img-blog.csdnimg.cn/c8639246ea604bf79873e3c50524fde0.png#pic_center