欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

必备 SQL 查询优化技巧提升网站访问速度

程序员文章站 2022-04-28 09:22:22
在这篇文章中,我将介绍如何识别导致性能出现问题的查询,如何找出它们的问题所在,以及快速修复这些问题和其他加快查询速度的方法。 你一定知道,一个快速访问的网站能让用户喜...

在这篇文章中,我将介绍如何识别导致性能出现问题的查询,如何找出它们的问题所在,以及快速修复这些问题和其他加快查询速度的方法。

必备 SQL 查询优化技巧提升网站访问速度

你一定知道,一个快速访问的网站能让用户喜欢,可以帮助网站从google 上提高排名,可以帮助网站增加转化率。如果你看过网站性能优化方面的文章,例如设置服务器的最佳实现、到干掉慢速代码以及 使用cdn 加载图片,就认为你的 wordpress 网站已经足够快了。但是事实果真如此吗?

使用动态数据库驱动的网站,例如wordpress,你的网站可能依然有一个问题亟待解决:数据库查询拖慢了网站访问速度。

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

定位

处理慢sql查询的第一步是找到慢查询。ashley已经在之前的博客里面赞扬了调试插件query monitor,而且这个插件的数据库查询特性使其成为定位慢sql查询的宝贵工具。

该插件会报告所有页面请求过程中的数据库请求,并且可以通过调用这些查询代码或者原件(插件,主题,wordpress核)过滤这些查询,高亮重复查询和慢查询。

必备 SQL 查询优化技巧提升网站访问速度

要是不愿意在生产安环境装调试插件(性能开销原因),也可以打开mysql slow query log,这样在特定时间执行的所有查询都会被记录下来。这种方法配置和设置存放查询位置相对简单。

由于这是一个服务级别的调整,性能影响会小于使用调试插件,但当不用的时候也应该关闭。

理解

一旦你找到了一个你要花很大代价找到的查询,那么接下来就是尝试去理解它并找到是什么让查询变慢。最近,在我们开发我们网站的时候,我们找到了一个要执行8秒的查询。

我们使用woocommerce和定制版的woocommerce软件插件来运行我们的插件商店。此查询的目的是获取那些我们知道客户号的客户的所有订阅。

woocommerce是一个稍微复杂的数据模型,即使订单以自定义的类型存储,用户的id(商店为每一个用户创建的wordpress)也没有存储在post_author,而是作为后期数据的一部分。订阅软件插件给自义定表创建了一对链接。让我们深入了解查询的更多信息。

把 mysql 当作朋友

mysql有一个很方便的语句describe,它可以输出表结构的信息,比如字段名,数据类型等等。所以,当你执行describe wp_postmeta;你将会看到如下的结果:

必备 SQL 查询优化技巧提升网站访问速度

你可能已经知道了这个语句。但是你知道describe语句可以放在select, insert, update, replace 和 delete语句前边使用吗?更为人们所熟知的是他的同义词 explain ,并将提供有关该语句如何执行的详细信息。

这是我们查询到的结果:

必备 SQL 查询优化技巧提升网站访问速度

乍一看,这很难解释。幸运的是,人们通过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转换成可视化执行语句:

必备 SQL 查询优化技巧提升网站访问速度

它自动将查询的问题用颜色着重表示提醒用户去注意。我们可以马上看到,连接wp_woocommerce_software_licences(别名l)的表有严重的问题。

解决

你应该避免()这种全部表浏览的查询,因为他使用非索引字段order_id去连接wp_woocommerce_software_licences表和wp_posts表。这对于查询慢是常见的问题,而且也是比较容易解决的问题。

索引

order_id在表中是一个相当重要的标志性数据,如果想像这种方式查询,我们需要在列上建立一个索引,除此之外,mysql将逐字扫描表的每一行,直到找到我们想要的行为止。让我们添加一个索引并看看它是怎么样工作的:

必备 SQL 查询优化技巧提升网站访问速度

哇,干的漂亮!我们成功的添加了索引并将查询的时间缩短了5s.

了解你的查询语句

检查下查询语句——看看每一个join,每一个子查询。它们做了它们不该做的事了吗?这里能做什么优化吗?

这个例子中,我们把licenses 表和posts 表通过order_id 连接起来同时限制post type 为shop_order。这是为了通过保持数据的完整性来保证我们只使用正确的订单记录,但是事实上这在查询中是多余的。

我们知道这是一个关于安全的赌注,在posts 表中software license 行是通过order_id 来跟 woocommerce order 相关联的,这在php 插件代码中是强制的。让我们移除join 来看看有什么提升没有:

必备 SQL 查询优化技巧提升网站访问速度 

提升并不算很大但现在查询时间低于3 秒了。

缓存所有数据

如果你的服务器默认情况下没有使用mysql查询缓存,那么你应该开启缓存。

开启缓存意味着mysql 会把所有的语句和语句执行的结果保存下来,如果随后有一条与缓存中完全相同的语句需要执行,那么mysql 就会返回缓存的结果。缓存不会过时,因为mysql 会在表数据更新后刷新缓存。

查询监视器发现在加载一个页面时我们的查询语句执行了四次,尽管有mysql查询缓存很好,但是在一个请求中重复读取数据库的数据是应该完全避免的。

你的php 代码中的静态缓存很简单并且可以很高效的解决这个问题。基本上,首次请求时从数据库中获取查询结果,并将其存储在类的静态属性中,然后后续的查询语句调用将从静态属性中返回结果:

必备 SQL 查询优化技巧提升网站访问速度

缓存有一个生命周期,具体地说是实例化对象有一个生命周期。如果你正在查看跨请求的查询结果,那么你需要实现一个持久对象缓存。然而不管怎样,你的代码应该负责设置缓存,并且当基础数据变更时让缓存失效。

换位思考

不仅仅是调整查询或添加索引,还有其他方法可以加快查询的执行速度。 我们查询的最慢的部分是从客户id到产品id再到加入表格所做的工作,我们必须为每个客户做到。

我们是不是可以在需要的时候抓取客户的数据?如果是那样,那我们就只需要加入一次。

您可以通过创建数据表来存储许可数据,以及所有许可用户标识和产品标识符来对数据进行非规范化(反规范化)处理,并针对特定客户进行查询。

您需要使用insert / update / delete上的mysql触发器来重建表格(不过这要取决于数据来更改的表格),这会显着提高查询数据的性能。

类似地,如果一些连接在mysql中减慢了查询速度,那么将查询分解为两个或更多语句并在php中单独执行它们可能会更快,然后可以在代码中收集和过滤结果。 laravel 通过预加载在 eloquent 中就做了类似的事情。

如果您有大量数据和许多不同的自定义帖子类型,wordpress可能会在wp_posts表上减慢查询速度。 如果您发现查询的帖子类型较慢,那么可以考虑从自定义帖子类型的存储模型移动到自定义表格中 - 更多内容将在后面的文章中介绍。

结论

通过这些查询优化方法,我们设法将查询从8秒降低到2秒,并且将查询次数从4次减少到1次。需要说明的是,这些查询时间是在我们开发环境运行时记录的 ,生产环境速度会更快。

这对追踪查询缓慢及其修复等问题是一个有用的指南。 优化查询看起来可能像一个可怕的任务,但只要你尝试一下,并取得一些初步的胜利,你就会开始找到错误,并希望做出进一步改善。