SQL執行計劃詳解

一、執行計劃概述

在介紹SQL執行計劃之前，我們需要先明確什麼是SQL執行計劃。SQL執行計劃是指SQL語句執行時資料庫系統為其生成的一個操作步驟列表，該列表記錄了SQL語句如何通過索引、表掃描等方式訪問資料庫表，以及如何使用連接方式、排序等邏輯操作。

SQL執行計劃非常重要，因為執行計劃可以幫助我們理解SQL語句的執行過程，以及優化SQL語句的性能。

二、查看執行計劃

了解SQL執行計劃後，我們需要知道如何查看SQL執行計劃。在MySQL資料庫中，我們可以使用EXPLAIN關鍵字來查看執行計劃。

EXPLAIN SELECT * FROM employees WHERE first_name='John';

上述SQL查詢語句中，我們使用EXPLAIN關鍵字來查看該查詢語句的執行計劃。執行結果將會返回如下信息：

id: 1
select_type: SIMPLE
table: employees
partitions: NULL
type: ref
possible_keys: first_name
key: first_name
key_len: 13
ref: const
rows: 2
Extra: Using index

從上面的執行結果可以看出，該查詢語句的執行計劃是使用索引，其中索引的鍵名為first_name，該索引可以有效地過濾出符合條件的2條數據。

三、執行計劃詳解

1. id

id是執行計劃中每個操作步驟的標識符，MySQL會按照從小到大的順序為每個操作步驟分配一個唯一的id。

例如，上面示例中的查詢操作有一個id為1。

2. select_type

select_type表示操作類型，MySQL中查詢操作的select_type可能有以下幾種：

• SIMPLE：簡單SELECT查詢，不包含子查詢或UNION操作
• PRIMARY：外層查詢
• SUBQUERY：子查詢
• DERIVED：導出表的子查詢
• UNION：UNION操作的第二個或後面的查詢操作
• UNION RESULT：UNION操作的結果

例如，上面示例中的查詢操作的select_type為SIMPLE。

3. table

table表示操作的數據表名。

例如，上面示例中的查詢操作訪問的是employees表。

4. partitions

partitions表示操作分區表的分區名。

例如，如果查詢操作訪問的是一個分區表，則該欄位會顯示所訪問的分區名。

5. type

type表示查詢操作的訪問方式，MySQL中查詢操作的type可能有以下幾種：

• ALL：全表掃描，該操作不使用索引或切忌使用索引
• index：索引掃描，該操作使用了覆蓋索引，例如SELECT column1,column2 FROM table WHERE column1=’value’，其中column1為索引列。
• range：範圍掃描，該操作使用了表中的一個區間，例如SELECT * FROM table WHERE column1 BETWEEN 1 and ‘value’。
• ref：基於索引的等值匹配，該操作使用了非唯一或唯一的索引來匹配單行數據，例如SELECT * FROM table WHERE column1=’value’，其中column1為索引列。
• eq_ref：基於索引的等值聯接，該操作與ref相似，但是在連接操作時使用的是唯一索引，例如SELECT * FROM table1 JOIN table2 ON table1.column1=table2.column1，其中column1為唯一索引列。
• const：基於常量的查詢，該操作使用了常量表達式，例如SELECT * FROM table WHERE column1=’value’，其中’value’為常量。
• system：基於系統的查詢，此時MySQL只有一行訪問數據，例如內部的mysql這個資料庫查詢操作也可能出現system類型的行為操作。

例如，上面示例中的查詢操作的type為ref。

6. possible_keys

possible_keys表示MySQL可以使用哪些索引來執行查詢操作。

例如，如果一個查詢操作查詢的列在多個索引中都有出現，那麼possible_keys欄位會顯示這些索引。

7. key

key表示MySQL實際使用的索引名。

例如，上面示例中的查詢操作使用的是名為first_name的索引。

8. key_len

key_len表示MySQL在執行查詢操作時使用的索引長度。

例如，上面示例中的查詢操作使用的索引長度為13。

9. ref

ref表示MySQL在執行查詢操作時使用的索引列。

例如，上面示例中的查詢操作使用的索引列為const。

10. rows

rows表示MySQL執行查詢操作時估計需要掃描的行數。該值並不一定正確，但可以幫助我們了解查詢操作的性能瓶頸。

例如，上面示例中的查詢操作估計需要掃描的行數為2。

11. Extra

Extra表示查詢操作的額外信息，可能包括如下幾種：

• Using index：表示MySQL使用了覆蓋索引。在查詢操作中，如果所有需要查詢的列都在一個索引中出現，那麼MySQL可以直接使用該索引進行查詢操作，避免了查詢操作必須要訪問數據表的缺陷，大大提高查詢性能。
• Using where：表示MySQL在查詢操作中使用了WHERE子句中的篩選條件
• Using temporary：表示MySQL在查詢操作中需要使用臨時表來處理查詢結果。在查詢操作中，如果需要進行排序、排序需要使用到filesort演算法，則MySQL需要使用臨時表。
• Using filesort：表示MySQL在查詢操作中使用了文件排序演算法進行數據排序。在查詢操作中，如果需要進行排序、排序沒有使用到覆蓋索引，則MySQL需要使用文件排序演算法。
• Using join buffer：表示MySQL在查詢操作中使用了連接緩存。

例如，上面示例中的查詢操作的Extra為Using index。

四、總結

通過以上對SQL執行計劃的詳解，我們可以了解到SQL執行計劃在資料庫查詢優化中的重要性，同時也能夠清楚地了解如何查看和解釋SQL執行計劃的信息，以及如何根據SQL執行計劃來進行優化SQL查詢語句的性能。讓我們在實際使用SQL查詢語句的過程中，更加靈活地使用SQL執行計劃，提升SQL查詢操作的性能。