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 —— 都有各类各样的需求。划分了解各个组件可以也许接济削减梗概会招致应用法式速率变慢的瓶颈。

信息滥觞尚品中国:大兴区网站建造,大兴区网站设想,大兴区网站扶植公司