使用索引和解释提升 MySQL 性能

提高应用程序性能的技术可以来自很多不同的地方，但通常我们首先要看的东西——最常见的瓶颈——是数据库。 可以改进吗？ 我们如何衡量和了解需要改进的地方？

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

假设我们的数据库中有以下插入内容（假设用户 1 和画廊 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');    

显然，这个数据量不会造成什么麻烦，但我们还是用它来做一个简单的剖析吧。 让我们考虑以下查询：

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;

结果如下所示：

Query_Id 持续时间查询 1 0.00016950 显示警告 2 0.00039200 SELECT * FROM homestead.images 作为我 nWHERE i.description LIKE ‘%street%’nLIMIT 0, 1000 3 0.00037600 SHOW KEYS FROM homestead.images
4 0.00034625 显示像 ‘homestead 这样的数据库 5 0.00027600 显示来自的表 homestead 喜欢’图像’ 6 0.00024950 SELECT * FROM homestead.images WHERE 0=1 7 0.00104300 显示完整列来自 homestead.images 喜欢’id’

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

但是我们怎样才能真正改进它们呢？

我们可以依靠我们的 SQL 知识和即兴创作，也可以依靠 MySQL explain 根据实际信息指挥和提高我们的查询性能。

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

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

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

SELECT * FROM `homestead`.`users` WHERE email = '[email protected]';

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

EXPLAIN SELECT * FROM `homestead`.`users` WHERE email = '[email protected]';

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

id select_type 表分区 type possible_keys key key_len ref rows filtered Extra 1 SIMPLE ‘users’ NULL ‘const’ ‘UNIQ_1483A5E9E7927C74’ ‘UNIQ_1483A5E9E7927C74’ ‘182’ ‘const’ 100.00 NULL

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

id：这只是 SELECT 中每个查询的顺序标识符。

select_type: SELECT 查询的类型。 该字段可以采用许多不同的值，因此我们将重点关注最重要的值：

SIMPLE：没有子查询或联合的简单查询
PRIMARY：选择在连接的最外层查询中
DERIVED：选择是 from 中子查询的一部分
SUBQUERY: 子查询中的第一个选择
UNION：选择是联合的第二个或后面的语句。

可以出现在 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 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%';

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

id select_type 表分区 type possible_keys key key_len ref rows filtered Extra 1 SIMPLE ‘users’ NULL ‘index’ ‘PRIMARY,UNIQ_1483A5E9BF396750’ ‘UNIQ_1483A5E9BF396750’ ‘108’ NULL 100.00 ‘Using index’ 1 SIMPLE ‘gal’ NULL ‘ref’ ‘PRIMARY, UNIQ_F70E6EB7BF396750,IDX_F70E6EB7A76ED395’ ‘UNIQ_1483A5E9BF396750’ ‘108’ ‘homestead.users.id’ 100.00 NULL 1 SIMPLE ‘img’ NULL ‘ref’ ‘IDX_E01FBE6A4E7AF8F’ ‘IDX_E01FBE6A4E7AF8F’ ‘109’ ‘homestead.gal.id’ ‘25.00’ ‘Using where ‘

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

正如我们之前看到的，我们应该首先查看的主要列是 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:

id select_type 表分区 type possible_keys key key_len ref rows filtered Extra 1 SIMPLE ‘gal’ NULL ‘ALL’ ‘PRIMARY,UNIQ_1483A5E9BF396750’ NULL NULL NULL 100.00 NULL 1 SIMPLE ‘img’ NULL ‘ref’ ‘IDX_E01FBE6A4E7AF8F’ ‘IDX_E017AFBE86A4′ homestead.gal.id’ ‘25.00’ ‘使用位置’

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

我们可以做的最后一项改进，因为我们正在处理一个 LIKE, 就是给我们的描述字段加一个全文索引。 这样，我们可以改变 LIKE 到一个 match() 并提高性能。 可以在此处找到有关全文索引的更多信息。

还有两个非常有趣的案例我们必须看看： 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 可能会很棘手，让我们应用我们可信赖的 explain.

id select_type 表分区 type possible_keys key key_len ref rows filtered Extra 1 SIMPLE ‘gal’ NULL ‘ALL’ ‘IDX_F70E6EB7A76ED395’ NULL NULL NULL 100.00 ‘Using where; 使用文件排序’ 1 SIMPLE ‘u’ NULL ‘eq_ref’ ‘PRIMARY,UNIQ_1483A5E9BF396750’ ‘PRIMARY ‘108’ ‘homestead.gal.id’ ‘100.00’ NULL

和，

id select_type 表分区 type possible_keys key key_len ref rows filtered Extra 1 SIMPLE ‘gal’ NULL ‘ALL’ NULL NULL NULL NULL 100.00 ‘Using filesort’

如我们所见，我们有最坏的连接类型情况： ALL 对于我们的两个查询。

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

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

确保我们正在使用索引。 在我们的例子中， created_at 是一个很好的候选人，因为它是我们订购的领域。 这样，我们就有了 ORDER BY 和 LIMIT 在不扫描和排序完整结果集的情况下执行。 按前导表中的列排序。 通常情况下，如果 ORDER BY 正在按不是连接顺序中第一个的表中的字段进行操作，则无法使用索引。 不要按表达式排序。 表达式和函数不允许使用索引 ORDER BY. 当心一个…

阅读更多