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 recovery or from a backup plus the transaction log.
Ø
You can combine many statements and accept them all at the same time with the
COMMIT
statement (if autocommit is disabled).
Ø
You can execute
ROLLBACK
to ignore your changes (if autocommit is disabled).
Ø
If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.)
Ø
Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.
非事务型表具有以下优点：
Ø
Much faster
Ø
Lower disk space requirements
Ø
Less memory required to perform updates
4
．
MyISAM
存储引擎
下面
MyISAM
的参数是
MySQL
手册推荐的参数，据说适应于大部分情况。对于如何监视参数设置是否合理，仍然没有头绪。
max_connections=200
read_buffer_size=1M
read_rnd_buffer_size
＝
8M
sort_buffer_size=1M
Read_buffer_size
Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you might want to increse this value.
Read_rnd_buffer_size
When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
Bulk_insert_buffer_size
该参数于
4.0.3
中引入。
MyISAM
使用一个树型的缓冲区来加速大量的插入，如
INSERT…SELECT
，
INSERT…VALUES(…),VALUES(…),…,LOAD DATA INFILE
等。该参数指定了缓冲区的大小。缺省值为
8M
，设置为
0
则表示不使用该优化。
如果不使用
MyISAM
表，则可以将其设置为
0
。
5
．
InnoDB
存储引擎
参考了很多资料，都没有明确地表明如何优化
InnoDB
参数，以及如何监视这些参数设置是否合理，只有根据
MySQL
用户手册上面的介绍来进行设置。
innodb_buffer_pool_size
对于
InnoDB
表来说，
innodb_buffer_pool_size
的作用就相当于
key_buffer_size
对于
MyISAM
表的作用一样。
InnoDB
使用该参数指定大小的内存来缓冲数据和索引。对于单独的
MySQL
数据库服务器，最大可以把该值设置成物理内存的
80%
。
根据
MySQL
手册，对于
2G
内存的机器，推荐值是
1G
（
50%
）。
innodb_flush_log_at_trx_commit
该值指定
InnoDB
记录日志的方式。如果设置为
1
，则每个事务提交的时候，
MySQL
都会将事务日志写入磁盘。如果设置为
0
或者
2
，则大概每秒中将日志写入磁盘一次。（
还不清楚
0
和
2
的区别
）
实际测试发现，该值对插入数据的速度影响非常大，设置为
2
时插入
10000
条记录只需要
2
秒，设置为
0
时只需要
1
秒，而设置为
1
时则需要
229
秒。因此，
MySQL
手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据
MySQL
手册，在存在丢失最近部分事务的危险的前提下，可以把该值设为
0
。
innodb_log_file_size
The size of each log file in a log group. The default is 5MB. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But large log files also mean that recovery will be slower in case of a crash.
根据
MySQL
手册，推荐值是
innodb_buffer_pool_size
的
25%
。
注意：在重新设置该值时，好像要把原来的文件删除掉。
innodb_log_buffer_size
The size of the buffer that InnoDB uses to write to the log files on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger will save disk I/O.
根据
MySQL
手册，推荐值是
8M
。
innodb_additional_mem_pool_size
该参数指定
InnoDB
用来存储数据字典和其他内部数据结构的内存池大小。缺省值是
1M
。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，
MySQL
会在错误日志中写入一条警告信息。
根据
MySQL
手册，对于
2G
内存的机器，推荐值是
20M
。
SHOW INNODB STATUS
显示
InnoDB
存储引擎的状态