最近在技术社区看到一个很有意思的面试题它没有问“索引怎么建”或者“SQL怎么写”而是抛出了一个更贴近实战的场景一条昨天还跑得飞快的SQL今天突然慢如蜗牛直接把数据库CPU拉满你作为第一责任人怎么快速定位问题这个问题之所以经典是因为它戳中了后端开发和DBA的日常痛点。很多同学对SQL优化理论头头是道但真遇到线上突发性能问题面对监控告警和业务方的催促往往容易手忙脚乱陷入“重启大法好”或者“盲目加索引”的误区。这篇文章我们就来系统性地拆解这个“SQL突然变慢”的排查难题。我会结合真实的线上运维经验为你梳理出一条从现象到根因的清晰路径。读完本文你将掌握一套可复用的、层层递进的排查方法论而不仅仅是几个零散的命令。下次再遇到类似问题你就能像老中医一样望闻问切快速定位病灶。1. 问题本质为什么“昨天快今天慢”在开始动手之前我们必须先理解问题的本质。一条SQL的执行时间从50毫秒飙升到5秒CPU使用率从个位数冲到90%这绝不是简单的“代码没变”就能解释的。我们需要建立一个核心认知SQL的执行性能是数据库系统内部多种因素动态作用的结果。这些因素可以归纳为三个层面SQL本身与数据查询逻辑、索引有效性、数据分布数据量、倾斜度。数据库运行时状态连接数、锁竞争、缓冲区命中率、临时表/排序状态。外部环境与资源服务器负载CPU、内存、IO、网络、并发压力。“昨天快今天慢”的现象强烈暗示了环境或数据的动态变化是主要原因。我们的排查思路就应该像侦探破案一样沿着“现场证据当前状态”回溯“案发经过变化点”。2. 建立排查心智模型从全局到局部面对突发问题最忌讳的就是一头扎进细节。一个高效的排查者应该遵循“先全局后局部先外部后内部”的原则。我将其总结为“四步排查法”确认现象与影响范围问题真的如描述所说吗只影响这一条SQL还是整个库检查外部资源与负载是不是宿主机的“锅”探查数据库内部状态数据库自身是否健康有无资源瓶颈或等待事件聚焦问题SQL与执行计划最终锁定到具体的SQL及其执行路径。接下来我们按照这个模型一步步展开操作。3. 第一步确认现象与影响范围接到告警不要急着连数据库。首先利用监控系统回答几个关键问题问题SQL是否唯一变慢的查看数据库整体的QPS每秒查询数、平均响应时间、慢查询数量。如果只有个别SQL变慢可能是索引或数据问题如果整体性能下降则可能是资源瓶颈或数据库级问题如锁表。CPU飙高是持续性的还是间歇性的持续高可能是有“慢查询”在持续消耗间歇性高可能与定时任务或特定业务高峰并发有关。影响的时间点是否与某些变更吻合回想或查询变更记录是否有应用发布、配置更新、数据迁移、定时任务调整操作与判断查看监控大盘使用如 Prometheus Grafana、阿里云CloudMonitor、腾讯云DBbrain等工具。查询慢日志立即查看数据库慢查询日志slow log确认那条5秒的SQL是否已被记录并关注同一时间段内是否还有其他慢查询涌现。-- MySQL 示例查看慢查询日志配置及近期慢查询需有权限 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 可以直接查看慢日志文件或使用 mysqldumpslow 工具分析 -- mysqldumpslow -s t -t 10 /path/to/slow.log # 按时间排序取最慢的10条如果确认是单条SQL问题且与发布时间点关联不大我们进入下一步。4. 第二步检查外部资源与服务器负载数据库是跑在服务器上的应用。服务器资源瓶颈会直接拖慢所有数据库操作。我们需要快速排除这个可能性。关键检查点CPU使用top或htop命令看是否是数据库进程如mysqld,postgres本身占用了高CPU还是其他进程如备份、日志分析导致的。内存使用free -h或vmstat观察是否发生大量Swap交换分区。数据库大量使用Swap会导致性能急剧下降。磁盘I/O使用iostat -x 1或iotop命令查看磁盘的利用率%util、等待时间await和读写速率。高I/O等待往往是性能杀手。网络检查网络连接数和带宽是否打满。操作示例# 1. 整体资源概览 top -c # 在top界面按1查看各CPU核心利用率按P按CPU排序观察mysqld进程占比。 # 2. 内存与Swap检查 free -h # 关注 available 内存和 swap used。如果swap used持续增长说明物理内存不足。 # 3. 磁盘I/O检查 iostat -x 1 5 # 重点观察 %util (利用率接近100%表示饱和) 和 await (平均I/O等待时间单位毫秒值越大越慢)。 # 4. 快速查看数据库所在服务器整体负载 uptime # 查看1,5,15分钟的平均负载。如果负载远高于CPU核心数说明系统过载。判断如果服务器资源特别是CPU和I/O整体吃紧且与数据库进程关联度高那么问题可能不仅是SQL本身而是并发量上升或资源不足。但如果资源使用正常唯独数据库进程CPU高那么问题焦点就收缩到数据库内部。5. 第三步探查数据库内部状态现在我们登录数据库检查其内部运行状态。目标是找到“瓶颈点”或“等待事件”。5.1 查看当前活动会话与正在执行的SQL这是最重要的一步相当于给数据库做“实时心电图”。-- MySQL 5.7/8.0 示例 SELECT p.ID AS process_id, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME AS execution_time_sec, -- 执行时间单位秒 p.STATE, i.INFO AS sql_text -- 正在执行的SQL语句 FROM INFORMATION_SCHEMA.PROCESSLIST p LEFT JOIN PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_CURRENT i ON p.ID i.PROCESSLIST_ID WHERE p.COMMAND ! Sleep AND p.TIME 2 -- 筛选执行时间超过2秒的活跃会话 ORDER BY p.TIME DESC;关注点sql_text找到那条执行时间TIME很长的SQL确认它就是罪魁祸首。STATE会话状态。如果是Sending data,Copying to tmp table,Sorting result,Creating sort index等往往意味着查询正在做大量数据操作或排序是性能问题的直接表现。如果找不到完全匹配的SQL可能是SQL已经执行完毕但连接未释放或者问题具有间歇性。此时需要开启性能模式Performance Schema或使用更高级的监控工具抓取。5.2 分析数据库关键性能指标通过数据库状态变量了解其健康度。-- MySQL 查看一些关键计数器需要定期采样对比 SHOW GLOBAL STATUS LIKE Threads_running; -- 当前正在执行的线程数过高说明并发紧张 SHOW GLOBAL STATUS LIKE Innodb_row_lock%; -- 行锁情况 SHOW GLOBAL STATUS LIKE Table_locks_waited; -- 表锁等待 SHOW GLOBAL STATUS LIKE Slow_queries; -- 慢查询计数 SHOW GLOBAL STATUS LIKE Sort_merge_passes; -- 排序合并次数过多可能意味着排序缓冲区不足 -- 查看当前锁信息MySQL 8.0 或使用 InnoDB引擎 SELECT * FROM performance_schema.data_locks; -- 查看当前持有的锁 SELECT * FROM performance_schema.data_lock_waits; -- 查看锁等待关系5.3 检查缓冲区命中率数据库的性能极度依赖内存缓冲。如果缓冲区命中率低会导致大量物理磁盘I/O。-- MySQL InnoDB Buffer Pool 命中率计算近似 -- 需要计算两次采样的差值 SET innodb_buffer_pool_reads_1 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME Innodb_buffer_pool_reads); SET innodb_buffer_pool_read_requests_1 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME Innodb_buffer_pool_read_requests); -- 等待一段时间如10秒 SELECT SLEEP(10); -- 再次采样并计算 SET innodb_buffer_pool_reads_2 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME Innodb_buffer_pool_reads); SET innodb_buffer_pool_read_requests_2 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME Innodb_buffer_pool_read_requests); SELECT (1 - ((innodb_buffer_pool_reads_2 - innodb_buffer_pool_reads_1) / NULLIF((innodb_buffer_pool_read_requests_2 - innodb_buffer_pool_read_requests_1), 0))) * 100 AS buffer_pool_hit_rate(%); -- 通常命中率应高于99%低于95%可能意味着内存不足或扫描了太多无效数据。经过第三步我们可能已经发现了明显的锁等待、大量的全表扫描状态或低缓冲区命中率。接下来就要对具体的SQL进行“解剖”了。6. 第四步聚焦问题SQL与执行计划分析假设我们通过第三步的PROCESSLIST找到了那条慢SQL。现在我们需要获取它的执行计划Explain Plan。执行计划是数据库优化器决定的查询执行路径是理解“为什么慢”的钥匙。6.1 获取并解读执行计划-- 在测试环境或从慢日志中取出完整的SQL前面加上 EXPLAIN 或 EXPLAIN FORMATJSON EXPLAIN FORMATJSON SELECT * FROM your_table WHERE user_id 123 AND create_time 2024-01-01 ORDER BY id DESC LIMIT 100; -- 或者使用传统格式 EXPLAIN SELECT * FROM your_table WHERE ...;解读执行计划的关键点以MySQL为例type列访问类型性能从优到劣大致是systemconsteq_refrefrangeindexALL。ALL全表扫描最需要警惕的尤其是大表。这意味着数据库需要逐行检查。index全索引扫描虽然走了索引但扫描了整个索引树也可能很慢。ref/range通常是比较好的使用了索引的等值或范围查找。key列实际使用的索引。如果为NULL说明没用到索引。rows列优化器预估需要扫描的行数。这个值如果远大于实际需要说明统计信息可能不准或者索引选择不佳。Extra列额外信息包含很多“危险信号”Using filesort表示MySQL需要额外的一次排序而排序无法通过索引顺序完成。这通常在ORDER BY和GROUP BY子句中出现如果数据量大会在磁盘或内存中创建临时表排序非常消耗CPU和内存。Using temporary表示使用了临时表。这常见于排序、分组或多表连接时。临时表可能在内存中也可能被写到磁盘上后者极慢。Using where表示在存储引擎检索行后服务器层再次进行了过滤。如果type是ALL且Using where说明全表扫描后还做了大量过滤性能极差。6.2 对比“昨天”和“今天”的执行计划“昨天快今天慢”的核心往往是执行计划发生了变化。你需要设法获取或推断出昨天的执行计划如果历史监控有记录最好。对比两者重点关注使用的索引是否不同例如从高效索引变成了低效索引或全表扫描连接顺序join order是否改变预估行数rows是否有巨大差异如何获取历史计划如果数据库有SQL性能洞察如阿里云的DAS腾讯云的DBbrain可以直接查看历史执行计划。如果没有则需要依靠慢查询日志如果昨天50ms的SQL没被记录可能就看不到了或者根据经验推断。6.3 深入分析为什么执行计划会变执行计划变化的常见元凶统计信息过时/不准确数据库优化器依赖表和索引的统计信息如数据行数、唯一值数量、数据分布直方图来选择“成本最低”的执行路径。如果统计信息没有及时更新例如在大量数据插入、删除、更新后优化器可能会做出错误判断。-- MySQL 更新表统计信息 ANALYZE TABLE your_table; -- 对于InnoDB也可以设置 innodb_stats_auto_recalc 为 ON默认但大变动后手动执行一次更稳妥。索引失效或未被使用函数操作导致索引失效WHERE DATE(create_time) 2024-05-20会使create_time索引失效。隐式类型转换WHERE user_id 123user_id是整型可能导致索引失效。不满足最左前缀原则对于复合索引(a, b, c)查询条件WHERE b 1 AND c 2无法有效使用该索引。索引选择性差在“性别”这种只有两个值的列上建索引优化器可能认为全表扫描更快。数据量突变这是最直接的原因。例如查询条件WHERE status PENDING昨天只有100条数据今天由于某个批量任务失败积压了100万条。查询WHERE create_time NOW() - INTERVAL 1 DAY随着时间推移扫描的数据范围自然变大。数据库参数或版本变化虽然不常见但数据库参数调整如optimizer_switch中的标志或小版本升级也可能改变优化器的行为。7. 实战模拟与复现演练让我们用一个简化的例子来串联整个排查过程。假设我们有一张订单表orders。表结构CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, amount DECIMAL(10,2), status VARCHAR(20), create_time DATETIME, INDEX idx_user_status (user_id, status), INDEX idx_create_time (create_time) );问题SQLSELECT * FROM orders WHERE user_id 10086 AND status COMPLETED ORDER BY create_time DESC LIMIT 10;昨天执行很快今天突然变慢。排查步骤复现确认现象监控显示此SQL平均响应时间从100ms升至3s数据库CPU同步升高。检查服务器iostat显示磁盘await较高但%util未饱和。top显示mysqld进程CPU占用高。查看数据库会话执行SHOW PROCESSLIST;发现该SQL状态为Creating sort index执行时间已超5秒。分析执行计划EXPLAIN FORMATJSON SELECT * FROM orders WHERE user_id 10086 AND status COMPLETED ORDER BY create_time DESC LIMIT 10;发现执行计划显示type:refkey:idx_user_status用到了复合索引rows: 预估500行Extra:Using filesort-- 危险信号根因分析索引idx_user_status (user_id, status)能高效定位到user_id10086 AND statusCOMPLETED的所有行。但是ORDER BY create_time DESC要求按时间排序。而create_time不在idx_user_status索引中因此数据库需要将筛选出的500行实际可能远多于500数据根据create_time在内存或磁盘上进行一次额外的排序filesort。数据变化昨天user_id10086的完成订单只有几十个排序很快。今天该用户完成了数千个订单排序的数据量剧增导致filesort操作消耗大量CPU和时间并可能使用磁盘临时表进一步拖慢速度。解决方案短期应急考虑优化查询比如如果业务允许去掉ORDER BY或者增加create_time的筛选条件减少排序数据量。根本解决创建更合适的索引来覆盖查询和排序。例如创建索引(user_id, status, create_time)。这样数据库可以直接利用索引的有序性来满足WHERE和ORDER BY避免filesort。ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time DESC); -- 注意MySQL 8.0支持降序索引对于 ORDER BY ... DESC 的场景有优化。执行ANALYZE TABLE orders;更新统计信息确保优化器能做出正确选择。8. 常见问题排查清单速查表当你时间紧迫时可以按此清单快速过一遍问题现象/怀疑方向排查命令/方法可能原因与解决方案CPU持续高大量活跃会话SHOW PROCESSLIST;SELECT * FROM sys.session;(MySQL 5.7/Perf Schema)慢查询堆积找到慢SQL并优化。锁等待检查data_lock_waits优化事务减少锁持有时间。单条SQL突然变慢EXPLAIN FORMATJSON [你的慢SQL]对比历史执行计划执行计划变更更新统计信息(ANALYZE TABLE)。索引失效检查SQL写法避免函数、类型转换。数据量激增优化查询条件或增加更合适的索引。磁盘IO高响应慢iostat -x 1SHOW GLOBAL STATUS LIKE Innodb_buffer_pool%;缓冲池命中率低考虑增加innodb_buffer_pool_size。全表扫描或大排序通过执行计划优化查询避免Using temporary; Using filesort。大量STATE为Copying to tmp table或Sorting resultSHOW PROCESSLIST;检查Extra列查询需要临时表/排序优化GROUP BY,ORDER BY,DISTINCT子句尝试利用索引完成排序。检查tmp_table_size和max_heap_table_size是否过小。怀疑统计信息问题SHOW INDEX FROM your_table;查看基数(Cardinality)ANALYZE TABLE your_table;索引基数不准确手动更新统计信息。对于数据分布不均匀的表考虑使用更详细的统计信息收集如直方图。应用层面感觉慢但数据库监控正常检查应用连接池、网络延迟、GC停顿。在数据库端抓取该应用会话的完整SQL及其执行时间。网络问题或应用侧处理耗时使用数据库的审计日志或性能模式追踪完整事务。9. 最佳实践与长效预防机制救火固然重要但防火才是根本。建立长效预防机制能极大减少此类突发问题的发生完善的监控与告警核心指标监控数据库CPU、内存、连接数、IOPS、慢查询数、QPS、TPS。设置智能告警对慢查询数量、CPU使用率、活跃连接数设置阈值告警而不是等到业务反馈。使用APM工具在应用层集成APM如SkyWalking, Pinpoint追踪从用户请求到SQL执行的完整链路快速定位瓶颈。SQL审核与上线前优化强制SQL审核所有上线的SQL必须经过EXPLAIN审核禁止出现全表扫描(typeALL)和低效的Using filesort/Using temporary。建立慢查询档案对核心业务的SQL进行性能基线管理记录其历史执行时间、扫描行数。当出现偏离基线时自动告警。定期健康检查与优化定期更新统计信息对于核心且数据变化频繁的表在业务低峰期定期执行ANALYZE TABLE。定期Review索引清理无效、重复的索引。为新上线的业务查询设计合适的覆盖索引。进行压力测试在大促或业务增长前对数据库进行压测提前发现潜在性能瓶颈。开发规范与意识避免在WHERE子句中对字段进行函数操作。注意隐式类型转换的风险。大数据量排序/分组优先考虑在数据库层面用索引解决而非拉到应用层处理。写操作UPDATE/DELETE务必带上WHERE条件并利用索引避免锁全表。回到开头的面试题一个完整的回答思路应该是先全局监控确认影响范围再排除服务器资源瓶颈接着深入数据库内部查看活跃会话和锁信息最后通过对比分析执行计划的变迁锁定统计信息不准、索引失效或数据量突变等具体原因并给出应急和根治方案。这套方法的价值在于其系统性和可复用性。它训练的不是死记硬背命令而是一种在压力下依然保持清晰逻辑的排查思维。掌握它你不仅能应对面试更能从容处理未来无数个真实的“惊心动魄”的线上时刻。
SQL性能突降排查实战：从CPU拉满到根因定位的完整指南
最近在技术社区看到一个很有意思的面试题它没有问“索引怎么建”或者“SQL怎么写”而是抛出了一个更贴近实战的场景一条昨天还跑得飞快的SQL今天突然慢如蜗牛直接把数据库CPU拉满你作为第一责任人怎么快速定位问题这个问题之所以经典是因为它戳中了后端开发和DBA的日常痛点。很多同学对SQL优化理论头头是道但真遇到线上突发性能问题面对监控告警和业务方的催促往往容易手忙脚乱陷入“重启大法好”或者“盲目加索引”的误区。这篇文章我们就来系统性地拆解这个“SQL突然变慢”的排查难题。我会结合真实的线上运维经验为你梳理出一条从现象到根因的清晰路径。读完本文你将掌握一套可复用的、层层递进的排查方法论而不仅仅是几个零散的命令。下次再遇到类似问题你就能像老中医一样望闻问切快速定位病灶。1. 问题本质为什么“昨天快今天慢”在开始动手之前我们必须先理解问题的本质。一条SQL的执行时间从50毫秒飙升到5秒CPU使用率从个位数冲到90%这绝不是简单的“代码没变”就能解释的。我们需要建立一个核心认知SQL的执行性能是数据库系统内部多种因素动态作用的结果。这些因素可以归纳为三个层面SQL本身与数据查询逻辑、索引有效性、数据分布数据量、倾斜度。数据库运行时状态连接数、锁竞争、缓冲区命中率、临时表/排序状态。外部环境与资源服务器负载CPU、内存、IO、网络、并发压力。“昨天快今天慢”的现象强烈暗示了环境或数据的动态变化是主要原因。我们的排查思路就应该像侦探破案一样沿着“现场证据当前状态”回溯“案发经过变化点”。2. 建立排查心智模型从全局到局部面对突发问题最忌讳的就是一头扎进细节。一个高效的排查者应该遵循“先全局后局部先外部后内部”的原则。我将其总结为“四步排查法”确认现象与影响范围问题真的如描述所说吗只影响这一条SQL还是整个库检查外部资源与负载是不是宿主机的“锅”探查数据库内部状态数据库自身是否健康有无资源瓶颈或等待事件聚焦问题SQL与执行计划最终锁定到具体的SQL及其执行路径。接下来我们按照这个模型一步步展开操作。3. 第一步确认现象与影响范围接到告警不要急着连数据库。首先利用监控系统回答几个关键问题问题SQL是否唯一变慢的查看数据库整体的QPS每秒查询数、平均响应时间、慢查询数量。如果只有个别SQL变慢可能是索引或数据问题如果整体性能下降则可能是资源瓶颈或数据库级问题如锁表。CPU飙高是持续性的还是间歇性的持续高可能是有“慢查询”在持续消耗间歇性高可能与定时任务或特定业务高峰并发有关。影响的时间点是否与某些变更吻合回想或查询变更记录是否有应用发布、配置更新、数据迁移、定时任务调整操作与判断查看监控大盘使用如 Prometheus Grafana、阿里云CloudMonitor、腾讯云DBbrain等工具。查询慢日志立即查看数据库慢查询日志slow log确认那条5秒的SQL是否已被记录并关注同一时间段内是否还有其他慢查询涌现。-- MySQL 示例查看慢查询日志配置及近期慢查询需有权限 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 可以直接查看慢日志文件或使用 mysqldumpslow 工具分析 -- mysqldumpslow -s t -t 10 /path/to/slow.log # 按时间排序取最慢的10条如果确认是单条SQL问题且与发布时间点关联不大我们进入下一步。4. 第二步检查外部资源与服务器负载数据库是跑在服务器上的应用。服务器资源瓶颈会直接拖慢所有数据库操作。我们需要快速排除这个可能性。关键检查点CPU使用top或htop命令看是否是数据库进程如mysqld,postgres本身占用了高CPU还是其他进程如备份、日志分析导致的。内存使用free -h或vmstat观察是否发生大量Swap交换分区。数据库大量使用Swap会导致性能急剧下降。磁盘I/O使用iostat -x 1或iotop命令查看磁盘的利用率%util、等待时间await和读写速率。高I/O等待往往是性能杀手。网络检查网络连接数和带宽是否打满。操作示例# 1. 整体资源概览 top -c # 在top界面按1查看各CPU核心利用率按P按CPU排序观察mysqld进程占比。 # 2. 内存与Swap检查 free -h # 关注 available 内存和 swap used。如果swap used持续增长说明物理内存不足。 # 3. 磁盘I/O检查 iostat -x 1 5 # 重点观察 %util (利用率接近100%表示饱和) 和 await (平均I/O等待时间单位毫秒值越大越慢)。 # 4. 快速查看数据库所在服务器整体负载 uptime # 查看1,5,15分钟的平均负载。如果负载远高于CPU核心数说明系统过载。判断如果服务器资源特别是CPU和I/O整体吃紧且与数据库进程关联度高那么问题可能不仅是SQL本身而是并发量上升或资源不足。但如果资源使用正常唯独数据库进程CPU高那么问题焦点就收缩到数据库内部。5. 第三步探查数据库内部状态现在我们登录数据库检查其内部运行状态。目标是找到“瓶颈点”或“等待事件”。5.1 查看当前活动会话与正在执行的SQL这是最重要的一步相当于给数据库做“实时心电图”。-- MySQL 5.7/8.0 示例 SELECT p.ID AS process_id, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME AS execution_time_sec, -- 执行时间单位秒 p.STATE, i.INFO AS sql_text -- 正在执行的SQL语句 FROM INFORMATION_SCHEMA.PROCESSLIST p LEFT JOIN PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_CURRENT i ON p.ID i.PROCESSLIST_ID WHERE p.COMMAND ! Sleep AND p.TIME 2 -- 筛选执行时间超过2秒的活跃会话 ORDER BY p.TIME DESC;关注点sql_text找到那条执行时间TIME很长的SQL确认它就是罪魁祸首。STATE会话状态。如果是Sending data,Copying to tmp table,Sorting result,Creating sort index等往往意味着查询正在做大量数据操作或排序是性能问题的直接表现。如果找不到完全匹配的SQL可能是SQL已经执行完毕但连接未释放或者问题具有间歇性。此时需要开启性能模式Performance Schema或使用更高级的监控工具抓取。5.2 分析数据库关键性能指标通过数据库状态变量了解其健康度。-- MySQL 查看一些关键计数器需要定期采样对比 SHOW GLOBAL STATUS LIKE Threads_running; -- 当前正在执行的线程数过高说明并发紧张 SHOW GLOBAL STATUS LIKE Innodb_row_lock%; -- 行锁情况 SHOW GLOBAL STATUS LIKE Table_locks_waited; -- 表锁等待 SHOW GLOBAL STATUS LIKE Slow_queries; -- 慢查询计数 SHOW GLOBAL STATUS LIKE Sort_merge_passes; -- 排序合并次数过多可能意味着排序缓冲区不足 -- 查看当前锁信息MySQL 8.0 或使用 InnoDB引擎 SELECT * FROM performance_schema.data_locks; -- 查看当前持有的锁 SELECT * FROM performance_schema.data_lock_waits; -- 查看锁等待关系5.3 检查缓冲区命中率数据库的性能极度依赖内存缓冲。如果缓冲区命中率低会导致大量物理磁盘I/O。-- MySQL InnoDB Buffer Pool 命中率计算近似 -- 需要计算两次采样的差值 SET innodb_buffer_pool_reads_1 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME Innodb_buffer_pool_reads); SET innodb_buffer_pool_read_requests_1 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME Innodb_buffer_pool_read_requests); -- 等待一段时间如10秒 SELECT SLEEP(10); -- 再次采样并计算 SET innodb_buffer_pool_reads_2 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME Innodb_buffer_pool_reads); SET innodb_buffer_pool_read_requests_2 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME Innodb_buffer_pool_read_requests); SELECT (1 - ((innodb_buffer_pool_reads_2 - innodb_buffer_pool_reads_1) / NULLIF((innodb_buffer_pool_read_requests_2 - innodb_buffer_pool_read_requests_1), 0))) * 100 AS buffer_pool_hit_rate(%); -- 通常命中率应高于99%低于95%可能意味着内存不足或扫描了太多无效数据。经过第三步我们可能已经发现了明显的锁等待、大量的全表扫描状态或低缓冲区命中率。接下来就要对具体的SQL进行“解剖”了。6. 第四步聚焦问题SQL与执行计划分析假设我们通过第三步的PROCESSLIST找到了那条慢SQL。现在我们需要获取它的执行计划Explain Plan。执行计划是数据库优化器决定的查询执行路径是理解“为什么慢”的钥匙。6.1 获取并解读执行计划-- 在测试环境或从慢日志中取出完整的SQL前面加上 EXPLAIN 或 EXPLAIN FORMATJSON EXPLAIN FORMATJSON SELECT * FROM your_table WHERE user_id 123 AND create_time 2024-01-01 ORDER BY id DESC LIMIT 100; -- 或者使用传统格式 EXPLAIN SELECT * FROM your_table WHERE ...;解读执行计划的关键点以MySQL为例type列访问类型性能从优到劣大致是systemconsteq_refrefrangeindexALL。ALL全表扫描最需要警惕的尤其是大表。这意味着数据库需要逐行检查。index全索引扫描虽然走了索引但扫描了整个索引树也可能很慢。ref/range通常是比较好的使用了索引的等值或范围查找。key列实际使用的索引。如果为NULL说明没用到索引。rows列优化器预估需要扫描的行数。这个值如果远大于实际需要说明统计信息可能不准或者索引选择不佳。Extra列额外信息包含很多“危险信号”Using filesort表示MySQL需要额外的一次排序而排序无法通过索引顺序完成。这通常在ORDER BY和GROUP BY子句中出现如果数据量大会在磁盘或内存中创建临时表排序非常消耗CPU和内存。Using temporary表示使用了临时表。这常见于排序、分组或多表连接时。临时表可能在内存中也可能被写到磁盘上后者极慢。Using where表示在存储引擎检索行后服务器层再次进行了过滤。如果type是ALL且Using where说明全表扫描后还做了大量过滤性能极差。6.2 对比“昨天”和“今天”的执行计划“昨天快今天慢”的核心往往是执行计划发生了变化。你需要设法获取或推断出昨天的执行计划如果历史监控有记录最好。对比两者重点关注使用的索引是否不同例如从高效索引变成了低效索引或全表扫描连接顺序join order是否改变预估行数rows是否有巨大差异如何获取历史计划如果数据库有SQL性能洞察如阿里云的DAS腾讯云的DBbrain可以直接查看历史执行计划。如果没有则需要依靠慢查询日志如果昨天50ms的SQL没被记录可能就看不到了或者根据经验推断。6.3 深入分析为什么执行计划会变执行计划变化的常见元凶统计信息过时/不准确数据库优化器依赖表和索引的统计信息如数据行数、唯一值数量、数据分布直方图来选择“成本最低”的执行路径。如果统计信息没有及时更新例如在大量数据插入、删除、更新后优化器可能会做出错误判断。-- MySQL 更新表统计信息 ANALYZE TABLE your_table; -- 对于InnoDB也可以设置 innodb_stats_auto_recalc 为 ON默认但大变动后手动执行一次更稳妥。索引失效或未被使用函数操作导致索引失效WHERE DATE(create_time) 2024-05-20会使create_time索引失效。隐式类型转换WHERE user_id 123user_id是整型可能导致索引失效。不满足最左前缀原则对于复合索引(a, b, c)查询条件WHERE b 1 AND c 2无法有效使用该索引。索引选择性差在“性别”这种只有两个值的列上建索引优化器可能认为全表扫描更快。数据量突变这是最直接的原因。例如查询条件WHERE status PENDING昨天只有100条数据今天由于某个批量任务失败积压了100万条。查询WHERE create_time NOW() - INTERVAL 1 DAY随着时间推移扫描的数据范围自然变大。数据库参数或版本变化虽然不常见但数据库参数调整如optimizer_switch中的标志或小版本升级也可能改变优化器的行为。7. 实战模拟与复现演练让我们用一个简化的例子来串联整个排查过程。假设我们有一张订单表orders。表结构CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, amount DECIMAL(10,2), status VARCHAR(20), create_time DATETIME, INDEX idx_user_status (user_id, status), INDEX idx_create_time (create_time) );问题SQLSELECT * FROM orders WHERE user_id 10086 AND status COMPLETED ORDER BY create_time DESC LIMIT 10;昨天执行很快今天突然变慢。排查步骤复现确认现象监控显示此SQL平均响应时间从100ms升至3s数据库CPU同步升高。检查服务器iostat显示磁盘await较高但%util未饱和。top显示mysqld进程CPU占用高。查看数据库会话执行SHOW PROCESSLIST;发现该SQL状态为Creating sort index执行时间已超5秒。分析执行计划EXPLAIN FORMATJSON SELECT * FROM orders WHERE user_id 10086 AND status COMPLETED ORDER BY create_time DESC LIMIT 10;发现执行计划显示type:refkey:idx_user_status用到了复合索引rows: 预估500行Extra:Using filesort-- 危险信号根因分析索引idx_user_status (user_id, status)能高效定位到user_id10086 AND statusCOMPLETED的所有行。但是ORDER BY create_time DESC要求按时间排序。而create_time不在idx_user_status索引中因此数据库需要将筛选出的500行实际可能远多于500数据根据create_time在内存或磁盘上进行一次额外的排序filesort。数据变化昨天user_id10086的完成订单只有几十个排序很快。今天该用户完成了数千个订单排序的数据量剧增导致filesort操作消耗大量CPU和时间并可能使用磁盘临时表进一步拖慢速度。解决方案短期应急考虑优化查询比如如果业务允许去掉ORDER BY或者增加create_time的筛选条件减少排序数据量。根本解决创建更合适的索引来覆盖查询和排序。例如创建索引(user_id, status, create_time)。这样数据库可以直接利用索引的有序性来满足WHERE和ORDER BY避免filesort。ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time DESC); -- 注意MySQL 8.0支持降序索引对于 ORDER BY ... DESC 的场景有优化。执行ANALYZE TABLE orders;更新统计信息确保优化器能做出正确选择。8. 常见问题排查清单速查表当你时间紧迫时可以按此清单快速过一遍问题现象/怀疑方向排查命令/方法可能原因与解决方案CPU持续高大量活跃会话SHOW PROCESSLIST;SELECT * FROM sys.session;(MySQL 5.7/Perf Schema)慢查询堆积找到慢SQL并优化。锁等待检查data_lock_waits优化事务减少锁持有时间。单条SQL突然变慢EXPLAIN FORMATJSON [你的慢SQL]对比历史执行计划执行计划变更更新统计信息(ANALYZE TABLE)。索引失效检查SQL写法避免函数、类型转换。数据量激增优化查询条件或增加更合适的索引。磁盘IO高响应慢iostat -x 1SHOW GLOBAL STATUS LIKE Innodb_buffer_pool%;缓冲池命中率低考虑增加innodb_buffer_pool_size。全表扫描或大排序通过执行计划优化查询避免Using temporary; Using filesort。大量STATE为Copying to tmp table或Sorting resultSHOW PROCESSLIST;检查Extra列查询需要临时表/排序优化GROUP BY,ORDER BY,DISTINCT子句尝试利用索引完成排序。检查tmp_table_size和max_heap_table_size是否过小。怀疑统计信息问题SHOW INDEX FROM your_table;查看基数(Cardinality)ANALYZE TABLE your_table;索引基数不准确手动更新统计信息。对于数据分布不均匀的表考虑使用更详细的统计信息收集如直方图。应用层面感觉慢但数据库监控正常检查应用连接池、网络延迟、GC停顿。在数据库端抓取该应用会话的完整SQL及其执行时间。网络问题或应用侧处理耗时使用数据库的审计日志或性能模式追踪完整事务。9. 最佳实践与长效预防机制救火固然重要但防火才是根本。建立长效预防机制能极大减少此类突发问题的发生完善的监控与告警核心指标监控数据库CPU、内存、连接数、IOPS、慢查询数、QPS、TPS。设置智能告警对慢查询数量、CPU使用率、活跃连接数设置阈值告警而不是等到业务反馈。使用APM工具在应用层集成APM如SkyWalking, Pinpoint追踪从用户请求到SQL执行的完整链路快速定位瓶颈。SQL审核与上线前优化强制SQL审核所有上线的SQL必须经过EXPLAIN审核禁止出现全表扫描(typeALL)和低效的Using filesort/Using temporary。建立慢查询档案对核心业务的SQL进行性能基线管理记录其历史执行时间、扫描行数。当出现偏离基线时自动告警。定期健康检查与优化定期更新统计信息对于核心且数据变化频繁的表在业务低峰期定期执行ANALYZE TABLE。定期Review索引清理无效、重复的索引。为新上线的业务查询设计合适的覆盖索引。进行压力测试在大促或业务增长前对数据库进行压测提前发现潜在性能瓶颈。开发规范与意识避免在WHERE子句中对字段进行函数操作。注意隐式类型转换的风险。大数据量排序/分组优先考虑在数据库层面用索引解决而非拉到应用层处理。写操作UPDATE/DELETE务必带上WHERE条件并利用索引避免锁全表。回到开头的面试题一个完整的回答思路应该是先全局监控确认影响范围再排除服务器资源瓶颈接着深入数据库内部查看活跃会话和锁信息最后通过对比分析执行计划的变迁锁定统计信息不准、索引失效或数据量突变等具体原因并给出应急和根治方案。这套方法的价值在于其系统性和可复用性。它训练的不是死记硬背命令而是一种在压力下依然保持清晰逻辑的排查思维。掌握它你不仅能应对面试更能从容处理未来无数个真实的“惊心动魄”的线上时刻。
