Mysql几个参数的优化（转）
fjlh 发表于 2006-8-23 10:20:00
假设你已经知道my.cnf文件以及如何设置变量，如果没有的话，请参照先前的文章。
key_buffer_size
key_buffer_size有可能是最有用的调整的参数，key_buffer_size值越大，你的内存存储MyISAM表的索引
越大。大多数的查询都需要使用索引，存储在内存的速度要比使用硬盘文件快得多，此参数的重要性不用
多说。
在一个专门的mysql服务器上，key_buffer_size的值应该至少为整个内存值的四分之一，但是不要超过一
半。理想值应该是足够容纳所有的索引（mysql服务器所有的.MYI文件的大小）。如果你不能设置这么大
的话，最好的优化方法就是参照key_reads和key_read_requests参数。后者是所有的索引请求的总和，而
前者是所有的必须从硬盘读取的索引请求的总和。如果你每100或更多个请求有1个请求从硬盘索引，参照
情节1，每个同样的查询耗费的时间有很小的差距。
情节1：
mysql> SHOW STATUS LIKE '%key_read%';+-------------------+------------+| Variable_name |
Value |+-------------------+------------+| Key_read_requests | 3606100254 || Key_reads |
2594030 |+-------------------+------------+ mysql> SHOW STATUS LIKE '%key_read%';+---------
----------+------------+| Variable_name | Value |+-------------------+------------+|
Key_read_requests | 3606102741 || Key_reads | 2594030 |+-------------------+------------+这
是一个正常的比率，key_read_requests/key_read大概是1400/1。在这两个例子中没有一个需要使用硬盘
索引。在这个服务器中，key_buffer设置为768M，而所有的内存值是3GB。
情节二：
mysql> SHOW STATUS LIKE '%key_read%';+-------------------+-----------+| Variable_name |
Value |+-------------------+-----------+| Key_read_requests | 609601541 || Key_reads |
46729832 |+-------------------+-----------+在情节2，这个比例是惊人的
key_read_requests/key_read大概是13/1，这个服务器的key_buffer是16M而总内存是64M。如果遇到相似
情况，很明显下次硬件升级应该考虑内存。内存始终都是提高系统性能的主要硬件。
Query Cache
Mysql 4增加了一项极为有用的工具query search。
。。。。
table cache
table_cache保留了有用的参数用来调整。每次读取mysql的表，将它放到cache里。如果系统读取很多表
，把它读取到cache中将会比直接从硬盘读取更快。需要注意的是打开的标有可能比服务器中数据库的表
要多。Mysql的多线程有可能会在同一个表中同时运行多个查询，每一个线程会打开一个表。系统是否需
要赠家此参数值最好的方法是查询高峰时open_tables的值。如果发现它保持在table_cache值左右然后
open_tables开始迅速增加，如果有足够的内存你应该增加table_cache的值。看一下在高峰期下面的3个
场景：
Scenario 1
mysql> SHOW STATUS LIKE 'open%tables%';+---------------+-------+| Variable_name | Value |+-
--------------+-------+| Open_tables | 98 || Opened_tables | 1513 |+---------------+-------
+table_cache设置为512，服务器已经运行了很长时间。如果服务器在别的地方内存紧张的话，
table_cache值可以安全的减小。
Scenario 2
mysql> SHOW STATUS LIKE 'open%tables%';+---------------+-------+| Variable_name | Value |+-
--------------+-------+| Open_tables | 64 || Opened_tables | 517 |+---------------+-------+
此服务器table_cache设置为64，服务器已经运行了很长时间，即使open_tables值达到其设置最大值，考
虑到服务器已经运行了很长时间，open_tables仍然很小。table_cache增加也许不会带来速度的明显提升
。这是一个开发服务器的例子。
Scenario 3
mysql> SHOW STATUS LIKE 'open%tables%';+---------------+-------+| Variable_name | Value |+-
--------------+-------+| Open_tables | 64 || Opened_tables | 13918 |+---------------+------
-+此服务器table_cache设置为64，服务器已经运行了很长时间，很明显table_cache值太小。
open_tables达到了设置的最大值，opened_tables值也更是惊人，如果有足够的内存，就应该增加
table_cache的值。
sort_buffer
sort_buffer对提高myisamchk操作性能非常有用（这是为什么在配置文件设置更大的值的目的），但是它
在对大量的纪录进行分类时将非常有用。在my-huge.cnf文件中默认的大小是2M，我在3G内存运行大量的
分类操作的服务器上成功的将它增加到9M。
read_rnd_buffer_size
read_rnd_buffer_size用于分类操作之后读经过分类的列。如果你在查询中使用了大量的ORDER BY语句，
增加该值将改善数据库的性能。记住：不像key_buffer_size 和table_cache的缓存可以分配到每个线程
。这个变量在MySQL 4.0.3被命名为record_rnd_buffer。默认的情况下它的大小和read_buffer_size,相
等，默认值是128KB。一般的标准每1KB对应1M内存，比如3M对应3GB服务器内存。
tmp_table_size
前面知道key_buffer_size是相对于MyISAM表的，那么对于InnoDB表，起作用的是
innodb_buffer_pool_size。因为硬盘速度的原因你将尽可能增加它的大小以减少硬盘的使用。在一个专
门存储InnoDB 表的 mysql服务器上，你可以将它的大小最大设置为内存大小的80%。
innodb_additional_mem_pool_size
此参数存储内部数据结构。请确认它的大小能足够容纳所有的InnoDB表（如果该大小不够的话你将在错误
日志里看到该警告）。
结论
Mysql有许多参数，但是我讨论的是经常使用的或者是在性能提升的关键参数。首先做的应该是经常优化
查询，合适的索引和自习的编写查询语句会对性能提升有益。如果你对编写的查询语句很有信心，那么这
将是增加mysql在内存中的比重的时候了，如果需要的话请增加内存。许多原先表现不佳的服务器在经过
如此优化后性能显著提升。
有效配置MySQL参数
作者：cqj1314 提交日期：2006-4-7 12:00:00
??[转载] 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的查询，如果需要，
还可以记录不使用索引的记录。下面是一个慢查询日志的例子：
??
??aspectratio="t" v:ext="edit">
??
??开启慢查询日志，需要设置参数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作为缺省
