用索引提高MySQL性能并解释

关键要点

• 使用MySQL的EXPLAIN命令分析和优化查询执行计划，通过揭示连接类型和索引使用等关键信息，确保更高效的数据库操作。
• 实施查询分析来衡量查询的实际运行时间，从而进行有针对性的优化，以减少执行时间并提高整体性能。
• 根据EXPLAIN命令的反馈添加合适的索引，重点关注WHERE子句中使用的列，以加快数据检索速度并提高查询性能。
• 对于搜索操作中涉及的列，考虑使用全文索引来优化性能，尤其是在查询中使用LIKE运算符时。
• 注意ORDER BY与LIMIT组合使用的情况，因为它可能会抵消限制结果的性能优势，尤其是在没有有效使用索引的情况下。

数据库优化通常是提升应用性能的首要关注点，也是最常见的瓶颈。如何衡量和理解需要改进的地方？

一个简单而有效的工具是查询分析。启用分析可以更准确地估计查询的运行时间。这是一个两步过程：首先，启用分析；然后，调用show profiles来获取查询运行时间。

假设数据库中存在以下插入操作（并假设用户1和图库1已创建）：

<code class="language-sql">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');</code>

少量数据不会造成问题，但我们可以用它来进行简单的分析。考虑以下查询：

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

如果照片条目很多，此查询将来可能会成为问题。

要获取此查询的准确运行时间，可以使用以下SQL：

<code class="language-sql">set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;</code>

结果如下所示：

Query_Id	Duration	Query
1	0.00016950	SHOW WARNINGS
2	0.00039200	SELECT * FROM homestead.images AS i WHERE i.description LIKE '%street%' LIMIT 0, 1000
3	0.00037600	SHOW KEYS FROM homestead.images
4	0.00034625	SHOW DATABASES LIKE 'homestead'
5	0.00027600	SHOW TABLES FROM homestead LIKE 'images'
6	0.00024950	SELECT * FROM homestead.images WHERE 0=1
7	0.00104300	SHOW FULL COLUMNS FROM homestead.images LIKE 'id'

show profiles;命令不仅显示原始查询的时间，还显示所有其他查询的时间，从而可以准确地分析查询。

如何改进查询？

可以依靠SQL知识进行改进，或者依靠MySQL的EXPLAIN命令并根据实际信息来提高查询性能。

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

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

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

<code class="language-sql">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');</code>

要使用EXPLAIN命令，只需在SELECT类型查询之前添加它：

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

结果如下（向右滚动查看所有内容）：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	const	UNIQ_1483A5E9E7927C74	UNIQ_1483A5E9E7927C74	182	const	1	100.00	NULL

这些结果一开始不容易理解，让我们仔细看看每一个：

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

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

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

回到我们的应用程序模式，我们可能想要获取所有图库图像。我们可能还只想包含描述中包含单词“cat”的照片。这绝对是我们可以在项目需求中找到的情况。让我们看看查询：

<code class="language-sql">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');</code>

在这个更复杂的情况下，我们应该在EXPLAIN中获得更多信息来分析：

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

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

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

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

如前所述，首先应该查看的主要列是type列和rows列。目标应该是获得type列中更好的值，并尽可能减少rows列的值。

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

查看我们的查询，有两种方法可以解决它。首先，Users表没有被使用。我们要么扩展查询以确保我们正在定位用户，要么应该完全删除查询的用户部分。它只会增加我们整体性能的复杂性和时间。

<code class="language-sql">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');</code>

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

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	PRIMARY,UNIQ_1483A5E9BF396750	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

我们剩下的是ALL类型。虽然ALL可能是最差的连接类型，但也有一些情况下它是唯一的选择。根据我们的要求，我们想要所有图库图像，因此我们需要搜索整个galleries表。当我们需要表中的所有信息时，索引在尝试查找表中的特定信息时非常好，但它们帮不了我们。当我们遇到这种情况时，我们必须求助于其他方法，例如缓存。

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

我们还必须查看两个非常有趣的情况：应用程序中最新的和相关的功能。这些适用于图库，并涉及我们应该注意的一些极端情况：

<code class="language-sql">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');</code>

以上是相关图库。

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

以上是最新的图库。

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

由于我们知道ORDER BY可能很棘手，让我们应用我们可靠的EXPLAIN。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	IDX_F70E6EB7A76ED395	NULL	NULL	NULL	1	100.00	Using where; Using filesort
1	SIMPLE	u	NULL	eq_ref	PRIMARY,UNIQ_1483A5E9BF396750	PRIMARY	108	homestead.gal.id	1	100.00	NULL

以及，

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort

我们可以看到，对于我们的两个查询，我们都有最糟糕的连接类型：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和索引。

关于MySQL性能索引的常见问题解答(FAQ)

MySQL性能索引的重要性是什么？

MySQL性能索引对于优化数据库性能至关重要。它们通过根据已编入索引的列中的值快速访问数据表中的行，显着加快数据检索操作。如果没有索引，MySQL将不得不遍历表中的每一行才能找到相关的行，这可能会非常耗时，尤其对于大型数据库而言。

EXPLAIN命令如何帮助提高MySQL性能？

MySQL中的EXPLAIN命令是一个强大的工具，它提供有关MySQL如何执行查询的信息。它显示读取表的顺序、执行的读取操作类型、可选择的索引以及要检查的估计行数。这些信息可以帮助开发人员优化查询并提高数据库性能。

为什么MySQL不使用任何可能的键？

MySQL不使用任何可能的键可能有几个原因。一个原因可能是优化器估计使用索引需要扫描表的大部分，并决定表扫描会更快。另一个原因可能是WHERE子句中的列与索引中的列不匹配。

如何优化我的MySQL查询？

有几种方法可以优化MySQL查询。一种方法是有效地使用索引。索引可以显着加快数据检索速度。但是，它们会减慢数据修改操作（如INSERT、UPDATE和DELETE）的速度。因此，找到平衡点非常重要。另一种方法是使用EXPLAIN命令来了解MySQL如何执行查询并查找潜在的瓶颈。

MySQL中主键和索引的区别是什么？

MySQL中的主键是一种索引。主键是表中行的唯一标识符。它强制执行列或列组合的唯一性，并确保列或列组合不包含NULL值。另一方面，索引是一种数据结构，它可以提高数据检索操作的速度。它可以应用于任何列或列的组合。

如何在MySQL中创建索引？

可以使用CREATE INDEX语句在MySQL中创建索引。语法如下：CREATE INDEX index_name ON table_name (column1, column2, …);。这将在指定表的指定列上创建一个索引。

MySQL中的复合索引是什么？

复合索引，也称为多列索引，是一个包含多列的索引。在MySQL中，复合索引最多可以包含16列，但已编入索引的列的总大小不能超过767字节。

如何在MySQL中删除索引？

可以使用DROP INDEX语句在MySQL中删除索引。语法如下：DROP INDEX index_name ON table_name;。这将从指定的表中删除指定的索引。

MySQL中聚集索引和非聚集索引的区别是什么？

聚集索引决定表中数据的物理顺序。每个表只能有一个聚集索引。另一方面，非聚集索引不会更改表中数据的物理顺序。相反，它维护一个指向数据行的单独数据结构（索引），从而允许更快地检索数据。

MySQL如何选择要使用的索引？

MySQL使用基于成本的优化器来选择要使用的索引。优化器会估计不同查询执行计划的成本，并选择成本最低的计划。成本是根据要读取的行数、磁盘查找次数、CPU成本和内存使用情况等因素来估计的。

以上是用索引提高MySQL性能并解释的详细内容。更多信息请关注PHP中文网其他相关文章！