3种方法可以加快MySQL的运行速度，而其他所有方法只是这3种方法之一的优点。按重要性顺序排列如下：

1.优化查询

2.调整MySQL配置

3.添加更多硬件

================================================== ==========

＃1。查询优化

================================================== ==========

MySQL性能最常见的问题是未优化的查询。以下是一些未优化查询的示例：

-不使用索引的查询。

-使用SELECT *的查询。

-搜索全文字段的查询。

-没有适当限制的查询。

-不必要使用ORDER BY的查询。

指标

到目前为止，最大的问题查询是不使用索引或不使用BEST索引的查询。索引是从查询中获得最佳性能的关键。索引基本上是MySQL的快捷方式-它们的作用方式与课堂教科书中的索引相同。假设您要查找包含“ gr8gonzo”的所有页面。您可以仔细阅读书中的每个单词并找到所有页面，但是翻到索引并看到“ gr8gonzo”恰好位于第2、6和32页上，这要快得多。

大多数人知道如何使用基本索引，但大多数人不知道如何使用BEST索引。许多查询在WHERE子句中具有多个功能，例如：

SELECT fields FROM mytable
 WHERE field1 > 123 AND field2 = 'gr8gonzo';

大多数人将有一个field1的索引和一个field2的索引。这很好，查询将尝试使用这些索引之一（并且会更快）。但是，如果这是一个经常运行的查询，则最好具有一个同时包含field1和field2的ANOTHER索引。（通常）可以为您提供最佳的查询性能。

就是说，您不想只创建大量的这些索引，因为每当表更改时，每个索引的确需要MySQL进行一些额外的工作来更新，并且随着时间的推移这些点会累加。您应该只在频繁，缓慢的查询可以利用它们时才创建这些多字段索引。在本文的第2节中，我们将介绍让MySQL告诉您哪些查询需要调优的几种方法，但是有一种方法可以立即告诉您查询是否不使用索引...

解释查询

如果我想看看上面的查询是否运行良好，可以使用EXPLAIN来完成。当您解释查询时，您只是在要求MySQL告诉您，如果它为您运行查询，它将做什么。它以计算机化的版本响应：“好，为了运行查询，我将使用该索引。这将给我留下X行，然后我将查看这些行以确定您想要的行。”

要解释一个查询，您要做的就是运行相同的查询，但在其前面加上“ EXPLAIN”：

EXPLAIN SELECT fields FROM mytable 
 WHERE field1 > 123 AND field2 = 'gr8gonzo';

结果看起来像这样：

+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+ 
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       | 
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+ 
|  1 | SIMPLE      | mytable | ALL  | PRIMARY       | NULL | NULL    | NULL | 898256 | Using where | 
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+.

哇！乍一看，这可能确实令人困惑，但是您通常可以忽略很多信息，例如id，select_type，table，type和ref。MySQL有时将索引称为“键”，所以现在让我们看一下相同的结果，但没有多余的列：

+---------------+------+---------+------+--------+-------------+ 
| possible_keys | key  | key_len | ref  | rows   | Extra       | 
+---------------+------+---------+------+--------+-------------+ 
| PRIMARY       | NULL | NULL    | NULL | 898256 | Using where | 
+---------------+------+---------+------+--------+-------------+ 

基本上，这就是说MySQL必须逐一遍历898,256行，并检查每一行以查看field1> 123和field2 ='gr8gonzo'。这需要做很多处理，特别是如果最终结果只有几行（意味着有将近90万行被无用搜索）。让我们尝试为这些字段之一添加索引：

ALTER TABLE `mytable` 
 ADD INDEX `IDX_FIELD1` (`field1`) ;

如果我们重新运行EXPLAIN，则会看到：

+---------------+---------------+---------+-------+------+-------------+ 
| possible_keys | key           | key_len | ref   | rows | Extra       | 
+---------------+---------------+---------+-------+------+-------------+ 
| IDX_FIELD1    | IDX_FIELD1    | 5       | const | 1246 | Using where | 
+---------------+---------------+---------+-------+------+-------------+

好吧，现在我们只能查看1,246行。这比89.8万要好得多，但我们可以做得更好。我们的查询在WHERE子句中使用两个字段，因此我们可以通过添加包含这两个字段的索引来获得更好的性能：

ALTER TABLE `mytable` 
 ADD INDEX `IDX_FIELDS1_2` (`field1`, `field2`) ;

...现在重新运行EXPLAIN，我们得到了。

+---------------+---------------+---------+-------------+------+-------------+ 
| possible_keys | key           | key_len | ref         | rows | Extra       | 
+---------------+---------------+---------+-------------+------+-------------+ 
| IDX_FIELDS1_2 | IDX_FIELDS1_2 | 5       | const,const | 16   | Using where | 
+---------------+---------------+---------+-------------+------+-------------+

瞧！现在，当我们对real运行完全相同的查询时，我们知道MySQL只需要搜索16行，而不是近一百万。保证的速度增加，而且它是免费的！

注意：在上面的输出中，“ possible_keys”有时会显示多个索引，表明有多个选择可以帮助查询运行更快。但是，“选择”索引将在“键”字段中。“ ref”可以使您了解索引中涉及多少个字段。例如，如果您在一个字段上有一个索引，那么您的“ ref”列可能只会说“ const”，但是如果您在两个字段上有一个索引并且这两个字段都在WHERE子句中，那么您可能会看到“参考”列中的“ const，const”。

另一个注意事项：每当MySQL必须查看表中的每一行时，这都称为“表扫描”。表扫描是MySQL查找数据的最慢方式。当您解释查询时，请查看“类型”列-如果其显示“全部”，则MySQL正在进行表扫描以查找您的数据。如果它说其他内容，例如“范围”，则说明它正在使用索引。有时，在小型表上，即使您有索引，MySQL也会进行表扫描。这只是MySQL知道在那种情况下最好的方法，但是您通常希望避免这些情况。这是有关避免表扫描的MySQL文档的链接：

http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html

关于如何使用EXPLAIN，有很多深入的优化知识。如果您想阅读文档，请在此处查看：

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

我最近还发现并强烈推荐一个名为HeidiSQL的免费MySQL管理器应用程序，该应用程序（除其他外）使创建和更新索引变得容易。另外，添加索引时，它将显示为创建这些索引而运行的SQL代码，从而使其成为有用的学习工具。

还有phpMyAdmin，它已安装在许多Web主机上：http://www.phpmyadmin.net

使用SELECT *

我对此感到内gui。编写使用SELECT *的查询要容易得多，而不必担心输入10个字段名称，但这很可能是导致Web应用程序变慢的罪魁祸首。这是一个常见的错误：

假设您经营一个网站，该网站收集会员撰写的故事。所有的故事都放在一张叫做故事的大桌子上。到现在为止还挺好。但是，现在让我们说您有一个查询，该查询用于创建菜单以链接到所有故事：

SELECT * FROM stories;

好吧，如果每个故事的目录都在故事表中，那么每当您运行上述查询时，MySQL都会将系统中每个故事的每个字母都发送回脚本。如果您有1000个故事，每个故事约1万个，那么每次有人查看菜单时，您的脚本都会下载10 MB的额外数据，这些数据将被丢弃而无需使用。真是浪费！

相反，尝试将查询更改为以下内容：

DESCRIBE mytable;

现在，我们仅选择菜单所需的一些字段。养成只指定脚本需要的字段的习惯，您会发现它比您想象的要容易，并且脚本的运行速度会更快。

提示：有一种快速的方法可以查看表中所有字段的摘要以及它们是什么类型的字段：

DESCRIBE mytable;

全文

让我们继续上面的“故事”示例。人们可能会希望在故事中搜索特定的单词。如果您的故事内容在全文字段（例如TEXT数据类型）中，那么您可能会像这样进行搜索：

SELECT storyID FROM stories
 WHERE storyContent LIKE '%fondled the hubcaps%';

当您没有太多故事时，这可能会很快运行，但是随着时间的流逝，它会越来越慢。在这种情况下，请考虑一个名为Sphinx Search的开源产品：

http://sphinxsearch.com/

它专门用于获取全文内容并使其可搜索。在MySQL中运行需要10秒钟的查询，在Sphinx中可能需要0.1秒钟，这并不夸张。缺点是它是一个单独的程序/守护程序，需要一些专门知识来设置和运行，但这是值得的。他们有社区论坛可以提供帮助，并且Experts Exchange上的某些人（例如我）也可以提供帮助。

添加限制

这很简单-如果您只需要返回几千行中的几行（例如，获取某事物的前10位），则在查询末尾添加一个LIMIT子句：

SELECT storyID FROM stories
 ORDER BY storyRating DESC
 LIMIT 10;

有时，运行查询以对结果中的行数进行计数，然后再提取所有行，这可能会很有用。这可以使您了解如何限制行或如何运行下一个查询（尽管这在很大程度上取决于您的特定情况）。这是一种从我们的示例中快速获取故事数量的方法：

SELECT COUNT(storyID) AS storyCount FROM stories;

结果将是包含名为“ storyCount”的字段的行。随着您的数据库越来越大，这种技术变得越来越有用。

困境中的秩序

使用ORDER BY可以很好地进行排序，但是有时它会在MySQL上造成实际的速度下降。当您对一个字段进行ORDER BY时，MySQL首先会找到结果中的所有行，然后，然后返回并根据该ORDER BY字段对它们进行重新排序。如果您有很多行，那么MySQL必须进行很多重新排序，这可能会很慢。

在上面关于LIMITs的示例中，查询必须在返回前10个故事之前按其评分对每个故事进行排序。但是，如果我知道所有前10个故事的评分都为4或更高，那么我可以减少排序的故事数量：

SELECT storyID FROM stories
 WHERE storyRating >= 4
 ORDER BY storyRating DESC
 LIMIT 10;

现在，MySQL可能只需要整理100个故事，而不是10,000个。

有时值得一问自己是否真的需要使用ORDER BY。有时，完全跳过数据库上的订购信息并使用PHP或其他方法来进行排序会更快（尽管MySQL通常更快）。

另一个技巧是为要选择和排序的字段创建索引。因此，如果您有一个查询：

SELECT storyID,storyRating FROM stories
 ORDER BY storyRating DESC;

然后，该查询可以从storyID和storyRating的多字段索引中受益匪浅。

================================================== ==========

＃2。MySQL配置

================================================== ==========

有很多方法可以配置MySQL，但是所有方法都以my.cnf配置文件开始（通常就是这种方式）。一般来说，您可以通过告诉MySQL将其缓存在内存中来优化MySQL。当它在内存中存储任何数据时，MySQL几乎可以立即访问它，而不必回到硬盘驱动器上的完整数据库并查找所请求的数据（这很慢）。

这是my.cnf文件的示例部分（我删除了一些与性能不相关的额外参数，以及本文中将不讨论的其他一些参数）：

[mysqld]
skip-name-resolvequery_cache_size = 16M
# Don't use these next 3 lines if you're on MySQL 5.1.6 or later
log-slow-queries=/var/log/slowqueries.log
long_query_time = 4
log-queries-not-using-indexes
table_cache = 512
tmp_table_size = 128M
max_heap_table_size = 128M
myisam_sort_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 256K
key_buffer = 128M

我始终要做的第一件事是禁用名称解析（skip-name-resolve）。基本上，名称解析只是尝试在连接到数据库的任何人上查找“呼叫者ID”。我仍然不知道为什么默认情况下启用它。这不仅是一个潜在的安全问题，而且通常对于大多数Web服务器设置都是不必要的（因为Web服务器是进行连接的服务器，而不是访问者），并且有可能使系统崩溃（如果您的DNS崩溃）一会儿，MySQL充满了等待“解决”的连接。

接下来，启用查询缓存（query_cache_size）。在上面的示例中，我有一个16 MB的查询缓存。基本上，如果我运行一个查询需要花费5秒钟的时间，然后刷新页面或其他内容（导致查询再次运行），则该查询将立即运行，因为MySQL将从第一时间起就记住查询结果。但是，如果查询中涉及的表发生了更改，则它将清除使用这些表的所有缓存结果（因此，您始终可以获得准确的数据）。从16 MB的缓存开始，然后根据需要逐步提高（我将在一点儿说明如何确定何时增加缓存）。

第三，启用慢查询日志（log-slow-queries和long_query_time和log-queries-not-using-indexes）。这告诉MySQL跟踪所有花费超过一定秒数（long_query_time）才能完成的查询。log-query-not-using-indexes选项还包括不使用索引的查询（足够简单）。在使用应用程序时，只需将日志放置一两天，然后查看日志以查找所有需要优化的查询。

更新：在MySQL 5.1.6和更高版本上，您可以通过设置全局变量来启用慢速查询日志。因此，要在较新的版本上启用慢速查询日志记录，请运行以下查询：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 4;
SET GLOBAL slow_query_log_file = '/var/log/slowqueries.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';

行的最后一部分具有几个不同的目的（缓存连接，ORDER BY结果，临时表等），这些都影响速度，但是有时很难确切知道要使用什么值。这就是为什么我建议使用MySQLTuner的原因：

http://wiki.mysqltuner.com/MySQLTuner

这是一个Perl脚本，您可以让服务器运行几天（无需重新启动），然后下载并在数据库服务器上运行。该脚本将查看MySQL收集的所有统计信息，并将就my.cnf文件中的更改内容提出建议，以使事情更好地运行（例如增加查询缓存大小或table_cache以及此类东西）。它非常简单，不需要很长时间即可运行。

================================================== ==========

＃3。瞬间速度！只需添加硬件！

================================================== ==========

这通常是最明显的答案。升级到更快的CPU，添加更多RAM等，您的运行速度就会更快。的确如此，但是首先要了解几件事。

首先，硬盘驱动器的数量比SPACE更重要。有些人错误地得到了两个1 TB的驱动器，而仅仅使用它们来运行他们的数据库服务器。通过在RAID阵列中添加多个硬盘（大多数服务器无论如何都使用该磁盘），可以有效地分配负载。

如果两个查询同时运行，并且您只有两个硬盘驱动器，则很有可能两个查询的数据都位于同一硬盘驱动器上。由于硬盘一次只能执行一项操作，因此其中一个查询将需要等待更长的时间才能使另一个查询完成才能运行。但是，如果您有6个或更多硬盘（更多），那么一个查询可能需要来自2号硬盘的数据，而第二个查询可能需要5号硬盘的数据。两个硬盘驱动器可以同时工作，并几乎同时发送数据。至少这就是要旨，因此，请花钱购买多个快速硬盘以提高速度。无论如何，硬盘通常是最大的速度瓶颈（就硬件而言）。

硬盘上的最后一点-如果其他人正在设置服务器并想知道要使用的RAID级别，请尝试使用RAID 10（安全+良好性能）。否则，请使用RAID 1（安全）。其他RAID级别也有其优点和缺点，但这是我的标准建议。

其次，RAM和速度之间通常不存在直接关系。仅仅因为添加更多RAM并不意味着系统会自动使用它（或正确使用它）。如果您已经有几个GB的RAM，那么任何额外的RAM都应该用于更大的缓存。也有其他用途（例如增加最大连接数），但是如果您正在阅读本文，那么您可能还没有到那时。

第三，CPU有点像运气-它会稍微影响所有事情，并且会影响很多事情。如果您正在编写需要大量数学计算的应用程序，处理统计数据等问题，那么投资于最新，最好的CPU和主板可能是个不错的选择。如果这只是您的标准Web /数据库服务器，那么可能会有更好的支出方式（-咳嗽-更多硬盘-咳嗽-）。

最后，如果一台服务器无法处理所有流量，请考虑使用双主复制来设置另一台服务器，以实现快速，零脏的负载平衡方式。（注意-复制实际上并没有进行负载平衡，它只是使两台服务器保持完整的实时同步，因此您可以将50％的访问者发送到一台数据库服务器，将另一50％的访问者发送到另一台服务器。这也使得方便的备份/故障转移系统。）

最终提示：很多地方都会有一个测试数据库服务器和一个真实的数据库服务器，但是测试数据库服务器中只有很少的行（足以进行基本测试）。这使得容易判断应用程序的真实性能。尝试确保测试数据库具有与真实数据库相似的数据，以便您更好地了解查询在现实世界中的性能。许多MySQL管理器程序（例如phpMyAdmin和HeidiSQL）使从真实数据库中下载所有数据变得容易，因此您可以将其上传到测试数据库中。（还有一个名为mysqldump的命令行工具。）