如何分析 SQL 查詢以獲得更好的性能

已發表: 2023-03-16

在 Servebolt，我們以性能為生。

數據庫性能也不例外。

在網站訪問者單擊鏈接後執行低效查詢會顯著降低用戶體驗。 他們將需要等待慢速查詢的整個持續時間執行，這可能需要幾秒鐘，然後才會發生任何其他操作，例如頁面呈現。此等待時間不僅包括查詢運行所需的時間，還包括預處理和後處理所需的任何額外時間。因此，設計不當的查詢會顯著降低網站的整體性能，從而導致令人沮喪的用戶體驗。

Time to First Byte (TTFB)是一種衡量在用戶向網站發出請求後接收到第一個字節數據所需時間的方法。它也是搜索引擎在評估網站時使用的關鍵指標。 當觸發慢查詢時，會對 TTFB 產生負面影響。慢速查詢運行的時間越長，TTFB 就越高，從而導致整體網站性能變慢，用戶體驗也不太令人滿意。

在本指南中，我們將向您介紹如何分析 SQL 查詢——這是維護依賴於數據庫響應的 Web 應用程序性能的關鍵部分。這是一個設置基礎的過程，然後能夠開始優化這些查詢以提高它們的性能。

了解 SQL 查詢分析

當您開發 Web 應用程序並開始大規模運行時，曾經順利運行的 SQL 查詢可能會導致性能問題。一般來說，隨著每秒請求數的增加，越來越多的查詢針對越來越多的數據運行。當性能受到影響時，用戶在與您的網站、軟件或服務交互時的體驗也會受到影響。

查詢分析是一種分析數據庫查詢、評估其性能和識別潛在問題的方法。

通過分析和識別這些有問題的查詢，您可以進行特定的改進，從而對其數據庫的性能產生可衡量的差異。反過來，這將允許在未來提高可擴展性以及整體客戶滿意度，因為應用程序和網站將更具響應性。

MariaDB（和 MySQL）提供了多種用於查詢分析的工具和技術，我們將在本文中進行介紹。一旦識別出慢速查詢，下一步就是優化它們。此過程包括確定問題的根本原因，並更改查詢的結構以提高其效率。

如何分析 SQL 查詢（7 種方法）

讓我們首先分解可用於識別緩慢和低效查詢的不同工具和技術，以便您知道應該在哪裡集中改進工作：

1 – EXPLAIN EXTENDED命令

可用於分析 SQL 查詢的工具之一是EXPLAIN命令。

通過對查詢運行 EXPLAIN 命令，您可以看到查詢是如何執行的，包括使用了哪些索引，以及檢查的行數。

EXPLAIN SELECT * FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.name = 'John Smith';

當您對查詢運行EXPLAIN命令時，它會返回一個包含多個列的結果集，包括：

id: 查詢在執行計劃中的唯一標識
select_type ：查詢的類型，例如 SIMPLE 或 SUBQUERY
table: 正在查詢的表
type ：使用的連接類型，例如 JOIN 或 INDEX
possible_keys：MariaDB 或 MySQL 可以用來處理查詢的索引
key : MariaDB 或 MySQL 實際用來處理查詢的索引
key_len ：使用的密鑰的長度
rows ：MariaDB 或 MySQL 估計將為查詢檢查的行數

Extra ：這包含有關查詢的其他信息，例如是否執行了全表掃描或是否使用了臨時表。

通過分析EXPLAIN命令的輸出，您通常能夠識別潛在的性能瓶頸，例如糟糕的索引、次優的連接類型或大量檢查的行。

例如，如果類型列顯示“ALL”而不是“index”，則查詢正在執行全表掃描，這幾乎肯定會導致性能下降。如果鍵列為 NULL，則 MySQL 沒有使用任何索引，這也會很慢。如果 rows 列的值很高，則意味著正在檢查許多行，從而導致性能進一步下降。

我們更喜歡使用EXPLAIN EXTENDED變體來幫助提供額外信息。

注意：雖然這在 MySQL 中已被棄用，但它在 MariaDB 中仍然可用。

通過使用 EXTENDED 選項，您將能夠看到有用的信息，例如檢查的行數、返回的行數、有關使用的 JOIN 類型的信息、掃描表的順序、使用的索引以及持續時間查詢被執行。

以下是使用 EXPLAIN EXTENDED 命令的方式：

EXPLAIN EXTENDED SELECT * FROM your_table WHERE column_name = 'value';

在此示例中，EXPLAIN 命令將顯示數據庫為執行查詢將採取的步驟列表，以及它將使用的資源列表。

通過使用此命令，您將能夠更輕鬆地發現查詢中的瓶頸，允許您進行任何必要的更改，這將有助於緩解瓶頸並加快查詢的性能。

例如，使用 EXPLAIN EXTENDED 命令可以幫助確定是否需要添加索引、優化 JOIN 條件以及限制查詢返回的總行數。

您還應該確保在執行此測試和優化時已禁用查詢緩存，以確保獲得準確的結果。為此，請在連接客戶端時先運行此命令。

SET SESSION query_cache_type=0;

對查詢進行這些更改後，再次測試其性能以確定已實現多少改進（如果有）。請記住，與查詢的任何分析和優化一樣，該過程是迭代的——期望多次使用 EXPLAIN EXTENDED 命令，然後進行性能測試。

2 – EXPLAIN ANALYZE命令

此命令用於分析查詢的執行計劃並返回性能指標，例如查詢執行的實際時間和實際檢查的行數。通過分析 EXPLAIN ANALYZE 命令的結果，您可以識別查詢執行中的任何潛在瓶頸，例如缺少索引或需要檢查的大量行。

3 – 慢查詢日誌

這是 MariaDB（和 MySQL）中的一個內置功能，它記錄所有執行時間超過一定時間的查詢。慢速查詢日誌可以配置為記錄花費時間超過特定閾值（例如一秒）的查詢。

在 Servebolt，慢速查詢日誌記錄了所有執行時間超過 1 秒的查詢。這是因為大多數查詢應該在幾分之一秒內執行。在 Web 應用程序的上下文中，例如運行 WordPress 的站點，加載單個頁面需要 10 到 100 個數據庫查詢，所有這些查詢都必須按順序執行，然後才能將頁面編譯為 HTML 並返回給用戶。

當前的 Servebolt Cloud 配置將慢速查詢日誌保存在全局日誌服務器上。如果有需要，您只需與我們的支持團隊聯繫，我們將過濾相關日誌的文件並為您提供輸出。

在您自己的環境中，您可以通過將以下行添加到您的 MariaDB 或 MySQL 配置文件（my.cnf 或 my.ini）來啟用慢速查詢日誌：

log_slow_queries = /path/to/slow.log

long_query_time = 1

4 – 視覺解釋計劃

可視化解釋計劃提供了EXPLAIN 命令輸出的圖形表示，使理解查詢的執行和檢測任何性能問題變得更加容易。

注意：在開發 Web 應用程序的過程中，Visual Explain Plans 很有用。

它不是純文本輸出，而是以樹結構顯示查詢執行，每個節點代表一個表、索引或操作，它們之間的連接描述了操作的順序。

MySQL Workbench和EXPLAIN Analyzer等不同的工具可以生成可視化的解釋計劃，並提供交互式界面來導航執行計劃和詳細檢查每個操作。

例如，在 MySQL Workbench 中，生成可視化解釋計劃就像執行查詢並單擊結果選項卡上的“解釋計劃”按鈕一樣簡單。這提供了查詢執行計劃的圖形表示，以及每個操作的詳細信息。這使您能夠識別任何性能問題，然後根據需要優化查詢。

5 – MySQL 調諧器

MySQL Tuner是一個腳本，用於檢查數據庫服務器的性能和配置並提供改進建議。它提供了當前服務器狀態的摘要，包括查詢總數、慢查詢數和當前緩衝池使用情況等信息。

它還可用於檢查各種其他設置，例如數據庫版本、正在使用的存儲引擎和查詢緩存配置，並根據當前工作負載提供優化這些設置的建議。

與其他工具的主要區別之一是它是一個命令行工具，可以在服務器本身或遠程運行，從而可以輕鬆地自動執行監視和優化數據庫性能的過程。

注意：如果您的 Web 應用程序（和數據庫）已經託管在 Servebolt 雲中——這是我們團隊的專長，並且能夠比工具能夠提供的任何建議做得更好。

6 – 查詢分析器

有第三方查詢分析器可用於分析 SQL 查詢，例如MariaDB Enterprise Query Analyzer 、 Dataedo和Percona Toolkit 。與 MariaDB（或 MySQL）中可用的內置工具相比，第三方查詢分析器可以提供額外的特性和功能。

注意：當您處於開發 Web 應用程序的過程中時，Query Profiler 很有用。

例如，它們可能會提供有關查詢性能的更詳細信息，例如執行時間和鎖定等待時間，並且可以以內置工具無法實現的方式提供數據可視化。

如果內置工具足以滿足您的需求，則無需使用第三方查詢分析器。但是，如果您需要更詳細的信息或高級功能，那麼可能值得考慮使用第三方分析器。

7 – 使用監控工具進行分析

還有許多監控工具，例如 Prometheus、Grafana 和 Nagios，可用於分析查詢和監控數據庫的性能。

Prometheus是一個高效的監控系統，可以收集、存儲和查詢指標數據，讓您實時獲得有價值的洞察。它與 MariaDB（和 MySQL）集成以存儲收集的指標，並與 Grafana 一起提供以實現有效的可視化。

Grafana是一種功能強大的開源分析工具，可用於監控和可視化從 Prometheus 收集的數據。設置自定義儀表板和警報可讓您實時關注數據庫的性能。

Nagios可幫助您始終關注數據庫的健康狀況。它可以設置為監控關鍵資源，如 CPU、RAM 和磁盤空間，同時還跟踪其他服務和網絡設備。因為它是高度可配置的，所以它是一個用於主動數據庫查詢監控的好工具。

借助這些服務器監控工具，您可以跟踪性能問題並迅速採取行動，從而確保您的數據庫服務器平穩運行。

常見的查詢優化技術

有幾種常見的查詢優化技術可用於提高 SQL 查詢的性能：

1 – 索引

索引是一種加快查詢速度的方法——尤其是那些使用過濾器(WHERE) 的查詢。 使用索引會在特定表之外的數據庫引擎（MariaDB 或 MySQL）中產生數據結構，並指向您嘗試查詢的數據。我們不會在這篇文章中介紹太多細節，因為使用索引來改進數據庫查詢值得單獨寫一篇文章——我們計劃在未來涵蓋這些內容。

例如，考慮一個名為“訂單”的大表，其中包含數百萬行數據，包括訂單 ID、客戶 ID 和訂單日期等信息。如果執行查詢以檢索特定客戶下的所有訂單，而客戶 ID 列上沒有索引，則 MariaDB 將不得不掃描整個表以找到相關數據。這可能會佔用大量時間和資源，尤其是對於大型表。

從廣義上講，只要您確信您將重複運行特定查詢並閱讀性能問題，創建索引（或多個索引）可能是加快查詢速度的正確方法。

在 WordPress 的上下文中，這很常見。許多插件是由開發人員構建的，他們（出於方便）使用通用的共享表而不使用索引。因此，這也是一個經常有非常顯著的性能提升的領域。

要查看特定表上存在的任何索引，

您可以使用SHOW INDEX FROM查看特定表上存在的任何索引——例如wp_postmeta表的以下示例：

MariaDB [db_name] > SHOW INDEX FROM wp_postmeta;

在一個場景中，我們最近為 wp_postmeta 表創建了兩個索引： sb_postid_metakey和sb_postid_metakey_metaval。

添加這些索引的基礎是查看最慢的查詢並發現它們都是相對相似的，因為它們是 SELECT 語句的特徵，除了大量 (AND/OR) 比較條件外，還使用 WHERE 進行過濾。看到這一點後，我查看了所用表的當前索引，並對查詢運行EXPLAIN EXTENDED以進一步驗證我的方法。

查詢主要是工作並使用wp_postmeta表使用JOIN 。根據發生的順序，添加這些索引將允許 MariaDB（或 MySQL）從索引中獲取響應，而不是掃描整個表及其所有行。

CREATE INDEX sb_postid_metakey ON wp_postmeta (post_id, meta_key);

CREATE INDEX sb_postid_metakey_metaval ON wp_postmeta (post_id, meta_key, meta_value);

這是通過使用您擁有的工具（如上所述）“弄清楚事情”以及數據庫的數據類型和內容的知識的組合。這絕不總是有效。即使這樣做，也並不總能帶來 500% 的性能提升。擁有一個巨大的索引最終可能比掃描所有行更慢，因此必須在應用索引之前和之後測試查詢以確保。

注意：嘗試測試索引速度時，您需要禁用會話的查詢緩存，使用：

SET SESSION query_cache_type=0;

在這種情況下，在使用索引之前，查詢需要 10.437 秒才能執行。在創建這兩個索引之後，同一個查詢花費了 [# 秒]。

2 – 減少數據訪問

減少數據訪問，即最小化要訪問以執行查詢的行數和列數。這可以通過過濾查詢檢索的數據、使用索引和對大型表進行分區來實現。雖然這不是大多數人需要（或能夠）做的事情，但從頭開始設計數據庫查詢時要牢記這一點。

例如，如果數據庫查詢正在查找有關用戶的數據以用於登錄目的，則查詢應為 LIMIT 1，因為顯然需要的用戶數據永遠不會超過一個。

注意：這更多地涉及數據庫設計而不是優化。雖然保持性能很重要，但與大多數最終用戶相比，這項工作與插件開發人員（在 WordPress 的上下文中）更相關。

請記住，在對數據訪問進行任何更改後測試速度之前，您應該確保已通過運行以下命令禁用查詢緩存：

SET SESSION query_cache_type=0;

3 – 使用數據分區

通過將數據劃分為更小的塊，數據庫變得更高效且管理起來更省時。此策略有助於減少備份和更新等維護過程所花費的時間，並限制需要管理的數據量。總體而言，它有助於提高性能和優化資源使用。

要對數據庫中的數據進行分區，您可以按照以下步驟操作：

選擇要分區的表時，請確保選擇一個包含大量數據並且會從拆分中受益的表。這將有助於優化您的系統並提高查詢性能。
為您的數據庫選擇正確的分區方法至關重要。您可以從範圍、列表、散列或鍵分區中進行選擇，具體取決於您的數據結構和您計劃執行的查詢。確保選擇最適合您優化性能和結果需求的那一款。
1. 當您擁有可以劃分為特定範圍的數據時，範圍分區是理想的選擇。例如，如果您有一個包含多年數據的表，您可以創建一個範圍分區來更好地組織它。它可以基於相關列的日期或數值。
2. 列表分區是一種處理數據的有效技術，這些數據可以根據特定參數輕鬆分為不同的組。例如，您有一個表，其中包含按部門分類的員工信息；這需要使用列表分區。
3. 哈希分區是一種基於特定列的哈希值將數據排列成大小相等的簇的有效策略。這允許跨多個分區均勻分佈數據，使其成為高效分佈數據的絕佳選擇。
4. 鍵分區與散列分區類似，但主要區別在於它使用特定列值作為將數據劃分為不同組的基礎。這使得它成為可以根據唯一標識符或自然鍵分成不同組的數據集的理想選擇。
通過創建分區表，您可以有效地將原始表分成更小的表。這是通過在 CREATE TABLE 語句中添加分區子句來實現的，您可以在其中指定所需的分割方法和條件。這樣做可以幫助提高查詢性能，還可以提高數據管理效率。
您可以使用 INSERT INTO ... SELECT 語句將數據從原始表快速復製到新分區的表中。這將很容易地用所有相關信息填充您的分區表。
現在必須重新配置應用程序以利用分區表。這將替換原始表並使您的應用程序更有效率。
在運行任何測試以評估潛在的性能改進之前，必須先通過運行以下命令禁用查詢緩存： SET SESSION query_cache_type=0;
為確保分區表平穩運行，密切關注其性能非常重要。如果您發現任何問題，調整分區條件或切換到另一種方法可能會有所幫助。定期監控您的分區將幫助您最大限度地發揮其潛力。

關於腳本升級和分區表的重要說明

雖然分區數據庫可以在效率方面產生積極的影響，但重要的是要記住運行升級腳本以更改數據庫架構所導致的潛在問題。在編寫這些升級腳本時，必須考慮分區表。如果升級腳本中未考慮分區表，則可能存在幾乎肯定會導致站點出現故障的潛在問題。

例如，如果創建一個腳本來向分區表中添加一個新列，它可能只會更改一個分區，從而在數據中產生不一致和問題。同樣，如果創建升級腳本為分區表添加索引，它只能在一個分區上生成索引，從而導致性能下降和結果不一致。

為避免此類問題，升級腳本的設計必須考慮分區表。這可能涉及在每個分區上單獨運行腳本或修改腳本以使用分區表。進行全面測試以確保升級過程不會產生任何意外問題或數據丟失也很重要。

4 – 雷迪斯

對於 Servebolt 客戶， Redis是一個（付費）插件，可以幫助優化查詢。

Redis（有時稱為遠程字典服務器）是一種開源解決方案，可將數據存儲在內存中，可用於緩存、數據庫，甚至用作消息代理。它可以與數據庫集成以提高性能，充當應用程序和數據庫之間的有效中介。

它通過減少數據庫的負載來提高應用程序的性能和響應時間。這是通過將頻繁使用的數據存儲在 Redis 中而不是每個請求的數據庫中來完成的，從而節省了大量時間。

通過正確配置插件，Redis 可以與數據庫一起使用以優化查詢執行。當 Redis 中不存在所需數據時，應用程序將從數據庫中檢索它並將其存儲在 Redis 中以備將來使用。這使得數據檢索更快、更高效。

通過使用這種方法，應用程序可以受益於 Redis 的快速內存訪問，還可以根據需要存儲和訪問數據庫中的數據。

請記住，如果您是第一次實施 Redis，則需要在運行任何性能測試之前禁用查詢緩存。為此，請使用以下命令：

SET SESSION query_cache_type=0;

結論

MariaDB 和 MySQL 生態系統擁有廣泛的工具和方法，可以更輕鬆地發現數據庫查詢執行中的瓶頸，從而提高 Web 應用程序的性能。

在運行任何應用程序的整個生命週期中都可能發生減速。盡量避免它們很好，但您最終需要知道在開始診斷性能問題時要查看的位置。根據您運行的數據庫的大小和性質，這是一個迭代過程，需要持續監控、故障排除和持續改進，以保持您的數據庫以高標準運行。