那些年把我问懵的MySQL优化面试题（附血泪踩坑实录）

原创于 2025-05-16 13:08:17 发布 · 913 阅读

CC 4.0 BY-SA版权

文章标签：

前言

最近帮公司面了十几个中高级开发，发现MySQL优化真是面试官的必杀技（特别是P6以上的岗位）！每次问到"你处理过的最有挑战的SQL优化案例"时，超过一半的候选人都会卡壳。今天我就把压箱底的9个高频优化题+实战踩坑经验整理成章，附带当年被技术总监怼到自闭的真实案例！（文末有思维导图福利）

一、索引优化三连击

1.1 索引失效的七大死亡场景

上周才发生的惨案：某核心接口突然超时，追查发现status字段的索引失效了。原来开发小哥写了这样的查询：

SELECT * FROM orders WHERE YEAR(create_time) = 2023;

（划重点）时间函数操作会导致索引失效！！正确的姿势应该是：

SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

其他六大死亡名单：

隐式类型转换（比如varchar字段传了数字）
前导通配符LIKE '%关键字%'
对索引列进行运算WHERE id + 1 = 5
使用!=或者<>
联合索引跳过最左字段
索引列使用OR连接条件

1.2 最坑爹的索引选择难题

当多个索引可用时，MySQL怎么选？来看这个魔鬼案例：

ALTER TABLE users ADD INDEX idx_age_name(age,name);
ALTER TABLE users ADD INDEX idx_name(name);

SELECT * FROM users WHERE name = '老王' AND age = 30;

你以为会走联合索引？Too young！实测发现5.7版本可能选择单列索引，8.0版本优化器变聪明了会选联合索引。所以（重要结论）高版本MySQL的索引选择策略更智能！

1.3 索引设计的三个灵魂拷问

被技术总监怼过的问题：

UUID主键到底能不能用？（答案：自增ID的插入性能快3倍！）
性别字段该不该建索引？（200万数据测试：查男用户反而全表扫描更快）
联合索引字段顺序怎么定？（高频查询字段放前面，区分度高的放左边）

二、查询优化的五个必杀技

2.1 EXPLAIN执行计划破译指南

拿到执行计划不会看？记住三个关键指标：

type列：system > const > ref > range > index > ALL（出现ALL就完蛋）
rows列：估算扫描行数（超过1万行就要警惕）
Extra列：出现Using filesort或Using temporary立即拉响警报

2.2 避免全表扫描的骚操作

某电商系统曾因SELECT *导致OOM崩溃！优化方案：

-- 错误示范
SELECT * FROM products WHERE category_id = 5;

-- 正确姿势
SELECT id,name,price FROM products 
WHERE category_id = 5 
ORDER BY sales_volume DESC 
LIMIT 100;

（关键点）只查需要的字段+合理利用覆盖索引，查询速度直接提升8倍！

2.3 分页优化的魔鬼细节

LIMIT 100000,10为什么会慢成狗？解决方案：

-- 原始慢查询
SELECT * FROM logs ORDER BY id DESC LIMIT 1000000, 10;

-- 优化方案1：游标分页
SELECT * FROM logs 
WHERE id < last_max_id 
ORDER BY id DESC 
LIMIT 10;

-- 优化方案2：延迟关联
SELECT * FROM logs 
INNER JOIN (
    SELECT id FROM logs 
    ORDER BY id DESC 
    LIMIT 1000000, 10
) AS tmp USING(id);

三、结构优化的降维打击

3.1 范式和反范式的终极抉择

某金融系统坚持第三范式，结果联表查询多达8张表！后来适当冗余字段，QPS从50飙升到2000+。记住（血泪教训）高并发场景要敢于反范式！

3.2 字段类型选择的坑

见过最离谱的案例：用VARCHAR(255)存IP地址！优化方案：

-- 错误示范
ip VARCHAR(15) NOT NULL 

-- 正确操作
ip INT UNSIGNED NOT NULL
-- 转换方法：INET_ATON('192.168.1.1') 和 INET_NTOA()

存储空间直接减少75%！

3.3 分库分表的信号灯

什么时候该分表？我们的经验公式：

单表数据量 > 500万 
或 
数据增长率 > 100万/月
或 
查询延迟 > 500ms

满足任意两项就该考虑拆分了！

四、实战案例分析

4.1 慢查询日志分析全流程

某次线上事故排查实录：

开启慢查询日志：set global slow_query_log = ON;
抓取TOP10慢SQL：mysqldumpslow -t 10 /var/log/mysql-slow.log
发现魔鬼查询：SELECT COUNT(*) FROM huge_table WHERE status = 0;
优化方案：改用SELECT TABLE_ROWS FROM information_schema.tables估算

4.2 死锁现场还原

某订单系统凌晨报死锁，查看SHOW ENGINE INNODB STATUS发现：

LATEST DETECTED DEADLOCK
...
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table `test`.`t`

原因竟是批量更新顺序不一致！解决方案：统一按照主键升序更新。

五、面试加分秘籍

5.1 必问的三大连环题

“你如何定位慢查询？”
- 标准答案：慢查询日志+EXPLAIN+PROFILING三件套
“怎么处理深分页问题？”
- 加分回答：游标分页+业务限制最大页码+ES辅助查询
“遇到过死锁吗？怎么解决的？”
- 完美答案：监控报警+死锁日志分析+代码审查更新顺序

5.2 反问面试官的绝杀问题

“咱们公司的数据库版本是？不同版本优化器差异大吗？”
“现有系统中最大的单表数据量是多少？”
“有没有遇到过分库分表后的分布式事务问题？”

结语

记得刚毕业时被问到"为什么索引能加快查询"，我竟然回答"因为数据库会先看索引再查数据"（被面试官翻白眼）。现在把这些血泪经验打包给大家，附上我整理的MySQL优化知识图谱（模拟链接）。最后送大家一句话：纸上得来终觉浅，绝知此事要跑EXPLAIN！

下期预告：《从被骂到涨薪：我的Redis优化逆袭之路》敬请期待！