SQL資料庫查詢最佳化技巧提升網站訪問速度的方法

mrr發表於2019-01-30

  在這篇文章中,我將介紹如何識別導致效能出現問題的查詢,如何找出它們的問題所在,以及快速修復這些問題和其他加快查詢速度的方法。

  你一定知道,一個快速訪問的網站能讓使用者喜歡,可以幫助網站從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)的表有嚴重的問題。

解決

  你應該避免(https://dev.mysql.com/doc/refman/5.7/en/table-scan-avoidance.html)這種全部表瀏覽的查詢,因為他使用非索引欄位order_id去連線wp_woocommerce_software_licences表和wp_posts表。這對於查詢慢是常見的問題,而且也是比較容易解決的問題。

索引

  order_id在表中是一個相當重要的標誌性資料,如果想像這種方式查詢,我們需要在列上建立一個索引,除此之外,MySQL將逐字掃描表的每一行,直到找到我們想要的行為止。讓我們新增一個索引並看看它是怎麼樣工作的:

  哇,乾的漂亮!我們成功的新增了索引並將查詢的時間縮短了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 程式碼中的靜態快取很簡單並且可以很高效的解決這個問題。基本上,首次請求時從資料庫中獲取查詢結果,並將其儲存在類的靜態屬性中,然後後續的查詢語句呼叫將從靜態屬性中返回結果:

  快取有一個生命週期,具體地說是例項化物件有一個生命週期。如果你正在檢視跨請求的查詢結果,那麼你需要實現一個持久物件快取。然而不管怎樣,你的程式碼應該負責設定快取,並且當基礎資料變更時讓快取失效。

換位思考

  不僅僅是調整查詢或新增索引,還有其他方法可以加快查詢的執行速度。 我們查詢的最慢的部分是從客戶ID到產品ID再到加入表格所做的工作,我們必須為每個客戶做到。

  我們是不是可以在需要的時候抓取客戶的資料?如果是那樣,那我們就只需要加入一次。

  您可以透過建立資料表來儲存許可資料,以及所有許可使用者標識和產品識別符號來對資料進行非規範化(反規範化)處理,並針對特定客戶進行查詢。

  您需要使用INSERT / UPDATE / DELETE上的MySQL觸發器來重建表格(不過這要取決於資料來更改的表格),這會顯著提高查詢資料的效能。

  類似地,如果一些連線在MySQL中減慢了查詢速度,那麼將查詢分解為兩個或更多語句並在PHP中單獨執行它們可能會更快,然後可以在程式碼中收集和過濾結果。 Laravel 透過預載入在 Eloquent 中就做了類似的事情。

  如果您有大量資料和許多不同的自定義帖子型別,WordPress可能會在wp_posts表上減慢查詢速度。 如果您發現查詢的帖子型別較慢,那麼可以考慮從自定義帖子型別的儲存模型移動到自定義表格中 - 更多內容將在後面的文章中介紹。

結論

  透過這些查詢最佳化方法,我們設法將查詢從8秒降低到2秒,並且將查詢次數從4次減少到1次。需要說明的是,這些查詢時間是在我們開發環境執行時記錄的 ,生產環境速度會更快。

  這對追蹤查詢緩慢及其修復等問題是一個有用的指南。 最佳化查詢看起來可能像一個可怕的任務,但只要你嘗試一下,並取得一些初步的勝利,你就會開始找到錯誤,並希望做出進一步改善。

相關文章