MySQL 如何使用内存

InnoDB缓冲池是一个内存区域，用于保存InnoDB表、索引和其他辅助缓冲区的缓存数据 。为了提高大容量读取操作的效率，缓冲池被划分为 可能包含多行的页面。为了缓存管理的效率，缓冲池被实现为页链表；使用LRU算法的变体，很少使用的数据会从缓存中老化 。

缓冲池的大小对系统性能很重要：

系统 innodb_buffer_pool_size 变量定义缓冲池大小。通常，推荐 innodb_buffer_pool_size 值为系统内存的 50% 到 75%。 
innodb_buffer_pool_size 可以在服务器运行时动态配置。

在具有大量内存的系统上，您可以通过将缓冲池划分为多个 缓冲池实例来提高并发性。系统变量定义缓冲池实例的 innodb_buffer_pool_instances 数量。

太小的缓冲池可能会导致过度搅动，因为页面从缓冲池中刷新，只是在短时间内再次需要。

过大的缓冲池可能会因为内存竞争而导致交换。

所有线程共享MyISAM 密钥缓冲区。key_buffer_size系统变量决定了它的大小 。

对于服务器打开的每个MyISAM表，索引文件打开一次；对于访问该表的每个并发运行的线程，数据文件打开一次。对于每个并发线程，分配一个表结构、每列的列结构和一个大小的缓冲区 （其中是最大行长度，不包括 列）。一 列需要五到八个字节加上 数据的长度。存储引擎维护一个额外的行缓冲区供内部使用 。 

如果内部内存临时表变得太大（由 tmp_table_size和 max_heap_table_size 系统变量确定），MySQL 会自动将表从内存转换为磁盘格式。磁盘临时表使用 internal_tmp_disk_storage_engine 系统变量定义的存储引擎。

对于使用MEMORY显式创建的表CREATE TABLE，只有 max_heap_table_size 系统变量确定表可以增长到多大，并且不会转换为磁盘格式。

服务器用来管理客户端连接的每个线程都需要一些特定于线程的空间。以下列表指示这些以及哪些系统变量控制它们的大小：

一个堆栈 ( thread_stack)

连接缓冲区 ( net_buffer_length)

结果缓冲区 ( net_buffer_length)

连接缓冲区和结果缓冲区 net_buffer_length均以字节大小开始，但根据需要动态扩大到 max_allowed_packet字节。结果缓冲区 net_buffer_length在每个 SQL 语句之后缩小为字节。在运行语句时，还会分配当前语句字符串的副本。

每个连接线程都使用内存来计算语句摘要。服务器为每个会话分配 max_digest_length字节。

所有线程共享相同的基本内存。

当不再需要某个线程时，分配给它的内存将被释放并返回给系统，除非该线程返回到线程缓存中。在这种情况下，内存保持分配状态。

每个对表执行顺序扫描的请求都会分配一个读取缓冲区。系统 read_buffer_size变量确定缓冲区大小。

当以任意顺序读取行时（例如，在排序之后），可能会分配一个 随机读取缓冲区 以避免磁盘寻道。系统 read_rnd_buffer_size 变量确定缓冲区大小。

所有连接都在一次执行中执行，大多数连接甚至可以在不使用临时表的情况下完成。大多数临时表是基于内存的哈希表。具有大行长度（计算为所有列长度的总和）或包含 BLOB列的临时表存储在磁盘上。

大多数执行排序的请求会根据结果集大小分配一个排序缓冲区和零到两个临时文件。

几乎所有的解析和计算都是在线程本地和可重用的内存池中完成的。小项目不需要内存开销，从而避免了正常缓慢的内存分配和释放。仅为意外的大字符串分配内存。

对于每个具有BLOB 列的表，动态扩大缓冲区以读取更大的BLOB值。如果你扫描一个表，缓冲区会增长到 BLOB最大值。

MySQL 需要用于表缓存的内存和描述符。所有正在使用的表的处理程序结构都保存在表缓存中，并按照“先进先出”（FIFO）进行管理。系统 table_open_cache变量定义了初始表缓存大小；

MySQL 还需要内存用于表定义缓存。table_definition_cache 系统变量定义.frm可以存储在表定义缓存中的表定义（来自文件）的 数量。如果使用大量的表，可以创建一个大的表定义缓存来加快表的打开速度。与表缓存不同，表定义缓存占用较少空间并且不使用文件描述符。

GRANT作为, CREATE USER, CREATE SERVER, 和 INSTALL PLUGIN语句 的结果，服务器将信息缓存在内存中 。该内存不会被相应 REVOKE的 , DROP USER, DROP SERVER和 UNINSTALL PLUGIN 语句释放，因此对于执行许多导致缓存的语句实例的服务器，缓存内存使用很可能会增加，除非它被释放 FLUSH PRIVILEGES。

当MySQL的Join有使用到Block Nested-Loop Join，那么调大变量join_buffer_size才是有意义的

----------------------------------------------------------------------------------------------------------------
--查看每个线程占用多少内存，然后乘以正在运行的线程（也就是排查sleep的）。
SELECT ( ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@max_allowed_packet
+ @@net_buffer_length )
) / (1024*1024) AS MEMORY_MB;

--查看MySQL全局占用多少内存
select (@@innodb_buffer_pool_size
+@@innodb_log_buffer_size
+@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;

--查看performance_schema占用多少内存
use sys;
SELECT 
SUBSTRING_INDEX(event_name,'/',2) AS code_area,
(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;

--查看 memory 存储引擎占用多少内存
use information_schema;
select sum(max_data_length)/1024/1024 as MEMORY_MB from tables where engine='memory';

--查看innodb_buffer_pool情况
select sum(POOL_SIZE),sum(free_buffers),sum(DATABASE_PAGES) from INNODB_BUFFER_POOL_STATS;

--根据thread的个数，计算内存
select count(*) from information_schema.processlist where db="db_name" ;

--MySQL最大可使用内存（M）：

select (@@key_buffer_size 
+@@innodb_buffer_pool_size 
+ @@tmp_table_size 
+ @@max_connections*(@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack) )/1024/1024  as "Total_AllMem result";

在线计算mysql内存网站  http://www.mysqlcalculator.com/

--------------------------------------------------------------------------------------------------------------