SQL复杂查询通常出现在多表关联统计、嵌套子查询、多层聚合计算等业务场景中，合理编写和优化这类查询是提升数据库性能的关键。良好的复杂查询编写习惯不仅能减少数据库的资源消耗，还能让后续维护更加便捷。
常用SQL复杂查询技巧
1. 使用CTE替代多层嵌套子查询
多层嵌套子查询会让SQL逻辑变得难以阅读，同时可能导致数据库重复执行子查询逻辑。使用公用表表达式（CTE）可以将复杂查询拆分为多个逻辑块，提升可读性的同时，部分数据库会对CTE进行优化，减少重复计算。
-- 嵌套子查询写法
SELECT d.dept_name, t.avg_salary
FROM departments d
JOIN (
SELECT dept_id, AVG(salary) as avg_salary
FROM (
SELECT dept_id, salary
FROM employees
WHERE status = 1
) e
GROUP BY dept_id
) t ON d.dept_id = t.dept_id;
-- CTE改写后写法
WITH valid_employees AS (
SELECT dept_id, salary
FROM employees
WHERE status = 1
),
dept_avg_salary AS (
SELECT dept_id, AVG(salary) as avg_salary
FROM valid_employees
GROUP BY dept_id
)
SELECT d.dept_name, t.avg_salary
FROM departments d
JOIN dept_avg_salary t ON d.dept_id = t.dept_id;
2. 合理使用关联方式替代子查询
部分场景下，将子查询改写为JOIN关联可以提升查询效率，尤其是当子查询返回结果集较大时，JOIN可以利用索引优化执行逻辑，减少临时表的生成。
-- 子查询写法
SELECT e.emp_name, e.salary
FROM employees e
WHERE e.dept_id IN (
SELECT dept_id
FROM departments
WHERE location = '北京'
);
-- JOIN改写后写法
SELECT e.emp_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = '北京';
3. 聚合查询中合理使用窗口函数
如果需要在查询结果中同时展示明细数据和聚合统计结果，窗口函数可以避免多次关联聚合子查询，简化查询逻辑。
-- 传统写法需要两次查询关联
SELECT e.emp_name, e.salary, t.dept_avg
FROM employees e
JOIN (
SELECT dept_id, AVG(salary) as dept_avg
FROM employees
GROUP BY dept_id
) t ON e.dept_id = t.dept_id;
-- 窗口函数写法
SELECT
emp_name,
salary,
AVG(salary) OVER (PARTITION BY dept_id) as dept_avg
FROM employees;
SQL复杂查询优化方法
1. 分析执行计划定位瓶颈
优化复杂查询的第一步是查看数据库的执行计划，不同数据库查看执行计划的语法不同，例如MySQL使用EXPLAIN关键字，PostgreSQL使用EXPLAIN ANALYZE。执行计划会展示查询的执行顺序、表的访问方式、索引使用情况、预计扫描行数等信息，帮助定位全表扫描、临时表使用过多等问题。
-- MySQL查看执行计划示例 EXPLAIN SELECT d.dept_name, AVG(e.salary) as avg_salary FROM departments d JOIN employees e ON d.dept_id = e.dept_id WHERE e.status = 1 GROUP BY d.dept_id;
2. 优化索引设计
索引是提升查询性能的核心手段，针对复杂查询的索引设计需要注意以下几点：
- 关联字段必须建立索引，例如JOIN条件中的
dept_id字段 - 过滤条件中的字段优先建立索引，例如
status、location等字段 - 避免建立过多冗余索引，冗余索引会增加写入时的性能开销
- 如果查询中使用了函数操作字段，普通索引会失效，可以考虑建立函数索引
3. 简化查询逻辑减少计算量
在编写复杂查询时，可以通过以下方式减少数据库的计算压力：
- 提前过滤不需要的数据，将
WHERE条件尽量前置，减少后续关联、聚合的数据量 - 避免
SELECT *，只查询需要的字段，减少数据读取和传输的开销 - 如果业务允许，将部分复杂计算逻辑放到应用层处理，避免数据库承担过多计算任务
- 对于频繁执行的复杂查询，可以考虑建立物化视图，定期刷新数据，避免每次查询都重新计算
4. 控制查询返回的数据量
复杂查询如果返回过多数据，会占用大量网络带宽和内存资源，建议根据业务需求添加分页逻辑，避免一次性返回全量结果。
-- MySQL分页示例 SELECT d.dept_name, AVG(e.salary) as avg_salary FROM departments d JOIN employees e ON d.dept_id = e.dept_id WHERE e.status = 1 GROUP BY d.dept_id LIMIT 10 OFFSET 20;
常见注意事项
在编写和优化复杂查询时，还需要注意以下细节：
- 避免在
WHERE条件中对字段进行函数操作，例如WHERE YEAR(create_time) = 2023会导致索引失效，可以改写为WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01' - 关联查询时注意关联条件的唯一性，避免产生笛卡尔积导致数据量爆炸
- 生产环境执行复杂查询前，一定要先在测试环境验证执行时间和资源占用，避免影响线上业务
- 定期清理无用的复杂查询，对于不再使用的统计逻辑及时下线对应的查询语句