MySQL大分区表LIMIT查询缓慢问题优化咨询

问题背景 #

拥有一个AWS Aurora 5.7版本的MySQL数据库，总大小约5TB，其中一张表占2.5TB。该表为范围分区，每天创建一个新分区，目前约有700个分区，表结构如下：

尝试导出整张表的数据，每次针对一个分区执行如下LIMIT查询：

SELECT HEX(`hash`), `value`
FROM my_table
WHERE partition=... AND user_id=... AND  group_id=...
LIMIT 200000, 100000

这类查询每次耗时1-3分钟，且LIMIT起始索引越大，查询速度越慢，无WHERE条件查询1000行的耗时如下：

LIMIT       0, 1000 -> 0.000sec
LIMIT    1000, 1000 -> 0.015sec
LIMIT   10000, 1000 -> 0.094sec
LIMIT  100000, 1000 -> 0.400sec
LIMIT 1000000, 1000 -> 5.781sec

优化方案 #

1. 改用主键分页（键集分页）替代OFFSET分页 #

OFFSET慢的核心原因是数据库需要先扫描OFFSET指定的行数再返回结果，随着OFFSET增大，扫描的数据量线性增长。利用主键的有序性，通过上一页的最后一条主键值定位下一页起始位置：

假设上一页最后一条数据的partition、user_id、group_id、hash值分别为p_val、u_val、g_val、h_val，下一页查询可写为：

SELECT HEX(`hash`), `value`
FROM my_table
WHERE partition = ... 
  AND user_id = ... 
  AND group_id = ...
  AND (partition, user_id, group_id, hash) > (p_val, u_val, g_val, h_val)
ORDER BY partition, user_id, group_id, hash
LIMIT 100000;

这种方式直接利用主键索引定位，无需扫描前面的行，性能不会随分页次数增加而下降。

2. 利用分区特性批量导出 #

由于表按partition（每天一个分区）范围分区，可直接针对单个分区全量导出，避免分页：

• 使用SELECT ... FROM my_table PARTITION(partition_name)读取整个分区数据，配合INTO OUTFILE（权限允许时）或客户端流式读取，一次性获取分区内所有数据。
• Aurora支持mysqldump指定分区导出，示例命令：

mysqldump -h host -u user -p db_name my_table --partition=partition_name > partition_data.sql

3. 优化查询的索引使用 #

当前主键是(partition, user_id, group_id, hash)，查询已指定partition、user_id、group_id等值条件，需确保查询显式指定与主键顺序一致的ORDER BY，避免额外排序操作，让数据库直接利用主键索引快速定位数据范围。

4. 利用Aurora只读副本分流查询 #

将导出查询转移到Aurora只读副本执行，避免影响主库业务性能，同时可单独优化只读副本配置（如更大内存、更高IOPS）提升导出速度。

5. 临时调整Aurora实例配置 #

如果导出是临时需求，可临时升级Aurora实例规格（增加内存、CPU、IOPS），提升数据库处理能力，完成导出后再降级。

内容的提问来源于stack exchange，提问作者rubenhak