怎么优化MySQL大批量数据分页查询后半段性能下降的问题

1.png

MySQL大批量数据分页查询后半段性能为什么会下降？

关键字：大批量数据

何为大批量数据？表中的记录至少得几十万以上了吧。所以这个问题的前提是表中的记录行数达到了一个量级。

首先我们得回顾一下MySQL分页查询的SQL语句：

SELECT * FROM 表名 LIMIT 每页显示的行数 OFFSET 偏移量;

或者使用简化版：表示查询结果返回前面多少行数据

SELECT * FROM 表名 LIMIT 每页显示的行数;

或者

SELECT * FROM 表名 LIMIT 偏移量, 每页显示的行数;

比较常用的一般是最后一种。

我们都知道，在进行分页查询时，需要注意两个问题：

需要对结果进行排序，以确保分页结果的顺序一致
对于大数据集，使用OFFSET进行分页可能会影响性能，因为MySQL仍然需要扫描并跳过指定数量的行

例子：

SELECT * FROM `user` ORDER BY id LIMIT 1000000,10;

当使用LIMIT 1000000, 10时，MySQL需要扫描前1000000+10行数据并丢弃前1000000行，从而导致深分页效率极低。

怎么优化深分页效率极低的问题呢？

归根到底是Limit OFFSET大偏移量导致的。优化MySQL查询效率无非就是尽可能的减少扫描行数。

首先SQL优化：避免Limit OFFSET大偏移量

上面的例子中，LIMIT 1000000, 10，需要偏移1百万行记录，怎么有效规避这种情况呢？

可以进行游标分页（记录下最后一条记录的游标值（主键或者其他唯一标识））

SELECT * FROM 表名 WHERE id > last_id ORDER BY id LIMIT 10;

在不进行额外操作记录最后一条记录的游标值时，可以使用子查询优化

SELECT * FROM 表名 WHERE id >= (SELECT id FROM 表名 ORDER BY id LIMIT 1000000,1) LIMIT 10;

子查询仅扫描主键，可以避免回表。

当然了，主键可以是自增整数，也可以是字符串，如果主键是字符串的话，就不能使用这种方式了。

名词解释：回表

MySQL的存储引擎InnoDB,有聚焦索引（聚集索引）和非聚焦索引，有主键索引的情况下，主键索引就是聚焦索引，数据结构是B+树，叶子节点保存了主键的值和实际的数据行；非聚焦索引（辅助索引）的叶子节点保存的是索引字段的值以及主键的值，如果查询的字段不在辅助索引上，就需要进行回表操作，回表操作指的是需要通过辅助索引找到主键值，然后根据主键值从聚集索引中获取完整的数据行。

接着业务与框架层调整：避免Limit OFFSET大偏移量

限制分页深度

业务层面限制用户可访问的最大页码（如仅允许查询前1000页）。
冷热数据分离

将历史数据归档至单独表或数据库，减少单表数据量。

一般方案为：将历史数据从主表中移至单独的表或数据库 -> 将归档的数据同步到 Elasticsearch 中 -> 对于历史数据，查询 Elasticsearch

面试题：根据业务规则定义冷热数据，业务需求变更时，冷数据突然又变成了热数据应该怎么操作？

确定哪些冷数据需要迁回主表->根据需要，从归档表中提取数据->将提取到的数据插入到主表中->从归档存储中删除数据、从ES删除数据（可选）

异步加载与缓存

高频访问的深分页结果可异步生成并缓存，例如使用Redis缓存分页结果集，减少实时查询压力。

再就是架构设计方案：避免Limit OFFSET大偏移量

分区表
分库分表

分区表仍然属于单库单表的范畴，无法解决单库容量或连接数的瓶颈的问题，不利于水平扩展；分库分表可以通过增加数据库实例或表的数量，轻松应对数据增长和高并发，利于水平扩展。