如何優化更快站點的SQL查詢

圖片優化和數據庫查詢優化：提升WordPress網站速度的實用指南

本文最初發表於Delicious Brains博客，經許可在此轉載

您知道快速的網站意味著更快樂的用戶、更好的谷歌排名和更高的轉化率。您甚至可能認為您的WordPress網站已經足夠快了：您已經檢查了網站性能，從最佳服務器設置實踐到慢速代碼故障排除，以及將圖像卸載到CDN，但這僅僅是全部嗎？

對於像WordPress這樣的動態、數據庫驅動的網站，您可能仍然面臨一個問題：數據庫查詢導致網站速度變慢。

在這篇文章中，我將引導您了解如何識別導致瓶頸的查詢，如何理解這些查詢的問題，以及快速修復和提高速度的其他方法。我將使用我們最近解決的一個實際查詢，該查詢減慢了deliciousbrains.com客戶門戶網站的速度。

查詢識別

修復緩慢的SQL查詢的第一步是找到它們。 Ashley之前在博客中讚揚過Query Monitor調試插件，而該插件的數據庫查詢功能使其成為識別緩慢SQL查詢的寶貴工具。該插件報告頁面請求期間執行的所有數據庫查詢。它允許您按調用它們的代碼或組件（插件、主題或WordPress核心）過濾它們，並突出顯示重複和緩慢的查詢：

如果您不想在生產站點上安裝調試插件（也許您擔心會增加一些性能開銷），可以選擇啟用MySQL慢查詢日誌，該日誌記錄所有需要一定時間才能執行的查詢。這相對容易配置和設置查詢的日誌記錄位置。由於這是一個服務器級別的調整，性能影響將小於站點上的調試插件，但在不使用時應將其關閉。

理解查詢問題

找到要改進的代價高昂的查詢後，下一步是嘗試了解是什麼導致查詢變慢。最近，在對我們網站進行開發時，我們發現一個查詢需要大約8秒才能執行！

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

我們使用WooCommerce和WooCommerce軟件訂閱插件的自定義版本來運行我們的插件商店。此查詢的目的是獲取我們知道客戶編號的客戶的所有訂閱。 WooCommerce具有相當複雜的數據模型，即使訂單存儲為自定義帖子類型，客戶的ID（對於每個客戶都為其創建WordPress用戶的商店）也不是存儲為post_author，而是存儲為帖子元數據的一部分。軟件訂閱插件還創建了幾個自定義表連接。讓我們深入了解一下查詢。

利用MySQL工具

MySQL提供了一個方便的DESCRIBE語句，可用於輸出有關表結構的信息，例如其列、數據類型和默認值。因此，如果您執行DESCRIBE wp_postmeta;，您將看到以下結果：

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
post_id	bigint(20) unsigned	NO	MUL	0
meta_key	varchar(255)	YES	MUL	NULL
meta_value	longtext	YES		NULL

這很酷，但您可能已經知道了。但您是否知道DESCRIBE語句前綴實際上可以用於SELECT、INSERT、UPDATE、REPLACE和DELETE語句？這更常被稱為其同義詞EXPLAIN，它將為我們提供有關語句如何執行的詳細信息。

以下是我們緩慢查詢的結果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	pm2	ref	meta_key	meta_key	576	const	28	Using where; Using temporary; Using filesort
1	SIMPLE	pm	ref	post_id,meta_key	meta_key	576	const	37456	Using where
1	SIMPLE	p	eq_ref	PRIMARY,type_status_date	PRIMARY	8	deliciousbrainsdev.pm.post_id	1	Using where
1	SIMPLE	l	ref	PRIMARY,order_id	order_id	8	deliciousbrainsdev.pm.post_id	1	Using index condition; Using where
1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	8	deliciousbrainsdev.l.key_id	1	NULL

乍一看，這不太容易解釋。幸運的是，SitePoint 的朋友們已經編寫了一份關於理解該語句的全面指南。

最重要的列是type，它描述了表的連接方式。如果您看到ALL，則表示MySQL正在從磁盤讀取整個表，從而增加了I/O速率並增加了CPU負載。這被稱為“全表掃描”（稍後將詳細介紹）。

rows列也是MySQL必須執行操作的一個很好的指示，因為它顯示了它為了找到結果而查看的行數。

EXPLAIN還提供了更多可用於優化的信息。例如，pm2表（wp_postmeta），它告訴我們我們正在使用filesort，因為我們要求使用語句中的ORDER BY子句對結果進行排序。如果我們也對查詢進行分組，我們將增加執行的開銷。

可視化分析

MySQL Workbench是另一種方便的免費工具，可用於此類調查。對於在MySQL 5.6及更高版本上運行的數據庫，EXPLAIN的結果可以輸出為JSON，MySQL Workbench將該JSON轉換為語句的可視化執行計劃：

它通過按成本對查詢的部分進行著色來自動引起您的注意。我們可以立即看到，與wp_woocommerce_software_licences（別名l）表的連接存在嚴重問題。

解決方法

查詢的一部分正在執行全表掃描，您應該盡量避免這種情況，因為它使用非索引列order_id作為wp_woocommerce_software_licences表與wp_posts表之間的連接。這是緩慢查詢的常見問題，並且可以輕鬆解決。

添加索引

order_id是表中非常重要的標識數據的一部分，如果我們像這樣查詢，我們確實應該在該列上添加索引，否則MySQL將逐行掃描表，直到找到所需的行。讓我們添加一個索引並看看它會做什麼：

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

哇，我們通過添加該索引成功地減少了查詢超過5秒的時間，幹得好！

了解您的查詢

檢查查詢——逐個連接，逐個子查詢。它是否執行了不需要的操作？可以進行哪些優化？

在本例中，我們使用order_id將許可證表連接到帖子表，同時將語句限制為shop_order的帖子類型。這是為了強制數據完整性，以確保我們只使用正確的訂單記錄。但是，它實際上是查詢中冗餘的部分。我們知道，表中的軟件許可證行具有與帖子表中的WooCommerce訂單相關的order_id是一個安全的賭注，因為這是在PHP插件代碼中強制執行的。讓我們刪除連接並看看這是否會改善情況：

這並沒有很大的節省，但查詢現在不到3秒。

緩存

如果您的服務器默認情況下未啟用MySQL查詢緩存，則值得啟用。這意味著MySQL將保留所有已執行語句及其結果的記錄，如果隨後執行相同的語句，則將返回緩存的結果。緩存不會過期，因為MySQL在更改表時會刷新緩存。

Query Monitor發現我們的查詢在一個頁面加載中運行了4次，儘管啟用MySQL查詢緩存很好，但在一個請求中重複讀取數據庫實際上應該完全避免。 PHP代碼中的靜態緩存是一種簡單且非常有效的方法來解決此問題。基本上，您在第一次請求數據庫查詢的結果時從數據庫中獲取它們並將它們存儲在類的靜態屬性中，然後後續調用將從靜態屬性返回結果：

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

緩存的壽命是請求的壽命，更具體地說，是實例化對象的壽命。如果您希望在請求之間持久保存查詢結果，則需要實現持久對象緩存。但是，您的代碼需要負責設置緩存，並在基礎數據更改時使緩存條目失效。

其他方法

我們可以採取其他方法來嘗試加快查詢執行速度，這些方法比僅僅調整查詢或添加索引需要更多工作。我們查詢中最慢的部分之一是從客戶ID到產品ID的表連接工作，我們必須為每個客戶執行此操作。如果我們只執行一次所有連接，那麼我們只需要在需要時獲取客戶數據怎麼辦？

您可以通過創建一個表來反規範化數據，該表存儲許可證數據以及所有許可證的用戶ID和產品ID，只需針對特定客戶查詢該表即可。您需要使用MySQL觸發器在INSERT/UPDATE/DELETE到許可證表（或其他表，具體取決於數據如何更改）時重建該表，但這將顯著提高查詢該數據的性能。

同樣，如果許多連接會減慢MySQL中的查詢速度，那麼將查詢分解為兩個或多個語句並在PHP中分別執行它們，然後在代碼中收集和過濾結果可能會更快。 Laravel通過在Eloquent中急切加載關係來執行類似的操作。

如果您的數據量很大，並且有很多不同的自定義帖子類型，WordPress可能會容易在wp_posts表上出現較慢的查詢。如果您發現查詢您的帖子類型速度很慢，那麼請考慮放棄自定義帖子類型存儲模型並使用自定義表。

結果

通過這些查詢優化方法，我們設法將查詢時間從8秒減少到略高於2秒，並將調用次數從4次減少到1次。請注意，這些查詢時間是在我們的開發環境中記錄的，在生產環境中會更快。

我希望本指南對您跟踪和修復緩慢的查詢有所幫助。查詢優化似乎是一項可怕的任務，但是一旦您嘗試並獲得一些快速成功，您就會開始對它著迷，並希望進一步改進。

以上是如何優化更快站點的SQL查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！