MySQL 可优化的一些参数详解

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_size和max_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值。设置这个变量不影响任何现有的内存表，除非表被重新创建或者用ALTERTABLE或TRUNCATE 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内存使用量说明的图表：