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

发表评论

A+

所属分类：MySQL

一个非常简单但非常有用的工具是查询分析。启用分析是获得运行查询的更准确时间估计的简单方法。这是一个两步的过程。首先，我们必须启用特征分析。然后，我们打电话给

1	<span class="token keyword">show</span> profiles

实际获得查询运行时间。

让我们想象一下，我们的数据库中有以下插入(假设用户1和库1已经创建)：


1
2
3
4
5
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>images<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>gallery_id<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>original_filename<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>filename<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>description<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span>

<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'me.jpg'</span><span class="token punctuation">,</span> <span class="token string">'me.jpg'</span><span class="token punctuation">,</span> <span class="token string">'A photo of me walking down the street'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>

<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'dog.jpg'</span><span class="token punctuation">,</span> <span class="token string">'dog.jpg'</span><span class="token punctuation">,</span> <span class="token string">'A photo of my dog on the street'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>

<span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'cat.jpg'</span><span class="token punctuation">,</span> <span class="token string">'cat.jpg'</span><span class="token punctuation">,</span> <span class="token string">'A photo of my cat walking down the street'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>

<span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'purr.jpg'</span><span class="token punctuation">,</span> <span class="token string">'purr.jpg'</span><span class="token punctuation">,</span> <span class="token string">'A photo of my cat purring'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

显然，这样的数据量不会带来任何麻烦，但是让我们使用它来做一个简单的概要文件。让我们考虑以下查询：


1
2
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>images<span class="token punctuation">`</span> <span class="token keyword">AS</span> i

<span class="token keyword">WHERE</span> i<span class="token punctuation">.</span>description <span class="token operator">LIKE</span> <span class="token string">'%street%'</span><span class="token punctuation">;</span>

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

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


1
2
3
4
<span class="token keyword">set</span> profiling <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>images<span class="token punctuation">`</span> <span class="token keyword">AS</span> i

<span class="token keyword">WHERE</span> i<span class="token punctuation">.</span>description <span class="token operator">LIKE</span> <span class="token string">'%street%'</span><span class="token punctuation">;</span>

<span class="token keyword">show</span> profiles<span class="token punctuation">;</span>

结果如下：

查询ID

持续时间

查询

0.00016950

显示警告

0.00039200

选择*从

homestead

images

就像I\n在这里的描述，比如\‘%Street%\nLIMIT 0，1000

0.00037600

显示钥匙

homestead

images

0.00034625

显示像“宅基地”这样的数据库

0.00027600

显示表

homestead

比如“图像”

0.00024950

选择*从

homestead

images

0=1

0.00104300

显示完整的列

homestead

images

就像‘id’

如我们所见，

1	<span class="token keyword">show</span> profiles<span class="token punctuation">;</span>

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

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

我们可以依靠对sql的了解和即兴发挥，也可以依赖mysql。

1	<span class="token keyword">explain</span>

根据实际信息命令并提高查询性能。

解释用于获取查询执行计划，或MySQL将如何执行我们的查询。它适用于

1	<span class="token keyword">SELECT</span>

1	<span class="token keyword">DELETE</span>

1	<span class="token keyword">INSERT</span>

1	<span class="token keyword">REPLACE</span>

，和

1	<span class="token keyword">UPDATE</span>

语句，并显示来自优化器的有关语句执行计划的信息。这个正式文件很好地描述了

1	<span class="token keyword">explain</span>

可以帮助我们：

在解释的帮助下，您可以看到应该将索引添加到表中的位置，以便通过使用索引查找行来使语句执行得更快。还可以使用EXPLAIN检查优化器是否以最佳顺序加入表。

举例说明

1	<span class="token keyword">explain</span>

，我们将使用

1	UserManager<span class="token punctuation">.</span>php

若要通过电子邮件查找用户：


1
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>users<span class="token punctuation">`</span> <span class="token keyword">WHERE</span> email <span class="token operator">=</span> <span class="token string">'claudio.ribeiro@examplemail.com'</span><span class="token punctuation">;</span>

使用

1	<span class="token keyword">explain</span>

命令，我们只需在选择类型查询之前将其放在前面：


1
<span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>users<span class="token punctuation">`</span> <span class="token keyword">WHERE</span> email <span class="token operator">=</span> <span class="token string">'claudio.ribeiro@examplemail.com'</span><span class="token punctuation">;</span>

这就是结果(向右滚动查看所有内容)：

ID	选择类型	表	隔断	类型	可能键	钥匙	键伦	参考文献	行	滤过	额外
1	简约	“用户”	零	康斯特	‘UNIQ_1483A5E9E7927C74’	‘UNIQ_1483A5E9E7927C74’	‘182’	康斯特	100.00	零

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

1
id

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

选择查询的类型。这个领域可以采用许多不同的值，因此我们将重点讨论最重要的值：
- 1
 SIMPLE
 
 *没有子查询或联合的简单查询
- 1
 PRIMARY
 
 ：SELECT位于联接中最外层的查询中
- 1
 DERIVED
 
 ：SELECT是FROM中子查询的一部分
- 1
 SUBQUERY
 
 子查询中的第一个选择
- 1
 UNION
 
 ：SELECT是工会的第二个或更高版本的语句。
中可以显示的值的完整列表。

1
select_type

字段可以找到这里.
1
table

*行所引用的表。
1
type

这个字段是MySQL如何连接所使用的表的。这可能是最重要的字段中的解释输出。它可以指示缺少的索引，也可以显示应该如何重写查询。该字段的可能值如下(从最佳类型到最差类型排序)：
- 1
 system
 
 ：：这张表有零行或一行。
- 1
 const
 
 该表只有一行匹配的索引。这是最快的连接类型。
- 1
 eq_ref
 
 索引的所有部分都由JOIN使用，索引要么是PRIMARY_KEY，要么是UNIQUE NOTNULL。
- 1
 ref
 
 ：：索引列的所有匹配行都是针对上一个表中的每个行组合读取的。此类型的联接通常出现在索引列中。
 
 1
 =
 
 或
 
 1
 <=>
 
 操作员。
- 1
 fulltext
 
 连接使用表全文索引。
- 1
 ref_or_null
 
 这与ref相同，但也包含列中带有空值的行。
- 1
 index_merge
 
 ：：联接使用索引列表来生成结果集。控件的键列。
 
 1
 explain
 
 将包含使用的密钥。
- 1
 unique_subquery
 
 IN子查询只返回表中的一个结果，并使用主键。
- 1
 range
 
 *索引用于查找特定范围内的匹配行。
- 1
 index
 
 *扫描整个索引树以查找匹配的行。
- 1
 all
 
 对整个表进行扫描，以找到连接的匹配行。这是最糟糕的联接类型，通常表示表中缺少适当的索引。
1
possible_keys

*显示MySQL可用于从表中查找行的键。在实践中，这些密钥可以使用，也可以不使用。
1
keys

*指示MySQL使用的实际索引。MySQL总是寻找可用于查询的最佳密钥。在连接许多表时，它可能会找出其他一些键，这些键没有在

1
possible_keys

但更理想。
1
key_len

*指示查询优化器选择使用的索引的长度。
1
ref

：：显示与键列中命名的索引相比较的列或常量。
1
rows

*列出为产生输出而检查的记录数。这是一个非常重要的指标；检查的记录越少，越好。
1
Extra

*载有补充资料。值，例如

1
Using filesort

或

1
Using temporary

在本列中，可能表示一个麻烦的查询。

的完整文档

1	<span class="token keyword">explain</span>

输出格式可找到在正式的MySQL页面上.

回到我们的简单查询：它是一个

1	<span class="token keyword">SIMPLE</span>

具有连接类型的SELECT类型。这是我们可能拥有的最好的查询案例。但是，当我们需要更大和更复杂的查询时，会发生什么呢？

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


1
2
3
4
<span class="token keyword">SELECT</span> gal<span class="token punctuation">.</span>name<span class="token punctuation">,</span> gal<span class="token punctuation">.</span>description<span class="token punctuation">,</span> img<span class="token punctuation">.</span>filename<span class="token punctuation">,</span> img<span class="token punctuation">.</span>description <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>users<span class="token punctuation">`</span> <span class="token keyword">AS</span> users

<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>galleries<span class="token punctuation">`</span> <span class="token keyword">AS</span> gal <span class="token keyword">ON</span> users<span class="token punctuation">.</span>id <span class="token operator">=</span> gal<span class="token punctuation">.</span>user_id

<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>images<span class="token punctuation">`</span> <span class="token keyword">AS</span> img <span class="token keyword">on</span> img<span class="token punctuation">.</span>gallery_id <span class="token operator">=</span> gal<span class="token punctuation">.</span>id

<span class="token keyword">WHERE</span> img<span class="token punctuation">.</span>description <span class="token operator">LIKE</span> <span class="token string">'%dog%'</span><span class="token punctuation">;</span>

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

1	<span class="token keyword">explain</span>


1
2
3
4
<span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> gal<span class="token punctuation">.</span>name<span class="token punctuation">,</span> gal<span class="token punctuation">.</span>description<span class="token punctuation">,</span> img<span class="token punctuation">.</span>filename<span class="token punctuation">,</span> img<span class="token punctuation">.</span>description <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>users<span class="token punctuation">`</span> <span class="token keyword">AS</span> users

<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>galleries<span class="token punctuation">`</span> <span class="token keyword">AS</span> gal <span class="token keyword">ON</span> users<span class="token punctuation">.</span>id <span class="token operator">=</span> gal<span class="token punctuation">.</span>user_id

<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>images<span class="token punctuation">`</span> <span class="token keyword">AS</span> img <span class="token keyword">on</span> img<span class="token punctuation">.</span>gallery_id <span class="token operator">=</span> gal<span class="token punctuation">.</span>id

<span class="token keyword">WHERE</span> img<span class="token punctuation">.</span>description <span class="token operator">LIKE</span> <span class="token string">'%dog%'</span><span class="token punctuation">;</span>

这将产生以下结果(滚动显示所有单元格)：

ID	选择类型	表	隔断	类型	可能键	钥匙	键伦	参考文献	行	滤过
1	简约	“用户”	零	“索引”	‘PRIMARY，UNIQ_1483A5E9BF396750’	‘UNIQ_1483A5E9BF396750’	‘108’	零	100.00	“使用指数”
1	简约	“女孩”	零	“参考文献”	‘PRIMARY，UNIQ_F70E6EB7BF396750，IDX_F70E6EB7A76ED395’	‘UNIQ_1483A5E9BF396750’	‘108’	‘homsti.users.id’	100.00	零
1	简约	“IMG”	零	“参考文献”	‘IDX_E01FBE6A4E7AF8F’	‘IDX_E01FBE6A4E7AF8F’	‘109’	“家园.半.id”	‘25.00’	“用在哪里”

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

正如我们前面所看到的，我们首先应该查看的主要列是

1	<span class="token keyword">type</span>

列和

1	<span class="token keyword">rows</span>

柱子。目标应该在

1	<span class="token keyword">type</span>

列并尽可能减少

1	<span class="token keyword">rows</span>

列。

第一个查询的结果是

1	<span class="token keyword">index</span>

，这根本不是一个好结果。这意味着我们可能会改进它。

查看我们的查询，有两种方法来处理它。首先，

Users

没有使用表。我们要么展开查询以确保我们的目标是用户，要么我们应该完全删除

users

查询的一部分。它只会给我们的整体性能增加复杂性和时间。


1
2
3
<span class="token keyword">SELECT</span> gal<span class="token punctuation">.</span>name<span class="token punctuation">,</span> gal<span class="token punctuation">.</span>description<span class="token punctuation">,</span> img<span class="token punctuation">.</span>filename<span class="token punctuation">,</span> img<span class="token punctuation">.</span>description <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>galleries<span class="token punctuation">`</span> <span class="token keyword">AS</span> gal

<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>images<span class="token punctuation">`</span> <span class="token keyword">AS</span> img <span class="token keyword">on</span> img<span class="token punctuation">.</span>gallery_id <span class="token operator">=</span> gal<span class="token punctuation">.</span>id

<span class="token keyword">WHERE</span> img<span class="token punctuation">.</span>description <span class="token operator">LIKE</span> <span class="token string">'%dog%'</span><span class="token punctuation">;</span>

所以现在我们有了完全相同的结果。让我们看看

1	<span class="token keyword">explain</span>

ID	选择类型	表	隔断	类型	可能键	钥匙	键伦	参考文献	行	滤过	额外
1	简约	“女孩”	零	“全部”	‘PRIMARY，UNIQ_1483A5E9BF396750’	零	零	零	100.00	零
1	简约	“IMG”	零	“参考文献”	‘IDX_E01FBE6A4E7AF8F’	‘IDX_E01FBE6A4E7AF8F’	‘109’	“家园.半.id”	‘25.00’	“用在哪里”

我们只剩下一个

1	<span class="token keyword">ALL</span>

在类型上。当

1	<span class="token keyword">ALL</span>

可能是最糟糕的连接类型，有时它也是唯一的选择。根据我们的要求，我们想要所有的画廊图像，所以我们需要浏览整个画廊表。虽然索引在试图查找表上的特定信息时确实很好，但当我们需要表中的所有信息时，它们无法帮助我们。当我们遇到这样的情况时，我们不得不求助于另一种方法，比如缓存。

我们能做的最后一个改进，因为我们要面对的是

1	<span class="token operator">LIKE</span>

，将全文索引添加到我们的Description字段中。这样，我们就可以更改

1	<span class="token operator">LIKE</span>

转到

1	<span class="token keyword">match</span><span class="token punctuation">(</span><span class="token punctuation">)</span>

并提高性能。更多关于全文索引的信息。可以在这里找到.

还有两个非常有趣的案例我们必须看：

newest

和

我们的应用程序中的功能。这些适用于画廊，并触及一些我们应该注意的角落案例：


1
2
3
4
5
<span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>galleries<span class="token punctuation">`</span> <span class="token keyword">AS</span> gal

<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>users<span class="token punctuation">`</span> <span class="token keyword">AS</span> u <span class="token keyword">ON</span> u<span class="token punctuation">.</span>id <span class="token operator">=</span> gal<span class="token punctuation">.</span>user_id

<span class="token keyword">WHERE</span> u<span class="token punctuation">.</span>id <span class="token operator">=</span> <span class="token number">1</span>

<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> gal<span class="token punctuation">.</span>created_at <span class="token keyword">DESC</span>

<span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">;</span>

以上是相关的画廊。


1
2
3
<span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>homestead<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>galleries<span class="token punctuation">`</span> <span class="token keyword">AS</span> gal

<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> gal<span class="token punctuation">.</span>created_at <span class="token keyword">DESC</span>

<span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">;</span>

以上是最新的画廊。

乍一看，这些查询应该非常迅速，因为它们正在使用

1	<span class="token keyword">LIMIT</span>

...大多数查询都是这样

1	<span class="token keyword">LIMIT</span>

...不幸的是，对于我们和我们的应用程序来说，这些查询还使用

1	<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>

...因为我们需要在限制查询之前对所有结果进行排序，所以我们失去了使用

1	<span class="token keyword">LIMIT</span>

因为我们知道

1	<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>

可能很棘手，让我们应用我们的信任

1	<span class="token keyword">explain</span>

ID	选择类型	表	隔断	类型	可能键	钥匙	键伦	参考文献	行	滤过	额外
1	简约	“女孩”	零	“全部”	‘IDX_F70E6EB7A76ED395’	零	零	零	100.00	‘在什么地方使用；使用文件’
1	简约	“u”	零	‘eq_ref’	‘PRIMARY，UNIQ_1483A5E9BF396750’	“PRIMARY”	‘108’	“家园.半.id”	‘100.00’	零

和,

ID	选择类型	表	隔断	类型	可能键	钥匙	键伦	参考文献	行	滤过	额外
1	简约	“女孩”	零	“全部”	零	零	零	零	100.00	“使用文件”

正如我们所看到的，我们遇到了连接类型中最糟糕的情况：

1	<span class="token keyword">ALL</span>

对于我们的两个查询。

历史上，MySQL的

1	<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>

执行，特别是与

1	<span class="token keyword">LIMIT</span>

，常常是MySQL性能问题的原因。这种组合也用于大多数具有大型数据集的交互式应用程序中。像新注册的用户和顶部标签这样的功能通常使用这种组合。

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

发表评论取消回复