一、EXPLAIN执行计划是什么?为什么看它?
比喻:
想象你要去图书馆找一本书,你会先查目录,看书在哪个区域、哪排书架,这就是执行计划告诉你“数据库怎么找数据”的路线图。
二、EXPLAIN输出主要字段解释
字段 | 作用/比喻 | 你要重点看什么? |
---|---|---|
id | 任务编号,谁先干活 | 看清楚执行顺序 |
select_type | 查询类型,比如简单查、子查询 | 复杂SQL有子查询要注意 |
table | 当前正在查的表 | 确认表名 |
type | 访问方式,数据库找书的效率等级 | ALL 最差(全书架找),const 最好(目录直达) |
possible_keys | 可能用的索引 | 有索引就好 |
key | 实际用的索引 | 最重要,看数据库到底用哪个索引 |
key_len | 索引长度,越长越精准 | 越长越精确,代表范围更窄 |
ref | 哪个字段或值和索引匹配 | 帮你理解条件怎么用索引 |
rows | 大概扫多少行,相当于找多少本书 | 越小越好 |
Extra | 额外信息,比如“用临时表”或“文件排序” | 不希望看到 Using filesort 或 Using temporary |
三、访问类型(type)详解
访问类型 | 意义 | 比喻 | 优劣排序(好到差) |
---|---|---|---|
system | 表只有1行 | 图书馆只有1本书 | 最快 |
const | 主键或唯一索引匹配 | 直接拿到指定书 | 极快 |
eq_ref | 关联主键唯一匹配 | 按目录找到特定书 | 快 |
ref | 普通索引匹配 | 找索引区段里的多本书 | 好 |
range | 索引范围扫描 | 按分类范围查找书 | 中 |
index | 全索引扫描 | 扫描所有目录页 | 慢 |
ALL | 全表扫描 | 一排排书架全翻 | 最慢 |
四、重要Extra说明
-
Using index:只用索引,不回主表(回表),效率高。
-
Using filesort:额外排序,可能慢。
-
Using temporary:用临时表,可能慢,出现时注意优化。
-
Using index condition:索引条件下推,InnoDB新技术,提升效率。
五、什么是回表?为什么要避免?
回表比喻:
你先在目录(索引)找到书编号,再跑到书架(主表)去拿书。多了跑动,花时间。
避免方法:
用覆盖索引,索引里有你需要的所有信息,就不用再跑书架。
六、EXPLAIN实际示例
EXPLAIN SELECT id, name FROM user WHERE id = 100;
假设结果:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | user | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
解释:
-
type是
const
,说明用主键直接定位到一行,非常快。 -
Extra显示
Using index
,表示只用了索引,不回表。
七、优化技巧与工具
-
索引设计:理解联合索引的最左前缀原则。
-
参数化查询:避免拼接字符串导致缓存失效。
-
数据类型匹配:条件字段类型必须和索引字段一致。
-
慢查询日志:定位长时间SQL。
-
performance_schema:细粒度性能监控。
-
读写分离:减轻主库压力。
-
缓存:热点数据用Redis缓存。
八、面试回答模板
“我一般先用EXPLAIN查看执行计划,重点关注type和key字段,判断是否用到索引。比如type是ALL说明全表扫描,应该考虑加索引。遇到回表时,我会考虑设计覆盖索引,减少回表。额外信息里如果有Using filesort或Using temporary,说明有排序或临时表开销,需优化排序或分组。实际执行我会用EXPLAIN ANALYZE,结合慢查询日志和performance_schema,定位具体瓶颈。优化常用手段有索引设计、SQL重写、分页游标和缓存。结合读写分离和监控,保证系统整体性能稳定。”
九、总结口诀
看执行计划,先看type,索引用上performance high;
回表要少,覆盖为王,filesort临时别用忙;
分页别深游标快,缓存统计秒杀慢;
慢查询抓得紧,性能提升少不了。
索引命中看 key,扫描行数看 rows;
字段尽量不 *,回表避免漏;
Extra 一查 filesort,优化排序是王道;
LIMIT 慢查游标快,COUNT 靠缓存好。
MySQL常见优化场景详解(带问题、原因、优化及示例)
-
场景编号 优化场景 问题表现 原因分析 优化建议 SQL示例 1 全表扫描 查询慢,扫描大量数据 查询字段无索引,导致全表扫描 针对查询字段建立合适索引 CREATE INDEX idx_name ON user(name);
SELECT * FROM user WHERE name='张三';
2 函数包裹字段导致索引失效 查询慢,执行计划未用索引 对索引字段使用函数,MySQL无法用索引 避免在索引字段上使用函数,改用范围查询 WHERE DATE(create_time)='2025-06-09'
改为
WHERE create_time BETWEEN '2025-06-09 00:00:00' AND '2025-06-09 23:59:59'
3 回表次数多 查询响应慢,IO开销大 查询字段未被索引覆盖,导致频繁回表 设计覆盖索引(索引包含所有查询字段) CREATE INDEX idx_email_name_age ON user(email, name, age);
SELECT name, age FROM user WHERE email='a@b.com';
4 排序用filesort 查询慢,额外排序开销大 ORDER BY字段未被索引覆盖,MySQL使用filesort排序 为ORDER BY字段建立合适索引 CREATE INDEX idx_create_time ON orders(create_time DESC);
SELECT * FROM orders ORDER BY create_time DESC;
5 深分页慢 OFFSET值大时查询响应极慢 OFFSET跳过大量数据,导致大量扫描 用“游标分页”或基于主键ID分页 SELECT * FROM user WHERE id < ? ORDER BY id DESC LIMIT 10;
6 COUNT(*) 慢 COUNT(*)全表扫描导致慢 无法用索引统计,MySQL扫描全表 维护缓存计数,或建小型统计表 用Redis缓存计数,或者维护订单计数表 7 大事务导致锁竞争 事务锁时间长,阻塞其他请求 批量数据操作事务过大,锁持有时间长 拆分事务,缩短事务时间 分批处理数据,每批1000条提交 8 联合索引顺序不合理 查询索引失效,性能下降 联合索引最左前缀原则不满足 调整联合索引字段顺序,或补充索引 CREATE INDEX idx_name_age ON user(name, age);
9 小查询过多导致压力大 频繁连接数据库,连接数高 每条业务逻辑小查询导致数据库压力大 批量查询,或使用缓存优化 批量查询示例: SELECT * FROM orders WHERE id IN (...);
10 锁争用严重 高并发时大量锁等待,响应慢 热点记录被频繁修改导致锁竞争 使用乐观锁,减少锁持有时间 乐观锁示例:版本号或时间戳字段控制更新
详细示例讲解
示例1:函数包裹字段导致索引失效
-- 慢查询示例 SELECT * FROM orders WHERE DATE(create_time) = '2025-06-09'; -- 优化:改成范围查询,索引生效 SELECT * FROM orders WHERE create_time >= '2025-06-09 00:00:00' AND create_time <= '2025-06-09 23:59:59';
解释:
使用DATE()
函数导致MySQL无法利用索引,只能全表扫描。改为范围查询,让索引起作用。
示例2:回表次数多,设计覆盖索引
-- 查询email对应用户的name和age字段 SELECT name, age FROM user WHERE email = 'test@example.com'; -- email字段有索引,但name、age没有,导致查询先查email索引,再回主表查name和age,性能差 -- 优化:设计覆盖索引,包含所有查询字段 CREATE INDEX idx_email_name_age ON user(email, name, age); -- 这时查询只需索引,避免回表
示例3:深分页慢,改用游标分页
-- 慢查询,深度分页 SELECT * FROM user ORDER BY id DESC LIMIT 10000, 10; -- 优化,游标分页,利用上次查询最大ID SELECT * FROM user WHERE id < 123456 ORDER BY id DESC LIMIT 10;
示例4:排序用filesort,建立索引优化
-- 无索引排序,执行文件排序,慢 SELECT * FROM orders ORDER BY create_time DESC LIMIT 20; -- 优化,建立支持排序的索引 CREATE INDEX idx_create_time ON orders(create_time DESC); -- 查询快速,避免filesort
示例5:大事务拆分
-- 慢事务示例 BEGIN; UPDATE orders SET status = 'processed' WHERE id IN (...10000条...); COMMIT; -- 优化:分批处理,每次处理1000条 BEGIN; UPDATE orders SET status = 'processed' WHERE id IN (...1000条...); COMMIT; -- 循环执行多次
✅ 面试官常见反问拆解(+应答建议)
❓ 1. 为什么组合索引要遵守“最左前缀原则”?
✅ 回答:
MySQL 的 B+ 树索引是有序的多级结构,组合索引会按照第一个字段排序。只有查询条件从左往右连续命中,索引才能被使用。
比如索引 (a, b, c),如果只查WHERE b=1
,不能利用索引;但WHERE a=1
或a=1 AND b=2
可以。
❓ 2. 为什么不能在索引字段上使用函数?会导致什么?
✅ 回答:
索引是基于原始字段值构建的,函数改变了字段的值,导致无法用原始索引匹配。例如:
WHERE DATE(create_time) = '2024-06-01'
无法使用 create_time 上的索引。
正确写法应为:
WHERE create_time >= '2024-06-01' AND create_time < '2024-06-02'
❓ 3. 字段如果是 null,会不会走索引?为什么你说“避免 null 值”?
✅ 回答:
MySQL B+ 树中可以存 null 值,但在实际查询中,如果用 IS NULL、IS NOT NULL 做判断,可能不走索引(取决于版本和统计信息)。
而且在组合索引中,一旦最左字段为 null,会影响后续字段使用。
所以生产中我们通常避免将索引字段设计成 null。
❓ 4. 你说“不能使用 like 前模糊”,那用
like '%abc'
会怎样?为什么不走索引?✅ 回答:
前模糊
%abc
会让 MySQL 无法定位索引的起点,只能全表扫描。
索引是按顺序查找的,必须能从前缀部分匹配才行。
like 'abc%'
是可以走索引的。
❓ 5. 你说“覆盖索引更快”,为什么?什么是回表?
✅ 回答:
覆盖索引指的是:查询字段正好都在索引里,不需要回主表查数据。
回表是:先通过索引找到主键,然后去主表根据主键再查一次数据,成本更高。
所以合理设计索引列顺序,可以最大程度提升查询性能。
❓ 6. explain 里面出现了 type=ALL,意味着什么?
✅ 回答:
type=ALL 表示全表扫描,一般说明没有命中索引,是最差的情况。
优化目标应该是 type=ref、range、const 等。