MySQL 可优化的一些参数详解

Mysql IT敢客 2年前 (2016-11-10) 8805次浏览 已收录 0个评论 扫描二维码

        mysql 里有些变量设置可用于 server 层面的调优,多是 cache 和 buffer 之类,有的是为整个 server 分配的大小,有些则是为每个 session/thread 分配的。为每个 session/thread 分配大小的变量尤其要注意不要设置太大。

sort_buffer_size

Each sessionthat needs to do a
sort allocates a buffer of thissize. sort_buffer_size isnot specific to any
storage engine and applies in ageneral manner foroptimization.

If you see manySort_merge_passes
per second in SHOW GLOBALSTATUS output, you can considerincreasing the
sort_buffer_size value to speedup ORDER BY or GROUP BYoperations that cannot be
improved with queryoptimization or improvedindexing.

As of MySQL5.6.4, the optimizer
tries to work out how much spaceis needed but canallocate more, up to the
limit. Before MySQL 5.6.4, theoptimizer allocates theentire buffer even if it
is not all needed

每一个 session 都会分配设定的 sort_buffer_size 大小的内存(基于此,此值不可以设置的过大,否则能会导致内存溢出)。此参数跟存储引擎和应用类型无关,是一个通用的优化项。

如果在SHOWGLOBAL STATUS看到每秒的Sort_merge_passes增长较大,你可以考虑适当增大sort_buffer_size,以求在无法进一步通过优化sql语句和优化索引的情况下取得更好的效率。

MySQL5.6.4及以上的版本,优化器尝试找出实际需要的内存(并不是一次性分配所有),但是可以根据实际需要分配超过设置的内存;在MySQL5.6.4之前的版本,优化器一次性分配所有设定的内存无论是否需要。

 

tmp_table_size

The maximum sizeof internal
in-memory temporary tables. (Theactual limit is determined as theminimum of
tmp_table_size andmax_heap_table_size.) If an in-memory temporarytable exceeds
the limit, MySQLautomatically converts it to an on-disk MyISAMtable. Increase
the value oftmp_table_size (and max_heap_table_size ifnecessary) if you do many
advancedGROUP BY queries and you have lots ofmemory.

内部临时表的最大内存使用量(实际的限制是由tmp_table_sizemax_heap_table_size两个参数中较小的一个决定的),如果内部临时表超过了限制,MySQL自动将其转换为MyISAM表存储于磁盘上。如果你经常做一些高级/复杂的GROUPBY语句并且你有比较大的内存,可以适当增加此变量的值(适当增加此值,可以减少磁盘临时表的创建,减少IO

 

max_heap_table_size

This variablesets the maximum
size to which user-created MEMORYtables are permitted togrow. The value of the
variable is used to calculateMEMORY table MAX_ROWSvalues. Setting this variable
has no effect on anyexisting MEMORY table,unless the table is re-created with a
statement such asCREATE TABLE or alteredwith ALTER TABLE or TRUNCATE TABLE. A
server restartalso sets the maximum sizeof existing MEMORY tables to the
globalmax_heap_table_size value.

这个变量设置允许用户创建的内存表的最大大小。变量的值用于计算内存表MAX_ROWS值。设置这个变量不影响任何现有的内存表,除非表被重新创建或者用ALTERTABLETRUNCATE TABLE修改。服务重启也会将现有内存表的最大行数设置为全局max_heap_table_size的值。

 

read_buffer_size

Each thread thatdoes a sequential
scan for a MyISAM tableallocates a buffer of this size (inbytes) for each table
it scans. If you domany sequential scans, you might wantto increase this value,
which defaults to131072. The value of this variable shouldbe a multiple of 4KB.
If it is set toa value that is not a multiple of 4KB,its value will be rounded
down to thenearest multiple of 4KB

This option isalso used in the
following context for all searchengines:

·        For
caching theindexes in atemporary file (not a temporary table), when
sorting  rows forORDER BY.

·        For
bulk insertinto partitions.

·        For
cachingresults of nestedqueries.

    and in one
otherstorage engine-specific way:to determine the memory block size for MEMORY
tables.Themaximum permissiblesetting for read_buffer_size is 2GB.

每个顺序/全表扫描MyISAM表的线程分配该值大小的内存,如果有很多全表扫描,你可能希望增加这个值,默认值是131072,这个变量的值应该是4K的倍数,如果不是,则MySQL自动将其减小为最近的4k倍数的值。

这个选项也可以在一下环境/条件下被其他存储引擎所使用:

·        当进行ORDER
BY
排序时,在一个临时文件缓存索引(不是临时表)

·        对于批量插入分区的操作

·        缓存嵌套查询的结果

在另一个存储引擎指定的方式下:决定内存表的内存块大小

这个值得最大值为2G

 

max_sort_length

The number ofbytes to use when
sorting data values. Only thefirst max_sort_length bytes ofeach value are used;
the rest are ignored.As of MySQL5.6.9, max_sort_length[503] is ignored for
integer, decimal, floating-point,and temporal data types.

对值进行排序时前多少字节被使用,每个值/结果只有最前面的max_sort_length字节被使用,其余的被忽略。对于MySQL5.6.9版本,该值对于integer, decimal,floating-point, and temporal 的数据类型无效。默认值是1k

 

myisam_max_sort_file_size

The maximum sizeof the temporary
file that MySQL is permitted touse while re-creating a MyISAMindex (during
REPAIR TABLE, ALTER TABLE, or LOADDATA INFILE). If the file sizewould be larger
than this value, the index iscreated using the key cacheinstead, which is
slower. The value is given inbytes.

The defaultvalue is 2GB. If
MyISAM index files exceed this sizeand disk space isavailable, increasing the
value may help performance. Thespace must beavailable in the file system
containing the directory where theoriginal indexfile is located.

MySQL允许的用于MyISAM表重建索引(在 REPAIRTABLE, ALTER TABLE, o LOAD DATA INFILE期间)的最大存储空间(在磁盘)。如果文件的大小超过此值,索引会用keycache代替,这样会慢一些。

默认是2G,如果MyISAM表的索引文件大小超过此值并且磁盘空间充裕,增加此变量的值可以提升效率。必须在原有的索引文件所在的分区上有磁盘空间。

 

未完。。。

附上一张 MySQL 内存使用量说明的图表:

MySQL 可优化的一些参数详解

 


IT 敢客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MySQL 可优化的一些参数详解
喜欢 (155)
[313176056@qq.com]
分享 (0)
IT敢客
关于作者:
“我所做的一切都是为了方便我的生活~~~“
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址