Mysql深分页

前言

分页是一个很普通的功能，只要是个后端开发就要写分页，那为什么要分页？

• 从业务上来讲，即使系统返回所有数据，用户绝大多数情况下是不会看后面的数据的。
• 技术上，因为要考虑取数据的成本，目标服务器磁盘、内存、网络带宽，以及请求发起方自身是否能承受大批量数据。

MySQL 分页语法

 select * from table limit 0, 20

思考: 使用分页，上面提到的第 2 点，这些成本真的能降低吗？

建表，造数据

 CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `name` varchar(255) DEFAULT NULL,
  `balance` int DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3571068 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=REDUNDANT 

-- 先初始化数据
DELIMITER //  
CREATE PROCEDURE InsertRandomAccounts(IN num_rows INT)  
BEGIN  
  DECLARE i INT DEFAULT 0;  
  WHILE i < num_rows DO  
    SET @name = CONCAT('Account', LPAD(i, 6, '0'));  
    SET @balance = FLOOR(RAND() * 10000); -- 随机生成一个0到9999之间的余额  
    SET @create_time = NOW() - INTERVAL FLOOR(RAND() * 3650) DAY; -- 随机生成一个过去3650天内的创建时间  
    SET @update_time = NOW(); -- 更新时间设置为当前时间  
      
    INSERT INTO account (name, balance, create_time, update_time)  
    VALUES (@name, @balance, @create_time, @update_time);  
      
    SET i = i + 1;  
  END WHILE;  
END //  
DELIMITER ;  
  
-- 调用存储过程插入两百万条数据  （插入数据前可先将索引删除来提升效率）
CALL InsertRandomAccounts(2000000);  
  
-- 删除存储过程（如果你不再需要它）  
DROP PROCEDURE IF EXISTS InsertRandomAccounts;

1：没有查询条件，没有排序

select id,name,balance from account a limit 1000000,20;

100w 之后的数据，耗时：

0.31 sec

加主键排序

select id,name,balance from account a order by id limit 1000000,20;

耗时：有所降低

0.22 sec

执行计划对比：
1:

2:

可以看到带主键排序使用了主键索引，且只读取了需要的前 n 条数据，所以快。

因此, 结论 1：即使业务上看起来没有任何条件还不需要排序，也加上 order by 主键。

这里其实有另一个问题：如果不带排序条件，MySQL 默认是什么排序？

通常认为是主键，但通过查资料发现并不一定，这里有个物理顺序和逻辑顺序的区别，如：删除原有数据后再插入复用旧 id 的数据，可能会由于存放在不同页上造成物理顺序与逻辑顺序不一致，此时可以通过优化表改善：optimize table table_name。

2： 带排序 - 排序字段没有索引

select * from account order by update_time desc limit 1000,20;

执行时间：
0.98s

排序字段有索引：（注：上条SQL执行前已将idx_update_time索引删除）

select * from account order by update_time desc limit 1000,20;

执行时间：
0.04s

执行计划对比：

1:

2：

可以看到有索引的表，直接走索引取前 n 条数据，不需要全表扫描，也不会用到 filesor。

结论 2：给常用字段加索引，包括排序字段。

新的问题：
以上 2 个场景看似已可以解决大部分分页问题，但：

排序字段有索引就一定快吗？1k的时候速度较快，换成查询 1w 之后的数据呢？
如果当前表已经有多个索引，不适合再添加索引了呢？

3： 排序字段有索引，但分页再深一点： 从 100w 开始取 20 条

select * from account order by update_time desc limit 1000000,20;

耗时：非常慢
4.1s

执行计划：

通过执行计划发现，并没有走索引，为什么没有走索引？
因为 mysql 优化器发现这条 sql 查询行数超过一定比例（据说是 30%，但测试下来并不完全是）就会自动转换为全表扫描，能不能强制走索引呢？
可以的，加 force index(idx)。
4：强制索引

select * from account force index(idx_update_time) order by update_time desc limit 1000000,20;

强制索引后的执行计划：

看下执行时间：
9.52s
反而更慢了，看来MySQL内部做的优化还是很充分的，数据量大的时候直接不走索引了

结论 3：即使有索引，再深一点的分页也会有问题，要避免。

以上尝试结果都没有很好的解决深分页性能问题，那是否有更好的解决方案？
有！

5： 带 last_条件查询

select *from t2
where id > #{last_id},update_time > #{last_update_time}
order by update desc
limit 0, 20;

性能与正常浅分页没差别，但前提是 last_*字段有索引。

同时，该方案受使用场景限制，如跳页、多排序字段等，last_*都将无法使用。

推荐使用场景：没有页码的应用，如：滑动加载下一页、只有上下页按钮等。

6：联表子查询

将场景 4 中强制索的 sql 改为子查询，先测试排序字段有索引的 t2 表。

select * from account a join (select id from account order by update_time desc limit 1000000,20) b on a.id = b.id;

执行时间：0.25 s
效果明显。

删除idx_update_time索引
sql 复制代码-- 在 执行：

select * from account a join (select id from account order by update_time desc limit 1000000,20) b on a.id = b.id;

执行时间：1.45s
效果明显。（原 sql 执行时长：0.25s）

改成子查询关联后，无论有没有索引都节省了大量时间，下面来分析下原因。

这两条关联查询的执行计划对比差距还是在于子查询是否使用索引排序，1 使用索引所以快。

对比子查询与非子查询执行计划：
1、

2、

区别：
全表扫描与强制索引区别上面已经讲过，那强制索引跟子查询区别是什么？

看起来就只是多了一个 Using index，那什么是 Using index 呢?

简单说就是直接通过索引树就能拿到查询字段的值，所以快的原因是子查询方式减少了回表查询操作，进而减少了大量数据的回表 IO，因此更高效。

区别：

乍一看发现这两条查询没有区别，不仅没区别，子查询还比直接查询复杂了，但它反而快了，为什么？

这里的关键其实就在于 Using filesort。
Using filesort 时，mysql 的两种排序策略。

一、单路排序

根据条件将所有查询字段数据取出到 sort buffer 缓冲区。

缓冲区满了根据排序字段执行一次排序（快排）把然后把排序后的数据写到临时文件。

将所有数据取出排序后，对所有临时文件按顺序做合并（归并排序）再写回到文件，直到最后所有文件合并完成。

从临时文件中读取满足分页条件所需数据返回，如果首次归并就可以取到分页数据则直接返回（浅分页）。

双路排序

根据查询条件将 row_id 和排序字段取出放到 sort buffer （区别 1）。

缓冲区满了根据排序字段执行一次排序（快排）把然后把排序后的数据写到临时文件。

将所有数据取出排序后，对所有临时文件按顺序做合并（归并排序）再写回到文件，直到最后所有文件合并完成。

从临时文件中读取满足分页条件的 row_id，再通过 row_id 读取对应行数据返回（区别 2）。

MySQL 在 4.1 之前都是双路排序，之后优化改为满足条件默认单路排序，条件为：查询字段数据大小小于 max_length_for_sort_data 值，但改到最小值测试也没有看到变化。
因此：子查询方式快的原因可以确定了，子查询只取了 create_time+id 到 sort buffer（相当于双路排序的做法）, 相比直接查询，省去了绝大部分字段，减少大量临时文件 IO 操作，因此提高查询效率。

关注VX-公众号，回复 J01 获取后端学习面试资源

本文由mdnice多平台发布