30款热门AI模型一站整合DeepSeek/GLM/Qwen 随心用限时 5 折。 点击领海量免费额度这次我们来看一个经典的数据库性能排查问题一条SQL昨天还跑50毫秒今天突然飙升到5秒连带CPU飙到90%该怎么处理这不是一个具体的开源项目而是一个在面试和实际运维中高频出现的实战场景。它的核心不是工具本身而是一套可复用的排查思路和工具箱。如果你关心数据库性能、线上问题定位、SQL调优和应急响应这篇文章可以直接收藏。最值得关注的不是某个单一命令而是从现象到根因的完整链路如何快速止血降低CPU如何定位问题SQL如何分析执行计划突变以及如何验证和解决。整个过程需要在分钟级内做出判断对数据库基础知识、监控工具熟悉度和排查经验都有要求。本文会带你走完一次完整的线上SQL性能劣化排查。我们将从监控告警切入演示如何使用系统命令、数据库内置视图和性能工具一步步锁定问题并给出常见的解决思路。无论你用的是MySQL、PostgreSQL还是其他关系型数据库这套方法论的核心是相通的。1. 核心能力速览问题定位工具箱面对“SQL突然变慢CPU飙升”的问题我们依赖的不是一个万能脚本而是一组分层、联动的工具和命令。下表梳理了核心的排查能力与对应工具。能力项说明与常用工具现象确认与监控首先确认问题范围是单实例问题还是全局问题使用top,htop,vmstat,dstat观察系统整体CPU、IO、负载。通过数据库连接池监控或数据库自身状态如SHOW PROCESSLIST确认是否由数据库引起。问题SQL定位找到消耗资源的罪魁祸首。工具包括•慢查询日志需提前开启记录超过阈值的SQL。•information_schema.PROCESSLIST/SHOW FULL PROCESSLIST实时查看当前执行的SQL。•performance_schema/sysschema(MySQL)提供更细粒度的历史性能数据。•pg_stat_activity(PostgreSQL)查看当前活动会话和查询。执行计划分析分析SQL为什么变慢。核心命令是EXPLAIN(或EXPLAIN ANALYZE)。对比昨天和今天的执行计划查看是否发生了全表扫描、索引失效、错误的连接顺序或类型转换。资源与锁分析检查是否因资源竞争导致。查看•锁等待SHOW ENGINE INNODB STATUS(MySQL InnoDB),pg_locks(PostgreSQL)。•缓冲池命中率SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’。•临时表与磁盘IO检查是否在磁盘上创建了临时表或进行了大量排序。外部因素排查检查数据库之外的变化•数据量突变是否有人批量导入/删除数据•统计信息过时自动更新是否失败导致优化器误判•硬件/系统层磁盘IO延迟是否暴增云平台底层资源是否被抢占解决与验证根据根因采取措施刷新统计信息、优化索引、调整查询、终止会话、扩容等。并通过回放或压力测试验证效果。2. 适用场景与使用边界这套排查方法主要适用于以下场景和角色适用场景线上应急响应数据库监控告警CPU、慢查询、连接数突增需要快速定位原因并恢复服务。版本/数据变更后验证在发布新代码、更新数据库索引、迁移数据后验证核心SQL性能是否符合预期。周期性性能分析对系统进行定期健康检查提前发现潜在的性能退化风险。容量规划与优化通过分析历史性能数据为数据库扩容或架构调整提供依据。适合人员DBA数据库管理员日常运维和深度优化的核心技能。后端开发工程师需要对自己编写的SQL负责具备初步的排查和优化能力。SRE/运维工程师在负责系统稳定性的过程中需要介入数据库层的问题定位。使用边界与注意事项对业务的影响某些诊断命令如获取锁信息、执行EXPLAIN ANALYZE实际运行查询可能对繁忙的数据库产生额外负载应在业务低峰期或从库执行。权限要求执行这些命令通常需要较高的数据库权限如PROCESS,SUPER需遵循权限最小化原则。数据安全排查过程中可能看到SQL原文和部分业务数据必须严格遵守数据安全规范不得泄露。根因复杂性性能问题可能是多因素叠加结果如锁过时统计信息慢磁盘需要综合判断避免片面下结论。3. 环境准备与前置条件在开始排查前请确保你具备以下环境和权限。这不是一个需要安装的软件而是对操作环境的基本要求。操作系统访问权限你需要能通过SSH登录到数据库服务器或至少拥有运行系统级监控命令如top,iostat的权限。数据库连接权限需要一个具有足够权限的数据库账号。通常需要以下权限PROCESS: 查看所有会话。SELECT: 查询information_schema,performance_schema,sys等系统表。SUPER(或类似权限): 在某些情况下用于终止会话或设置全局参数。监控与日志慢查询日志确认是否已开启。这是事后分析的宝贵资料。开启方法通常为设置slow_query_logON并配置long_query_time。基础监控确保有基础的服务器CPU、内存、磁盘IO、网络和数据库QPS、连接数、缓冲池命中率监控。Zabbix, Prometheus, 云平台监控等均可。知识准备对目标数据库的基本架构如MySQL的InnoDB引擎、索引原理、事务隔离级别和锁机制有基本了解。4. 标准排查流程与操作步骤下面我们按照从外到内、从现象到根因的顺序拆解完整的排查流程。你可以将其视为一个检查清单。4.1 第一步确认现象与范围接到告警后首先确认问题是否确实由数据库引起以及影响范围。操作步骤登录服务器使用top或htop命令。观察%Cpu(s)一行如果us(用户态) 或sy(内核态) 异常高且PID靠前的进程是数据库进程如mysqld,postgres则初步判断是数据库问题。top -c快速查看数据库整体状态。连接数据库执行-- MySQL SHOW GLOBAL STATUS LIKE ‘Threads_connected’; -- 查看连接数 SHOW GLOBAL STATUS LIKE ‘Questions’; -- 查看查询总数 SHOW ENGINE INNODB STATUS\G -- 查看InnoDB引擎状态关注SEMAPHORES部分 -- PostgreSQL SELECT count(*) FROM pg_stat_activity WHERE state ‘active’; -- 活跃连接数 SELECT datname, usename, application_name, client_addr, state FROM pg_stat_activity; -- 查看所有会话判断是全局问题还是局部问题如果所有或大部分业务接口都变慢可能是全局性问题如锁、资源瓶颈。如果只是个别功能变慢则更可能指向特定的SQL或表。4.2 第二步定位问题SQL这是最关键的一步找到正在消耗大量CPU资源的SQL语句。操作步骤查看当前正在执行的会话-- MySQL SHOW FULL PROCESSLIST; -- 或者使用 information_schema SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.PROCESSLIST WHERE COMMAND ! ‘Sleep’ AND TIME 2 -- 查询执行时间超过2秒的 ORDER BY TIME DESC; -- PostgreSQL SELECT pid, usename, application_name, client_addr, backend_start, state, query FROM pg_stat_activity WHERE state ‘active’ AND query NOT LIKE ‘%pg_stat_activity%’ -- 排除这个查询自身 ORDER BY backend_start;重点关注TIME时间长、STATE为Sending data,Sorting result,Creating tmp table的会话其INFO或query字段就是正在执行的SQL。查询历史慢SQL如果慢查询日志已开启# MySQL 慢日志通常位于 /var/lib/mysql/ 或数据目录下文件名如 -slow.log # 使用 mysqldumpslow 工具分析 mysqldumpslow -s t /var/lib/mysql/hostname-slow.log | head -20 # -s t 表示按总时间排序可以快速看到最耗时的查询对于PostgreSQL需要配置log_min_duration_statement并查看日志文件。使用性能库如果可用-- MySQL 5.7使用 sys 库更友好 SELECT * FROM sys.session WHERE command ! ‘Sleep’ ORDER BY time DESC LIMIT 10; -- 或者查看哪些SQL消耗了最多的时间 SELECT query, db, exec_count, total_latency, avg_latency FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 5;4.3 第三步分析SQL执行计划找到可疑SQL后立即分析其执行计划。核心是对比如果可能获取昨天正常时的执行计划和今天的执行计划进行对比。操作步骤在测试环境或从库上运行EXPLAIN命令。切勿直接在压力巨大的主库上运行EXPLAIN ANALYZE它会实际执行查询。-- MySQL EXPLAIN FORMATJSON SELECT * FROM your_table WHERE your_column ‘value’; -- FORMATJSON 会输出更详细的信息 -- 或者使用传统格式 EXPLAIN SELECT * FROM your_table WHERE your_column ‘value’; -- PostgreSQL EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE your_column ‘value’; -- ANALYZE 会实际执行并给出真实耗时BUFFERS 显示缓存命中情况解读执行计划的关键点type/access_type (MySQL) 或 Scan Type (PgSQL)这是最重要的列之一。按性能从优到劣大致为systemconsteq_refrefrangeindexALL。如果看到ALL全表扫描就是重大嫌疑。possible_keys key (MySQL)possible_keys显示可能用到的索引key显示实际用到的索引。如果key为NULL说明没用到索引。rows预估需要扫描的行数。如果这个数字巨大比如上百万而实际表很小或查询条件应该过滤掉大部分数据说明统计信息可能不准。Extra包含额外信息。需要警惕的有Using filesort: 表示需要额外的排序步骤可能消耗大量内存和CPU。Using temporary: 表示需要创建临时表如果临时表在磁盘上Using temporary; Using filesort性能会急剧下降。Using where: 表示在存储引擎层之后进行了过滤。4.4 第四步深入检查相关因素如果执行计划显示异常如索引失效需要深入检查几个常见原因。操作步骤检查索引状态确认索引是否存在、是否损坏。-- MySQL 查看表索引 SHOW INDEX FROM your_table; -- 重点关注 Cardinality基数它表示索引列上不同值的数量估计。如果这个值非常小相比表行数优化器可能认为索引效率低而不使用它。检查表统计信息统计信息过时是导致执行计划突变的常见原因。-- MySQL 手动更新统计信息 ANALYZE TABLE your_table; -- 对于InnoDB也可以设置 innodb_stats_persistent 和 innodb_stats_auto_recalc 来控制自动更新。检查锁竞争是否有其他事务锁住了关键资源导致本查询等待-- MySQL 查看当前锁信息需要开启InnoDB监控 SHOW ENGINE INNODB STATUS\G -- 查看 TRANSACTIONS 和 LATEST DETECTED DEADLOCK 部分。 -- 也可以查询 information_schema SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- PostgreSQL SELECT * FROM pg_locks WHERE granted false; -- 查看未授予的锁即等待中的锁检查系统资源使用iostat -x 1查看磁盘IO利用率%util和响应时间await。如果磁盘IO成为瓶颈所有需要读盘的查询都会变慢。4.5 第五步验证与解决根据以上分析形成假设并验证。常见根因与解决方案根因现象线索验证与解决方案统计信息过时执行计划预估行数 (rows) 与实际严重不符昨天用索引A今天全表扫描。执行ANALYZE TABLE。然后重新EXPLAIN观察执行计划是否恢复正常。索引失效/未使用EXPLAIN结果中key为NULLtype为ALL。检查查询条件字段是否有索引。检查是否发生了隐式类型转换如字符串字段用数字查询。检查是否在索引列上使用了函数或运算。锁竞争查询状态长时间为Waiting for table metadata lock或Lock wait timeout exceeded。根据SHOW ENGINE INNODB STATUS或pg_locks找到阻塞者评估后决定等待或终止阻塞会话 (KILL [session_id])。数据量突变查询条件筛选出的数据量远大于以往。检查业务逻辑是否有批量任务导入了大量无效数据是否删除了大量数据导致索引选择性变化资源瓶颈系统监控显示磁盘IO或CPU持续100%。扩容硬件资源或优化查询减少资源消耗如避免SELECT *增加索引减少IO。查询本身变更SQL文本发生了细微变化如多了个空格注释改变虽然罕见。对比代码版本确认SQL是否被意外修改。验证方法在解决问题后如刷新统计信息、优化索引在测试环境或业务低峰期重新执行该SQL并观察执行时间和资源消耗。同时持续监控线上数据库相关指标是否回落。5. 功能测试与效果验证模拟一个排查案例为了更直观地理解整个流程我们模拟一个简单的测试案例。测试目的验证当表统计信息过时导致索引失效时如何通过上述流程定位并解决问题。前置条件一个测试数据库。一张有索引的表user(id主键name上有普通索引)。初始时数据量较小。操作步骤制造问题向user表灌入远超平时数量级的数据例如从1万行增加到500万行。注意在MySQL的InnoDB引擎下大量数据变更后统计信息可能不会立即更新取决于innodb_stats_auto_recalc设置。执行测试查询SELECT * FROM user WHERE name ‘test_user_12345’;假设name‘test_user_12345’的记录只有一条。定位与排查通过SHOW PROCESSLIST发现该查询执行很慢。使用EXPLAIN分析EXPLAIN SELECT * FROM user WHERE name ‘test_user_12345’\G可能的结果type为ALL(全表扫描)key为NULL。因为优化器根据过时的统计信息可能还显示基数很小认为全表扫描比走索引更快。解决问题ANALYZE TABLE user;再次执行EXPLAIN观察type是否变为refkey是否显示为name索引预估rows是否变为1。效果验证重新执行原查询速度应恢复正常。判断成功的标准EXPLAIN的输出显示查询正确使用了索引且实际执行时间回归到毫秒级。6. 接口与批量场景下的考量如果问题SQL来自某个API接口或批量任务排查时还需注意接口流量突增是否因为活动推广导致调用该接口的QPS暴涨从而拖慢数据库需要结合应用层监控如QPS、响应时间一起看。批量任务逻辑变化批量任务是否修改了循环逻辑导致对同一条SQL的调用次数呈指数级增长连接池配置应用连接池配置是否合理是否存在连接泄漏导致数据库连接数耗尽引发连锁反应N1查询问题在ORM如Hibernate, MyBatis中常见的性能反模式。表现为一个列表查询会触发大量额外的单条查询。需要通过分析应用日志或使用数据库的审计功能来发现。7. 资源占用与性能观察方法论在整个排查过程中对资源占用的观察是持续进行的。CPU占用观察持续使用top或htop。区分是数据库进程 (mysqld) 的us(用户态) CPU高还是sy(系统态) CPU高。us高通常意味着SQL计算本身复杂sy高可能意味着系统调用频繁可能与锁或IO有关。内存与Swap使用free -h和vmstat 1观察内存使用和Swap交换情况。如果数据库大量使用Swap性能会雪崩。磁盘IO观察使用iostat -x 1。关注%util(利用率) 和await(平均等待时间)。如果await远高于正常值如从几ms升到几百ms说明磁盘响应慢。数据库内部指标缓冲池命中率对于MySQL InnoDB缓冲池命中率低会导致大量物理读。计算(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%。理想情况应大于99%。临时表创建SHOW GLOBAL STATUS LIKE ‘Created_tmp%tables’;如果Created_tmp_disk_tables数量激增说明很多排序或分组操作无法在内存完成拖慢性能。8. 常见问题与排查方法速查表问题现象可能原因排查方式解决方案SQL突然从毫秒级变秒级1. 统计信息过时2. 索引失效隐式转换、函数3. 数据量突变1.EXPLAIN对比计划2. 检查SHOW INDEX的基数3. 检查表数据量变化1.ANALYZE TABLE2. 改写SQL避免索引列运算3. 优化业务逻辑数据库CPU持续90%但慢查询日志无记录1. 大量短时查询QPS高2. 并发锁竞争3. 全表扫描但未超慢查询阈值1.SHOW PROCESSLIST看并发数与SQL2.SHOW ENGINE INNODB STATUS看锁3. 降低long_query_time阈值1. 优化应用减少无效查询2. 处理锁问题3. 调整慢查询阈值或开启全量日志谨慎SHOW PROCESSLIST看到大量Waiting for table metadata lock1. 有未提交的长事务持有元数据锁2. 有DDL操作如加索引被阻塞1. 查找持有锁的会话MySQL 5.7performance_schema.metadata_locks2. 检查是否有ALTER TABLE在执行1. 提交或终止长事务2. 在业务低峰期执行DDL执行计划显示用对了索引但还是很慢1. 索引选择性差重复值多2. 回表代价高查询需要大量非索引列3. 索引碎片化严重1. 计算索引列不同值比例2.EXPLAIN看rows和filtered3. 检查索引大小和碎片1. 考虑使用复合索引覆盖查询2. 优化查询只取必要字段3. 重建索引 (OPTIMIZE TABLE或ALTER TABLE ... ENGINEINNODB)磁盘IO等待高 (await飙升)1. 大量物理读缓冲池命中率低2. 临时表或排序写到磁盘3. 磁盘本身性能问题1. 计算缓冲池命中率2. 查看Created_tmp_disk_tables3. 使用fio等工具测试磁盘1. 增加innodb_buffer_pool_size2. 优化SQL减少磁盘临时表3. 联系运维检查硬件/云盘9. 最佳实践与使用建议将排查经验固化为日常习惯能有效预防和快速解决类似问题。监控告警先行务必配置完善的监控。核心指标包括数据库服务器CPU/内存/磁盘IO、数据库连接数、QPS/TPS、慢查询数量、缓冲池命中率、锁等待数量。设置合理的告警阈值。慢查询日志常态化始终开启慢查询日志并定期分析如每天一次。使用pt-query-digest(Percona Toolkit) 或mysqldumpslow进行聚合分析找出“最慢”和“最常出现”的慢查询进行优化。变更管理任何可能影响数据库的变更如代码发布、索引变更、数据迁移、服务器维护都应在低峰期进行并做好回滚预案。变更后主动观察核心业务SQL的性能指标。建立性能基线记录核心业务SQL在正常状态下的执行时间、执行计划、扫描行数等。当性能劣化时可以快速对比。使用执行计划绑定对于极其重要且执行计划必须稳定的SQL可以考虑使用执行计划绑定如MySQL 8.0的Optimizer Hints或SQL Plan Management防止优化器“抽风”。测试环境复现尽量在测试环境复现问题。可以通过备份生产数据到测试库或使用流量回放工具在隔离环境中进行安全的分析和验证。文档与复盘每次处理完线上性能问题后进行简短的复盘记录根本原因、处理步骤和后续预防措施形成团队知识库。面对“SQL昨天快今天慢CPU飙升”的紧急状况一套清晰、有序的排查思路远比记住几个命令更重要。核心路径是监控告警 - 系统定位 - 定位问题SQL - 分析执行计划 - 检查索引/统计信息/锁 - 验证解决。最先应该验证的往往是执行计划是否突变而统计信息过时和索引失效是最常见的两个“元凶”。最容易踩的坑是在高压主库上直接运行重型诊断命令可能加剧问题。掌握这套方法不仅能应对面试更能实实在在地保障线上系统的稳定。建议你将本文中的命令和排查清单保存下来下次遇到类似问题时按图索骥快速定位。 30款热门AI模型一站整合DeepSeek/GLM/Qwen 随心用限时 5 折。 点击领海量免费额度