加强网站拜访速度的 SQL 查询优化技巧-天涯论坛

fny5jt9 发表于 2024-8-22 21:12:41

加强网站拜访速度的 SQL 查询优化技巧

（点击上方公众号，可快速关注）
英文：Delicious Brains，翻译：开源中国
www.oschina.net/translate/sql-query-optimization
<img src="http://mmbiz.qpic.cn/mmbiz_jpg/DmibiaFiaAI4B1gnZEibCXyQrWdY6a6Pk4vcniasMzg1A2bHoHvlxh7wMztYLuxeic03aPhbOK9ZibdfNsYBLQFwJPObg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
你必定晓得，一个快速拜访的网站能让用户爱好，能够帮忙网站从Google 上加强排名，能够帮忙网站增多转化率。倘若你看过网站性能优化方面的文案，例如设置服务器的最佳实现、到干掉慢速代码以及 运用CDN 加载照片，就认为你的 WordPress 网站已然足够快了。然则事实果真如此吗？
运用动态数据库驱动的网站，例如WordPress，你的网站可能依然有一个问题亟待处理：数据库查找拖慢了网站拜访速度。
在这篇文案中，我将介绍怎样识别引起性能显现问题的查找，怎样找出它们的问题所在，以及快速修复这些问题和其他加快查找速度的办法。我会把门户网站 deliciousbrains.com 显现的拖慢查找速度的状况做为实质的案例。
定位
处理慢SQL查找的第1步是找到慢查找。Ashley已然在之前的博客里面赞扬了调试插件Query Monitor，况且这个插件的数据库查找特性使其作为定位慢SQL查找的宝贵工具。该插件会报告所有页面请求过程中的数据库请求，并且能够经过调用这些查找代码或原件(插件，主题，WordPress核)过滤这些查找，高亮重复查找和慢查找。
<img src="http://mmbiz.qpic.cn/mmbiz_png/DmibiaFiaAI4B1gnZEibCXyQrWdY6a6Pk4vcrV8HibhVQmA5YJytPXC8s02gReKwibg3JEu3icMeEkxhgG2Vcl7NWOiamw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
要是不愿意在生产安环境装调试插件(性能开销原由)，亦能够打开MySQL Slow Query Log，这般在特按时间执行的所有查找都会被记录下来。这种办法配置和设置存放查找位置相对简单。因为这是一个服务级别的调节，性能影响会少于运用调试插件，但当不消的时候亦应该关闭。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">理解</h1>
一旦你找到了一个你要花很大代价找到的查找，那样接下来便是尝试去理解它并找到是什么让查找变慢。近期，在我们研发咱们网站的时候，咱们找到了一个要执行8秒的查找。
咱们运用WooCommerce和定制版的WooCommerce软件插件来运行咱们的插件商店。此查找的目的是获取哪些咱们晓得客户号的客户的所有订阅。WooCommerce是一个稍微繁杂的数据模型，即使订单以自定义的类型存储，用户的ID（商店为每一个用户创建的WordPress）亦无存储在post_author，而是做为后期数据的一部分。订阅软件插件给自义定表创建了一对链接。让咱们深入认识查找的更加多信息。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">MySQL是你的伴侣</h1>
MySQL有一个很方便的语句DESCRIBE，它能够输出表结构的信息，例如字段名，数据类型等等。因此，当你执行DESCRIBE wp_postmeta;你将会看到如下的结果：
<img src="https://mmbiz.qpic.cn/mmbiz_png/DmibiaFiaAI4B1gnZEibCXyQrWdY6a6Pk4vcdr8gLDp2Cxgb7OQBwGxJX5bibJ7JEoO7MTYdGlictpMCDoY6BTeKAmew/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
你可能已然晓得了这个语句。然则你晓得DESCRIBE语句能够放在SELECT, INSERT, UPDATE, REPLACE 和 DELETE语句前边运用吗？更为人们所熟知的是他的同义词 EXPLAIN ，并将供给相关该语句怎样执行的仔细信息。
这是咱们查找到的结果：
<img src="https://mmbiz.qpic.cn/mmbiz_png/DmibiaFiaAI4B1gnZEibCXyQrWdY6a6Pk4vcq6ZN1O7aQqiaOm238cl8metqTibGTjvc6MHICElTibUPrIcdialoic3C7RA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
乍一看，这很难解释。幸运的是，人们经过SitePoint总结了一个理解语句的全面指南。
最重要的字段是type，它描述了一张表是怎么形成的。倘若你想看所有的内容，那就寓意着MySQL要从内存读取整张表，增多I/O的速度并在CPU上加载。这种被叫作为“全表浏览”—稍后将对此进行仔细介绍。
rows字段亦是一个好的标识，标识着MySQL将要不得不做的事情，它表示了结果中查询了多少行。
Explain亦给了咱们非常多能够优化的信息。例如，pm2表（(wp_postmeta），告诉咱们是Using filesort，由于咱们运用了 ORDER BY语句对结果进行了排序。倘若咱们要对查找结果进行分组，这将会给执行增多开销。
<h3 style="color: black; text-align: left; margin-bottom: 10px;">可视化科研</h3>
针对这种类型的科研，MySQL Workbench是另一一个方便，免费的工具。将数据库用MySQL5.6及其以上的版本打开，EXPLAIN的结果能够用JSON格式输出，同期MySQL Workbench将JSON转换成可视化执行语句：
<img src="http://mmbiz.qpic.cn/mmbiz_png/DmibiaFiaAI4B1gnZEibCXyQrWdY6a6Pk4vcI5YFkJ7496ugIUiaGe9BI871zt25mdpdMVg9fAWLl7MeVGa5t35TIWQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
它自动将查找的问题用颜色着重暗示提醒用户去重视。咱们能够马上看到，连接wp_woocommerce_software_licences（别名l）的表有严重的问题。
<h3 style="color: black; text-align: left; margin-bottom: 10px;">处理</h3>
你应该避免这种所有表浏览的查找，由于他运用非索引字段order_id去连接wp_woocommerce_software_licences表和wp_posts表。这针对查找慢是平常的问题，况且亦是比较容易处理的问题。
<h3 style="color: black; text-align: left; margin-bottom: 10px;">索引</h3>
order_id在表中是一个相当重要的标志性数据，倘若想像这种方式查找，咱们需要在列上创立一个索引，除此之外，MySQL将逐字扫描表的每一行，直到找到咱们想要的行径止。让咱们添加一个索引并瞧瞧它是怎么样工作的：
CREATE INDEX order_id ON wp_woocommerce_software_licences(order_id)
<img src="http://mmbiz.qpic.cn/mmbiz_png/DmibiaFiaAI4B1gnZEibCXyQrWdY6a6Pk4vcia2HkWmicjylUePCGwibKqQYweBr4RII3icErnTicT1XibV8wz3ZvPLqW3uw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
哇，干的美丽！咱们成功的添加了索引并将查找的时间缩短了5s.
<h3 style="color: black; text-align: left; margin-bottom: 10px;">认识你的查找语句</h3>
检测下查找语句——瞧瞧每一个join，每一个子查找。它们做了它们不应做的事了吗？这儿能做什么优化吗？
这个例子中，咱们把licenses 表和posts 表经过order_id 连接起来同期限制post type 为shop_order。这是为了经过保持数据的完整性来保准咱们只运用正确的订单记录，然则事实上这在查找中是多余的。咱们晓得这是一个关于安全的赌注，在posts 表中software license 行是经过order_id 来跟 WooCommerce order 关联联的，这在PHP 插件代码中是强制的。让咱们移除join 来瞧瞧有什么提高无：
<img src="http://mmbiz.qpic.cn/mmbiz_png/DmibiaFiaAI4B1gnZEibCXyQrWdY6a6Pk4vcWw2SnEf4XdgQ2ojiaxZWSUZd18uDv1vJy3E58yp8icVORrLQGhKhuCuA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
提高并不算很大但此刻查找时间小于3 秒了。
<h3 style="color: black; text-align: left; margin-bottom: 10px;">缓存一切数据</h3>
倘若你的服务器默认状况下无运用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 技能
<img src="http://mmbiz.qpic.cn/mmbiz_png/DmibiaFiaAI4B2sOp0oeiajw1p0x744u9Hib4rSfhhuv5HI8N8WqUoJdxQNWUfDIWtgOicy5T1XmVl9XEblJf30ovzcQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<img src="https://mmbiz.qpic.cn/mmbiz_jpg/FoiciaVBBCfia4PkmTUFBP8fpd2PLlCx8iaJkRZ2iaj7seE62y9yicOlk9jFMWibvEfPG5PBFtORnqCoPP3EiajlyK1Xnw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
淘口令：复制以下红色内容，再打开手淘就可购买
范品社，运用￥极客T恤￥抢先预览（长按复制整段文案，打开手机淘宝就可进入活动内容）
近期，北京地区正常发货，但派件时间有所延长。

7wu1wm0 发表于 2024-10-28 21:12:01

期待你更多的精彩评论，一起交流学习。

m5k1umn 发表于 2024-10-29 10:05:56

大势所趋，用于讽刺一些制作目的就是为了跟风玩梗，博取眼球的作品。

7wu1wm0 发表于 2024-11-14 05:35:58

论坛的成功是建立在我们诚恳、务实、高效、创新和团结合作基础上，我们要把这种精神传递下去。

页: [1]

天涯论坛's Archiver

加强网站拜访速度的 SQL 查询优化技巧