一、子查询优化策略

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;

优化要点：

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

二、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';

优化方案：

1. 添加复合索引：(transaction_date, account_id)
2. 重写为子查询先过滤：

   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 索引优化策略

1. 复合索引设计：遵循最左前缀原则，将高选择性列放在前面
2. 覆盖索引：包含查询所需全部字段，避免回表操作
3. 函数索引：对常用函数操作创建索引（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 专用表空间 |

四、实战优化流程

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

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

五、常见误区警示

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

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