一个非常简单但非常有用的工具是查询分析。启用分析是获得运行查询的更准确时间估计的简单方法。这是一个两步过程。首先，我们必须启用分析。然后，我们调用show profiles实际获取查询运行时间。

假设我们的数据库中有以下插入（假设已经创建了 User 1 和 Gallery 1）：

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');    

显然，这个数据量不会造成任何麻烦，但是我们用它来做一个简单的profile。让我们考虑以下查询：

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

这个查询是一个很好的例子，如果我们得到很多照片条目，将来可能会出现问题。

要获得此查询的准确运行时间，我们将使用以下 SQL：

set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;

结果如下所示：

正如我们所看到的，该show profiles;命令不仅为原始查询提供了时间，还为所有其他查询提供了时间。这样我们就可以准确地分析我们的查询。

但是我们如何才能真正改进它们呢？

我们既可以依靠我们对 SQL 的了解，即兴发挥，也可以依靠 MySQLexplain命令，根据实际信息提高查询性能。

Explain用于获取查询执行计划，或者 MySQL 将如何执行我们的查询。它适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句，并显示来自优化器的有关语句执行计划的信息。官方文档很好地描述了如何帮助explain我们：

在 EXPLAIN 的帮助下，您可以看到应该在哪里为表添加索引，以便通过使用索引查找行来更快地执行语句。您还可以使用 EXPLAIN 检查优化器是否以最佳顺序连接表。

为了举例说明 的用法explain，我们将使用我们的查询UserManager.php通过电子邮件查找用户：

SELECT * FROM `homestead`.`users` WHERE email = 'claudio.ribeiro@examplemail.com';

要使用该explain命令，我们只需在选择类型查询之前添加它：

EXPLAIN SELECT * FROM `homestead`.`users` WHERE email = 'claudio.ribeiro@examplemail.com';

这是结果（向右滚动查看全部）：

这些结果乍一看并不容易理解，所以让我们仔细看看它们中的每一个：

• id：这只是 SELECT 中每个查询的顺序标识符。
• select_type: SELECT 查询的类型。该字段可以采用许多不同的值，因此我们将重点关注最重要的值：
  • SIMPLE：没有子查询或联合的简单查询
  • PRIMARY：选择在连接的最外层查询中
  • DERIVED: select 是 from 中子查询的一部分
  • SUBQUERY：子查询中的第一个选择
  • UNION: select 是联合的第二条或之后的语句。可以在此处找到可以出现在select_type字段中的值的完整列表。
• table: 行引用的表。
• type：这个字段是 MySQL 如何连接使用的表。这可能是解释输出中最重要的字段。它可以指示丢失的索引，还可以显示应该如何重写查询。此字段的可能值如下（从最佳类型到最差类型）：
  • system: 该表有零或一行。
  • const：该表只有一个匹配的被索引的行。这是最快的连接类型。
  • eq_ref: 索引的所有部分都被连接使用，并且索引是 PRIMARY_KEY 或 UNIQUE NOT NULL。
  • ref: 为前一个表中的每个行组合读取索引列的所有匹配行。这种类型的连接通常出现在与=或<=>运算符比较的索引列中。
  • fulltext：连接使用表 FULLTEXT 索引。
  • ref_or_null: 这与 ref 相同，但也包含列中具有 NULL 值的行。
  • index_merge：连接使用索引列表来生成结果集。的 KEY 列explain将包含使用的键。
  • unique_subquery: IN 子查询只从表中返回一个结果并使用主键。
  • range：索引用于查找特定范围内的匹配行。
  • index：扫描整个索引树以查找匹配的行。
  • all：扫描整个表以查找连接的匹配行。这是最差的连接类型，通常表明表中缺少适当的索引。
• possible_keys: 显示 MySQL 可以用来从表中查找行的键。这些密钥可能会或可能不会在实践中使用。
• keys：表示MySQL实际使用的索引。MySQL 总是寻找可用于查询的最佳键。在加入许多表时，它可能会找出一些其他未列出possible_keys但更优化的键。
• key_len: 表示查询优化器选择使用的索引的长度。
• ref：显示与键列中命名的索引进行比较的列或常量。
• rows: 列出为产生输出而检查的记录数。这是一个非常重要的指标；检查的记录越少越好。
• Extra: 包含附加信息。此列中的Using filesort或等值可能表示查询有问题。Using temporary

有关输出格式的完整文档explain可以在 MySQL 官方页面上找到。

回到我们的简单查询：它是一种SIMPLE带有 const 连接类型的 select 类型。这是我们可能拥有的最好的查询案例。但是当我们需要更大更复杂的查询时会发生什么？

回到我们的应用程序模式，我们可能想要获取所有图库图像。我们也可能希望只包含描述中包含“猫”一词的照片。这绝对是我们在项目需求上可以找到的一个案例。让我们看一下查询：

SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`users` AS users
LEFT JOIN `homestead`.`galleries` AS gal ON users.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

在这个更复杂的情况下，我们应该有更多的信息来分析我们的explain：

EXPLAIN SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`users` AS users
LEFT JOIN `homestead`.`galleries` AS gal ON users.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

这给出了以下结果（向右滚动以查看所有单元格）：

让我们仔细看看，看看我们可以在查询中改进什么。

正如我们之前看到的，我们首先应该看的主要列是type列和rows列。目标应该在列中获得更好的值，并在type列上尽可能地减少rows。

我们第一次查询的结果是index，这根本不是一个好的结果。这意味着我们可能会改进它。

查看我们的查询，有两种方法可以接近它。首先，该Users表未被使用。我们要么扩展查询以确保我们以用户为目标，要么我们应该完全删除users查询的一部分。它只会增加我们整体性能的复杂性和时间。

SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`galleries` AS gal
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

所以现在我们得到了完全相同的结果。让我们来看看explain：

我们留下了一个ALLon 类型。虽然ALL可能是最糟糕的连接类型，但有时它是唯一的选择。根据我们的要求，我们想要所有的画廊图像，所以我们需要遍历整个画廊表。虽然索引在尝试查找表上的特定信息时非常有用，但当我们需要其中的所有信息时，它们无法帮助我们。当我们遇到这样的情况时，我们不得不求助于不同的方法，比如缓存。

由于我们正在处理 , 我们可以做的最后一个改进LIKE是在我们的描述字段中添加一个 FULLTEXT 索引。这样，我们可以将其更改LIKE为 amatch()并提高性能。更多关于全文索引的信息可以在这里找到。

我们还必须研究两个非常有趣的案例：应用程序中的newest和related功能。这些适用于画廊，并涉及我们应该注意的一些极端案例：

EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
LEFT JOIN `homestead`.`users` AS u ON u.id = gal.user_id
WHERE u.id = 1
ORDER BY gal.created_at DESC
LIMIT 5;

以上是相关画廊。

EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
ORDER BY gal.created_at DESC
LIMIT 5;

以上是最新的画廊。

乍一看，这些查询应该很快，因为它们使用LIMIT. 大多数使用LIMIT. 不幸的是，对于我们和我们的应用程序来说，这些查询也在使用ORDER BY. 因为我们需要在限制查询之前对所有结果进行排序，所以我们失去了使用LIMIT.

既然我们知道ORDER BY可能会很棘手，让我们应用我们的 trusty explain。

和，

正如我们所看到的，我们遇到了最坏的连接类型：ALL对于我们的两个查询。

从历史上看，MySQL 的ORDER BY实现，尤其是与 一起使用LIMIT，通常是导致 MySQL 性能问题的原因。这种组合也用于大多数具有大型数据集的交互式应用程序。新注册用户和热门标签等功能通常使用这种组合。

因为这是一个常见问题，所以还有一小部分我们应该应用的常见解决方案来解决性能问题。

• 确保我们正在使用索引。在我们的例子中，created_at是一个很好的候选人，因为它是我们订购的领域。这样，我们就可以同时ORDER BY执行这两种操作，LIMIT而无需扫描和排序完整的结果集。
• 按前导表中的列排序。通常，如果ORDER BY表中的字段不是连接顺序中的第一个，则不能使用索引。
• 不要按表达式排序。表达式和函数不允许ORDER BY.
• 谨防大LIMIT值。较大的LIMIT值将强制ORDER BY对更多的行进行排序。这会影响性能。

这些是我们在两者兼有时应该采取的一些措施LIMIT，ORDER BY以尽量减少性能问题。

结论

正如我们所看到的，explain这对于尽早发现查询中的问题非常有用。只有当我们的应用程序在生产中并且有大量数据或大量访问者访问数据库时，我们才会注意到很多问题。如果这些东西可以在使用的早期发现explain，那么将来出现性能问题的空间就会小得多。

我们的应用程序具有它需要的所有索引，而且速度非常快，但我们现在知道explain，只要我们需要检查性能提升，我们总是可以求助于索引。