MySQL服务器优化本领手册

关于 MySQL调优

有3种门径可以也许加速 MySQL 任事器的运转速率，效力从低到高顺次为：

交换有题目的硬件。对 MySQL 过程的设置举办调优。 对查询举办优化。

交换有题目的硬件平常是我们的第一考虑，首要缘故原由是数据库会占用大批资本。不外这类处理打算也就仅限于此了。现实上，您平常可以也许让中央处置惩罚器(CPU)或磁盘速率加倍，也可以也许让内存增大4到8倍。

第二种门径是对 MySQL 任事器(也称为 mysqld)举办调优。对这个过程举办调优意味着恰当地分配内存，并让 mysqld 懂得将会承受何品种型的负载。加速磁盘运转速率不如削减所需的磁盘拜访次数。类似地，确保 MySQL 过程准确操纵就意味着它斲丧在任事查询上的时候要多于斲丧在处置惩罚背景义务(如处置惩罚且自磁盘表或翻开和封闭文件)上的时候。对 mysqld 举办调优是本文的重点。

最好的门径是确保查询已举办了优化。这意味着对表应用了恰当的索引，查询是依照可以也许充实哄骗 MySQL 功效的方法来编写的。虽然本文并没有包罗查询调优方面的内容(很多著作中已针对这个主题举办了切磋)，不外它会设置装备陈设 mysqld 来讲述能够需要举办调优的查询。

虽然已为这些义务指派了顺序，但是依然要看重硬件和 mysqld 的设置以利于恰当地调优查询。机械速率慢也就而已，我曾见过速率很快的机械在运转设想杰出的查询时因为负载太重而失利，因为 mysqld 被大批忙碌的义务所占用而不克不及任事查询。

记载慢速查询

在一个 SQL 任事器中，数据表都是留存在磁盘上的。索引为任事器供给了一种在表中查找特定数据行的门径，而不消搜刮所有表。当必需要搜刮所有表时，就称为表扫描。平常来说，您能够只但愿获得表中数据的一个子集，因而全表扫描会糟蹋大批的磁盘 I/O，因而也就会糟蹋大批时候。当必需对数据举办衔接时，这个题目就愈加庞大了，因为必需要对衔接两头的多行数据举办比力。

虽然，表扫描实在不老是会带来题目；偶然读取所有表反而会比从中遴选出一部份数据愈加有效(任事器过程中查询打算器用来作出这些决意)。如果索引的使 用效力很低，或根本就不克不及应用索引，则会减慢查询速率，而且跟着任事器上的负载和表巨细的增加，这个题目会变得愈加明显。履行时候凌驾给定时候规模的查 询就称为慢速查询。

您可以也许设置装备陈设 mysqld 将这些慢速查询记载到恰当定名的慢速查询日记中。治理员然后会检察这个日记来扶助他们肯定应用法式中有哪些部份需要进一步查询拜访。清单 1 给出了要启用慢速查询日记需要在 my.cnf 中所做的设置装备陈设。

清单 1、启用 MySQL 慢速查询日记

[mysqld]

; enable the slow query log, default 10 seconds

log-slow-queries

; log queries taking longer than 5 seconds

long_query_time = 5

; log queries that don't use indexes even if they take less than long_query_time

; MySQL 4.1 and newer only

log-queries-not-using-indexes

这三个设置一路应用，可以也许记载履行时候凌驾 5 秒和没有应用索引的查询。请看重有关 log-queries-not-using-indexes 的告诫：您必需应用 MySQL 4.1 或更高版本。慢速查询日记都留存在 MySQL 数据目次中，名为 hostname-slow.log。如果但愿应用一个不同的名字或途径，可以也许在 my.cnf 中应用 log-slow-queries = /new/path/to/file 完成此目标。

浏览慢速查询日记最好是经由过程 mysqldumpslow 号令举办。指定日记文件的途径，就可以也许看到一个慢速查询的排序后的列表，而且还浮现,了它们在日记文件中出现的次数。一个非常有效的特性是 mysqldumpslow 在比力成果之前，会删除任何用户指定的数据，因而对同一个查询的不同挪用被计为一次；这可以也许扶助找出需要义务量最多的查询。

对查询举办缓存

很多 LAMP 应用法式都严峻依赖于数据库，但却会重复履行不异的查询。每次履行查询时，数据库都必需要履行不异的义务 —— 对查询举办阐发，肯定若何履行查询，从磁盘中加载信息，然后将成果前往给客户机。MySQL 有一个特性称为查询缓存，它将(后面会用到的)查询成果留存在内存中。在很多状态下，这会极大地进步功能。不外，题目是查询缓存在默许状态下是禁用的。

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

看管查询缓存

在启用查询缓存当前，首要的是要懂得它是否是失掉了有效的应用。MySQL 有几个可以也许检察的变量，可以也许用来懂得缓存中的状态。清单 2 给出了缓存的状态。

清单 2、浮现,查询缓存的统计信息

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

| 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 |

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

8 rows in set (0.00 sec)这些项的诠释如表 1 所示。

表 1、MySQL 查询缓存变量

变量名 阐明

Qcache_free_blocks 缓存中相邻内存块的个数。数目大阐明能够有碎片。FLUSH QUERY CACHE 会对缓存中的碎片举办收拾整顿，从而失掉一个余暇块。

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 缓存中块的数目。

平常，隔断几秒浮现,这些变量就可以也许看出区分，这可以也许扶助肯定缓存是否是正在有效地应用。运转 FLUSH STATUS 可以也许重置一些计数器，如果任事器已运转了一段时候，这会非常有扶助。

应用非常大的查询缓存，希冀可以也许缓存所有器材，这类设法非常诱人。因为 mysqld 必需要对缓存举办保护，比方当内存变得很低时履行剪除，响应式网页设计，因而任事器能够会在试图治理缓存时而堕入逆境。作为一条法则，如果 FLUSH QUERY CACHE 占用了很长时候，那就阐明缓存太大了。

强制限定

您可以也许在 mysqld 中强制一些限定来确保体系负载不会招致资本耗尽的状态出现。清单 3 给出了 my.cnf 中与资本有关的一些首要设置。

清单 3、MySQL 资本设置

set-variable=max_connections=500

set-variable=wait_timeout=10

max_connect_errors = 100

衔接最大个数是在第一行中举办治理的。与 Apache 中的 MaxClients 类似，其设法是确保只树立任事容许数目标衔接。要肯定任事器上现在树立过的最大衔接数，请履行 SHOW STATUS LIKE 'max_used_connections'。

第 2 行告知 mysqld 停止所有余暇时候凌驾 10 秒的衔接。在 LAMP 应用法式中，衔接数据库的时候平常就是 Web 任事器处置惩罚要求所斲丧的时候。偶然辰，如果负载太重，衔接会挂起，而且会占用衔接表空间。如果有多个交互用户或应用了到数据库的耐久衔接，那末将这个值设 低一点实在不成取！

末了一行是一个平安的门径。如果一个主机在衔接到任事器时有题目，并重试很屡次后抛却，那末这个主机就会被锁定，直到 FLUSH HOSTS 当前才气运转。默许状态下，10 次失利就足以招致锁定了。将这个值点窜为 100 会给任事器充足的时候来从题目中规复。如果重试 100 次都没法树立衔接，那末应用再高的值也不会有太多扶助，能够它根本就没法衔接。

缓冲区和缓存

MySQL 支撑凌驾 100 个的可调理设置；但是侥幸的是，控制多数几个就可以也许知足大部份需要。查找这些设置的准确值可以也许经由过程 SHOW STATUS 号令检察状态变量，从中可以也许肯定 mysqld 的运作状态是否是契合我们的预期。给缓冲区和缓存分配的内存不克不及凌驾体系中的现有内存，因而调优平常都需要举办一些让步。

MySQL 可调理设置可以也许应用于所有 mysqld 过程，也可以也许应用于单个客户机遇话。

任事器端的设置

每个表都可以也许示意为磁盘上的一个文件，必需先翻开，后读取。为了加速从文件中读取数据的过程，mysqld 对这些翻开文件举办了缓存，其最大数目由 /etc/mysqld.conf 中的 table_cache 指定。清单 4 给出了浮现,与翻开表有关的流动的方法。

清单 4、浮现,翻开表的流动

mysql> SHOW STATUS LIKE 'open%tables';

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

| Variable_name | Value |

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

| Open_tables | 5000 |

| Opened_tables | 195 |

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

2 rows in set (0.00 sec)

清单 4 阐明现在有 5,000 个表是翻开的，有 195 个表需要翻开，因为而今缓存中已没有可用文件描写符了(因为统计信息在后面已清除，因而能够会存在 5,000 个翻开表中只需 195 个翻开记载的状态)。如果 Opened_tables 随侧重新运转 SHOW STATUS 号令疾速增加，就阐明缓存掷中率不敷。如果 Open_tables 比 table_cache 设置小很多，就阐明该值太大了(不外有空间可以也许增加总不是甚么好事)。比方，应用 table_cache = 5000 可以也许调剂表的缓存。

与表的缓存类似，关于线程来说也有一个缓存。mysqld 在吸收衔接时会根据需要天生线程。在一个衔接变更很快的忙碌任事器上，对线程举办缓存便于当前应用可以也许加速最后的衔接。

清单 5、浮现,若何肯定是否是缓存了充足的线程。

mysql> SHOW STATUS LIKE 'threads%';

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

| Variable_name | Value |

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

| Threads_cached | 27 |

| Threads_connected | 15 |

| Threads_created | 838610 |

| Threads_running | 3 |

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

4 rows in set (0.00 sec)

此处首要的值是 Threads_created，每次 mysqld 需要树立一个新线程时，这个值城市增加。如果这个数字在连气儿履行 SHOW STATUS 号令时疾速增加，就应当测验考试增大线程缓存。比方，可以也许在 my.cnf 中应用 thread_cache = 40 来完成此目标。

要害字缓冲区留存了 MyISAM 表的索引块。幻想状态下，关于这些块的要求应当来自于内存，而不是来自于磁盘。清单 6 浮现,了若何肯定有几多块是从磁盘中读取的，和有几多块是从内存中读取的。

清单 6、肯定要害字效力

mysql> show status like '%key_read%';

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

| Variable_name | Value |

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

| Key_read_requests | 163554268 |

| Key_reads | 98247 |

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

2 rows in set (0.00 sec)

Key_reads 代表掷中磁盘的要求个数， Key_read_requests 是总数。掷中磁盘的读要求数除以读要求总数就是不中比率 —— 在本例中每 1,000 个要求，约莫有 0.6 个没有掷中内存。如果每 1,000 个要求中掷中磁盘的数目凌驾 1 个，就应当考虑增大要害字缓冲区了。比方，key_buffer = 384M 会将缓冲区设置为 384MB。

且自表可以也许在更初级的查询中应用，个中数据在进一步举办处置惩罚(比方 GROUP BY 字句)之前，都必需先留存到且自表中；幻想状态下，在内存中树立且自表。但是如果且自表变得太大，就需要写入磁盘中。清单 7 给出了与且自表树立有关的统计信息。

清单 7、肯定且自表的应用

mysql> SHOW STATUS LIKE 'created_tmp%';

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

| Variable_name | Value |

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

| Created_tmp_disk_tables | 30660 |

| Created_tmp_files | 2 |

| Created_tmp_tables | 32912 |

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

3 rows in set (0.00 sec)

每次应用且自表城市增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。关于这个比率，并没有甚么严酷的法则，因为这依赖于所触及的查询。长时候观测 Created_tmp_disk_tables 会浮现,所树立的磁盘表的比率，您可以也许肯定设置的效力。 tmp_table_size 和 max_heap_table_size 都可以也许把持且自表的最大巨细，因而请确保在 my.cnf 中对这两个值都举办了设置。

每个会话的设置

下面这些设置针关于每个会话。在设置这些数字时要非常谨慎，因为它们在乘以能够存在的衔接数时辰，这些选项示意大批的内存！您可以也许经由过程代码点窜会话中的这些数字，或在 my.cnf 中为所有会话点窜这些设置。

当 MySQL 必需要举办排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来寄存这些数据行。如果要排序的数据太大，那末数据就必需留存到磁盘上的且自文件中，并再次举办排序。如果 sort_merge_passes 状态变量很大，这就领导了磁盘的流动状态。清单 8 给出了一些与排序相干的状态计数器信息。

清单 8、浮现,排序统计信息

mysql> 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 很大，就示意需要看重 sort_buffer_size。比方， sort_buffer_size = 4M 将排序缓冲区设置为 4MB。

MySQL 也会分配一些内存来读取表。幻想状态下，索引供给了充足多的信息，可以也许只读入所需要的行，但是偶然辰查询(设想欠安或数据赋性使然)需要读取表中大批数据。要懂得这类举动，需要晓得运转了几多个 SELECT 语句，和需要读取表中的下一行数据的次数(而不是经由过程索引直接拜访)。完成这类功效的号令如清单 9 所示。

清单 9、肯定表扫描比率

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)

Handler_read_rnd_next / Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值凌驾 4000，就应当检察 read_buffer_size，比方 read_buffer_size = 4M。如果这个数字凌驾了 8M，就应当与开辟职员会商一下对这些查询举办调优了！

3 个必不成少的工具

虽然在懂得具体设置时，SHOW STATUS 号令会非常有效，但是您还需要一些工具来诠释 mysqld 所供给的大批数据。我发明有 3 个工具是必不成少的；在 参考材料 一节中您可以也许找到呼应的链接。

大部份体系治理员都非常熟习 top 号令，它为义务所斲丧的 CPU 和内存供给了一个不息更新的视图。 mytop 对 top 举办了仿真；它为所有衔接上的客户机和它们正在运转的查询供给了一个视图。mytop 还供给了一个有关要害字缓冲区和查询缓存效力的及时数据和汗青数据，和有关正在运转的查询的统计信息。这是一个很有效的工具，可以也许检察体系中(比如 10 秒钟以内)的状态，您可以也许获得有关任事器安康信息的视图，并浮现,招致题目的任何衔接。

mysqlard 是一个衔接到 MySQL 任事器上的保卫法式，担任每 5 分钟汇集一次数据，并将它们存储到背景的一个 Round Robin Database 中。有一个 Web 页面会浮现,这些数据，比方表缓存的应用状态、要害字效力、衔接上的客户机和且自表的应用状态。虽然 mytop 供给了任事器安康信息的快照，但是 mysqlard 则供给了且自的安康信息。作为嘉奖，mysqlard 应用自身汇集到的一些信息针对若何对任事器举办调优给出一些建议。

汇集 SHOW STATUS 信息的别的一个工具是 mysqlreport。其讲述要远比 mysqlard 愈加庞大，因为需要对任事器的每个方面都举办阐发。这是对任事器举办调优的一个非常好的工具，因为它对状态变量举办恰当较量争论来扶助肯定需要点窜哪些题目。

竣事语

本文先容了对 MySQL 举办调优的一些根蒂根基常识，并对这个针对 LAMP 组件举办调优的 3 部份系列文章举办了总结。调优很大程度上需要懂得组件的义务道理，肯定它们是否是一般义务，举办一些调剂，并重新评测。每个组件 —— Linux、Apache、PHP 或 MySQL —— 都有各类各样的需求。离别懂得各个组件可以也许扶助削减能够会招致应用法式速率变慢的瓶颈。

信息起源尚品中国:大兴区网站制作,大兴区网站设想,大兴区网站扶植公司