- A+
一个非常简单但非常有用的工具是查询分析。启用分析是获得运行查询的更准确时间估计的简单方法。这是一个两步的过程。首先,我们必须启用特征分析。然后,我们打电话给
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 | 持续时间 | 查询 | ||||
---|---|---|---|---|---|---|
1 | 0.00016950 | 显示警告 | ||||
2 | 0.00039200 | 选择*从
.
就像I\n在这里的描述,比如\‘%Street%\nLIMIT 0,1000 |
||||
3 | 0.00037600 | 显示钥匙
.
|
||||
4 | 0.00034625 | 显示像“宅基地”这样的数据库 | ||||
5 | 0.00027600 | 显示表
比如“图像” |
||||
6 | 0.00024950 | 选择*从
.
0=1 |
||||
7 | 0.00104300 | 显示完整的列
.
就像‘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 | 零 |
这些结果乍一看不容易理解,所以让我们仔细看看其中的每一个:
-
1id
这只是SELECT中每个查询的顺序标识符。
-
1select_type
选择查询的类型。这个领域可以采用许多不同的值,因此我们将重点讨论最重要的值:
-
1<span class="token keyword">SIMPLE</span>
*没有子查询或联合的简单查询
-
1<span class="token keyword">PRIMARY</span>
:SELECT位于联接中最外层的查询中
-
1DERIVED
:SELECT是FROM中子查询的一部分
-
1SUBQUERY
子查询中的第一个选择
-
1<span class="token keyword">UNION</span>
:SELECT是工会的第二个或更高版本的语句。
中可以显示的值的完整列表。
1select_type字段可以找到这里.
-
-
1<span class="token keyword">table</span>
*行所引用的表。
-
1<span class="token keyword">type</span>
这个字段是MySQL如何连接所使用的表的。这可能是最重要的字段中的解释输出。它可以指示缺少的索引,也可以显示应该如何重写查询。该字段的可能值如下(从最佳类型到最差类型排序):
-
1system
::这张表有零行或一行。
-
1const
该表只有一行匹配的索引。这是最快的连接类型。
-
1eq_ref
索引的所有部分都由JOIN使用,索引要么是PRIMARY_KEY,要么是UNIQUE NOTNULL。
-
1ref
::索引列的所有匹配行都是针对上一个表中的每个行组合读取的。此类型的联接通常出现在索引列中。
1<span class="token operator">=</span>或
1<span class="token operator"><=></span>操作员。
-
1fulltext
连接使用表全文索引。
-
1ref_or_null
这与ref相同,但也包含列中带有空值的行。
-
1index_merge
::联接使用索引列表来生成结果集。控件的键列。
1<span class="token keyword">explain</span>将包含使用的密钥。
-
1unique_subquery
IN子查询只返回表中的一个结果,并使用主键。
-
1range
*索引用于查找特定范围内的匹配行。
-
1<span class="token keyword">index</span>
*扫描整个索引树以查找匹配的行。
-
1<span class="token keyword">all</span>
对整个表进行扫描,以找到连接的匹配行。这是最糟糕的联接类型,通常表示表中缺少适当的索引。
-
-
1possible_keys
*显示MySQL可用于从表中查找行的键。在实践中,这些密钥可以使用,也可以不使用。
-
1<span class="token keyword">keys</span>
*指示MySQL使用的实际索引。MySQL总是寻找可用于查询的最佳密钥。在连接许多表时,它可能会找出其他一些键,这些键没有在
1possible_keys但更理想。
-
1key_len
*指示查询优化器选择使用的索引的长度。
-
1ref
::显示与键列中命名的索引相比较的列或常量。
-
1<span class="token keyword">rows</span>
*列出为产生输出而检查的记录数。这是一个非常重要的指标;检查的记录越少,越好。
-
1Extra
*载有补充资料。值,例如
1<span class="token keyword">Using</span> filesort或
1<span class="token keyword">Using</span> <span class="token keyword">temporary</span>在本列中,可能表示一个麻烦的查询。
的完整文档
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> |
,这根本不是一个好结果。这意味着我们可能会改进它。
查看我们的查询,有两种方法来处理它。首先,
1 | Users |
没有使用表。我们要么展开查询以确保我们的目标是用户,要么我们应该完全删除
1 | 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> |
并提高性能。更多关于全文索引的信息。可以在这里找到.
还有两个非常有趣的案例我们必须看:
1 | newest |
和
1 | related |
我们的应用程序中的功能。这些适用于画廊,并触及一些我们应该注意的角落案例:
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性能问题的原因。这种组合也用于大多数具有大型数据集的交互式应用程序中。像新注册的用户和顶部标签这样的功能通常使用这种组合。
因为这是一个常见的问题,我们还应该应用一小部分共同的解决方案来解决性能问题。