印刻数据库

外观

MySQL子查询优化 ​

子查询的定义与分类 ​

子查询是指嵌套在另一个SQL语句中的SELECT查询。MySQL支持多种类型的子查询：

1. 按位置分类 ​

  • WHERE子查询：嵌套在WHERE子句中的子查询
  • FROM子查询：嵌套在FROM子句中的子查询，也称为派生表
  • SELECT子查询：嵌套在SELECT列表中的子查询
  • HAVING子查询：嵌套在HAVING子句中的子查询

2. 按返回结果分类 ​

  • 标量子查询：返回单个值的子查询
  • 行子查询：返回一行多列的子查询
  • 列子查询：返回一列多行的子查询
  • 表子查询：返回多行多列的子查询

3. 按执行方式分类 ​

  • 关联子查询：子查询依赖外部查询的结果，需要多次执行
  • 非关联子查询：子查询独立于外部查询，可以只执行一次

子查询的执行原理 ​

1. MySQL 5.5及之前的版本 ​

在MySQL 5.5及之前的版本中，子查询通常采用嵌套循环执行方式：

  1. 外部查询执行一次，获取一行结果
  2. 将外部行的列值传递给子查询
  3. 执行子查询，获取结果
  4. 根据子查询结果评估WHERE条件
  5. 重复上述过程，直到外部查询处理完所有行

这种执行方式在子查询结果集较大时，性能会急剧下降。

2. MySQL 5.6及之后的版本 ​

从MySQL 5.6开始，优化器引入了子查询优化技术：

  • 半连接（Semi-Join）优化：用于IN、EXISTS等子查询，减少子查询的执行次数
  • 物化（Materialization）：将子查询结果存储在临时表中，避免重复执行
  • 优化器提示：允许使用STRAIGHT_JOIN等提示影响执行计划
  • 子查询解嵌套：将某些子查询转换为连接操作，提高执行效率

3. MySQL 8.0的增强 ​

MySQL 8.0进一步增强了子查询优化：

  • 更智能的半连接优化策略选择
  • 改进的物化算法
  • 更好的执行计划估计
  • 支持更多类型的子查询解嵌套

子查询优化策略 ​

1. 优先使用JOIN替代相关子查询 ​

相关子查询需要多次执行，而JOIN操作通常更高效。

优化前：

sql
SELECT * FROM orders o
WHERE o.customer_id IN (
  SELECT c.customer_id FROM customers c WHERE c.country = 'China'
);

优化后：

sql
SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'China';

2. 使用EXISTS替代IN子查询 ​

对于大表查询，EXISTS通常比IN更高效，因为EXISTS在找到匹配项后会立即停止搜索。

优化前：

sql
SELECT * FROM products p
WHERE p.product_id IN (
  SELECT oi.product_id FROM order_items oi WHERE oi.quantity > 10
);

优化后：

sql
SELECT * FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items oi 
  WHERE oi.product_id = p.product_id AND oi.quantity > 10
);

3. 利用半连接优化 ​

MySQL 5.6+支持半连接优化，自动将某些IN子查询转换为更高效的执行计划。确保启用相关优化器开关：

sql
SET optimizer_switch = 'semijoin=on,firstmatch=on,loosescan=on,materialization=on';

4. 避免在子查询中使用ORDER BY ​

子查询中的ORDER BY通常是多余的，因为外部查询会重新排序结果。除非与LIMIT结合使用：

优化前：

sql
SELECT * FROM orders o
WHERE o.order_id IN (
  SELECT oi.order_id FROM order_items oi 
  WHERE oi.quantity > 5 ORDER BY oi.order_id
);

优化后：

sql
SELECT * FROM orders o
WHERE o.order_id IN (
  SELECT oi.order_id FROM order_items oi 
  WHERE oi.quantity > 5
);

5. 使用LIMIT限制子查询结果集 ​

当只需要少量结果时，使用LIMIT限制子查询返回的行数：

sql
SELECT * FROM products p
WHERE p.product_id IN (
  SELECT oi.product_id FROM order_items oi 
  WHERE oi.quantity > 10 
  ORDER BY oi.quantity DESC 
  LIMIT 10
);

6. 为子查询添加合适的索引 ​

确保子查询中使用的列有适当的索引，减少子查询的执行时间：

sql
-- 为order_items表的product_id和quantity列添加索引
CREATE INDEX idx_order_items_product_quantity ON order_items(product_id, quantity);

7. 使用物化表缓存子查询结果 ​

对于重复执行的子查询，可以将结果存储在临时表中，避免重复计算：

sql
-- 创建物化表
CREATE TEMPORARY TABLE top_products AS
SELECT product_id FROM order_items 
WHERE quantity > 10 
GROUP BY product_id 
ORDER BY SUM(quantity) DESC 
LIMIT 100;

-- 为物化表添加索引
ALTER TABLE top_products ADD INDEX idx_product_id(product_id);

-- 使用物化表进行查询
SELECT * FROM products p
WHERE p.product_id IN (SELECT product_id FROM top_products);

8. 避免在子查询中使用NOT IN ​

NOT IN子查询性能通常较差，尤其是当子查询结果包含NULL值时。可以考虑使用NOT EXISTS或LEFT JOIN替代：

优化前：

sql
SELECT * FROM customers c
WHERE c.customer_id NOT IN (
  SELECT o.customer_id FROM orders o
);

优化后：

sql
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

子查询性能分析 ​

1. 使用EXPLAIN分析执行计划 ​

通过EXPLAIN命令可以查看子查询的执行计划，了解优化器如何处理子查询：

sql
EXPLAIN SELECT * FROM orders o
WHERE o.customer_id IN (
  SELECT c.customer_id FROM customers c WHERE c.country = 'China'
);

2. 关键执行计划指标 ​

  • select_type：显示子查询的类型（SUBQUERY、DERIVED等）
  • type：访问类型（ALL、index、range、ref、eq_ref、const等）
  • rows：估计需要扫描的行数
  • filtered：通过条件过滤后剩余的行百分比
  • Extra：额外信息，如"Using index"、"Using temporary"、"Using filesort"等

3. 使用EXPLAIN FORMAT=JSON获取详细信息 ​

MySQL 5.6+支持JSON格式的执行计划，提供更详细的优化器决策信息：

sql
EXPLAIN FORMAT=JSON SELECT * FROM orders o
WHERE o.customer_id IN (
  SELECT c.customer_id FROM customers c WHERE c.country = 'China'
);

版本差异与兼容性 ​

1. MySQL 5.5及之前版本 ​

  • 子查询优化有限，相关子查询性能较差
  • 不支持半连接优化
  • 不支持物化优化

2. MySQL 5.6版本 ​

  • 引入半连接优化（semijoin）
  • 支持子查询物化
  • 支持子查询解嵌套
  • 优化器可以将某些子查询转换为JOIN

3. MySQL 5.7版本 ​

  • 进一步改进半连接优化算法
  • 支持更多类型的子查询解嵌套
  • 改进了执行计划估计
  • 支持EXPLAIN FORMAT=JSON显示更详细的子查询优化信息

4. MySQL 8.0版本 ​

  • 引入更多半连接优化策略
  • 改进了子查询物化算法
  • 支持递归CTE，减少某些复杂子查询的需求
  • 优化器能够更智能地选择子查询执行方式

常见子查询优化案例 ​

案例1：标量子查询优化 ​

问题：查询每个订单的总金额，使用标量子查询

优化前：

sql
SELECT o.order_id, o.order_date,
  (SELECT SUM(oi.quantity * p.price) 
   FROM order_items oi 
   JOIN products p ON oi.product_id = p.product_id 
   WHERE oi.order_id = o.order_id) AS total_amount
FROM orders o;

优化后：

sql
SELECT o.order_id, o.order_date, SUM(oi.quantity * p.price) AS total_amount
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id, o.order_date;

案例2：IN子查询优化 ​

问题：查询购买了特定产品的客户信息

优化前：

sql
SELECT c.* FROM customers c
WHERE c.customer_id IN (
  SELECT DISTINCT o.customer_id 
  FROM orders o 
  JOIN order_items oi ON o.order_id = oi.order_id 
  WHERE oi.product_id = 1001
);

优化后：

sql
SELECT DISTINCT c.* FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.product_id = 1001;

案例3：EXISTS子查询优化 ​

问题：查询至少有一个订单的客户

优化前：

sql
SELECT c.* FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.customer_id = c.customer_id
);

优化后：

sql
-- 确保orders表的customer_id列有索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 查询语句无需修改，EXISTS本身已经是高效的写法
SELECT c.* FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.customer_id = c.customer_id
);

子查询的最佳实践 ​

1. 优先使用JOIN替代相关子查询 ​

相关子查询需要多次执行，而JOIN通常可以通过更高效的方式执行。

2. 合理使用EXISTS和IN ​

  • 当子查询结果集较小且没有NULL值时，IN可能更高效
  • 当子查询结果集较大或可能包含NULL值时，EXISTS通常更高效
  • NOT IN性能较差，建议使用NOT EXISTS或LEFT JOIN替代

3. 为子查询添加适当的索引 ​

确保子查询中使用的列有适当的索引，减少子查询的执行时间。

4. 避免在子查询中使用复杂的聚合函数 ​

复杂的聚合函数会增加子查询的执行时间，尽量将聚合操作移到外部查询。

5. 使用物化表缓存重复执行的子查询结果 ​

对于需要重复执行的子查询，可以将结果存储在临时表中，提高查询效率。

6. 定期更新表统计信息 ​

MySQL优化器依赖表统计信息来生成执行计划，定期更新统计信息可以帮助优化器做出更好的决策：

sql
ANALYZE TABLE customers, orders, order_items, products;

7. 升级到较新版本的MySQL ​

新版本的MySQL在子查询优化方面有显著改进，升级到5.6+版本可以获得更好的子查询性能。

8. 监控子查询性能 ​

使用MySQL的Performance Schema或其他监控工具，监控子查询的执行时间和资源消耗，及时发现性能问题。

常见问题（FAQ） ​

Q1: 子查询和JOIN哪个性能更好？ ​

A1: 这取决于具体情况：

  • 对于简单查询，JOIN通常比相关子查询性能更好
  • 对于复杂查询，优化器可能会将子查询转换为JOIN
  • MySQL 5.6+版本对某些类型的子查询进行了优化，性能可以接近JOIN
  • 建议通过EXPLAIN分析执行计划，选择更高效的写法

Q2: 如何判断子查询是否被优化器转换为JOIN？ ​

A2: 可以通过EXPLAIN命令查看执行计划：

  • 如果select_type显示为SIMPLE，说明子查询已被转换为JOIN
  • 如果select_type显示为SUBQUERY或DERIVED，说明子查询未被完全转换
  • 使用EXPLAIN FORMAT=JSON可以查看更详细的优化信息

Q3: 子查询中可以使用ORDER BY吗？ ​

A3: 子查询中可以使用ORDER BY，但通常只有与LIMIT结合使用时才有意义：

sql
SELECT * FROM products p
WHERE p.product_id IN (
  SELECT oi.product_id FROM order_items oi 
  WHERE oi.quantity > 10 
  ORDER BY oi.quantity DESC 
  LIMIT 10
);

如果没有LIMIT，子查询的ORDER BY会被忽略，因为外部查询会重新排序结果。

Q4: 如何优化包含NOT IN的子查询？ ​

A4: NOT IN子查询性能通常较差，尤其是当子查询结果包含NULL值时。可以考虑以下优化方案：

  1. 使用NOT EXISTS替代NOT IN
  2. 使用LEFT JOIN + IS NULL替代NOT IN
  3. 确保子查询结果不包含NULL值
  4. 为子查询添加适当的索引

Q5: 子查询的嵌套层级有限制吗？ ​

A5: MySQL对子查询的嵌套层级没有硬性限制，但嵌套层级过深会导致：

  • 执行计划变得复杂，优化器难以选择最优计划
  • 查询执行时间增加
  • 内存消耗增加

建议子查询嵌套层级不超过3层，对于更复杂的查询，可以考虑使用JOIN或拆分为多个查询。

Q6: 如何优化关联子查询？ ​

A6: 关联子查询优化方法包括：

  1. 为子查询中使用的列添加适当的索引
  2. 考虑将关联子查询转换为JOIN
  3. 使用半连接优化（MySQL 5.6+）
  4. 减少子查询的结果集大小
  5. 避免在子查询中使用复杂的逻辑

Q7: MySQL 8.0在子查询优化方面有哪些改进？ ​

A7: MySQL 8.0在子查询优化方面的改进包括：

  1. 引入更多半连接优化策略
  2. 改进了子查询物化算法
  3. 支持递归CTE，减少某些复杂子查询的需求
  4. 优化器能够更智能地选择子查询执行方式
  5. 改进了执行计划估计
  6. 支持更详细的EXPLAIN信息

Q8: 如何监控子查询的性能？ ​

A8: 可以使用以下方法监控子查询性能：

  1. 使用Performance Schema监控查询执行时间
  2. 启用慢查询日志，记录执行时间超过阈值的子查询
  3. 使用EXPLAIN ANALYZE（MySQL 8.0+）查看实际执行计划
  4. 使用MySQL Enterprise Monitor或其他第三方监控工具
  5. 定期分析查询执行计划，识别性能问题

Q9: 子查询中可以使用LIMIT吗？ ​

A9: 子查询中可以使用LIMIT，但需要注意：

  1. LIMIT必须与ORDER BY结合使用才有意义
  2. 只有当子查询是标量子查询或用于IN/EXISTS条件时，LIMIT才有效果
  3. 表子查询（FROM子查询）可以使用LIMIT限制结果集大小

Q10: 如何优化FROM子查询（派生表）？ ​

A10: FROM子查询优化方法包括：

  1. 为派生表添加别名
  2. 考虑使用CTE（MySQL 8.0+）替代派生表
  3. 为派生表添加适当的索引
  4. 减少派生表的结果集大小
  5. 考虑将派生表转换为临时表，并添加索引
  6. 确保派生表中的查询有适当的索引