mysql内存体系结构_mysql 内存体系结构--session

体系结构

tmp_table_size & max_heap_table_size ，这个是会话级的内存结构

mysql使用总内存 = global_buffers + thread_buffers

All thread buffer(会话/线程级内存分配总和) =

max_threads(当前活跃连接数) * (

read_buffer_size -- 顺序读缓冲，提高顺序读效率

+read_rnd_buffer_size -- 随机读缓冲，提高随机读效率

+sort_buffer_size -- 排序缓冲，提高排序效率

+join_buffer_size -- 表连接缓冲，提高表连接效率

+binlog_cache_size -- 二进制日志缓冲，提高二进制日志写入效率

+tmp_table_size -- 内存临时表，提高临时表存储效率

+thread_stack -- 线程堆栈，暂时寄存SQL语句/存储过程

+thread_cache_size -- 线程缓存，降低多次反复打开线程开销

+net_buffer_length -- 线程持连接缓冲以及读取结果缓冲

+bulk_insert_buffer_size -- MyISAM表批量写入数据缓冲

)

global buffer(SGA, 全局内存分配总和) =

innodb_buffer_pool_size -- InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等

+innodb_additional_mem_pool_size -- InnoDB数据字典额外内存，缓存所有表数据字典

+innodb_log_buffer_size -- InnoDB REDO日志缓冲，提高REDO日志写入效率

+key_buffer_size -- MyISAM表索引高速缓冲，提高MyISAM表索引读写效率

+query_cache_size -- 查询高速缓存，缓存查询结果，提高反复查询返回效率

+table_cahce -- 表空间文件描述符缓存，提高数据表打开效率

+table_definition_cache -- 表定义文件描述符缓存，提高数据表打开效率

max_heap_table_size 负责设置MEMORY/HEAP表最大容量，不管其他执行SQL产生的临时表，如果内存不够用，

则不允许写入新的数据，MEMORY/HEAP表也不会转成磁盘表，只会告警超限后拒绝写入

tmp_table_size 不负责限制 MEMORY/HEAP表最大容量，如果执行SQL产生临时表超过 tmp_table_size/max_heap_table_size，

则会产生基于磁盘的MyISAM表

key_buffer_size

• MyISAM表的索引只需打开一次，多个线程间共享；而数据文件则是每个线程各自打开

• MyISAM索引缓冲。对MyISAM很重要，缓存MyISAM表索引数据。

• MyISAM只缓存索引数据，不缓存行数据，行数据交给OS页面缓存(OS page cache)

• 若主要使用MyISAM，推荐设置可用RAM的20%~50%,更多的内存留给OS，缓存从*.MYD读取的行数据。

即便全是innodb表,没用MyISAM，也有必要设置该值用于缓存临时表之索引，推荐32MB

可以设置多个key buffer

set global k1.key_buffer_size = 1G;

set global k2.key_buffer_size = 1G;

#将索引load到指定的key buffer

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache;

CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache;

read_buffer_size

是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。

read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得

太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

read_rnd_buffer_size

是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序

查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值

。但MySQL会为每个客户连接分配该缓冲区，所以应尽量适当设置该值，以避免内存开销过大。

join_buffer_size

应用程序经常会出现一些两表(或多表)JOIN的操作需求，MySQL在完成某些 Join 需求的时候(all row join/

all index join/ range index scan join)，为了减少参与JOIN的“被驱动表”的读取次数以提高性能，需要使用

到 JOIN Buffer 来协助完成 JOIN操作。

当 JOIN Buffer 太小，MySQL 不会将该 Buffer 存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join

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

这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。(执行计划中，如果显示Using join

buffer，就说明会用到JOIN Buffer) --分段写入

多表join时，需要用到join buffer的三种情况：

all row join =&get; do not use indexes and thus perform full table scans(没有索引的全表扫描)

all index join =&get; plain index scans(普通索引扫描)

range index scan join =&get; range index scans(范围索引扫描)

任何两个表间的全表 join 就会分配一次 join buffer，也就是说，如果有3个表join，就会分配2次join buffer。(而不是一个session只分配一次)。

上面几个buffer size，一般设置为128K ~ 2M足够

max_heap_table_size

个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值

binlog_cache_size

在事务过程中容纳二进制日志SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志

(—log-bin 选项)的前提下为每个客户端分配的内存。

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

binlog hit ratio = (Binlog_cache_use)/( Binlog_cache_use + Binlog_cache_disk_use)

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

Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的错误。

比如，我们开启binlog时，执行load data infile，将一个大文件导入时，就可能会出现这个错误提示

set sql_log_bin = 0;

load data infile ...

然后在slave，再次执行load data