|
（点击上方公众号，可快速关注）
英文：Delicious Brains，翻译：开源中国
www.oschina.net/translate/sql-query-optimization
你必定晓得，一个快速拜访的网站能让用户爱好，能够帮忙网站从Google 上加强排名，能够帮忙网站增多转化率。倘若你看过网站性能优化方面的文案，例如设置服务器的最佳实现、到干掉慢速代码以及 运用CDN 加载照片，就认为你的 WordPress 网站已然足够快了。然则事实果真如此吗？
运用动态数据库驱动的网站，例如WordPress，你的网站可能依然有一个问题亟待处理：数据库查找拖慢了网站拜访速度。
在这篇文案中，我将介绍怎样识别引起性能显现问题的查找，怎样找出它们的问题所在，以及快速修复这些问题和其他加快查找速度的办法。我会把门户网站 deliciousbrains.com 显现的拖慢查找速度的状况做为实质的案例。
定位
处理慢SQL查找的第1步是找到慢查找。Ashley已然在之前的博客里面赞扬了调试插件Query Monitor，况且这个插件的数据库查找特性使其作为定位慢SQL查找的宝贵工具。该插件会报告所有页面请求过程中的数据库请求，并且能够经过调用这些查找代码或原件(插件，主题，WordPress核)过滤这些查找，高亮重复查找和慢查找。
要是不愿意在生产安环境装调试插件(性能开销原由)，亦能够打开MySQL Slow Query Log，这般在特按时间执行的所有查找都会被记录下来。这种办法配置和设置存放查找位置相对简单。因为这是一个服务级别的调节，性能影响会少于运用调试插件，但当不消的时候亦应该关闭。
理解
一旦你找到了一个你要花很大代价找到的查找，那样接下来便是尝试去理解它并找到是什么让查找变慢。近期，在我们研发咱们网站的时候，咱们找到了一个要执行8秒的查找。
咱们运用WooCommerce和定制版的WooCommerce软件插件来运行咱们的插件商店。此查找的目的是获取哪些咱们晓得客户号的客户的所有订阅。WooCommerce是一个稍微繁杂的数据模型，即使订单以自定义的类型存储，用户的ID（商店为每一个用户创建的WordPress）亦无存储在post_author，而是做为后期数据的一部分。订阅软件插件给自义定表创建了一对链接。让咱们深入认识查找的更加多信息。
MySQL是你的伴侣
MySQL有一个很方便的语句DESCRIBE，它能够输出表结构的信息，例如字段名，数据类型等等。因此，当你执行DESCRIBE wp_postmeta;你将会看到如下的结果：
你可能已然晓得了这个语句。然则你晓得DESCRIBE语句能够放在SELECT, INSERT, UPDATE, REPLACE 和 DELETE语句前边运用吗？更为人们所熟知的是他的同义词 EXPLAIN ，并将供给相关该语句怎样执行的仔细信息。
这是咱们查找到的结果：
乍一看，这很难解释。幸运的是，人们经过SitePoint总结了一个理解语句的全面指南。
最重要的字段是type，它描述了一张表是怎么形成的。倘若你想看所有的内容，那就寓意着MySQL要从内存读取整张表，增多I/O的速度并在CPU上加载。这种被叫作为“全表浏览”—稍后将对此进行仔细介绍。
rows字段亦是一个好的标识，标识着MySQL将要不得不做的事情，它表示了结果中查询了多少行。
Explain亦给了咱们非常多能够优化的信息。例如，pm2表（(wp_postmeta），告诉咱们是Using filesort，由于咱们运用了 ORDER BY语句对结果进行了排序。倘若咱们要对查找结果进行分组，这将会给执行增多开销。
可视化科研
针对这种类型的科研，MySQL Workbench是另一一个方便，免费的工具。将数据库用MySQL5.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)
哇，干的美丽！咱们成功的添加了索引并将查找的时间缩短了5s.
认识你的查找语句
检测下查找语句——瞧瞧每一个join，每一个子查找。它们做了它们不应做的事了吗？这儿能做什么优化吗？
这个例子中，咱们把licenses 表和posts 表经过order_id 连接起来同期限制post type 为shop_order。这是为了经过保持数据的完整性来保准咱们只运用正确的订单记录，然则事实上这在查找中是多余的。咱们晓得这是一个关于安全的赌注，在posts 表中software license 行是经过order_id 来跟 WooCommerce order 关联联的，这在PHP 插件代码中是强制的。让咱们移除join 来瞧瞧有什么提高无：
提高并不算很大但此刻查找时间小于3 秒了。
缓存一切数据
倘若你的服务器默认状况下无运用MySQL查找缓存，那样你应该开启缓存。开启缓存寓意着MySQL 会把所有的语句和语句执行的结果保留下来，倘若随后有一条与缓存中完全相同的语句需要执行，那样MySQL 就会返回缓存的结果。缓存不会过时，由于MySQL 会在表数据更新后刷新缓存。
查找监测器发掘在加载一个页面时咱们的查找语句执行了四次，尽管有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再到加入表格所做的工作，咱们必须为每一个客户做到。咱们是不是能够在需要的时候抓取客户的数据？倘若是那样，那咱们就只需要加入一次。
您能够经过创建数据表来存储许可数据，以及所有许可用户标识和制品标识符来对数据进行非规范化（反规范化）处理，并针对特定客户进行查找。 您需要运用INSERT / UPDATE / DELETE上的MySQL触发器来重建表格（不外这要取决于数据来更改的表格），这会显着加强查找数据的性能。
类似地，倘若有些连接在MySQL中减慢了查找速度，那样将查找分解为两个或更加多语句并在PHP中单独执行它们可能会更快，而后能够在代码中收集和过滤结果。 Laravel 经过预加载在 Eloquent 中就做了类似的事情。
倘若您有大量数据和许多区别的自定义帖子类型，WordPress可能会在wp_posts表上减慢查找速度。 倘若您发掘查找的帖子类型较慢，那样能够思虑从自定义帖子类型的存储模型移动到自定义表格中 – 更加多内容将在后面的文案中间商绍。
结论
经过这些查找优化办法，咱们设法将查找从8秒降低到2秒，并且将查找次数从4次减少到1次。需要说明的是，这些查找时间是在咱们研发环境运行时记录的 ，生产环境速度会更快。
这对跟踪查找缓慢及其修复等问题是一个有用的指南。 优化查找看起来可能像一个可怕的任务，但只要你尝试一下，并取得有些初步的胜利，你就会起始找到错误，并期盼做出进一步改善。
倘若你有任何优化查找的意见或你爱好运用的工具？ 能够在评论中留言，让咱们晓得。
看完本文有收获？请转发分享给更加多人
关注「数据库研发」，提高 DB 技能
淘口令：复制以下红色内容，再打开手淘就可购买
范品社，运用￥极客T恤￥抢先预览（长按复制整段文案，打开手机淘宝就可进入活动内容）
近期，北京地区正常发货，但派件时间有所延长。
| 
发表于 2024-8-22 21:12:41