熟女嗷嗷叫高潮合集91,亚洲精品涩涩,少妇高潮网,成人AV天堂,97视频在线播放,2024午夜福利,最新操逼视频,三级视频成人

本文來(lái)源：http://8rr.co/S4B7

大家都知道，mysql在執(zhí)行查詢的時(shí)候會(huì)進(jìn)行查詢優(yōu)化。簡(jiǎn)單來(lái)講就是執(zhí)行的時(shí)候先基于成本和規(guī)則優(yōu)化生成執(zhí)行計(jì)劃，然后再按照?qǐng)?zhí)行計(jì)劃執(zhí)行查詢。本文主要介紹EXPLAIN各輸出項(xiàng)的含義，從而幫助大家更好的進(jìn)行sql性能優(yōu)化！

本文主要內(nèi)容是根據(jù)掘金小冊(cè)《從根兒上理解 MySQL》整理而來(lái)。如想詳細(xì)了解，建議購(gòu)買掘金小冊(cè)閱讀。

我們可以在查詢語(yǔ)句前面加上EXPLAIN關(guān)鍵字來(lái)查看這個(gè)查詢的執(zhí)行計(jì)劃。例如

mysql> EXPLAIN SELECT 1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+1 row in set, 1 warning (0.01 sec)

可以看到，執(zhí)行計(jì)劃包含很多輸出列，我們先簡(jiǎn)單過(guò)一下各列的大致作用，后面再進(jìn)行詳細(xì)講解。

列名	描述
id	在一個(gè)大的查詢語(yǔ)句中每個(gè)`SELECT`關(guān)鍵字都對(duì)應(yīng)一個(gè)唯一的id
select_type	`SELECT`關(guān)鍵字對(duì)應(yīng)的那個(gè)查詢的類型
table	表名
partitions	匹配的分區(qū)信息
type	針對(duì)單表的訪問(wèn)方法
possible_keys	可能用到的索引
key	實(shí)際上使用的索引
key_len	實(shí)際使用到的索引長(zhǎng)度
ref	當(dāng)使用索引列等值查詢時(shí)，與索引列進(jìn)行等值匹配的對(duì)象信息
rows	預(yù)估的需要讀取的記錄條數(shù)
filtered	某個(gè)表經(jīng)過(guò)搜索條件過(guò)濾后剩余記錄條數(shù)的百分比
Extra	一些額外的信息

前置相關(guān)知識(shí)點(diǎn)

為了詳細(xì)了解執(zhí)行計(jì)劃各列含義，我們先得了解以下相關(guān)知識(shí)點(diǎn)。

不相關(guān)子查詢

如果子查詢可以單獨(dú)運(yùn)行出結(jié)果，而不依賴于外層查詢，我們把這個(gè)子查詢稱之為不相關(guān)子查詢。

子查詢物化

不直接將不相關(guān)子查詢的結(jié)果集當(dāng)作外層查詢的參數(shù)，而是將該結(jié)果集寫入一個(gè)臨時(shí)表(物化表)里。例如：

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

假設(shè)子查詢物化表的名稱為materialized_table，該物化表存儲(chǔ)的子查詢結(jié)果集的列為m_val。子查詢物化之后可以將表s1和子查詢物化表materialized_table進(jìn)行內(nèi)連接操作，然后獲取對(duì)應(yīng)的查詢結(jié)果。

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

將子查詢轉(zhuǎn)換為semi-join

將子查詢進(jìn)行物化之后再執(zhí)行查詢都會(huì)有建立臨時(shí)表的成本，能不能不進(jìn)行物化操作直接把子查詢轉(zhuǎn)換為連接呢？讓我們重新審視一下上邊的查詢語(yǔ)句：

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

我們可以把這個(gè)查詢理解成：對(duì)于s1表中的某條記錄，如果我們能在s2表（準(zhǔn)確的說(shuō)是執(zhí)行完WHERE s2.key3 = 'a'之后的結(jié)果集）中找到一條或多條符合s2.common_field=s1.key1的記錄，那么該條s1表的記錄就會(huì)被加入到最終的結(jié)果集。這個(gè)過(guò)程其實(shí)和把s1和s2兩個(gè)表連接起來(lái)的效果很像：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = 'a';

這么做唯一的問(wèn)題在于，對(duì)于s1表的某條記錄來(lái)說(shuō)，如果s2表中有多條記錄滿足s1.key1 = s2.common_field這個(gè)條件，那么該記錄會(huì)被多次加入最終的結(jié)果集，因此二者不能認(rèn)為是完全等價(jià)的，因此就有了semi-join(半連接)。將s1表和s2表進(jìn)行半連接的意思就是：對(duì)于s1表的某條記錄來(lái)說(shuō)，我們只關(guān)心在s2表中是否存在與之匹配的記錄，而不關(guān)心具體有多少條記錄與之匹配，最終的結(jié)果集中只保留s1表的記錄。當(dāng)然semi-join是mysql內(nèi)部機(jī)制，無(wú)法直接用在sql語(yǔ)句中。

semi-join實(shí)現(xiàn)機(jī)制

Table pullout （子查詢中的表上拉）

當(dāng)子查詢的查詢列表處只有主鍵或者唯一索引列時(shí)，可以直接把子查詢中的表上拉到外層查詢的FROM子句中，并把子查詢中的搜索條件合并到外層查詢的搜索條件中，比如這個(gè)：

SELECT * FROM s1  WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');

由于key2列是s2表的唯一二級(jí)索引列，所以我們可以直接把s2表上拉到外層查詢的FROM子句中，并且把子查詢中的搜索條件合并到外層查詢的搜索條件中，實(shí)際上就是直接將子查詢優(yōu)化為連接查詢，上拉之后的查詢就是這樣的：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a';

DuplicateWeedout execution strategy （重復(fù)值消除）

比如下面這個(gè)查詢語(yǔ)句：

SELECT * FROM s1  WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

轉(zhuǎn)換為半連接查詢后，s1表中的某條記錄可能在s2表中有多條匹配的記錄，所以該條記錄可能多次被添加到最后的結(jié)果集中。為了消除重復(fù)，我們可以建立一個(gè)臨時(shí)表，比方說(shuō)這個(gè)臨時(shí)表長(zhǎng)這樣：

CREATE TABLE tmp (    id PRIMARY KEY);

這樣在執(zhí)行連接查詢的過(guò)程中，每當(dāng)某條s1表中的記錄要加入結(jié)果集時(shí)，就首先把這條記錄的id值加入到這個(gè)臨時(shí)表里。這種使用臨時(shí)表消除semi-join結(jié)果集中的重復(fù)值的方式稱之為DuplicateWeedout。

LooseScan execution strategy （松散掃描）

比如下面這個(gè)查詢語(yǔ)句：

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');

在子查詢中，對(duì)于s2表的訪問(wèn)可以使用到key1列的索引，而恰好子查詢的查詢列表處就是key1列，這樣在將該查詢轉(zhuǎn)換為半連接查詢后，如果將s2作為驅(qū)動(dòng)表執(zhí)行查詢的話，那么執(zhí)行過(guò)程就是這樣：

如圖所示，在s2表的idx_key1索引中，值為'aa'的二級(jí)索引記錄一共有3條，那么只需要取第一條的值到s1表中查找s1.key3 = 'aa'的記錄，如果能在s1表中找到對(duì)應(yīng)的記錄，那么就把對(duì)應(yīng)的記錄加入到結(jié)果集。這種雖然是掃描索引，但只取值相同的記錄的第一條去做匹配操作的方式稱之為松散掃描。

FirstMatch execution strategy （首次匹配）

FirstMatch是一種最原始的半連接執(zhí)行方式，簡(jiǎn)單來(lái)說(shuō)就是說(shuō)先取一條外層查詢的中的記錄，然后到子查詢的表中尋找符合匹配條件的記錄，如果能找到一條，則將該外層查詢的記錄放入最終的結(jié)果集并且停止查找更多匹配的記錄，如果找不到則把該外層查詢的記錄丟棄掉；然后再開始取下一條外層查詢中的記錄，重復(fù)上邊這個(gè)過(guò)程。

執(zhí)行計(jì)劃詳解

為了詳細(xì)解釋執(zhí)行計(jì)劃各列含義，先建2張示例表s1和s2，它們的表結(jié)構(gòu)完全一樣。

CREATE TABLE s1 (    id INT NOT NULL AUTO_INCREMENT,    key1 VARCHAR(100),    key2 INT,    key3 VARCHAR(100),    key_part1 VARCHAR(100),    key_part2 VARCHAR(100),    key_part3 VARCHAR(100),    common_field VARCHAR(100),    PRIMARY KEY (id),    KEY idx_key1 (key1),    UNIQUE KEY idx_key2 (key2),    KEY idx_key3 (key3),    KEY idx_key_part(key_part1, key_part2, key_part3)) Engine=InnoDB CHARSET=utf8;

table

不論我們的查詢語(yǔ)句有多復(fù)雜，里邊兒包含了多少個(gè)表，到最后也是需要對(duì)每個(gè)表進(jìn)行單表訪問(wèn)的，因此EXPLAIN語(yǔ)句輸出的每條記錄都對(duì)應(yīng)著某個(gè)單表的訪問(wèn)方法。其中的table列代表的就是該表的表名。比如：

mysql> EXPLAIN SELECT * FROM s1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

上面的查詢只涉及單表查詢，因此EXPLAIN只輸出了一條記錄。table列的值是s1，表示該條記錄描述了對(duì)s1表的訪問(wèn)方法。

下邊我們看一下一個(gè)連接查詢的執(zhí)行計(jì)劃：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL                                  ||  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+2 rows in set, 1 warning (0.01 sec)

可以看到，EXPLAIN只輸出了兩條記錄。table列的值是s1和s2，分別表示了對(duì)s1表和s2表的訪問(wèn)方法。

id

大家都知道，查詢語(yǔ)句中一般都會(huì)包含一個(gè)或多個(gè)select關(guān)鍵字?？梢院?jiǎn)單認(rèn)為，查詢語(yǔ)句每出現(xiàn)一個(gè)select關(guān)鍵字，執(zhí)行計(jì)劃中就會(huì)有一個(gè)對(duì)應(yīng)的id值。比如下邊這個(gè)查詢中只有一個(gè)SELECT關(guān)鍵字：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.03 sec)

對(duì)于連接查詢來(lái)說(shuō)，一個(gè)select往往是對(duì)多張表進(jìn)行查詢的，所以在執(zhí)行計(jì)劃中就會(huì)有多條記錄，但是它們的id都是一樣的。其中，出現(xiàn)在前邊的表是驅(qū)動(dòng)表，出現(xiàn)在后邊的表是被驅(qū)動(dòng)表。

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL                                  ||  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+2 rows in set, 1 warning (0.01 sec)

對(duì)于子查詢來(lái)說(shuō)，就可能包含多個(gè)select關(guān)鍵字，每個(gè)select關(guān)鍵字都會(huì)對(duì)應(yīng)一個(gè)唯一的id值。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+|  1 | PRIMARY     | s1    | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 9688 |   100.00 | Using where ||  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9954 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.02 sec)

但是還有一點(diǎn)需要注意：查詢優(yōu)化器可能對(duì)涉及子查詢的查詢語(yǔ)句進(jìn)行重寫，從而轉(zhuǎn)換為連接查詢。此時(shí)執(zhí)行計(jì)劃的id值就是一樣的了。

對(duì)于包含union關(guān)鍵字的查詢來(lái)說(shuō)，除了每個(gè)select關(guān)鍵字對(duì)應(yīng)一個(gè)id值，還會(huì)包含一個(gè)id值為NULL的記錄。這條記錄主要用來(lái)表示將兩次查詢的結(jié)果集進(jìn)行去重的(union all因?yàn)椴恍枰ブ兀詻](méi)有這條記錄)。

mysql> EXPLAIN SELECT * FROM s1  UNION SELECT * FROM s2;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | PRIMARY      | s1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL            ||  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | NULL            || NULL | UNION RESULT | 
       
         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
       +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+3 rows in set, 1 warning (0.00 sec)

select_type

我們已經(jīng)知道，每一個(gè)select關(guān)鍵字都代表一次小查詢，而select_type屬性就是用來(lái)描述當(dāng)前這個(gè)小查詢的含義的。select_type屬性含義(直接用官網(wǎng)英文表示)如下：

名稱	描述
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
UNION RESULT	Result of a UNION
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
DERIVED	Derived table
MATERIALIZED	Materialized subquery
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

SIMPLE

查詢語(yǔ)句中不包含UNION或者子查詢的查詢都算作是SIMPLE類型，比如常見(jiàn)的單表查詢和連接查詢等。

PRIMARY

對(duì)于包含UNION、UNION ALL或者子查詢的大查詢來(lái)說(shuō)，它是由幾個(gè)小查詢組成的，其中最左邊的那個(gè)查詢的select_type值就是PRIMARY，比方說(shuō)：

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | PRIMARY      | s1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL            ||  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | NULL            || NULL | UNION RESULT | 
       
         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
       +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+3 rows in set, 1 warning (0.00 sec)

UNION

對(duì)于包含UNION或者UNION ALL的大查詢來(lái)說(shuō)，它是由幾個(gè)小查詢組成的，其中除了最左邊的那個(gè)小查詢以外，其余的小查詢的select_type值就是UNION。

UNION RESULT

MySQL選擇使用臨時(shí)表來(lái)完成UNION查詢的去重工作，針對(duì)該臨時(shí)表的查詢的select_type就是UNION RESULT。

SUBQUERY

如果包含子查詢的查詢語(yǔ)句不能夠轉(zhuǎn)為對(duì)應(yīng)的semi-join的形式，并且該子查詢是不相關(guān)子查詢，并且查詢優(yōu)化器決定采用將該子查詢物化的方案來(lái)執(zhí)行該子查詢時(shí)，該子查詢的第一個(gè)SELECT關(guān)鍵字代表的那個(gè)查詢的select_type就是SUBQUERY，比如下邊這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+|  1 | PRIMARY     | s1    | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 9688 |   100.00 | Using where ||  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9954 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

DEPENDENT SUBQUERY

如果包含子查詢的查詢語(yǔ)句不能夠轉(zhuǎn)為對(duì)應(yīng)的semi-join的形式，并且該子查詢是相關(guān)子查詢，則該子查詢的第一個(gè)SELECT關(guān)鍵字代表的那個(gè)查詢的select_type就是DEPENDENT SUBQUERY，比如下邊這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';+----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-------------+| id | select_type        | table | partitions | type | possible_keys     | key      | key_len | ref               | rows | filtered | Extra       |+----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-------------+|  1 | PRIMARY            | s1    | NULL       | ALL  | idx_key3          | NULL     | NULL    | NULL              | 9688 |   100.00 | Using where ||  2 | DEPENDENT SUBQUERY | s2    | NULL       | ref  | idx_key2,idx_key1 | idx_key2 | 5       | xiaohaizi.s1.key2 |    1 |    10.00 | Using where |+----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)

DEPENDENT UNION

在包含UNION或者UNION ALL的大查詢中，如果各個(gè)小查詢都依賴于外層查詢的話，那除了最左邊的那個(gè)小查詢之外，其余的小查詢的select_type的值就是DEPENDENT UNION。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+| id | select_type        | table      | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+|  1 | PRIMARY            | s1         | NULL       | ALL  | NULL          | NULL     | NULL    | NULL  | 9688 |   100.00 | Using where              ||  2 | DEPENDENT SUBQUERY | s2         | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |   12 |   100.00 | Using where; Using index ||  3 | DEPENDENT UNION    | s1         | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | Using where; Using index || NULL | UNION RESULT       | 
       
         | NULL       | ALL  | NULL          | NULL     | NULL    | NULL  | NULL |     NULL | Using temporary          |
       +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+4 rows in set, 1 warning (0.03 sec)

DERIVED

對(duì)于采用物化的方式執(zhí)行的包含派生表的查詢，該派生表對(duì)應(yīng)的子查詢的select_type就是DERIVED，比方說(shuō)下邊這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+|  1 | PRIMARY     | 
       
         | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | 9688 |    33.33 | Using where |
       |  2 | DERIVED     | s1         | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9688 |   100.00 | Using index |+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

MATERIALIZED

當(dāng)查詢優(yōu)化器在執(zhí)行包含子查詢的語(yǔ)句時(shí)，選擇將子查詢物化之后與外層查詢進(jìn)行連接查詢時(shí)，該子查詢對(duì)應(yīng)的select_type屬性就是MATERIALIZED，比如下邊這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref               | rows | filtered | Extra       |+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+|  1 | SIMPLE       | s1          | NULL       | ALL    | idx_key1      | NULL       | NULL    | NULL              | 9688 |   100.00 | Using where ||  1 | SIMPLE       | 
       
         | NULL       | eq_ref | 
        
             | 
         
           | 303     | xiaohaizi.s1.key1 |    1 |   100.00 | NULL        |
         
        
       |  2 | MATERIALIZED | s2          | NULL       | index  | idx_key1      | idx_key1   | 303     | NULL              | 9954 |   100.00 | Using index |+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+3 rows in set, 1 warning (0.01 sec)

type

上面提到過(guò)，執(zhí)行計(jì)劃的一條記錄就代表了對(duì)一張表的訪問(wèn)方法，其中的type列就是用描述訪問(wèn)方法的。完整的訪問(wèn)方法如下：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL。

system

當(dāng)表中只有一條記錄并且該表使用的存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)是精確的，比如MyISAM、Memory，那么對(duì)該表的訪問(wèn)方法就是system。

const

根據(jù)主鍵或者唯一二級(jí)索引列與常數(shù)進(jìn)行等值匹配時(shí)，對(duì)單表的訪問(wèn)方法就是const。

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)

eq_ref

在連接查詢時(shí)，如果被驅(qū)動(dòng)表是通過(guò)主鍵或者唯一二級(jí)索引列等值匹配的方式進(jìn)行訪問(wèn)的（如果該主鍵或者唯一二級(jí)索引是聯(lián)合索引的話，所有的索引列都必須進(jìn)行等值比較），則對(duì)該被驅(qū)動(dòng)表的訪問(wèn)方法就是eq_ref。

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL            | 9688 |   100.00 | NULL  ||  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xiaohaizi.s1.id |    1 |   100.00 | NULL  |+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+2 rows in set, 1 warning (0.01 sec)

ref

當(dāng)通過(guò)普通的二級(jí)索引列與常量進(jìn)行等值匹配時(shí)來(lái)查詢某個(gè)表，那么對(duì)該表的訪問(wèn)方法就可能是ref。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.04 sec)

ref_or_null

當(dāng)對(duì)普通二級(jí)索引進(jìn)行等值匹配查詢，該索引列的值也可以是NULL值時(shí)，那么對(duì)該表的訪問(wèn)方法就可能是ref_or_null。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+| id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+|  1 | SIMPLE      | s1    | NULL       | ref_or_null | idx_key1      | idx_key1 | 303     | const |    9 |   100.00 | Using index condition |+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

index_merge

一般情況下對(duì)于某個(gè)表的查詢只能使用到一個(gè)索引，但是某些場(chǎng)景下也可能使用索引合并，此時(shí)的type就是index_merge。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+|  1 | SIMPLE      | s1    | NULL       | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL |   14 |   100.00 | Using union(idx_key1,idx_key3); Using where |+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+1 row in set, 1 warning (0.01 sec)

索引合并

一般情況下，執(zhí)行一個(gè)查詢最多只會(huì)用到一個(gè)索引。但是在特殊情況下也可能會(huì)使用多個(gè)二級(jí)索引，使用這種方式執(zhí)行的查詢稱為index_merge。具體的索引合并算法有下邊三種。

Intersection合并 Intersection翻譯過(guò)來(lái)的意思是交集。這里是說(shuō)某個(gè)查詢可以使用多個(gè)二級(jí)索引，將從多個(gè)二級(jí)索引中查詢到的結(jié)果取交集，比方說(shuō)下邊這個(gè)查詢：
```
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
```
Union合并我們?cè)趯懖樵冋Z(yǔ)句時(shí)經(jīng)常想把既符合某個(gè)搜索條件的記錄取出來(lái)，也把符合另外的某個(gè)搜索條件的記錄取出來(lái)，我們說(shuō)這些不同的搜索條件之間是OR關(guān)系。比如：
```
SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'
```
Intersection是交集的意思，這適用于使用不同索引的搜索條件之間使用AND連接起來(lái)的情況；Union是并集的意思，適用于使用不同索引的搜索條件之間使用OR連接起來(lái)的情況。
Sort-Union合并 Union索引合并的使用條件太苛刻，必須保證各個(gè)二級(jí)索引列在進(jìn)行等值匹配的條件下才可能被用到，比方說(shuō)下邊這個(gè)查詢就無(wú)法使用到Union索引合并：
```
SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
```
我們把上述這種先按照二級(jí)索引記錄的主鍵值進(jìn)行排序，之后按照Union索引合并方式執(zhí)行的方式稱之為Sort-Union索引合并，很顯然，這種Sort-Union索引合并比單純的Union索引合并多了一步對(duì)二級(jí)索引記錄的主鍵值排序的過(guò)程。

unique_subquery

類似于兩表連接中被驅(qū)動(dòng)表的eq_ref訪問(wèn)方法，unique_subquery是針對(duì)在一些包含IN子查詢的查詢語(yǔ)句中，如果查詢優(yōu)化器決定將IN子查詢轉(zhuǎn)換為EXISTS子查詢，而且子查詢可以使用到主鍵進(jìn)行等值匹配的話，那么該子查詢執(zhí)行計(jì)劃的type列的值就是unique_subquery，比如下邊的這個(gè)查詢語(yǔ)句：

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+| id | select_type        | table | partitions | type            | possible_keys    | key     | key_len | ref  | rows | filtered | Extra       |+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+|  1 | PRIMARY            | s1    | NULL       | ALL             | idx_key3         | NULL    | NULL    | NULL | 9688 |   100.00 | Using where ||  2 | DEPENDENT SUBQUERY | s2    | NULL       | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4       | func |    1 |    10.00 | Using where |+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)

index_subquery

index_subquery與unique_subquery類似，只不過(guò)訪問(wèn)子查詢中的表時(shí)使用的是普通的索引，比如這樣：

mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';+----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+| id | select_type        | table | partitions | type           | possible_keys     | key      | key_len | ref  | rows | filtered | Extra       |+----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+|  1 | PRIMARY            | s1    | NULL       | ALL            | idx_key3          | NULL     | NULL    | NULL | 9688 |   100.00 | Using where ||  2 | DEPENDENT SUBQUERY | s2    | NULL       | index_subquery | idx_key1,idx_key3 | idx_key3 | 303     | func |    1 |    10.00 | Using where |+----+--------------------+-------+------------+----------------+-------------------+----------+---------+------+------+----------+-------------+2 rows in set, 2 warnings (0.01 sec)

range

如果使用索引獲取某些范圍區(qū)間的記錄，那么就可能使用到range訪問(wèn)方法，比如下邊的這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |   27 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

index

需要掃描全部的索引記錄時(shí)，該表的訪問(wèn)方法就是index。

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+|  1 | SIMPLE      | s1    | NULL       | index | NULL          | idx_key_part | 909     | NULL | 9688 |    10.00 | Using where; Using index |+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)

ALL

全表掃描

possible_keys和key

possible_keys列表示在某個(gè)查詢語(yǔ)句中，對(duì)某個(gè)表執(zhí)行單表查詢時(shí)可能用到的索引有哪些，key列表示實(shí)際用到的索引有哪些，比方說(shuō)下邊這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra       |+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1,idx_key3 | idx_key3 | 303     | const |    6 |     2.75 | Using where |+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)

key_len

key_len列表示當(dāng)優(yōu)化器決定使用某個(gè)索引執(zhí)行查詢時(shí)，該索引記錄的最大長(zhǎng)度，它是由這三個(gè)部分構(gòu)成的：

對(duì)于使用固定長(zhǎng)度類型的索引列來(lái)說(shuō)，它實(shí)際占用的存儲(chǔ)空間的最大長(zhǎng)度就是該固定值；對(duì)于指定字符集是變長(zhǎng)類型的索引列來(lái)說(shuō)，比如某個(gè)索引列的類型是VARCHAR(100)，使用的字符集是utf8，那么該列實(shí)際占用的最大存儲(chǔ)空間就是100 × 3 = 300個(gè)字節(jié)。
如果該索引列可以存儲(chǔ)NULL值，則key_len比不可以存儲(chǔ)NULL值時(shí)多1個(gè)字節(jié)。
對(duì)于變長(zhǎng)字段來(lái)說(shuō)，都會(huì)有2個(gè)字節(jié)的空間來(lái)存儲(chǔ)該變長(zhǎng)列的實(shí)際長(zhǎng)度。

ref

當(dāng)使用索引列等值匹配的條件去執(zhí)行查詢時(shí)，也就是在訪問(wèn)方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一時(shí)，ref列展示的就是與索引列作等值匹配的具體信息，比如只是一個(gè)常數(shù)或者是某個(gè)列。大家看下邊這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)

rows

如果查詢優(yōu)化器決定使用全表掃描的方式對(duì)某個(gè)表執(zhí)行查詢時(shí)，執(zhí)行計(jì)劃的rows列就代表預(yù)計(jì)需要掃描的行數(shù)，如果使用索引來(lái)執(zhí)行查詢時(shí)，執(zhí)行計(jì)劃的rows列就代表預(yù)計(jì)掃描的索引記錄行數(shù)。比如下邊這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  266 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)

filtered

我們更關(guān)注在連接查詢中驅(qū)動(dòng)表對(duì)應(yīng)的執(zhí)行計(jì)劃記錄的filtered值，因?yàn)檫@直接影響了驅(qū)動(dòng)表的扇出值。在rows樣的情況下，filtered越大，扇出值越小，效率可能也越高。比如：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL              | 9688 |    10.00 | Using where ||  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s1.key1 |    1 |   100.00 | NULL        |+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

從執(zhí)行計(jì)劃中可以看出來(lái)，查詢優(yōu)化器打算把s1當(dāng)作驅(qū)動(dòng)表，s2當(dāng)作被驅(qū)動(dòng)表。我們可以看到驅(qū)動(dòng)表s1表的執(zhí)行計(jì)劃的rows列為9688， filtered列為10.00，這意味著驅(qū)動(dòng)表s1的扇出值就是9688 × 10.00% = 968.8，這說(shuō)明還要對(duì)被驅(qū)動(dòng)表執(zhí)行大約968次查詢。

Extra

Extra是用來(lái)說(shuō)明一些額信息的，從而幫助我們更加準(zhǔn)確的理解查詢。下面我們挑幾個(gè)比較常見(jiàn)的進(jìn)行介紹。

No tables used

當(dāng)查詢語(yǔ)句中沒(méi)有from字句時(shí)會(huì)出現(xiàn)No tables used。

Impossible WHERE

當(dāng)查詢語(yǔ)句中的where字句永遠(yuǎn)為false時(shí)會(huì)出現(xiàn)Impossible WHERE。

No matching min/max row

當(dāng)查詢列表有min()或者max()聚集函數(shù)，但是沒(méi)有匹配到對(duì)應(yīng)的記錄時(shí)會(huì)出現(xiàn)No matching min/max row。

Using index

當(dāng)使用索引覆蓋的時(shí)候，會(huì)出現(xiàn)Using index。

Using index condition

如果查詢的執(zhí)行過(guò)程中使用了索引條件下推(Index Condition Pushdown)，就會(huì)出現(xiàn)Using index condition。例如：

SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

1、先根據(jù)key1 > 'z'這個(gè)條件，定位到二級(jí)索引idx_key1中對(duì)應(yīng)的二級(jí)索引記錄。2、先不回表，而是檢測(cè)是否滿足key1 LIKE '%a'條件，最后再將滿足條件的二級(jí)索引記錄回表。

Using where

當(dāng)使用全表掃描執(zhí)行查詢時(shí)，如果查詢語(yǔ)句包含where條件，就會(huì)出現(xiàn)Using where。當(dāng)使用索引訪問(wèn)執(zhí)行查詢時(shí)，如果where字句包含非索引列字段，也會(huì)出現(xiàn)Using where。

Using join buffer (Block Nested Loop)

在連接查詢執(zhí)行過(guò)程中，當(dāng)被驅(qū)動(dòng)表不能有效的利用索引加快訪問(wèn)速度，MySQL一般會(huì)為其分配一塊名叫join buffer的內(nèi)存塊來(lái)加快查詢速度，也就是我們所講的基于塊的嵌套循環(huán)算法，比如下邊這個(gè)查詢語(yǔ)句：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL                                               ||  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |    10.00 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+2 rows in set, 1 warning (0.03 sec)

Not exists

當(dāng)我們使用左（外）連接時(shí)，如果WHERE子句中包含要求被驅(qū)動(dòng)表的某個(gè)列等于NULL值的搜索條件，而且那個(gè)列又是不允許存儲(chǔ)NULL值的，那么在該表的執(zhí)行計(jì)劃的Extra列就會(huì)提示Not exists額外信息，比如這樣：

mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra                   |+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL     | NULL    | NULL              | 9688 |   100.00 | NULL                    ||  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s1.key1 |    1 |    10.00 | Using where; Not exists |+----+-------------+-------+------------+-

Using intersect(...)、Using union(...)和Using sort_union(...)

如果使用了索引合并執(zhí)行查詢，則會(huì)出現(xiàn)Using intersect(...)或者Using union(...)或者Using sort_union(...)。比如：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND key3 = 'a';+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                           |+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+|  1 | SIMPLE      | s1    | NULL       | index_merge | idx_key1,idx_key3 | idx_key3,idx_key1 | 303,303 | NULL |    1 |   100.00 | Using intersect(idx_key3,idx_key1); Using where |+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+1 row in set, 1 warning (0.01 sec)

Zero limit

當(dāng)limit子句參數(shù)為0時(shí)，就會(huì)出現(xiàn)Zero limit。

Using filesort

有一些情況下對(duì)結(jié)果集中的記錄進(jìn)行排序是可以使用到索引的，比如下邊這個(gè)查詢：

mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | index | NULL          | idx_key1 | 303     | NULL |   10 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+1 row in set, 1 warning (0.03 sec)

但是更多情況下，排序操作無(wú)法使用到索引，而是只能使用文件排序(filesort)。如果排序使用了filesort，那么在Extra列就會(huì)出現(xiàn)Using filesort。

Using temporary

在許多查詢的執(zhí)行過(guò)程中，MySQL可能會(huì)借助臨時(shí)表來(lái)完成一些功能，比如去重、排序之類的，比如我們?cè)趫?zhí)行許多包含DISTINCT、GROUP BY、UNION等子句的查詢過(guò)程中，如果不能有效利用索引來(lái)完成查詢，MySQL很有可能尋求通過(guò)建立內(nèi)部的臨時(shí)表來(lái)執(zhí)行查詢。如果查詢中使用到了內(nèi)部的臨時(shí)表，在執(zhí)行計(jì)劃的Extra列將會(huì)顯示Using temporary提示，比方說(shuō)這樣：

mysql> EXPLAIN SELECT DISTINCT common_field FROM s1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | Using temporary |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+1 row in set, 1 warning (0.00 sec)

執(zhí)行計(jì)劃中出現(xiàn)Using temporary并不是一個(gè)好的征兆，因?yàn)榻⑴c維護(hù)臨時(shí)表要付出很大成本的，所以我們最好能使用索引來(lái)替代掉使用臨時(shí)表。

Start temporary, End temporary

查詢優(yōu)化器會(huì)優(yōu)先嘗試將IN子查詢轉(zhuǎn)換成semi-join，而semi-join又有好多種執(zhí)行策略，當(dāng)執(zhí)行策略為DuplicateWeedout時(shí)，也就是通過(guò)建立臨時(shí)表來(lái)實(shí)現(xiàn)為外層查詢中的記錄進(jìn)行去重操作時(shí)，驅(qū)動(dòng)表查詢執(zhí)行計(jì)劃的Extra列將顯示Start temporary提示，被驅(qū)動(dòng)表查詢執(zhí)行計(jì)劃的Extra列將顯示End temporary提示，就是這樣：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a');+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra                        |+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+|  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key3      | NULL     | NULL    | NULL              | 9954 |    10.00 | Using where; Start temporary ||  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s2.key3 |    1 |   100.00 | End temporary                |+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+2 rows in set, 1 warning (0.00 sec)

LooseScan

在將In子查詢轉(zhuǎn)為semi-join時(shí)，如果采用的是LooseScan執(zhí)行策略，則在驅(qū)動(dòng)表執(zhí)行計(jì)劃的Extra列就是顯示LooseScan提示，比如這樣：

mysql> EXPLAIN SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'z');+----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref               | rows | filtered | Extra                               |+----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+|  1 | SIMPLE      | s2    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL              |  270 |   100.00 | Using where; Using index; LooseScan ||  1 | SIMPLE      | s1    | NULL       | ref   | idx_key3      | idx_key3 | 303     | xiaohaizi.s2.key1 |    1 |   100.00 | NULL                                |+----+-------------+-------+------------+-------+---------------+----------+---------+-------------------+------+----------+-------------------------------------+2 rows in set, 1 warning (0.01 sec)

FirstMatch(tbl_name)

在將In子查詢轉(zhuǎn)為semi-join時(shí)，如果采用的是FirstMatch執(zhí)行策略，則在被驅(qū)動(dòng)表執(zhí)行計(jì)劃的Extra列就是顯示FirstMatch(tbl_name)提示，比如這樣：

mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key1 FROM s2 where s1.key3 = s2.key3);+----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys     | key      | key_len | ref               | rows | filtered | Extra                       |+----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key3          | NULL     | NULL    | NULL              | 9688 |   100.00 | Using where                 ||  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1,idx_key3 | idx_key3 | 303     | xiaohaizi.s1.key3 |    1 |     4.87 | Using where; FirstMatch(s1) |+----+-------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-----------------------------+2 rows in set, 2 warnings (0.00 sec)

   
    
     
      
       
        
       
      
     
    
   
最后免費(fèi)給大家分享50個(gè)Java項(xiàng)目實(shí)戰(zhàn)資料，涵蓋入門、進(jìn)階各個(gè)階段學(xué)習(xí)內(nèi)容，可以說(shuō)非常全面了。大部分視頻還附帶源碼，學(xué)起來(lái)還不費(fèi)勁！

附上截圖。（下面有下載方式）。

項(xiàng)目領(lǐng)取方式：
掃描下方公眾號(hào)回復(fù)：50，
可獲取下載鏈接
???
    
     ?長(zhǎng)按上方二維碼 2 秒
    
    
     回復(fù)「50」即可獲取資料
    
    
     

    
    
     

    
點(diǎn)贊是最大的支持

MySQL執(zhí)行計(jì)劃Explain詳解