在 MySQL 中，查詢執(zhí)行計劃是指 MySQL 在執(zhí)行 SQL 查詢語句時，優(yōu)化器生成的用于指導查詢引擎執(zhí)行查詢操作和訪問數(shù)據(jù)的一個計劃。這個計劃包含了查詢語句的執(zhí)行順序、使用的索引以及關聯(lián)表等信息，因此它對查詢性能的影響非常大。而 EXPLAIN 關鍵字可以幫助我們分析查詢執(zhí)行計劃，從而優(yōu)化查詢性能。

一、基本語法

在 MySQL 中，使用 EXPLAIN 關鍵字可以分析查詢語句的執(zhí)行計劃。其基本語法如下：

EXPLAIN SELECT column1, column2, ... FROM table_name WHERE condition;

其中，SELECT 語句是需要分析的查詢語句，可以通過 WHERE 條件限制查詢范圍；EXPLAIN 關鍵字用來分析該查詢語句的執(zhí)行計劃，輸出查詢計劃的相關信息。下面是一個簡單的例子：

EXPLAIN SELECT * FROM users WHERE age > 18;

二、輸出結果

執(zhí)行 EXPLAIN 關鍵字后，MySQL 會輸出查詢語句的執(zhí)行計劃，包括以下幾個方面的信息：

• id: 查詢編號，表示查詢語句的執(zhí)行順序。
• select_type: 查詢類型，表示查詢的類型及優(yōu)化器使用的策略。
• table: 表名，表示查詢時訪問的表名稱。
• partitions: 分區(qū)，表示查詢操作涉及到的分區(qū)。
• type: 訪問方式，表示 MySQL 在查找表時使用的讀取方式。
• possible_keys: 可能使用的索引，表示 MySQL 可以使用哪些索引來優(yōu)化查詢。
• key: 實際使用的索引，表示 MySQL 最終選擇哪個索引來優(yōu)化查詢。
• key_len: 索引長度，表示 MySQL 在使用索引時所需要的長度。
• ref: 返回匹配條件的列，表示 MySQL 在索引中查找值時使用的比較值。
• rows: 掃描的行數(shù)，表示 MySQL 檢索數(shù)據(jù)的行數(shù)。
• filtered: 過濾比例，表示 MySQL 對檢索的數(shù)據(jù)進行過濾的比例。
• Extra: 其他信息，可能會包含一些有用的輔助信息。

接下來，我們將逐個解釋這些查詢計劃中提供的信息。

1. 查詢編號 id

id 表示了查詢語句的執(zhí)行順序。在一個查詢語句中，不同的操作都會有一個唯一的編號。這個編號為整數(shù)類型，表示 MySQL 執(zhí)行查詢操作的順序。在查詢計劃中，如果兩個操作的編號相同，則表示它們是同一級別的操作。

2. 查詢類型 select_type

select_type 表示了查詢操作的類型。根據(jù)查詢操作的類型，MySQL 可以使用不同的優(yōu)化器策略來處理查詢語句。常見的幾種查詢類型如下：

• SIMPLE: 簡單查詢，不包含子查詢或者 UNION 查詢。
• PRIMARY: 主查詢，包含多個子查詢或者 UNION 查詢。
• SUBQUERY: 子查詢，作為其它查詢的子查詢使用。
• DERIVED: 派生表，作為其它查詢的臨時表使用。
• UNION: UNION 查詢。
• UNION RESULT: UNION 查詢的結果集。
• DEPENDENT UNION: UNION 查詢的依賴查詢。
• DEPENDENT SUBQUERY: 依賴子查詢，其結果集取決于外層查詢。
• MATERIALIZED: 物化查詢，將查詢結果先緩存再做后續(xù)操作。

可以看到，查詢類型分為簡單查詢和復雜查詢兩種。其中，復雜查詢還可以分為主查詢、子查詢、派生表以及 UNION 查詢等幾個子類型。

3. 表名 table

table 表示了當前執(zhí)行的操作涉及到的表名稱。如果包含多個表，則中間使用逗號 , 隔開。

4. 分區(qū) partitions

partitions 表示當前正在操作的分區(qū)，如果查詢操作沒有涉及到分區(qū)，則該字段值為空。否則會顯示出查詢操作所涉及到的分區(qū)名稱。

5. 訪問方式 type

type 表示了 MySQL 在查找表時使用的讀取方式，也就是訪問的方式。常見的幾種訪問方式如下：

• ALL: 全表掃描，將整個表的數(shù)據(jù)都讀入內(nèi)存，對于大表來說，這種方式的代價非常大，一般不建議使用。
• index: 全索引掃描，需掃描整個索引文件，并且需要進行回表操作，從而導致性能低下。
• range: 范圍掃描，只掃描滿足查詢條件的記錄。
• ref: 索引查找，通過某個索引找到一個或多個值，并訪問對應的行。
• eq_ref: 唯一索引查找，類似 ref，區(qū)別在于索引本身是唯一的，因此只返回一行數(shù)據(jù)。
• const: 常量查找，MySQL 在查詢時發(fā)現(xiàn)查詢條件中有常量值時，直接按常量值進行查詢。

常用的訪問方式包括 ref、eq_ref、range 和 index。這些訪問方式都是使用索引進行查找數(shù)據(jù)的方式，可以有效地提高查詢效率。

6. 可能使用的索引 possible_keys

possible_keys 表示 MySQL 可以使用哪些索引來優(yōu)化查詢。在查詢計劃中，可能會有多個索引可以用于查詢，這個字段列舉了這些索引的名稱（多個索引名之間以逗號 , 分隔）。MySQL 在執(zhí)行查詢操作時，會根據(jù)所提供的查詢條件使用其中一個索引。

7. 實際使用的索引 key

key 表示 MySQL 實際使用的索引。如果查詢語句中包含了可用的索引，則 MySQL 將使用其中一個索引以優(yōu)化查詢。這個字段顯示了實際使用的索引的名稱。

8. 索引長度 key_len

key_len 表示 MySQL 在使用索引時所需要的長度。這個長度是以字節(jié)為單位計算的，并且包含了被索引字段的所有部分。這個長度對查詢性能非常重要，如果這個值太大，將會導致查詢速度變慢。

9. 返回匹配條件的列 ref

ref 表示 MySQL 在索引中查找值時使用的比較值。這個比較值是查詢條件中列的值，或者是常量值。如果使用的索引是唯一索引（eq_ref），則這個值只有一個。否則，就可能有多個值。

10. 掃描的行數(shù) rows

rows 表示 MySQL 檢索數(shù)據(jù)的行數(shù)。這個值是一個估計值，并不一定非常準確。

11. 過濾比例 filtered

filtered 表示 MySQL 對檢索的數(shù)據(jù)進行過濾的比例。如果使用了索引，則這個比例表示已經(jīng)從索引中檢索出的行數(shù)占總行數(shù)的比例。

12. 其他信息 Extra

Extra 表示其他一些信息，可能包括：

• Using index: 表示 MySQL 使用了覆蓋索引，而無需回到表中去查找數(shù)據(jù)。
• Using where: 表示 MySQL 使用了 WHERE 條件來過濾數(shù)據(jù)。
• Using temporary: 表示 MySQL 使用了臨時表。
• Using filesort: 表示 MySQL 需要使用文件排序來完成查詢操作。
• Using join buffer: 表示 MySQL 使用了連接緩存來加速聯(lián)接（JOIN）操作。
• Impossible where: 表示 MySQL 發(fā)現(xiàn)查詢條件是不可能出現(xiàn)的，因此不需要執(zhí)行。
• Select tables optimized away: 表示 MySQL 可以通過優(yōu)化查詢的方式省略某些表。

三、參數(shù)選項

EXPLAIN 關鍵字還支持一些參數(shù)選項，可以幫助我們分析查詢計劃和優(yōu)化查詢性能。以下是一些常用的參數(shù)選項：

1. EXTENDED

EXTENDED 參數(shù)選項將返回更詳細的查詢執(zhí)行計劃信息，包括掃描的行數(shù)、內(nèi)存使用情況等。在默認情況下，MySQL 只返回一些基本的信息，該參數(shù)選項可以使輸出更加詳細。

EXPLAIN EXTENDED SELECT column1, column2, ... FROM table_name WHERE condition;

例如：

EXPLAIN EXTENDED SELECT name, age FROM users ORDER BY age DESC;

查詢語句中使用了 ORDER BY 子句，因此 MySQL 使用了臨時表和文件排序的方式進行優(yōu)化。

2. FORMAT

FORMAT 參數(shù)選項可以指定輸出的格式，常用的格式有 JSON 和 XML 兩種。

EXPLAIN FORMAT=JSON SELECT column1, column2, ... FROM table_name WHERE condition;

例如：

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 18;

使用 JSON 格式輸出的結果如下：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.10"
    },
    "table": {
      "table_name": "users",
      "access_type": "range",
      "possible_keys": [
        "age"
      ],
      "key": "age",
      "used_key_parts": [
        "age"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 20,
      "rows_produced_per_join": 20,
      "filtered": "100.00",
      "index_condition": "(`users`.`age` > 18)"
    }
  }
}

3. PARTITIONS

PARTITIONS 參數(shù)選項可以幫助我們分析查詢操作涉及到的分區(qū)。在查詢計劃中，partitions字段可以顯示出查詢操作所涉及的分區(qū)名稱。使用 PARTITIONS 參數(shù)選項可以讓 MySQL 輸出更多分區(qū)相關的信息。

EXPLAIN PARTITIONS SELECT column1, column2, ... FROM table_name PARTITION (p1,p2...) WHERE condition;

例如：

EXPLAIN PARTITIONS SELECT * FROM orders WHERE date >= '2022-01-01' AND date < '2022-02-01';

使用 PARTITIONS 參數(shù)輸出的結果如下：

結果顯示，查詢操作涉及到了名為 p202201 的分區(qū)表。

4. ANALYZE

ANALYZE 參數(shù)選項可以強制 MySQL 對查詢操作進行實際的執(zhí)行，從而獲取更準確的查詢計劃信息。如果沒有使用 ANALYZE 參數(shù)，則 MySQL 可能會基于統(tǒng)計信息來做出一些估算。

EXPLAIN ANALYZE SELECT column1, column2, ... FROM table_name WHERE condition;

例如：

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 18;

使用 ANALYZE 參數(shù)輸出的結果中包含了實際執(zhí)行查詢的時間和 I/O 統(tǒng)計信息，如下所示：

結果顯示，執(zhí)行該查詢的實際執(zhí)行時間為 analyzed_time 字段所示，I/O 消耗的頁數(shù)為 sampled_pages 字段所示。

四、示例

下面舉幾個不同類型的查詢語句的查詢計劃輸出結果，以幫助讀者更好地理解 EXPLAIN 關鍵字的用法。

1. 簡單查詢

EXPLAIN SELECT * FROM users WHERE age > 18;

輸出結果：

該查詢操作為簡單查詢（SIMPLE），使用了范圍掃描（range）方式進行查詢。MySQL 可能使用了 age 索引來優(yōu)化查詢，而實際上確實使用了該索引（age）。需要掃描的行數(shù)為 20 行，沒有涉及分區(qū)的相關信息。

2. 復雜查詢（主查詢）

EXPLAIN SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

輸出結果：

該查詢操作為復雜查詢，包含一個子查詢（SUBQUERY）。這個查詢語句中使用了 AVG 函數(shù)來計算平均值，并且使用該平均值作為主查詢的查詢條件。在查詢計劃中，MySQL 將主查詢和子查詢分別分配了不同的查詢編號。主查詢使用了全表掃描（ALL）方式，子查詢使用了常量查找（const）方式。

3. JOIN 查詢

EXPLAIN SELECT users.name, orders.order_number FROM users JOIN orders ON users.id = orders.user_id;

輸出結果：

該查詢?yōu)?JOIN 查詢，使用了 JOIN 關鍵字將兩個表 users 和 orders 進行聯(lián)接。在查詢計劃中，MySQL 首先使用了全表掃描（ALL）方式掃描 users 表，然后使用索引查找（ref）方式查找 orders 表中的相關記錄。需要掃描的行數(shù)比較少，分別為 1000 和 5 行。

4. ORDER BY 和 GROUP BY 查詢

EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name ORDER BY name;

輸出結果：

該查詢?yōu)?GROUP BY 查詢，使用了 GROUP BY 關鍵字，按照 name 列進行分組，并對分組后的結果進行 COUNT(*) 統(tǒng)計。此外，還有一個 ORDER BY 子句，按 name 的字母順序排序。在查詢計劃中，MySQL 使用了索引查找（index）方式來進行查詢，并且需要掃描 1000 行記錄。

五、高級特性

除了上述可選參數(shù)以外，EXPLAIN 還支持一些高級特性，可以通過在查詢語句中使用特定的注釋來啟用這些特性。這些特性主要包括以下幾種：

1. STRAIGHT_JOIN

STRAIGHT_JOIN 可以強制 MySQL 使用連接表的順序。

例如：

EXPLAIN SELECT * FROM orders STRAIGHT_JOIN users ON orders.user_id = users.id;

使用 STRAIGHT_JOIN 注釋啟用該特性后，MySQL 將按照指定的順序進行連接操作。

2. SQL_NO_CACHE

SQL_NO_CACHE 可以讓 MySQL 不緩存查詢結果，每次都強制重新執(zhí)行查詢操作。

例如：

EXPLAIN SELECT SQL_NO_CACHE * FROM users WHERE age > 18;

使用 SQL_NO_CACHE 注釋啟用該特性后，MySQL 不會緩存查詢結果。

3. SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS 可以在執(zhí)行查詢操作的同時獲取總記錄數(shù)，有效地避免了多次查詢。

例如：

EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE age > 18 LIMIT 10;

使用 SQL_CALC_FOUND_ROWS 注釋啟用該特性后，在查詢操作的結果中，可以額外輸出一個 rows_examined 字段，表示掃描的記錄數(shù)，以及一個 rows_founds 字段，表示滿足條件的總記錄數(shù)。

六、性能優(yōu)化

通過使用 EXPLAIN 關鍵字，我們可以深入了解查詢語句的執(zhí)行過程，并發(fā)現(xiàn)其中的瓶頸和改進空間，從而優(yōu)化查詢性能，提升數(shù)據(jù)庫系統(tǒng)的整體運行效率。

下面是一些常見的性能優(yōu)化技巧：

1. 使用索引

在設計表結構時，可以通過創(chuàng)建索引來提高查詢效率。可以使用 EXPLAIN 查看查詢操作是否使用了索引，以及是否使用最優(yōu)的索引；如果沒有使用索引或者使用了不合適的索引，可以考慮為相應的列添加新的索引。

2. 避免全表掃描

全表掃描會消耗大量的 I/O 資源，導致查詢效率下降。可以優(yōu)化查詢條件，盡可能地使用索引或者其他方式（如分區(qū)表）來避免全表掃描。

3. 減少臨時表和文件排序

排序操作通常需要使用臨時表和文件排序，會消耗大量的 CPU 和 I/O 資源，降低查詢效率。可以通過優(yōu)化查詢條件、增加合適的索引、調(diào)整查詢順序等方式來減少排序操作的出現(xiàn)。

4. 避免子查詢

子查詢通常涉及到多次查詢操作，會增加數(shù)據(jù)庫系統(tǒng)的負擔，導致查詢效率下降?？梢酝ㄟ^使用 JOIN 操作、合理使用索引等方式來避免子查詢的出現(xiàn)。

5. 避免隱式類型轉換

在查詢操作中，經(jīng)常會涉及到不同類型之間的比較，比如字符串和數(shù)字之間的比較。如果 MySQL 需要進行隱式類型轉換，會導致查詢效率下降。可以使用 CAST 或者 CONVERT 函數(shù)來顯式轉換數(shù)據(jù)類型，避免隱式類型轉換的出現(xiàn)。

總結

本文詳細講解了 MySQL 中的 EXPLAIN 關鍵字，包括其基本用法、輸出結果的各個字段含義、可選參數(shù)、更多高級特性以及性能優(yōu)化等相關內(nèi)容。使用 EXPLAIN 關鍵字可以深入了解查詢語句的執(zhí)行過程，發(fā)現(xiàn)其中的瓶頸和改進空間，從而優(yōu)化查詢性能，提升數(shù)據(jù)庫系統(tǒng)的整體運行效率。在實際應用中，我們應當密切關注查詢語句的執(zhí)行情況，不斷改進優(yōu)化，提高系統(tǒng)性能和穩(wěn)定性。

到此這篇關于全面解析MySQL Explain如何優(yōu)化SQL查詢性能的文章就介紹到這了,更多相關MySQL Explain內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論