数据库基础体系 · 第 17/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL 查询优化：EXPLAIN、统计信息、Join、排序和慢日志

查询优化不是“给 SQL 加一个索引”这么简单。一个查询从提交到返回，通常经历以下过程：

解析 SQL，确定表、列、谓词和语义约束；
根据索引、统计信息和代价模型生成候选执行计划；
选择访问路径和 Join 顺序；
执行索引查找、记录读取、回表、Join、排序或聚合；
在事务和锁规则下完成一致性读取；
由慢查询日志或运行时监控记录结果。

EXPLAIN 主要回答“优化器打算怎么执行”，统计信息影响“优化器为什么这样选择”，Join 和排序解释“执行计划中最耗时的算子如何工作”，慢日志则回答“生产环境中哪些实际请求值得调查”。

一、先明确查询优化的边界

1. InnoDB 中一次普通索引查询发生了什么

以 InnoDB 为例，表数据存储在聚簇索引中。通常情况下：

主键索引的叶子节点直接保存整行数据；
二级索引的叶子节点保存二级索引列和主键值；
通过二级索引找到主键后，再访问聚簇索引取得其他列，这一步通常称为“回表”。

例如：

SELECT name, status
FROM user
WHERE email = 'a@example.com';

假设 email 上有二级索引，但索引中没有 name 和 status：

在 email 二级索引中定位 'a@example.com'；
取出对应的主键；
根据主键访问聚簇索引；
读取整行，再返回 name 和 status。

如果查询只需要索引中已经包含的列，就可能成为覆盖索引查询，不需要回表：

SELECT id, email
FROM user
WHERE email = 'a@example.com';

这里的“可能”很重要。是否覆盖取决于索引定义、查询列和优化器实际选择的访问路径，不能只凭 SQL 文本判断。

2. 优化的目标不是“扫描行数最少”这一项

一个执行计划的成本通常受到多个因素影响：

需要读取多少索引页和数据页；
随机访问还是顺序访问；
是否需要回表；
Join 的外表和内表分别有多少行；
是否需要建立临时表；
是否需要排序；
过滤条件在多早的阶段生效；
是否受到锁等待、磁盘读取或 Buffer Pool 命中率影响。

可以用一个简化模型表示：

C \approx C_{\text{page read}} + C_{\text{row filter}} + C_{\text{join}} + C_{\text{sort}} + C_{\text{temporary}}

其中：

$C_{\text{page read}}$ 是读取索引页和数据页的成本；
$C_{\text{row filter}}$ 是取行后判断条件的成本；
$C_{\text{join}}$ 是多表匹配的成本；
$C_{\text{sort}}$ 是排序成本；
$C_{\text{temporary}}$ 是临时表或中间结果处理成本。

真实 MySQL 成本模型比这个公式更复杂，而且成本值不是毫秒数。它用于比较候选计划，不保证等于最终墙上时延。

二、EXPLAIN：查看优化器选择的计划

1. 基本用法

EXPLAIN
SELECT u.id, u.name
FROM user AS u
WHERE u.email = 'a@example.com';

典型的传统格式可能包含：

列	含义
`id`	查询块编号
`select_type`	查询块类型
`table`	当前访问的表或派生表
`partitions`	使用的分区
`type`	访问方法
`possible_keys`	优化器认为可能使用的索引
`key`	实际选择的索引
`key_len`	使用的索引长度
`ref`	与索引比较的值或列
`rows`	预计读取的行数
`filtered`	预计通过当前条件的百分比
`Extra`	额外执行信息

传统格式适合快速查看，但复杂查询应优先使用 JSON 或树状格式：

EXPLAIN FORMAT=JSON
SELECT u.id, u.name
FROM user AS u
WHERE u.email = 'a@example.com';

EXPLAIN FORMAT=TREE
SELECT u.id, u.name
FROM user AS u
WHERE u.email = 'a@example.com';

不同格式展示的字段和层次不同。不要把某一列的文本当成跨版本永远不变的接口；应结合当前 MySQL 版本的官方语义解释。

2. `type` 的含义不能机械排名

常见访问类型包括：

const：通过唯一索引或主键等值定位至多一行；
eq_ref：Join 时，前一张表的每一行通过唯一索引匹配当前表至多一行；
ref：通过非唯一索引等值匹配多行；
range：索引范围扫描，例如 >, <, BETWEEN, IN；
index：扫描整个索引；
ALL：扫描整张表。

通常从 const 到 ALL，单次访问的预期成本可能增加，但这不是绝对的性能排序。

例如，表只有 20 行时，ALL 可能比走索引更便宜。反过来，即使显示为 range，如果范围包含了大部分表，也可能非常慢。

以下 SQL 用于检查实际表结构：

SHOW CREATE TABLE user\G
SHOW INDEX FROM user;

possible_keys 只是候选集合，不代表一定会使用；key 为 NULL 也不一定是错误，可能是全表扫描成本更低，或者谓词无法有效使用索引。

3. `rows` 和 `filtered` 是估算值

假设计划显示：

rows = 10000
filtered = 10.00

可以粗略理解为：

10000 \times 10\% = 1000

优化器预计当前步骤读取约 10000 行，其中约 1000 行通过剩余过滤条件。

但这不是执行结果：

rows 不是实际读取行数；
filtered 是估算比例；
估算误差会继续影响 Join 顺序和 Join 算法选择。

如果统计信息错误，优化器可能认为某个条件非常有选择性，实际却会匹配大量数据。

4. `key_len` 反映实际使用了多少索引前缀

假设有索引：

CREATE INDEX idx_order_status_created
ON orders(status, created_at);

查询：

SELECT id
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2025-01-01';

可能同时使用 status 和 created_at 两个索引部分。

而下面的查询：

SELECT id
FROM orders
WHERE created_at >= '2025-01-01';

通常不能直接利用这个复合索引的最左列 status 来建立有效的范围定位。

这就是复合索引的最左前缀规则：索引首先按 (status, created_at) 排序，不能跳过第一列后直接把第二列当成连续的索引入口。

不过“没有使用第二列”不应只看 key_len。等值条件、范围条件、索引下推和剩余过滤可能使不同版本的输出细节有所不同，应结合 JSON 或 TREE 计划判断。

5. `Extra` 中的关键信息

常见内容包括：

Using where：取到行后还要执行条件过滤；
Using index：通常表示覆盖索引访问；
Using index condition：使用索引条件下推，在存储引擎层进一步过滤；
Using filesort：需要额外排序操作；
Using temporary：使用临时表或临时中间结构；
Using join buffer：Join 使用了 Join 缓冲相关机制。

Using filesort 不等于“使用了磁盘文件”，后文会说明。Using temporary 也不自动等于错误，例如某些合法的分组和排序天然需要中间结果。

三、EXPLAIN ANALYZE：把估算与实际执行对照

普通 EXPLAIN 不执行查询。MySQL 8.0.18 及之后提供的 EXPLAIN ANALYZE 会实际执行可解释的查询，并报告迭代器的实际行数和耗时。

EXPLAIN ANALYZE
SELECT u.id, u.name
FROM user AS u
WHERE u.email = 'a@example.com';

典型输出会包含类似信息：

-> Index lookup on u using idx_user_email (email='a@example.com')
   (cost=..., rows=1) (actual time=... rows=1 loops=1)

需要区分：

cost、rows：执行前的估算；
actual time：实际运行时观察到的耗时；
actual rows：实际输出行数；
loops：该迭代器被执行的次数。

Join 中尤其要看 loops。例如内表显示：

(actual time=... rows=20 loops=1000)

这意味着内表访问被重复执行约 1000 次，累计处理量可能是 20000 行，而不是只看单次的 20 行。

使用 `EXPLAIN ANALYZE` 的风险

它不是只读的性能采样命令，而是会真正运行查询。因此：

大查询会消耗 CPU、I/O 和锁资源；
查询可能等待其他事务；
读取可能影响 Buffer Pool；
在不适合的语句上执行会产生业务副作用。

应优先在测试环境、只读副本或受控时间窗口执行。即使查询本身是 SELECT，它也会真实访问数据；如果查询包含用户自定义函数、锁定读取等特性，还应单独评估风险。

四、统计信息：优化器如何估计数据分布

1. 优化器并不逐行试跑所有方案

考虑：

SELECT o.id
FROM orders AS o
WHERE o.status = 'PAID'
  AND o.created_at >= '2025-01-01';

优化器可能比较：

使用 status 索引；
使用 created_at 索引；
使用 (status, created_at) 复合索引；
扫描整表；
先读取一个范围，再回表过滤另一个条件。

如果它知道：

status = 'PAID' 只匹配 0.1% 的记录；
created_at >= ... 匹配 60% 的记录；

则更可能选择 status 作为入口。若实际数据是 95% 都为 PAID，错误统计信息就可能导致错误决策。

2. InnoDB 持久化统计信息

InnoDB 使用统计信息估算索引基数和数据分布。常见相关设置包括：

SHOW VARIABLES LIKE 'innodb_stats_persistent%';
SHOW VARIABLES LIKE 'innodb_stats_method';

在启用持久化统计信息时，统计信息不会只存在于内存中，而会持久化到数据字典相关位置。表级设置也可以通过建表或修改表属性指定，例如：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_customer_created (customer_id, created_at),
    KEY idx_status_created (status, created_at)
) ENGINE = InnoDB
  STATS_PERSISTENT = 1;

具体统计信息何时自动更新，与表变化量和相关参数有关。不要假设每次 INSERT 都立即重算统计信息，也不要假设重启后统计信息一定丢失。

3. 手动更新统计信息

ANALYZE TABLE orders;

这会重新收集表和索引统计信息。执行前应确认：

当前账号有权限；
目标表大小和业务负载；
当前版本对 ANALYZE TABLE 的并发和元数据锁行为；
是否有副本延迟或运维窗口要求。

它不是“强制让某个索引生效”的命令。它只改变优化器可用的信息，最终计划仍由代价比较决定。

4. 为什么基数估算可能不准

索引统计信息通常基于采样页，而不是扫描全部数据。以下情况容易产生误差：

数据高度倾斜，例如 99% 的行都是同一个状态；
数据刚发生大规模导入或删除；
多列之间存在强相关性；
表较大而采样规模有限；
条件使用了函数、隐式类型转换或复杂表达式。

例如：

WHERE status = 'PAID'
  AND region = 'CN'

如果 status 和 region 存在相关性，分别估算两个条件再相乘可能不准确。简化地说，优化器可能使用：

P(A \land B) \approx P(A) \times P(B)

但只有在近似独立时这个推断才合理。

5. 直方图适合修正单列分布倾斜

MySQL 支持为列建立直方图，使优化器获得比普通索引基数更细的单列分布信息：

ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status
  WITH 10 BUCKETS;

查看：

SELECT *
FROM information_schema.column_statistics
WHERE schema_name = DATABASE()
  AND table_name = 'orders'
  AND column_name = 'status'\G

删除：

ANALYZE TABLE orders
  DROP HISTOGRAM ON status;

直方图不是索引，不能直接用来定位行，也不能代替复合索引。它主要改善选择性估算，尤其适合“列值分布严重倾斜，但不一定适合建立索引”的情况。

修改统计信息后必须重新验证：

EXPLAIN FORMAT=TREE
SELECT ...

必要时使用 EXPLAIN ANALYZE 对照估算和实际。统计信息改变计划并不保证查询一定更快，因为新计划可能改善一个条件，却使另一个查询变差。

五、Join：先确定连接语义，再分析执行顺序

1. SQL 中的 Join 不是固定执行顺序

下面的 SQL：

SELECT o.id, c.name
FROM orders AS o
JOIN customer AS c
  ON c.id = o.customer_id
WHERE o.status = 'PAID';

逻辑上表示：

找到满足 o.status = 'PAID' 的订单；
按 o.customer_id = c.id 连接客户；
返回所需列。

但优化器可能选择：

先扫描订单，再按客户主键查找客户；
先扫描客户，再查找订单；
先使用其他条件缩小某一侧；
在合适条件下使用哈希 Join。

SQL 书写顺序不等于物理执行顺序。EXPLAIN 中表的顺序才是一个重要线索。

2. Nested-loop Join 的推导

最常见的 Join 思路可以抽象为 Nested-loop：

for each row in outer_table:
    find matching rows in inner_table

若外表有 $N$ 行，内表连接列有索引，且每次索引查找成本近似为 $C_{\text{lookup}}$ ，则成本可近似为：

C \approx C_{\text{scan outer}} + N \times C_{\text{lookup}}

这解释了两个重要结论：

外表行数估错，会放大内表访问成本；
给内表连接列建立合适索引很重要。

例如 orders.customer_id 没有索引，客户表作为外表时，可能对每个客户扫描订单表，形成非常高的成本。

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

若同时有过滤和排序需求：

CREATE INDEX idx_orders_status_customer_created
ON orders(status, customer_id, created_at);

也不能仅凭列出现顺序决定索引。需要根据查询的等值条件、范围条件、Join 条件、排序和回表需求综合设计。

3. `eq_ref`、`ref` 与一对多关系

如果 customer.id 是主键：

c.id = o.customer_id

对于每一个订单，最多匹配一个客户，客户侧常见为 eq_ref。

如果连接列不是唯一列：

SELECT ...
FROM customer c
JOIN customer_tag t
  ON t.customer_id = c.id;

一个客户可以有多个标签，标签侧通常是 ref，因为一次连接可能产生多行。

Join 的结果行数还受关系基数影响。若外表有 1000 行，每行平均匹配 20 个内表行，Join 中间结果就可能接近 20000 行，即使最终 SELECT 只返回少量列，也不能忽略中间结果成本。

4. Hash Join 的适用边界

MySQL 8.0.18 引入了 Hash Join 能力。在某些没有合适索引、且连接条件适合哈希匹配的场景，优化器可能选择：

读取一侧数据；
按连接键建立哈希表；
扫描另一侧；
用连接键查找哈希表中的匹配项。

其成本直觉近似为：

C_{\text{hash}} \approx C_{\text{build}} + C_{\text{probe}}

相比 Nested-loop 的 $N \times C_{\text{lookup}}$ ，Hash Join 可能减少大量随机索引查找。但它需要构造中间结构，受内存和数据量影响；并不是“没有索引就一定更好”。

外连接、非等值连接、排序需求和结果基数都会影响可选算法。应以实际执行计划为准，而不是看到 Hash Join 就认定它优于索引 Join。

5. `LEFT JOIN` 的条件位置有语义差异

这两个查询不等价：

-- 条件放在 ON 中
SELECT c.id, o.id
FROM customer AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'PAID';

它保留没有 PAID 订单的客户，并将订单列补为 NULL。

-- 条件放在 WHERE 中
SELECT c.id, o.id
FROM customer AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.status = 'PAID';

第二个查询会过滤掉 o.status 为 NULL 的行，通常等价于更接近 Inner Join 的结果。

因此，不能为了让过滤“更早执行”而随意移动外连接条件。先保证结果语义，再讨论优化。

6. Join 的常见失败表现

连接条件缺失

SELECT *
FROM customer AS c
JOIN orders AS o;

这会产生笛卡尔积。若客户有 10000 行、订单有 100000 行，中间组合规模理论上可达：

10000 \times 100000 = 10^9

即使后续条件会过滤，数据库也可能先付出巨大的中间结果代价。

对连接列进行函数运算

ON DATE(o.created_at) = c.target_date

这可能阻碍对 created_at 普通索引的有效范围定位。可考虑改写为范围条件，但必须确认边界：

ON o.created_at >= c.target_date
AND o.created_at <  c.target_date + INTERVAL 1 DAY

这里还涉及时区、数据类型和日期边界，不能机械改写。

隐式类型转换

WHERE numeric_id = '123'

如果两侧类型不同，可能发生转换，影响索引使用或结果语义。应使比较两侧的数据类型一致，并检查字符集、排序规则和 NULL 语义。

六、排序：`ORDER BY` 为什么会触发 filesort

1. `Using filesort` 的准确含义

EXPLAIN
SELECT id, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC;

如果结果无法直接按照索引顺序产生，Extra 可能显示 Using filesort。

这里的 filesort 是 MySQL 对额外排序过程的称呼，不表示一定创建磁盘文件。排序可能在内存中完成，也可能因为中间结果或内存限制使用磁盘临时结构。

因此：

Using filesort 不等于必然慢；
不显示 Using filesort 也不保证整体很快；
真正要看输出行数、排序数据宽度、排序次数和 I/O。

2. 什么情况下索引可以直接提供顺序

假设有：

CREATE INDEX idx_orders_status_created_id
ON orders(status, created_at, id);

以下查询具备较好的索引顺序基础：

SELECT id, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY created_at, id;

原因是：

status 是等值条件；
在固定的 status = 'PAID' 分组内，索引剩余部分按 created_at, id 有序；
查询顺序与索引顺序一致；
如果需要的列都在索引中，还可能避免回表。

如果排序方向与索引定义不一致，MySQL 8.0 支持降序索引，可显式建立：

CREATE INDEX idx_orders_status_created_desc
ON orders(status, created_at DESC, id DESC);

但不要把“有降序索引”理解成所有混合方向都能直接利用。排序方向、列顺序、等值前缀、范围条件和 Join 访问方式必须共同满足索引有序输出的条件。

3. 范围条件会破坏后续列的连续排序能力

CREATE INDEX idx_a_b_c ON t(a, b, c);

查询：

WHERE a = 1
  AND b > 10
ORDER BY c;

索引中满足 a = 1, b > 10 的记录虽然按 (b, c) 排列，但不同 b 值之间会交错出现多个 c 范围。仅靠这个索引通常不能直接得到全局 c 排序，因此可能仍需排序。

这不是“索引里有 c 就一定不排序”。要判断的是：过滤后的记录在索引中是否已经按照要求的最终顺序连续产生。

4. Join 后排序更复杂

SELECT c.name, o.created_at
FROM customer AS c
JOIN orders AS o
  ON o.customer_id = c.id
WHERE c.region = 'CN'
ORDER BY o.created_at DESC;

即使 orders.created_at 有索引，Join 产生的结果也未必按 o.created_at 有序。因为执行过程可能先访问客户，再对每个客户查订单，多个局部有序结果合并后不一定是全局有序结果。

如果排序是主要成本，应在计划中检查：

Join 顺序；
排序发生在 Join 前还是 Join 后；
中间结果行数；
是否使用临时结构；
LIMIT 是否足够小以及是否能被有效下推。

5. `LIMIT` 可以减少排序成本，但不是万能药

SELECT id, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

优化器可能采用只保留前若干名的策略，而不是完整排序所有结果。但它仍需读取足够多的候选行，尤其当过滤条件选择性低且没有合适索引时。

分页查询中的深偏移也会放大成本：

ORDER BY created_at DESC
LIMIT 100000, 20;

数据库通常需要先找到并跳过前 100000 条，再返回 20 条。若业务允许，可使用基于排序键的游标分页：

SELECT id, created_at
FROM orders
WHERE status = 'PAID'
  AND (created_at, id) < ('2025-01-20 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

这里 id 用于打破 created_at 相同的并列，保证分页边界稳定。实际使用时应建立与过滤和排序匹配的复合索引，并确认 (created_at, id) 的比较语义与 NULL 情况。

七、一个完整的分析算例

建立示例表：

CREATE TABLE customer (
    id BIGINT PRIMARY KEY,
    region CHAR(2) NOT NULL,
    name VARCHAR(100) NOT NULL,
    KEY idx_customer_region (region)
) ENGINE = InnoDB;

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    KEY idx_orders_customer_created (customer_id, created_at, id),
    KEY idx_orders_status_created (status, created_at DESC, id DESC)
) ENGINE = InnoDB;

查询：

SELECT o.id, o.created_at, o.amount, c.name
FROM customer AS c
JOIN orders AS o
  ON o.customer_id = c.id
WHERE c.region = 'CN'
  AND o.status = 'PAID'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 20;

可以按以下步骤分析。

第一步：看语义和候选入口

过滤条件位于两张表：

customer.region = 'CN'；
orders.status = 'PAID'；
连接条件为 orders.customer_id = customer.id；
最终按订单时间排序。

可能的执行方向包括：

先过滤客户，再按客户查订单；
先过滤订单，再按客户主键查客户；
先使用订单状态和时间索引获取候选订单，再连接客户。

第二步：查看计划

EXPLAIN FORMAT=TREE
SELECT o.id, o.created_at, o.amount, c.name
FROM customer AS c
JOIN orders AS o
  ON o.customer_id = c.id
WHERE c.region = 'CN'
  AND o.status = 'PAID'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 20;

关注：

哪张表是外表；
orders 使用的是哪个索引；
customer_id 是否成为有效的连接查找条件；
是否出现排序；
估算的中间结果有多大。

第三步：比较估算和实际

EXPLAIN ANALYZE
SELECT o.id, o.created_at, o.amount, c.name
FROM customer AS c
JOIN orders AS o
  ON o.customer_id = c.id
WHERE c.region = 'CN'
  AND o.status = 'PAID'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 20;

假设观察到：

估算 region = 'CN' 返回 1000 个客户；
实际返回 90000 个客户；
内表访问 loops 接近 90000；
每次订单查找平均返回几十行；
最后排序处理了数百万行。

那么根因不是“排序慢”这一句，而可能是：

region 的统计信息严重失真；
Join 顺序导致大量重复订单查找；
status 与 customer_id 的索引组合不适合当前访问路径；
LIMIT 20 无法在 Join 和排序前有效缩小数据。

接下来应先验证：

ANALYZE TABLE customer, orders;

EXPLAIN FORMAT=JSON
SELECT ...;

如果数据分布仍然严重倾斜，再评估直方图或重新设计索引。不能直接使用 FORCE INDEX 掩盖统计信息问题，因为数据分布变化后，强制计划可能变成新的故障源。

八、慢查询日志：从生产实际请求中发现问题

1. 慢日志记录的是什么

慢查询日志记录满足条件的语句，例如执行时间超过 long_query_time 的查询。它的价值是发现生产环境真实发生过的慢请求，而不是只分析开发者手工挑选的 SQL。

查看相关配置：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

启用和设置示例：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

这类设置有作用域、权限和持久化边界。直接执行 SET GLOBAL 通常影响当前实例运行状态，但未必写入配置文件；重启后是否保留取决于部署方式和配置管理。生产环境应通过受控配置发布，并确认日志目录、磁盘容量和轮转策略。

2. 常见慢日志字段

典型记录可能包含：

# Query_time: 2.315  Lock_time: 0.004 Rows_sent: 20  Rows_examined: 350000
SELECT ...

字段直觉如下：

Query_time：语句处理耗时；
Lock_time：等待表级锁等锁相关时间字段，不能等同于所有 InnoDB 行锁等待；
Rows_sent：返回给客户端的行数；
Rows_examined：执行过程中检查的行数估计或统计值。

几个重要判断：

`Rows_sent` 小不代表查询成本小

Rows_sent: 10
Rows_examined: 1000000

这通常表示数据库检查了大量行，最后只返回 10 行，可能存在低选择性、缺少索引或排序/过滤位置不佳的问题。

慢不一定是执行计划慢

若 Query_time 很高而 Rows_examined 不大，可能是：

等待 InnoDB 行锁；
磁盘 I/O；
Buffer Pool 命中率低；
CPU 饱和；
网络发送或客户端读取缓慢；
元数据锁等待。

因此慢日志是入口，不是根因证明。

3. “没有使用索引的查询”日志有误导性

可以启用：

SET GLOBAL log_queries_not_using_indexes = ON;

但它不应被解释为“所有未用索引的查询都是错误”。以下查询可能合理地不使用索引：

表很小；
查询需要大部分数据；
使用索引会造成大量随机回表；
优化器判断全表扫描成本更低。

反过来，使用了索引的查询也可能很慢，例如：

范围过大；
二级索引回表次数过多；
Join 内表被重复查找；
排序处理了大量结果；
统计信息导致错误访问路径。

4. 慢日志与 EXPLAIN 如何串联

一个可靠的调查链路是：

慢日志发现具体 SQL
        ↓
确认参数、调用频率和时间分布
        ↓
在相同数据分布下执行 EXPLAIN
        ↓
检查 rows、Join 顺序、索引、排序和临时结构
        ↓
用 EXPLAIN ANALYZE 验证估算误差
        ↓
修改统计信息、SQL 或索引
        ↓
再次观察慢日志和业务指标

不要直接拿慢日志中的 SQL 文本在开发环境执行就下结论。计划依赖：

数据量；
数据分布；
MySQL 版本；
表结构；
统计信息；
参数类型；
Buffer Pool 状态；
并发和锁环境。

如果使用参数化 SQL，应保留实际参数类型和具有代表性的参数值。不同参数值可能触发不同计划，例如一个值只匹配一行，另一个值匹配数百万行。

九、锁等待与慢查询的区分

考虑两个事务：

-- 事务 A
START TRANSACTION;

UPDATE orders
SET status = 'PAID'
WHERE id = 1001;
-- 长时间不提交

-- 事务 B
START TRANSACTION;

UPDATE orders
SET status = 'CANCELLED'
WHERE id = 1001;

事务 B 的 SQL 可能没有复杂 Join，也没有排序，但会等待事务 A 持有的记录锁。此时慢日志可能记录较高的 Query_time，但用 EXPLAIN 并不能解释等待时间。

应结合 InnoDB 锁和事务观测信息，例如：

SHOW ENGINE INNODB STATUS\G

以及当前版本可用的 Performance Schema、information_schema.innodb_trx 等视图检查：

哪个事务持有锁；
哪个事务在等待；
等待的记录或索引范围；
持锁事务是否长时间未提交。

这也说明了标题中几个工具的边界：

EXPLAIN 解释执行计划；
统计信息解释估算；
慢日志发现慢请求；
锁监控解释并发等待；
它们不能互相替代。

十、从现象到根因的诊断方法

现象一：计划显示走索引，但仍然很慢

依次检查：

rows 估算是否远小于实际；
二级索引是否导致大量回表；
范围是否覆盖了大部分数据；
是否发生大量 Join loops；
是否有排序或临时结构；
是否存在锁等待或 I/O 压力。

“走索引”只说明访问入口，不说明访问量足够小，也不说明避免了回表。

现象二：`EXPLAIN` 看起来很快，生产却很慢

可能原因包括：

生产和测试数据分布不同；
生产统计信息过期；
生产 Buffer Pool 冷；
生产并发更高；
生产存在锁等待；
生产参数值触发了另一条更合适或更差的计划；
网络和客户端消费结果速度不同。

此时应采集实际慢日志、锁等待、实例资源和 EXPLAIN ANALYZE 结果，而不是只复制一份测试环境的 EXPLAIN。

现象三：加索引后另一类查询变慢

索引不是免费的：

占用磁盘和 Buffer Pool；
增加 INSERT、UPDATE、DELETE 的维护成本；
可能改变优化器的候选计划；
多个相似索引会增加管理复杂度。

例如原本全表扫描很适合的大范围报表，新增一个低选择性索引后，优化器可能尝试索引扫描和大量回表。应比较变更前后的计划、写入成本和整体工作负载，而不是只看一条查询。

十一、一个可执行的最小检查流程

对一条疑似慢 SQL，可以使用下面的顺序：

-- 1. 确认表结构和索引
SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;

-- 2. 查看传统计划
EXPLAIN
SELECT ...;

-- 3. 查看更完整的计划结构
EXPLAIN FORMAT=JSON
SELECT ...;

-- 4. 检查实际执行情况
EXPLAIN ANALYZE
SELECT ...;

-- 5. 必要时刷新统计信息
ANALYZE TABLE orders;

-- 6. 重新比较计划
EXPLAIN FORMAT=TREE
SELECT ...;

每一步都有不同目的：

SHOW CREATE TABLE 防止凭印象分析字段类型、主键和索引；
SHOW INDEX 查看索引列顺序和基数线索；
EXPLAIN 查看优化器计划；
EXPLAIN ANALYZE 判断估算是否偏离实际；
ANALYZE TABLE 更新统计信息，而不是强制计划；
最后重新验证修改是否改变了实际行为。

如果 SQL 来自生产慢日志，还应同时记录：

实际执行参数；
Query_time、Rows_examined 和 Rows_sent；
调用频率；
是否集中发生在高并发时段；
是否伴随锁等待、磁盘压力或副本延迟。

十二、结论：用证据连接估算、执行和生产现象

查询优化的核心不是背诵 EXPLAIN 中某个 type 的优劣，而是建立一条因果链：

表结构和 InnoDB 索引决定数据能否按某种路径被定位；
统计信息决定优化器如何估算选择性、Join 基数和排序成本；
代价模型据此选择访问路径、Join 顺序和排序方式；
EXPLAIN 展示计划，EXPLAIN ANALYZE 检查估算与实际的偏差；
慢日志提供生产中的真实样本；
锁、I/O、缓存和并发监控补足“计划之外”的等待原因。

当 rows 估算与实际差距很大时，优先调查统计信息和数据分布；当 Join 的 loops 很高时，调查外表规模和内表连接索引；当出现 Using filesort 时，判断排序数据量和索引顺序，而不是仅凭文字报警；当慢日志耗时高但扫描行数少时，检查锁等待和系统资源。

只有把执行计划、统计信息、数据访问、排序过程和生产运行状态放在同一个分析框架中，优化结果才不会停留在“看起来用了索引”。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。