選單
探索 Prisma

MySQL / 讀取與查詢資料

分析與優化 MySQL 中的慢查詢

分享至

簡介

維持資料庫系統的效能是優化資料庫支援應用程式回應速度的重要環節。這包括管理伺服器的硬體資源和軟體配置，但也關係到您要求它執行的查詢。

在之前的指南中，我們檢視了如何在 MySQL 中識別效能不佳和慢查詢。在本指南中，我們將繼續探討如何評估和優化慢速執行的查詢。這將建立在先前文章的討論基礎上，為您提供一套完整的策略，以識別和修復與效能不佳查詢相關的問題。

啟用查詢分析以了解慢查詢階段

當嘗試優化慢查詢時，您可能想要做的第一件事是對查詢進行分析，以找出查詢在哪個階段花費最多時間。

首先，輸入以下內容檢查是否已啟用查詢分析：

SELECT @@PROFILING;
 +-------------+
 | @@profiling |
 +-------------+
 |           0 |
 +-------------+
 1 row in set, 1 warning (0.00 sec)

0 表示目前已停用分析。您可以輸入以下內容啟用分析：

SET PROFILING = 1;

啟用分析後，您可以輸入以下內容開始列出可用的分析：

SHOW PROFILES;
 +----------+------------+--------------------------+
 | Query_ID | Duration   | Query                    |
 +----------+------------+--------------------------+
 |        1 | 3.00368075 | select sleep(3)          |
 |        2 | 0.00740700 | select * from mysql.user |
 |        3 | 0.00075875 | select @@profiling       |
 +----------+------------+--------------------------+
 3 rows in set, 1 warning (0.00 sec)

輸出將顯示自啟用分析以來，目前工作階段中已執行的最新陳述式。每個查詢都會顯示總執行時間，並具有可用於深入探究的查詢編號。

若要顯示最近查詢的分析資訊，請輸入：

SHOW PROFILE;
 +----------------------+----------+
 | Status               | Duration |
 +----------------------+----------+
 | starting             | 0.000144 |
 | checking permissions | 0.000020 |
 | Opening tables       | 0.000024 |
 | init                 | 0.000013 |
 | optimizing           | 0.000047 |
 | executing            | 0.000032 |
 | end                  | 0.000010 |
 | query end            | 0.000016 |
 | closing tables       | 0.000012 |
 | freeing items        | 0.000367 |
 | cleaning up          | 0.000074 |
 +----------------------+----------+
 11 rows in set, 1 warning (0.00 sec)

預設情況下，輸出會準確顯示陳述式在查詢處理的每個階段所花費的時間。這可以幫助您準確識別查詢的哪個部分效能不佳。

若要顯示與不同查詢相關聯的結果，您可以加入 FOR QUERY <N> 子句以及 SHOW PROFILES 輸出提供的查詢 ID。

例如，若要取得查詢 1 的預設顯示，您可以輸入：

SHOW PROFILE FOR QUERY 1;
 +----------------------+----------+
 | Status               | Duration |
 +----------------------+----------+
 | starting             | 0.001419 |
 | checking permissions | 0.000028 |
 | Opening tables       | 0.000030 |
 | init                 | 0.000020 |
 | optimizing           | 0.000020 |
 | executing            | 0.000025 |
 | User sleep           | 3.000165 |
 | end                  | 0.000043 |
 | query end            | 0.000019 |
 | closing tables       | 0.000012 |
 | freeing items        | 0.000540 |
 | logging slow query   | 0.001320 |
 | cleaning up          | 0.000041 |
 +----------------------+----------+
 13 rows in set, 1 warning (0.00 sec)

在這裡，輸出清楚顯示 User sleep 狀態花費了很長時間，這直接管理了已執行的函式。

您也可以變更輸出以顯示不同類型的資訊。一旦您檢視了預設輸出，為了檢查更具體的資訊，這通常很有幫助。

例如，您可以輸入以下內容查看第一個查詢的區塊輸入和輸出相關資訊：

SHOW PROFILE BLOCK IO FOR QUERY 1;
 +----------------------+----------+--------------+---------------+
 | Status               | Duration | Block_ops_in | Block_ops_out |
 +----------------------+----------+--------------+---------------+
 | starting             | 0.001419 |          184 |             0 |
 | checking permissions | 0.000028 |            0 |             0 |
 | Opening tables       | 0.000030 |            0 |             0 |
 | init                 | 0.000020 |            0 |             0 |
 | optimizing           | 0.000020 |            0 |             0 |
 | executing            | 0.000025 |            0 |             0 |
 | User sleep           | 3.000165 |            0 |             0 |
 | end                  | 0.000043 |            0 |             0 |
 | query end            | 0.000019 |            0 |             0 |
 | closing tables       | 0.000012 |            0 |             0 |
 | freeing items        | 0.000540 |            0 |             0 |
 | logging slow query   | 0.001320 |           64 |             8 |
 | cleaning up          | 0.000041 |            0 |             0 |
 +----------------------+----------+--------------+---------------+
 13 rows in set, 1 warning (0.00 sec)

除了先前的資訊外，這還顯示了 MySQL 必須與檔案系統上的區塊資料互動的時間。

您可以指定的可用輸出變化包括：

  • ALL：顯示所有可用資訊
  • BLOCK IO：顯示查詢產生的輸入和輸出區塊數量
  • CONTEXT SWITCHES：顯示執行查詢時發生的自願和非自願內容切換次數
  • CPU：顯示使用者空間和系統類別中的 CPU 時間
  • IPC：顯示已傳送和接收的跨程序訊息數量
  • PAGE FAULTS：顯示頁面錯誤的數量，分為主要錯誤和次要錯誤
  • SOURCE：顯示 MySQL 中正在執行的實際函式，以及其來源檔案和行號，以允許執行對應
  • SWAPS：顯示查詢所需的交換次數

若要顯示與查詢相關聯的所有可能資訊，請輸入：

SHOW PROFILE ALL FOR QUERY 1\G

我們使用替代輸出格式設定 \G 陳述式終止序列，使結果更具可讀性，因為會顯示大量額外輸出。

一旦您使用分析資訊對查詢的哪個部分速度緩慢有了很好的了解後，您可以關閉分析以保留資源：

SET PROFILING = 0;

使用 EXPLAIN 了解查詢效能

查詢分析應有助於您了解執行時間最長的查詢執行階段，以及與查詢相關聯的資源。然後，您可以使用 MySQL 的 EXPLAIN 陳述式來了解查詢最佳化工具對查詢的評估。

EXPLAIN 陳述式接受 SELECT、DELETE、UPDATE、INSERT 或 REPLACE 陳述式，並顯示查詢最佳化工具將如何評估和執行給定的查詢。輸出顯示 MySQL 將如何聯結表格、選取欄位、排序和篩選結果等等。它還顯示了將評估多少列，以及將諮詢哪些索引以加快處理速度等資訊。

若要了解此陳述式如何運作，請要求 MySQL 解釋它將如何執行查詢 SELECT * FROM INFORMATION_SCHEMA.VIEWS;

EXPLAIN SELECT * FROM information_schema.views\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cat
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: name
      key_len: 194
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: vw
   partitions: NULL
         type: ref
possible_keys: schema_id,type,view_client_collation_id,view_connection_collation_id,type_2
          key: type
      key_len: 1
          ref: const
         rows: 100
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: sch
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,catalog_id
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.schema_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: conn_coll
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.view_connection_collation_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: client_coll
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,character_set_id
          key: PRIMARY
      key_len: 8
          ref: mysql.vw.view_client_collation_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: cs
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.client_coll.character_set_id
         rows: 1
     filtered: 100.00
        Extra: NULL
6 rows in set, 1 warning (0.01 sec)

以上輸出顯示需要六個單獨的查詢才能將該查詢的資訊匯集在一起。如果您查看表格定義，就可以了解原因：

SHOW CREATE TABLE information_schema.views\G

為了格式化而修改，產生的表格建立命令如下所示：

CREATE algorithm=undefined definer=`mysql.infoschema`@`localhost` SQL security definer view `views`
AS
  SELECT `cat`.`name`                                                                                                             AS `table_catalog`,
         `sch`.`name`                                                                                                             AS `table_schema`,
         `vw`.`name`                                                                                                              AS `table_name`,
         IF((can_access_view(`sch`.`name`,`vw`.`name`,`vw`.`view_definer`,`vw`.`options`) = TRUE),`vw`.`view_definition_utf8`,'') AS `view_definition`,
         `vw`.`view_check_option`                                                                                                 AS `check_option`,
         `vw`.`view_is_updatable`                                                                                                 AS `is_updatable`,
         `vw`.`view_definer`                                                                                                      AS `definer`,
         IF((`vw`.`view_security_type` = 'DEFAULT'),'DEFINER',`vw`.`view_security_type`)                                          AS `security_type`,
         `cs`.`name`                                                                                                              AS `character_set_client`,
         `conn_coll`.`name`                                                                                                       AS `collation_connection`
  FROM   (((((`mysql`.`tables` `vw`
  JOIN   `mysql`.`schemata` `sch`
  ON    ((
                       `vw`.`schema_id` = `sch`.`id`)))
  JOIN   `mysql`.`catalogs` `cat`
  ON    ((
                       `cat`.`id` = `sch`.`catalog_id`)))
  JOIN   `mysql`.`collations` `conn_coll`
  ON    ((
                       `conn_coll`.`id` = `vw`.`view_connection_collation_id`)))
  JOIN   `mysql`.`collations` `client_coll`
  ON    ((
                       `client_coll`.`id` = `vw`.`view_client_collation_id`)))
  JOIN   `mysql`.`character_sets` `cs`
  ON    ((
                       `cs`.`id` = `client_coll`.`character_set_id`)))
  WHERE  ((
                       0 <> can_access_table(`sch`.`name`,`vw`.`name`))
         AND    (
                       `vw`.`type` = 'VIEW'))

有五個聯結陳述式，表示必須查詢六個單獨的表格才能建構結果。這與我們執行的 EXPLAIN 陳述式中傳回的項目數量相符 — 每個表格一個。

若要解讀 EXPLAIN 陳述式的結果，您需要了解每個個別欄位的含義。特別值得關注的是 type、key、rows 和 filtered 欄位。

解讀 type 欄位

type 欄位告訴您 MySQL 如何實作查詢中的任何聯結運算。MySQL 文件中對可用的不同聯結類型及其可能如何影響效能有很好的描述。

type: const

例如，const 類型會非常快，因為這表示 MySQL 正在將唯一欄位與常數值進行比較，這最多應傳回單一列。

type: eq_ref

eq_ref 類型類似地是組合兩個表格時最快的聯結運算，因為它表示查詢使用完整的唯一、非 Null 索引值進行比較。

type: index

index 類型在需要完整表格掃描時使用，但查詢可以掃描索引樹而不是掃描實際表格。這使得它在查詢範圍內的效能相對較低，但仍然在某種程度上進行了最佳化，因為它可以掃描較小的索引而不是實際表格。

type: ref

ref 聯結類型表示 MySQL 能夠使用索引的前導欄位進行比較（當整個索引包含未用於比較的複數欄位時）。這表示 MySQL 能夠有效率地使用部分索引，即使它與整個比較不符。

解讀 key 欄位

key 欄位及其相關欄位（possible_keys、key_length 和 ref）可協助您了解哪些索引可用、使用了哪些索引以及索引使用效率如何。

. . .
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.client_coll.character_set_id
. . .

possible_keys 欄位告訴我們哪些索引可用於要聯結的表格。它很有幫助，因為它顯示了 MySQL 在嘗試尋找適合滿足查詢的索引時的起點。如果您在非主索引鍵上聯結表格，這可能是尋找位置以確保您有可用的索引來加速處理程序。

key 欄位本身會告訴您 MySQL 決定使用哪個索引來執行查詢。在某些情況下，這甚至可能包括未在 possible_keys 欄位中列出的結果。例如，當索引包含查詢要求的所有欄位時，掃描它的效率可能比相關表格更高，即使索引不適用於（且未用於）實際聯結。

key_length 欄位指出實際使用了多少索引。對於多欄位索引，這可以幫助您了解此特定比較使用了多少儲存的索引。ref 欄位在這裡也發揮作用，它告訴您具體而言，正在與特定索引進行比較的是什麼。這可以幫助您判斷建立更最佳化的索引是否是個好主意。

解讀評估的列

rows 和 filtered 欄位很有用，因為它們告訴我們正在檢查的表格的範圍。

         rows: 1
     filtered: 100.00

rows 欄位給出了 MySQL 認為它必須評估才能執行查詢每個部分的列數。此處的數字受到我們討論過的所有先前欄位的影響。它讓我們了解給定提供的陳述式和可用的索引，MySQL 可以多有效率地削減整個表格。

filtered 欄位估計在套用查詢中的任何列篩選條件後將傳回的評估列的百分比。如果值為 100，則表示不會發生列篩選。低於 100 的值表示由於聯結條件或其他篩選，某些評估的列將不會傳回。

改寫慢查詢以加快執行速度

現在我們已經討論了一些 MySQL 允許您評估個別陳述式效能的方法，我們可以討論如何提高該效能。

您要考慮的確切步驟很大程度上取決於您在查詢分析和 EXPLAIN 調查中發現的內容。我們將在下面介紹一些優化查詢效能的最常見方法。

建立額外索引

提高查詢執行速度最直接的方法之一是確保您的表格有可用的適當索引。您的索引應反映您查詢資料的最常見方式，並可能隨著您的使用情況演變或實作額外查詢而隨時間變更。

若要建立新索引，您可以使用 CREATE INDEX 陳述式：

CREATE INDEX <index_name> ON <table> (<columns_to_index>)

您可以建立許多不同類型的索引，具體取決於您要索引的欄位的資料類型、值的最大長度以及您要使用的索引結構。如果您的查詢已在使用索引且索引效能不佳，則這些選項可能值得研究。但是，首先，您可以盡可能保持簡單，主要使用預設值。

您需要考量的第一個因素是您要索引哪些欄位。如果您經常在特定欄位上進行聯結，請務必確保您的索引中已考量到這些欄位。索引主要用於列查詢，因此聯結運算（其中不同表格中的各種列會進行比對）在沒有索引的情況下非常耗費資源。

如果單一欄位索引完全封裝了您通常查詢表格的方式，則可以使用單一欄位索引。您也可以在多個欄位上建立索引，以使索引更實用。如果在多個欄位上建立索引，則排序非常重要。第一個欄位應是尋找比對項目的主要方式，而任何額外欄位應有助於您精簡這些比對項目。

例如，如果您為表格 person 建立 first_name 和 last_name 的索引，您幾乎肯定會想要使用如下所示的內容定義索引：

CREATE INDEX last_and_first_names ON person (last_name, first_name);

在這裡，last_name 欄位是索引中的第一個或最左邊的欄位，表示它可以快速尋找 Alan Smith 或所有 Smith 項目。但是，它無法有效地尋找所有 Alan 實例（first_name），除非我們預期經常僅按名字查詢，否則這可能沒問題。

雖然建立索引可以幫助您提高資料集的讀取效能，但重要的是不要過度索引您的表格。索引會以寫入速度為代價，因為每次在表格中記錄新值時，也必須更新與其相關聯的所有索引。如果表格的主索引鍵與將對其使用的查詢模式非常吻合，則表格效果最佳，因為這可讓您從強大的索引效能中受益，而無需大量補充索引來適應不同的存取模式。

限制候選列

您可以加速查詢的另一種方法是在可能的情況下限制陳述式中的候選列和傳回列的數量。這是一個非常特定於情境的建議，因此在許多情況下並不適用，但如果您可以減少 MySQL 必須評估的列數，則可以顯著提高效能。

您可以使用 LIMIT 子句來執行此操作。例如，若要僅傳回五個結果，您可以使用如下所示的格式：

SELECT * FROM <table> LIMIT 5

在許多情況下（取決於查詢中包含的其他子句），限制傳回的列數可以幫助 MySQL 短路查詢處理程序，比正常情況更快停止。這有助於提前節省時間，而不是在整個資料集上執行，然後稍後截斷結果。

當聯結多個表格時，這尤其有用。在任何可能的情況下，最好在聯結發生之前限制查詢的結果。這允許 MySQL 評估少量資料到其餘聯結，而不是第一個表格的全部資料。

僅選取相關欄位

提高查詢效能的另一種方法是注意您實際傳回的欄位。這可以幫助從多個不同方面加速結果。

限制您選取的欄位會影響效能的主要方式是透過最小化網路負載。透過傳回您不需要的欄位，較大的查詢可能會大幅增加查詢產生的網路流量。僅選取您需要滿足查詢邏輯約束的欄位，有助於透過減少在網路上傳輸的內容來預防此問題。

對您傳回的欄位具有選擇性的另一種影響效能的方式與索引有關。如果 MySQL 能夠將您傳回的所有欄位與索引建立關聯，則它可能會對較小的、更有組織的資料集而不是完整表格進行運算。當您的索引與您的查詢模式良好匹配時，限制您正在查詢的欄位允許發生這種情況。

移除與前導萬用字元的比較

可能對查詢效能產生負面影響的一件事是過度依賴萬用字元。在 SQL 中，% 字元在比較中用作萬用字元，表示可以替換任何值。這是一種非常強大的在記錄中尋找資料的方式，但它可能會對查詢效能產生非常大的影響。

對於前導或前綴萬用字元尤其如此。前導萬用字元是檢查欄位值是否符合以萬用字元開頭的模式的比較。例如，檢查欄位是否符合 %Main St.。這效能特別差，因為 MySQL 無法使用索引來探索相符的結果。相反，即使該欄位有索引，它也必須在整個表格中尋找比對項目。

在這個特定範例中，如果您將定期按街道名稱查詢以探索該街道上的所有地址，則將街道名稱和街道號碼分成兩個單獨的欄位可能更有意義。這將允許您使用索引快速查詢「Main St.」上的地址，然後在您尋找特定地址時根據需要篩選這些結果。首先使用街道名稱，然後使用街道號碼欄位建立複合索引會使速度更快。

結論

在本指南中，我們逐步介紹了一些評估個別查詢的基本方法，並取得有關可能使其速度變慢的更多資訊。我們討論了分析查詢，並使用 MySQL 的 EXPLAIN 陳述式來取得有關查詢規劃工具如何評估查詢的詳細資訊。然後，我們查看了一些方法，您可以透過修改查詢以更好地與資料庫檢索資料的方式對齊來避免這些速度變慢的情況。

資料庫是複雜的軟體，負責對大量可能採用多種不同格式的資料進行運算。除了簡單地檢索必要的資料外，人們還期望資料庫應能夠快速傳回結果。MySQL 的查詢規劃工具和最佳化工具機制中建置了無數的最佳化，以協助盡可能縮短回答查詢所需的時間。

但是，資料庫只能在有限的意義上自動最佳化。作為資料庫的使用者，您還需要能夠了解正在存取的資料、您對查詢的要求，以及哪些查詢對於最佳化最重要。為此，務必了解如何評估您的條件，並在查詢效能未達到預期水準時進行調整。

PRISMA.IO 相關內容

如果您將 Prisma 與 MySQL 資料庫搭配使用，您可以閱讀文件中的查詢最佳化章節，以了解優化查詢的方法。這將幫助您了解在使用 Prisma 時，各種查詢建構如何影響您的資料庫效能。

Prisma 是一個適用於 Typescript 和 Node.js 的開放原始碼資料庫工具組，旨在讓應用程式開發人員在使用資料庫時更有效率且更具信心。
關於作者
Justin Ellingwood

Justin Ellingwood

Justin 自 2013 年以來一直撰寫關於資料庫、Linux、基礎架構和開發人員工具的文章。他目前與妻子和兩隻兔子住在柏林。他通常不必以第三人稱寫作，這對所有相關方來說都是一種解脫。
在 GitHub 上編輯此頁面

Prisma 資料指南

一個不斷成長的文章庫，專注於使資料庫更平易近人。

用 ❤️ 由 Prisma 製作