1. MySQL 大表扫描引发的磁盘 I/O 性能问题解析上周排查一个线上慢查询时发现一个看似简单的分页查询竟然触发了全表扫描导致磁盘 I/O 使用率直接飙到 100%。这个案例特别典型今天就来拆解下 MySQL 处理大偏移量分页查询时的底层机制以及我们最终采用的 7 种优化方案。当执行SELECT * FROM orders LIMIT 1000000, 10这类查询时MySQL 必须先扫描前 100 万条记录才能获取最后的 10 条结果。这种操作会产生三个致命问题无效数据扫描引擎需要读取 100 万条根本不需要的记录随机 I/O 风暴InnoDB 二级索引需要回表查询完整数据内存压力排序缓冲区可能溢出到临时文件关键点磁盘 I/O 爆炸的根本原因不是 LIMIT 语法本身而是大偏移量导致的无效数据加载2. 问题重现与原理剖析2.1 测试环境搭建用以下脚本创建一个包含 200 万条记录的测试表CREATE TABLE stress_test ( id bigint(20) NOT NULL AUTO_INCREMENT, random_data varchar(200) NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_create_time (create_time) ) ENGINEInnoDB; -- 插入200万测试数据 DELIMITER // CREATE PROCEDURE insert_test_data() BEGIN DECLARE i INT DEFAULT 0; WHILE i 2000000 DO INSERT INTO stress_test VALUES (NULL, CONCAT(data_, FLOOR(RAND()*1000)), DATE_ADD(2020-01-01, INTERVAL FLOOR(RAND()*1000) DAY)); SET i i 1; END WHILE; END // DELIMITER ; CALL insert_test_data();2.2 性能对比测试分别执行以下两个查询并观察执行计划-- 小偏移量查询 EXPLAIN SELECT * FROM stress_test ORDER BY create_time LIMIT 10, 10; -- 大偏移量查询 EXPLAIN SELECT * FROM stress_test ORDER BY create_time LIMIT 1000000, 10;通过SHOW STATUS LIKE Handler_read%可以看到查询类型Handler_read_nextHandler_read_rndDisk_io小偏移量(10)20016KB大偏移量(100W)1000010101.2GB2.3 底层执行流程解析索引扫描阶段使用idx_create_time索引定位到第一条满足条件的记录沿着索引叶子节点链表顺序读取 100 万条记录的主键 ID回表查询阶段根据主键 ID 逐个回聚簇索引查找完整记录产生大量随机 I/O关键瓶颈点排序过滤阶段将 100 万条完整记录加载到 sort buffer如果超出sort_buffer_size则使用临时文件3. 七种优化方案实战3.1 延迟关联最优解SELECT t.* FROM stress_test t JOIN ( SELECT id FROM stress_test ORDER BY create_time LIMIT 1000000, 10 ) AS tmp ON t.id tmp.id;原理先通过覆盖索引获取主键再用主键精确关联减少回表数据量3.2 书签记录法-- 第一页 SELECT * FROM stress_test ORDER BY create_time LIMIT 10; -- 获取上一页最后一条记录的create_time -- 下一页查询 SELECT * FROM stress_test WHERE create_time 2021-03-15 00:00:00 ORDER BY create_time LIMIT 10;适用场景有序翻页如新闻列表3.3 预计算分页-- 创建分页映射表 CREATE TABLE page_mapping ( page_no INT PRIMARY KEY, min_id INT, max_id INT ); -- 定期执行如每天凌晨 INSERT INTO page_mapping SELECT FLOOR((id-1)/1000) as page_no, MIN(id) as min_id, MAX(id) as max_id FROM stress_test GROUP BY FLOOR((id-1)/1000);3.4 倒序分页技巧-- 传统方式性能差 SELECT * FROM stress_test ORDER BY id DESC LIMIT 1000000, 10; -- 优化方式 SELECT * FROM stress_test WHERE id (SELECT id FROM stress_test ORDER BY id DESC LIMIT 1000000, 1) ORDER BY id DESC LIMIT 10;3.5 分区表方案CREATE TABLE stress_test_part ( id BIGINT NOT NULL AUTO_INCREMENT, random_data VARCHAR(200), create_time DATETIME, PRIMARY KEY (id, create_time) ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p2020 VALUES LESS THAN (TO_DAYS(2021-01-01)), PARTITION p2021 VALUES LESS THAN (TO_DAYS(2022-01-01)), PARTITION pmax VALUES LESS THAN MAXVALUE );3.6 游标分页适合APIGET /api/items?cursor12345limit10服务端实现SELECT * FROM items WHERE id 12345 ORDER BY id LIMIT 10;3.7 终极方案Elasticsearch 辅助对于千万级以上的表建议将分页查询迁移到ES{ query: {match_all: {}}, sort: [{create_time: asc}], from: 1000000, size: 10 }4. 生产环境避坑指南索引设计黄金法则分页查询必须要有 ORDER BY 条件的索引复合索引顺序(排序字段, 查询条件, 主键)关键参数调优[mysqld] sort_buffer_size 4M read_rnd_buffer_size 1M optimizer_switch prefer_ordering_indexon监控指标预警Handler_read_rnd突增Sort_merge_passes大于 0Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests比率升高分页查询红线禁止无索引排序的分页查询偏移量超过 5000 必须使用优化方案单页数据量不超过 100 条5. 真实案例复盘某电商平台订单历史页优化原查询SELECT * FROM orders WHERE user_id123 ORDER BY create_time DESC LIMIT 100000, 20执行时间8.2秒优化方案CREATE INDEX idx_user_time ON orders(user_id, create_time, id)优化后查询SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE user_id123 ORDER BY create_time DESC LIMIT 100000, 20 ) AS tmp ON o.id tmp.id最终耗时0.15秒这个案例给我的启示是MySQL 分页优化的本质是减少数据搬运量。通过覆盖索引延迟关联我们成功将磁盘 I/O 从 1.8GB 降到了 12MB
