MySQL 线上 SQL 性能问题定位方法

当线上 SQL 执行变慢时，可以通过以下系统化的方法来定位问题：

1. 基础监控检查

• 系统资源：

  • top/htop 查看 CPU、内存、I/O 使用率
  • vmstat 1 查看系统整体负载
  • iostat -x 1 查看磁盘 I/O 情况

• MySQL 全局状态：

  SHOW GLOBAL STATUS LIKE 'Threads_running';  -- 当前运行线程数
  SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';  -- 行锁情况
  SHOW ENGINE INNODB STATUS;  -- 完整 InnoDB 状态
  

2. 慢查询分析

• 开启慢查询日志：

  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 1;  -- 设置慢查询阈值(秒)
  SET GLOBAL log_queries_not_using_indexes = 'ON';
  

• 分析慢日志：

  mysqldumpslow -s t /var/lib/mysql/mysql-slow.log  # 按时间排序
  pt-query-digest /var/lib/mysql/mysql-slow.log     # Percona 工具更详细分析
  

3. 实时问题查询分析

• 查看当前运行查询：

  SHOW FULL PROCESSLIST;  -- 查看所有连接
  SELECT * FROM information_schema.processlist WHERE COMMAND != 'Sleep';
  

• 使用性能分析工具：

  EXPLAIN [EXTENDED] SELECT ...;  -- 查看执行计划
  EXPLAIN ANALYZE SELECT ...;     -- MySQL 8.0+ 实际执行分析
  

4. 常见问题点检查

• 索引问题：

  • 缺失索引：EXPLAIN 中 type=ALL 表示全表扫描
  • 索引失效：隐式类型转换、函数操作列等

• 锁问题：

  • 行锁等待：SHOW ENGINE INNODB STATUS 查看 LATEST DETECTED DEADLOCK
  • 表锁：SHOW OPEN TABLES WHERE In_use > 0

• 配置问题：

  • SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; 缓冲池是否足够
  • SHOW VARIABLES LIKE 'tmp_table_size'; 临时表大小

5. 高级诊断工具

• Performance Schema：

  -- 查看等待事件
  SELECT * FROM performance_schema.events_waits_history_long;
  
  -- 查看最耗资源的SQL
  SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;
  

• sys 库（MySQL 5.7+）：

  -- 查看未使用索引
  SELECT * FROM sys.schema_unused_indexes;
  
  -- 查看全表扫描
  SELECT * FROM sys.statements_with_full_table_scans;
  

6. 应急处理

对于严重影响系统的慢查询：

-- 终止问题查询
KILL [CONNECTION|QUERY] process_id;

优化建议

1. 对高频慢查询优先优化
2. 考虑增加合适的索引
3. 检查表结构和数据类型是否合理
4. 对于复杂查询考虑拆解或重写
5. 定期进行数据库维护（ANALYZE TABLE, OPTIMIZE TABLE）

通过以上方法可以系统性地定位并解决大多数 SQL 性能问题。对于复杂场景，可能需要结合多种工具和方法进行深入分析。