技术笔记

外观

一次SQL查询优化原理分析

约 1028 字大约 3 分钟

2025-06-19

数据量：950W+，优化前 17s → 优化后 300ms

本文通过实际案例讲解 MySQL 深度分页优化的原理与方法。

优化方案

核心思路：将查询条件放到子查询中，子查询只查主键 ID，然后通过子查询确定的主键关联查询其他字段。

原理：

  1. 减少回表操作：子查询只扫描索引，避免大量回表
  2. 参考《阿里巴巴Java开发手册》：利用延迟关联或者子查询优化超多分页场景

说明：MySQL 并不会跳过 offset 行，而是取出 offset + N 行，然后丢弃前 offset 行、返回 N 行。当 offset 特别大时，效率极低。解决方法：控制返回的总页数，或对超过特定阈值的页数进行 SQL 改写。

优化前

SELECT 各种字段
FROM `table_name`
WHERE 各种条件
LIMIT 0, 10;

优化后（延迟关联）

SELECT main_tale.*
FROM `table_name` main_tale
INNER JOIN (
    SELECT id
    FROM `table_name`
    WHERE 各种条件
    LIMIT 0, 10
) temp_table ON temp_table.id = main_tale.id;

案例演示

环境信息

-- MySQL 版本
SELECT VERSION();
-- +-----------+
-- | version() |
-- +-----------+
-- | 5.7.17    |
-- +-----------+

-- 表结构
DESC test;
-- +--------+---------------------+------+-----+---------+----------------+
-- | Field  | Type                | Null | Key | Default | Extra          |
-- +--------+---------------------+------+-----+---------+----------------+
-- | id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
-- | val    | int(10) unsigned    | NO   | MUL | 0       |                |
-- | source | int(10) unsigned    | NO   |     | 0       |                |
-- +--------+---------------------+------+-----+---------+----------------+
  • id：自增主键
  • val：非唯一索引
  • 数据量：约 524 万条

优化前的慢查询

SELECT * FROM test WHERE val = 4 LIMIT 300000, 5;
-- 耗时：15.98 秒

优化后的快速查询

SELECT * FROM test a
INNER JOIN (SELECT id FROM test WHERE val = 4 LIMIT 300000, 5) b
ON a.id = b.id;
-- 耗时：0.38 秒

性能提升约 40 倍。

原理解析

慢查询的执行过程

SELECT * FROM test WHERE val = 4 LIMIT 300000, 5 的执行过程：

  1. 通过 val 索引找到索引叶子节点数据
  2. 根据叶子节点上的主键值去聚簇索引上查询全部字段值（回表）
  3. 重复步骤 1~2 共 300005 次
  4. 丢弃前 300000 条，返回最后 5 条

问题：MySQL 耗费了大量随机 I/O 在查询聚簇索引的数据上，而有 300000 次随机 I/O 查询到的数据最终被丢弃。

优化方案的执行过程

子查询 SELECT id FROM test WHERE val = 4 LIMIT 300000, 5：

  1. 通过 val 索引定位到符合条件的 300005 个主键 ID
  2. 由于子查询只查主键，无需回表，直接在索引上完成
  3. 外层查询通过 5 个确定的主键 ID 回表获取完整数据

优势：只需 5 次随机 I/O（回表），而非 300005 次。

Buffer Pool 验证

通过对比两次查询后 Buffer Pool 中的数据页数量，可以证实上述分析：

优化前查询后：

SELECT index_name, COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE
WHERE INDEX_NAME IN ('val', 'PRIMARY') AND TABLE_NAME LIKE '%test%'
GROUP BY index_name;
-- PRIMARY: 4098 个数据页
-- val:     208  个索引页

优化后查询后：

-- PRIMARY: 5    个数据页
-- val:     390  个索引页

第一个查询加载了 4098 个数据页到 Buffer Pool，而第二个查询只加载了 5 个。这证实了优化方案大幅减少了无用数据的加载。

注意：加载大量低热度数据到 Buffer Pool 会造成Buffer Pool 污染，挤占热点数据空间，影响整体性能。

其他分页优化方案

方案一：基于游标的分页（推荐）

-- 上一页最后一条记录的 id
SELECT * FROM test WHERE val = 4 AND id > 3327622 ORDER BY id LIMIT 5;

适用于按主键递增排序的场景，性能最优。

方案二：限制最大页码

-- 前端控制最大页码，后端校验
-- 超过 10000 页时提示用户

方案三：COUNT 与数据分离

-- 分页查询与总数查询分离，避免 COUNT 影响性能
SELECT SQL_CALC_FOUND_ROWS * FROM test WHERE val = 4 LIMIT 0, 5;
SELECT FOUND_ROWS();

总结

方案适用场景性能复杂度
延迟关联（子查询）大数据量深度分页⭐⭐⭐⭐⭐低
游标分页主键递增排序⭐⭐⭐⭐⭐中
限制最大页码前端展示⭐⭐⭐⭐低

核心原则：尽量减少需要扫描的行数，避免无意义的回表操作。