问题背景

业务反馈某个查询页面加载需要 3-5 秒，接口超时率高达 30%。开发同学说”数据库有索引”，DBA 看了一下说”执行计划没问题”，运维看了一眼说”CPU 和内存都够用”。问题到底在哪？

MySQL 慢查询优化不是玄学，也不是调几个参数就能解决的。它需要工程师从业务语义出发，理解 SQL 的执行过程，分析执行计划，找到瓶颈所在，然后从索引设计、SQL 改写、配置调整等多个维度综合优化。

这篇文章用一个真实的优化案例，覆盖从发现慢查询、分析根因、设计方案、实施修复到验证效果的全流程。

环境说明与建表

为了便于理解，我们从一张真实的订单表开始。

业务场景

电商家务系统，用户下单后需要查询”我的订单列表”，包含订单基本信息、商品信息、支付信息，条件是当前用户 ID，支持分页和按时间倒序排列。

表结构

-- 订单主表
CREATETABLE`t_order` (
`id`bigintNOTNULL AUTO_INCREMENT COMMENT'订单ID',
`order_no`varchar(32) NOTNULLCOMMENT'订单编号',
`user_id`bigintNOTNULLCOMMENT'用户ID',
`status`tinyintNOTNULLDEFAULT'1'COMMENT'订单状态：1待支付 2已支付 3已发货 4已完成 5已取消',
`total_amount`decimal(10,2) NOTNULLCOMMENT'订单总金额',
`pay_amount`decimal(10,2) NOTNULLCOMMENT'实付金额',
`pay_time` datetime DEFAULTNULLCOMMENT'支付时间',
`ship_time` datetime DEFAULTNULLCOMMENT'发货时间',
`receive_time` datetime DEFAULTNULLCOMMENT'收货时间',
`create_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',
`update_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',
  PRIMARY KEY (`id`),
UNIQUEKEY`uk_order_no` (`order_no`),
KEY`idx_user_id` (`user_id`),
KEY`idx_user_status` (`user_id`,`status`),
KEY`idx_create_time` (`create_time`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COMMENT='订单主表';

-- 订单商品明细表
CREATETABLE`t_order_item` (
`id`bigintNOTNULL AUTO_INCREMENT COMMENT'明细ID',
`order_id`bigintNOTNULLCOMMENT'订单ID',
`user_id`bigintNOTNULLCOMMENT'用户ID',
`goods_name`varchar(128) NOTNULLCOMMENT'商品名称',
`goods_id`bigintNOTNULLCOMMENT'商品ID',
`price`decimal(10,2) NOTNULLCOMMENT'商品单价',
`quantity`intNOTNULLCOMMENT'购买数量',
`subtotal`decimal(10,2) NOTNULLCOMMENT'小计金额',
`create_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',
  PRIMARY KEY (`id`),
KEY`idx_order_id` (`order_id`),
KEY`idx_user_id` (`user_id`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COMMENT='订单商品明细表';

-- 支付记录表
CREATETABLE`t_pay_record` (
`id`bigintNOTNULL AUTO_INCREMENT COMMENT'支付记录ID',
`order_id`bigintNOTNULLCOMMENT'订单ID',
`user_id`bigintNOTNULLCOMMENT'用户ID',
`pay_no`varchar(64) NOTNULLCOMMENT'支付流水号',
`pay_channel`varchar(16) NOTNULLCOMMENT'支付渠道：alipay wechat bankcard',
`pay_amount`decimal(10,2) NOTNULLCOMMENT'支付金额',
`status`tinyintNOTNULLDEFAULT'1'COMMENT'支付状态：1待支付 2已支付 3已退款',
`pay_time` datetime DEFAULTNULLCOMMENT'支付时间',
`create_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',
  PRIMARY KEY (`id`),
UNIQUEKEY`uk_pay_no` (`pay_no`),
KEY`idx_order_id` (`order_id`),
KEY`idx_user_id` (`user_id`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COMMENT='支付记录表';

数据量

• t_order：约 800 万条
• t_order_item：约 3200 万条（每单平均 4 个商品）
• t_pay_record：约 800 万条

初始慢查询

-- 查询用户最近 30 天的所有订单详情（带商品明细和支付信息）
SELECT
    o.id AS order_id,
    o.order_no,
    o.status,
    o.total_amount,
    o.pay_amount,
    o.pay_time,
    o.create_time,
    oi.id AS item_id,
    oi.goods_name,
    oi.price,
    oi.quantity,
    oi.subtotal,
    pr.pay_no,
    pr.pay_channel,
    pr.pay_amount AS real_pay_amount
FROM t_order o
LEFTJOIN t_order_item oi ON o.id = oi.order_id
LEFTJOIN t_pay_record pr ON o.id = pr.order_id
WHERE o.user_id = 123456
    AND o.create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
ORDERBY o.create_time DESC
LIMIT20;

在 MySQL 5.7.30 中执行这条 SQL，耗时 3.2 秒。用 EXPLAIN 分析执行计划。

第 1 步：开启慢查询日志并分析

开启慢查询日志

-- 查看慢查询是否开启
SHOWVARIABLESLIKE'slow_query_log';

-- 查看慢查询阈值（秒）
SHOWVARIABLESLIKE'long_query_time';

-- 开启慢查询日志（临时）
SETGLOBAL slow_query_log = 'ON';
SETGLOBAL long_query_time = 1;  -- 超过 1 秒就记录

-- 开启慢查询日志（永久），修改 my.cnf
-- [mysqld]
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1
-- log_queries_not_using_indexes = 1

查看慢查询日志内容

# 用 mysqldumpslow 汇总慢查询（安装 mysql 包后自带）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 参数说明：
# -s c：按查询次数排序
# -s t：按总执行时间排序
# -s l：按平均执行时间排序
# -t 10：取前 10 条

输出示例：

Count: 1500  Time=2.85s (4275s)  Lock=0.00s (15s)  Rows=8.5 (12750), RootUser[root]@192.168.1.10
  SELECT o.id, o.order_no, o.status FROM t_order o LEFT JOIN t_order_item oi ON o.id = oi.order_id ...

pt-query-digest 深度分析

如果安装了 Percona Toolkit，用 pt-query-digest 可以看到更详细的分析：

# 安装 Percona Toolkit
# CentOS: yum install percona-toolkit
# Ubuntu: apt install percona-toolkit

pt-query-digest /var/log/mysql/slow.log --since='2026-05-01 10:00:00' --until='2026-05-01 12:00:00'

第 2 步：分析执行计划

EXPLAIN 基本用法

EXPLAINSELECT
    o.id AS order_id,
    o.order_no,
    o.status,
    o.total_amount,
    o.pay_amount,
    o.pay_time,
    o.create_time,
    oi.id AS item_id,
    oi.goods_name,
    oi.price,
    oi.quantity,
    oi.subtotal,
    pr.pay_no,
    pr.pay_channel,
    pr.pay_amount AS real_pay_amount
FROM t_order o
LEFTJOIN t_order_item oi ON o.id = oi.order_id
LEFTJOIN t_pay_record pr ON o.id = pr.order_id
WHERE o.user_id = 123456
    AND o.create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
ORDERBY o.create_time DESC
LIMIT20;

执行计划字段详解

输出示例：

+----+-------------+-------+------------+--------+---------------------------+---------+---------+-------+--------+----------+------------------------------------------+
| id | select_type | table | partitions | type   | key                       | key_len | ref     | rows  | filtered| Extra   |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+-------+--------+----------+------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | ref    | idx_user_id               | 8       | const   | 15680 |   10.00| Using index condition; Using filesort    |
|  1 | SIMPLE      | oi    | NULL       | ref    | idx_order_id              | 8       | o.id    |     4 |  100.00| NULL                                   |
|  1 | SIMPLE      | pr    | NULL       | ref    | idx_order_id              | 8       | o.id    |     1 |  100.00| NULL                                   |
+----+-------------+-------+------------+--------+---------------------------+---------+---------+-------+--------+----------+------------------------------------------+

重点字段解读：

type（连接类型）：

• const：主键或唯一索引等值查询，最多返回一条记录，最优
• eq_ref：在联表查询中，被驱动表通过主键或唯一索引等值查询，每行返回一条
• ref：通过普通索引等值查询，返回多条匹配记录
• range：索引范围扫描（>、<、BETWEEN、IN）
• index：全索引扫描，扫描整个索引文件
• ALL：全表扫描，最差

本例中三个表都是 ref，看起来还可以，但实际性能问题在后面。

key（实际使用的索引）：

• 本例 o 表用到了 idx_user_id，但还需要 ORDER BY create_time 排序，而 idx_user_id 只包含 (user_id)，不包含 create_time，所以产生了 Using filesort。

rows（预估扫描行数）：

• 本例 o 表预估扫描 15680 行，oi 表 4 行（每单 4 个商品），pr 表 1 行
• 15680 行全部扫描，然后排序取前 20，这个过程很慢

Extra（额外信息）：

• Using filesort：需要额外的排序步骤，无法利用索引顺序
• Using index condition：使用了索引下推（Index Condition Pushdown，ICP）
• Using temporary：需要创建临时表
• Using where：在存储引擎层之后用 WHERE 条件过滤
• Range checked for each record：没有合适的索引，动态计算

理解 Using filesort

Using filesort 是性能杀手。它的意思是：MySQL 无法利用索引顺序，只能把数据读到内存中，在 sort buffer 里排序。

排序的数据量越大，filesort 越慢。如果排序的行数很多，MySQL 会把 sort buffer 分成多份，分别排序，最后合并（外部排序），性能急剧下降。

可以通过 max_length_for_sort_data 参数控制 sort buffer 中每行的大小，但如果这个值设得太小，会导致更频繁的分组合并，反而更慢。

第 3 步：索引优化

问题分析

当前索引：idx_user_id(user_id)

SQL 的 WHERE 条件：user_id = 123456 AND create_time >= ...

SQL 的 ORDER BY：ORDER BY create_time DESC

SQL 的 LIMIT：LIMIT 20

问题在于 idx_user_id 只覆盖了 user_id，MySQL 找到了该用户的所有订单（约 15680 条），然后按 create_time 排序，取前 20 条。

优化方向：让索引包含 WHERE 条件和 ORDER BY 字段，减少回表和排序。

方案 1：覆盖索引

覆盖索引（Covering Index）是指一个索引包含了查询所需的所有字段，MySQL 不需要回表就能拿到所有数据。

对于 t_order 表，最优索引是：

-- 创建覆盖索引
ALTERTABLE t_order ADDINDEX idx_user_time (user_id, create_time DESC, status, id, order_no, total_amount, pay_amount, pay_time);

但这个索引字段太多，索引体积大，维护成本高。

更合理的做法是分析查询字段，只放必要的：

-- 针对当前查询的覆盖索引
-- 注意：user_id 必须在最前面（最左前缀原则）
ALTERTABLE t_order ADDINDEX idx_covering (user_id, create_time DESC);

验证索引是否生效：

EXPLAINSELECT
    o.id AS order_id,
    o.order_no,
    o.status,
    o.total_amount,
    o.pay_amount,
    o.pay_time,
    o.create_time
FROM t_order o
WHERE o.user_id = 123456
    AND o.create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
ORDERBY o.create_time DESC
LIMIT20;

执行计划变化：

+----+-------------+-------+------------+------+------------------+---------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | key              | key_len | ref     | rows  | filtered| Extra   |
+----+-------------+-------+------------+------+------------------+---------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | o     | NULL       | ref  | idx_covering     | 8       | const   |  150  |   10.00| Using where; Using index |
+----+-------------+-------+------------+------+------------------+---------+---------+-------+--------+----------+-------+

变化：

• type 从 ALL 变成 ref（从全表扫描变成索引查找）
• key 从 idx_user_id 变成 idx_covering
• rows 从 15680 变成 150（30 天内的订单数）
• Extra 变成了 Using where; Using index（覆盖索引，不需要回表）
• Using filesort 消失了（索引本身有序）

单表查询从 3.2 秒降到了 3-5 毫秒。

方案 2：联合索引优化（进阶）

实际查询需要关联 t_order_item 和 t_pay_record，需要考虑 JOIN 的顺序和索引。

分析 JOIN 顺序：

1. t_order 先按 user_id 和 create_time 过滤，得到 150 条记录
2. 用这 150 条的 id 去关联 t_order_item，每单平均 4 条，得到 600 条
3. 用这 150 条的 id 去关联 t_pay_record，每单 1 条，得到 150 条

idx_order_id 在 t_order_item 和 t_pay_record 上已经存在，但最好改成覆盖索引，减少回表：

-- t_order_item 的索引优化
ALTERTABLE t_order_item ADDINDEX idx_order_id_covering (order_id, user_id, goods_name, price, quantity, subtotal);

-- t_pay_record 的索引优化
ALTERTABLE t_pay_record ADDINDEX idx_order_id_covering (order_id, user_id, pay_no, pay_channel, pay_amount);

再次验证完整查询的执行计划：

EXPLAINSELECT
    o.id AS order_id,
    o.order_no,
    o.status,
    o.total_amount,
    o.pay_amount,
    o.pay_time,
    o.create_time,
    oi.id AS item_id,
    oi.goods_name,
    oi.price,
    oi.quantity,
    oi.subtotal,
    pr.pay_no,
    pr.pay_channel,
    pr.pay_amount AS real_pay_amount
FROM t_order o
LEFTJOIN t_order_item oi ON o.id = oi.order_id
LEFTJOIN t_pay_record pr ON o.id = pr.order_id
WHERE o.user_id = 123456
    AND o.create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
ORDERBY o.create_time DESC
LIMIT20;

EXPLAIN ANALYZE（MySQL 8.0+）

如果你用的是 MySQL 8.0，可以用 EXPLAIN ANALYZE 看实际执行成本：

EXPLAINANALYZESELECT
    o.id AS order_id,
    ...
FROM t_order o
WHERE o.user_id = 123456
    AND o.create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
ORDERBY o.create_time DESC
LIMIT20;

输出示例：

-> Limit: 20 row(s)  (actual time=0.032..0.048 rows=20 loops=1)
    -> Nested loop left join  (actual time=0.030..0.045 rows=20 loops=1)
        -> Nested loop left join  (actual time=0.027..0.040 rows=20 loops=1)
            -> Index lookup on o using idx_covering (user_id=123456), (actual time=0.010..0.020 rows=20 loops=1)
            -> Index lookup on oi using idx_order_id_covering (order_id=o.id)  (cost=0.71 rows=4) (actual time=0.003..0.004 rows=4 loops=20)
            -> Index lookup on pr using idx_order_id_covering (order_id=o.id)  (cost=0.37 rows=1) (actual time=0.001..0.002 rows=1 loops=20)

这个输出非常直观：主查询 0.010 秒找到 20 条记录，每次 JOIN 都是索引查找，每次只需 0.003 秒。

第 4 步：SQL 改写优化

即使索引已经优化，SQL 本身的写法也会影响性能。以下是几种常见的改写思路。

4.1 减少不必要的 LEFT JOIN

当前查询用 LEFT JOIN 关联了 t_pay_record，但实际业务中只有已支付的订单才有支付记录。如果业务上不需要显示未支付订单的支付信息，可以把 LEFT JOIN 改成 JOIN，并在 WHERE 中加上支付状态过滤：

SELECT
    o.id AS order_id,
    o.order_no,
    o.status,
    o.total_amount,
    o.pay_amount,
    o.pay_time,
    o.create_time,
    oi.id AS item_id,
    oi.goods_name,
    oi.price,
    oi.quantity,
    oi.subtotal,
    pr.pay_no,
    pr.pay_channel,
    pr.pay_amount AS real_pay_amount
FROM t_order o
INNERJOIN t_order_item oi ON o.id = oi.order_id
LEFTJOIN t_pay_record pr ON o.id = pr.order_id AND pr.status = 2
WHERE o.user_id = 123456
    AND o.create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
ORDERBY o.create_time DESC
LIMIT20;

改成 INNER JOIN t_order_item 的理由：订单商品明细表不可能没有数据，而且我们需要商品信息，LEFT JOIN 没有意义。

4.2 分步查询 vs 联表查询

如果数据量大、JOIN 多，可以考虑分步查询：

-- 第一步：查询主订单（已优化）
SELECTid, order_no, status, total_amount, pay_amount, pay_time, create_time
FROM t_order
WHERE user_id = 123456
    AND create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
ORDERBY create_time DESC
LIMIT20;

-- 拿到 20 个订单 ID 后，第二步：查询商品明细
SELECTid, order_id, goods_name, price, quantity, subtotal
FROM t_order_item
WHERE order_id IN (?, ?, ?, ...)
    AND user_id = 123456;  -- 加 user_id 过滤，利用覆盖索引

-- 第三步：查询支付记录
SELECT order_id, pay_no, pay_channel, pay_amount
FROM t_pay_record
WHERE order_id IN (?, ?, ?, ...)
    ANDstatus = 2;

分步查询的优势：

• 每一步都可以独立利用最优索引
• 避免了 MySQL 优化器选错 JOIN 顺序的问题
• 可以在应用层做并行查询（三个查询同时发往数据库）
• 如果某一步查到 0 条，后续查询可以直接跳过

分步查询的劣势：

• 网络往返次数增加
• 需要在应用层组装数据
• 如果 IN 列表很长，需要拆分成多个批次

4.3 使用延迟关联优化分页

分页查询在深度翻页时（OFFSET 很大）性能急剧下降，因为 MySQL 需要先扫描到 OFFSET 位置才能返回 LIMIT 数据。

延迟关联的思路：先在索引中定位到目标行，再关联回表获取所有字段：

-- 原始慢分页（OFFSET 10000, LIMIT 20）
SELECT * FROM t_order
WHERE user_id = 123456
ORDERBY create_time DESC
LIMIT10000, 20;
-- 扫描 10020 行，只返回 20 行

-- 延迟关联优化
SELECT o.id, o.order_no, o.status, o.total_amount, o.pay_amount,
       o.pay_time, o.create_time
FROM t_order o
INNERJOIN (
    SELECTidFROM t_order
    WHERE user_id = 123456
      AND create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
    ORDERBY create_time DESC
    LIMIT10000, 20
) AS t USING(id);
-- 内层只扫描索引（覆盖索引），只返回 20 个 ID
-- 外层用这 20 个 ID 查主表，回表 20 次

如果总共有 50000 条订单，普通分页在第 500 页时需要扫描 50020 行；延迟关联只需要扫描索引 50020 行（仍然要扫描这么多），但实际回表只有 20 次。这里有个权衡：延迟关联减少的是回表次数，但无法减少索引扫描行数。真正的优化手段是禁止深度翻页，引导用户按时间范围或 ID 区间翻页。

第 5 步：配置参数调优

5.1 调整 innodb_buffer_pool_size

InnoDB 的缓存池大小直接影响查询性能。如果缓存池够大，热数据都在内存里，就不需要频繁磁盘 I/O。

# 查看当前设置
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

# 查看缓存池实例数
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

# 推荐设置：物理内存的 60-80%，但要给操作系统留足内存
# 假设物理机 64GB，MySQL 留 48GB
# 在 my.cnf 中设置：
# innodb_buffer_pool_size = 48G
# innodb_buffer_pool_instances = 4  # 4-8 个实例，减少锁竞争

5.2 开启 Query Cache 的替代方案

MySQL 5.7 中 Query Cache 已经被移除（8.0 彻底删除）。不要试图开启 Query Cache。

在 MySQL 5.6/5.7 中，query_cache_type = 0 或 query_cache_size = 0。

替代方案：

• 应用层缓存（Redis）
• 使用 ANALYZE TABLE 保持统计信息准确
• 使用 prepared statements 减少解析开销

5.3 调整 sort_buffer_size

sort_buffer_size 控制 filesort 操作的内存缓冲区大小。如果排序数据量超过这个值，会使用临时文件排序。

-- 查看当前值
SHOWVARIABLESLIKE'sort_buffer_size';  -- 默认 262144（256KB）

-- 适当调大（对每个连接有效，不需要全局太大）
-- SET GLOBAL sort_buffer_size = 1048576;  -- 1MB

注意：这个值不是越大越好。每个连接都会分配这么大的内存，如果并发连接数很多，内存会被耗尽。合理值是 1-4MB。

5.4 调整 read_rnd_buffer_size

read_rnd_buffer_size 用于 MySQL 读取排序后的结果集。如果值太小，MySQL 需要多次读取。

SHOWVARIABLESLIKE'read_rnd_buffer_size';  -- 默认 262144（256KB）

合理值：2-4MB。

第 6 步：完整优化后的 SQL 和验证

优化后的完整 SQL

SELECT
    o.id AS order_id,
    o.order_no,
    o.status,
    o.total_amount,
    o.pay_amount,
    o.pay_time,
    o.create_time,
    oi.id AS item_id,
    oi.goods_name,
    oi.price,
    oi.quantity,
    oi.subtotal,
    pr.pay_no,
    pr.pay_channel,
    pr.pay_amount AS real_pay_amount
FROM t_order o
INNERJOIN t_order_item oi ON o.id = oi.order_id
LEFTJOIN t_pay_record pr ON o.id = pr.order_id AND pr.status = 2
WHERE o.user_id = 123456
    AND o.create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
    AND o.status != 5-- 排除已取消订单
ORDERBY o.create_time DESC
LIMIT20;

新建索引汇总

-- t_order 的覆盖索引
ALTERTABLE t_order ADDINDEX idx_user_time_covering (
    user_id, create_time DESC, status, id, order_no,
    total_amount, pay_amount, pay_time
);

-- t_order_item 的覆盖索引
ALTERTABLE t_order_item ADDINDEX idx_order_user_covering (
    order_id, user_id, goods_name, price, quantity, subtotal
);

-- t_pay_record 的覆盖索引
ALTERTABLE t_pay_record ADDINDEX idx_order_status_covering (
    order_id, status, pay_no, pay_channel, pay_amount
);

优化前后对比

验证方法

-- 1. EXPLAIN 确认执行计划
EXPLAINSELECT ... (优化后的 SQL)

-- 2. 使用 ANALYZE TABLE 更新统计信息
ANALYZETABLE t_order;
ANALYZETABLE t_order_item;
ANALYZETABLE t_pay_record;

-- 3. 实际执行并计时
SET profiling = 1;
-- 执行 SQL
SELECT ... (优化后的 SQL)
SHOWPROFILES;

-- 4. 查看执行时间分解
SHOW PROFILE FORQUERY1;
-- CPU, Memory, I/O 各阶段耗时

-- 5. 查看优化后的索引使用情况
SHOWINDEXFROM t_order;

第 7 步：写入部署与回滚

部署前准备

-- 1. 备份原表（生产环境必做）
mysqldump -h 192.168.1.100 -u root -p \
    --single-transaction \
    --quick \
    --databases your_database \
    --tables t_order t_order_item t_pay_record \
    > /backup/before_optimize_$(date +%Y%m%d).sql

-- 2. 在测试环境验证（必须）
-- 在测试库执行同样的索引变更和 SQL 验证

分步执行

-- 1. 在低峰期执行索引创建（MySQL 5.6+ 支持 Online DDL）
-- 使用 pt-online-schema-change（Percona Toolkit）可以做更安全的变更

-- 直接执行（MySQL 5.6+ 默认支持 Online DDL）
ALTERTABLE t_order ADDINDEX idx_user_time_covering (
    user_id, create_time DESC, status, id, order_no,
    total_amount, pay_amount, pay_time
), ALGORITHM=INPLACE, LOCK=NONE;

-- 2. 验证索引是否创建成功
SHOWINDEXFROM t_order;

-- 3. 用 EXPLAIN 验证 SQL 使用了新索引
EXPLAINSELECT ... (优化后的 SQL)

-- 4. 在测试环境跑一遍完整的业务回归
-- 确保分页、排序、关联数据都正确

回滚方案

-- 如果出现问题，删除新增的索引
ALTERTABLE t_order DROPINDEX idx_user_time_covering;
ALTERTABLE t_order_item DROPINDEX idx_order_user_covering;
ALTERTABLE t_pay_record DROPINDEX idx_order_status_covering;

-- 如果表结构被改坏，用备份恢复
-- mysql -u root -p < /backup/before_optimize_20260519.sql

常见慢查询优化套路汇总

套路 1：SELECT * 改为只查必要字段

* 会导致回表。明确列出字段，可以利用覆盖索引避免回表。

-- 慢
SELECT * FROM t_order WHERE user_id = 123;

-- 快
SELECTid, order_no, status, create_time FROM t_order WHERE user_id = 123;

套路 2：隐式类型转换导致索引失效

字段类型和传入参数类型不一致时，MySQL 会做隐式类型转换，导致无法使用索引。

-- user_id 是 bigint，但传入字符串 '123456'
-- 慢：隐式转换
SELECT * FROM t_order WHERE user_id = '123456';

-- 快：类型匹配
SELECT * FROM t_order WHERE user_id = 123456;

套路 3：OR 条件导致索引失效

OR 条件中如果有字段没有索引，整个查询可能退化为全表扫描。

-- 假设 status 没有索引
-- 慢
SELECT * FROM t_order WHERE user_id = 123ORstatus = 2;

-- 快：用 UNION 拆分，每个条件独立使用索引
SELECT * FROM t_order WHERE user_id = 123
UNIONALL
SELECT * FROM t_order WHEREstatus = 2AND user_id != 123;

套路 4：IN 列表过大

-- 慢：IN 里 10000 个值
SELECT * FROM t_order WHEREidIN (1, 2, 3, ..., 10000);

-- 快：分批查询，每批 500-1000 个
SELECT * FROM t_order WHEREidIN (1, 2, ..., 500);
SELECT * FROM t_order WHEREidIN (501, 502, ..., 1000);
-- 在应用层并发请求

套路 5：COUNT(*) 慢

-- 慢：对大表 COUNT(*)
SELECTCOUNT(*) FROM t_order WHERE user_id = 123;

-- 快：维护计数器表（异步更新）
-- 优点：毫秒级返回
-- 缺点：可能有一定延迟

-- 快2：用 EXPLAIN 估算（MySQL 8.0+）
EXPLAINSELECTCOUNT(*) FROM t_order WHERE user_id = 123;
-- rows 列的值就是估算行数

套路 6：JOIN 顺序错误

MySQL 优化器不总是选择最优 JOIN 顺序。可以用 STRAIGHT_JOIN 强制按你指定的顺序 JOIN：

SELECTSTRAIGHT_JOIN
    o.id, oi.goods_name, pr.pay_no
FROM t_order o
INNERJOIN t_order_item oi ON o.id = oi.order_id
INNERJOIN t_pay_record pr ON o.id = pr.order_id
WHERE o.user_id = 123;

套路 7：GROUP BY 产生临时表和 filesort

-- 慢：GROUP BY 字段没有索引
SELECT user_id, COUNT(*) FROM t_order GROUPBY user_id;

-- 快：确保 GROUP BY 字段有索引
ALTERTABLE t_order ADDINDEX idx_user_id (user_id);

-- 快2：如果只需要统计不同 user_id 的数量
SELECTCOUNT(DISTINCT user_id) FROM t_order;

深度优化：MySQL 8.0 新特性和执行计划解读

MySQL 8.0 带来的查询优化器改进

MySQL 8.0 在查询优化器方面有显著改进，如果生产环境还在用 MySQL 5.6/5.7，建议升级。

1. 不可见索引（Invisible Indexes）

可以隐藏索引不让优化器使用，测试禁用某索引对性能的影响，不需要删除索引就能验证。

-- 创建不可见索引
CREATEINDEX idx_test ON t_order_demo (user_id) INVISIBLE;

-- 修改索引可见性
ALTERTABLE t_order_demo ALTERINDEX idx_test INVISIBLE;
ALTERTABLE t_order_demo ALTERINDEX idx_test VISIBLE;

-- 验证：查询时不会使用该索引
EXPLAINSELECT * FROM t_order_demo WHERE user_id = 1;
-- key 列不会显示 idx_test

实战用法：在生产环境测试新索引的影响，不需要 DROP INDEX（危险操作），只需要改成 INVISIBLE，观察查询性能是否下降。如果没下降，说明这个索引不被需要，可以安全删除。

2. 直方图统计信息

MySQL 8.0 引入了直方图（histogram）统计信息，用于估算过滤条件的选择性。

-- 为字段创建直方图
ANALYZETABLE t_order_demo UPDATE HISTOGRAM ONstatus, amount;

-- 查看直方图信息
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME = 't_order_demo';

-- 删除直方图
ANALYZETABLE t_order_demo DROP HISTOGRAM ONstatus;

直方图对于非索引列的 WHERE 条件特别有用，能帮助优化器更准确地估算行数，选择更好的执行计划。

3. 窗口函数（Window Functions）

MySQL 8.0 支持窗口函数，很多原来需要子查询或自连接的 SQL 可以更简洁高效。

-- 原来的写法：子查询查每个用户的订单排名
SELECT * FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITIONBY user_id ORDERBY create_time DESC) AS rn
    FROM t_order o
    WHERE user_id IN (1, 2, 3)
) AS t WHERE rn <= 10;

-- 优化后：直接用窗口函数，MySQL 8.0 可以更高效地执行
SELECT * FROM t_order
WHERE user_id IN (1, 2, 3)
ORDERBY user_id, create_time DESC;

4. CTD（Common Table Expression，公共表表达式）

-- 使用 WITH 语法简化复杂查询
WITH recent_orders AS (
    SELECTid, user_id, order_no, create_time
    FROM t_order
    WHERE create_time >= DATE_SUB(NOW(), INTERVAL30DAY)
)
SELECT
    ro.id,
    ro.order_no,
    ro.create_time,
    oi.goods_name,
    pr.pay_no
FROM recent_orders ro
INNERJOIN t_order_item oi ON ro.id = oi.order_id
LEFTJOIN t_pay_record pr ON ro.id = pr.order_id
WHERE ro.user_id = 123
ORDERBY ro.create_time DESC
LIMIT20;

CTE 让 SQL 更可读，而且 MySQL 8.0 的 CTE 优化器会对查询进行物化，避免重复计算。

执行计划的进阶分析

1. EXPLAIN FORMAT=JSON

JSON 格式的 EXPLAIN 能看到更详细的成本估算：

EXPLAINFORMAT=JSONSELECT o.id, o.order_no
FROM t_order o
WHERE o.user_id = 1
ORDERBY o.create_time DESC
LIMIT20\G

输出示例（关键部分）：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1847.35"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "o",
        "access_type": "ref",
        "key": "idx_user_time",
        "rows_examined_per_scan": 156,
        "rows_produced_per_join": 156,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1520.00",
          "eval_cost": "156.00",
          "prefix_cost": "1847.35"
        }
      }
    }
  }
}

关键成本字段：

• query_cost：总查询成本，越低越好
• read_cost：读取数据的成本
• eval_cost：评估条件的成本
• prefix_cost：当前步骤的累计成本

2. 查看 optimizer trace

MySQL 优化器是如何做出决策的？optimizer_trace 能看到完整的推理过程：

-- 开启 optimizer trace
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000;

-- 执行查询
SELECT o.id, o.order_no, o.status
FROM t_order o
WHERE o.user_id = 1
ORDERBY o.create_time DESC
LIMIT20;

-- 查看优化器的推理过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 关闭
SET optimizer_trace = 'enabled=off';

optimizer_trace 输出非常详细，包括：

• 优化器考虑了哪些执行计划
• 每个计划的成本估算
• 为什么不选择某个计划
• 索引条件下推（ICP）的执行过程

这对于理解”为什么优化器选错了索引”特别有用。

Buffer Pool 调优深度指南

InnoDB Buffer Pool 是 MySQL 最核心的内存区域，它的效率直接影响查询性能。

1. 查看 Buffer Pool 使用情况

-- 缓存池整体状态
SHOWENGINEINNODBSTATUS\G

-- 关注以下指标（在 STATUS 输出中）：
-- Total memory allocated = xxx; Buffer pool size = xxx pages
-- Free buffers = xxx (空闲页)
-- Database pages = xxx (已用数据页)
-- Modified db pages = xxx (脏页数)
-- Pending reads = xxx / Pending writes = xxx (等待中的读/写请求)

-- 更详细的统计（MySQL 5.7+）
SHOWSTATUSLIKE'Innodb_buffer_pool%';

关键指标：

• Innodb_buffer_pool_pages_total：总页数
• Innodb_buffer_pool_pages_free：空闲页数
• Innodb_buffer_pool_pages_dirty：脏页数
• Innodb_buffer_pool_read_requests：读请求次数
• Innodb_buffer_pool_reads：从磁盘读的次数（未命中缓存）
• Innodb_buffer_pool_wait_free：等待空闲页的次数（表示缓存池已满且需要读取新页）

命中率计算：

-- 计算缓存命中率
SELECT
    (1 - (b.pread - b.pread_local) / bpread_total) * 100AS read_hit_rate
FROM (
    SELECT
        VARIABLE_VALUE AS pread,
        0AS pread_local,
        0 ASpread_total
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) a,
(
    SELECT
        0AS pread,
        0AS pread_local,
        VARIABLE_VALUE ASpread_total
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) b;

正常情况下命中率应该在 95% 以上。如果低于 90%，说明 Buffer Pool 不够用，需要增大。

2. 预热 Buffer Pool

MySQL 重启后，Buffer Pool 是空的，所有查询都要从磁盘读。需要预热：

-- 方案1：手动加载热点数据（MySQL 5.6+）
-- 设置 innodb_buffer_pool_dump_at_shutdown = ON，重启前自动保存热点页列表
SETGLOBAL innodb_buffer_pool_dump_now = ON;

-- 设置 innodb_buffer_pool_load_at_startup = ON，重启后自动加载
-- 在 my.cnf 中启用

-- 方案2：手动 SQL 预热（指定热点查询）
-- 执行你最常用的查询，让数据进入缓存
SELECTCOUNT(*) FROM t_order;
SELECT * FROM t_order WHERE user_id IN (1, 2, 3, ...) LIMIT10000;

-- 方案3：用 sys schema 的存储过程预热（MySQL 5.7+）
-- CALL sys.ps_trace_thread('/tmp/trace.log', 60);

3. 多个 Buffer Pool 实例

Buffer Pool 太大时会有锁竞争。MySQL 5.7 支持多个实例，每个实例独立加锁，减少并发访问争用：

# 在 my.cnf 中配置
# innodb_buffer_pool_instances = 4  # 建议 4-8 个实例
# innodb_buffer_pool_size = 48G     # 每个实例大约 12G

# 验证
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

配置原则：

• innodb_buffer_pool_instances 建议设置为 CPU 核心数，但不超过 16
• innodb_buffer_pool_size 必须能被 innodb_buffer_pool_instances 整除
• 每个实例至少 1GB 才有效

生产环境操作规范

操作前的检查清单

-- 1. 确认当前执行的 SQL
SHOWPROCESSLIST;
-- 找到慢查询对应的连接 ID

-- 2. 确认表的数据量和索引
SHOWTABLESTATUSLIKE't_order';
SHOWINDEXFROM t_order;

-- 3. 确认没有长时间运行的事务
SELECT * FROM information_schema.INNODB_TRX;
-- 如果有未提交的事务，ALTER TABLE 会等待锁

-- 4. 备份
mysqldump -h 192.168.1.100 -u root -p \
    --single-transaction --quick \
    --databases your_database --tables t_order \
    > /backup/t_order_$(date +%Y%m%d).sql

-- 5. 在测试环境执行同样的操作

在线 DDL 的正确姿势

MySQL 5.6+ 支持在线 DDL（添加索引时不需要锁表），但仍需注意：

-- 正确的方式：ALGORITHM=INPLACE, LOCK=NONE
ALTERTABLE t_order
ADDINDEX idx_user_time (user_id, create_time),
ALGORITHM=INPLACE, LOCK=NONE;

-- 如果不支持在线 DDL（老版本或特殊场景），用 pt-online-schema-change
pt-online-schema-change \
    --alter "ADD INDEX idx_user_time (user_id, create_time)" \
    --execute \
    --charset=utf8mb4 \
    --chunk-size=1000 \
    --max-load="Threads_running=50" \
    D=yourdatabase,t=t_order

pt-online-schema-change 的工作原理：

1. 创建新表（带新索引）
2. 在原表上创建触发器，把新写入的数据同步到新表
3. 分批把原表数据复制到新表（每次 1000 行）
4. rename 新表为原表名，删除原表

风险提醒：pt-online-schema-change 在数据量极大时（数千万行），触发器会带来额外的写入开销，可能影响正常业务。建议在低峰期操作，并设置 --max-load 限制并发。

验证优化效果的标准流程

-- 步骤1：执行 EXPLAIN，确认执行计划正确
EXPLAINSELECT ... (优化后的 SQL)

-- 步骤2：执行 ANALYZE TABLE，更新统计信息
ANALYZETABLE t_order;
ANALYZETABLE t_order_item;
ANALYZETABLE t_pay_record;

-- 步骤3：用 EXPLAIN 再次确认
EXPLAINSELECT ... (优化后的 SQL)

-- 步骤4：用 SQL_NO_CACHE 确保不走查询缓存
SELECT SQL_NO_CACHE ... (优化后的 SQL)

-- 步骤5：用 SHOW PROFILE 看各阶段耗时
SET profiling = 1;
SELECT ... (优化后的 SQL)
SHOWPROFILES;
SHOW PROFILE FORQUERY1;

-- 步骤6：用 EXPLAIN ANALYZE 验证实际执行成本（MySQL 8.0+）
EXPLAINANALYZESELECT ... (优化后的 SQL)

总结

MySQL 慢查询优化的核心在于三件事：

第一，知道 SQL 实际在做什么。通过 EXPLAIN、EXPLAIN ANALYZE、慢查询日志、profiling 工具，把 SQL 的执行过程拆解清楚，知道每一步是在扫描全表还是索引扫描，是在内存排序还是文件排序，是回表 1 次还是回表 1 万次。

第二，让索引匹配查询语义。索引设计的核心是：最左前缀匹配、覆盖索引减少回表、避免 filesort。对于 WHERE、ORDER BY、LIMIT 共存的查询，尽量让索引同时覆盖这三者。

第三，改写 SQL 而不是只会调参数。参数调优（buffer pool、sort buffer 等）只能锦上添花，真正决定性能的是索引设计和 SQL 写法。如果 SQL 写得差，再大的 buffer pool 也救不了。

第四，关注 MySQL 8.0 的新特性。不可见索引、直方图、窗口函数、CTE、执行计划 JSON 格式、optimizer trace 等新工具，让优化过程更精准、更可控。

优化完成后，必须在测试环境验证执行计划、业务逻辑正确性、性能提升幅度，然后才能上生产。生产环境操作要备份、回滚预案、低峰期执行。

版权申明：内容来源网络，版权归原创者所有，如有侵权请联系删除

想了解更多干货，可通过下方扫码关注

可扫码添加上智启元官方客服微信👇