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;

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

​四、工具与调试

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

​五、常见误区

1. ​过度索引：索引会降低写入速度，需权衡。
2. ​忽略 NULL 值：WHERE column = NULL 应改为 WHERE column IS NULL。
3. ​滥用子查询：优先使用 JOIN 或 CTE。
4. ​不更新统计信息：导致优化器选择错误执行计划。