数据库性能排查五步法：从慢查询到系统资源优化

1. 数据库性能排查的黄金五步法当线上数据库出现性能问题时很多DBA会陷入手忙脚乱的状态。根据我多年处理生产环境数据库性能问题的经验建议按照以下五个关键检查点进行系统性排查。这套方法在MySQL、Oracle等主流关系型数据库中普遍适用能快速定位80%以上的性能瓶颈。重要提示性能排查一定要有方法论避免无头苍蝇式的检查。以下顺序是根据问题出现概率和排查效率优化的结果。1.1 第一步检查慢查询日志慢查询日志是数据库性能问题的第一现场证据。以MySQL为例通过以下配置开启慢查询监控-- 查看当前慢查询配置 SHOW VARIABLES LIKE slow_query%; SHOW VARIABLES LIKE long_query_time; -- 临时设置慢查询阈值(单位秒) SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log ON;关键分析要点重点关注执行时间超过阈值的TOP 10查询检查出现频率高的重复查询模式注意没有使用索引的查询rows_examined远大于rows_sent典型问题特征# Query_time: 5.123456 Lock_time: 0.000123 Rows_sent: 2 Rows_examined: 500000 SELECT * FROM orders WHERE status pending AND create_time 2023-01-01;这个查询扫描了50万行却只返回2条数据明显存在索引缺失问题。1.2 第二步EXPLAIN分析执行计划对发现的慢SQL必须使用EXPLAIN进行执行计划分析EXPLAIN SELECT * FROM users WHERE username LIKE john% AND age 25;需要重点关注的字段字段正常值异常值问题原因typeconst/ref/rangeALL全表扫描key索引名NULL未使用索引rows小数大数扫描行数过多ExtraUsing indexUsing filesort需要优化排序常见问题处理出现Using temporary查询需要优化临时表使用Using filesort需要添加合适的索引优化排序Select tables optimized away这是理想状态1.3 第三步索引有效性检查索引是数据库性能的核心。检查索引问题需要多维度验证索引缺失检查-- 查找WHERE条件中常用但未索引的列 SELECT * FROM sys.schema_unused_indexes WHERE object_schema your_db; -- 查找高选择性的未索引列 SELECT column_name, count(*) as cnt FROM table_name GROUP BY column_name ORDER BY cnt DESC LIMIT 10;索引冗余检查-- 查找重复或冗余索引 SELECT * FROM sys.schema_redundant_indexes;索引使用统计-- 查看索引使用频率 SELECT * FROM sys.schema_index_statistics WHERE table_schema your_db;索引优化经验法则为高频查询条件创建复合索引遵循最左前缀原则设计索引避免在索引列上使用函数区分度低的列不适合单独建索引1.4 第四步系统资源监控当SQL本身没问题时需要检查系统资源状况数据库连接数SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections;缓冲池使用率-- InnoDB缓冲池命中率 SELECT (1 - (SELECT variable_value FROM performance_schema.global_status WHERE variable_name Innodb_buffer_pool_reads) / (SELECT variable_value FROM performance_schema.global_status WHERE variable_name Innodb_buffer_pool_read_requests)) * 100 AS buffer_pool_hit_ratio;锁等待情况-- 查看当前锁等待 SELECT * FROM sys.innodb_lock_waits; -- 长事务检查 SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) 60;关键阈值参考连接数使用率 70% 需要预警缓冲池命中率 95% 需要优化锁等待时间 500ms 需要关注1.5 第五步硬件I/O性能检查最后需要排除硬件层面的瓶颈磁盘I/O延迟# Linux下检查磁盘延迟 iostat -dx 1关注await列正常应10msSWAP使用情况free -h vmstat 1swap使用率0说明内存不足网络延迟ping -c 5 database_host traceroute database_host数据库网络延迟应1ms2. 典型性能问题处理实录2.1 案例一索引失效导致查询变慢问题现象 用户报告订单查询接口响应时间从200ms突增到5s排查过程从慢日志发现大量类似查询SELECT * FROM orders WHERE user_id 123 AND status completed ORDER BY create_time DESC LIMIT 10;EXPLAIN显示全表扫描type: ALL key: NULL rows: 500000 Extra: Using filesort检查现有索引SHOW INDEX FROM orders; -- 发现只有单独的user_id索引和status索引解决方案 创建复合索引ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);效果验证 执行计划变为type: ref key: idx_user_status_time rows: 15 Extra: Backward index scan查询时间恢复至50ms左右2.2 案例二连接池耗尽导致服务不可用问题现象 应用频繁报Too many connections错误排查过程检查连接数SHOW STATUS LIKE Threads_connected; -- 显示400/400查看连接来源SELECT user, host, db, command, time FROM information_schema.processlist;发现大量sleep状态的连接| app_user | 10.0.0.% | orders_db | Sleep | 500 |问题原因 应用未正确关闭数据库连接连接池配置过大导致耗尽解决方案优化应用连接管理设置连接超时SET GLOBAL wait_timeout 60; SET GLOBAL interactive_timeout 60;使用连接池中间件3. 性能优化工具箱3.1 必备监控命令命令用途关键指标SHOW ENGINE INNODB STATUSInnoDB状态锁等待、死锁SHOW PROCESSLIST当前会话长事务、阻塞操作SHOW GLOBAL STATUS全局状态QPS、TPS、缓存命中率SHOW GLOBAL VARIABLES系统变量配置参数检查3.2 常用性能分析工具pt-query-digest# 分析慢查询日志 pt-query-digest /var/log/mysql/mysql-slow.logsys schema-- 查看未使用索引 SELECT * FROM sys.schema_unused_indexes; -- 查看冗余索引 SELECT * FROM sys.schema_redundant_indexes;Percona Toolkitpt-index-usage索引使用分析pt-visual-explain可视化执行计划4. 预防性维护建议4.1 日常监控项关键指标监控QPS/TPS波动慢查询数量变化连接数使用率缓冲池命中率定期健康检查-- 每周执行一次 ANALYZE TABLE important_table; OPTIMIZE TABLE fragmented_table;4.2 容量规划要点磁盘空间监控数据文件增长趋势日志文件轮转情况性能基准测试业务高峰期前进行压力测试比较版本升级前后的性能差异我在实际运维中发现很多性能问题都是日积月累的小问题爆发的。建议建立定期检查机制在问题影响用户前就发现并解决。对于核心业务表最好在开发阶段就进行索引设计和SQL评审这比事后优化要高效得多。