必備 SQL 查詢優(yōu)化技巧提升網(wǎng)站訪問(wèn)速度

在這篇文章中，我將介紹如何識(shí)別導(dǎo)致性能出現(xiàn)問(wèn)題的查詢，如何找出它們的問(wèn)題所在，以及快速修復(fù)這些問(wèn)題和其他加快查詢速度的方法。

你一定知道，一個(gè)快速訪問(wèn)的網(wǎng)站能讓用戶喜歡，可以幫助網(wǎng)站從Google 上提高排名，可以幫助網(wǎng)站增加轉(zhuǎn)化率。如果你看過(guò)網(wǎng)站性能優(yōu)化方面的文章，例如設(shè)置服務(wù)器的最佳實(shí)現(xiàn)、到干掉慢速代碼以及 使用CDN 加載圖片，就認(rèn)為你的 WordPress 網(wǎng)站已經(jīng)足夠快了。但是事實(shí)果真如此嗎？

使用動(dòng)態(tài)數(shù)據(jù)庫(kù)驅(qū)動(dòng)的網(wǎng)站，例如WordPress，你的網(wǎng)站可能依然有一個(gè)問(wèn)題亟待解決：數(shù)據(jù)庫(kù)查詢拖慢了網(wǎng)站訪問(wèn)速度。

在這篇文章中，我將介紹如何識(shí)別導(dǎo)致性能出現(xiàn)問(wèn)題的查詢，如何找出它們的問(wèn)題所在，以及快速修復(fù)這些問(wèn)題和其他加快查詢速度的方法。我會(huì)把門(mén)戶網(wǎng)站 deliciousbrains.com 出現(xiàn)的拖慢查詢速度的情況作為實(shí)際的案例。

定位

處理慢SQL查詢的第一步是找到慢查詢。Ashley已經(jīng)在之前的博客里面贊揚(yáng)了調(diào)試插件Query Monitor，而且這個(gè)插件的數(shù)據(jù)庫(kù)查詢特性使其成為定位慢SQL查詢的寶貴工具。

該插件會(huì)報(bào)告所有頁(yè)面請(qǐng)求過(guò)程中的數(shù)據(jù)庫(kù)請(qǐng)求，并且可以通過(guò)調(diào)用這些查詢代碼或者原件(插件，主題，WordPress核)過(guò)濾這些查詢，高亮重復(fù)查詢和慢查詢。

要是不愿意在生產(chǎn)安環(huán)境裝調(diào)試插件(性能開(kāi)銷原因)，也可以打開(kāi)MySQL Slow Query Log，這樣在特定時(shí)間執(zhí)行的所有查詢都會(huì)被記錄下來(lái)。這種方法配置和設(shè)置存放查詢位置相對(duì)簡(jiǎn)單。

由于這是一個(gè)服務(wù)級(jí)別的調(diào)整，性能影響會(huì)小于使用調(diào)試插件，但當(dāng)不用的時(shí)候也應(yīng)該關(guān)閉。

理解

一旦你找到了一個(gè)你要花很大代價(jià)找到的查詢，那么接下來(lái)就是嘗試去理解它并找到是什么讓查詢變慢。最近，在我們開(kāi)發(fā)我們網(wǎng)站的時(shí)候，我們找到了一個(gè)要執(zhí)行8秒的查詢。

我們使用WooCommerce和定制版的WooCommerce軟件插件來(lái)運(yùn)行我們的插件商店。此查詢的目的是獲取那些我們知道客戶號(hào)的客戶的所有訂閱。

WooCommerce是一個(gè)稍微復(fù)雜的數(shù)據(jù)模型，即使訂單以自定義的類型存儲(chǔ)，用戶的ID（商店為每一個(gè)用戶創(chuàng)建的WordPress）也沒(méi)有存儲(chǔ)在post_author，而是作為后期數(shù)據(jù)的一部分。訂閱軟件插件給自義定表創(chuàng)建了一對(duì)鏈接。讓我們深入了解查詢的更多信息。

把 MySQL 當(dāng)作朋友

MySQL有一個(gè)很方便的語(yǔ)句DESCRIBE，它可以輸出表結(jié)構(gòu)的信息，比如字段名，數(shù)據(jù)類型等等。所以，當(dāng)你執(zhí)行DESCRIBE wp_postmeta;你將會(huì)看到如下的結(jié)果：

你可能已經(jīng)知道了這個(gè)語(yǔ)句。但是你知道DESCRIBE語(yǔ)句可以放在SELECT, INSERT, UPDATE, REPLACE 和 DELETE語(yǔ)句前邊使用嗎？更為人們所熟知的是他的同義詞 EXPLAIN ，并將提供有關(guān)該語(yǔ)句如何執(zhí)行的詳細(xì)信息。

這是我們查詢到的結(jié)果：

乍一看，這很難解釋。幸運(yùn)的是，人們通過(guò)SitePoint總結(jié)了一個(gè)理解語(yǔ)句的全面指南。

最重要的字段是type，它描述了一張表是怎么構(gòu)成的。

如果你想看全部的內(nèi)容，那就意味著MySQL要從內(nèi)存讀取整張表，增加I/O的速度并在CPU上加載。這種被稱為“全表瀏覽”—稍后將對(duì)此進(jìn)行詳細(xì)介紹。

rows字段也是一個(gè)好的標(biāo)識(shí)，標(biāo)識(shí)著MySQL將要不得不做的事情，它顯示了結(jié)果中查找了多少行。

Explain也給了我們很多可以優(yōu)化的信息。例如，pm2表（(wp_postmeta），告訴我們是Using filesort，因?yàn)槲覀兪褂昧?ORDER BY語(yǔ)句對(duì)結(jié)果進(jìn)行了排序。如果我們要對(duì)查詢結(jié)果進(jìn)行分組，這將會(huì)給執(zhí)行增加開(kāi)銷。

可視化研究

對(duì)于這種類型的研究，MySQL Workbench是另外一個(gè)方便，免費(fèi)的工具。將數(shù)據(jù)庫(kù)用MySQL5.6及其以上的版本打開(kāi)，EXPLAIN的結(jié)果可以用JSON格式輸出，同時(shí)MySQL Workbench將JSON轉(zhuǎn)換成可視化執(zhí)行語(yǔ)句：

它自動(dòng)將查詢的問(wèn)題用顏色著重表示提醒用戶去注意。我們可以馬上看到，連接wp_woocommerce_software_licences（別名l）的表有嚴(yán)重的問(wèn)題。

解決

你應(yīng)該避免（https://dev.mysql.com/doc/refman/5.7/en/table-scan-avoidance.html）這種全部表瀏覽的查詢，因?yàn)樗褂梅撬饕侄蝟rder_id去連接wp_woocommerce_software_licences表和wp_posts表。這對(duì)于查詢慢是常見(jiàn)的問(wèn)題，而且也是比較容易解決的問(wèn)題。

索引

order_id在表中是一個(gè)相當(dāng)重要的標(biāo)志性數(shù)據(jù)，如果想像這種方式查詢，我們需要在列上建立一個(gè)索引，除此之外，MySQL將逐字掃描表的每一行，直到找到我們想要的行為止。讓我們添加一個(gè)索引并看看它是怎么樣工作的：

哇，干的漂亮！我們成功的添加了索引并將查詢的時(shí)間縮短了5s.

了解你的查詢語(yǔ)句

檢查下查詢語(yǔ)句——看看每一個(gè)join，每一個(gè)子查詢。它們做了它們不該做的事了嗎？這里能做什么優(yōu)化嗎？

這個(gè)例子中，我們把licenses 表和posts 表通過(guò)order_id 連接起來(lái)同時(shí)限制post type 為shop_order。這是為了通過(guò)保持?jǐn)?shù)據(jù)的完整性來(lái)保證我們只使用正確的訂單記錄，但是事實(shí)上這在查詢中是多余的。

我們知道這是一個(gè)關(guān)于安全的賭注，在posts 表中software license 行是通過(guò)order_id 來(lái)跟 WooCommerce order 相關(guān)聯(lián)的，這在PHP 插件代碼中是強(qiáng)制的。讓我們移除join 來(lái)看看有什么提升沒(méi)有：

提升并不算很大但現(xiàn)在查詢時(shí)間低于3 秒了。

緩存所有數(shù)據(jù)

如果你的服務(wù)器默認(rèn)情況下沒(méi)有使用MySQL查詢緩存，那么你應(yīng)該開(kāi)啟緩存。

開(kāi)啟緩存意味著MySQL 會(huì)把所有的語(yǔ)句和語(yǔ)句執(zhí)行的結(jié)果保存下來(lái)，如果隨后有一條與緩存中完全相同的語(yǔ)句需要執(zhí)行，那么MySQL 就會(huì)返回緩存的結(jié)果。緩存不會(huì)過(guò)時(shí)，因?yàn)镸ySQL 會(huì)在表數(shù)據(jù)更新后刷新緩存。

查詢監(jiān)視器發(fā)現(xiàn)在加載一個(gè)頁(yè)面時(shí)我們的查詢語(yǔ)句執(zhí)行了四次，盡管有MySQL查詢緩存很好，但是在一個(gè)請(qǐng)求中重復(fù)讀取數(shù)據(jù)庫(kù)的數(shù)據(jù)是應(yīng)該完全避免的。

你的PHP 代碼中的靜態(tài)緩存很簡(jiǎn)單并且可以很高效的解決這個(gè)問(wèn)題。基本上，首次請(qǐng)求時(shí)從數(shù)據(jù)庫(kù)中獲取查詢結(jié)果，并將其存儲(chǔ)在類的靜態(tài)屬性中，然后后續(xù)的查詢語(yǔ)句調(diào)用將從靜態(tài)屬性中返回結(jié)果：

緩存有一個(gè)生命周期，具體地說(shuō)是實(shí)例化對(duì)象有一個(gè)生命周期。如果你正在查看跨請(qǐng)求的查詢結(jié)果，那么你需要實(shí)現(xiàn)一個(gè)持久對(duì)象緩存。然而不管怎樣，你的代碼應(yīng)該負(fù)責(zé)設(shè)置緩存，并且當(dāng)基礎(chǔ)數(shù)據(jù)變更時(shí)讓緩存失效。

換位思考

不僅僅是調(diào)整查詢或添加索引，還有其他方法可以加快查詢的執(zhí)行速度。 我們查詢的最慢的部分是從客戶ID到產(chǎn)品ID再到加入表格所做的工作，我們必須為每個(gè)客戶做到。

我們是不是可以在需要的時(shí)候抓取客戶的數(shù)據(jù)？如果是那樣，那我們就只需要加入一次。

您可以通過(guò)創(chuàng)建數(shù)據(jù)表來(lái)存儲(chǔ)許可數(shù)據(jù)，以及所有許可用戶標(biāo)識(shí)和產(chǎn)品標(biāo)識(shí)符來(lái)對(duì)數(shù)據(jù)進(jìn)行非規(guī)范化（反規(guī)范化）處理，并針對(duì)特定客戶進(jìn)行查詢。

您需要使用INSERT / UPDATE / DELETE上的MySQL觸發(fā)器來(lái)重建表格（不過(guò)這要取決于數(shù)據(jù)來(lái)更改的表格），這會(huì)顯著提高查詢數(shù)據(jù)的性能。

類似地，如果一些連接在MySQL中減慢了查詢速度，那么將查詢分解為兩個(gè)或更多語(yǔ)句并在PHP中單獨(dú)執(zhí)行它們可能會(huì)更快，然后可以在代碼中收集和過(guò)濾結(jié)果。 Laravel 通過(guò)預(yù)加載在 Eloquent 中就做了類似的事情。

如果您有大量數(shù)據(jù)和許多不同的自定義帖子類型，WordPress可能會(huì)在wp_posts表上減慢查詢速度。 如果您發(fā)現(xiàn)查詢的帖子類型較慢，那么可以考慮從自定義帖子類型的存儲(chǔ)模型移動(dòng)到自定義表格中 - 更多內(nèi)容將在后面的文章中介紹。

結(jié)論

通過(guò)這些查詢優(yōu)化方法，我們?cè)O(shè)法將查詢從8秒降低到2秒，并且將查詢次數(shù)從4次減少到1次。需要說(shuō)明的是，這些查詢時(shí)間是在我們開(kāi)發(fā)環(huán)境運(yùn)行時(shí)記錄的 ，生產(chǎn)環(huán)境速度會(huì)更快。

這對(duì)追蹤查詢緩慢及其修復(fù)等問(wèn)題是一個(gè)有用的指南。 優(yōu)化查詢看起來(lái)可能像一個(gè)可怕的任務(wù)，但只要你嘗試一下，并取得一些初步的勝利，你就會(huì)開(kāi)始找到錯(cuò)誤，并希望做出進(jìn)一步改善。