深度解析 MySQL 慢 SQL 优化：从 EXISTS 到 UNION 的实战蜕变

在数据库性能优化领域，慢 SQL 的诊断与改写是开发与运维人员的必备技能。本文将通过一个真实业务场景中的 SQL 优化案例，系统拆解从问题定位到方案实施的完整流程，揭示 MySQL 查询优化的核心思路与实践技巧。

一、问题背景与原 SQL 分析

某电商平台的发票管理系统近期频繁收到业务侧反馈，称某查询接口响应时间长达 40 秒以上。开发人员提交的 SQL 语句如下：

 

SELECT COUNT(0)
FROM invoice_sales_application a
WHERE (
    shop_order_id LIKE '23060919546335%'
    OR (
        EXISTS (
            SELECT 1
            FROM invoice_sales_application_detail b
            WHERE a.application_no = b.application_no
            AND a.invoice_category = b.invoice_category
            AND b.del_flag = 0
            AND b.shop_order_id LIKE '23060919546335%'
        )
        AND a.is_merge = 1
    )
)

 

该 SQL 的业务逻辑为：统计满足以下条件之一的发票申请记录数

• 主表中订单号以指定前缀开头
• 存在关联明细表记录，且明细表订单号匹配前缀，同时主表合并标识为 1

通过 EXPLAIN 执行计划分析发现：

• 主表invoice_sales_application触发全表扫描，扫描行数达 116 万
• 子查询采用 ref 类型查询，依赖uk_appno_invcategory复合索引
• 整体执行时间长达 43 秒，显然存在严重的性能瓶颈

二、首轮优化：EXISTS 转 INNER JOIN 的实践

优化思路

EXISTS 子查询本质是存在性检查，当子查询数据量较大时，可能导致主表循环匹配。将其转换为 INNER JOIN 关联查询，利用索引进行连接筛选，理论上可提升效率。

改写后 SQL

SELECT COUNT(0)
FROM invoice_sales_application a
INNER JOIN invoice_sales_application_detail b 
ON a.application_no = b.application_no
WHERE (
    a.shop_order_id LIKE '23060919546335%'
    OR (
        b.shop_order_id LIKE '23060919546335%'
        AND a.is_merge = 1
    )
)
AND a.invoice_category = b.invoice_category
AND b.del_flag = 0

 

执行计划分析

| id | select_type | table | type | possible_keys | key | rows | Extra |
|----|-------------|-------|------|---------------|-----|------|-------|
| 1  | SIMPLE      | a     | ALL  | -             | -   | 116W | Using where |
| 1  | SIMPLE      | b     | ref  | uk_appno_invcategory | uk_appno_invcategory | 2    | Using index |

 

优化效果与瓶颈

• 子查询转换为 JOIN 后，明细表查询效率提升，但主表仍为全表扫描
• 执行时间降至 28 秒，瓶颈在于 OR 条件导致主表索引失效
• 核心问题：OR 条件使 MySQL 无法有效使用单一索引，导致全表扫描

三、二次优化：OR 转 UNION 的进阶方案

优化原理

OR 条件在 SQL 中会使索引选择性降低，当多个条件涉及不同表字段时，可通过 UNION 将查询拆分为独立子查询，每个子查询专注于单一条件路径，充分利用索引。

改写后 SQL

(
    SELECT COUNT(*)
    FROM invoice_sales_application a
    INNER JOIN invoice_sales_application_detail b 
    ON a.application_no = b.application_no
    AND a.invoice_category = b.invoice_category
    AND b.del_flag = 0
    WHERE a.shop_order_id = '23060919546335'
    AND a.del_flag = 0
)
UNION
(
    SELECT COUNT(*)
    FROM invoice_sales_application a
    INNER JOIN invoice_sales_application_detail b 
    ON a.application_no = b.application_no
    AND a.invoice_category = b.invoice_category
    AND b.del_flag = 0
    WHERE b.shop_order_id = '23060919546335'
    AND a.is_merge = 1
    AND a.del_flag = 0
)

 

执行计划解析

| id | select_type | table | type | key | key_len | rows |
|----|-------------|-------|------|---------------------------|-------|------|
| 1  | PRIMARY     | a     | ref  | idx_shop_order_id         | 259   | 1    |
| 1  | PRIMARY     | b     | ref  | uk_appno_invcategory      | 258   | 1    |
| 2  | UNION       | b     | ref  | idx_shop_order_id         | 259   | 1    |
| 2  | UNION       | a     | eq_ref | uk_application_no_invoice_category | 388 | 1    |

 

最终优化效果

• 执行时间从 43 秒锐减至 18 毫秒，性能提升超过 2000 倍
• 主表与明细表均实现索引覆盖查询（Index Covering）
• 执行计划显示 eq_ref+ref 组合，达到 MySQL 查询优化的理想状态

四、SQL 优化的核心方法论

1. EXISTS 与 JOIN 的选择策略

• EXISTS 适用于 "主表大、子表小" 的场景，利用子表索引快速过滤
• JOIN 更适合 "子表大、主表小" 的场景，通过关联条件提前筛选数据
• 实际应用中可通过 EXPLAIN 对比两种方式的 rows 扫描量决定

2. OR 条件的优化原则

• 当 OR 两侧条件涉及不同表字段时，UNION 拆分是最佳方案
• 若 OR 条件在同表同字段，可尝试创建复合索引（如 (a,b)）
• 避免在 OR 条件中混合使用索引字段与非索引字段

3. 索引设计的黄金法则

• 为 LIKE 前缀匹配字段创建前缀索引（如 idx_shop_order_id (20)）
• 复合索引遵循 "最左前缀" 原则，如 (application_no, invoice_category)
• 定期通过SHOW INDEX与EXPLAIN分析索引使用效率

4. 性能优化的验证流程

1. 采集原始 SQL 的执行计划与执行时间
2. 制定优化方案并进行小范围测试
3. 对比优化前后的扫描行数 (rows) 与成本 (cost)
4. 监控实际业务场景下的资源占用（CPU/IO/ 内存）
5. 建立 SQL 审核机制，避免低效 SQL 再次上线

五、进阶优化思考

本次优化案例中，将 LIKE 前缀匹配改为精准匹配（'23060919546335'）获得了性能提升。在实际业务中，若必须保留前缀匹配，可考虑：

• 创建全文索引配合 MATCH AGAINST
• 采用倒排索引思想，将前缀字段单独存储
• 对于高频查询，引入 Redis 等缓存中间件

此外，UNION 操作默认会去重，若确认结果集无重复数据，可使用 UNION ALL 进一步提升性能，避免排序去重开销。

结语

SQL 优化是理论与实践结合的艺术，需要深入理解数据库引擎的执行原理，同时具备丰富的实战经验。本案例展示的 EXISTS 转 JOIN、OR 转 UNION 的优化技巧，本质上是通过查询改写引导 MySQL 优化器选择更优的执行路径。在实际工作中，应建立 "分析 - 改写 - 验证" 的标准化优化流程，持续提升系统的数据处理能力。