对MySQL配置参数 my.ini/my.cnf的详细解析
时间：2020-10-15 23:16:29|栏目：Mysql|点击： 次
以下的文章主要描述的是对MySQL配置参数 my.ini/my.cnf的详细解析，我们主要是以实例演示的方式来对MySQL配置参数 my.ini/my.cnf的实际操作步骤进行说明，以下就是相关内容的具体描述。
1．获取当前配置参数
要优化MySQL配置参数，首先要了解当前的配置参数以及运行情况。使用下列命令可以获得目前服务器使用的配置参数：
mysqld ?Cverbose ?Chelp
mysqladmin variables extended-status ?Cu root ?Cp
在MySQL控制台里面，运行下列命令可以获取状态变量的值：
mysql> SHOW STATUS; 如果只要检查某几个状态变量，可以使用下列命令：
mysql> SHOW STATUS LIKE ‘[匹配模式]'; ( 可以使用%、?等 )
2．优化参数
参数优化基于一个前提，就是在我们的数据库中通常都使用InnoDB表，而不使用MyISAM表。在优化MySQL时，有两个MySQL配置参数是最重要的，即table_cache和key_buffer_size。
table_cache
table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区 中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决 定是否需要增加table_cache的值。如果你发现 open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状 态值可以使用SHOW STATUS LIKE ‘Open%tables'获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能 不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
案例1：该案例来自一个不是特别繁忙的服务器
table_cache ?C 512
open_tables ?C 103
opened_tables ?C 1273
uptime ?C 4021421 (measured in seconds)
该案例中table_cache似乎设置得太高了。在峰值时间，打开表的数目比table_cache要少得多。
案例2：该案例来自一台开发服务器。
table_cache ?C 64
open_tables ?C 64
opened-tables ?C 431
uptime ?C 1662790 (measured in seconds)
虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。
案例3：该案例来自一个upderperforming的服务器
table_cache ?C 64
open_tables ?C 64
opened_tables ?C 22423
uptime ?C 19538
该案例中table_cache设置得太低了。虽然运行时间不到6小时，open_tables达到了最大值，opened_tables的值也非常高。这样就需要增加table_cache的值。
key_buffer_size
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读 的速度。通过检查状态值 Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。
案例1：健康状况
key_buffer_size ?C 402649088 (384M)
key_read_requests ?C 597579931
key_reads ?C 56188
案例2：警报状态
key_buffer_size ?C 16777216 (16M)
key_read_requests ?C 597579931
key_reads ?C 53832731
案例1中比例低于1:10000，是健康的情况；案例2中比例达到1:11，警报已经拉响。
优化query_cache_size
从4.0.1开始，MySQL配置参数提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238％的效率。
通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理 （上述状态值可以使用SHOW STATUS LIKE ‘Qcache%'获得）。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也 非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；
如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反 而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
与查询缓冲有关的参数还有query_cache_type、 query_cache_limit、 query_cache_min_res_unit。query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是 SESSION级的变量。
query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。 query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值 Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit。
开启二进制日志( Binary Log )
二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。
开启二进制日志，需要设置参数log-bin。log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字索引，每次启动服务时，都会重新生成一个新的二进制文件。
此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以 指定记录的数据库；使用binlog-ignore- db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语 句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。
在MySQL中使用SHOW MASTER STATUS命令可以查看目前的二进制日志状态。
开启慢查询日志( slow query log )
慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询，如果需要，还可以记录不使用索引的记录。下面是一个慢查询日志的例子：
开启慢查询日志，需要设置参数log_slow_queries、 long_query_times、log-queries-not-using -indexes。log_slow_queries指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。 long_query_times指定慢查询的阈值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的 参数，它指示记录不使用索引的查询。
配置InnoDB
相对于MyISAM表来说，正确MySQL配置参数对于InnoDB表更加关键。其中，最重要的参数是 innodb_data_file_path。它指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一 个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。例如：
innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend
两个数据文件放在不同的磁盘上。数据首先放在ibdata1中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以8MB为单位自动增长。
如果磁盘满了，你需要在另外的磁盘上面增加一个数据文件。为此，你需要查看最后一个文件的尺寸，然后计算最接近的整数（MB）。然后手工修改该文件的大小，并添加新的数据文件。例如：假设ibdata2已经有109MB数据，那么可以修改如下：
innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend
flush_time
如果系统有问题并且经常锁死或重新引导，应将该变量设置为非零值，这将导致服务器按flush_time 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能，但可减少表讹误或数据丢失的机会。
1．获取当前配置参数
要优化MySQL配置参数，首先要了解当前的配置参数以及运行情况。使用下列命令可以获得目前服务器使用的配置参数：
复制代码 代码如下:
mysqld ?Cverbose ?Chelp
mysqladmin variables extended-status ?Cu root ?Cp
在MySQL控制台里面，运行下列命令可以获取状态变量的值：
复制代码 代码如下:
mysql> SHOW STATUS; 如果只要检查某几个状态变量，可以使用下列命令：
mysql> SHOW STATUS LIKE ‘[匹配模式]'; ( 可以使用%、?等 )
2．优化参数
参数优化基于一个前提，就是在我们的数据库中通常都使用InnoDB表，而不使用MyISAM表。在优化MySQL时，有两个MySQL配置参数是最重要的，即table_cache和key_buffer_size。
table_cache
table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区 中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决 定是否需要增加table_cache的值。如果你发现 open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状 态值可以使用SHOW STATUS LIKE ‘Open%tables'获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能 不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
案例1：该案例来自一个不是特别繁忙的服务器
复制代码 代码如下:
table_cache ?C 512
open_tables ?C 103
opened_tables ?C 1273
uptime ?C 4021421 (measured in seconds)
该案例中table_cache似乎设置得太高了。在峰值时间，打开表的数目比table_cache要少得多。
案例2：该案例来自一台开发服务器。
复制代码 代码如下:
table_cache ?C 64
open_tables ?C 64
opened-tables ?C 431
uptime ?C 1662790 (measured in seconds)
虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。
案例3：该案例来自一个upderperforming的服务器
复制代码 代码如下:
table_cache ?C 64
open_tables ?C 64
opened_tables ?C 22423
uptime ?C 19538
该案例中table_cache设置得太低了。虽然运行时间不到6小时，open_tables达到了最大值，opened_tables的值也非常高。这样就需要增加table_cache的值。
key_buffer_size
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读 的速度。通过检查状态值 Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。
案例1：健康状况
复制代码 代码如下:
key_buffer_size ?C 402649088 (384M)
key_read_requests ?C 597579931
key_reads ?C 56188
案例2：警报状态
复制代码 代码如下:
key_buffer_size ?C 16777216 (16M)
key_read_requests ?C 597579931
key_reads ?C 53832731
案例1中比例低于1:10000，是健康的情况；案例2中比例达到1:11，警报已经拉响。
优化query_cache_size
从4.0.1开始，MySQL配置参数提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238％的效率。
通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理 （上述状态值可以使用SHOW STATUS LIKE ‘Qcache%'获得）。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也 非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；
如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反 而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
与查询缓冲有关的参数还有query_cache_type、 query_cache_limit、 query_cache_min_res_unit。query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是 SESSION级的变量。
query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。 query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值 Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit。
开启二进制日志( Binary Log )
二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。
开启二进制日志，需要设置参数log-bin。log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字索引，每次启动服务时，都会重新生成一个新的二进制文件。
此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以 指定记录的数据库；使用binlog-ignore- db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语 句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。
在MySQL中使用SHOW MASTER STATUS命令可以查看目前的二进制日志状态。
开启慢查询日志( slow query log )
慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询，如果需要，还可以记录不使用索引的记录。下面是一个慢查询日志的例子：
开启慢查询日志，需要设置参数log_slow_queries、 long_query_times、log-queries-not-using -indexes。log_slow_queries指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。 long_query_times指定慢查询的阈值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的 参数，它指示记录不使用索引的查询。
配置InnoDB
相对于MyISAM表来说，正确MySQL配置参数对于InnoDB表更加关键。其中，最重要的参数是 innodb_data_file_path。它指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一 个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。例如：
复制代码 代码如下:
innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend
两个数据文件放在不同的磁盘上。数据首先放在ibdata1中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以8MB为单位自动增长。
如果磁盘满了，你需要在另外的磁盘上面增加一个数据文件。为此，你需要查看最后一个文件的尺寸，然后计算最接近的整数（MB）。然后手工修改该文件的大小，并添加新的数据文件。例如：假设ibdata2已经有109MB数据，那么可以修改如下：
复制代码 代码如下:
innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend
flush_time
如果系统有问题并且经常锁死或重新引导，应将该变量设置为非零值，这将导致服务器按flush_time 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能，但可减少表讹误或数据丢失的机会。
