SELECT * FROM userWHERE id IN (SELECT user_id FROM order);

一、为什么子查询慢？

1. 执行次数多（相关子查询）

子查询依赖外层查询的值，导致每行都执行一次子查询。如:

-- 例子：每行都查一次SELECT * FROM orders oWHERE o.customer_id IN (    SELECT c.id FROM customers c WHERE c.region = 'US');

2. 数据量大 + 无索引

• 子查询返回大量数据，且没有命中索引。

• MySQL 可能对子查询结果做临时表或文件排序，导致性能差。

二、如何定位和优化？

看执行计划

EXPLAIN ANALYZE SELECT ...

• 看子查询是否被物化为临时表（Using temporary）。

• 看是否使用了索引（key 字段）。

• 看是否每行都执行子查询（DEPENDENT SUBQUERY）。

EXPLAIN ANALYZE 是一种非常有用的工具，用于分析和优化 SQL 查询的性能。它可以帮助你理解数据库是如何执行查询的，以及查询的各个部分是如何影响性能的。

EXPLAIN 用于显示数据库如何执行查询的详细信息。它提供了一个执行计划，显示了查询的各个步骤，包括表的扫描方式、连接方法、使用的索引等。EXPLAIN 不实际执行查询，因此它不会返回查询的结果，也不会对数据库产生实际影响。它主要用于分析查询的逻辑结构和优化方向。

ANALYZE用于收集数据库表的统计信息。这些统计信息包括表的行数、列的分布、索引的使用情况等。数据库优化器使用这些统计信息来生成更高效的查询执行计划。通常在数据库表结构或数据发生较大变化后运行，以确保优化器能够生成最佳的执行计划。

EXPLAIN ANALYZE 是将 EXPLAIN 和 ANALYZE 结合起来的一个命令，它不仅显示查询的执行计划，还会实际执行查询，并提供实际的执行时间和资源使用情况。

怎么使用呢?

直接加在查询的语句之前即可。如:

EXPLAIN ANALYZE SELECT * FROM orders oWHERE o.customer_id IN (    SELECT c.id FROM customers c WHERE c.region = 'US');

完成！

EXPLAIN ANALYZE 的输出通常包括：

• 查询计划：

• 显示查询的逻辑结构，包括表的扫描方式、连接方法、使用的索引等。

• 例如，Seq Scan 表示顺序扫描，Index Scan 表示索引扫描。

• 实际执行时间：

• 显示每个步骤的实际执行时间，单位通常是毫秒。

• 例如，actual time=0.002..0.003 rows=1 loops=1 表示该步骤实际执行时间为 0.002 毫秒到 0.003 毫秒，返回了 1 行数据，循环了 1 次。

• 资源使用情况：

• 显示查询执行过程中使用的资源，如临时表的使用、内存使用情况等。

说了这么多怎么查看为什么慢，那怎么解决呢？

优化策略可以用下面的方法：

1. 改成 JOIN

-- 原：子查询SELECT * FROM orders oWHERE o.customer_id IN (    SELECT c.id FROM customers c WHERE c.region = 'US');
-- 改为 JOINSELECT o.*FROM orders oJOIN customers c ON o.customer_id = c.idWHERE c.region = 'US';

如果现有的解释器有点旧，用原来的子查询，则数据库拿到 orders 的每一行，都要拿它的 customer_id 去子查询里做“有没有”的判断。

• 如果优化器不够聪明，它真的会对 orders 的每一行触发一次子查询，行数就是驱动表的行数。

• 即便优化器能做“半连接”转换，也只是把“逐行问”变成“一次性问”，但这一步转换本身不一定成功，也不一定彻底。

但是，如果用join，则数据库只需要把两个表按customer_id做一次集合匹配：

• 先扫 customers，把 region='US' 的行筛出来；

• 再拿这批 id 一次性去 orders 里做匹配。
  整个过程只扫两遍表（或利用索引），没有“逐行触发”这回事。

2. 使用 EXISTS 替代 IN

-- 原... WHERE o.customer_id IN (SELECT c.id ...)
-- 改... WHERE EXISTS (    SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.region = 'US');

把 IN (子查询) 改写成 EXISTS (相关子查询) 之所以常常更快，根本原因在于 “半连接” 的执行方式变了：
前者容易退化成“先算完子查询，再拿着结果集去主表一条条比对”；
而后者天然就是“逐行短路”，一旦找到匹配就立刻返回，不再继续找，也不再把子查询结果物化成临时集合。

IN：数据库必须先把子查询里所有满足 region='US' 的 id 收集齐全，再去和主表的 customer_id 做集合成员测试。
EXISTS：对主表的每一行，只需在子查询里发现第一条满足 c.id = o.customer_id 的记录即可返回 true，后面的行不再扫描。

此外，IN 子查询的结果在不少数据库里会被做成临时表（derived 表、hash 表、或排序后的数组）。
EXISTS 子查询由于与主表行相关，优化器通常直接把它转成嵌套循环或半连接，不再物化，内存和 CPU 都省一步。

3. 提前聚合

-- 原SELECT o.*, (    SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paidFROM orders o;
-- 优化SELECT o.*, p.total_paidFROM orders oLEFT JOIN (    SELECT order_id, SUM(amount) AS total_paid    FROM payments    GROUP BY order_id) p ON p.order_id = o.id;

提前聚合，就是把“每一行都要重新算一次”的聚合计算，改成“先一次性把所有结果算完，再拿现成的值去匹配”。

原来的写法运行是这样的：

• 数据库先扫 orders 表，拿到第一行 order；

• 然后针对这一行的 id，再去 payments 表里把所有 amount 求和；

• 再拿下一行 order，重复一次求和……

• orders 有多少行，payments 就被扫多少遍（或索引回表多少次）。
  这种“一行触发一次聚合”的模式，行数放大、I/O 放大，自然慢。

优化聚合后：

• 先把payments全表扫一遍（或走索引），按order_id分组，把每个 order_id 对应的SUM(amount)算出来，生成一张只有order_id和 total_paid两列的小结果集；

• 再把这张小结果集跟orders做连接；

• orders 有多少行，都只跟这张已经算好的小表做一次匹配，不再重复求和。

在标准 SQL 的执行模型里，LEFT JOIN 的左右两边会先被当成两个独立的输入流，优化器决定谁先谁后、用哪种算法（嵌套循环、哈希连接、排序合并等）。概括起来：

1. 逻辑顺序
   FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
   我们看到 LEFT JOIN 发生在 FROM/JOIN 阶段，此时左右两边都必须先准备好各自的结果，然后才能做连接。

2. 物理顺序由优化器决定

   如果右表很小、左表很大，优化器可能先扫右表，把结果装进内存（哈希表），再扫左表去匹配。

   如果右表很大、左表很小，也可能反过来先扫左表。

   也有可能两边同时扫（并行哈希连接）。

3. 子查询提前聚合的那一步
   在上面的例子里，右表是一个派生表（子查询），这个子查询要先算完，它是被优化器当成一个整体“右表”。

阅读原文：原文链接