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

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

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

鉴定

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

如果您不想在生产站点上安装调试插件(可能您担心增加一些性能开销)，可以选择打开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用户的商店)并不存储在

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

MySQL是你的朋友

MySQL有一条方便的语句

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

您将看到以下结果：

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

语句前缀实际上可以用于

, 

, 

, 

和

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

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

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

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

最重要的一栏是

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

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

这个

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

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

，因为我们要求使用

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

视觉调查

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

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

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

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

解

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

之间的连接。

表到

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

指标

是表中标识数据的一个非常重要的部分，如果我们像这样查询，我们应该有一个指数在列上，否则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>

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

了解您的查询

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

在本例中，我们使用

，同时将语句限制为

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

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

这不是一个很大的节省，但查询现在不到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触发器在……上面

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

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

WordPress的查询速度可能较慢。

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