MySql优化的场景有哪些？-优快云博客

本文链接：https://blog.youkuaiyun.com/chenxuefeng_accp/article/details/148540208

一、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'