如何为更快的站点优化 SQL 查询
本文最初发表于 Delicious Brains 博客，经许可在此重新发布。
您知道，一个快速的网站 == 更快乐的用户、更高的 Google 排名以及更高的转化率。 也许您甚至认为您的 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 表类型 possible_keys key key_len ref rows Extra 1 SIMPLE pm2 ref meta_key meta_key 576 const 28 Using where; 使用临时的； 使用文件排序 1 SIMPLE pm ref post_id,meta_key meta_key 576 const 37456 使用 where 1 SIMPLE p eq_ref PRIMARY,type_status_date PRIMARY 8 deliciousbrainsdev.pm.post_id 1 使用 where 1 SIMPLE l ref PRIMARY,order_id order_id 8 deliciousbrainsdev.pm.post_id 1 使用索引条件; 使用 where 1 SIMPLE s eq_ref PRIMARY PRIMARY 8 deliciousbrainsdev.l.key_id 1 NULL
乍一看，这不是很容易解释。 幸运的是，SitePoint 的人们整理了一份综合指南来理解该声明。
最重要的列是 type，它描述了表的连接方式。 如果你看到 ALL 那么这意味着 MySQL 正在从磁盘读取整个表，增加 I/O 速率并增加 CPU 负载。 这被称为“全表扫描”（稍后会详细介绍）。
这 rows column 也很好地指示了 MySQL 必须做什么，因为它显示了它查找了多少行以找到结果。
Explain 还为我们提供了更多可用于优化的信息。 例如，pm2 表 (wp_postmeta)，它告诉我们我们是 Using 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 将逐字扫描表的每一行，直到找到需要的行。 让我们添加一个索引，看看它做了什么：
CREATE INDEX order_id ON wp_woocommerce_software_licences(order_id)
哇，通过添加该索引，我们已经设法将查询时间缩短了 5 多秒，干得好！
了解您的查询
检查查询——通过连接连接，通过子查询进行子查询。 它会做不需要做的事情吗？ 可以进行优化吗？
在这种情况下，我们使用 order_id，同时限制语句发布类型 shop_order. 这是为了加强数据完整性，以确保我们只使用正确的订单记录。 但是，它实际上是查询的冗余部分。 我们知道可以肯定的是，表中的软件许可证行有一个 order_id 与帖子表中的 WooCommerce 订单相关，因为这是在 PHP 插件代码中强制执行的。 让我们删除连接，看看是否有改进：
这不是一个巨大的节省，但查询现在不到 3 秒。
缓存所有的东西！
如果您的服务器没有默认启用 MySQL 查询缓存，那么值得启用它。 这意味着 MySQL 将保留所有执行结果的记录，如果随后执行相同的语句，则返回缓存的结果。 缓存不会过时，因为 MySQL 会在表更改时刷新缓存。
Query Monitor 发现我们的查询在单个页面加载时运行了 4 次，虽然开启 MySQL 查询缓存是件好事，但确实应该避免在一个请求中重复读取数据库。 PHP 代码中的静态缓存是解决此问题的一种简单且非常有效的方法。 基本上，您是在第一次请求查询时从数据库中获取查询结果，并将它们存储在类的静态属性中，然后后续调用将从静态属性返回结果：
class WC_Software_Subscription {
protected static $subscriptions = array();
public static function get_user_subscriptions( $user_id ) {
if ( isset( static::$subscriptions[ $user_id ] ) ) {
return static::$subscriptions[ $user_id ];
}
global $wpdb;
$sql = '...';
$results = $wpdb->get_results( $sql, ARRAY_A );
static::$subscriptions[ $user_id ] = $results;
return $results;
}
}
缓存具有请求的生命周期，更具体地说是实例化对象的生命周期。 如果您正在查看跨请求的持久查询结果，那么您将需要实现一个持久对象缓存。 但是，您的代码需要负责设置缓存，并在基础数据更改时使缓存条目失效。
外箱思考
我们可以采用其他方法来尝试加快查询执行速度，这些方法涉及的工作比仅仅调整查询或添加索引要多一些。 我们查询中最慢的部分之一是连接表以从客户 ID 到产品 ID 的工作，我们必须为每个客户执行此操作。 如果我们只做一次所有连接，这样我们就可以在需要时获取客户数据会怎么样？
您可以通过创建一个存储许可证数据的表以及所有许可证的用户 ID 和产品 ID 来对数据进行非规范化，并只针对特定客户查询。 您需要使用 MySQL 触发器重建表 INSERT/UPDATE/DELETE 到许可证表（或其他取决于数据如何更改），但这将显着提高查询该数据的性能。
类似地，如果大量连接减慢了您在 MySQL 中的查询，将查询分解为两个或多个语句并在 PHP 中分别执行它们，然后在代码中收集和过滤结果可能会更快。 Laravel 通过 Eloquent 中的预加载关系来做类似的事情。
WordPress 可能容易在 wp_posts 表，如果您有大量数据和许多不同的自定义帖子类型。 如果您发现查询您的帖子类型很慢，请考虑从自定义帖子类型存储模型转移到自定义表。
结果
通过这些查询优化方法，我们设法将查询时间从 8 秒减少到 2 秒多一点，并将调用次数从 4 次减少到 1 次。请注意，这些查询时间是在我们的计算机上运行时记录的开发环境，并且在生产中会更快。
我希望这对跟踪慢速查询并修复它们有帮助。 查询优化似乎是一项可怕的任务，但一旦您尝试并快速取得成功，您就会开始发现错误并想进一步改进。
