Mysql性能优化全攻略.docx

Mysql性能优化全攻略.docx

《Mysql性能优化全攻略.docx》由会员分享，可在线阅读，更多相关《Mysql性能优化全攻略.docx（19页珍藏版）》请在冰豆网上搜索。

Mysql性能优化全攻略

mysql（SHOWSTATUS）性能的检查和调优方

记录慢查询:

在f中配置

[mysqld]

;enabletheslowquerylog,default10seconds

log-slow-queries

;logqueriestakinglongerthan5seconds

long_query_time=5

;logqueriesthatdon'tuseindexeseveniftheytakelessthanlong_query_time

;MySQL4.1andneweronly

log-queries-not-using-indexes

慢速查询日志都保存在MySQL数据目录中，名为hostname-slow.log。

如果希望使用一个不同的名字或路径，可以在f中使用log-slow-queries=/new/path/to/file实现此目的。

阅读慢速查询日志最好是通过mysqldumpslow命令进行。

指定日志文件的路径，就可以看到一个慢速查询的排序后的列表，并且还显示了它们在日志文件中出现的次数。

一个非常有用的特性是mysqldumpslow在比较结果之前，会删除任何用户指定的数据，因此对同一个查询的不同调用被计为一次；这可以帮助找出需要工作量最多的查询。

对查询进行缓存:

默认不启用

启用方法将query_cache_size=32M添加到/etc/my.conf中可以启用32MB的查询缓存。

监视查询缓存的统计信息

mysql>SHOWSTATUSLIKE'qcache%';

+-------------------------+------------+

|Variable_name          |Value     |

+-------------------------+------------+

|Qcache_free_blocks     |5216      |

|Qcache_free_memory     |14640664  |

|Qcache_hits            |2581646882|

|Qcache_inserts         |360210964|

|Qcache_lowmem_prunes   |281680433|

|Qcache_not_cached      |79740667  |

|Qcache_queries_in_cache|16927     |

|Qcache_total_blocks    |47042     |

+-------------------------+------------+

8rowsinset（0.00sec）

Qcache_free_blocks   缓存中相邻内存块的个数。

数目大说明可能有碎片。

FLUSHQUERYCACHE会对缓存中的碎片进行整理，从而得到一个空闲块。

Qcache_free_memory   缓存中的空闲内存。

Qcache_hits   每次查询在缓存中命中时就增大。

Qcache_inserts   每次插入一个查询时就增大。

命中次数除以插入次数就是不中比率；用1减去这个值就是命中率。

在上面这个例子中，大约有87%的查询都在缓存中命中。

Qcache_lowmem_prunes   缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。

这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。

（上面的free_blocks和free_memory可以告诉您属于哪种情况）。

Qcache_not_cached   不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句。

Qcache_queries_in_cache   当前缓存的查询（和响应）的数量。

Qcache_total_blocks   缓存中块的数量。

强制限制:

在f中设置

set-variable=max_connections=500

set-variable=wait_timeout=10        #终止空闲超过10秒的连接

max_connect_errors=100             #主机连接到mysql100次无法建立连接,就锁定直到flushhosts之后.

查看当前最大连接数 

mysql>showstatuslike'max_used_connections';

缓冲区和缓存:

设置打开文件缓存

/etc/mysqld.conf

table_cache=5000

显示打开表的活动

mysql>SHOWSTATUSLIKE'open%tables';

+---------------+-------+

|Variable_name|Value|

+---------------+-------+

|Open_tables  |5000|

|Opened_tables|195  |

+---------------+-------+

2rowsinset（0.00sec）

如果Open_tables比table_cache设置小很多，就说明该值太大了.

设置线程缓存

在f中

thread_cache=40

显示线程使用统计信息

mysql>SHOWSTATUSLIKE'threads%';

+-------------------+--------+

|Variable_name    |Value|

+-------------------+--------+

|Threads_cached   |27    |

|Threads_connected|15    |

|Threads_created  |838610|

|Threads_running  |3     |

+-------------------+--------+

4rowsinset（0.00sec）

如果连续执行showstatus命令Threads_created都快速增加就应该增加线程缓存.

设置关键字缓存

key_buffer=384M

确定关键字效率

mysql>showstatuslike'%key_read%';

+-------------------+-----------+

|Variable_name    |Value    |

+-------------------+-----------+

|Key_read_requests|163554268|

|Key_reads        |98247    |

+-------------------+-----------+

2rowsinset（0.00sec）

key_reads命中磁盘的请求个数

key_read_requests是请求总数

如果1000个请求中有超过1个命中磁盘,就应该考虑增大关键字缓存.

设置临时表

临时表太大就会写入磁盘

在f中设置

tmp_table_size

max_heap_table_size

显示临时表

mysql>SHOWSTATUSLIKE'created_tmp%';

+-------------------------+-------+

|Variable_name          |Value|

+-------------------------+-------+

|Created_tmp_disk_tables|30660|

|Created_tmp_files      |2    |

|Created_tmp_tables     |32912|

+-------------------------+-------+

3rowsinset（0.00sec）

设置每个会话

f中修改或通过代码修改

sort_buffer_size=4M将排序缓冲区设置为4MB

mysql>SHOWSTATUSLIKE"sort%";

+-------------------+---------+

|Variable_name    |Value  |

+-------------------+---------+

|Sort_merge_passes|1      |

|Sort_range       |79192  |

|Sort_rows        |2066532|

|Sort_scan        |44006  |

+-------------------+---------+

4rowsinset（0.00sec）

如果sort_merge_passes很大，就表示需要注意sort_buffer_size

确定表的扫描比率

mysql>SHOWSTATUSLIKE"com_select";

+---------------+--------+

|Variable_name|Value|

+---------------+--------+

|Com_select   |318243|

+---------------+--------+

1rowinset（0.00sec）

mysql>SHOWSTATUSLIKE"handler_read_rnd_next";

+-----------------------+-----------+

|Variable_name        |Value    |

+-----------------------+-----------+

|Handler_read_rnd_next|165959471|

+-----------------------+-----------+

1rowinset（0.00sec）

Handler_read_rnd_next/Com_select得出了表扫描比率——在本例中是521:

1。

如果该值超过4000，就应该查看read_buffer_size，例如read_buffer_size=4M。

如果这个数字超过了8M，就应该与开发人员讨论一下对这些查询进行调优了！

3个工具

mytop

mysqlard

mysqlreport

flushshow

函数介绍

benchmark

key_reads/Key_read_requests<0.01  OK

key_writes/Key_write_requests<1OK

Optimizetabletable_name

对表自动优化

Explainselectstatement

1．获取当前配置参数

要优化配置参数，首先要了解当前的配置参数以及运行情况。

使用下列命令可以获得目前服务器使用的配置参数：

mysqld–verbose–help

mysqladminvariablesextended-status–uroot–p

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

mysql>SHOWSTATUS;

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

mysql>SHOWSTATUSLIKE‘[匹配模式]’;（可以使用%、?

等）

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的值了（上述状态值可以使用SHOWSTATUSLIKE‘Open%tables’获得）。

注意，不能盲目地把table_cache设置成很大的值。

如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

对于有1G内存的机器，推荐值是128－256。

案例1：

该案例来自一个不是特别繁忙的服务器

table_cache–512

open_tables–103

opened_tables–1273

uptime–4021421（measuredinseconds）

该案例中table_cache似乎设置得太高了。

在峰值时间，打开表的数目比table_cache要少得多。

案例2：

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

table_cache–64

open_tables–64

opened-tables–431

uptime–1662790（measuredinseconds）

虽然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更好（上述状态值可以使用SHOWSTATUSLIKE‘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设置是否合理（上述状态值可以使用SHOWSTATUSLIKE‘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。

开启二进制日志（BinaryLog）

二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。

另外，如果做同步复制（Replication）的话，也需要使用二进制日志传送修改情况。

开启二进制日志，需要设置参数log-bin。

log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。

MySQL会在文件名后面自动添加数字索引，每次启动服务时，都会重新生成一个新的二进制文件。

此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定记录的数据库；使用binlog-ignore-db可以指定不记录的数据库。

注意的是：

binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。

而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。

在MySQL中使用SHOWMASTERSTATUS命令可以查看目前的二进制日志状态。

开启慢查询日志（slowquerylog）

慢查询日志对于跟踪有问题的查询非常有用。

它记录所有查过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

ThesizeofthecachetoholdtheSQLstatementsforthebinarylogduringatransaction.Abinarylogcacheisallocatedforeachclientiftheserversupportsanytransactionalstorageenginesandiftheserverhasbinarylogenabled（–log-binoption）.Ifyouoftenusebig,multiple-statementtransactions,youcanincreasethistogetmoreperformance.TheBinlog_cache_useandBinlog_cache_disk_usestatusvariablescanbeusefulfortuningthesizeofthisvariable.

3．存储引擎

在MYSQL3.23.0版本中，引入了MyISAM存储引擎。

它是一个非事务型的存储引擎，成为了MYSQL的缺省存储引擎。

但是，如果使用设置向导来设置参数，则它会把InnoDB作为缺省的存储引擎。

InnoDB是一个事务型的存储引擎。

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

CREATETABLEt（iINT）ENGINE=MyISAM

CREATETABLEt（iINT）TYPE=MyISAM

如果没有指定，则使用缺省的存储引擎。

也可以使用ALTERTABLE来更换表引擎，语法如下：

ALTERTABLEtENGINE=MyISAM

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

事务型表具有以下特点：

?

       Safer.EvenifMySQLcrashesoryougethardwareproblems,youc