共计 6985 个字符，预计需要花费 18 分钟才能阅读完成。
自动写代码机器人，免费开通
怎么在 MySQL 中查看使用的内存？针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
performance_schema 有如下表记录内存使用情况
mysql show tables like %memory%summary%
+-------------------------------------------------+
| Tables_in_performance_schema (%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 |
+-------------------------------------------------+每个内存统计表都有如下统计列：
* COUNT_ALLOC，COUNT_FREE：对内存分配和释放内存函数的调用总次数
* SUM_NUMBER_OF_BYTES_ALLOC，SUM_NUMBER_OF_BYTES_FREE：已分配和已释放的内存块的总字节大小
* CURRENT_COUNT_USED：这是一个便捷列，等于 COUNT_ALLOC – COUNT_FREE
* CURRENT_NUMBER_OF_BYTES_USED：当前已分配的内存块但未释放的统计大小。这是一个便捷列，等于 SUM_NUMBER_OF_BYTES_ALLOC – SUM_NUMBER_OF_BYTES_FREE
* LOW_COUNT_USED，HIGH_COUNT_USED：对应 CURRENT_COUNT_USED 列的低和高水位标记
* LOW_NUMBER_OF_BYTES_USED，HIGH_NUMBER_OF_BYTES_USED：对应 CURRENT_NUMBER_OF_BYTES_USED 列的低和高水位标记
内存统计表允许使用 TRUNCATE TABLE 语句。使用 truncate 语句时有如下行为：
* 通常，truncate 操作会重置统计信息的基准数据（即清空之前的数据），但不会修改当前 server 的内存分配等状态。也就是说，truncate 内存统计表不会释放已分配内存
* 将 COUNT_ALLOC 和 COUNT_FREE 列重置，并重新开始计数（等于内存统计信息以重置后的数值作为基准数据）
* SUM_NUMBER_OF_BYTES_ALLOC 和 SUM_NUMBER_OF_BYTES_FREE 列重置与 COUNT_ALLOC 和 COUNT_FREE 列重置类似
* LOW_COUNT_USED 和 HIGH_COUNT_USED 将重置为 CURRENT_COUNT_USED 列值
* LOW_NUMBER_OF_BYTES_USED 和 HIGH_NUMBER_OF_BYTES_USED 将重置为 CURRENT_NUMBER_OF_BYTES_USED 列值
* 此外，按照帐户，主机，用户或线程分类统计的内存统计表或 memory_summary_global_by_event_name 表，如果在对其依赖的 accounts、hosts、users 表执行 truncate 时，会隐式对这些内存统计表执行 truncate 语句
简单来说，就是可以根据用户、主机、线程、账号、全局的维度对内存进行监控。同时库 sys 也就这些表做了进一步的格式化，可以使得用户非常容易的观察到每个对象的内存开销：
mysql select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10;
+-----------------------------------------------------------------------------+---------------+
| event_name | current_alloc |
+-----------------------------------------------------------------------------+---------------+
| memory/performance_schema/events_statements_history_long | 13.66 MiB |
| memory/performance_schema/events_statements_history_long.sqltext | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.tokens | 9.77 MiB |
| memory/performance_schema/events_statements_summary_by_digest.tokens | 9.77 MiB |
| memory/performance_schema/table_handles | 9.06 MiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 8.67 MiB |
| memory/sql/String::value | 6.02 MiB |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 5.62 MiB |
| memory/performance_schema/events_statements_summary_by_digest | 4.88 MiB |
| memory/sql/TABLE | 4.35 MiB |
+-----------------------------------------------------------------------------+---------------+默认情况下 performance_schema 只对 performance_schema 进行了内存开销的统计。根据你的 MySQL 安装代码区域可能包括 performance_schema、sql、client、innodb、myisam、csv、memory、blackhole、archive、partition 和其他。
查看 innodb 相关的内存监控是否开启，默认不开启
mysql SELECT * FROM performance_schema.setup_instruments
- WHERE NAME LIKE %memory%+--------------------------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+--------------------------------------------------------------------------------+---------+-------+
| memory/performance_schema/mutex_instances | YES | NO |
| memory/performance_schema/rwlock_instances | YES | NO |
| memory/performance_schema/cond_instances | YES | NO |
| memory/performance_schema/file_instances | YES | NO |
| memory/performance_schema/socket_instances | YES | NO |
| memory/performance_schema/metadata_locks | YES | NO |
| memory/performance_schema/file_handle | YES | NO |
| memory/performance_schema/accounts | YES | NO |
| memory/performance_schema/events_waits_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/events_stages_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/events_statements_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/events_transactions_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/memory_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/events_stages_summary_global_by_event_name | YES | NO |
| memory/performance_schema/events_statements_summary_global_by_event_name | YES | NO |
| memory/performance_schema/memory_summary_global_by_event_name | YES | NO |
| memory/performance_schema/hosts | YES | NO |
| memory/performance_schema/events_waits_summary_by_host_by_event_name | YES | NO |
| memory/performance_schema/events_stages_summary_by_host_by_event_name | YES | NO |
| memory/performance_schema/events_statements_summary_by_host_by_event_name | YES | NO |
| memory/performance_schema/events_transactions_summary_by_host_by_event_name | YES | NO |可以通过条件缩小范围：
mysql SELECT * FROM performance_schema.setup_instruments
WHERE NAME LIKE %memory/innodb%
+-------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index | NO | NO |
| memory/innodb/buf_buf_pool | NO | NO |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO |
| memory/innodb/dict_stats_index_map_t | NO | NO |
| memory/innodb/dict_stats_n_diff_on_level | NO | NO |
| memory/innodb/other | NO | NO |
| memory/innodb/row_log_buf | NO | NO |
| memory/innodb/row_merge_sort | NO | NO |
| memory/innodb/std | NO | NO |
| memory/innodb/trx_sys_t::rw_trx_ids | NO | NO |对所有可能的对象进行内存监控。因此，还需要做下面的设置：
mysql update performance_schema.setup_instruments set enabled = yes where name like memory%
Query OK, 306 rows affected (0.00 sec)
Rows matched: 376 Changed: 306 Warnings: 0但是这种在线打开内存统计的方法仅对之后新增的内存对象有效，重启数据库后又会还原设置：
如想要对全局生命周期中的对象进行内存统计，必须在配置文件中进行设置，然后重启：
[mysqld]
performance-schema-instrument= memory/%=COUNTED可以使用 sys 库下的 memory_global_by_current_bytes 表来查询相同的底层数据，该模式表显示了全局服务器内当前内存使用情况，按分配类型进行细分。
mysql SELECT * FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE memory/innodb/buf_buf_pool \G
*************************** 1. row ***************************
event_name: memory/innodb/buf_buf_pool
current_count: 1
current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB
high_count: 1
high_alloc: 131.06 MiB
high_avg_alloc: 131.06 MiB此 sys 模式查询通过 current_alloc() 代码区域聚合当前分配的内存：
mysql SELECT SUBSTRING_INDEX(event_name, / ,2) AS
code_area, sys.format_bytes(SUM(current_alloc))
AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name, / ,2)
ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 843.24 MiB |
| memory/performance_schema | 81.29 MiB |
| memory/mysys | 8.20 MiB |
| memory/sql | 2.47 MiB |
| memory/memory | 174.01 KiB |
| memory/myisam | 46.53 KiB |
| memory/blackhole | 512 bytes |
| memory/federated | 512 bytes |
| memory/csv | 512 bytes |
| memory/vio | 496 bytes |
+---------------------------+---------------+关于怎么在 MySQL 中查看使用的内存问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。
向 AI 问一下细节
