4_mysql用explain查看执行计划和索引测试

发表于 更新于 分类于 ， 阅读次数： Waline： 本文字数： 12k 阅读时长 ≈ 11 分钟

当你执行 EXPLAIN SELECT … 时，MySQL 内部的查询优化器 (Query Optimizer) 会开始工作。它会分析你的 SQL，评估多种可能的执行路径（比如，使用索引 A、使用索引 B、或者干脆全表扫描），并基于一个成本模型 (Cost Model) 来选择它认为总成本最低的那个计划。EXPLAIN 就是将这个最终选定的计划以表格形式展示给你。

1. Explain

1.1 字段

列名解释
id查询中操作的序列号。id 越大，越先执行；id 相同，则从上到下执行。
select_type查询的类型（SIMPLE：简单查询，SUBQUERY：子查询，UNION：联合查询等）。
table这一步操作正在访问的表。
type【最重要】 访问类型，显示了查找数据的方式。从最好到最差依次是： system > const > eq_ref > ref > range > index > ALL。ALL 代表全表扫描，通常是优化的首要目标。
possible_keysMySQL 认为 “ 可能 “ 可以用于这个查询的索引列表。
keyMySQL 最终 “ 实际决定 “ 使用的索引。如果为 NULL，则表示没有使用索引。
rowsMySQL 估算要读取并检查才能找到所需结果的行数。这是个估算值，但非常关键。原则上 rows 越少越好。
filtered一个百分比，表示 WHERE 条件过滤后，剩下的数据行占 rows 的比例。
Extra【第二重要】 包含额外但至关重要的信息。比如 Using index (使用了覆盖索引，很棒！), Using where (需要回表查询), Using temporary (使用了临时表), Using filesort (进行了文件排序，性能杀手)。

1.2 Type 性能排序

从最优到最差排序：

  • system: 表只有一行数据（系统表），是 const 的特例。几乎不会出现。
  • const: 通过主键或唯一索引，最多只匹配一行数据。速度极快。
    • explain select * from user_info where id = 2
  • eq_ref: 在 JOIN 中，驱动表（前一张表）的每一行，在被驱动表（当前表）中都只通过主键或唯一索引匹配到一行数据。是 JOIN 的最佳类型。
    • EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id
  • ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。例如，基于普通索引的等值查询。
  • fulltext: 全文索引。
  • ref_or_null: 类似 ref，但会额外搜索 NULL 值的行。
  • index_merge: 查询使用了多个单列索引，然后将结果合并。
  • unique_subquery: IN 子查询中，子查询返回的是唯一值。
  • range: 使用索引进行范围查询。例如 BETWEEN, >, <, IN() 等。
  • index: 全索引扫描。和 ALL 类似，但只扫描索引树，不扫描表数据。如果 Extra 中有 Using index，表示是覆盖索引，性能尚可。否则表示需要回表，性能较差。
  • ALL: 全表扫描 (Full Table Scan)。遍历整张表来查找匹配的行，是性能最差的情况，是优化的首要目标。

1.3 Extra 信息

A. 绿色信号 (好消息)

  • Using index: 覆盖索引。
    • 查询所需的所有列都可以在这个索引中找到，因此存储引擎无需再访问数据表（即 “ 回表 “）。这是极高的性能优化，尤其是在 COUNT(*) 或只查询索引列时。
  • Using index condition (ICP): 索引下推。
    • 这是一个重要的优化。它允许存储引擎在使用索引检索行时，就用 WHERE 子句中其他条件（无法用于索引定位，但其列包含在索引中）来过滤数据。这减少了从存储引擎传回给 Server 层的行数，降低了 I/O 和 CPU 消耗。
  • Select tables optimized away:
    • 查询可以直接从索引的元信息或聚合函数中得到结果，根本不需要访问表。例如 SELECT MIN(id) FROM users (id 是主键）。
  • No tables used: 查询不涉及任何表
    • 例如 SELECT NOW(); 或者 SELECT 1;。

B. 黄色信号 (需警惕)

  • Using where
    • 这是一个非常常见的信息。它表示存储引擎返回数据后，MySQL Server 层需要对这些行再次应用 WHERE 子句进行过滤。
    • 它本身不是坏事，但它提示你：当前索引无法覆盖所有的 WHERE 条件。
    • 与 Using index 结合: Using index; Using where 表示使用了覆盖索引，但索引中的数据仍需进一步过滤。性能不错。
    • 单独出现: 表示发生了回表，然后在 Server 层进行过滤。你需要思考：是否可以通过创建更合适的索引来消除 Using where 或将其转变为 Using index condition？
  • Using join buffer (Block Nested Loop / Batched Key Access)
    • 当两个表进行 JOIN 时，如果关联字段在被驱动表上没有索引，MySQL 不得不使用一块内存区域（Join Buffer）来缓存驱动表的行，然后扫描整个被驱动表进行匹配。这是极其低效的 JOIN 方式，看到它就意味着必须为 JOIN 字段在被驱动表上创建索引。
  • const query
    • 查询被优化为常量，只执行一次。

C. 红色信号 (严重性能问题)

  • Using filesort
    文件排序。这是性能的主要杀手之一。它意味着查询结果的排序无法利用任何索引的天然顺序，MySQL 必须在内存中（如果结果集小）或在磁盘上创建临时文件（如果结果集大）来进行一次额外的排序操作。这会消耗大量 CPU 和 I/O。
    • 修复办法: 调整或创建联合索引，使其列顺序与 ORDER BY 子句的顺序和方向一致。
  • Using temporary
    使用临时表。这是另一个主要杀手。通常发生在 GROUP BY 或 DISTINCT 操作中，当优化器无法使用索引来完成这些操作时，它需要创建一个内部临时表来存放中间结果。
    • 内存临时表：性能尚可，但消耗内存。
    • 磁盘临时表：如果临时表过大（超过 tmp_table_size 和 max_heap_table_size），会转为磁盘存储，性能会急剧下降。
    • 修复办法: 优化 GROUP BY 的字段，确保它们在同一个索引中且顺序正确。
  • Impossible WHERE
    WHERE 子句的条件逻辑上永远为 FALSE。这通常指示查询本身存在逻辑错误，而不是性能问题，但 EXPLAIN 仍会帮你找出来。

1.4 Rows 和 Filtered

  • rows：估算的扫描行数，但它只是一个估算，可能会不准。
  • filtered : 它表示在 rows 数量的记录中，经过 WHERE 子句中其他条件的过滤后，预计还剩下多少比例的记录（百分比）。
1
2
3
4
5
6
EXPLAIN SELECT * FROM users WHERE age > 25;
-- 如果 rows = 1000，表示 MySQL 估计需要检查 1000 行来找到所有 age > 25 的记录。

EXPLAIN SELECT * FROM users WHERE age > 25 AND city = 'Beijing';
-- rows = 1000（扫描 1000 行） filtered = 10（其中 10% 满足条件）
-- 实际返回约 100 行（1000 × 10%）
  • mysql explain 的 rows 和 filtered 怎么理解，都是越小越好吗？
    • rows 越小通常越好。这好比图书管理员告诉你：” 根据你的条件，我只需要翻看 10 本书就能找到你要的。” 这显然比 “ 我得翻 100 万本 “ 要高效得多。一个小的 rows 值通常意味着你的索引利用得很好。
    • filtered: 扫描后留下的书的比例。
    • filtered 越大越好（越接近 100% 越好）。这好比图书管理员在翻看了 10 本书（rows =10）之后，发现这 10 本书 100% 都是你想要的（filtered =100%）。这说明索引的筛选非常精准。
    • 反之，如果 rows =100 万，而 filtered =0.01%，这意味着 MySQL 辛辛苦苦扫描了 100 万行数据，但其中 99.99% 都是无用功，最后只留下了区区 100 行。这是一个强烈的危险信号，说明当前索引对于整个查询条件的过滤效果很差。

1.5 key_len

索引使用长度（字节）。表示本次查询实际用到了索引的多少个字节。key_len 是诊断联合索引使用情况最直接、最精确的工具。看到一个远小于预期的 key_len 值，就意味着你的查询没能有效利用联合索引的后续部分，需要检查 WHERE 和 JOIN 条件是否遵循了最左前缀原则。

  • INT: 4 字节。
  • BIGINT: 8 字节。
  • VARCHAR(N): N * 字符集字节数 + 2 字节。
  • 如果字段允许为 NULL，则需要额外 +1 字节。

联合索引场景

1
2
3
4
5
6
7
8
-- (user_id, award_id, lottery_id) 创建一个复合索引, 一共是 12

-- key_len:4
explain SELECT * FROM `lottery_user_log` WHERE (user_id=279223) 
-- key_len:8
explain SELECT * FROM `lottery_user_log` WHERE (user_id=279223 and award_id = 8) 
-- key_len:12
explain SELECT * FROM `lottery_user_log` WHERE (user_id=279223 and award_id = 8 and lottery_id=1)

字符串的 key_len

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `city` VARCHAR(50) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `age` INT NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4;



ALTER TABLE `users` ADD INDEX `idx_city` (`city`);
EXPLAIN SELECT * FROM `users` WHERE `city` = '北京';
长度 = 50 (字符数) * 4 (utf8mb4) + 2 (变长存储) = 202。
所以，你会看到 key_len: 202。


ALTER TABLE `users` ADD INDEX `idx_city_name_age` (`city`, `name`, `age`);
EXPLAIN SELECT * FROM `users` WHERE `city` = '上海';
key_len 计算: 只用到了 city。结果和场景一相同，key_len: 202。


EXPLAIN SELECT * FROM `users` WHERE `city` = '上海' AND `name` = '张三';
city 部分: 50 * 4 + 2 = 202
name 部分: 50 * 4 + 2 = 202
总长度 = 202 + 202 = 404。


EXPLAIN SELECT * FROM `users` WHERE `city` = '上海' AND `age` = 30;
结果仍然是 key_len: 202。这个结果告诉你，你的联合索引没有被充分利用。

2. 提问问题

2.1 Explain 行为

  • 定期 ANALYZE TABLE：EXPLAIN 的估算依赖于表的统计信息。定期运行 ANALYZE TABLE your_table; 可以更新这些信息，让优化器做出更准确的判断。

  • EXPLAIN 和 EXPLAIN ANALYZE 的核心区别是什么？请描述一个你必须使用 EXPLAIN ANALYZE 才能确诊的性能问题场景。

    • EXPLAIN 是计划书（告诉你 “ 我打算怎么做 “），它不执行查询，只返回优化器的估算。而 EXPLAIN ANALYZE 是带行车记录仪的导航（告诉你 “ 我不但计划好了，还实际跑了一遍，这是真实记录 “），它会实际执行查询，并返回包含真实执行时间、真实返回行数的详细计划。当 EXPLAIN 的估算严重失准，使用 EXPLAIN ANALYZE。

  • 当你用 EXPLAIN 分析一条 SELECT 语句时，发现 possible_keys 列显示了一个理想的索引 idx_user_id，但 key 列却是 NULL，并且 type 是 ALL。请问，除了 “ 表数据量太小 “ 这个原因外，还可能存在哪两种情况导致了优化器做出这个 “ 糟糕 “ 的决定？

    • 查询条件导致索引失效：查询语句的写法本身阻止了优化器使用索引。最典型的情况是：对索引列使用函数或运算和发生隐式类型转换。
    • 索引选择性（Selectivity）过差：优化器通过统计信息判断，使用这个索引需要扫描的行数占了总行数的很大比例（例如，超过 20%-30%）。在这种情况下，它认为直接进行全表扫描（一次顺序 I/O）比先读索引（随机 I/O）再回表查询大部分数据（更多随机 I/O）的综合成本更低。

  • 在 Extra 列中，你看到了 Using index; Using filesort。这代表了什么看似矛盾的情况？这样的查询性能究竟是好是坏

    • 它意味着从索引中获取数据后，得到的结果集顺序不满足 ORDER BY 子句的要求，因此 MySQL 必须在内存或临时磁盘文件上进行一次额外的排序操作。
    • 调整索引，使其顺序与 ORDER BY 一致。
    • 它通常比 Using where; Using filesort 要好。因为 Using index 避免了回表查询，大大减少了数据读取量。

2.2 Type 问题

  • 过度关注 type，而忽略了 Extra 列。type 固然重要，但 Extra 列里的 Using filesort 和 Using temporary 是两大性能杀手。一个查询即使 type 是 range（还不错），但如果伴随着 filesort，其性能可能依然很糟糕。

  • 为什么 range 比 index 性能高？

    • range: 扫描的是索引的子集。工作量 = 范围大小。
    • index: 扫描的是索引的全部。工作量 = 整个索引大小。
    • 因此，只要 range 的范围不是覆盖整个索引，它的性能就必然优于 index。

  • ref 和 index 的区别是什么?

    • ref 是基于等值条件的精确查找，而 index 是无差别地扫描整个索引树。
    • 当你使用非唯一索引或联合索引的最左前缀进行等值查询 (=) 时，就会出现 ref。
    • index 是对索引的地毯式轰炸，它没有利用索引进行快速定位的优势，只是简单地把索引当成一个比主表小的数据文件来遍历。

2.3 索引问题

  • 单词查询一次可以用多个索引吗？

    • 通常情况下，一个表在一次查询中只会使用一个最优索引。但有一种特殊情况可以，它叫做 “ 索引合并（Index Merge）”。
    • SELECT * FROM users WHERE phone = '138...' OR email = 'test@example.com'; (假设 phone 和 email 分别有独立索引)

  • 符合索引 (status, publish_time) 会比单个索引 ( publish_time) 增加多倍索引大小吗

    • status 的值数量（3 个）只会影响 BTREE 的 “ 分支 “（树如何分组行），但不会导致大小成倍增加。树会按 (status, publish_time) 排序存储所有行，不会复制数据。相反，添加 status 只增加了每个键的长度（很小），总大小略微增加。
    • 为什么不是 3 倍？status 有 3 个值（如 -1, 0, 1）不会导致索引 “ 分裂成 3 份复制 “。BTREE 只是将行分组：
      • 例如，status=-1 的行按 publish_time 排序在一个分支；status=0 在另一个；status=1 在第三个。
      • 这只是树结构的组织方式，总条目数还是 N（不是 3N），总大小几乎不变。只因为键长从 8 字节变 9 字节，略微增加。
    • 如果 status 区分度低（很多行都是 status=1），树的分支会不均衡（一个分支很大，其他小），但大小还是基于总行数，不是乘以值数量。
    • 对比：如果你有高区分度列（如唯一 ID），复合索引大小类似；低区分度只是影响查询效率，不放大大小。

  • 不满足最左匹配，什么场景下也可以使用到索引？

    • 索引覆盖 (Covering Index)
    • idx_city_age_name (city, age, name)
    • SELECT name, age FROM users WHERE age = 30;
    • 索引下推 (Index Condition Pushdown, ICP)
    • idx_name_age (name, age)
    • SELECT * FROM staff WHERE name LIKE ‘ 张%’ AND age = 25;

3. 索引测试

3.1 数据初始化

1
2
docker run -d --name mysql-explain -e MYSQL_ROOT_PASSWORD=123456 mysql # 创建一个容器
docker exec -it mysql-explain mysql -u root -p  # 输入密码, 进入操作

新建测试表，插入 10w 数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 创建库
CREATE DATABASE test1;
use test1;

-- 创建表
CREATE TABLE `test` (  
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 批量插入 10w 数据
DROP PROCEDURE IF EXISTS batchInsert;
DELIMITER $  
CREATE PROCEDURE batchInsert() BEGIN DECLARE i INT DEFAULT 1;  
START TRANSACTION; WHILE i<=100000  
DO  
INSERT INTO test (a,b) VALUES (i,i);  
SET i=i+1; END WHILE;  
COMMIT; END $

CALL batchInsert();  


-- 得到100000
select count(*) from test;

3.2 单个索引

  • 全表扫描 ALL
1
explain select * from test;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrapartitionsfiltered
1SIMPLEtestALLNULLNULLNULLNULL100098NULLNULL100.00

其中 type 值为 ALL，表示全表扫描了，我们看到 rows 这个字段显示有 100098 条，实际上我们一共才 10w 条数据，说明这个字段只是 mysql 的一个预估，不总是准确的。

  • ref
1
2
3
alter table test add index idx_a(a);  

explain select * from test where a = 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrefidx_aidx_a4const1NULL100
  • 全表扫描 ALL，Using where
1
2
3
-- 因为这条语句会从索引中查出 9w 条数据，全表扫描都才 10w 条数据，所以 mysql 决策是还不如直接全表扫描得了。

explain select * from test where a > 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestALLidx_aNULLNULLNULL100098Using where50.00
  • range
1
2
3
-- 使用了 a 索引树, 因为满足索引只有 10000 条数据，mysql 认为 10000 条数据就算回表也要比全表扫描的代价低，因而决定查索引，但还是需要回表。

explain select * from test where a > 90000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_aidx_a4NULL10000Using index condition100.00
  • range 不回表
1
2
3
-- 只select 索引字段, 注意这次 Extra 的值为 `Using where; Using index`，表示查询用到了索引，且要查询的字段在索引中就能拿到，所以不需要回表。
-- 显然这种效率比上面的要高，这也是日常开发中不建议写 select * 的原因，尽量只查询业务所需的字段。
explain select a from test where a > 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_aidx_a4NULL50049Using where; Using index100.00

3.3 Order by

  • range 不回表
1
2
-- 走 a 索引树, 不回表
explain select a from test where a > 90000 order by a;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_aidx_a4NULL10000Using where; Using index100.00
  • range 回表
1
2
3
-- 走 a 索引树, 需要回表
explain select * from test where a > 90000 order by a;
explain select b from test where a > 90000 order by a;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_aidx_a4NULL10000Using index condition100.00
  • range + 内存排序
1
2
3
-- Extra中返回了一个 Using filesort，说明无法利用索引完成排序，需要从内存或磁盘进行排序。就算 b 有索引, 也无法避免, 因为也会走 a 的索引树。

explain select a from test where a > 90000 order by b;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_aidx_a4NULL10000Using index condition; Using filesort100.00

3.4 多个字段索引

目前 a, b 有自己的单个索引。

1
2
3
alter table test drop index idx_a_b;
alter table test add index idx_a(a);  
alter table test add index idx_b(b);
  • 没走索引,是因为无论如何都需要回表, 如果把 10000 改成 90000, 会变成走索引加回表.
1
2
3
explain select * from test where a > 10000;
explain select a,b from test where a > 10000;
explain select b from test where a > 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestALLidx_aNULLNULLNULL100098Using where50
  • 直接走 a 的索引树, 不回表
1
explain select a from test where a > 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_aidx_a4NULL50049Using where; Using index100
  • 走 b 的索引树更好,回表
1
explain select a,b from test where a > 90000 and b = 90000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrefidx_a,idx_bidx_b4const1Using where9.99
  • 走 a 的索引树更好,回表
1
explain select a,b from test where a = 90000 and b > 90000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrefidx_a,idx_bidx_a4const1Using where9.99

3.5 复合索引

一个复合索引，(a,b) 复合索引

1
2
3
alter table test drop index idx_a;
alter table test drop index idx_b;
alter table test add index idx_a_b(a,b);
  • 走 (a,b) 的索引树, 不回表
1
2
3
4
explain select * from test where a > 10000;
explain select a,b from test where a > 10000;
explain select b from test where a > 10000;
explain select a from test where a > 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_a_bidx_a_b4NULL50049Using where; Using index100
  • 不满足最左匹配原则
1
explain select * from test where b > 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestindexNULLidx_a_b8NULL100098Using where; Using index33.33
  • 走 (a,b) 的索引树, 不回表, key_len=4
1
explain select a,b from test where a > 90000 and b = 90000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_a_bidx_a_b4NULL18142Using where; Using index10
  • 走 (a,b) 的索引树, 不回表, key_len=8
1
explain select a,b from test where a = 90000 and b > 90000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_a_bidx_a_b8NULL1Using where; Using index100

普通索引和复合索引同时存在（2 个普通索引，1 个复合索引。）

1
2
3
alter table test add index idx_a(a); 
alter table test add index idx_b(b);  
alter table test add index idx_a_b(a,b);
  • 走 (a,b) 的索引树更好, 不回表
1
2
3
explain select * from test where a > 10000;
explain select a,b from test where a > 10000;
explain select b from test where a > 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_a_b,idx_aidx_a_b4NULL50049Using where; Using index100
  • 走 a 的索引树更好, 不回表
1
explain select a from test where a > 10000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_a_b,idx_aidx_a4NULL50049Using where; Using index100
  • 走 b 的索引树, 需要回表. 其实此时走复合索引更好, 所以不建议复合索引和普通索引一起用
1
explain select a,b from test where a > 90000 and b = 90000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrefidx_a_b,idx_a,idx_bidx_b4const1Using where18.12
  • 走 (a,b) 的索引树, 不回表
1
explain select a,b from test where a = 90000 and b > 90000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtrafiltered
1SIMPLEtestrangeidx_a_b,idx_a,idx_bidx_a_b8NULL1Using where; Using index100

4. 参考资料