mysql OCP之内存学习

MySQL 中内存分为全局内存和线程内存两大部分（其实并不全部，只是影响比较大的 部分）：

per_thread_buffers=(read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+

join_buffer_size+binlog_cache_size+tmp_table_size)*max_connections

global_buffers=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buff

er_size+key_buffer_size+query_cache_size

total_memory=global_buffers+per_thread_buffers

全局缓存 ：

key_buffer_size ：决定索引处理的速度，尤其是索引读的速度。默认值是 16M ，通过检查

状态值 Key_read_requests 和 Key_reads ，可知  key_buffer_size 设置是否合理。

比例 key_reads / key_read_requests 应该尽可能的低，至少是 1:100 ， 1:1000 更好（上述状态值

可以使用 'key_read%' 获得用来显示状态数据）。

key_buffer_size 只对 MyISAM 表起作用 。即使你不使用 MyISAM 表，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。可以使用 检查状态值 'created_tmp_disk_tables' 得知详情。

innodb_buffer_pool_size ： InnoDB 使用该参数指定大小的内存来缓冲数据和索引， 这个是

Innodb 引擎中影响性能最大的参数 。

innodb_additional_mem_pool_size ：指定 InnoDB 用来存储数据字典和其他内部数据结构的

内存池大小。缺省值是 8M 。通常不用太大，只要够用就行，应该与表结构的复杂度有关

系。如果不够用， MySQL 会在错误日志中写入一条警告信息。

innodb_log_buffer_size ：指定 InnoDB 用来存储日志数据的缓存大小，如果您的表操作中

包含大量并发事务（或大规模事务），并且在事务提交前要求记录日志文件，请尽量调高

此项值，以提高日志效率。 query_cache_size ：是 MySQL 的查询缓冲大小。（从 4.0.1 开始， MySQL 提供了查询缓冲机 制）使用查询缓冲， MySQL 将 SELECT 语句和查询结果存放在缓冲区中，今后对于同样的 SELECT 语句（区分大小写），将直接从缓冲区中读取结果。根据 MySQL 用户手册，使用 查询缓冲最多可以达到 238% 的效率。通过检查状态值 ’Qcache_%’ ，可以知道

query_cache_size 设置是否合理：如果 Qcache_lowmem_prunes 的值非常大，则表明经常

出现缓冲不够的情况，如果 Qcache_hits 的值也非常大，则表明查询缓冲使用非常频繁，

此时需要增加缓冲大小；如果 Qcache_hits 的值不大，则表明你的查询重复率很低，这种

情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在 SELECT 语

句中加入 SQL_NO_CACHE 可以明确表示不使用查询缓冲。

线程缓存：

每个连接到 MySQL 服务器的线程都需要有自己的缓冲。大概需要立刻分配 256K ，甚至在

线程空闲时，它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多

的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表

做复杂的操作例如扫描、排序或者需要临时表，则需分配大约 read_buffer_size， sort_buffer_size ， read_rnd_buffer_size ， tmp_table_size 大小的内存空间。不过它们只是在

需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。

tmp_table_size 可能高达 MySQL 所能分配给这个操作的最大内存空间了。

read_buffer_size ：是 MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入

缓冲区， MySQL 会为它分配一段内存缓冲区。 read_buffer_size 变量控制这一缓冲区的大

小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增

加该变量值以及内存缓冲区大小提高其性能。

sort_buffer_size ：是 MySQL 执行排序使用的缓冲大小。如果想要增加 ORDER BY 的速度，

首先看是否可以让 MySQL 使用索引而不是额外的排序阶段。如果不能，可以尝试增加

sort_buffer_size 变量的大小。

read_rnd_buffer_size ：是 MySQL 的随机读缓冲区大小。当按任意顺序读取行时 ( 例如，按

照排序顺序 ) ，将分配一个随机读缓存区。进行排序查询时， MySQL 会首先扫描一遍该缓

冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但 MySQL

会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

tmp_table_size ：是 MySQL 的临时表缓冲大小。所有联合在一个 DML 指令内完成，并且大

多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的 (HEAP) 表。具有大 的记录长度的临时表 ( 所有列的长度的和 ) 或包含 BLOB 列的表存储在硬盘上。如果某个内

部 heap （堆积）表大小超过 tmp_table_size ， MySQL 可以根据需要自动将内存中的 heap

表改为基于硬盘的 MyISAM 表。还可以通过设置 tmp_table_size 选项来增加临时表的大小。

也就是说，如果调高该值， MySQL 同时将增加 heap 表的大小，可达到提高联接查询速度

的效果。

thread_stack ：主要用来存放每一个线程自身的标识信息，如线程 id ，线程运行时基本信

息等等，我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存。

join_buffer_size ：应用程序经常会出现一些两表（或多表） Join 的操作需求， MySQL 在完

成某些 Join 需求的时候（ all/index join ），为了减少参与 Join 的 “ 被驱动表 ” 的读取次数以

提高性能，需要使用到 Join Buffer 来协助完成 Join 操作。当 Join Buffer 太小， MySQL 不

会将该 Buffer 存入磁盘文件，而是先将 Join Buffer 中的结果集与需要 Join 的表进行 Join

操作，然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer 中，如此往复。

这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。

binlog_cache_size ：在事务过程中容纳二进制日志 SQL 语句的缓存大小。二进制日志缓存

是服务器支持事务存储引擎并且服务器启用了二进制日志 (—log-bin 选项 ) 的前提下为每

个客户端分配的内存，注意，是每个 Client 都可以分配设置大小的 binlog cache 空间。如

果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能。

当然，我们可以通过 MySQL 的以下两个状态变量来判断当前的 binlog_cache_size 的状况：

Binlog_cache_use 和 Binlog_cache_disk_use 。 “max_binlog_cache_size” ：和

"binlog_cache_size" 相对应，但是所代表的是 binlog 能够使用的最大 cache 内存大小。当

我们执行多语句事务的时候， max_binlog_cache_size 如果不够大的话，系统可能会报出

“ Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage” 的

错误。

其中需要注意的是：table_cache 表示的是所有线程打开的表的数目，和内存无关 。