MySQL占用大量内存与CPU如何处理

MySQL 内存与 CPU 占用过高的综合处理方案

一、内存占用过高处理

1. ‌优化查询语句与索引‌

   • 检查全表扫描的慢查询，通过 EXPLAIN 分析执行计划，确保 WHERE、JOIN 等关键字段有索引。
   • 避免 SELECT *，仅查询必要字段，减少内存缓冲区的数据加载压力。
   • 使用 OPTIMIZE TABLE 定期整理表碎片，释放未利用的存储空间。

2. ‌调整缓冲区参数‌

   • 设置 innodb_buffer_pool_size 为物理内存的 ‌60-80%‌（例如 64G 内存建议设为 40-50G），避免频繁磁盘 I/O。
   • 控制 max_connections 防止过多连接占用内存，并启用 thread_cache_size 复用线程资源。

3. ‌控制数据规模‌

   • 对历史数据归档或分库分表，减少单表数据量（建议单表不超过 5000 万行）。
   • 启用分区表（Partitioning）分散数据存储压力。

二、CPU 占用过高处理

1. ‌定位高负载 SQL‌

   • 通过 SHOW FULL PROCESSLIST 查看活跃线程，识别长期处于 Sending data 或 Sorting result 状态的查询。
   • 开启慢查询日志（slow_query_log），捕获执行时间超过 ‌1 秒‌的 SQL。

2. ‌解决锁竞争问题‌

   • 使用 SHOW OPEN TABLES WHERE In_use > 0 检查表锁，SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS 分析行锁冲突。
   • 优化事务粒度，避免长事务持有锁过久。

3. ‌参数调优与资源限制‌

   • 降低 innodb_thread_concurrency 控制并发线程数，防止过多线程争用 CPU。
   • 设置 query_cache_type=OFF 关闭查询缓存（适用于频繁写入场景）。

三、系统级排查步骤

1. ‌快速定位进程与线程‌

   • 执行 top → top -Hp [pid] 找到高 CPU 占用的 MySQL 线程，通过 printf "%x\n" [tid] 转换线程 ID。
   • 使用 jstack [pid] | grep -A 20 [nid] 查看线程堆栈，识别 GC 频繁或死循环问题。

2. ‌监控关键指标‌

   • 通过 jstat -gcutil [pid] 1000 观察 Full GC 频率，若 FGC 持续增长需优化 JVM 内存或升级硬件。
   • 使用 vmstat 1 分析系统上下文切换（cs）和 CPU 空闲率（id）。

四、高级解决方案

1. ‌架构优化‌

   • 引入读写分离（如 MySQL 主从复制），将复杂查询分流到只读节点。
   • 使用 Redis 或 Memcached 缓存热点数据，降低数据库直接访问压力。

2. ‌集群与分片‌

   • 对超大规模数据采用分片集群（如 TiDB、Vitess），实现横向扩展。
   • 通过 ProxySQL 或 MyCat 实现 SQL 路由与负载均衡。

优先级建议

1. ‌紧急处理‌：通过 kill [query_id] 终止高负载查询临时缓解压力。
2. ‌中长期优化‌：建立慢查询监控体系，定期执行 ANALYZE TABLE 更新统计信息。
3. ‌硬件升级‌：若数据量持续增长，建议升级内存至 ‌128G 以上‌并采用 NVMe SSD 存储