SQL 复杂查询和性能优化

一、掌握复杂查询的核心技能

1. 理解 SQL 执行顺序

SQL 语句的逻辑执行顺序（非书写顺序）：

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

关键点：每一步的结果会传递给下一步，影响优化逻辑。

2. 多表连接（JOIN）

类型：
- INNER JOIN（默认）
- LEFT/RIGHT JOIN
- FULL OUTER JOIN（部分数据库不支持）
- CROSS JOIN（笛卡尔积）

示例：

SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

3. 子查询与 CTE

子查询：

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

CTE（公共表表达式）：

WITH high_value_orders AS (SELECT user_id FROM orders WHERE amount > 100
)
SELECT u.name FROM users u
JOIN high_value_orders hvo ON u.id = hvo.user_id;

优势：CTE 可读性更好，支持递归查询。

4. 窗口函数

用途：在结果集的子集上计算聚合（不折叠行）。

示例：

SELECT name, salary,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

常用函数：
- ROW_NUMBER(), RANK(), DENSE_RANK()
- LEAD(), LAG()
- SUM() OVER (PARTITION BY ...)

5. 复杂条件与 CASE 语句

动态分类：

SELECT name,CASE WHEN salary > 10000 THEN 'High'WHEN salary > 5000 THEN 'Medium'ELSE 'Low' END AS salary_level
FROM employees;

二、性能优化核心技巧

1. 分析执行计划

使用 EXPLAIN：

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

关键指标：
- Full Table Scan（全表扫描）→ 需要避免。
- Index Scan（索引扫描）→ 理想情况。
- Rows Removed by Filter → 过滤效率。

2. 索引优化

何时使用索引：
- 频繁作为查询条件的列（WHERE、JOIN、ORDER BY）。
- 高选择性的列（唯一值多，如用户ID）。
索引类型：
- 单列索引、复合索引、唯一索引、覆盖索引。
复合索引设计：
- 将最常用的列放在前面。
- 示例：INDEX (country, city) 可优化 WHERE country='US' AND city='NY'。

3. 避免全表扫描

反例：

SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 无法使用 created_at 的索引

优化：

SELECT * FROM users
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

4. 分页优化

低效写法：

SELECT * FROM users LIMIT 1000000, 10; -- 扫描前 100 万行

优化：

SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;

5. 减少数据传输量

避免 SELECT *：

SELECT id, name FROM users; -- 只取必要字段

分批次处理：

-- 使用 WHERE 条件分块处理大表
DELETE FROM logs WHERE created_at < '2020-01-01' LIMIT 1000;

6. 利用分区表

按时间分区：

-- 按月份分区（具体语法因数据库而异）
CREATE TABLE logs (id INT,log_time DATETIME,message TEXT
) PARTITION BY RANGE (YEAR(log_time), MONTH(log_time));

优势：快速定位分区，减少扫描范围。

三、高级优化场景

1. JOIN 优化

小表驱动大表：

-- 假设 users 是小表，orders 是大表
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id;

使用 STRAIGHT_JOIN（MySQL）：强制指定 JOIN 顺序。

2. 临时表与物化视图

临时表：

CREATE TEMPORARY TABLE temp_high_salary AS
SELECT * FROM employees WHERE salary > 10000;

物化视图：预计算复杂查询结果（如 PostgreSQL 的 MATERIALIZED VIEW）。

3. 统计信息更新

手动更新（适用于数据变化大的表）：

ANALYZE TABLE users; -- MySQL
VACUUM ANALYZE users; -- PostgreSQL

4. 避免锁竞争

使用乐观锁：

UPDATE products SET stock = stock - 1
WHERE id = 100 AND stock > 0;

分拆事务：将大事务拆分为小操作。

四、工具与调试

数据库管理工具：
- MySQL: EXPLAIN FORMAT=JSON、SHOW PROFILE
- PostgreSQL: EXPLAIN ANALYZE、pg_stat_statements
监控工具：
- Prometheus + Grafana（监控慢查询、锁等待）。
基准测试：
- 使用 sysbench 或自定义脚本模拟负载。