一、MySQL内存监控的重要性
内存相关问题是MySQL中除锁问题外最为复杂的故障类型之一。与锁问题通常具有明确的等待或死锁信息不同，内存问题往往表现为性能的渐进式下降、OOM（内存耗尽）导致的进程异常终止或系统整体不稳定。构建一套完善的多维度内存监控体系，能够帮助数据库管理员快速定位并诊断问题的根本原因。下文将以MySQL 5.7版本为例展开说明。
二、多维度内存监控视图
2.1 通过sys库进行内存监控
MySQL 5.7的sys库内置了四个核心内存监控视图，分别从不同维度展示内存的分配与使用情况：
sql
查看所有内存监控视图
SELECT table_name, table_comment
FROM information_schema.tables
WHERE table_schema = 'sys'
AND table_name LIKE 'memory%'
ORDER BY table_name;
各视图功能概述：
| 视图名称 | 分组维度 | 主要应用场景 |
| memory_by_host_by_current_bytes | 客户端主机 | 排查连接级内存泄漏 |
| memory_by_thread_by_current_bytes | 线程 | 分析特定会话内存使用 |
| memory_by_user_by_current_bytes | 用户 | 按用户审计内存消耗 |
| memory_global_by_current_bytes | 事件类型 | 全局内存分配分析 |
2.2 主机维度监控实战
场景：生产环境中发现MySQL内存占用异常攀升，需识别内存消耗最高的客户端来源。
sql
SELECT
IFNULL(host, 'background') AS 连接主机,
ROUND(current_allocated/1024/1024, 2) AS 当前分配_MB,
ROUND(current_count_used, 0) AS 使用中的块数,
ROUND(current_avg_alloc/1024, 2) AS 平均分配_KB,
ROUND(current_max_alloc/1024/1024, 2) AS 最大分配_MB,
ROUND(total_allocated/1024/1024, 2) AS 历史分配总量_MB,
ROUND((current_allocated/total_allocated)100, 2) AS 内存使用率_百分比
FROM sys.x$memory_by_host_by_current_bytes
ORDER BY current_allocated DESC
LIMIT 10;
结果分析示例：
主机 10.186.61.18 当前分配约1.88GB内存，但内存使用率仅为1.49%，可能为连接池中持久会话所致。
background 系统线程占用约1GB内存且使用率较高，需进一步分析其内部组件。
可针对高内存占用的主机进行会话级深度追踪。
三、Performance Schema内存监控详解
3.1 五维度内存监控表
Performance Schema提供了更为精细的内存监控能力：
sql
查看相关监控表
SHOW TABLES FROM performance_schema LIKE 'memory%summary%';
核心表示例：
按账户统计：memory_summary_by_account_by_event_name
按主机统计：memory_summary_by_host_by_event_name
按线程统计：memory_summary_by_thread_by_event_name（最细粒度）
按用户统计：memory_summary_by_user_by_event_name
全局事件统计：memory_summary_global_by_event_name
3.2 线程级内存监控实战
场景：业务高峰期内存急剧上升，需定位具体SQL。
sql
SELECT
t.PROCESSLIST_ID AS 连接ID,
CONCAT(t.PROCESSLIST_USER, '@', t.PROCESSLIST_HOST) AS 用户连接,
t.PROCESSLIST_DB AS 数据库,
m.EVENT_NAME AS 内存事件,
ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 4) AS 当前使用_MB,
ROUND(m.HIGH_NUMBER_OF_BYTES_USED/1024/1024, 4) AS 峰值使用_MB,
m.COUNT_ALLOC AS 分配次数,
m.COUNT_FREE AS 释放次数,
LEFT(t.PROCESSLIST_INFO, 200) AS 执行语句摘要
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID IS NOT NULL
AND m.CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;
四、深度关联分析：SQL级内存追踪
4.1 关联分析实战
场景：复杂查询执行缓慢，疑似内存分配过多。
sql
SELECT
t.PROCESSLIST_ID AS 会话ID,
CONCAT(t.PROCESSLIST_USER, '@', t.PROCESSLIST_HOST) AS 账户信息,
CASE
WHEN m.EVENT_NAME LIKE 'memory/sql/%' THEN 'SQL层内存'
WHEN m.EVENT_NAME LIKE 'memory/innodb/%' THEN 'InnoDB存储引擎'
WHEN m.EVENT_NAME LIKE 'memory/myisam/%' THEN 'MyISAM存储引擎'
WHEN m.EVENT_NAME LIKE 'memory/temptable/%' THEN '临时表内存'
ELSE '其他内存'
END AS 内存类型,
m.EVENT_NAME AS 具体事件,
ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 6) AS 当前使用_MB,
ROUND(m.SUM_NUMBER_OF_BYTES_ALLOC/1024/1024, 2) AS 历史分配总量_MB,
ROUND((m.SUM_NUMBER_OF_BYTES_FREE / NULLIF(m.SUM_NUMBER_OF_BYTES_ALLOC, 0)) 100, 2) AS 内存释放率_百分比,
t.PROCESSLIST_TIME AS 执行时间_秒,
LEFT(t.PROCESSLIST_INFO, 300) AS 完整SQL语句
FROM performance_schema.memory_summary_by_thread_by_event_name m
INNER JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID IS NOT NULL
AND t.PROCESSLIST_INFO IS NOT NULL
AND m.CURRENT_NUMBER_OF_BYTES_USED > 10241024
ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 15;
4.2 内存事件分类统计
sql
SELECT
CASE
WHEN EVENT_NAME LIKE 'memory/sql/%' THEN 'SQL层'
WHEN EVENT_NAME LIKE 'memory/innodb/%' THEN 'InnoDB引擎'
WHEN EVENT_NAME LIKE 'memory/myisam/%' THEN 'MyISAM引擎'
WHEN EVENT_NAME LIKE 'memory/performance_schema/%' THEN '监控系统'
WHEN EVENT_NAME LIKE 'memory/temptable/%' THEN '临时表'
ELSE '其他'
END AS 内存类别,
COUNT() AS 事件数量,
ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 当前使用_MB,
ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 峰值使用_MB,
ROUND(AVG(CURRENT_NUMBER_OF_BYTES_USED/1024), 2) AS 平均使用_KB
FROM performance_schema.memory_summary_global_by_event_name
GROUP BY 内存类别
ORDER BY 当前使用_MB DESC;
五、关键内存事件深度解读
5.1 SQL层核心内存组件
1. main_mem_root – 查询主内存池
该内存池用于存储查询解析与执行期间的临时数据结构。
sql
SELECT
t.PROCESSLIST_ID,
CONCAT(t.PROCESSLIST_USER, '@', t.PROCESSLIST_HOST) AS user_host,
ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 4) AS mem_root_used_MB,
m.HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS mem_root_high_MB,
t.PROCESSLIST_TIME AS query_time_sec,
LEFT(t.PROCESSLIST_INFO, 150) AS current_query
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
WHERE m.EVENT_NAME = 'memory/sql/thd::main_mem_root'
AND t.PROCESSLIST_ID IS NOT NULL
AND m.CURRENT_NUMBER_OF_BYTES_USED > 1010241024
ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESC;
2. JOIN_CACHE – 连接操作缓存
用于大表JOIN操作时的内存缓存。
sql
SELECT
t.PROCESSLIST_ID,
t.PROCESSLIST_INFO AS query,
ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 2) AS join_cache_mb,
ROUND(m.HIGH_NUMBER_OF_BYTES_USED/1024/1024, 2) AS join_cache_high_mb
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
WHERE m.EVENT_NAME = 'memory/sql/JOIN_CACHE'
AND m.CURRENT_NUMBER_OF_BYTES_USED > 5010241024
ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESC;
5.2 存储引擎内存组件
InnoDB非缓冲池内存分析：
sql
SELECT
EVENT_NAME,
ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS current_mb,
ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS high_mb,
COUNT() AS thread_count
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/innodb/%'
GROUP BY EVENT_NAME
HAVING current_mb > 1
ORDER BY current_mb DESC;
六、内存问题排查工作流
6.1 日常监控检查清单
sql
1. 全局内存健康检查
SELECT
'全局内存使用' AS 检查项,
ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024/1024, 2) AS 当前使用_GB,
ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024/1024, 2) AS 历史峰值_GB,
COUNT(DISTINCT EVENT_NAME) AS 内存事件类型数
FROM performance_schema.memory_summary_global_by_event_name;
2. 用户级内存TOP 5
SELECT
IFNULL(USER, 'system') AS 用户名,
ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 当前使用_MB,
ROUND(SUM(HIGH_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 历史峰值_MB
FROM performance_schema.memory_summary_by_user_by_event_name
GROUP BY USER
ORDER BY 当前使用_MB DESC
LIMIT 5;
3. 疑似内存泄漏检查（分配次数远大于释放次数）
SELECT
t.PROCESSLIST_ID,
CONCAT(t.PROCESSLIST_USER, '@', t.PROCESSLIST_HOST) AS 用户连接,
m.EVENT_NAME,
m.COUNT_ALLOC,
m.COUNT_FREE,
m.COUNT_ALLOC m.COUNT_FREE AS 未释放次数,
ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 4) AS 当前持有_MB
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID IS NOT NULL
AND m.COUNT_ALLOC > m.COUNT_FREE
AND (m.COUNT_ALLOC m.COUNT_FREE) > 1000
ORDER BY (m.COUNT_ALLOC m.COUNT_FREE) DESC
LIMIT 10;
6.2 紧急内存问题排查流程
场景：内存使用持续增长，接近OOM阈值。
sql
第一步：定位内存消耗最高的会话
SELECT
'紧急排查' AS 场景,
t.PROCESSLIST_ID AS 会话ID,
CONCAT(t.PROCESSLIST_USER, '@', t.PROCESSLIST_HOST) AS 来源,
ROUND(SUM(m.CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 总内存_MB,
GROUP_CONCAT(
CONCAT(
SUBSTRING_INDEX(m.EVENT_NAME, '/', 1),
':',
ROUND(m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024, 2),
'MB'
)
ORDER BY m.CURRENT_NUMBER_OF_BYTES_USED DESC
SEPARATOR ' | '
) AS 内存分布详情,
t.PROCESSLIST_TIME AS 运行时间_秒,
LEFT(t.PROCESSLIST_INFO, 200) AS 当前SQL
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID IS NOT NULL
AND m.CURRENT_NUMBER_OF_BYTES_USED > 0
GROUP BY t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_HOST,
t.PROCESSLIST_TIME, t.PROCESSLIST_INFO
HAVING 总内存_MB > 100
ORDER BY 总内存_MB DESC
LIMIT 20;
第二步：分析内存分配最频繁的事件
SELECT
EVENT_NAME,
SUM(COUNT_ALLOC) AS 总分配次数,
SUM(COUNT_FREE) AS 总释放次数,
SUM(COUNT_ALLOC) SUM(COUNT_FREE) AS 未释放次数,
ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS 当前占用_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE COUNT_ALLOC > 0
GROUP BY EVENT_NAME
HAVING 未释放次数 > 10000
OR 当前占用_MB > 500
ORDER BY 未释放次数 DESC, 当前占用_MB DESC
LIMIT 15;
七、内存监控配置优化建议
7.1 启用完整内存监控
sql
检查当前配置
SELECT FROM performance_schema.setup_instruments
WHERE NAME LIKE 'memory/%';
启用所有内存监控（生产环境需评估性能影响）
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'memory/%';
选择性启用关键组件监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME IN (
'memory/sql/thd::main_mem_root',
'memory/sql/JOIN_CACHE',
'memory/innodb/mem0mem',
'memory/temptable/%'
);
7.2 定期维护内存统计信息
sql
备份当前内存统计
CREATE TABLE memory_stats_backup AS
SELECT NOW() AS collect_time,
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0;
重置统计（谨慎操作，会清除历史数据）
TRUNCATE TABLE performance_schema.memory_summary_global_by_event_name;
八、源码级解读
内存分配事件主要分为以下几类：
memory/sql/xxx：SQL层内存分配
memory/innodb/xxx：InnoDB存储引擎内存（如使用MyISAM则为memory/myisam/xxx）
memory/temptable/xxx：临时表相关内存
在源码sql/sql_class.h中，对main_mem_root的描述如下：
c
/
This memory root is used for two purposes:
for conventional queries, to allocate structures stored in main_lex
during parsing, and allocate runtime data (execution plan, etc.)
during execution.
for prepared queries, only to allocate runtime data. The parsed
tree itself is reused between executions and thus is stored elsewhere.
/
MEM_ROOT main_mem_root;
mem0mem是InnoDB引擎中用于内存分配与管理的核心模块。
九、总结
MySQL 5.7提供了从宏观到微观的全方位内存监控能力：
1. 多维度监控：支持主机、用户、线程、事件等多角度交叉分析。
2. 事件分类识别：可清晰区分SQL层、存储引擎、临时表等不同组件内存使用。
3. 深度关联分析：结合线程信息可追踪至具体SQL语句。
4. 历史趋势对比：通过历史与当前数据对比，及时发现异常模式。
建议在生产环境中建立定期内存监控机制，设置合理的阈值告警，从而在内存问题萌芽阶段及时干预，保障数据库系统稳定运行。
来源：小程序app开发|ui设计|软件外包|IT技术服务公司-木风未来科技-成都木风未来科技有限公司
