MySQL复杂关联SQL优化实战：通过执行计划与索引重构降低耗时

问题分析与初步诊断

在处理一则涉及多表关联的SQL优化需求时，通常遵循以下四个核心步骤：评估结果集规模、审查驱动表选择、理清表间关联与索引，以及必要时强制连接顺序。

原始SQL语句涉及四张表（a, b, c, d）的复杂关联：

SELECT 
    c.yh_id, c.yh_dm, c.yh_mc, c.mm, c.yh_lx,
    a.jg_id, a.jg_dm, a.jg_mc, a.jgxz_dm,
    d.js_dm AS yh_js
FROM a, b, c
LEFT JOIN d ON d.yh_id = c.yh_id
WHERE a.jg_id = b.jg_id
  AND b.yh_id = c.yh_id
  AND a.yx_bj = 'Y'
  AND c.sc_bj = 'N'
  AND c.yx_bj = 'Y'
  AND c.yh_dm = '006939748XX';

该查询最终仅返回 1行数据，但初始执行时间为 0.75秒（750ms）。对于只有一行结果的查询而言，这显然存在严重的性能隐患。我们通过 EXPLAIN 查看执行计划以深入分析。

执行计划深度解析

执行 EXPLAIN 后，我们可以看到MySQL优化器选择的执行路径。重点关注扫描行数（rows）和访问类型（type）：

+----+-------------+-------+------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys   | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-----------------+---------+---------+------+------+-------------+
| 1  | SIMPLE      | a     | ALL  | PRIMARY         | NULL    | NULL    | NULL | 52616| Using where |
| 1  | SIMPLE      | b     | ref  | PRIMARY         | PRIMARY | 98      | test.a.JG_ID | 1 | Using index |
| 1  | SIMPLE      | c     | eq_ref| PRIMARY        | PRIMARY | 98      | test.b.YH_ID | 1 | Using where |
| 1  | SIMPLE      | d     | index| NULL            | PRIMARY | 196     | NULL   | 54584| Using index |
+----+-------------+-------+------+-----------------+---------+---------+------+------+-------------+

从执行计划中识别出两个明显的性能瓶颈：

1. 表a全表扫描：type为 ALL，扫描了52,616行数据。虽然最终过滤条件可能有效，但作为驱动表或中间表，如此大的基数会导致后续关联操作开销巨大。
2. 表d的全索引扫描：type为 index，尽管使用了覆盖索引（Using index），但仍需遍历整个主键索引树，扫描行数高达54,584行。这是由 LEFT JOIN 的特性决定的，MySQL必须读取d表中所有匹配c表条件的数据。

结合表数据量分析（a: ~53k, b: ~106k, c: ~54k），由于b表数据量大且缺乏过滤条件，优化器排除其作为驱动表；而c表存在高选择性过滤条件（yh_dm），理论上更适合做驱动表，但优化器仍选择了a表。我们需要介入优化。

第一阶段优化：完善索引减少扫描

首先分析当前的连接逻辑：a → b → c → d。在第三步 c ↔ d 的连接中，执行计划显示d表类型为 index，且 ref 列为 NULL，说明d表上缺少针对 yh_id 的有效索引，导致无法通过引用快速查找，只能扫描索引树。

为解决此问题，我们在d表的关联字段 yh_id 上创建索引：

ALTER TABLE d ADD INDEX ind_yh_id (yh_id);

再次查看执行计划：

+----+-------------+-------+------+---------------+-----------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref        | rows | Extra       |
+----+-------------+-------+------+---------------+-----------+---------+------------+------+-------------+
| 1  | SIMPLE      | a     | ALL  | ...           | NULL      | NULL    | NULL       | 52616| Using where |
| 1  | SIMPLE      | b     | ref  | PRIMARY       | PRIMARY   | 98      | test.a.JG_ID | 1 | Using index |
| 1  | SIMPLE      | c     | eq_ref| PRIMARY      | PRIMARY   | 98      | test.b.YH_ID | 1 | Using where |
| 1  | SIMPLE      | d     | ref  | ind_yh_id     | ind_yh_id | 98      | test.b.YH_ID| 272  | Using index |
+----+-------------+-------+------+---------------+-----------+---------+------------+------+-------------+

改进效果显著：d表的扫描行数从 54,584 降至 272，访问类型变为 ref。但由于最基础的驱动表a仍然是全表扫描，整体执行时间仅从 0.75s 微降至 0.77s（波动正常，主要受缓存等因素影响，逻辑更优但总耗时未根本改变）。

第二阶段优化：重构驱动表与连接顺序

为了进一步提速，我们需要让高选择性条件的表成为驱动表。c表上的 yh_dm = '006939748XX' 是最高效的过滤条件，若以此切入，可大幅减少关联基数。理想的新连接顺序应为：c → d / b → a（注意d为LEFT JOIN，通常依附于主查询主体）。

第1步：为c表添加过滤索引

虽然c表有主键，但为了加速起始点的定位，显式为 yh_dm 添加索引：

ALTER TABLE c ADD INDEX ind_yh_dm (yh_dm);

此时c表中符合条件的记录仅 2行。然而，重新生成的执行计划依然选择a表为驱动表。这是因为在旧逻辑中，b表缺乏 yh_id 索引，导致以c驱动b时成本高昂（假设的大基数关联）。优化器出于保守估计，认为先查a再查b更“安全”。

第2步：为b表添加关键关联索引

为了使c表能够高效驱动b表，必须在b表上建立 yh_id 的索引：

ALTER TABLE b ADD INDEX ind_yh_id (yh_id);

现在，b表既可以通过 jg_id 关联a，也可以通过 yh_id 关联c。这使得优化器可以灵活选择以c为起点的路径。

最终验证：效果对比

在所有索引到位后，观察新的执行计划：

+----+-------------+-------+-------+----------------------------------+-----------+---------+------------+------+-------------+
| id | select_type | table | type  | possible_keys                    | key       | key_len | ref        | rows | Extra       |
+----+-------------+-------+-------+----------------------------------+-----------+---------+------------+------+-------------+
| 1  | SIMPLE      | c     | ref   | PRIMARY,ind_yh_dm                | ind_yh_dm | 57      | const      | 2    | Using where |
| 1  | SIMPLE      | d     | ref   | ind_yh_id                        | ind_yh_id | 98      | test.c.YH_ID| 272  | Using index |
| 1  | SIMPLE      | b     | ref   | PRIMARY,ind_yh_id                | ind_yh_id | 98      | test.c.YH_ID| 531  | Using index |
| 1  | SIMPLE      | a     | eq_ref| PRIMARY,INDEX_JG                 | PRIMARY   | 98      | test.b.JG_ID| 1    | Using where |
+----+-------------+-------+-------+----------------------------------+-----------+---------+------------+------+-------------+

关键变化：

• 驱动表变更：c表率先使用 ind_yh_dm 索引定位，仅扫描 2行。
• 后续关联高效化：基于c表的2条数据，分别去关联d表（272行预估，实际精确匹配）、b表（531行预估）、最后通过b关联a（eq_ref，精确匹配）。
• 彻底消除全表扫描：表a不再是驱动表，不再进行5万行的全表扫描。

最终执行时间从最初的 0.75秒 降到了 0.00秒（毫秒级）。整个过程证明了在高复杂度JOIN场景中，合理的索引布局和对驱动表逻辑的理解，能带来数量级的性能提升。

以上就是MySQL在复杂关联情况下通过执行计划分析与索引优化降低耗时的详细内容，更多关于SQL性能调优的资料请关注本站其它相关文章！