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

一、基本語法

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

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

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

EXPLAIN SELECT * FROM users WHERE age > 18;

二、輸出結(jié)果

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

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

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

1. 查詢編號(hào) id

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

2. 查詢類型 select_type

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

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

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

3. 表名 table

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

4. 分區(qū) partitions

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

5. 訪問方式 type

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

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

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

6. 可能使用的索引 possible_keys

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

7. 實(shí)際使用的索引 key

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

8. 索引長度 key_len

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

9. 返回匹配條件的列 ref

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

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

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

11. 過濾比例 filtered

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

12. 其他信息 Extra

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

• Using index: 表示 MySQL 使用了覆蓋索引，而無需回到表中去查找數(shù)據(jù)。
• Using where: 表示 MySQL 使用了 WHERE 條件來過濾數(shù)據(jù)。
• Using temporary: 表示 MySQL 使用了臨時(shí)表。
• 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ù)選項(xiàng)

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

1. EXTENDED

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

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

例如：

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

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

2. FORMAT

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

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

例如：

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

使用 JSON 格式輸出的結(jié)果如下：

{
  "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ù)選項(xiàng)可以幫助我們分析查詢操作涉及到的分區(qū)。在查詢計(jì)劃中，partitions字段可以顯示出查詢操作所涉及的分區(qū)名稱。使用 PARTITIONS 參數(shù)選項(xiàng)可以讓 MySQL 輸出更多分區(qū)相關(guān)的信息。

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ù)輸出的結(jié)果如下：

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

4. ANALYZE

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

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

例如：

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 18;

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

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

四、示例

下面舉幾個(gè)不同類型的查詢語句的查詢計(jì)劃輸出結(jié)果，以幫助讀者更好地理解 EXPLAIN 關(guān)鍵字的用法。

1. 簡單查詢

EXPLAIN SELECT * FROM users WHERE age > 18;

輸出結(jié)果：

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

2. 復(fù)雜查詢（主查詢）

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

輸出結(jié)果：

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

3. JOIN 查詢

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

輸出結(jié)果：

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

4. ORDER BY 和 GROUP BY 查詢

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

輸出結(jié)果：

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

五、高級(jí)特性

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

1. STRAIGHT_JOIN

STRAIGHT_JOIN 可以強(qiáng)制 MySQL 使用連接表的順序。

例如：

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

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

2. SQL_NO_CACHE

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

例如：

EXPLAIN SELECT SQL_NO_CACHE * FROM users WHERE age > 18;

使用 SQL_NO_CACHE 注釋啟用該特性后，MySQL 不會(huì)緩存查詢結(jié)果。

3. SQL_CALC_FOUND_ROWS

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

例如：

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

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

六、性能優(yōu)化

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

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

1. 使用索引

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

2. 避免全表掃描

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

3. 減少臨時(shí)表和文件排序

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

4. 避免子查詢

子查詢通常涉及到多次查詢操作，會(huì)增加數(shù)據(jù)庫系統(tǒng)的負(fù)擔(dān)，導(dǎo)致查詢效率下降。可以通過使用 JOIN 操作、合理使用索引等方式來避免子查詢的出現(xiàn)。

5. 避免隱式類型轉(zhuǎn)換

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

總結(jié)

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

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

最新評(píng)論