提高网站访问速度SQL查询优化技巧

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 263

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 264

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 290

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 291

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 263

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 264

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 290

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 291

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 263

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 264

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 290

Warning: Illegal offset type in isset or empty in /www/wwwroot/www.moyuseo.com/wp-content/themes/justnews/themer/core/wpcom.php on line 291

(点击上面，快速关注)

英文：Delicious Brains，翻译:开源中国

www.oschina.net/translate/sql-query-optimization

你必须知道，一个快速访问的网站可以让用户喜欢它，并帮助网站从Google 提高排名可以帮助网站提高转化率。如果你读过关于网站性能优化的文章，比如设置服务器的最佳实现，杀死慢代码和 使用CDN 如果你加载图片，你会认为你的 WordPress 网站已经够快了。但真的是这样吗？

使用动态数据库驱动的网站，如WordPress，您的网站可能仍有一个问题需要解决：数据库查询减慢了网站访问速度。

在本文中，我将介绍如何识别性能问题，如何找出问题，以及快速修复这些问题和其他加快查询速度的方法。我会把门户网站 deliciousbrains.com 拖慢查询速度作为实际案例。

定位

处理慢SQL查询的第一步是找到慢查询。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恤￥抢先预览（长按复制整段文案，打开手机淘宝即可进入活动内容）

近期，北京地区正常发货，但派件时间有所延长。