如何为更快的站点优化SQL查询

  • A+
所属分类:MySQL

有了像WordPress这样的动态、数据库驱动的网站,您可能仍然有一个问题:数据库查询减慢了站点的速度。

在这篇文章中,我将向您介绍如何识别导致瓶颈的查询,如何理解它们的问题,以及快速修复和其他加快速度的方法。我将使用我们最近处理的一个实际查询,该查询减慢了deliciousbrains.com.

鉴定

修复缓慢SQL查询的第一步是查找它们。艾希礼歌颂调试插件的查询监视器在之前的博客上,正是这个插件的数据库查询特性使它成为识别缓慢SQL查询的宝贵工具。插件报告页面请求期间执行的所有数据库查询。它允许您通过调用它们的代码或组件(插件、主题或WordPress核心)过滤它们,并突出显示重复和缓慢的查询:

如何为更快的站点优化SQL查询插图

如果您不想在生产站点上安装调试插件(可能您担心增加一些性能开销),可以选择打开MySQL慢查询日志,它记录所有需要一定时间执行的查询。这相对来说很简单配置设置将查询记录到何处。由于这是服务器级的调整,因此性能影响将小于站点上的调试插件,但是如果不使用它,则应该关闭。

理解

一旦您找到了要改进的昂贵查询,下一步就是尝试理解是什么使查询变慢。最近,在我们的站点开发过程中,我们发现了一个需要大约8秒才能执行的查询!


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<span class="token keyword">SELECT</span>
    l<span class="token punctuation">.</span>key_id<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>order_id<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>activation_email<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>licence_key<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>software_product_id<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>software_version<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>activations_limit<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>created<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>renewal_type<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>renewal_id<span class="token punctuation">,</span>
    l<span class="token punctuation">.</span>exempt_domain<span class="token punctuation">,</span>
    s<span class="token punctuation">.</span>next_payment_date<span class="token punctuation">,</span>
    s<span class="token punctuation">.</span><span class="token keyword">status</span><span class="token punctuation">,</span>
    pm2<span class="token punctuation">.</span>post_id <span class="token keyword">AS</span> <span class="token string">'product_id'</span><span class="token punctuation">,</span>
    pm<span class="token punctuation">.</span>meta_value <span class="token keyword">AS</span> <span class="token string">'user_id'</span>
<span class="token keyword">FROM</span>
    oiz6q8a_woocommerce_software_licences l
        <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span>
    oiz6q8a_woocommerce_software_subscriptions s <span class="token keyword">ON</span> s<span class="token punctuation">.</span>key_id <span class="token operator">=</span> l<span class="token punctuation">.</span>key_id
        <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span>
    oiz6q8a_posts p <span class="token keyword">ON</span> p<span class="token punctuation">.</span>ID <span class="token operator">=</span> l<span class="token punctuation">.</span>order_id
        <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span>
    oiz6q8a_postmeta pm <span class="token keyword">ON</span> pm<span class="token punctuation">.</span>post_id <span class="token operator">=</span> p<span class="token punctuation">.</span>ID
        <span class="token operator">AND</span> pm<span class="token punctuation">.</span>meta_key <span class="token operator">=</span> <span class="token string">'_customer_user'</span>
        <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span>
    oiz6q8a_postmeta pm2 <span class="token keyword">ON</span> pm2<span class="token punctuation">.</span>meta_key <span class="token operator">=</span> <span class="token string">'_software_product_id'</span>
        <span class="token operator">AND</span> pm2<span class="token punctuation">.</span>meta_value <span class="token operator">=</span> l<span class="token punctuation">.</span>software_product_id
<span class="token keyword">WHERE</span>
    p<span class="token punctuation">.</span>post_type <span class="token operator">=</span> <span class="token string">'shop_order'</span>
        <span class="token operator">AND</span> pm<span class="token punctuation">.</span>meta_value <span class="token operator">=</span> <span class="token number">279</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> s<span class="token punctuation">.</span>next_payment_date

我们使用WooCommerce和自定义版本的WooCommerce软件订阅插件来运行我们的插件商店。此查询的目的是在我们知道客户号码的情况下获取客户的所有订阅。WooCommerce有一个有点复杂的数据模型,即使订单存储为自定义POST类型,但客户的id(对于每个客户获得为其创建的WordPress用户的商店)并不存储在

1
post_author

,而是作为一段后元数据。软件订阅插件创建的自定义表也有几个连接。让我们深入了解查询。

MySQL是你的朋友

MySQL有一条方便的语句

1
<span class="token constant">DESCRIBE</span>

它可用于输出有关表的结构的信息,如其列、数据类型、默认值。所以如果你

1
<span class="token constant">DESCRIBE</span> wp_postmeta<span class="token punctuation">;</span>

您将看到以下结果:

场域 类型 钥匙 违约 额外
元id Bigint(20) PRI 自动增量
后ID Bigint(20) 穆尔 0
元键 varchar(255) 穆尔
元值 长文本

很酷,但你可能已经知道了。但你知道吗

1
<span class="token constant">DESCRIBE</span>

语句前缀实际上可以用于

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

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

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

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

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

口供?更常见的是它的同义词。

1
<span class="token constant">EXPLAIN</span>

并将向我们提供关于如何执行该语句的详细信息。

以下是我们缓慢查询的结果:

ID 选择类型 类型 可能键 钥匙 键伦 参考文献 额外
1 简约 PM2 参考文献 元键 元键 576 常量 28 使用WHERE;使用临时;使用文件
1 简约 下午 参考文献 后置id、元键 元键 576 常量 37456 使用位置
1 简约 p EQ参考文献 主,类型状态日期 初等 8 deliciousbrainsdev.pm.post_id 1 使用位置
1 简约 l 参考文献 主,序号 有序id 8 deliciousbrainsdev.pm.post_id 1 使用索引条件
1 简约 s EQ参考文献 初等 初等 8 deliciousbrainsdev.l.key_id 1

乍一看,这并不容易理解。幸运的是,在SitePoint的那些人已经建立了一个理解声明的综合指南.

最重要的一栏是

1
type

,它描述了表是如何连接的。如果你看到

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

然后,这意味着MySQL从磁盘读取整个表,增加I/O率并增加CPU的负载。这就是所谓的“全表扫描”(稍后将详细介绍)。

这个

1
rows

列也很好地指示了MySQL必须做什么,因为这显示了它查找了多少行才能找到结果。

1
Explain

还提供了更多的信息,我们可以使用优化。例如,PM2表(Wp_Postmeta),它告诉我们

1
Using filesort

,因为我们要求使用

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

声明中的条款。如果我们也对查询进行分组,就会增加执行的开销。

视觉调查

MySQL工作台是另一个方便的,免费的工具,为这种类型的调查。对于运行在MySQL 5.6及更高版本上的数据库,

1
<span class="token constant">EXPLAIN</span>

可以输出为JSON,MySQLWorkbench将该JSON转换为语句的可视化执行计划:

如何为更快的站点优化SQL查询插图1

它通过按成本对查询的部分着色,自动提请您注意问题。我们可以马上看到连接到

1
wp_woocommerce_software_licences

(别名l)表有一个严重的问题。

查询的这一部分正在执行完整的表扫描,应该尽量避免,因为它使用非索引列。

1
order_id

之间的连接。

1
wp_woocommerce_software_licences

表到

1
wp_posts

桌子。对于慢速查询来说,这是一个常见的问题,也是一个很容易解决的问题。

指标

1
order_id

是表中标识数据的一个非常重要的部分,如果我们像这样查询,我们应该有一个指数在列上,否则MySQL将逐字逐句地扫描表的每一行,直到找到所需的行为止。让我们添加一个索引,看看它做了什么:


1
<span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> order_id <span class="token keyword">ON</span> wp_woocommerce_software_licences<span class="token punctuation">(</span>order_id<span class="token punctuation">)</span>

如何为更快的站点优化SQL查询插图2

哇,通过添加索引,我们已经减少了5秒以上的查询时间,干得好!

了解您的查询

检查查询--按联接连接,按子查询查询子查询。它能做一些它不需要做的事情吗?可以进行优化吗?

在本例中,我们使用

1
order_id

,同时将语句限制为

1
shop_order

...这是为了加强数据完整性,以确保我们只使用正确的订单记录。但是,它实际上是查询中的一个冗余部分。我们知道,可以肯定的是,表中的软件许可行具有

1
order_id

与POST表中的WooCommerce订单相关,因为这是在PHP插件代码中强制执行的。让我们移除联接,看看这是否改善了一些事情:

如何为更快的站点优化SQL查询插图3

这不是一个很大的节省,但查询现在不到3秒。

把所有东西都藏起来!

如果您的服务器没有MySQL查询缓存默认情况下,它是值得打开的。这意味着MySQL将保存与结果一起执行的所有语句的记录,如果随后执行相同的语句,则返回缓存的结果。缓存不会过时,因为当表被更改时,MySQL会刷新缓存。

QueryMonitor发现,我们的查询在一个页面加载上运行了4次,尽管启用MySQL查询缓存是件好事,但是在一个请求中重复读取数据库的操作应该避免完全停止。PHP代码中的静态缓存是解决此问题的一种简单而有效的方法。基本上,您是在第一次请求查询时从数据库中获取查询结果,并将其存储在类的静态属性中,然后后续调用将从静态属性返回结果:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<span class="token keyword">class</span> <span class="token class-name">WC_Software_Subscription</span> <span class="token punctuation">{</span>

    <span class="token keyword">protected</span> <span class="token keyword">static</span> <span class="token variable">$subscriptions</span> <span class="token operator">=</span> <span class="token keyword">array</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">function</span> <span class="token function">get_user_subscriptions</span><span class="token punctuation">(</span> <span class="token variable">$user_id</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span> <span class="token keyword">isset</span><span class="token punctuation">(</span> <span class="token keyword">static</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token variable">$subscriptions</span><span class="token punctuation">[</span> <span class="token variable">$user_id</span> <span class="token punctuation">]</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword">return</span> <span class="token keyword">static</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token variable">$subscriptions</span><span class="token punctuation">[</span> <span class="token variable">$user_id</span> <span class="token punctuation">]</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        <span class="token keyword">global</span> <span class="token variable">$wpdb</span><span class="token punctuation">;</span>

        <span class="token variable">$sql</span> <span class="token operator">=</span> <span class="token single-quoted-string string">'...'</span><span class="token punctuation">;</span>

        <span class="token variable">$results</span> <span class="token operator">=</span> <span class="token variable">$wpdb</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">get_results</span><span class="token punctuation">(</span> <span class="token variable">$sql</span><span class="token punctuation">,</span> <span class="token constant">ARRAY_A</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token keyword">static</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token variable">$subscriptions</span><span class="token punctuation">[</span> <span class="token variable">$user_id</span> <span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token variable">$results</span><span class="token punctuation">;</span>

        <span class="token keyword">return</span> <span class="token variable">$results</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

缓存具有请求的生存期,更具体地说,是实例化对象的生存期。如果您正在查看跨请求的持久化查询结果,则需要实现持久的查询结果。对象缓存...但是,您的代码需要负责设置缓存,并在基础数据更改时使缓存条目无效。

跳出框框思考

我们还可以采取其他方法来尝试和加快查询执行,这些方法所涉及的工作要比调整查询或添加索引要多一些。查询中最慢的部分之一是将表从Customer ID连接到ProductID,我们必须为每个客户这样做。如果我们只加入了一次,这样我们就可以在需要的时候获取客户的数据,那该怎么办呢?

您可以通过创建一个存储许可数据的表,以及所有许可证的用户id和产品id,并对特定客户的数据进行查询,从而对数据进行反美化。您需要使用MySQL触发器在……上面

1
<span class="token constant">INSERT</span><span class="token operator">/</span><span class="token constant">UPDATE</span><span class="token operator">/</span><span class="token constant">DELETE</span>

但是,这将大大提高查询该数据的性能。

类似地,如果有许多联接减慢了MySQL中的查询速度,则可以更快地将查询分解为两个或多个语句,并在PHP中分别执行它们,然后在代码中收集和过滤结果。拉勒维尔做了一些类似的事情急装雄辩的人际关系。

WordPress的查询速度可能较慢。

1
wp_posts

表,如果您有大量的数据,以及许多不同的自定义POST类型。如果发现查询POST类型很慢,请考虑从自定义POST类型存储模型移到自定义表.

avatar

发表评论

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