★ FEATURED ARTICLE 1. 面试官为什么爱问数据库调优连环炮1.1 一道题背后藏着三层能力考察数据库调优这个话题在技术面试里出现的频率高得离谱。你投的是后端开发面试官问索引你投的是运维面试官问慢查询你投的是数据开发面试官还是问执行计划。为什么因为数据库调优是一块试金石它能同时检验三个维度的能力基础原理的扎实程度、问题排查的系统性思维、以及工程实践中的取舍判断。我面过不少人也被人面过不少次。一个很深的感受是面试官问“这条SQL为什么慢”他真正想听的从来不是“加个索引就好了”这种标准答案。他想听的是你拿到一条慢SQL之后脑子里那套完整的排查链路——先看什么、再看什么、怎么验证、怎么权衡。这套链路才是区分“背过八股文”和“真正干过活”的分水岭。1.2 连环combo的典型套路拆解所谓“连环combo”就是面试官不会只问一个问题就放过你而是像剥洋葱一样一层一层往里钻。常见的套路是这样的第一层“你遇到过慢查询吗怎么排查的”——这是热身看你会不会用基本工具。第二层“你说加了索引那为什么加了索引还是慢”——这是试探看你知不知道索引失效的场景。第三层“如果索引没问题执行计划也走了索引但还是很慢怎么办”——这是深水区考的是锁、回表、数据量、硬件、参数配置等综合因素。第四层“线上不能随便改你怎么在不影响业务的前提下定位问题”——这是实战题考的是你对生产环境工具链和风险控制的认知。这四层下来能扛住的人不多。但扛住了基本就稳了。下面我按这个逻辑把每一层拆开讲透顺便把我在实际工作中踩过的坑和总结的技巧都倒出来。2. 第一层慢查询的定位与基础排查2.1 慢查询日志你的第一手现场证据排查慢查询第一步永远是拿到现场证据。数据库一般都有慢查询日志功能以MySQL为例核心参数是slow_query_log、long_query_time和log_queries_not_using_indexes。我通常会把long_query_time设成1秒先跑一段时间看看整体情况再根据业务敏感度调整。-- 查看当前慢查询配置 SHOW VARIABLES LIKE slow_query%; SHOW VARIABLES LIKE long_query_time; -- 动态开启慢查询日志重启失效生产环境慎用 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;拿到慢查询日志后不要急着一条条看。先用mysqldumpslow或者pt-query-digest做聚合分析按执行次数、总耗时、平均耗时排序。执行次数多但单次快的SQL往往比执行次数少但单次极慢的SQL更值得优化因为它对系统整体吞吐的影响更大。注意生产环境开启慢查询日志会带来一定的I/O开销建议在业务低峰期开启或者只开启log_queries_not_using_indexes来捕获未走索引的查询。2.2 EXPLAIN执行计划的正确读法拿到可疑SQL之后EXPLAIN是必用的。但很多人只看type列看到ref或range就放心了这是不够的。我读执行计划会重点关注这几列列名关注点危险信号type访问类型ALL全表扫描、index全索引扫描key实际使用的索引NULL没走索引rows预估扫描行数数值过大与实际返回行数差距悬殊filtered过滤比例百分比过低说明扫描了大量无用数据Extra额外信息Using filesort、Using temporaryExtra列里的Using filesort和Using temporary是两个高频杀手。前者意味着排序没有用到索引后者意味着用了临时表。这两个操作在数据量大的时候性能下降是指数级的。2.3 一个真实的排查案例之前有个业务反馈某个列表接口响应时间从200ms涨到了3秒。我拿到慢查询日志发现是一条带ORDER BY create_time DESC LIMIT 20的分页查询。EXPLAIN显示走了create_time的索引type是range看起来没问题。但仔细看rows列预估扫描行数是80万。问题就出在这里虽然走了索引但因为查询条件里还有一个status 1的过滤条件而status不在索引里导致数据库需要回表80万次才能过滤出符合条件的20条记录。这就是典型的索引选择性不足导致的回表放大。解决方案是建一个联合索引(status, create_time)让过滤和排序都在索引里完成回表次数从80万降到20次。改完之后接口响应时间回到了150ms。实操心得看执行计划不能只看有没有走索引还要看走了索引之后扫描了多少行、回了多少次表。rows列和实际返回行数的比值是判断索引效率的一个关键指标。3. 第二层索引失效的七种典型场景3.1 最左前缀原则与范围查询的陷阱联合索引(a, b, c)查询条件WHERE a 1 AND b 2 AND c 3c能用上索引吗答案是不能。因为b用了范围查询索引在b之后就不再有序了c无法继续利用索引的有序性。这个点面试官特别爱问而且会变着花样问。比如WHERE a 1 AND b LIKE xxx% AND c 3c同样用不上索引因为LIKE xxx%本质上也是范围查询。正确的做法是调整索引列的顺序把等值查询的列放在范围查询的列前面。如果业务允许也可以把范围查询拆成多个等值查询比如把b 2拆成b IN (3, 4, 5)这样c就能用上索引了。3.2 隐式类型转换看不见的索引杀手这个坑我踩过不止一次。表里user_id是varchar类型查询的时候写WHERE user_id 12345不加引号。MySQL会做隐式类型转换把user_id转成数字再比较索引直接失效。-- 索引失效 SELECT * FROM orders WHERE user_id 12345; -- 索引生效 SELECT * FROM orders WHERE user_id 12345;这个问题在代码里特别隐蔽因为很多ORM框架会自动处理参数类型但有时候传参的时候类型不对框架也不会报错直接就发出去了。我的建议是字段类型和传参类型必须严格一致代码review的时候把这条列为必查项。3.3 函数操作与计算索引的天然敌人WHERE DATE(create_time) 2024-01-01这个查询用不上create_time的索引。因为索引里存的是原始的create_time值不是DATE(create_time)的结果。数据库没法用索引去匹配一个函数计算后的值。正确的写法是改成范围查询-- 索引失效 SELECT * FROM orders WHERE DATE(create_time) 2024-01-01; -- 索引生效 SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;同样的道理WHERE id 1 10也用不上索引要改成WHERE id 9。索引列上不能做任何计算、函数、类型转换这是铁律。3.4 OR条件与索引合并的代价WHERE a 1 OR b 2如果a和b各自有索引MySQL可能会用索引合并index merge来优化。但索引合并的效率通常不如联合索引因为需要分别扫描两个索引再取并集中间还有去重和排序的开销。更好的做法是建联合索引(a, b)或者把OR改写成UNION-- 可能走索引合并效率一般 SELECT * FROM t WHERE a 1 OR b 2; -- 改写为UNION各自走索引 SELECT * FROM t WHERE a 1 UNION SELECT * FROM t WHERE b 2;注意UNION会自动去重如果不需要去重就用UNION ALL性能更好。3.5 LIKE通配符的位置决定生死LIKE %abc和LIKE abc%前者索引失效后者索引生效。这个大家都知道。但实际业务里经常有“模糊搜索”的需求用户就是要搜中间包含某个关键词的记录。这时候怎么办我的经验是如果数据量不大全表扫描也能接受那就别折腾。如果数据量大考虑用全文索引fulltext index或者引入专门的搜索引擎。不要试图用B树索引去解决所有搜索问题工具要选对场景。3.6 索引选择性不是所有索引都值得建索引的选择性 不重复的索引值数量 / 表的总行数。选择性越高索引效率越高。比如性别字段只有男/女两个值选择性极低建索引基本没用。而用户ID、订单号这种选择性接近1建索引效果最好。我一般会用一个简单的SQL来评估SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;如果结果小于0.1这个字段单独建索引的意义就不大需要考虑和其他字段组合。3.7 索引失效速查表场景是否失效解决方案联合索引不满足最左前缀失效调整索引列顺序或查询条件范围查询后的列失效把等值条件放前面或拆分范围隐式类型转换失效保证传参类型与字段类型一致索引列上使用函数失效改写为范围查询OR条件可能失效改写为UNION或建联合索引LIKE以%开头失效改用全文索引或搜索引擎索引选择性过低效果差考虑联合索引或不建4. 第三层索引没问题但依然慢的深层原因4.1 回表索引的隐形代价二级索引的叶子节点存的是主键值不是完整的行数据。所以通过二级索引查询时如果需要获取索引之外的列就必须拿着主键值回到聚簇索引里再查一次这就是回表。回表的代价在于每次回表都是一次随机I/O。如果二级索引扫描出1000行就要回表1000次。在机械硬盘上随机I/O的代价是顺序I/O的几十倍甚至上百倍。减少回表的手段有两个一是覆盖索引让查询需要的列全部包含在索引里二是延迟关联先用覆盖索引查出主键再用主键去关联原表。-- 延迟关联示例 SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) AS tmp ON t.id tmp.id;子查询里只查主键可以用覆盖索引完成避免回表。外层再用主键关联只回表20次。4.2 锁等待与并发争用有时候SQL本身没问题执行计划也漂亮但就是慢。这时候要怀疑锁。SHOW ENGINE INNODB STATUS可以看最近的锁等待情况information_schema.INNODB_TRX和INNODB_LOCKS可以看当前活跃的事务和锁。我遇到过一个案例一个看似简单的UPDATE语句执行时间偶尔飙到好几秒。排查发现是多个事务在竞争同一行记录的排他锁形成了锁等待队列。根本原因是业务逻辑里事务粒度太大一个事务里做了太多操作持锁时间过长。解决方案是缩小事务范围把不必要的操作移出事务或者调整业务逻辑减少热点行的并发更新。4.3 数据量与统计信息失真当表的数据量增长到一定程度优化器的统计信息可能失真导致它选了一个“看起来对但实际上错”的执行计划。这时候可以用ANALYZE TABLE手动更新统计信息。另外当单表数据量超过千万级别即使有索引维护索引的代价也会变得很高。这时候要考虑分区表或者分库分表。分区表把数据按规则拆到不同的物理文件查询时可以只扫描相关分区减少I/O。-- 按时间范围分区示例 CREATE TABLE orders ( id BIGINT, create_time DATETIME, ... ) PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025) );实操心得分区表的分区键必须包含在查询条件里否则会扫描所有分区性能反而更差。而且分区数量不宜过多一般建议控制在50个以内。4.4 硬件与参数配置的瓶颈如果SQL和索引都优化到位了还是慢那就要往上看磁盘I/O、内存、CPU、网络。iostat看磁盘top看CPU和内存sar看网络。数据库参数方面innodb_buffer_pool_size是最关键的。它决定了InnoDB能缓存多少数据和索引在内存里。如果这个值太小大量查询需要读磁盘性能自然上不去。一般建议设成物理内存的50%到70%。-- 查看缓冲池命中率 SHOW STATUS LIKE Innodb_buffer_pool_read%; -- 命中率 (1 - reads / read_requests) * 100%命中率低于95%就说明缓冲池可能不够用了。5. 第四层生产环境的安全排查与优化流程5.1 线上问题定位的黄金三分钟线上出问题第一要务是恢复业务不是找根因。我通常按这个顺序操作看监控大盘确认是数据库问题还是应用问题是单条SQL慢还是整体负载高。看当前活跃会话SHOW PROCESSLIST或information_schema.PROCESSLIST找出执行时间长的SQL。看锁等待如果有大量会话处于Waiting for table metadata lock或Waiting for row lock说明有锁争用。必要时kill掉阻塞源找到阻塞其他会话的源头评估影响后kill掉。-- 找出执行时间超过10秒的会话 SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND ! Sleep AND TIME 10 ORDER BY TIME DESC; -- kill指定会话 KILL CONNECTION 12345;注意kill会话之前一定要确认这个会话在做什么kill错了可能导致数据不一致。生产环境操作前最好和业务方确认。5.2 在线DDL加索引不能锁表给大表加索引如果直接ALTER TABLE ADD INDEX在MySQL 5.6之前会锁表业务直接不可用。5.6之后支持Online DDL但也不是完全无锁只是大部分阶段不阻塞读写。更稳妥的做法是用pt-online-schema-change或gh-ost这类工具。它们的原理是建一张新表把原表数据慢慢同步过去同步过程中在原表上建触发器捕获变更最后原子性地替换表名。整个过程对业务几乎无感知。# pt-online-schema-change 示例 pt-online-schema-change \ --alter ADD INDEX idx_status_create_time (status, create_time) \ --host127.0.0.1 \ --useradmin \ --password****** \ --databasebiz_db \ --tableorders \ --execute实操心得用这类工具之前一定要在测试环境完整跑一遍确认触发器逻辑和业务兼容。另外要关注磁盘空间因为要复制一份完整的数据。5.3 灰度验证与回滚预案任何优化上线之前必须准备好回滚方案。加索引的回滚就是删索引但删索引本身也可能锁表所以要用同样的在线工具操作。改SQL的回滚就是切回旧版本这个相对简单。灰度验证的思路是先在小流量环境验证观察核心指标响应时间、QPS、错误率、数据库负载有没有改善确认无误后再逐步放量。不要一次性全量上线否则出了问题影响面太大。5.4 优化效果评估的量化指标优化不能凭感觉要有数据支撑。我一般会记录优化前后的这几个指标指标优化前优化后目标平均响应时间3s150ms 200ms扫描行数80万20接近返回行数回表次数80万20最小化数据库CPU80%30% 50%慢查询数量100/分钟00这些数据从慢查询日志、监控系统和执行计划里都能拿到。有了量化对比才能证明优化是有效的也方便后续复盘。6. 面试中如何把连环炮变成加分项6.1 用排查链路代替标准答案面试官问“怎么排查慢查询”不要只回答“看慢查询日志”。要展示你的完整链路慢查询日志定位 → EXPLAIN分析执行计划 → 检查索引使用情况 → 分析回表和锁 → 评估数据量和配置 → 制定优化方案 → 灰度验证。这条链路说出来面试官就知道你是真干过。6.2 主动暴露取舍与权衡优化从来不是“越快越好”而是“在业务可接受的范围内找到性价比最高的方案”。比如加索引能提升查询性能但会降低写入性能、占用更多磁盘。分库分表能解决数据量问题但会带来分布式事务和跨库查询的复杂度。面试官很吃这一套因为这说明你有全局观不是只会背八股。6.3 用真实案例收尾最后准备一两个你实际做过的优化案例。不用多复杂但要有细节什么业务场景、什么现象、怎么排查、怎么解决、效果如何。细节越具体可信度越高。比如“我把一个分页查询从3秒优化到150毫秒核心是建了一个联合索引避免了80万次回表”这一句话就够面试官追问半天了而你已经准备好了所有答案。数据库调优这件事说到底就是理解原理、掌握工具、积累经验。原理让你知道为什么工具让你知道怎么做经验让你知道什么时候该做什么。这三样凑齐了面试连环炮也好线上故障也好都能从容应对。
阅读完成 · 觉得有帮助？
