1 示例

表：

CREATE TABLE `test`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`number_code` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `test_number_code`(`number_code`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

2 各字段表示什么？

id: 查询序号
select_type: 查询类型
table: 查询引用的表
partitions: 查询所涉及的分区
type: 扫描类型
possible_keys: 可能用到的索引
key: 实际用到的索引，如果这一项为 NULL，说明没有使用索引。
key_len: 使用的索引长度（字节数）
ref: 连接匹配的列或常量
rows: 扫描的数据行数
filtered: 过滤后的预估行数百分比
Extra: 额外执行信息

重点关注 type 和 key

3 select_type

表示 SELECT 语句的复杂程度或所属类别，常见取值有：

SIMPLE：简单查询，不包含子查询或 UNION

PRIMARY：最外层的查询（在包含子查询或 UNION 时）

SUBQUERY：子查询（在 SELECT 或 WHERE 子句中，不依赖外层）

DERIVED：派生表（FROM 子句中的子查询，会先生成临时表）

UNION：UNION 中的第二个或后续的查询

UNION RESULT：从 UNION 结果中获取最终结果的查询

DEPENDENT SUBQUERY：依赖外层查询结果的子查询（可能会被多次执行）

MATERIALIZED：物化子查询（将子查询结果存为临时表以提升性能）

3.1 示例

[1] SIMPLE

含义：简单查询，不包含子查询、UNION 或派生表。

-- 示例
SELECT * FROM user WHERE age > 18;

整个查询只有一个表，没有嵌套，所以 select_type = SIMPLE。

---

[2] PRIMARY
含义：在包含子查询或 UNION 的查询中，最外层的 SELECT 被标记为 PRIMARY。

-- 示例（子查询场景）
SELECT name FROM user 
WHERE id = (SELECT user_id FROM orders WHERE amount = 999);

外层 SELECT name FROM user ... 是 PRIMARY，内层子查询是 SUBQUERY。

---

[3] SUBQUERY
含义：子查询出现在 SELECT 或 WHERE 子句中（且不依赖外层）。

-- 示例
SELECT name FROM user 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

内层的 SELECT user_id FROM orders 是 SUBQUERY。
注意：如果该子查询只执行一次，不会随外层行变化而变化。

---

[4] DEPENDENT SUBQUERY

含义：子查询的执行依赖外层查询的当前行，通常需要对外层的每一行都执行一次子查询。

-- 示例
SELECT name FROM user u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100);

内层子查询引用了外层表的列 u.id，因此每扫描一行 user，就可能执行一次子查询 → DEPENDENT SUBQUERY。
性能风险：外层行数多时很慢，尽量改写为 JOIN。

---

[5] DERIVED

含义：派生表，即 FROM 子句中的子查询，MySQL 会将其结果先物化为一个临时表。

-- 示例
SELECT avg_age 
FROM (SELECT age FROM user WHERE city = 'Beijing') AS derived_table;

子查询 SELECT age FROM user ... 位于 FROM 子句中，被标记为 DERIVED。
执行时会先创建临时表，再从中读取。

---

[6] UNION
含义：UNION 中第二个及之后的 SELECT 语句。

-- 示例
SELECT id, name FROM user WHERE age < 20
UNION
SELECT id, name FROM user WHERE age > 60;

第一个 SELECT 是 PRIMARY，第二个 SELECT 是 UNION。

---

[7] UNION RESULT

含义：从多个 UNION 子查询的结果集中收集最终结果的临时表操作。

-- 沿用上面的 UNION 示例
-- EXPLAIN 会额外多出一行，select_type = 'UNION RESULT'

该行表示 MySQL 将两个 SELECT 的结果去重（或合并）后输出。
通常没有对应的 SQL 行，是执行计划中的内部步骤。

---

[8] MATERIALIZED

含义：将子查询结果物化为临时表（索引表），以避免重复执行。通常出现在 IN 子查询中，且优化器认为物化更优。

-- 示例（MySQL 可能选择物化）
SELECT * FROM user 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

如果子查询结果集较小且多次被引用，MySQL 会将其物化为临时表，此时子查询的 select_type 显示为 MATERIALIZED。
实际执行计划中，会先执行子查询并存入临时表，外层再与该临时表进行半连接。

---

[9] UNCACHEABLE SUBQUERY

含义：子查询不可缓存（例如包含用户变量、RAND() 等非确定性函数）。

-- 示例
SELECT name FROM user 
WHERE id = (SELECT user_id FROM orders WHERE amount = RAND() * 1000);

子查询每次执行结果可能不同，无法缓存，标记为 UNCACHEABLE SUBQUERY。

---

补充说明

• 以上取值可能同时出现在同一个 EXPLAIN 输出中的不同行。
• 实际 MySQL 版本不同（如 5.7 vs 8.0）优化策略会有差异，某些类型（如 MATERIALIZED）在 8.0 中可能被半连接优化替代。
• 使用 EXPLAIN FORMAT=TREE 或 EXPLAIN ANALYZE 可以看到更精确的执行过程。

如果你对某个特定类型的优化方式或改写方法感兴趣，我可以进一步展开说明。

4 type

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

All（全表扫描）：在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。

index（全索引扫描）：index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。

range（索引范围扫描）：range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref（非唯一索引扫描）：ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref（唯一索引扫描）：eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么
使用EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const（结果只有一条的主键或唯一索引扫描）：const 类型表示使用了主键或者唯一索引与常量值进行比较，
比如 select name from product where id=1。需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两类型有所区别const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

5 partitions

**含义：**当表使用了分区（PARTITION BY …），这一列显示查询实际会访问哪些分区。

如果表没有分区，显示 NULL。

如果查询只扫描部分分区（分区裁剪），会列出分区名，如 p1,p2。

如果扫描所有分区，可能显示所有分区名或 ALL（取决于 MySQL 版本）。

作用：检查分区裁剪是否生效，避免全分区扫描。

示例：

-- 创建一个分区表
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 查询 2023 年的数据
EXPLAIN SELECT * FROM sales WHERE YEAR(sale_date) = 2023;

输出示例（partitions 列）：

id	select_type	table	partitions	type	…
1	SIMPLE	sales	p2023	ALL	…
partitions = p2023：只扫描 p2023 分区，其他分区被裁剪掉。

如果查询条件无法利用分区裁剪（如 WHERE sale_date > ‘2022-01-01’ 可能扫描多个分区），则 partitions 会列出多个分区名。

6 key_len

**含义：**MySQL 决定使用的索引中，实际参与查找的最大字节长度。

它表示索引中用于匹配记录的前缀长度（对于复合索引，是多个列的长度之和）。

单位是字节，可以帮助判断索引是否被充分利用（例如，复合索引中用了多少列）。

NULL 表示没有使用索引。

影响因素：

列的数据类型（INT=4 字节，BIGINT=8 字节，CHAR(10)=10×字符集字节数等）。

是否为 NULL（可为空的列会额外占用 1 字节）。

是否使用部分索引前缀（例如 INDEX(name(10)) 只取前 10 个字符）。

字符集（utf8mb4 中每个字符占 4 字节）。

示例
示例 1：单列索引

CREATE TABLE t1 (
    id INT PRIMARY KEY,      -- INT: 4 字节
    name VARCHAR(32)
);
EXPLAIN SELECT * FROM t1 WHERE id = 100;

输出中 key_len = 4（因为 id 是 INT，占 4 字节，且 PRIMARY KEY 不允许 NULL，无额外开销）。

示例 2：可为 NULL 的列

CREATE TABLE t2 (
    a INT NULL,
    INDEX(a)
);
EXPLAIN SELECT * FROM t2 WHERE a = 10;

key_len = 5（INT 4 字节 + 1 字节 NULL 标志）。

示例 3：复合索引

CREATE TABLE t3 (
    a INT,
    b VARCHAR(10) CHARACTER SET utf8mb4,
    c DATE,
    INDEX idx_ab (a, b)
);
EXPLAIN SELECT * FROM t3 WHERE a = 5 AND b = 'abc';

假设 utf8mb4 下 VARCHAR(10) 最大 40 字节（10×4），且 VARCHAR 额外 2 字节存储长度，且列可为 NULL（+1）。

• a：INT NOT NULL（假设未声明 NULL，则默认可为 NULL？实际需要看表定义。为简化，假设 a INT 默认可为 NULL：4+1=5 字节）
• b：VARCHAR(10) 在 utf8mb4 下：最大 10×4=40 字节，加 2 字节长度，加 1 字节 NULL = 43 字节
• 合计 key_len ≈ 5 + 43 = 48 字节。

但实际 EXPLAIN 输出会根据实际使用的索引列计算。如果 WHERE 只用到了 a，则 key_len 只计算 a 的长度（比如 5 字节）。如果 a 和 b 都用到，则计算两者之和。

示例 4：部分索引前缀

CREATE TABLE t4 (
    name VARCHAR(100),
    INDEX idx_name (name(10))
);
EXPLAIN SELECT * FROM t4 WHERE name = 'hello world';

key_len 只计算前缀 10 个字符的长度（假设 utf8mb4，10×4=40 字节，再加 2 字节长度，加 1 字节 NULL = 43 字节），而不是完整 100 字符。

7 ref

显示在索引查找时，哪些列或常量被用来与索引列进行比较。常见取值：

const：使用常量等值匹配（如 WHERE id = 1）

db.table.column：使用某个表的某列进行关联（如 WHERE a.id = b.user_id）

func：使用了函数或表达式（如 WHERE id = ABS(?)）

NULL：未使用索引等值匹配（可能是全表扫描或范围查询）

该列与 key 列配合理解：key 是使用的索引名，ref 是具体用哪个值/列去匹配该索引。

8 filtered

表示经过表条件过滤后，剩余行数占扫描总行数的预估百分比（取值范围 0~100）。

值越小，说明表条件过滤性越强，最终需要处理的行数越少。

值越大（如接近 100），说明大部分扫描行都被保留，可能需要检查索引是否足够精准。

例如：
rows = 1000，filtered = 10.0，则最终估计返回约 100 行。
该值依赖于统计信息，不绝对精确，但对优化有参考价值。

9 Exteral

包含 MySQL 在执行计划中的关键策略或限制信息，对性能分析非常重要。常见值：

Using index：覆盖索引，直接从索引返回数据，无需回表（好）

Using where：存储引擎返回后，再通过 WHERE 条件过滤（可考虑索引优化）

Using index condition：索引条件下推（ICP），部分 WHERE 条件在索引层过滤（较好）

Using temporary：使用临时表（通常出现在 GROUP BY 或 ORDER BY 不同列时，需要关注）

Using filesort：需要额外排序（通常出现在 ORDER BY 未使用索引时，需优化）

Using join buffer：使用了连接缓存（如 BNL、BLJ），说明被驱动表无有效索引

Impossible WHERE：WHERE 条件永远为假，不会执行

No tables used：如 SELECT 1 或 SELECT NOW()

Distinct：优化 DISTINCT 操作

LooseScan / FirstMatch（多见于子查询优化）：特定的半连接策略

特别注意：Using temporary 和 Using filesort 通常是性能瓶颈的信号，应尽量通过索引消除。

---

补充半连接：

半连接（Semi-Join） 是一种专门用于处理 IN / EXISTS 子查询 的连接操作，其核心特点是：只返回左表中在右表至少有一条匹配记录的行，但不会返回右表的任何列，且即使右表有多条匹配，左表行也只出现一次。

与普通 INNER JOIN 的区别在于：

• INNER JOIN：如果右表有多条匹配，左表行会重复出现。
• SEMI JOIN：左表行最多出现一次，不关心右表具体匹配了几条。

为什么要用半连接？

对于 SELECT * FROM user WHERE id IN (SELECT user_id FROM orders) 这类子查询，MySQL 优化器可以将其重写为半连接来高效执行，避免“对左表每一行都执行一次子查询”（即依赖子查询的低效方式）。

半连接执行时，通常采用以下策略之一：

策略名	简要说明
Table pullout	将子查询中的表提升到外层做 JOIN，然后去重。
Duplicate Weedout	先做普通 JOIN，再用临时表消除重复的左表行。
LooseScan	利用索引扫描右表时跳过重复的左表关联键。
FirstMatch	当左表一行找到第一条右表匹配记录后，立即返回该左表行并继续下一行。
Materialization	将子查询结果物化成临时表（带索引），再与左表做半连接。

MySQL 的 EXPLAIN 中不会直接显示 Semi-join 字样，但会通过 Extra 列的关键词（如 FirstMatch、LooseScan、Using join buffer (Semi-join) 等）或 select_type 的变化（如 MATERIALIZED）来体现。

示例对比

原始 SQL（IN 子查询）：

SELECT * FROM user 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

等价半连接逻辑（非真实 SQL 语法，仅示意）：

SELECT user.* FROM user 
WHERE EXISTS (
    SELECT 1 FROM orders 
    WHERE orders.user_id = user.id AND amount > 100
);
-- 但半连接会避免对每个 user 都执行子查询，而是批量匹配。

MySQL 实际可能执行的优化写法（以 FirstMatch 为例）：

• 扫描 user 表，对每个 user.id，在 orders 索引上查找第一条满足 amount > 100 的记录，找到后立即返回该 user 行并继续。

为什么叫“半”连接？

因为它是 INNER JOIN 和 ANTI JOIN（反连接）之间的“一半”——只关心存在性，不关心匹配次数，也不返回右表数据。

如何确认是否使用了半连接？

使用 EXPLAIN 查看，如果出现以下特征，通常表示已应用半连接优化：

• select_type 为 SIMPLE（子查询被展开合并）
• Extra 列出现 FirstMatch(...)、LooseScan(...)、Using join buffer (Semi-join)、Materialize 等
• 或者子查询的 select_type 为 MATERIALIZED 且外层有 IN / EXISTS

可以通过 SHOW VARIABLES LIKE 'optimizer_switch'; 查看 semijoin 是否开启（MySQL 5.6+ 默认开启）。

半连接是 MySQL 子查询优化中非常重要的一环，理解它能帮助你写出更高效的 IN/EXISTS 查询，也能读懂执行计划中的关键提示。如果你想看某个具体半连接策略（如 FirstMatch）的执行计划示例，我可以进一步演示。