如何定位 Mysql 负载高

当 MySQL 服务器负载较高时，定位问题的核心是找出影响性能的瓶颈。以下是系统化的排查步骤和工具，帮助你快速定位和解决 MySQL 的高负载问题。

---

1. 检查系统资源

1.1. 检查服务器总体负载

• 使用 top 或 htop 查看整体 CPU、内存使用情况：

  top
  

  • CPU：观察 MySQL 是否占用了大量的 CPU。
  • 内存：是否存在内存不足或 MySQL 使用过多内存导致的交换（swap）。

• 使用 iostat 或 vmstat 检查 I/O 瓶颈：

  iostat -x 1
  

  • 看看磁盘的 await 是否较高（>10ms 表示可能有 I/O 问题）。
  • %util 是否接近 100%。

1.2. 网络负载

• 使用 sar 或 ifstat 检查网络流量：

  sar -n DEV 1
  

  • 如果网络流量异常大，可能是由于大批量的查询或备份任务。

---

2. 分析 MySQL 的当前状态

2.1. 查看 MySQL 当前连接数

• 检查活跃连接和等待连接数：

  SHOW PROCESSLIST;
  

  • 关键字段：
    • Command：显示线程状态，Query 和 Sleep 过多可能导致资源浪费。
    • Time：长时间运行的查询可能是瓶颈。
    • State：如 Copying to tmp table、Waiting for table metadata lock 等可能是问题的根源。

2.2. 使用 SHOW STATUS 查看关键指标

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

• 关键指标：
  • Threads_running：活跃线程数，持续高于 10 表示有压力。
  • Threads_connected：当前连接数。
  • Slow_queries：慢查询数量。

2.3. 检查锁等待

• 查看锁争用：

  SHOW ENGINE INNODB STATUS\G;
  

  • TRANSACTIONS 部分显示是否有大量锁等待。
  • LATEST DETECTED DEADLOCK 检查是否存在死锁。

2.4. 分析慢查询日志

• 确保开启慢查询日志：

  SET GLOBAL slow_query_log = 1;
  SHOW VARIABLES LIKE 'slow_query_log_file';
  

  • 查看运行时间最长的查询。

2.5. 查询性能概要

• 使用 EXPLAIN 分析慢查询：

  EXPLAIN SELECT ...;
  

  • 观察 type 字段：如 ALL 表示全表扫描。
  • 检查 rows 字段：大于预期表明查询需要优化。

---

3. 使用工具深入分析

3.1. 使用 mysqladmin

• 快速查看关键指标：

  mysqladmin -u root -p status
  

3.2. 使用 pt-query-digest

• 分析慢查询日志：

  pt-query-digest /path/to/slow_query.log
  

  • 识别耗时最高的 SQL 查询。

3.3. 使用 Performance Schema

• 开启 Performance Schema：

  SHOW VARIABLES LIKE 'performance_schema';
  

• 查询热点：

  SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT
  FROM performance_schema.events_waits_summary_global_by_event_name
  ORDER BY SUM_TIMER_WAIT DESC;
  

3.4. 使用 MySQLTuner

• 下载并运行 MySQLTuner 来检查数据库配置是否需要优化：

  wget http://mysqltuner.pl -O mysqltuner.pl
  perl mysqltuner.pl
  

---

4. 优化建议

4.1. 优化查询

1. 检查索引：

   • 确保所有查询使用适当的索引。
   • 使用 SHOW INDEX FROM table_name 检查表索引。

2. 优化慢查询：

   • 避免全表扫描，添加必要的索引。
   • 使用分页查询时避免 OFFSET 太大。

4.2. 调整配置

1. 调整最大连接数：

   SET GLOBAL max_connections = 500;
   

2. 调整缓冲区：

   • 增大 innodb_buffer_pool_size（一般设置为内存的 70%-80%）：

     SET GLOBAL innodb_buffer_pool_size = 4G;
     

3. 配置查询缓存：

   • 如果大量重复查询，开启查询缓存：

     SET GLOBAL query_cache_size = 128M;
     

4.3. 减轻写入压力

1. 批量插入：
   • 合并小批量插入为大批量事务。
2. 异步操作：
   • 使用队列（如 Redis）暂存高频写入操作。

4.4. 分库分表

• 数据量过大时，考虑使用分库分表或分区表。

---

5. 实战场景分析

场景 1：CPU 使用率高

• 检查是否有大量复杂查询或排序。
• 优化慢查询，添加索引。
• 增加 CPU 或分布式部署。

场景 2：磁盘 I/O 高

• 增加 innodb_buffer_pool_size。
• 检查是否有大量的临时表操作：

  SHOW GLOBAL STATUS LIKE 'Created_tmp%';
  

• 减少磁盘写入操作，优化大表。

场景 3：连接数过多

• 检查是否有未正确关闭的连接。
• 使用连接池管理连接。
• 增加 max_connections，但需结合硬件资源。

---

总结

MySQL 高负载通常是由查询优化、索引、配置或资源限制引起的。通过系统排查（资源监控 + SQL 分析 + 配置优化），可以快速找到瓶颈并加以解决。