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

图片优化和数据库查询优化：提升WordPress网站速度的实用指南

本文最初发表于Delicious Brains博客，经许可在此转载

您知道快速的网站意味着更快乐的用户、更好的谷歌排名和更高的转化率。您甚至可能认为您的WordPress网站已经足够快了：您已经检查了网站性能，从最佳服务器设置实践到慢速代码故障排除，以及将图像卸载到CDN，但这仅仅是全部吗？

对于像WordPress这样的动态、数据库驱动的网站，您可能仍然面临一个问题：数据库查询导致网站速度变慢。

在这篇文章中，我将引导您了解如何识别导致瓶颈的查询，如何理解这些查询的问题，以及快速修复和提高速度的其他方法。我将使用我们最近解决的一个实际查询，该查询减慢了deliciousbrains.com客户门户网站的速度。

查询识别

修复缓慢的SQL查询的第一步是找到它们。Ashley之前在博客中赞扬过Query Monitor调试插件，而该插件的数据库查询功能使其成为识别缓慢SQL查询的宝贵工具。该插件报告页面请求期间执行的所有数据库查询。它允许您按调用它们的代码或组件（插件、主题或WordPress核心）过滤它们，并突出显示重复和缓慢的查询：

如果您不想在生产站点上安装调试插件（也许您担心会增加一些性能开销），可以选择启用MySQL慢查询日志，该日志记录所有需要一定时间才能执行的查询。这相对容易配置和设置查询的日志记录位置。由于这是一个服务器级别的调整，性能影响将小于站点上的调试插件，但在不使用时应将其关闭。

理解查询问题

找到要改进的代价高昂的查询后，下一步是尝试了解是什么导致查询变慢。最近，在对我们网站进行开发时，我们发现一个查询需要大约8秒才能执行！

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

我们使用WooCommerce和WooCommerce软件订阅插件的自定义版本来运行我们的插件商店。此查询的目的是获取我们知道客户编号的客户的所有订阅。WooCommerce具有相当复杂的数据模型，即使订单存储为自定义帖子类型，客户的ID（对于每个客户都为其创建WordPress用户的商店）也不是存储为post_author，而是存储为帖子元数据的一部分。软件订阅插件还创建了几个自定义表连接。让我们深入了解一下查询。

利用MySQL工具

MySQL提供了一个方便的DESCRIBE语句，可用于输出有关表结构的信息，例如其列、数据类型和默认值。因此，如果您执行DESCRIBE wp_postmeta;，您将看到以下结果：

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
post_id	bigint(20) unsigned	NO	MUL	0
meta_key	varchar(255)	YES	MUL	NULL
meta_value	longtext	YES		NULL

这很酷，但您可能已经知道了。但您是否知道DESCRIBE语句前缀实际上可以用于SELECT、INSERT、UPDATE、REPLACE和DELETE语句？这更常被称为其同义词EXPLAIN，它将为我们提供有关语句如何执行的详细信息。

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	pm2	ref	meta_key	meta_key	576	const	28	Using where; Using temporary; Using filesort
1	SIMPLE	pm	ref	post_id,meta_key	meta_key	576	const	37456	Using where
1	SIMPLE	p	eq_ref	PRIMARY,type_status_date	PRIMARY	8	deliciousbrainsdev.pm.post_id	1	Using where
1	SIMPLE	l	ref	PRIMARY,order_id	order_id	8	deliciousbrainsdev.pm.post_id	1	Using index condition; Using where
1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	8	deliciousbrainsdev.l.key_id	1	NULL

乍一看，这不太容易解释。幸运的是，SitePoint 的朋友们已经编写了一份关于理解该语句的全面指南。

最重要的列是type，它描述了表的连接方式。如果您看到ALL，则表示MySQL正在从磁盘读取整个表，从而增加了I/O速率并增加了CPU负载。这被称为“全表扫描”（稍后将详细介绍）。

rows列也是MySQL必须执行操作的一个很好的指示，因为它显示了它为了找到结果而查看的行数。

EXPLAIN还提供了更多可用于优化的信息。例如，pm2表（wp_postmeta），它告诉我们我们正在使用filesort，因为我们要求使用语句中的ORDER BY子句对结果进行排序。如果我们也对查询进行分组，我们将增加执行的开销。

可视化分析

MySQL Workbench是另一种方便的免费工具，可用于此类调查。对于在MySQL 5.6及更高版本上运行的数据库，EXPLAIN的结果可以输出为JSON，MySQL Workbench将该JSON转换为语句的可视化执行计划：

它通过按成本对查询的部分进行着色来自动引起您的注意。我们可以立即看到，与wp_woocommerce_software_licences（别名l）表的连接存在严重问题。

解决方法

查询的一部分正在执行全表扫描，您应该尽量避免这种情况，因为它使用非索引列order_id作为wp_woocommerce_software_licences表与wp_posts表之间的连接。这是缓慢查询的常见问题，并且可以轻松解决。

添加索引

order_id是表中非常重要的标识数据的一部分，如果我们像这样查询，我们确实应该在该列上添加索引，否则MySQL将逐行扫描表，直到找到所需的行。让我们添加一个索引并看看它会做什么：

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

哇，我们通过添加该索引成功地减少了查询超过5秒的时间，干得好！

了解您的查询

检查查询——逐个连接，逐个子查询。它是否执行了不需要的操作？可以进行哪些优化？

在本例中，我们使用order_id将许可证表连接到帖子表，同时将语句限制为shop_order的帖子类型。这是为了强制数据完整性，以确保我们只使用正确的订单记录。但是，它实际上是查询中冗余的部分。我们知道，表中的软件许可证行具有与帖子表中的WooCommerce订单相关的order_id是一个安全的赌注，因为这是在PHP插件代码中强制执行的。让我们删除连接并看看这是否会改善情况：

这并没有很大的节省，但查询现在不到3秒。

缓存

如果您的服务器默认情况下未启用MySQL查询缓存，则值得启用。这意味着MySQL将保留所有已执行语句及其结果的记录，如果随后执行相同的语句，则将返回缓存的结果。缓存不会过期，因为MySQL在更改表时会刷新缓存。

Query Monitor发现我们的查询在一个页面加载中运行了4次，尽管启用MySQL查询缓存很好，但在一个请求中重复读取数据库实际上应该完全避免。PHP代码中的静态缓存是一种简单且非常有效的方法来解决此问题。基本上，您在第一次请求数据库查询的结果时从数据库中获取它们并将它们存储在类的静态属性中，然后后续调用将从静态属性返回结果：

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

缓存的寿命是请求的寿命，更具体地说，是实例化对象的寿命。如果您希望在请求之间持久保存查询结果，则需要实现持久对象缓存。但是，您的代码需要负责设置缓存，并在基础数据更改时使缓存条目失效。

其他方法

我们可以采取其他方法来尝试加快查询执行速度，这些方法比仅仅调整查询或添加索引需要更多工作。我们查询中最慢的部分之一是从客户ID到产品ID的表连接工作，我们必须为每个客户执行此操作。如果我们只执行一次所有连接，那么我们只需要在需要时获取客户数据怎么办？

您可以通过创建一个表来反规范化数据，该表存储许可证数据以及所有许可证的用户ID和产品ID，只需针对特定客户查询该表即可。您需要使用MySQL触发器在INSERT/UPDATE/DELETE到许可证表（或其他表，具体取决于数据如何更改）时重建该表，但这将显着提高查询该数据的性能。

同样，如果许多连接会减慢MySQL中的查询速度，那么将查询分解为两个或多个语句并在PHP中分别执行它们，然后在代码中收集和过滤结果可能会更快。Laravel通过在Eloquent中急切加载关系来执行类似的操作。

如果您的数据量很大，并且有很多不同的自定义帖子类型，WordPress可能会容易在wp_posts表上出现较慢的查询。如果您发现查询您的帖子类型速度很慢，那么请考虑放弃自定义帖子类型存储模型并使用自定义表。

结果

通过这些查询优化方法，我们设法将查询时间从8秒减少到略高于2秒，并将调用次数从4次减少到1次。请注意，这些查询时间是在我们的开发环境中记录的，在生产环境中会更快。

我希望本指南对您跟踪和修复缓慢的查询有所帮助。查询优化似乎是一项可怕的任务，但是一旦您尝试并获得一些快速成功，您就会开始对它着迷，并希望进一步改进。

以上是如何优化更快站点的SQL查询的详细内容。更多信息请关注PHP中文网其他相关文章！