使用索引和解释提高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 持续时间 查询
1 0.00016950 显示警告
2 0.00039200 选择*从

1
homestead

.

1
images

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

3 0.00037600 显示钥匙

1
homestead

.

1
images
4 0.00034625 显示像“宅基地”这样的数据库
5 0.00027600 显示表

1
homestead

比如“图像”

6 0.00024950 选择*从

1
homestead

.

1
images

0=1

7 0.00104300 显示完整的列

1
homestead

.

1
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
      <span class="token keyword">SIMPLE</span>

      *没有子查询或联合的简单查询

    • 1
      <span class="token keyword">PRIMARY</span>

      :SELECT位于联接中最外层的查询中

    • 1
      DERIVED

      :SELECT是FROM中子查询的一部分

    • 1
      SUBQUERY

      子查询中的第一个选择

    • 1
      <span class="token keyword">UNION</span>

      :SELECT是工会的第二个或更高版本的语句。

    中可以显示的值的完整列表。

    1
    select_type

    字段可以找到这里.

  • 1
    <span class="token keyword">table</span>

    *行所引用的表。

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

    这个字段是MySQL如何连接所使用的表的。这可能是最重要的字段中的解释输出。它可以指示缺少的索引,也可以显示应该如何重写查询。该字段的可能值如下(从最佳类型到最差类型排序):

    • 1
      system

      ::这张表有零行或一行。

    • 1
      const

      该表只有一行匹配的索引。这是最快的连接类型。

    • 1
      eq_ref

      索引的所有部分都由JOIN使用,索引要么是PRIMARY_KEY,要么是UNIQUE NOTNULL。

    • 1
      ref

      ::索引列的所有匹配行都是针对上一个表中的每个行组合读取的。此类型的联接通常出现在索引列中。

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

      1
      <span class="token operator">&lt;=&gt;</span>

      操作员。

    • 1
      fulltext

      连接使用表全文索引。

    • 1
      ref_or_null

      这与ref相同,但也包含列中带有空值的行。

    • 1
      index_merge

      ::联接使用索引列表来生成结果集。控件的键列。

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

      将包含使用的密钥。

    • 1
      unique_subquery

      IN子查询只返回表中的一个结果,并使用主键。

    • 1
      range

      *索引用于查找特定范围内的匹配行。

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

      *扫描整个索引树以查找匹配的行。

    • 1
      <span class="token keyword">all</span>

      对整个表进行扫描,以找到连接的匹配行。这是最糟糕的联接类型,通常表示表中缺少适当的索引。

  • 1
    possible_keys

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

  • 1
    <span class="token keyword">keys</span>

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

    1
    possible_keys

    但更理想。

  • 1
    key_len

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

  • 1
    ref

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

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

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

  • 1
    Extra

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

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

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

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: