本文知識大綱速覽

1. 前言

MySQL執(zhí)行計劃是MySQL提供的一種查看SQL與數(shù)據(jù)庫交互行為的工具?？梢院苤庇^的幫助開發(fā)者發(fā)現(xiàn)問題并進行SQL優(yōu)化。最常用的是EXPLAIN命令。

使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器的行為，返回MySQL如何使用索引進行搜索和表的連接順序（JOIN；)，并且可以讓我們知道在MySQL解析我們的查詢時，每個動作后的信息。

查詢執(zhí)行計劃信息包含：

1. 表的讀取順序
2. 數(shù)據(jù)讀取操作的操作類型
3. 哪些索引可以被用到
4. 哪些索引被實際使用
5. 表之間的引用
6. 每張表有多少行被優(yōu)化器查詢

當我們使用EXPLAIN開頭，對查詢做分析預(yù)測后，解析的結(jié)果能告訴我們：

1. 哪些部分或者說哪個環(huán)節(jié)消耗的時間多
2. 哪些索引被使用，哪些沒有被使用上
3. 查詢與表的行數(shù)和讀取方式
4. 總之，就是看待查詢各項操作的代價如何

要理解和解釋執(zhí)行計劃，就需要了解它包含的一些關(guān)鍵信息，并可以對其進行適當?shù)膬?yōu)化。例如，在合適的地方建立索引，改變JOIN順序，重寫查詢等，都可以改善查詢的效率。

2. 基本介紹

1. 什么是執(zhí)行計劃

執(zhí)行計劃是MySQL數(shù)據(jù)庫在執(zhí)行SQL查詢時的一個操作步驟集合。它描述了數(shù)據(jù)庫如何執(zhí)行SQL語句，以及如何從數(shù)據(jù)表中檢索或更新數(shù)據(jù)。執(zhí)行計劃包括了多種信息，如數(shù)據(jù)讀取的順序，數(shù)據(jù)過濾的方式，連接表的方式等。

2. 如何查看執(zhí)行計劃

在MySQL中，可以通過在查詢語句前面添加"EXPLAIN"關(guān)鍵字來查看該查詢的執(zhí)行計劃。例如：

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

這將會返回一個表，其中包含了執(zhí)行計劃的詳細信息。

3. 執(zhí)行計劃的組成部分

1. id：查詢的序列號，表示查詢的執(zhí)行順序。
2. select_type：查詢的類型，例如：SIMPLE（簡單查詢），PRIMARY（主查詢），SUBQUERY（子查詢）等。
3. table：輸出結(jié)果集的表。
4. type：連接類型，表示MySQL在表中找到所需行的方式，常見的類型有：ALL（全表掃描），index（全索引掃描），range（范圍掃描）等。
5. possible_keys：可能應(yīng)用的索引。
6. key：實際應(yīng)用的索引，如果為NULL，則沒有使用索引。
7. key_len：表示索引字段的長度，如果為NULL，則表示不使用索引。
8. ref：顯示關(guān)鍵字的比較，是常數(shù)還是字段等。
9. rows：根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出查找所需讀取的行數(shù)。
10. Extra：包含MySQL解決查詢的詳細信息，如：Using index（使用了覆蓋索引），Using where（使用了WHERE過濾器）等。

3. 執(zhí)行計劃的關(guān)鍵元素

1. id

這是查詢的標識符，代表查詢的組執(zhí)行順序。相同的id說明在同一執(zhí)行階段。

2. select_type

這個列標明查詢的類型。常見的查詢類型有SIMPLE（簡單查詢，不包含子查詢或者UNION操作）, PRIMARY（主查詢，外層查詢）, SUBQUERY（子查詢，在SELECT或者WHERE列表中）等。

3. table:

輸出結(jié)果集的表。對于多表查詢，會顯示訪問每個表的順序。

4. type:

這是表示MySQL如何對表的行進行遍歷的類型。各種類型從最好到最壞分別是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

5. possible_keys:

顯示可能用于查詢的索引。它并不代表這個索引一定會被使用。

6. key:

實際使用的索引。如果為NULL，則沒有使用索引。

7. key_len

表示索引字段的長度，如果為NULL，則表示不使用索引。

8. ref:

顯示使用哪個列或常量與key列進行比較。

9. rows:

估計MySQL需要讀取的行數(shù)來執(zhí)行查詢。

10. Extra

提供了關(guān)于MySQL如何解析查詢及查詢是否使用索引等信息。
常見的值有Using where, Using temporary, Using filesort等。這些都是MySQL優(yōu)化器在生成查詢執(zhí)行計劃時的一些策略說明：

1. Using where：表示MySQL服務(wù)器將在存儲引擎檢索行后再進行過濾，這說明查詢中有一個WHERE子句，并且存儲引擎不會自動過濾這些行。

2. Using temporary：表示MySQL需要使用一個臨時表來存儲結(jié)果集，這通常發(fā)生在對不同列的多次排序時。

3. Using filesort：表示MySQL需要進行額外的步驟來找出如何檢索行，因為它不能僅通過索引來完成。這通常出現(xiàn)在ORDER BY或GROUP BY查詢中，當無法使用索引順序來完成排序時，會進行額外的排序步驟。

這些策略都可能對查詢性能產(chǎn)生影響。例如，"Using temporary"和"Using filesort"都可能會導(dǎo)致查詢速度變
慢，尤其是在處理大量數(shù)據(jù)時。

4. Using index：表示使用了覆蓋索引（Covering Index），即所有需要的數(shù)據(jù)都被直接從索引中讀取，而無需從數(shù)據(jù)表中讀取。使用覆蓋索引可以大大提高查詢性能。

5. Using join buffer：表示使用了連接緩沖，這是MySQL優(yōu)化連接操作的一種方式，可以提高連接多張表的效率。

6. Using sort union：表示使用排序合并算法進行OR操作，該算法可以在不同的索引之間進行排序和合并操作。

7. Using index condition：表示使用了索引條件推送（Index Condition Pushdown），即將部分WHERE子句條件在存儲引擎層進行計算，避免了不必要的行掃描和傳輸。

MySQL查詢優(yōu)化器常見的一些策略。這些策略并非越多越好，而是根據(jù)具體查詢和數(shù)據(jù)表結(jié)構(gòu)來選擇最適合的策略。優(yōu)化器的目標是盡可能減少查詢處理所需的磁盤I/O和CPU時間。

4. 底層原理

MySQL的執(zhí)行計劃基于查詢優(yōu)化器，它的主要任務(wù)是找到執(zhí)行SQL查詢的最優(yōu)方式。優(yōu)化器會考慮各種可能的執(zhí)行計劃，比如使用哪個索引，以何種順序連接表，等等，然后根據(jù)估算的成本選擇一個最優(yōu)的執(zhí)行計劃。

當你執(zhí)行一個SQL查詢時，MySQL首先會解析這個查詢，檢查語法是否正確，然后生成一個對應(yīng)的內(nèi)部數(shù)據(jù)結(jié)構(gòu)，我們稱之為解析樹。接著，優(yōu)化器會使用各種規(guī)則和啟發(fā)式方法，對解析樹進行優(yōu)化，生成一個或多個可能的執(zhí)行計劃。每個執(zhí)行計劃都對應(yīng)一個可能的查詢執(zhí)行路徑，包括使用哪個索引，以何種順序連接表，等等。

然后，優(yōu)化器會對每個執(zhí)行計劃進行成本估算，這個成本主要基于讀取數(shù)據(jù)的數(shù)量，也就是IO操作的數(shù)量。此外，優(yōu)化器還會考慮CPU消耗，內(nèi)存消耗等因素。

最后，優(yōu)化器會選擇成本最低的執(zhí)行計劃來執(zhí)行這個查詢。執(zhí)行計劃中的每個步驟都會被轉(zhuǎn)換為一系列的底層操作，比如讀取磁盤上的數(shù)據(jù)，執(zhí)行計算，等等，這些操作最終由MySQL的存儲引擎來執(zhí)行。

當你使用EXPLAIN命令查看執(zhí)行計劃時，你看到的就是優(yōu)化器生成的這個最優(yōu)執(zhí)行計劃的詳細信息。

5. 執(zhí)行計劃示例解讀

以下是一個復(fù)雜的SQL查詢示例

SELECT p.product_name, c.category_name, s.supplier_name, SUM(od.quantity) as total_quantity
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
INNER JOIN suppliers s ON p.supplier_id = s.supplier_id
INNER JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_name, c.category_name, s.supplier_name
HAVING total_quantity > 100
ORDER BY total_quantity DESC;

這個查詢涉及到多個表的連接，并使用了聚合函數(shù)和分組操作。查詢的目標是獲取每個產(chǎn)品的名稱、所屬類別、供應(yīng)商名稱以及總銷量，并按照銷量進行降序排序。
它包含了多個表的連接操作和聚合函數(shù)的使用。通過INNER JOIN語句將四個表(products, categories, suppliers, order_details)連接起來，使用ON子句指定連接的條件。然后通過GROUP BY子句對產(chǎn)品名稱、類別名稱和供應(yīng)商名稱進行分組，使用SUM函數(shù)計算每個組別的總銷量。最后，在HAVING子句中對總銷量進行篩選，只返回銷量大于100的數(shù)據(jù)。最后，使用ORDER BY子句對總銷量進行降序排序。

這個復(fù)雜的查詢可以用于分析產(chǎn)品銷售情況，找出銷量最高的產(chǎn)品，并了解它們所屬的類別和供應(yīng)商。
EXPLAIN 的輸出可能如下

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
1	SIMPLE	c	ref	PRIMARY	PRIMARY	4	db.p.category_id	1	NULL
1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	4	db.p.supplier_id	1	NULL
1	SIMPLE	od	ref	p2	prod_id	4	db.p.product_id	10	Using Where

1. 所有四個表 products，categories，suppliers，order_details 都參與了查詢（操作1代表簡單查詢沒有UNION或子查詢）。
2. 表 p（products）要對行進行全表掃描(類型為ALL)，結(jié)果集預(yù)計的行數(shù)為1000，并且需要額外的操作（temporary和filesort）用來處理GROUP BY和ORDER BY。
3. 表 c (categories) 和 s (suppliers) 均是使用了ref查找方式對應(yīng)索引PRIMARY，意味著它們確認supplier_id 和 category_id是作為索引存在的，并且每一個可供該基表使用的聯(lián)接列都參與了索引查找，在聯(lián)接時每找出一行。
4. 表 od (order_details)使用了索引prod_id，該操作與提供的prod_id匹配行，并在對結(jié)果進行執(zhí)行，并過濾掉結(jié)果中未滿足WHERE子句條件的記錄。

這個計劃告訴我們，這個查詢可能的優(yōu)化：可能要考慮為產(chǎn)量表 products 添加以product_id作為主鍵的索引，用于減少全表掃描的影響。

本文知識圖譜

1. MySQL執(zhí)行計劃

• 用途：查看SQL與數(shù)據(jù)庫交互行為的工具
• 命令：EXPLAIN

2. 查詢執(zhí)行計劃信息

• 表的讀取順序
• 數(shù)據(jù)讀取操作的操作類型
• 可用的索引與實際使用的索引
• 表之間的引用
• 優(yōu)化器查詢的行數(shù)

3 執(zhí)行計劃的關(guān)鍵元素

• id：查詢的執(zhí)行順序
• select_type：查詢的類型
• table：輸出結(jié)果集的表
• type：MySQL在表中找到所需行的方式
• possible_keys：可能應(yīng)用的索引
• key：實際應(yīng)用的索引
• key_len：索引字段的長度
• ref：關(guān)鍵字的比較
• rows：查找所需讀取的行數(shù)
• Extra：MySQL解決查詢的詳細信息

4. 底層原理

• 基于查詢優(yōu)化器
• 優(yōu)化器任務(wù)：找到執(zhí)行SQL查詢的最優(yōu)方式
• 執(zhí)行計劃：優(yōu)化器生成的最優(yōu)執(zhí)行計劃的詳細信息