本文知識(shí)大綱速覽

1. 前言

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

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

查詢(xún)執(zhí)行計(jì)劃信息包含：

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

當(dāng)我們使用EXPLAIN開(kāi)頭，對(duì)查詢(xún)做分析預(yù)測(cè)后，解析的結(jié)果能告訴我們：

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

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

2. 基本介紹

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

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

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

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

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

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

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

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

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

1. id

這是查詢(xún)的標(biāo)識(shí)符，代表查詢(xún)的組執(zhí)行順序。相同的id說(shuō)明在同一執(zhí)行階段。

2. select_type

這個(gè)列標(biāo)明查詢(xún)的類(lèi)型。常見(jiàn)的查詢(xún)類(lèi)型有SIMPLE（簡(jiǎn)單查詢(xún)，不包含子查詢(xún)或者UNION操作）, PRIMARY（主查詢(xún)，外層查詢(xún)）, SUBQUERY（子查詢(xún)，在SELECT或者WHERE列表中）等。

3. table:

輸出結(jié)果集的表。對(duì)于多表查詢(xún)，會(huì)顯示訪問(wèn)每個(gè)表的順序。

4. type:

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

5. possible_keys:

顯示可能用于查詢(xún)的索引。它并不代表這個(gè)索引一定會(huì)被使用。

6. key:

實(shí)際使用的索引。如果為NULL，則沒(méi)有使用索引。

7. key_len

表示索引字段的長(zhǎng)度，如果為NULL，則表示不使用索引。

8. ref:

顯示使用哪個(gè)列或常量與key列進(jìn)行比較。

9. rows:

估計(jì)MySQL需要讀取的行數(shù)來(lái)執(zhí)行查詢(xún)。

10. Extra

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

1. Using where：表示MySQL服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾，這說(shuō)明查詢(xún)中有一個(gè)WHERE子句，并且存儲(chǔ)引擎不會(huì)自動(dòng)過(guò)濾這些行。

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

3. Using filesort：表示MySQL需要進(jìn)行額外的步驟來(lái)找出如何檢索行，因?yàn)樗荒軆H通過(guò)索引來(lái)完成。這通常出現(xiàn)在ORDER BY或GROUP BY查詢(xún)中，當(dāng)無(wú)法使用索引順序來(lái)完成排序時(shí)，會(huì)進(jìn)行額外的排序步驟。

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

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

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

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

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

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

4. 底層原理

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

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

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

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

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

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

以下是一個(gè)復(fù)雜的SQL查詢(xún)示例

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;

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

這個(gè)復(fù)雜的查詢(xún)可以用于分析產(chǎn)品銷(xiāo)售情況，找出銷(xiāo)量最高的產(chǎn)品，并了解它們所屬的類(lèi)別和供應(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. 所有四個(gè)表 products，categories，suppliers，order_details 都參與了查詢(xún)（操作1代表簡(jiǎn)單查詢(xún)沒(méi)有UNION或子查詢(xún)）。
2. 表 p（products）要對(duì)行進(jìn)行全表掃描(類(lèi)型為ALL)，結(jié)果集預(yù)計(jì)的行數(shù)為1000，并且需要額外的操作（temporary和filesort）用來(lái)處理GROUP BY和ORDER BY。
3. 表 c (categories) 和 s (suppliers) 均是使用了ref查找方式對(duì)應(yīng)索引PRIMARY，意味著它們確認(rèn)supplier_id 和 category_id是作為索引存在的，并且每一個(gè)可供該基表使用的聯(lián)接列都參與了索引查找，在聯(lián)接時(shí)每找出一行。
4. 表 od (order_details)使用了索引prod_id，該操作與提供的prod_id匹配行，并在對(duì)結(jié)果進(jìn)行執(zhí)行，并過(guò)濾掉結(jié)果中未滿(mǎn)足WHERE子句條件的記錄。

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

本文知識(shí)圖譜

1. MySQL執(zhí)行計(jì)劃

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

2. 查詢(xún)執(zhí)行計(jì)劃信息

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

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

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

4. 底層原理

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