本文深入探讨MySQL数据库中的缓冲区参数，包括bulk_insert_buffer_size、key_buffer_size、preload_buffer_size等，详细解释它们的作用、配置方法以及如何通过调整这些参数优化数据库性能。
有关buffer的参数
| bulk_insert_buffer_size
session and global 级别 可动态改变 默认值为8MB 最大4GB 单位为B
仅作用于使用 MyISAM存储引擎，用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件：insert … select …
insert … values (…) ,(…),(…)…
load data infile… into… (非空表)
| key_buffer_size
global 级别key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。使用MyISAM 存储时需注意，由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的SQL一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。
| preload_buffer_size
global/session级别 可动态变更
[size=-1] 重载索引时分配的缓冲区大小, 该变量仅支持MyISAM
现: windows 和linux的测试库以及正式库的默认值均为： 32K
mysql> SHOW VARIABLES like 'preload_buffer_size';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| preload_buffer_size | 32768 |
+---------------------+-------+
[size=-1]32768=1024*32=32K
| myisam_sort_buffer_size
global/session级别 可动态变更
当在REPAIR TABLE或用CREATE INDEX创建索引或ALTERTABLE过程中排序 MyISAM索引分配的缓冲区大小 范围：4B~4GB 默认8MB
设置恢复表之时使用的缓冲区的尺寸
| innodb_buffer_pool_size
global 级别 不可动态变更 最小单位为B字节
使用InnoDB存储引擎的时候，innodb_buffer_pool_size 参数可能是影响我们性能的最为关键的一个参数了，他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，当我们操作一个 InnoDB 表的时候，返回的所有数据或者数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，所以如果我们有足够的内存，尽可将该参数设置到足够大，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中，直至全部。我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。
| innodb_log_buffer_size
global 级别 不可动态变更
设置innodb存放Log的缓冲区大小 默认1MB,4MB可满足大多环境
| join_buffer_size
global/session级别 可动态变更
默认128KB 最大4GB 在windows 64位系统上可以超出4GB
联合查询操作所能使用的缓冲区大小, sort_buffer_size 一样, 该参数对应的分配内存也是 每连接独享!
当我们的Join 是ALL(全表扫描) ， index（全索引扫描） ，rang （索引范围扫描）或者index_merge（查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读
取表数据） 的时候使用的Buffer；实际上这种Join 被称为Full Join。实际上参与Join 的每一个表都需要一个Join Buffer，所以在Join 出现的时候，至少是两个。
| sort_buffer_size
global/session级别 可动态变更
系统默认大小为2MB 最大4GB 在windows 64位系统上可以超出4GB
系统中对数据进行排序的时候使用的Buffer；
Sort Buffer 同样是针对单个Thread 的，所以当多个Thread 同时进行排序的时候，系统中就会出现多个Sort Buffer。一般我们可以通过增大Sort Buffer 的大小来提高ORDER BY 或者是GROUP BY的处理性能。
如果Join 语句不少，可适当增大join_buffer_size 的设置到1MB 左右，如果内存充足甚至可以设置为2MB。
对于sort_buffer_size 参数来说，一般设置为2MB 到4MB 之间可以满足大多数应用的需求
如果应用系统中的排序都比较大，内存充足且并发量不是特别的大的时候，也可以继续增大sort_buffer_size 的设置。
在这两个Buffer 设置的时候，最需要注意的就是不要忘记是每个Thread 都会创建自己独立的Buffer，而不是整个系统共享的Buffer，不要因为设置过大而造成系统内存不足。
| net_buffer_length
global/session级别 可动态变更
单位为B字节 可设置范围为1024~1048576（1024*1024=1M）默认16384（16K） 控制网络缓冲区初始化大小,最大可扩展至max_allowed_packet 参数大小 在查询之间将通信缓冲区重设为该值。一般情况不应改变，但如果内存很小，可以将它设置为期望的客户端发送的SQL语句的长度。如果语句超出该长度，缓冲区自动扩大，直到max_allowed_packet字节。
max_allowed_packet
global/session级别 可动态变更
单位为B字节 系统默认为1MB 最大值为1G 设定值必须为1024的倍数
在网络传输中 一次消息传输量的最大值,这个参数与net_buffer_size相对应,只不过是net_buffer_size的最大值.当消息传输量大于net_buffer_size的设置时，mysql会自动增大net_buffer_size的大小 直至缓冲区大小达到max_allowed_packet 所设置的值.
包或任何生成的/中间字符串的最大大小。
包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节。该值默认很小，以捕获大的(可能是错误的)数据包。
如果使用大的BLOB 列或长字符串，你必须增加该值。应同你想要使用的最大的BLOB一样大。max_allowed_packet的协议限制为1GB
表的扫描分为Sequential Scan和Radom Scan 2种方式，read_buffer_size设置sequential scan时使用的缓存，read_rnd_buffer_size设置radom scan时使用的缓存
| read_buffer_size
global/session级别 可动态变更
系统默认为128KB 最大2G 设置的值必须为4KB的倍数,否则系统会自动修改成小于设置值的最大值（4KB）的倍数
1 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描，可能需要增加该值， 默认值为131072。
2 为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是128KB); 这个选项的设置值在必要时可以用SQL命令SET SESSION read_buffer_size = n命令加以改变。
3 read_buffer_size 是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区
4 设置以顺序扫描的方式扫描表数据的时候使用缓冲区的大小. 每个线程进行顺序扫描的时候都会产生该buffer ,而且同一个Query中如果有多个表进行全表扫描,会产生多个该buffer
所以在设置的时候尽量不要太高 避免因为并发太大造成内存不够
一般来说 可以尝试适当调大此参数看能否改善全表扫描的性能,在不同的平台上会有不同的表现,这主要与OS级别的文件系统IO大小有关,所以该参数的设置最好在真实环境下通过多次更改测试调整,才能选找到一个最佳值.
| read_rnd_buffer_size
global/session级别 可动态变更
默认值是256KB,最大值是4GB
类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)。
设置进行随机读的时候所使用的缓冲区，此参数和read_buffer_size所设置的buffer相反,一个是顺序读的时候使用,一个是随机读的时候使用,但是两者都是针对线程的设置，每个线程都可能产生两种buffer中的任何一个.
| sql_buffer_result
对于内存，MySQL没有过高要求，主要的除了每个连接使用的read_buffer_size 和sort_buffer_size尺寸的内存外，全局有一个Qcache使用query_cache_size尺寸的cache。MyISAM引擎主要使用的是key_buffer_size尺寸的内存,InnoDB主要使用内存就是 innodb_buffer_pool_size 尺寸内存。而且InnoDB可能还有一个2G内存使用限制（是否真没有这个问题有待验证）。所以依靠大规模增加内存提高MySQL性能未必有效
| bulk_insert_buffer_size
session and global 级别 可动态改变 默认值为8MB 最大4GB 单位为B
仅作用于使用 MyISAM存储引擎，用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件：insert … select …
insert … values (…) ,(…),(…)…
load data infile… into… (非空表)
| key_buffer_size
global 级别key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。使用MyISAM 存储时需注意，由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的SQL一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。
| preload_buffer_size
global/session级别 可动态变更
[size=-1] 重载索引时分配的缓冲区大小, 该变量仅支持MyISAM
现: windows 和linux的测试库以及正式库的默认值均为： 32K
mysql> SHOW VARIABLES like 'preload_buffer_size';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| preload_buffer_size | 32768 |
+---------------------+-------+
[size=-1]32768=1024*32=32K
| myisam_sort_buffer_size
global/session级别 可动态变更
当在REPAIR TABLE或用CREATE INDEX创建索引或ALTERTABLE过程中排序 MyISAM索引分配的缓冲区大小 范围：4B~4GB 默认8MB
设置恢复表之时使用的缓冲区的尺寸
| innodb_buffer_pool_size
global 级别 不可动态变更 最小单位为B字节
使用InnoDB存储引擎的时候，innodb_buffer_pool_size 参数可能是影响我们性能的最为关键的一个参数了，他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，当我们操作一个 InnoDB 表的时候，返回的所有数据或者数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，所以如果我们有足够的内存，尽可将该参数设置到足够大，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中，直至全部。我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。
| innodb_log_buffer_size
global 级别 不可动态变更
设置innodb存放Log的缓冲区大小 默认1MB,4MB可满足大多环境
| join_buffer_size
global/session级别 可动态变更
默认128KB 最大4GB 在windows 64位系统上可以超出4GB
联合查询操作所能使用的缓冲区大小, sort_buffer_size 一样, 该参数对应的分配内存也是 每连接独享!
当我们的Join 是ALL(全表扫描) ， index（全索引扫描） ，rang （索引范围扫描）或者index_merge（查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读
取表数据） 的时候使用的Buffer；实际上这种Join 被称为Full Join。实际上参与Join 的每一个表都需要一个Join Buffer，所以在Join 出现的时候，至少是两个。
| sort_buffer_size
global/session级别 可动态变更
系统默认大小为2MB 最大4GB 在windows 64位系统上可以超出4GB
系统中对数据进行排序的时候使用的Buffer；
Sort Buffer 同样是针对单个Thread 的，所以当多个Thread 同时进行排序的时候，系统中就会出现多个Sort Buffer。一般我们可以通过增大Sort Buffer 的大小来提高ORDER BY 或者是GROUP BY的处理性能。
如果Join 语句不少，可适当增大join_buffer_size 的设置到1MB 左右，如果内存充足甚至可以设置为2MB。
对于sort_buffer_size 参数来说，一般设置为2MB 到4MB 之间可以满足大多数应用的需求
如果应用系统中的排序都比较大，内存充足且并发量不是特别的大的时候，也可以继续增大sort_buffer_size 的设置。
在这两个Buffer 设置的时候，最需要注意的就是不要忘记是每个Thread 都会创建自己独立的Buffer，而不是整个系统共享的Buffer，不要因为设置过大而造成系统内存不足。
| net_buffer_length
global/session级别 可动态变更
单位为B字节 可设置范围为1024~1048576（1024*1024=1M）默认16384（16K） 控制网络缓冲区初始化大小,最大可扩展至max_allowed_packet 参数大小 在查询之间将通信缓冲区重设为该值。一般情况不应改变，但如果内存很小，可以将它设置为期望的客户端发送的SQL语句的长度。如果语句超出该长度，缓冲区自动扩大，直到max_allowed_packet字节。
max_allowed_packet
global/session级别 可动态变更
单位为B字节 系统默认为1MB 最大值为1G 设定值必须为1024的倍数
在网络传输中 一次消息传输量的最大值,这个参数与net_buffer_size相对应,只不过是net_buffer_size的最大值.当消息传输量大于net_buffer_size的设置时，mysql会自动增大net_buffer_size的大小 直至缓冲区大小达到max_allowed_packet 所设置的值.
包或任何生成的/中间字符串的最大大小。
包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节。该值默认很小，以捕获大的(可能是错误的)数据包。
如果使用大的BLOB 列或长字符串，你必须增加该值。应同你想要使用的最大的BLOB一样大。max_allowed_packet的协议限制为1GB
表的扫描分为Sequential Scan和Radom Scan 2种方式，read_buffer_size设置sequential scan时使用的缓存，read_rnd_buffer_size设置radom scan时使用的缓存
| read_buffer_size
global/session级别 可动态变更
系统默认为128KB 最大2G 设置的值必须为4KB的倍数,否则系统会自动修改成小于设置值的最大值（4KB）的倍数
1 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描，可能需要增加该值， 默认值为131072。
2 为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是128KB); 这个选项的设置值在必要时可以用SQL命令SET SESSION read_buffer_size = n命令加以改变。
3 read_buffer_size 是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区
4 设置以顺序扫描的方式扫描表数据的时候使用缓冲区的大小. 每个线程进行顺序扫描的时候都会产生该buffer ,而且同一个Query中如果有多个表进行全表扫描,会产生多个该buffer
所以在设置的时候尽量不要太高 避免因为并发太大造成内存不够
一般来说 可以尝试适当调大此参数看能否改善全表扫描的性能,在不同的平台上会有不同的表现,这主要与OS级别的文件系统IO大小有关,所以该参数的设置最好在真实环境下通过多次更改测试调整,才能选找到一个最佳值.
| read_rnd_buffer_size
global/session级别 可动态变更
默认值是256KB,最大值是4GB
类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)。
设置进行随机读的时候所使用的缓冲区，此参数和read_buffer_size所设置的buffer相反,一个是顺序读的时候使用,一个是随机读的时候使用,但是两者都是针对线程的设置，每个线程都可能产生两种buffer中的任何一个.
| sql_buffer_result
对于内存，MySQL没有过高要求，主要的除了每个连接使用的read_buffer_size 和sort_buffer_size尺寸的内存外，全局有一个Qcache使用query_cache_size尺寸的cache。MyISAM引擎主要使用的是key_buffer_size尺寸的内存,InnoDB主要使用内存就是 innodb_buffer_pool_size 尺寸内存。而且InnoDB可能还有一个2G内存使用限制（是否真没有这个问题有待验证）。所以依靠大规模增加内存提高MySQL性能未必有效
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27137314/viewspace-1191984/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/27137314/viewspace-1191984/
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
