本文收录于「bksczm」的系列专栏LinuxC/CMySQL面试题准备测试表与数据-- 班级表 CREATE TABLE class ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL ) ENGINEInnoDB; -- 学生表主键 idname 普通索引class_id 普通索引(name,age) 复合索引age/email 无独立索引 CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT, class_id INT, email VARCHAR(50), INDEX idx_name (name), INDEX idx_class_id (class_id), INDEX idx_name_age (name, age) ) ENGINEInnoDB; -- 文章表全文索引ngram 支持中文分词 CREATE TABLE articles ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200), content TEXT, FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram ) ENGINEInnoDB; INSERT INTO class(name) VALUES (一班),(二班),(三班); INSERT INTO student(name, age, class_id, email) VALUES (张三, 18, 1, zsxx.com), (张三, 20, 1, zs2xx.com), (李四, 19, 2, lsxx.com), (王五, 21, 2, NULL), (NULL, 22, 3, NULL); INSERT INTO articles(title, content) VALUES (MySQL索引教程,讲解数据库索引和B树), (事务隔离详解,讲解MVCC和ReadView);准备EXPLAIN 概述语法与作用EXPLAIN SQL语句;EXPLAIN 返回 MySQL 优化器打算如何执行这条 SQL的执行计划而不是盲目执行后再看结果。通过分析执行计划可以判断查询是否走了索引、扫描了多少行、是否有临时表 / 文件排序等性能问题。是否真正执行普通EXPLAIN SELECT ...不会真正执行查询只返回执行计划但优化器可能做一些统计和预处理EXPLAIN INSERT/UPDATE/DELETE不会真正修改数据EXPLAIN ANALYZEMySQL 8.0.18会真正执行SQL并返回实际执行的耗时、行数等信息比普通 EXPLAIN 更精确。一、id执行顺序1.1 id 相同从上到下顺序执行表连接EXPLAIN SELECT * FROM student s INNER JOIN class c ON s.class_id c.id;--------------------------------------------------- | id | select_type | table | type | key | ... | --------------------------------------------------- | 1 | SIMPLE | c | ALL | PRIMARY | ... | ← 先扫描 class | 1 | SIMPLE | s | ref | idx_class_id | ... | ← 再用 class_id 关联 student ---------------------------------------------------两行 id 都是 1属于同一层级从上到下执行。1.2 id 不同id 越大越先执行子查询EXPLAIN SELECT * FROM student WHERE class_id (SELECT id FROM class WHERE name 一班);--------------------------------- | id | select_type | table | type | --------------------------------- | 1 | PRIMARY | student | ref | ← 外层后执行 | 2 | SUBQUERY | class | const | ← id2 更大子查询先执行 ---------------------------------1.3 id 为 NULLUNION 结果集最后执行EXPLAIN SELECT id FROM student WHERE id 3 UNION SELECT id FROM student WHERE id 4;-------------------------------------- | id | select_type | table | type | -------------------------------------- | 1 | PRIMARY | student | range| | 2 | UNION | student | range| | NULL | UNION RESULT | union1,2 | ALL | ← 最后合并结果 --------------------------------------二、select_type查询类型2.1 SIMPLE简单查询EXPLAIN SELECT * FROM student WHERE id 1; -- select_type: SIMPLE无子查询、无 UNION2.2 PRIMARY SUBQUERY外层 非相关子查询EXPLAIN SELECT * FROM student WHERE class_id (SELECT id FROM class WHERE name 一班); -- 外层: PRIMARY 子查询: SUBQUERY2.3 DEPENDENT SUBQUERY相关子查询依赖外层EXPLAIN SELECT * FROM student s WHERE EXISTS (SELECT 1 FROM class c WHERE c.id s.class_id);| 1 | PRIMARY | s | ... | | 2 | DEPENDENT SUBQUERY | c | ... | ← 子查询引用了外层的 s.class_id2.4 DERIVEDFROM 子句中的派生表EXPLAIN SELECT t.name FROM ( SELECT id, name FROM student WHERE age 18 ) t WHERE t.id 1;| 1 | PRIMARY | derived2 | ... | ← 派生表 | 2 | DERIVED | student | ... | ← 先物化成临时表2.5 UNION / UNION RESULTEXPLAIN SELECT id FROM student WHERE id 3 UNION SELECT id FROM student WHERE id 4; -- 第一个 SELECT: PRIMARY -- 第二个 SELECT: UNION -- 合并行: UNION RESULTidNULL2.6 MATERIALIZED物化子查询EXPLAIN SELECT * FROM student WHERE class_id IN (SELECT id FROM class WHERE id 1); -- 子查询结果被物化成临时表select_type 可能显示 MATERIALIZED三、table 与 partitions3.1 table涉及的表EXPLAIN SELECT * FROM student s, class c WHERE s.class_id c.id; -- table: s / c显示表名或别名 -- UNION 结果: union1,2派生表: derived23.2 partitions匹配的分区-- 未分区表 EXPLAIN SELECT * FROM student WHERE id 1; -- partitions: NULL -- 分区表示例按 RANGE 分区 CREATE TABLE log_part ( id INT, create_time DATE ) PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025) ); EXPLAIN SELECT * FROM log_part WHERE YEAR(create_time) 2024; -- partitions: p2024只扫描命中的分区即分区裁剪partitions 显示分区名未分区表为 NULL。四、type访问类型⭐性能从好到差system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL4.1 system表中只有一行CREATE TABLE t_one (id INT PRIMARY KEY) ENGINEMyISAM; INSERT INTO t_one VALUES (1); EXPLAIN SELECT * FROM t_one; -- type: systemInnoDB 单行表通常显示 constsystem 多见于 MyISAM/MEMORY 单行表或系统表。4.2 const主键/唯一索引等值匹配常量最终结果只会出现单行EXPLAIN SELECT * FROM student WHERE id 1; -- type: const key: PRIMARY rows: 14.3 eq_ref被驱动表走主键/唯一索引等值关联子查询也会存在EXPLAIN SELECT s.name, c.name FROM student s INNER JOIN class c ON s.class_id c.id;| table | type | key | ref | | c | ALL | NULL | NULL | ← 驱动表全扫描数据少 | s | eq_ref | PRIMARY | test.c.id | ← 每行用 c.id 主键精确匹配最多一条4.4 ref普通索引等值查询可能多行EXPLAIN SELECT * FROM student WHERE name 张三; -- type: ref key: idx_name ref: const rows: 2name 是普通索引非唯一有两个张三返回多行。4.5 fulltext全文索引EXPLAIN SELECT * FROM articles WHERE MATCH(title, content) AGAINST(索引); -- type: fulltext key: ft_title_content必须用MATCH ... AGAINST用或LIKE不会走全文索引。4.6 ref_or_null等值 IS NULLEXPLAIN SELECT * FROM student WHERE name 王五 OR name IS NULL; -- type: ref_or_null key: idx_nameWHERE 中显式写了IS NULL才出现列允许 NULL 但不查 NULL 仍是 ref。4.7 index_merge多个索引合并EXPLAIN SELECT * FROM student WHERE id 1 OR name 张三; -- type: index_merge -- key: PRIMARY,idx_name -- Extra: Using union(PRIMARY,idx_name); Using where4.8 unique_subqueryIN 子查询返回主键/唯一键-- MySQL 5.6 默认 semi-join 会改写先关闭才能看到 SET optimizer_switch semijoinoff; EXPLAIN SELECT * FROM student WHERE class_id IN (SELECT id FROM class WHERE name 一班); -- 子查询 class.id 是主键 → type: unique_subquery SET optimizer_switch semijoinon; -- 恢复4.9 index_subqueryIN 子查询返回普通索引列SET optimizer_switch semijoinoff; EXPLAIN SELECT * FROM class c WHERE c.id IN (SELECT class_id FROM student WHERE age 18); -- 子查询 class_id 是普通索引非唯一→ type: index_subquery SET optimizer_switch semijoinon;4.10 range索引范围扫描EXPLAIN SELECT * FROM student WHERE id 3; -- type: range key: PRIMARY EXPLAIN SELECT * FROM student WHERE id BETWEEN 1 AND 3; -- range EXPLAIN SELECT * FROM student WHERE id IN (1,2,3); -- range EXPLAIN SELECT * FROM student WHERE name LIKE 张%; -- range前缀匹配不等于通常无法走 range。4.11 index扫描整棵索引树EXPLAIN SELECT name FROM student; -- type: index key: idx_name Extra: Using index只查 name 列idx_name 索引能覆盖不回表但要扫完整棵索引树。4.12 ALL全表扫描最差EXPLAIN SELECT * FROM student WHERE age 20; -- type: ALL key: NULL rows: 5 Extra: Using whereage 只是复合索引(name,age)的第二列单独查 age 不符合最左前缀且无独立索引 → 全表扫描。五、possible_keys / key / key_len / ref5.1 possible_keys 与 key-- 有可用索引且被使用 EXPLAIN SELECT * FROM student WHERE name 张三; -- possible_keys: idx_name key: idx_name -- 有索引但优化器没用如查询返回表中大部分数据 EXPLAIN SELECT * FROM student WHERE id 0; -- possible_keys: PRIMARY key: NULL数据太少/全取优化器选全表扫描 -- 完全没有可用索引 EXPLAIN SELECT * FROM student WHERE email lsxx.com; -- possible_keys: NULL key: NULL5.2 key_len判断复合索引用了几个字段复合索引idx_name_age(name, age)name 是 VARCHAR(20) utf8mb4 且允许 NULLage 是 INT 允许 NULL-- 只用到 namekey_len 20*421 83 EXPLAIN SELECT * FROM student WHERE name 张三; -- key_len: 83 -- 用到 name age83 (41) 88 EXPLAIN SELECT * FROM student WHERE name 张三 AND age 18; -- key_len: 88计算规则INT4CHAR(n) utf8mb44nVARCHAR(n) utf8mb44n2允许 NULL 再 1。5.3 ref索引与什么比较EXPLAIN SELECT * FROM student WHERE name 张三; -- ref: const与常量比较 EXPLAIN SELECT s.* FROM student s INNER JOIN class c ON s.class_id c.id; -- student 行 ref: test.c.id与另一表的列比较六、rows 与 filteredEXPLAIN SELECT * FROM student WHERE name 张三; -- rows: 2 filtered: 100.00 -- 估算扫描 2 行经过 WHERE 过滤后 100% 保留 → 预计返回 2 行 EXPLAIN SELECT * FROM student WHERE age 20; -- type: ALL rows: 5 filtered: 20.00 -- 估算扫描 5 行过滤后剩 20% → 预计返回约 1 行5 × 20% 1rows 是估算值不是精确值rows 越小越好filtered 越接近 100 越好。七、Extra附加信息⭐7.1 Using filesort排序没走索引EXPLAIN SELECT * FROM student WHERE class_id 1 ORDER BY age; -- class_id 走索引但 ORDER BY age 无法利用索引顺序 -- Extra: Using filesortfilesort 不一定用磁盘文件数据量小时在 sort buffer 内存排序。7.2 Using temporary用了临时表-- GROUP BY 非索引列 EXPLAIN SELECT age, COUNT(*) FROM student GROUP BY age; -- Extra: Using temporary; Using filesort -- DISTINCT 非索引列 EXPLAIN SELECT DISTINCT age FROM student; -- Extra: Using temporary7.3 Using whereServer 层额外过滤EXPLAIN SELECT * FROM student WHERE age 20; -- type: ALL Extra: Using where存储引擎取出后Server 层再用 age20 过滤7.4 Using index覆盖索引不回表EXPLAIN SELECT name, age FROM student WHERE name 张三; -- 查询列 name、age 都在复合索引 idx_name_age 中 -- Extra: Using index7.5 Using index condition索引条件下推ICPEXPLAIN SELECT * FROM student WHERE name LIKE 张% AND age 18; -- name 范围扫描age18 条件下推到存储引擎层用索引先过滤减少回表 -- Extra: Using index condition7.6 Using join buffer关联缺索引EXPLAIN SELECT * FROM student s1 INNER JOIN student s2 ON s1.age s2.age; -- age 无索引无法索引关联使用 BNL 连接缓冲 -- Extra: Using join buffer (Block Nested Loop)7.7 Impossible WHERE条件恒假EXPLAIN SELECT * FROM student WHERE 1 0; -- Extra: Impossible WHERErows 为空不会执行 EXPLAIN SELECT * FROM student WHERE id 1 AND id 2; -- 同样 Impossible WHERE7.8 Select tables optimized away无需扫描即可确定结果EXPLAIN SELECT MIN(id), MAX(id) FROM student; -- 直接从索引两端取值优化器确定最多一行结果 -- Extra: Select tables optimized away7.9 No tables used无 FROM 子句EXPLAIN SELECT 1; EXPLAIN SELECT NOW(); -- Extra: No tables used7.10 组合问题示例filesort temporary 同时出现EXPLAIN SELECT age, COUNT(*) AS cnt FROM student WHERE id 1 GROUP BY age ORDER BY cnt DESC; -- type: rangeid 走索引 -- Extra: Using where; Using temporary; Using filesort -- 分组用临时表、排序用 filesort是重点优化对象可考虑给 age 加索引八、一页速查表字段关注点好的表现差的表现id执行顺序层级清晰大量 DERIVED/临时表type访问方式const/eq_ref/ref/rangeindex/ALLkey实际索引命中预期索引NULL该有索引却没有key_len索引利用长度充分利用复合索引只用到最左一个字段rows扫描行数小巨大filtered过滤比例接近 100很小扫了大量无用行Extra附加信息Using indexUsing filesort / Using temporary / Using join buffer