Mysql性能优化全攻略.docx

1、Mysql性能优化全攻略mysql(SHOW STATUS)性能的检查和调优方记录慢查询:在f中配置mysqld; enable the slow query log, default 10 secondslog-slow-queries; log queries taking longer than 5 secondslong_query_time = 5; log queries that dont use indexes even if they take less than long_query_time; MySQL 4.1 and newer onlylog-queries-not

2、-using-indexes慢速查询日志都保存在 MySQL 数据目录中，名为 hostname-slow.log。如果希望使用一个不同的名字或路径，可以在 f 中使用 log-slow-queries = /new/path/to/file 实现此目的。阅读慢速查询日志最好是通过 mysqldumpslow 命令进行。指定日志文件的路径，就可以看到一个慢速查询的排序后的列表，并且还显示了它们在日志文件中出现的次数。一个非常有用的特性是 mysqldumpslow 在比较结果之前，会删除任何用户指定的数据，因此对同一个查询的不同调用被计为一次；这可以帮助找出需要工作量最多的查询。对查询进行缓存

3、:默认不启用启用方法 将 query_cache_size = 32M 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存。监视查询缓存的统计信息mysql SHOW STATUS LIKE qcache%;+-+-+| Variable_name | Value |+-+-+| Qcache_free_blocks | 5216 | Qcache_free_memory | 14640664 | Qcache_hits | 2581646882 | Qcache_inserts | 360210964 | Qcache_lowmem_prunes | 281680433 |

4、Qcache_not_cached | 79740667 | Qcache_queries_in_cache | 16927 | Qcache_total_blocks | 47042 |+-+-+8 rows in set (0.00 sec)Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。Qcache_free_memory 缓存中的空闲内存。Qcache_hits 每次查询在缓存中命中时就增大。Qcache_inserts 每次插入一个查询时就增大。命中次数除以插入次数就

5、是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。Qcache_not_cached 不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。Qcache_queries_in_cache 当前缓存的查询（和响应）的数量。Qcache_total_bloc

6、ks 缓存中块的数量。强制限制:在f中设置set-variable=max_connections=500set-variable=wait_timeout=10 #终止空闲超过10秒的连接max_connect_errors = 100 #主机连接到mysql 100次无法建立连接,就锁定直到flush hosts 之后.查看当前最大连接数mysql show status like max_used_connections;缓冲区和缓存:设置打开文件缓存/etc/mysqld.conftable_cache=5000显示打开表的活动mysql SHOW STATUS LIKE open%t

7、ables;+-+-+| Variable_name | Value |+-+-+| Open_tables | 5000 | Opened_tables | 195 |+-+-+2 rows in set (0.00 sec)如果 Open_tables 比 table_cache 设置小很多，就说明该值太大了.设置线程缓存在 f中thread_cache = 40显示线程使用统计信息mysql SHOW STATUS LIKE threads%;+-+-+| Variable_name | Value |+-+-+| Threads_cached | 27 | Threads_connec

8、ted | 15 | Threads_created | 838610 | Threads_running | 3 |+-+-+4 rows in set (0.00 sec)如果连续执行show status命令 Threads_created 都快速增加 就应该增加线程缓存.设置关键字缓存key_buffer = 384M确定关键字效率mysql show status like %key_read%;+-+-+| Variable_name | Value |+-+-+| Key_read_requests | 163554268 | Key_reads | 98247 |+-+-+2

9、rows in set (0.00 sec)key_reads 命中磁盘的请求个数key_read_requests 是请求总数如果1000个请求中有超过1个命中磁盘,就应该考虑增大关键字缓存.设置临时表临时表太大就会写入磁盘在f中设置tmp_table_sizemax_heap_table_size显示临时表mysql SHOW STATUS LIKE created_tmp%;+-+-+| Variable_name | Value |+-+-+| Created_tmp_disk_tables | 30660 | Created_tmp_files | 2 | Created_tmp_t

10、ables | 32912 |+-+-+3 rows in set (0.00 sec)设置每个会话f中修改 或通过代码修改sort_buffer_size = 4M 将排序缓冲区设置为 4MBmysql SHOW STATUS LIKE sort%;+-+-+| Variable_name | Value |+-+-+| Sort_merge_passes | 1 | Sort_range | 79192 | Sort_rows | 2066532 | Sort_scan | 44006 |+-+-+4 rows in set (0.00 sec)如果 sort_merge_passes 很

11、大，就表示需要注意 sort_buffer_size确定表的扫描比率mysql SHOW STATUS LIKE com_select;+-+-+| Variable_name | Value |+-+-+| Com_select | 318243 |+-+-+1 row in set (0.00 sec)mysql SHOW STATUS LIKE handler_read_rnd_next;+-+-+| Variable_name | Value |+-+-+| Handler_read_rnd_next | 165959471 |+-+-+1 row in set (0.00 sec)H

12、andler_read_rnd_next / Com_select 得出了表扫描比率 在本例中是 521:1。如果该值超过 4000，就应该查看 read_buffer_size，例如 read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！3个工具mytopmysqlardmysqlreportflush show函数介绍benchmarkkey_reads/Key_read_requests0.01 OKkey_writes/Key_write_requests SHOW STATUS;如果只要检查某几个状态变量，可以使用下列命令

13、：mysql SHOW STATUS LIKE 匹配模式; ( 可以使用%、?等 )2优化参数参数优化基于一个前提，就是在我们的数据库中通常都使用InnoDB表，而不使用MyISAM表。在优化MySQL时，有两个配置参数是最重要的，即table_cache和key_buffer_size。table_cachetable_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现ope

14、n_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE Open%tables获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。对于有1G内存的机器，推荐值是128256。案例1：该案例来自一个不是特别繁忙的服务器table_cache 512open_tables 103opened_tables 1273uptime 4021421 (measured in seconds)该

15、案例中table_cache似乎设置得太高了。在峰值时间，打开表的数目比table_cache要少得多。案例2：该案例来自一台开发服务器。table_cache 64open_tables 64opened-tables 431uptime 1662790 (measured in seconds)虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。案例3：该案例来自一个upderperforming的服务器table_cache 64open_tables 64ope

16、ned_tables 22423uptime 19538该案例中table_cache设置得太低了。虽然运行时间不到6小时，open_tables达到了最大值，opened_tables的值也非常高。这样就需要增加table_cache的值。key_buffer_sizekey_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好

17、（上述状态值可以使用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 597579931key_reads 56188案例2：警报状态key_buffer_size 16777216 (1

18、6M)key_read_requests 597579931key_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 LI

19、KE 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指定是否使用

20、查询缓冲，可以设置为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 )二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( Re

21、plication )的话，也需要使用二进制日志传送修改情况。开启二进制日志，需要设置参数log-bin。log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字索引，每次启动服务时，都会重新生成一个新的二进制文件。此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定记录的数据库；使用binlog-ignore-db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写

22、，在指定数据库时必须全部使用小写名字，否则不会起作用。在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将自己产生缺省文件名

23、。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/ibd

24、ata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件放在不同的磁盘上。数据首先放在ibdata1中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以8MB为单位自动增长。如果磁盘满了，你需要在另外的磁盘上面增加一个数据文件。为此，你需要查看最后一个文件的尺寸，然后计算最接近的整数（MB）。然后手工修改该文件的大小，并添加新的数据文件。例如：假设ibdata2已经有109MB数据，那么可以修改如下：innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/

25、disk3/ibdata3:500M:autoextendflush_time如果系统有问题并且经常锁死或重新引导，应将该变量设置为非零值，这将导致服务器按flush_time 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能，但可减少表讹误或数据丢失的机会。一般使用缺省值。Binlog_cache_sizeThe 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

26、 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 tun

27、ing the size of this variable.3存储引擎在MYSQL 3.23.0版本中，引入了MyISAM存储引擎。它是一个非事务型的存储引擎，成为了MYSQL的缺省存储引擎。但是，如果使用设置向导来设置参数，则它会把InnoDB作为缺省的存储引擎。InnoDB是一个事务型的存储引擎。创建表的时候，可以为表指定存储引擎，语法如下：CREATE TABLE t (i INT) ENGINE = MyISAMCREATE TABLE t (i INT) TYPE = MyISAM如果没有指定，则使用缺省的存储引擎。也可以使用ALTER TABLE来更换表引擎，语法如下：ALTER TABLE t ENGINE = MyISAM同一数据库中可以包含不同存储引擎的表。事务型表具有以下特点：? Safer. Even if MySQL crashes or you get hardware problems, you c