MYSQL - explain

MYSQL - explain

准备数据

drop table orders;
drop table products;
drop table users;

CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price FLOAT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_price FLOAT NOT NULL,
product_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table users add index index_name_email (name,email);


INSERT INTO users (name,email, password)
VALUES ('张三', 'zhangsan@example.com', 'password123'),
('李四', 'lisi@example.com', 'password123'),
('王五', 'wangwu@example.com', 'password123'),
('赵六', 'zhaoliu@example.com', 'password123'),
('钱七', 'qianqi@example.com', 'password123');

INSERT INTO products (name, price)
VALUES ('产品 1', 10.00),
('产品 2', 15.00),
('产品 3', 20.00),
('产品 4', 12.00),
('产品 5', 18.00);

INSERT INTO orders (user_id, order_date, total_price, product_id)
VALUES (1, '2023-02-18 10:00:00', 100.00, 1),
(2, '2023-02-18 11:00:00', 50.00, 2),
(3, '2023-02-18 12:00:00', 20.00, 3),
(4, '2023-02-18 13:00:00', 15.00, 4),
(5, '2023-02-18 14:00:00', 25.00, 5);

explain 详解

EXPLAIN 后会返回一行 / 多行结果，核心字段如下（按重要性排序）：

字段	核心含义
id	查询执行的顺序（子查询 / 关联查询的层级） 每个select对应一个id, 从1开始自增
select_type	查询类型（简单查询 / 子查询 / 派生表 / 联表等）
table	执行当前行的表名（或派生表别名）
type	核心指标：访问类型（全表扫描 / 索引扫描等，性能从差到优排序）
possible_keys	可能用到的索引（优化器评估的候选索引）
key	核心指标：实际使用的索引（NULL 表示未用索引）
key_len	使用的索引长度（越短越好，可判断是否用了联合索引的全部列）
ref	与索引比较的列 / 常量（如 const 表示常量，t1.id 表示关联列）
rows	优化器预估要扫描的行数（越小越好，预估值≠实际值）
filtered	符合条件的百分比
Extra	核心指标：额外信息（如文件排序、临时表、全表扫描等）

id：查询执行顺序

• 相同 id：执行顺序由上到下（如联表查询）；
• 不同 id：id 越大，执行优先级越高（如子查询）；
• NULL：在UNION操作之后执行，表示这是聚合 / 临时表操作（如 GROUP BY 生成的临时表）。

例子1： 相同 id，从上到下

explain
SELECT users.name, orders.total_price, products.price
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN products ON orders.product_id = products.id;

例子2：不同id，先执行大的

explain
select * from orders where product_id = (select id from products where products.price = 10);

例子3：id相同和不同都存在，先执行大，再从上到下

set session optimizer_switch='derived_merge=off'; #关闭MySQL5.7对衍生表合并优化

explain
select orders.*
from (select id from products) as temp inner join orders on temp.id = orders.product_id;

set session optimizer_switch='derived_merge=on'; #还原配置

例子4： NULL，最后执行

explain
select id from users
union
select id from products;

select_type：查询类型

类型	说明
SIMPLE	简单查询（无子查询、无联表、无 UNION）
PRIMARY	主查询（包含子查询 / UNION 时的外层查询）
SUBQUERY	子查询（SELECT/WHERE 中的子查询，不依赖外层）
DEPENDENT SUBQUERY	子查询（SELECT/WHERE 中的子查询，依赖外层）
DERIVED	派生表（FROM 中的子查询，MySQL 会生成临时表）
UNION	UNION 中第二个及以后的查询
UNION RESULT	UNION 的结果集（无实际表操作）

type: 访问类型（性能从差到优）

核心判断指标，目标是至少达到 range，最优为 const/system：

类型	说明	性能
ALL	全表扫描（最差，需优化）	最差
index	全索引扫描（遍历整个索引，比 ALL 略好）	差
range	索引范围扫描（如 >/</BETWEEN/IN）	中
ref	非唯一索引扫描（匹配多行，如普通索引）	好
eq_ref	唯一索引扫描（联表查询时匹配单行，如主键 / 唯一索引）	更好
const/system	常量查询（主键 / 唯一索引匹配单行，如 WHERE id=1）	最优
它描述了 MySQL 在查询时如何查找表中的行（访问类型），直接反映了查询的效率。type 从优到劣的排序大致为：

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1. NULL（最优）

• 含义：MySQL 无需访问表/索引就能直接得到结果（比如查询常量、聚合函数计算）。
• 场景：查询不涉及任何表，或通过常量计算即可返回结果。
• 示例：

  EXPLAIN SELECT 1 + 2; -- type = NULL
  EXPLAIN SELECT COUNT(*) FROM (SELECT 1) AS t; -- type = NULL
  

2. system

• 含义：表只有 1 行记录（系统表），是 const 的特例，几乎只出现在 mysql 系统库的表中。
• 场景：仅针对系统表（如 mysql.user 单一行场景），业务表几乎不会出现。
• 示例：

  -- 假设 mysql.procs_priv 只有1行（测试环境）
  EXPLAIN SELECT * FROM mysql.procs_priv LIMIT 1; -- 可能为 system
  

3. const

• 含义：通过主键/唯一索引等值查询，最多返回 1 行结果，MySQL 会将其优化为常量（查询执行时直接替换为值）。
• 场景：主键/唯一索引的等值条件查询。
• 示例：

  -- user 表 id 是主键
  EXPLAIN SELECT * FROM user WHERE id = 1; -- type = const
  

4. eq_ref

• 含义：多表关联时，被关联表通过主键/唯一索引等值匹配，每行匹配仅返回 1 行，是关联查询的最优类型。
• 场景：JOIN 语句中，被关联表用主键/唯一索引作为关联条件。
• 示例：

  EXPLAIN SELECT * FROM orders
  JOIN users ON orders.id = users.id
  

5. ref

• 含义：通过普通索引（非唯一、非主键）等值查询，可能返回多行匹配结果。
• 场景：普通索引的等值查询、关联查询中用普通索引关联。
• 示例：

  EXPLAIN SELECT * FROM orders
  JOIN users ON orders.user_id = users.id
  

-- user 表 name 是普通索引（非唯一）
EXPLAIN SELECT * FROM user WHERE name = '张三'; -- type = ref

6. ref_or_null

• 含义：类似 ref，但额外包含对 NULL 值的查询（需处理 字段 IS NULL 场景）。
• 场景：普通索引等值查询 + 空值判断。
• 示例：

  EXPLAIN SELECT * FROM users WHERE name = '张三' OR name IS NULL; -- type = ref_or_null
  

7. index_merge

• 含义：MySQL 使用索引合并优化，将多个单列索引的结果合并（如 OR/AND 组合多个索引）。
• 场景：查询条件包含多个单列索引，且 MySQL 选择合并索引而非全表扫描。
• 示例：

  -- user 表 name、age 均为单列索引
  EXPLAIN SELECT * FROM users WHERE name = '张三' OR age = 20; -- type = index_merge
  

8. unique_subquery

• 含义：子查询中使用主键/唯一索引（替代 IN 子查询，优化为索引查找）。
• 场景：IN (SELECT 主键 FROM ...) 形式的子查询。
• 示例：

  EXPLAIN SELECT * FROM users 
  WHERE id IN (SELECT user_id FROM `order` WHERE id = 100); -- type = unique_subquery
  

9. index_subquery

• 含义：类似 unique_subquery，但子查询使用普通索引（非唯一）。
• 场景：IN (SELECT 普通索引 FROM ...) 形式的子查询。
• 示例：

  -- order 表 user_id 是普通索引
  EXPLAIN SELECT * FROM users 
  WHERE name IN (SELECT user_name FROM `order` WHERE id = 100); -- type = index_subquery
  

10. range

• 含义：通过索引进行范围查询（如 BETWEEN/IN/>/</>=/<=/LIKE 'xxx%'）。
• 场景：索引字段的范围条件查询（注意：LIKE '%xxx' 会失效索引，type 变为 ALL）。
• 示例：

  EXPLAIN SELECT * FROM users WHERE id BETWEEN 1 AND 10; -- type = range
  EXPLAIN SELECT * FROM users WHERE id IN (1,2,3); -- type = range
  EXPLAIN SELECT * FROM users WHERE name LIKE '张%'; -- type = range
  

11. index

• 含义：遍历整个索引树（覆盖索引场景），但不访问数据行（比 ALL 快，因为索引通常比数据小）。
• 场景：查询字段全部包含在索引中（覆盖索引），且需要遍历整个索引。
• 示例：

  -- user 表有联合索引 (name, age)，查询字段仅包含索引列
  EXPLAIN SELECT name, age FROM users; -- type = index（遍历整个索引树）
  

12. ALL（最差）

• 含义：全表扫描（Full Table Scan），MySQL 遍历整个表找匹配行，效率极低。
• 场景：无索引、索引失效（如 LIKE '%xxx'/函数操作索引字段/类型不匹配）、查询条件未命中索引。
• 示例：

  EXPLAIN SELECT * FROM users WHERE age = 20; -- age 无索引，type = ALL
  EXPLAIN SELECT * FROM users WHERE name LIKE '%三'; -- 索引失效，type = ALL
  EXPLAIN SELECT * FROM users WHERE SUBSTR(name,1,1) = '张'; -- 函数操作索引，type = ALL
  

核心优化原则

1. 优先追求 const/eq_ref/ref：这三类是高效的索引查找，需确保查询条件命中主键/唯一索引/普通索引。
2. 避免 range 过度使用：范围查询会限制索引的下推，尽量拆分或优化为等值查询。
3. 杜绝 ALL（全表扫描）：通过加索引、优化查询条件（避免索引失效）解决。
4. index 类型需注意：若遍历整个索引仍慢，需优化索引设计（如更精准的索引）。

常见索引失效场景（导致 type = ALL）

• 索引字段使用函数（如 SUBSTR(name,1,1)）、运算（如 id + 1 = 10）；
• 模糊查询以 % 开头（如 LIKE '%张三'）；
• 索引字段为字符串，查询时未加引号（如 WHERE name = 123）；
• OR 条件中存在无索引的字段；
• JOIN 关联字段类型不匹配。

通过 EXPLAIN 的 type 字段，可快速定位查询的性能瓶颈，核心思路是让 MySQL 尽可能使用高效的索引访问类型，避免全表扫描。

key_len: 查询实际使用的索引长度（单位：字节）

在 MySQL EXPLAIN 输出中，key_len 是核心字段之一，用于表示查询实际使用的索引长度（单位：字节），它能帮你判断：

1. 索引是否被充分利用（是否只用到索引的部分列）；
2. 联合索引的匹配程度（前缀匹配/全列匹配）；
3. 索引字段的存储类型、是否为 NULL 对长度的影响。

一、key_len 计算规则

key_len 的值由字段类型、字符集、是否允许 NULL、是否有固定长度共同决定，核心公式：

key_len = 字段本身长度 + 字符集额外长度 + NULL 标志位（可选）

1. 基础类型长度（固定长度）

字段类型	基础长度（字节）	说明
TINYINT	1	无符号/有符号不影响长度
SMALLINT	2
MEDIUMINT	3
INT	4
BIGINT	8
DATE	3
DATETIME	8	MySQL 5.6+ 支持小数秒，额外+0~3字节
CHAR(n)	n × 字符集单字符长度	固定长度，不足补空格
VARCHAR(n)	n × 字符集单字符长度 + 2	2字节用于存储字符串实际长度

2. 字符集额外长度

不同字符集的单字符占用字节不同，直接影响 key_len：

• ASCII/latin1：1 字节/字符
• gbk：2 字节/字符
• utf8：3 字节/字符
• utf8mb4：4 字节/字符

3. NULL 标志位

• 字段允许 NULL：额外 +1 字节（存储 NULL 标志）；
• 字段设置 NOT NULL：不加这 1 字节。

二、核心示例（直观理解）

假设数据库字符集为 utf8（3 字节/字符），创建测试表：

CREATE TABLE `user` (
  `id` INT NOT NULL PRIMARY KEY,          -- INT(4) + NOT NULL = 4 字节
  `name` VARCHAR(10) DEFAULT NULL,        -- VARCHAR(10):10×3 + 2（长度） +1（NULL）=33
  `age` TINYINT NULL,                     -- TINYINT(1) +1（NULL）=2
  `phone` CHAR(11) NOT NULL,              -- CHAR(11):11×3 + NOT NULL=33
  `create_time` DATETIME NOT NULL,        -- DATETIME(8) + NOT NULL=8
  KEY `idx_name_age` (`name`, `age`)      -- 联合索引：name(33) + age(2) = 35
);

示例 1：单字段索引（主键 INT NOT NULL）

EXPLAIN SELECT * FROM user WHERE id = 1;
-- key_len = 4（INT 4字节 + NOT NULL 无额外）

示例 2：VARCHAR 字段（允许 NULL，utf8）

EXPLAIN SELECT * FROM user WHERE name = '张三';
-- key_len = 10×3（utf8） + 2（VARCHAR长度） +1（NULL）= 33

示例 3：CHAR 字段（NOT NULL，utf8）

EXPLAIN SELECT * FROM user WHERE phone = '13800138000';
-- key_len = 11×3（utf8） + NOT NULL = 33

示例 4：联合索引（部分匹配）

联合索引遵循「最左前缀原则」，只匹配前缀列时，key_len 仅计算匹配列的长度：

-- 只匹配联合索引的第一列 name
EXPLAIN SELECT * FROM user WHERE name = '张三';
-- key_len = 33（仅 name 的长度）

-- 匹配联合索引的 name + age
EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = 20;
-- key_len = 33（name） + 2（age） = 35

示例 5：字段为 NOT NULL 时的变化

若修改 name 为 NOT NULL：

ALTER TABLE user MODIFY name VARCHAR(10) NOT NULL;
EXPLAIN SELECT * FROM user WHERE name = '张三';
-- key_len = 10×3 + 2（VARCHAR长度） = 32（去掉了 NULL 的 1 字节）

三、key_len 的实用价值

1. 判断联合索引是否被全列使用

比如联合索引 idx_a_b_c (a,b,c)：

• 若 key_len 仅包含 a 的长度 → 只用到了索引第一列；
• 若包含 a+b 的长度 → 用到了前两列；
• 若包含 a+b+c 的长度 → 全列匹配（最优）。

2. 排查索引失效问题

比如预期使用联合索引，但 key_len 远小于全列长度 → 说明索引只匹配了前缀列，后续列未命中（通常是查询条件不满足最左前缀）。

3. 验证索引设计合理性

若 key_len 过大（比如 VARCHAR(255) + utf8mb4 → 255×4+2=1022 字节），需考虑：

• 是否可以缩短字段长度（如 VARCHAR(50)）；
• 是否用固定长度类型（如 INT 替代字符串存储ID）；
• 是否设置 NOT NULL（减少 1 字节）。

四、注意事项

1. key_len 是「最大可能长度」：MySQL 计算的是索引字段的最大长度，而非实际查询值的长度（比如 VARCHAR(10) 存了 2 个字符，key_len 仍按 10 计算）。
2. 不包含隐式转换的长度：若索引字段是字符串，查询时传入数字（如 WHERE name = 123），会触发隐式转换，索引失效，key_len 为 NULL。
3. TEXT/BLOB 类型：无法直接作为索引前缀以外的部分，key_len 仅计算前缀长度（如 KEY idx_text (content(10)) → key_len=10×字符集长度）。
4. key_len 为 NULL：表示查询未使用任何索引（type 通常为 ALL）。
5. 在 MySQL 中，key_len 计算时只有变长字符串类型（VARCHAR/TEXT/BLOB 等）需要额外的「长度标识字节」，而固定长度类型（如 TINYINT/INT/CHAR/DATE 等）无需这个「结束符 / 长度标识」。

五、总结

key_len 是分析索引使用效率的「量化指标」：

• 数值越大 → 索引匹配的列越多/字段长度越长；
• 数值越小 → 仅用到索引的部分列（或短字段）；
• NULL → 未使用索引。

优化时需结合 type（访问类型）和 key_len 一起分析：比如 type=ref 且 key_len 接近索引全列长度 → 索引使用最优；若 type=range 但 key_len 过小 → 需检查索引是否匹配完整。

ref：与索引列 (key) 匹配的「比较条件」是什么

在 MySQL EXPLAIN 输出中，ref 字段用于描述与索引列匹配的「比较条件」是什么 —— 具体来说，它表示：索引列是和一个常量（const）、某个列（如关联表的列）、还是一个函数结果等进行匹配，核心反映了「索引列的匹配值来源」。

一、ref 字段的核心含义

ref 字段的值直接关联 type 字段（访问类型）：

• 当 type = ref/eq_ref/ref_or_null 等索引匹配类型时，ref 才有实际意义；
• 当 type = const/range/ALL/NULL 时，ref 通常为 NULL（或 const）；
• 简单说：ref 回答了「索引列在查询中是和什么值做匹配的？」。

二、ref 字段的实用价值

1. 验证索引是否「正确匹配」

• 预期用索引匹配常量，但 ref = NULL → 索引未生效（如字段类型不匹配、函数操作索引）；
• 预期 JOIN 关联列匹配，但 ref = NULL → 关联列无索引，或关联条件写错误。

2. 区分「常量匹配」和「列匹配」

• ref = const：最优的索引匹配（常量等值查询）；
• ref = 表名.列名：关联查询的正常匹配（需确保关联列有索引）；
• ref = func：索引失效的高危信号（需优化查询，避免函数操作索引列）。

3. 结合 type 字段分析性能

type	ref 取值	性能分析
ref	const	高效（普通索引匹配常量）
ref	表名.列名	良好（关联查询，索引生效）
eq_ref	表名.列名	最优（主键/唯一索引关联匹配）
ALL	NULL	低效（全表扫描，无索引）
range	NULL	中等（索引范围查询）

三、常见问题与优化

问题 1：ref = NULL，但预期用索引？
原因：

• 索引列被函数/运算包裹（如 SUBSTR(name,1,1) = '张'）；
• 字段类型不匹配（如 name 是 VARCHAR，查询用 WHERE name = 123）；
• 联合索引未满足最左前缀（如 idx_name_age (name,age)，仅查 age = 20）。

优化：

• 避免函数操作索引列；
• 保证查询值与字段类型一致；
• 遵循联合索引最左前缀原则。

问题 2：ref = func，type = ALL？
原因：索引列参与函数/表达式运算，索引失效。
优化：

• 改写查询，将函数移到等号右侧（如 WHERE name = SUBSTR('张三123',1,2)）；
• 若无法改写，考虑新增生成列索引（如 ALTER TABLE user ADD COLUMN name_prefix VARCHAR(2) GENERATED ALWAYS AS (SUBSTR(name,1,2)) STORED, ADD KEY idx_name_prefix (name_prefix);）。

四、总结

ref 字段是 type 字段的「补充说明」：

• type 告诉我们「MySQL 用什么方式查索引」（如 ref/eq_ref/range）；
• ref 告诉我们「MySQL 用什么值去匹配索引列」（如常量/关联列/函数）。

优化核心：

• 尽量让 ref = const（常量匹配）或 ref = 表名.列名（关联列匹配）；
• 避免 ref = func 或 ref = NULL（索引失效/未使用）。

row 和 filtered

基于你提供的用户、产品、订单案例，我们把 EXPLAIN 中的 rows 和 filtered 字段结合起来讲解——这两个字段是「黄金搭档」，rows 表示「预估扫描行数」，filtered 表示「扫描行中符合条件的比例」，两者结合能精准判断查询的实际有效扫描行数。

字段	含义	取值范围	核心作用
rows	MySQL 优化器预估的「需要扫描的行数」（基于索引统计信息）。	正整数	反映「扫描范围大小」，越小越好。
filtered	预估的「扫描行中符合查询条件的比例」（百分比），默认显示小数（如 10.00 表示 10%）。	0.00 ~ 100.00	反映「扫描行的有效率」，越高越好（100% 表示扫描的行全部符合条件）。

关键公式

实际有效扫描行数 ≈ rows × (filtered / 100)

这个公式能帮你判断：MySQL 扫描的行数中，有多少是「真正有用的」。

先还原案例基础：

• 表数据：users/products/orders 各 5 行；
• 索引：users 有联合索引 index_name_email (name, email)，orders.user_id 暂未加索引；
• 新增数据：为了体现 filtered，我们给 users 插入 1 条重复姓名的记录：

  INSERT INTO users (name,email, password) VALUES ('张三', 'zhangsan2@example.com', '123456');
  -- 此时 users 表共 6 行，其中 name='张三' 的有 2 行
  

场景 1：rows 小 + filtered=100%（最优组合）

查询 users 表中 id=1 的用户（主键精准匹配）。

EXPLAIN SELECT * FROM users WHERE id = 1;

字段	取值	含义
type	const	主键等值匹配，最优访问类型。
rows	1	预估扫描 1 行（主键唯一，仅扫描 id=1 的行）。
filtered	100.00	扫描的 1 行 100% 符合条件（id=1 精准匹配）。
有效行数	1×100%=1	仅扫描 1 行且全部有效，无无效扫描。

核心结论
rows 极小 + filtered=100% 是最优查询，代表扫描行数最少且无无效数据，性能最佳。

场景 2：rows 大 + filtered 低（最差组合）

查询 orders 表中 user_id=1 且 total_price>20 的订单（user_id 无索引，全表扫描）。

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND total_price > 20;

字段	取值	含义
type	ALL	全表扫描，低效。
rows	5	预估扫描全表 5 行（orders 总行数）。
filtered	20.00	扫描的 5 行中，仅 20% 符合条件（仅 1 行 user_id=1 且 total_price>20）。
有效行数	5×20%=1	虽然最终只需要 1 行，但 MySQL 不得不扫描 5 行，80% 是无效数据。

优化后（给 orders.user_id 加索引）

ALTER TABLE orders ADD INDEX idx_user_id (user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND total_price > 20;

优化后字段变化

字段	取值	含义
type	ref	普通索引等值匹配，高效。
rows	1	索引匹配 user_id=1，预估扫描 1 行。
filtered	100.00	扫描的 1 行中，100% 符合 total_price>20（或根据数据调整，核心是 filtered 大幅提升）。
有效行数	1×100%=1	仅扫描 1 行且有效，无效扫描为 0。

核心结论
rows 大（全表扫描）+ filtered 低 → 大量无效扫描，是查询性能差的核心信号；加索引后 rows 骤降、filtered 提升，有效行数大幅减少。

场景 3：联合条件下的 rows 与 filtered

查询 users 表中 name='张三' 且 email LIKE '%example.com' 的用户（匹配联合索引前缀，后条件过滤）。

EXPLAIN SELECT * FROM users WHERE name = '张三' AND email LIKE '%example.com';

字段	取值	含义
type	ref	联合索引 index_name_email 匹配最左前缀 name='张三'，索引生效。
key	index_name_email	使用联合索引的 name 列。
rows	2	预估扫描 name='张三' 的 2 行（users 表中有 2 个张三）。
filtered	100.00	扫描的 2 行中，100% 符合 email LIKE '%example.com'（所有张三的邮箱都符合）。
有效行数	2×100%=2	索引缩小了扫描范围（从 6 行→2 行），且过滤后无无效数据。

变种场景（filtered 降低）

-- 新增一个张三的邮箱不符合条件
INSERT INTO users (name,email, password) VALUES ('张三', 'zhangsan@test.com', '654321');
EXPLAIN SELECT * FROM users WHERE name = '张三' AND email LIKE '%example.com';

字段	取值	含义
rows	3	预估扫描 name='张三' 的 3 行。
filtered	66.67	3 行中仅 2 行符合 email 条件（66.67%）。
有效行数	3×66.67%≈2	索引扫描 3 行，过滤掉 1 行无效数据。

联合条件中：

• 索引字段（如 name）决定 rows（扫描范围）；
• 非索引字段（如 email 模糊查询）决定 filtered（有效比例）；
• 即使 filtered 不是 100%，只要 rows 足够小，整体效率仍可接受。

场景 4：关联查询中的 rows + filtered

关联查询「张三的订单中 total_price>20 的记录」（orders.user_id 无索引）。

EXPLAIN 
SELECT u.*, o.* 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.name = '张三' AND o.total_price > 20;

表	type	rows	filtered	有效行数	含义
u	ref	3	33.33	3×33.33%≈1	扫描 3 个张三，仅 1 个张三的 id 对应订单 total_price>20。
o	ALL	5	20.00	5×20%=1	全表扫描 5 行订单，仅 20% 符合条件。

核心结论
关联查询的总有效行数 ≈ 各表 rows × filtered 的乘积（此处：1×1=1），但实际扫描行数是 3+5=8，大量无效扫描；给 orders.user_id 加索引后，o 表 rows=1、filtered=100%，总扫描行数骤降。

场景 5：filtered 反映「条件过滤效率」

关键对比：条件越精准，filtered 越高

SQL 条件	rows	filtered	有效行数	说明
WHERE name = '张三'	3	100.00	3	无额外过滤，扫描的 3 行全部有效。
WHERE name = '张三' AND age=20（假设 age 无索引）	3	33.33	1	额外过滤条件让有效比例降低，仅 1 行符合。

rows + filtered 的实用价值

1. 精准判断「无效扫描」

• 若 rows 大但 filtered 低 → 大量扫描行是无效的，需优化：
  • 给过滤字段加索引（缩小 rows）；
  • 调整查询条件（让过滤更精准，提升 filtered）。
    2. 验证索引是否「真的有用」
• 加索引后，若 rows 降低但 filtered 极低 → 索引只缩小了扫描范围，但过滤条件仍需大量筛除无效数据，需优化过滤条件（如将非索引过滤条件改为索引字段）。
  3. 分析关联查询的性能瓶颈
• 关联查询中，某张表的 rows × filtered 数值最大 → 这张表是性能瓶颈，优先优化（加索引/调整关联条件）。

实操优化建议（基于你的案例）

1. 给 orders.user_id/product_id 加索引：将 orders 表的 rows 从 5 降到 1，filtered 提升到 100%；
2. 避免「全表扫描 + 低过滤率」：任何查询条件（如 total_price>20）尽量结合索引字段（如 user_id=1），先通过索引缩小 rows，再过滤；
3. 更新统计信息：若 rows/filtered 与实际偏差大，执行 ANALYZE TABLE users, orders;，让预估更准确。

总结

组合	性能	优化方向
rows 小 + filtered 高	优	保持（索引精准匹配，过滤条件少）。
rows 大 + filtered 高	中	加索引缩小 rows（如全表扫描但过滤率 100%，加索引后 rows 骤降）。
rows 小 + filtered 低	中	优化过滤条件（如将非索引过滤改为索引字段，提升 filtered）。
rows 大 + filtered 低	差	紧急优化（加索引缩小 rows + 优化过滤条件提升 filtered）。

rows 告诉你「要扫多少行」，filtered 告诉你「扫的行里有多少能用」，两者结合才能全面评估查询效率——优化的终极目标是让 rows × (filtered/100) 最小化。

Extra: 额外信息

它是 EXPLAIN 里最丰富的字段，补充说明 MySQL 执行查询的「额外逻辑」（如是否使用索引下推、是否文件排序、是否临时表等），直接反映查询的性能瓶颈。

一、高效类 Extra（最优，无需优化）

1. Using index（覆盖索引）

• 含义：查询所需的所有字段都包含在索引中，无需回表访问数据行（仅扫描索引即可），是极致高效的场景。
• 场景：查询字段仅包含索引列，不包含非索引列。
• 示例（基于 users 表的联合索引 index_name_email (name, email)）

-- 查询字段仅为 name + email（均在索引中），无需回表
EXPLAIN SELECT name, email FROM users WHERE name = '张三';

2. Using index condition（索引下推，ICP）

• 含义：MySQL 5.6+ 新增特性，将「部分过滤条件」下推到存储引擎层（InnoDB），在索引扫描时直接过滤不符合条件的行，减少回表次数。
• 场景：联合索引匹配前缀列，且有非前缀列的过滤条件。
• 示例：

-- name 是联合索引前缀（匹配），email 是后缀（过滤），触发索引下推
EXPLAIN SELECT * FROM users WHERE name = '张三' AND email LIKE '%example.com';

3. Select tables optimized away (最优)

Select tables optimized away 是 MySQL EXPLAIN 结果中 Extra 字段的一种特殊取值，核心含义是：MySQL 优化器通过索引直接计算出聚合函数（min/max 为主）的结果，无需实际访问表数据行，甚至无需打开表—— 这是比 Using index（覆盖索引）更极致的优化，属于「最优级」性能表现。

EXPLAIN SELECT MAX(id) FROM orders;

二、中等类 Extra（需关注，可能有优化空间）

这类取值表示查询有额外操作，但未达到「低效」级别，需结合场景优化。

1. Using temporary（使用临时表）

• 含义：MySQL 需要创建临时表存储中间结果（如 GROUP BY/ORDER BY 涉及非索引列）。

• 场景：GROUP BY/ORDER BY 字段无索引，或多表关联后排序。

• 示例：

  -- 按 orders.total_price 分组，无索引，需临时表
  EXPLAIN SELECT total_price, COUNT(*) FROM orders GROUP BY total_price;
  

2. Using join buffer (Block Nested Loop)（使用连接缓冲区）

• 含义：多表 JOIN 时，被关联表无索引，MySQL 会将驱动表的数据加载到「连接缓冲区」，再逐行匹配被关联表（BNL 算法）。

• 场景：关联查询中，被关联表的关联字段无索引。

• 示例（orders.user_id 无索引）：

      EXPLAIN 
      SELECT u.*, o.* 
      FROM users u
      JOIN orders o ON u.id = o.user_id
      WHERE u.name = '张三';
  

三、低效类 Extra（必须优化，性能瓶颈）

这类取值表示查询存在严重性能问题，是优化的核心目标。

1. Using filesort（文件排序）

• 含义：MySQL 无法利用索引完成排序，需将数据加载到内存 / 磁盘进行排序（「文件排序」不是真的写文件，内存不足时才写磁盘）。
• 场景：ORDER BY 字段无索引，或排序字段与索引字段不一致。

-- 按 orders.total_price 排序，无索引，触发文件排序
EXPLAIN SELECT * FROM orders ORDER BY total_price DESC;

2. Using where（条件过滤）

• 含义：MySQL 会根据 WHERE 条件过滤行。
• 场景：简单过滤。

explain
select * from orders where total_price = 100;

这里低效是因为结合 type 来看，如果为 ref等，就是好的 。