一、子查询优化策略

1.1 子查询改写原则

在OLTP系统中，子查询是常见的性能瓶颈。根据数据规模差异，优化策略可分为三类：

大结果集场景：当子查询返回行数超过总表10%时，JOIN改写可获得更优执行计划
小结果集场景：子查询结果在50行以内可保持原写法，避免过度优化
NULL值陷阱：NOT IN/NOT EXISTS子查询中存在NULL值会导致逻辑错误，需使用NVL函数或COALESCE处理

典型案例：某电商平台订单查询接口，原使用NOT EXISTS子查询检查退货状态，改写为LEFT JOIN后响应时间从2.3s降至0.4s。

1.2 IN/EXISTS改写实践

1.2.1 标准改写模式

-- 原始IN子查询
SELECT * FROM employees e 
WHERE e.dept_id IN (
    SELECT d.dept_id FROM departments d WHERE d.location = 'NY'
);
-- 改写为INNER JOIN
SELECT e.* FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = 'NY';

1.2.2 复杂场景处理

当子查询列存在重复值时，需添加DISTINCT去重：

-- 原始EXISTS子查询（可能产生重复）
SELECT o.* FROM orders o
WHERE EXISTS (
    SELECT 1 FROM order_items oi 
    WHERE oi.order_id = o.order_id AND oi.quantity > 5
);
-- 优化改写
SELECT o.* FROM orders o
JOIN (
    SELECT DISTINCT order_id FROM order_items 
    WHERE quantity > 5
) t ON o.order_id = t.order_id;

1.3 NOT IN/NOT EXISTS优化

这类否定查询存在特殊优化需求：

-- 原始NOT IN查询（存在NULL风险）
SELECT * FROM products p
WHERE p.category_id NOT IN (
    SELECT c.category_id FROM categories c 
    WHERE c.is_active = 0
);
-- 优化改写（三步处理）
SELECT p.* FROM products p
LEFT JOIN (
    SELECT category_id FROM categories 
    WHERE is_active = 0
) c ON p.category_id = c.category_id
WHERE c.category_id IS NULL;

优化要点：

使用LEFT JOIN替代子查询
通过IS NULL条件过滤
在子查询中预先过滤无效数据

二、JOIN操作深度优化

2.1 JOIN类型选择指南

Join类型	适用场景	性能特征
INNER JOIN	精确匹配查询	最优执行计划
LEFT JOIN	保留左表全部数据	注意NULL值处理
HASH JOIN	大数据量关联	内存消耗较大
NESTED LOOP	小结果集关联	依赖索引质量

2.2 驱动表选择策略

优化器通常根据表大小选择驱动表，但可通过以下方式干预：

-- 强制指定驱动表（Oracle语法）
SELECT /*+ LEADING(e) */ e.*, d.* 
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;
-- MySQL的STRAIGHT_JOIN
SELECT STRAIGHT_JOIN e.*, d.*
FROM employees e JOIN departments d
ON e.dept_id = d.dept_id;

2.3 多表JOIN优化案例

某金融系统交易查询涉及5表关联，原始SQL执行时间12.7s：

-- 原始查询
SELECT t.*, a.account_name, c.customer_name 
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
JOIN customers c ON a.customer_id = c.customer_id
JOIN branches b ON a.branch_id = b.branch_id
JOIN products p ON t.product_id = p.product_id
WHERE t.transaction_date > '2023-01-01';

优化方案：

添加复合索引：(transaction_date, account_id)

重写为子查询先过滤：

SELECT t.*, a.account_name, c.customer_name 
FROM (
 SELECT * FROM transactions 
 WHERE transaction_date > '2023-01-01'
) t
JOIN accounts a ON t.account_id = a.account_id
JOIN customers c ON a.customer_id = c.customer_id
LEFT JOIN branches b ON a.branch_id = b.branch_id
LEFT JOIN products p ON t.product_id = p.product_id;

优化后执行时间降至1.8s，CPU使用率下降65%。

三、高级优化技术

3.1 索引优化策略

复合索引设计：遵循最左前缀原则，将高选择性列放在前面
覆盖索引：包含查询所需全部字段，避免回表操作
函数索引：对常用函数操作创建索引（Oracle支持函数索引，MySQL 8.0+支持表达式索引）

3.2 执行计划分析

使用EXPLAIN分析查询执行路径：

-- MySQL执行计划分析
EXPLAIN SELECT * FROM orders 
WHERE customer_id IN (
    SELECT customer_id FROM vip_customers
);
-- Oracle执行计划分析
EXPLAIN PLAN FOR
SELECT * FROM employees 
WHERE department_id = (
    SELECT department_id FROM departments 
    WHERE location_id = 1700
);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

关键指标解读：

type列：const/eq_ref最优，ALL表示全表扫描
key列：显示实际使用的索引
rows列：预估扫描行数
Extra列：Using temporary/Using filesort表示需要优化

3.3 数据库参数调优

关键参数配置建议：
| 参数 | MySQL建议值 | Oracle建议值 |
|———|——————|——————|
| sort_buffer_size | 2M-8M | SORT_AREA_SIZE 1M-10M |
| join_buffer_size | 1M-4M | PGA_AGGREGATE_TARGET 动态调整 |
| tmp_table_size | 64M-256M | TEMPORARY_TABLESPACE 专用表空间 |

四、实战优化流程

问题定位：通过慢查询日志识别TOP SQL
执行分析：使用EXPLAIN生成执行计划
索引检查：确认是否使用合适索引
SQL改写：应用本文介绍的优化技术
参数调整：必要时修改数据库配置
性能测试：使用真实数据量进行AB测试
监控维护：建立持续监控机制

某物流系统通过此流程优化后，数据库CPU负载从85%降至35%，平均响应时间从1.2s降至0.3s，系统稳定性显著提升。

五、常见误区警示

过度索引：每个额外索引增加10%写入开销
盲目重写：小结果集子查询无需改写为JOIN
忽视统计信息：过时统计导致优化器选择次优计划
参数滥用：调整前需理解参数间的相互影响

结语：SQL优化是系统工程，需要结合业务特点、数据特征和数据库特性进行综合调优。建议建立定期优化机制，持续监控查询性能，形成PDCA优化闭环。对于超大规模系统，可考虑引入自动化SQL审核工具辅助优化工作。

SQL查询优化全攻略：MySQL与Oracle通用实践指南