数据库性能排查五步法：从慢查询到索引优化

1. 数据库性能排查的黄金五步法当数据库响应变慢时我通常会像老中医把脉一样按照以下五个关键部位进行快速诊断。这套方法在MySQL、Oracle等关系型数据库中普遍适用能解决80%以上的常见性能问题。重要提示所有诊断操作建议在业务低峰期进行避免影响线上服务。生产环境操作前务必先验证SQL语句影响范围。1.1 第一检查点慢查询日志慢查询日志是数据库自带的黑匣子记录所有执行时间超过阈值的SQL语句。通过分析这些问题SQL往往能直接定位性能瓶颈。启用与配置方法以MySQL为例-- 查看当前慢查询设置 SHOW VARIABLES LIKE %slow_query%; -- 临时开启慢查询日志重启失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 阈值设为1秒 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log; -- 永久生效需修改my.cnf [mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1 -- 记录未走索引的查询日志分析工具推荐mysqldumpslowMySQL自带工具提供基础统计pt-query-digestPercona工具支持多维分析阿里云DAS可视化慢SQL分析平台典型问题SQL特征全表扫描rows_examined远大于rows_sent临时表使用Using temporary文件排序Using filesort嵌套循环连接Using join buffer1.2 第二检查点索引健康度索引相当于数据库的目录失效的索引会导致查询效率断崖式下降。我主要关注以下指标核心检查项-- 查看索引使用情况 SELECT * FROM sys.schema_unused_indexes; -- 查看索引统计信息 SHOW INDEX FROM 表名; -- 计算索引选择性越高越好 SELECT COUNT(DISTINCT 列名)/COUNT(*) FROM 表名;索引失效的六大经典场景隐式类型转换如VARCHAR字段用数字查询索引列使用函数如WHERE DATE(create_time)...前导模糊查询LIKE %关键字%不符合最左前缀原则的联合索引使用OR条件且未全覆盖索引索引列参与计算如WHERE id1100重建索引的正确姿势-- 在线重建不锁表 ALTER TABLE 表名 ALGORITHMINPLACE, LOCKNONE, REBUILD PARTITION ALL; -- 针对InnoDB的优化操作 ANALYZE TABLE 表名; -- 更新统计信息 OPTIMIZE TABLE 表名; -- 重组物理存储1.3 第三检查点连接池状态连接数暴增是导致数据库雪崩的常见原因。我会重点检查关键指标监控-- 查看当前连接数 SHOW STATUS LIKE Threads_connected; -- 查看连接来源分布 SELECT user,host,COUNT(*) FROM information_schema.processlist GROUP BY user,host; -- 查看活跃连接详情 SELECT * FROM information_schema.processlist WHERE COMMAND ! Sleep;连接池优化建议设置合理的最大连接数建议公式CPU核心数*2 磁盘数配置连接超时wait_timeout建议300-600秒使用连接池中间件如HikariCP、Druid实现读写分离降低主库压力突发连接数暴涨处理流程快速止血kill id终止异常连接分析原因检查应用连接泄漏或突发流量长期方案增加连接池监控告警1.4 第四检查点事务与锁争用长事务和锁冲突会形成性能瓶颈链。诊断方法包括锁监控方法-- 查看当前锁等待 SELECT * FROM sys.innodb_lock_waits; -- 查看事务详情 SELECT * FROM information_schema.INNODB_TRX; -- 查看行锁情况 SHOW ENGINE INNODB STATUS;典型问题模式热点行更新导致的锁等待未提交的长事务阻塞DDL操作不合理的隔离级别设置优化建议事务拆解大事务改为小批量提交锁升级避免使用SELECT...FOR UPDATE SKIP LOCKED死锁检测设置innodb_deadlock_detectON1.5 第五检查点服务器资源瓶颈最后要检查数据库所在服务器的生理指标关键资源检查命令# CPU使用率重点关注%sys是否过高 top -H -p $(pgrep -d, mysqld) # 内存使用关注swap是否被使用 free -h # 磁盘I/Oawait10ms说明有问题 iostat -dx 1 # 网络流量关注重传率 sar -n DEV 1常见资源配置误区盲目调大innodb_buffer_pool_size导致OOM未正确设置swappiness引发内存回收问题机械硬盘部署未调整innodb_io_capacity2. 实战案例电商系统慢查询优化全记录去年双十一期间我们某个核心接口响应时间从200ms飙升到5秒。通过上述五步法30分钟内定位并解决了问题。2.1 问题现象订单列表API响应变慢伴随数据库CPU使用率达到90%2.2 排查过程通过慢查询日志发现一条统计SQL平均执行4.8秒SELECT COUNT(*) FROM orders WHERE user_id123 AND status IN (1,2,5) AND create_time 2023-11-01;检查执行计划发现全表扫描id: 1 select_type: SIMPLE table: orders partitions: NULL type: ALL # 全表扫描 possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2,843,211 filtered: 1.23 Extra: Using where分析发现缺失复合索引原有索引是单独的(user_id)和(create_time)2.3 解决方案紧急创建覆盖索引ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);优化后执行计划id: 1 select_type: SIMPLE table: orders partitions: NULL type: range possible_keys: idx_user_status_time key: idx_user_status_time key_len: 9 ref: NULL rows: 127 filtered: 100.00 Extra: Using where; Using index最终效果SQL执行时间降至23msCPU使用率回落至40%3. 高级技巧预防性维护体系除了事后排查我建立了以下预防机制3.1 自动化监控看板慢查询TOP10每分钟刷新连接数变化趋势锁等待时间监控资源使用率热力图3.2 定期健康检查每周自动执行以下检查索引使用率分析表碎片率检查统计信息时效性验证备份恢复测试3.3 性能测试规范所有上线SQL必须通过EXPLAIN验证重大变更前进行基准测试建立SQL审核流程这套方法让我管理的数据库集群连续三年保持99.99%的可用性平均查询响应时间始终控制在200ms以内。最关键的是培养了对数据库性能问题的直觉——通过几个关键指标就能快速定位问题根源。