MySQL故障排查与优化

一、MySQL 故障快速定位5 分钟定位根因1. 先看整体状态最关键sql-- 查看 MySQL 整体运行状态 SHOW STATUS; -- 精简版只看关键指标 SHOW STATUS LIKE %Threads%; SHOW STATUS LIKE %Queries%; SHOW STATUS LIKE %Slow%;重点看 3 个值Threads_connected当前连接数太高 连接爆了Threads_running正在执行的查询数10 基本就堵了Slow_queries慢查询数量0 说明有慢 SQL2. 查看正在执行的 SQL定位卡死 / 慢查询sql-- 查看所有正在跑的查询含执行时间 SHOW FULL PROCESSLIST; -- 更精准只看运行超过 1 秒的活跃 SQL SELECT * FROM information_schema.processlist WHERE time 1 AND command ! Sleep;看到这些直接判定故障Sending dataSQL 没索引全表扫描Copying to tmp table临时表太大内存不够Locked / Waiting for table metadata lock表被锁死Sleep 大量连接代码没关闭连接连接池泄露3. 查看慢查询日志必开sql-- 临时开启慢查询重启失效 SET GLOBAL slow_query_log 1; SET GLOBAL long_query_time 1; -- 超过 1 秒就算慢查询 SET GLOBAL log_queries_not_using_indexes 1; -- 记录没走索引的 SQL慢查询日志是排查所有性能问题的核心。4. 查看锁等待表卡死、更新失败sql-- 查看 InnoDB 锁等待 SELECT * FROM information_schema.innodb_locks; SELECT * FROM information_schema.innodb_lock_waits;常见锁问题长事务不提交 → 锁不释放无索引更新 → 行锁变表锁DDLalter table→ 整表元数据锁死5. 查看主从延迟主从同步故障sqlSHOW SLAVE STATUS\G看两个字段Seconds_Behind_Master延迟秒数Slave_SQL_Running: NoSQL 线程挂了数据冲突 / 权限问题二、MySQL 性能优化从易到难直接照做1. SQL 优化80% 问题都在这① 必做给 SQL 加索引sql-- 查看 SQL 是否走索引 EXPLAIN SELECT * FROM user WHERE phone 13800138000;看到 type ALL 就是全表扫描必须加索引sqlCREATE INDEX idx_phone ON user(phone);② 禁止这些烂 SQLSELECT *LIKE %keyword%左模糊不走索引WHERE 11无索引条件大表JOIN无索引ORDER BY / GROUP BY无索引③ 分页优化sql-- 坏越往后越慢 SELECT * FROM table LIMIT 100000,20; -- 好延迟关联只查主键 SELECT * FROM table t JOIN (SELECT id FROM table ORDER BY id LIMIT 100000,20) AS tmp ON t.id tmp.id;2. 连接数优化连接爆、Too many connectionssql-- 查看最大连接数 SHOW VARIABLES LIKE max_connections; -- 调整my.cnf max_connections 2000 wait_timeout 600 interactive_timeout 600原因代码连接池没释放、Sleep 连接太多。3. InnoDB 缓冲池优化内存优化最核心配置plaintextinnodb_buffer_pool_size 物理内存的 50%~70%32G 内存 → 设 20G64G 内存 → 设 40G作用把热数据放内存磁盘 IO 瞬间下降。4. 临时表 排序优化plaintexttmp_table_size 256M max_heap_table_size 256M sort_buffer_size 2M join_buffer_size 2M避免Using temporary、Using filesort。5. 日志优化减少磁盘 IOplaintextinnodb_flush_log_at_trx_commit 2 主从架构推荐 sync_binlog 1000能大幅降低磁盘 IO提升写入速度。6. 表结构优化用INT不用VARCHAR存数字禁止TEXT/BLOB频繁查询大字段拆表时间用DATETIME或TIMESTAMP尽量用NOT NULL三、最常见 6 大故障 解决方案1. CPU 100%原因无索引 SQL、大量排序、全表扫描解决SHOW FULL PROCESSLIST找到慢 SQLEXPLAIN加索引杀掉烂 SQL2. 连接数爆了原因代码连接泄露、连接池配置错误解决杀掉 Sleep 长连接调整max_connections修复应用连接池3. 磁盘 IO 100%原因缓冲池太小、大量随机 IO、大事务解决调大innodb_buffer_pool_size优化慢 SQL关闭不必要的日志4. 表卡死、更新不动原因长事务、MDL 锁、无索引更新解决查看锁等待杀掉长事务给更新条件加索引5. 主从延迟巨大原因从库单线程回放、大事务、无索引解决开启并行复制拆大事务从库也加索引6. MySQL 突然宕机原因OOM、磁盘满、断电解决看系统日志/var/log/messages看 MySQL 错误日志error.log检查磁盘空间df -h四、企业级巡检命令直接复制用sql-- 1. 查看活跃连接 SHOW FULL PROCESSLIST; -- 2. 查看慢查询数量 SHOW STATUS LIKE Slow_queries; -- 3. 查看 InnoDB 状态 SHOW ENGINE INNODB STATUS; -- 4. 查看缓存命中率95% 正常 SHOW STATUS LIKE Innodb_buffer_pool_read%; -- 5. 查看表锁 contention SHOW STATUS LIKE Table_locks%;五、最佳实践总结慢查询日志必须永久开启所有查询必须走索引禁止长事务5 秒