一个快速的网站意味着更愉快的用户、更高的Google排名，以及更多的转化。也许你认为你的WordPress网站已经快到极致了——你已经检查了网站性能，从设置服务器的最佳实践，到排查慢代码，再到将图片剥离到CDN，但这就是全部了吗？
对于像WordPress这样的动态数据库驱动网站，你可能仍然面临一个问题：数据库查询正在拖慢你的网站。
在本文中，我将带你了解如何识别导致瓶颈的查询，如何理解它们的问题，以及加速SQL查询执行时间的快速修复方案和其他方法。为了说明目的，我将向你展示我们如何解决一个在deliciousbrains.com上导致变慢的实际查询。
慢SQL查询的识别
修复慢SQL查询的第一步是找到它们。我们之前已经赞扬过调试插件Query Monitor的功能，而这个插件的数据库查询功能确实使其成为识别慢SQL查询和改善数据库性能的宝贵工具。该插件报告页面请求期间执行的所有数据库查询。它允许你按调用它们的代码或组件（插件、主题或WordPress核心）进行筛选，并突出显示重复和慢查询：
如果你不想在生产网站上安装调试插件（也许你担心会增加一些性能开销），你可以选择开启MySQL慢查询日志，它会记录所有执行时间超过一定时间的查询。这配置和设置查询日志位置相对简单。这是一个服务器级别的调整，因此性能影响会比在网站上使用调试插件小，但不在使用时仍应关闭。
理解慢SQL查询
一旦你找到一个想要优化的昂贵查询，下一步就是尝试理解是什么导致查询变慢。在我们网站的开发过程中，我们发现一个查询执行时间约为8秒！
我们使用 WooCommerce 和定制版的 WooCommerce Software Subscriptions 插件来运行我们的插件商店。本查询的目的是获取我们知道其客户编号的客户的所有订阅。WooCommerce 的数据模型有些复杂，因为即使订单存储为自定义文章类型，客户ID（对于为每个客户创建 WordPress 用户的商店）并不存储在 post_author 中，而是存储为文章元数据。
此外，还有几个连接软件订阅插件创建的自定义表的内部连接，使这个 SQL 语句更加复杂和难以理解。让我们深入了解这个查询。
MySQL 是你进行 SQL 查询优化的朋友
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）显示我们正在 Using filesort，这是因为我们使用语句中的 ORDER BY 子句对结果进行排序。如果同时还要对查询进行分组，就会增加执行的开销。
使用执行计划进行可视化调查
MySQL Workbench 是另一个用于此类调查的便捷免费工具。对于运行在 MySQL 5.6 及以上版本的数据库，EXPLAIN 的结果可以输出为 JSON 格式，MySQL Workbench 会将该 JSON 转换为语句的可视化执行计划：
它通过按成本对查询的各个部分进行着色来自动引起你对问题的注意。我们可以立即看到与 wp_woocommerce_software_licences（别名 l）表的连接存在严重问题。让我们来进行一些查询优化！
通过查询优化解决慢 SQL 查询问题
查询的那部分正在执行全表扫描，你应该尽量避免这种情况，因为它使用非索引列 order_id 作为 wp_woocommerce_software_licences 表到 wp_posts 表的连接。这是慢查询的常见问题之一，可以通过简单的方式解决以提高查询性能。
索引
order_id 是表中非常重要的识别数据，如果我们这样查询，确实应该在列上建立索引，否则 MySQL 实际上会扫描表中的每一行，直到找到需要的行。
什么是数据库索引？
无论你是否意识到，你可能已经使用数据库索引相当长的时间了。如果你在 MySQL 中创建过带有主键列（通常称为 id）的表，MySQL 实际上已经为该列创建了一个索引（默认称为 PRIMARY）。
数据库索引提高了从表中检索数据的速度，因为索引用于快速找到正确的数据行，而不必每次都扫描整个表。对于小型数据库，性能提升微乎其微，因为查询小型数据库本身就很快。一旦表开始增长（达到数百万行），你在没有索引的情况下查询表时就会注意到性能受到明显影响。
使用索引时需要考虑一些权衡。索引会占用存储空间，但通常与数据库大小相比占用的空间很小。但是，对于小表来说，拥有索引实际上可能会让 MySQL 工作得更辛苦，因为它必须在运行查询时处理索引，而且写查询会变慢，因为它必须重建索引。所以建议只在表开始增长时才添加索引。
让我们添加一个索引来看看效果如何：
CREATE INDEX order_id ON wp_woocommerce_software_licences(order_id)
哇，通过添加这个索引，我们成功将查询时间减少了 5 秒以上，做得好！但为什么要止步于此呢？还有一些进一步的优化技术可以帮助我们减少执行时间和数据库服务器瓶颈。
了解你的查询
逐个查询地分析——一次一个连接，一个子查询。它是否做了不需要的事情？能否进行优化？
在这种情况下，我们使用 order_id 将 licenses 表连接到 posts 表，同时将语句限制为 shop_order 类型的文章。这是为了强制执行数据完整性，确保我们只使用正确的订单记录。然而，这实际上是查询中的一个冗余部分。我们知道表中的一行软件许可证的 order_id 关联到 posts 表中的 WooCommerce 订单是安全的，因为这是在 PHP 插件代码中强制执行的。让我们删除这个连接，看看是否能改善情况：
这节省的时间并不多，但查询现在在 3 秒以下了。
缓存一切！
本文的前一版建议在服务器默认没有开启的情况下启用 MySQL 查询缓存。然而，MySQL 的查询缓存在 MySQL 8.0 中已完全移除。部分原因是 MySQL 的查询缓存已知在高吞吐量工作负载下扩展性不佳。
相反，MySQL 建议使用中间人（MitM）缓存，可以选择服务器端查询重写或 ProxySQL。
使用 MitM 缓存时，MySQL 中频繁访问的数据会被拦截并缓存，允许直接从缓存中提供。这会加快响应时间并减少数据库负载，因为数据不必每次都检索。
有几种工具和技术可用于在 MySQL 中实现 MitM 缓存，包括 ProxySQL 和服务器端查询重写。
ProxySQL 是一个开源代理服务器，可用于缓存 MySQL 数据库中频繁访问的数据。它的工作原理是拦截 SQL 查询，并在可能时返回缓存结果，而不是将查询转发到数据库。
服务器端查询重写是另一种可用于在 MySQL 中实现 MitM 缓存的技术。这涉及修改 SQL 查询本身以包含缓存指令，例如 "SQL_CACHE" 关键字，告诉数据库缓存查询结果。这可以使用多种技术完成，包括使用 MySQL 插件或直接在应用程序代码中修改 SQL 查询。
ProxySQL 和服务器端查询重写都可能是在 MySQL 中实现 MitM 缓存的有效技术，但它们各有优缺点。ProxySQL 是一个更灵活、更强大的解决方案，但设置和维护可能更复杂。服务器端查询重写更简单实现，但可能不如 ProxySQL 灵活和强大。最终，最佳方法取决于应用程序的具体需求和要求。
跳出思维定式
还有其他方法可以尝试加快查询执行速度，这些方法比仅仅调整查询或添加索引需要更多的工作。我们查询中最慢的部分之一是从客户 ID 连接到产品 ID 的工作，而且我们必须为每个客户执行此操作。如果我们只做一次这样的连接，这样就可以在需要时直接获取客户的数据，该怎么办？
您可以通过创建一个表来非规范化数据，该表存储许可证数据以及所有许可证的用户 ID 和产品 ID，然后只需针对特定客户查询该表。您需要使用 MySQL 触发器在 licenses 表（或根据数据可能变化的其他表）上进行 INSERT/UPDATE/DELETE 时重建该表，但这将显著提高查询该数据的性能。
同样，如果多个连接拖慢了你的 MySQL 查询，将查询分解成两个或更多语句可能更快，在 PHP 中单独执行，然后在代码中收集和过滤结果。Laravel 通过 Eloquent 的预加载关系也有类似的做法。
WordPress 可能会在 wp_posts 表上出现较慢的查询，如果你有大量数据以及许多不同的自定义文章类型。如果你发现查询文章类型很慢，考虑放弃自定义文章类型存储模型，改用自定义表。
我们曾经发现查询变得越来越慢，原因是我们的文章元数据表不断增长（目前有 250 万行）。由于查询需要两次连接该表——一次获取客户 ID，一次获取产品 ID——我们决定移除其中一个连接。
我们已经在 licenses 表中存储了 'software_product_id'，这是许可证所对应产品的字符串表示，例如 WP Migrate 开发者许可证的 WPMDB-DEV。然而，这种与实际 WooCommerce 产品 ID 的抽象在我们的网站上没有实际用途，因此我们将 'software_product_id' 列替换为 'product_id' 列，迁移了数据，并更新了我们版本中的 WooCommerce 软件订阅插件的所有代码，使用实际的产品 ID。
这将一些最长需要 4 秒的查询减少到了仅仅 223 毫秒！
SQL 查询优化结果
通过这些 SQL 查询优化方法，我们将查询时间从 8 秒减少到刚过 2 秒，并将调用次数从 4 次减少到 1 次。值得注意的是，这些查询时间是在开发环境中记录的，在生产环境上会更快。
我希望这能成为追踪慢查询并修复它们的有用指南。查询优化可能看起来是一件可怕的事情，但只要你尝试并获得一些快速成效，你就会开始对此产生兴趣并想要进一步改进。
你有关于 SQL 查询优化的建议或喜欢使用的工具吗？请在评论中告诉我。
