- A+
有了像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用户的商店)并不存储在
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转换为语句的可视化执行计划:
它通过按成本对查询的部分着色,自动提请您注意问题。我们可以马上看到连接到
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>
哇,通过添加索引,我们已经减少了5秒以上的查询时间,干得好!
了解您的查询
检查查询--按联接连接,按子查询查询子查询。它能做一些它不需要做的事情吗?可以进行优化吗?
在本例中,我们使用
1 | order_id |
,同时将语句限制为
1 | shop_order |
...这是为了加强数据完整性,以确保我们只使用正确的订单记录。但是,它实际上是查询中的一个冗余部分。我们知道,可以肯定的是,表中的软件许可行具有
1 | order_id |
与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">></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类型存储模型移到自定义表.