SQL 优化-嵌套子查询和 JOIN 优化

常见优化技术

我们先从工业实践角度总结，几条常见 MySQL 查询优化策略。

1. 索引优化

• 为常用的查询条件（WHERE、JOIN、GROUP BY、ORDER BY）添加合适的索引。PS：索引类似于一本书的目录，通过为表中的一列或多列建立索引，MySQL 可以快速定位数据，避免全表扫描。
• 使用覆盖索引（EXPLAIN 中的 Using Index）以减少回表查询。PS：EXPLAIN 关键字 是 MySQL 中用来分析和优化 SQL 查询的重要工具

2. 避免全表扫描

• 使用筛选条件时，尽量避免对字段进行函数或运算操作（如 WHERE YEAR(date) = 2020，应改为 WHERE date >= '2020-01-01' AND date < '2021-01-01'）。YEAR(date) 会涉及整列数据，而 WHERE 只会涉及部分的数据。
• 避免使用 SELECT *，明确指定查询字段。

3. 查询语句简化

• 分解复杂查询：将复杂的多表 JOIN 查询，分解为简单的多次查询（基于CTE）。
• 使用子查询时，优先考虑改为 JOIN。

4. 控制返回结果

• 限制返回的行数：使用 LIMIT。
• 不需要的数据字段不要出现在 SELECT 中。

5. 优化排序操作

• 避免在 ORDER BY 中使用不必要的字段。
• 如果可能，使用索引来加速排序。

6. 避免死锁和阻塞

• 减少事务的锁范围：将事务中查询更新逻辑分步优化。
• 避免使用 SELECT ... FOR UPDATE 锁定大范围数据。

7. 分析执行计划

• 使用 EXPLAIN 检查查询计划，分析是否存在全表扫描或低效的索引使用。

8. 表结构和分区优化

• 大表可考虑分区（如时间分区或范围分区）。
• 常查询的数据放入单独的热点表或缓存（如 Redis）。

9. 缓存重复查询

• 使用查询缓存（Query Cache，注意适用场景）。
• 在应用层缓存热点数据，避免重复查询。

10. 定期优化表

• 清理碎片：使用 OPTIMIZE TABLE。
• 避免数据膨胀，定期归档历史数据。

嵌套子查询 vs JOIN

一般来说，嵌套子查询在逻辑上更直观，但可能性能较低。用 JOIN 替代嵌套子查询，尤其是对大数据集时，能显著提升效率。

• 嵌套子查询适用场景：
   
  • 当逻辑简单，结果小，且不需要优化性能时。
  • 例如只需快速筛选单个值：SELECT ... WHERE id = (SELECT MAX(...))
• JOIN 适用场景：
   
  • 数据量大，涉及多个表连接时，JOIN 通常效率更高。
  • JOIN 能利用索引、减少重复扫描，是优化嵌套子查询的首选。

具体来说，嵌套子查询和 JOIN 优化的关系主要体现在性能和执行方式上：

嵌套子查询：

• 子查询在主查询中作为条件，每次执行主查询的某一行时可能会多次执行子查询。
• 若子查询结果较大，且未优化，会导致多次扫描表，性能较低。
• 示例：

SELECT
	`name` 
FROM
	employees 
WHERE
	department_id IN ( select id FROM departments WHERE location = 'NY')

• 如果子查询结果未缓存，每次主查询都要重新计算。

JOIN 替代嵌套子查询：

• 使用 JOIN 将相关表连接起来，一次性获取所有匹配的数据，避免重复计算。
• 数据库优化器通常能更高效地生成执行计划。
• 示例：

SELECT
	e.NAME 
FROM
	employees e
	JOIN departments d ON e.department_id = d.id 
WHERE
	d.location = 'NY'

优化的核心：

• JOIN 通常通过索引和优化器更快处理多表数据，而嵌套子查询更容易导致重复扫描。
• 对于结果较大的子查询，JOIN 可以显著提升性能。

其它4个案例

让我们多练习几个案例，巩固一下这个知识点。

例子 1：查找在纽约工作的员工

描述：
假设我们有一个包含员工信息的表和一个部门信息的表，现在想找出在“纽约”工作的员工信息。

• 嵌套子查询方式：先通过子查询筛选出位于“纽约”的部门 ID，然后在主查询中将员工的部门 ID 与这些部门 ID 进行匹配。
• 关键问题：对于每个员工记录，数据库可能需要重复执行子查询，尤其当员工表或部门表数据量很大时，这种方式会导致性能下降。
• JOIN 优化方式：通过直接将员工表与部门表连接，一次性筛选出符合条件的员工。这种方式让数据库可以利用索引优化连接操作，从而减少重复计算，提高效率。

嵌套子查询方式

SELECT
	`name`,
	salary 
FROM
	employees 
WHERE
	department_id IN (SELECT id FROM departments WHERE location = 'New York')

JOIN优化方式

SELECT
	e.`name`,
	e.salary 
FROM
	employees e
	JOIN departments d ON e.department_id = d.id 
WHERE
	d.location = 'New York'

例子 2：查找拥有多个订单的客户

描述：
我们希望找出哪些客户下了多个订单（订单数量大于 1）。

• 嵌套子查询方式：首先在子查询中统计每个客户的订单数量，并筛选出订单数大于 1 的客户 ID。然后，主查询再从客户表中匹配这些客户的详细信息。
• 关键问题：嵌套子查询的结果需要反复匹配主查询，尤其当订单表数据量巨大时，子查询计算成本较高。
• JOIN 优化方式：通过将子查询生成的统计结果作为一个临时表，与客户表直接连接。这样数据库可以更高效地规划查询路径，减少性能瓶颈。

嵌套子查询方式

SELECT
	customer_id 
FROM
	customers 
WHERE
	customer_id IN ( SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*)> 1)

JOIN 优化方式

SELECT
	c.customer_id 
FROM
	customers c
	JOIN ( SELECT customer_id FROM orders GROUP BY customer_id HAVING count(*)> 1 ) o ON c.customer_id = o.customer_id

关联知识：mysql having的用法-XQLEE'Blog

例子 3：查找薪资最高的员工

描述：
我们需要从员工表中找到薪资最高的员工以及他们的薪资数额。

• 嵌套子查询方式：先用子查询计算出薪资的最大值，然后在主查询中将所有薪资等于这个最大值的员工筛选出来。
• 关键问题：子查询和主查询之间有依赖关系，容易导致数据库多次扫描整个员工表，尤其当表数据量较大时。
• JOIN 优化方式：通过将最大薪资的计算结果生成一个临时表，然后直接与员工表连接，避免重复计算。这种方法让数据库优化器更容易高效处理查询逻辑。

嵌套子查询方式

SELECT
	`name`,
	salary 
FROM
	employees 
WHERE
	salary = ( SELECT max( salary )  FROM employees)

JOIN 优化方式

SELECT
	e.`name`,
	e.salary 
FROM
	employees e
	JOIN ( SELECT max( salary ) AS max_salary FROM employees ) m ON e.salary = m.max_salary

例子 4：查找没有订单的客户

描述：
我们想找到所有没有下过订单的客户。

• 嵌套子查询方式：先在子查询中获取所有下过订单的客户 ID，然后在主查询中筛选出不在这些客户 ID 中的客户。
• 关键问题：使用 NOT IN 时，子查询可能包含 NULL 值，而这会导致主查询无法正确返回结果。此外，对于大数据量表，NOT IN 操作的性能也较差。
• JOIN 优化方式：通过左连接（LEFT JOIN），将客户表与订单表连接，然后筛选出那些没有匹配订单的客户。相比 NOT IN，这种方法更直观，同时能够避免 NULL 值的问题，并且通常性能更优。

嵌套子查询方式

SELECT
	customer_id 
FROM
	customers 
WHERE
	customer_id NOT IN (SELECT customer_id  FROM orders )

JOIN 优化方式

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

重点：“反复匹配主查询” 什么意思？

在“嵌套子查询方式”中，“反复匹配主查询” 指的是：对于客户表中的每一行，数据库需要查询子查询的结果，以检查该客户是否满足条件（即订单数量大于 1）。具体来说：

1. 子查询通常会提前计算出满足条件的客户 ID（比如订单数大于 1 的客户）。
2. 主查询会逐行扫描客户表，对于每一行客户数据，检查该客户 ID 是否出现在子查询的结果中。

如果子查询的结果是一个较大的数据集（比如数百万行客户 ID），数据库可能需要为客户表的每一行都在这个子查询结果中查找匹配。这种逐行扫描和匹配的操作会消耗大量资源，尤其是在缺少索引优化或子查询无法缓存的情况下，性能会大幅下降。

为什么 JOIN 更高效？

在 JOIN 的方式中，子查询生成的统计结果（比如订单数大于 1 的客户 ID）被视为一个临时表，并与客户表进行直接连接。这意味着：

1. 数据库一次性加载子查询的结果并进行连接，而不是在每一行客户记录中重复执行子查询。
2. 数据库优化器能够利用索引和连接条件来高效规划查询路径。
3. JOIN 能够减少重复的计算，从而显著提升查询效率。

换句话说，JOIN 避免了“主查询每行都重复检查子查询”的问题，而是一次性处理整个数据集，从整体上优化了查询性能。

搬运：https://zhuanlan.zhihu.com/p/20339398012

SQL优化 JOIN 子查询优化

https://blog.xqlee.com/article/2504231042503021.html