MySQL慢查询高频面试题及详细实战答案

MySQL慢查询高频面试题及详细实战答案一、基础认知类（初级必问）1、什么是MySQL慢查询？慢查询的核心作用是什么？答案：慢查询指执行耗时超过指定阈值的SQL语句。MySQL会将这类执行效率低、耗时过长的SQL记录到慢查询日志中。核心作用：帮助开发、运维人员精准定位数据库中执行效率差、占用资源高的SQL语句，排查数据库卡顿、接口超时、服务器CPU/负载过高的核心原因，是数据库SQL优化、性能排查最基础、最核心的手段。2、慢查询核心配置参数有哪些？各自含义是什么？答案：日常工作中最常用的三个核心参数：slow_query_log：慢查询日志开关，OFF为关闭，ON为开启。默认多数数据库是关闭状态，生产环境建议长期开启。long_query_time：慢查询阈值，单位秒。SQL执行耗时超过该值就会被记录。默认值10秒，生产环境一般设置为1秒，部分高并发系统会设置0.5秒。slow_query_log_file：慢查询日志文件存储路径，用于定位日志文件位置，分析日志内容。补充两个常用拓展参数：log_queries_not_using_indexes：开启后，未使用索引的SQL无论耗时多久，都会被记录到慢查询日志，适合排查索引失效问题。log_throttle_queries_not_using_indexes：限制每分钟记录无索引SQL的次数，避免日志文件被大量无效SQL刷爆。3、慢查询阈值设置越小越好吗？生产环境建议设置多少？答案：不是。阈值设置过小（比如0.1秒），会导致大量正常的高频短耗时SQL被记录，日志文件膨胀极快，占用磁盘空间，同时增加日志分析难度，无法精准定位真正的慢SQL。生产常规规范：普通业务系统：long_query_time=1（1秒）高并发、高响应要求系统：0.5秒严禁设置默认10秒，会遗漏大量性能隐患SQL4、开启慢查询日志会不会影响数据库性能？答案：几乎无影响，可以忽略。慢查询日志的写入是异步操作，只有SQL执行超时后才会触发日志记录，且日志写入IO开销极低。行业通用做法：生产环境永久开启慢查询日志，是数据库运维的基础配置，不会造成性能损耗。二、日志分析与工具类（实操必问）5、日常工作中怎么分析慢查询日志？常用工具是什么？答案：分为原生命令和第三方工具两种，工作中以原生命令为主：1、mysqldumpslow（MySQL自带原生工具，最常用）核心常用命令：查看耗时最慢的10条SQL：mysqldumpslow-st-t10慢日志文件路径查看访问次数最多的10条SQL：mysqldumpslow-sc-t10慢日志文件路径筛选包含指定关键字的慢SQL：mysqldumpslow-g'select'日志路径参数说明：-s代表排序方式，t按耗时、c按执行次数、l按锁定时间；-t代表展示条数。2、第三方工具pt-query-digest（percona工具集），功能比原生工具更强大，可分析SQL平均耗时、最大耗时、扫描行数、返回行数，适合大批量日志批量分析，是运维优化常用工具。6、慢查询日志中关键字段你重点看哪些？分别代表什么问题？答案：分析慢SQL核心关注4个字段，可快速定位问题：Query_time：SQL总执行耗时，数值越大执行越慢Lock_time：等待锁的耗时，该值高说明数据库锁竞争严重、事务等待超时，大概率是事务过长、更新频繁导致Rows_examined：扫描的数据库行数，核心指标，数值极大说明索引失效、全表扫描Rows_sent：最终返回给客户端的行数核心判断逻辑：Rows_examined远大于Rows_sent，是典型的索引失效、无效扫描大量数据，是90%慢SQL的核心问题。三、原理与问题排查类（进阶高频）7、一条SQL执行很快，为什么会被记录到慢查询日志？答案：大概率是开启了log_queries_not_using_indexes参数。该参数开启后，不管SQL执行耗时多久，只要没有走有效索引、触发全表扫描，就会被强制记录到慢查询日志。很多简单的查询表数据量小，全表扫描耗时很短，但依然会被记录，属于正常现象，目的是提前发现潜在的索引问题，避免数据量增长后出现性能雪崩。8、常见的慢查询产生原因有哪些？答案：结合工作实战，核心原因分为6类：索引问题（最主要）：无索引、索引失效、索引字段不合理、联合索引不遵循最左匹配原则SQL写法问题：select*、多表笛卡尔积、大量in/or、模糊查询%前置、嵌套子查询过多事务问题：事务过长、批量操作未拆分、锁等待时间过长数据量问题：单表数据量过大（千万级以上），未分库分表、未冷热分离数据库配置问题：缓冲区、连接池参数配置不合理，资源不足业务问题：不合理的高频查询、重复查询、未做缓存，频繁查库9、索引正常的情况下，SQL为什么还是慢？答案：很多时候不是没索引，而是索引没有起到优化效果，常见场景：索引选择性极低：比如性别、状态字段，重复数据多，MySQL优化器认为走索引不如全表扫描，自动放弃索引查询返回数据量过大：单条SQL查询结果超过表数据20%左右，优化器会直接全表扫描索引字段计算、函数操作：where条件中对索引字段做运算、加函数，导致索引失效联合索引顺序错误：不满足最左前缀原则，索引无法命中数据库存在锁、事务阻塞：SQL本身执行快，但等待锁、等待事务提交耗时久，整体超时四、优化实战类（面试核心加分项）10、拿到一条慢SQL，你的完整优化流程是什么？答案：我工作中固定的排查优化流程，分为5步，逻辑闭环：第一步：explain分析执行计划优先看type、key、rows、Extra字段，判断是否全表扫描、是否命中索引、扫描行数、是否存在Usingfilesort、Usingtemporary。第二步：结合慢日志定位耗时节点区分耗时是查询扫描耗时，还是锁等待耗时，针对性解决。第三步：SQL语句优化优化写法：去掉select*、优化模糊查询、拆分长SQL、避免索引字段运算、优化join和子查询。第四步：索引优化缺失索引则新建索引，索引失效则调整索引字段、遵循最左匹配，删除冗余无效索引。第五步：业务与架构优化高频查询加Redis缓存、大表拆分、冷热数据分离、分页查询优化、批量操作拆分，从业务层面减少数据库压力。11、出现Usingfilesort、Usingtemporary慢查询，怎么解决？答案：1、Usingtemporary（临时表）产生场景：groupby、distinct、多表join、查询字段无索引。解决方案：给groupby、distinct字段建立索引，减少查询字段，避免无效去重和分组，优化关联查询逻辑。2、Usingfilesort（文件排序）产生场景：orderby字段未建立索引，MySQL需要内存或磁盘排序。解决方案：给orderby字段建立索引，优先使用索引排序，避免文件排序；同时保证查询字段、排序字段在同一个联合索引中，实现索引覆盖。12、大分页慢查询（limit100000,10）如何优化？答案：大分页核心问题：数据库需要先扫描前10万条数据，再丢弃，返回后10条，扫描行数极大。实战优化方案：主键回溯优化：先通过主键索引分页查出id，再关联查询数据，减少无效扫描禁止深度分页：前端限制分页页码，不允许用户查询超大页码书签式分页：用最后一条数据的id作为条件分页，替代传统limit分页大数据量场景：同步数据到ES，分页、检索走ES，不查MySQL五、高阶场景面试题13、慢查询日志越来越大，如何处理？可以直接删除吗？答案：不可以直接rm删除日志文件。MySQL进程已经打开了日志文件句柄，直接删除文件，磁盘空间不会立即释放，MySQL依然会持续写入，导致磁盘占用异常、日志丢失。正确处理方式：先备份原日志文件执行flushslowlogs;刷新日志，生成新的空日志文件删除旧的日志备份文件，释放磁盘空间生产可配置定时任务，定期切割、清理慢日志，避免日志过大。14、为什么有时候explain显示索引命中，但实际还是慢查询？答案：explain是预估执行计划，不是实际执行结果，存在偏差，常见原因：索引选择性动态变化：数据分布不均，部分场景索引失效，预估和实际执行不一致覆盖索引缺失：命中二级索引后，需要频繁回表查询数据，回表开销大，导致耗时增加锁与事务影响：explain不检测锁等待、事务阻塞，实际执行存在锁耗时缓冲区不足：内存缓冲区不够，大量索引、数据读取走磁盘IO，拖慢速度15、高并发场景下，如何预防慢查询产生？答案：预防大于优化，生产落地规范：上线SQL