MySQL优化必杀技：这些面试题你躲不过的！（实战经验版）

文章目录

一、索引优化篇（面试官的最爱）

1.1 联合索引怎么用才不翻车？

老铁们注意了！联合索引可不是随便建的（血泪教训）。比如有个用户表索引是INDEX (province, city, age)，下面这个SQL能走索引吗？

SELECT * FROM users WHERE city='杭州' AND age=25;

答案是：不！走！索！引！（划重点！）
因为违背了最左前缀原则，就像查字典不按拼音首字母查一样。正确用法至少得带上province字段，比如WHERE province='浙江' AND city='杭州'。

1.2 索引失效的6大坑（真实案例）

去年我优化过的一个系统，明明有索引却慢如蜗牛，结果发现：

对字段做了运算：WHERE YEAR(create_time)=2023 ❌
使用!=或者<>：WHERE status != 1 ❌
LIKE通配开头：WHERE name LIKE '%张' ❌
类型转换：WHERE phone=13800138000（phone是varchar类型）❌
OR连接非索引字段：WHERE id=1 OR address='北京' ❌
索引列参与计算：WHERE age+10>30 ❌

（重要程度五颗星！面试必考！）

二、SQL优化实战技巧

2.1 分页查询怎么优化？

当遇到SELECT * FROM table LIMIT 1000000,10这种深分页时，试试这两招：

方案一：延迟关联

SELECT * FROM table t
JOIN (SELECT id FROM table ORDER BY create_time LIMIT 1000000,10) tmp
ON t.id=tmp.id

方案二：书签记录法
记录上一页最后一条记录的ID，下页查询：

SELECT * FROM table WHERE id > 1000000 ORDER BY id LIMIT 10

（实测速度提升50倍！）

2.2 你还在用SELECT *？

刚入行时我也爱用SELECT *，直到有次查10万条数据，网络传输直接爆炸！优化方案：

只取必要字段
用覆盖索引（Using index）
大字段用单独查询
分页先查ID再查详情

三、慢查询日志分析（DBA的杀手锏）

3.1 配置慢查询日志

# 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

# 设置阈值（单位：秒）
SET GLOBAL long_query_time = 1;

# 记录未走索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

3.2 分析工具推荐

# 原始日志查看
mysqldumpslow -s t /var/lib/mysql/slow.log

# 可视化神器pt-query-digest
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

（去年用这个工具挖出一个全表扫描的定时任务，直接让系统吞吐量翻倍！）

四、事务与锁机制（高并发必考）

4.1 事务隔离级别对比表

隔离级别	脏读	不可重复读	幻读	使用场景
读未提交	✔️	✔️	✔️	几乎不用
读已提交（RC）	✖️	✔️	✔️	金融交易
可重复读（RR）	✖️	✖️	✔️	MySQL默认
串行化	✖️	✖️	✖️	超低并发场景

4.2 死锁排查实战

某次线上报死锁，用这个命令快速定位：

SHOW ENGINE INNODB STATUS\G

在输出的LATEST DETECTED DEADLOCK段找到：

*** (1) TRANSACTION: 事务1信息
*** (1) HOLDS THE LOCK(S): 持有的锁
*** (2) TRANSACTION: 事务2信息
*** (2) HOLDS THE LOCK(S): 持有的锁
*** WE ROLL BACK TRANSACTION (2)

最后发现是批量更新顺序不一致导致的，改成按固定顺序更新后解决！