如何有效配置MySQL参数

共计 4891 个字符，预计需要花费 13 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下如何有效配置 MySQL 参数，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

1．获取当前配置参数
要优化配置参数，首先要了解当前的配置参数以及运行情况。使用下列命令可以获得目前服务器使用的配置参数：

mysqld –verbose –help

mysqladmin variables extended-status –u root –p

在 MySQL 控制台里面，运行下列命令可以获取状态变量的值：

mysql SHOW STATUS;

如果只要检查某几个状态变量，可以使用下列命令：

mysql SHOW STATUS LIKE‘[匹配模式]’; (可以使用 %、? 等)

2．优化参数
参数优化基于一个前提，就是在我们的数据库中通常都使用 InnoDB 表，而不使用 MyISAM 表。在优化 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 – 512

open_tables – 103

opened_tables – 1273

uptime – 4021421 (measured in seconds)

该案例中 table_cache 似乎设置得太高了。在峰值时间，打开表的数目比 table_cache 要少得多。

案例 2：该案例来自一台开发服务器。

table_cache – 64

open_tables – 64

opened-tables – 431

uptime – 1662790 (measured in seconds)

虽然 open_tables 已经等于 table_cache，但是相对于服务器运行时间来说，opened_tables 的值也非常低。因此，增加 table_cache 的值应该用处不大。

案例 3：该案例来自一个 upderperforming 的服务器

table_cache – 64

open_tables – 64

opened_tables – 22423

uptime – 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 – 402649088 (384M)

key_read_requests – 597579931

key_reads – 56188

案例 2：警报状态

key_buffer_size – 16777216 (16M)

key_read_requests – 597579931

key_reads – 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 表来说，正确配置参数对于 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 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能，但可减少表讹误或数据丢失的机会。

一般使用缺省值。

Binlog_cache_size

The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has binary log enabled(–log-bin option). If you often use big, multiple-statement transactions, you can increase this to get more performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.

3．存储引擎
在 MYSQL 3.23.0 版本中，引入了 MyISAM 存储引擎。它是一个非事务型的存储引擎，成为了 MYSQL 的缺省存储引擎。但是，如果使用设置向导来设置参数，则它会把 InnoDB 作为缺省的存储引擎。InnoDB 是一个事务型的存储引擎。

创建表的时候，可以为表指定存储引擎，语法如下：

CREATE TABLE t (i INT) ENGINE = MyISAM

CREATE TABLE t (i INT) TYPE = MyISAM

如果没有指定，则使用缺省的存储引擎。也可以使用 ALTER TABLE 来更换表引擎，语法如下：

ALTER TABLE t ENGINE = MyISAM

同一数据库中可以包含不同存储引擎的表。

事务型表具有以下特点：

Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic

以上是“如何有效配置 MySQL 参数”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！