【MySQL】優化—工欲善其事，必先利其器之EXPLAIN

Query-1 :explain select zipcode,firstname,lastname from people;

Query-2 :explain select zipcode from (select * from people a) b;

Query-3 :explain select * from people where zipcode = 100000 union select * from people where zipcode = 200000;

Query-4 :explain select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );

Query-5 :explain select * from people o where exists (select id from people where zipcode = 100000 and id = o.id union select id from people where zipcode = 200000 and id = o.id);

</div>

+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
| id | select_type        | table      | type   | possible_keys   | key     | key_len | ref       | rows | Extra           |
+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
|  1 | PRIMARY            | o          | ALL    | NULL            | NULL    | NULL    | NULL      |    3 | Using where     |
|  2 | DEPENDENT SUBQUERY | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | test.o.id |    1 | Using where     |
|  3 | DEPENDENT UNION    | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | test.o.id |    1 | Using where     |
| NULL | UNION RESULT       | <union2,3> | ALL    | NULL            | NULL    | NULL    | NULL      | NULL | Using temporary |
+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
4 rows in set (0.00 sec)

題外話：有時候MySQL優化器這種太過“聰明” 的做法會導致WHERE條件包含IN()的子查詢語句性能有很大損失。可以參看《高性能MySQL第三版》6.5.1關聯子查詢一節。

• SUBQUERY

子查詢中第一個SELECT語句。

+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL                     |
|  2 | SUBQUERY    | people | index | zipcode       | zipcode | 480     | NULL  |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)

• DEPENDENT SUBQUERY

</ul>

和DEPENDENT UNION相對UNION一樣。見Query-5。

除了上述幾種常見的select_type之外還有一些其他的這里就不一一介紹了，不同MySQL版本也不盡相同。

table

顯示的這一行信息是關于哪一張表的。有時候并不是真正的表名。

Query-7 :explain select * from (select * from (select * from people a) b ) c;

+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  2 | DERIVED     | <derived3> | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  3 | DERIVED     | a          | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
3 rows in set (0.00 sec)

可以看到如果指定了別名就顯示的別名。

N就是id值，指該id值對應的那一步操作的結果。

還有<unionm,n>這種類型，出現在UNION語句中，見 Query-4 。

注意：MySQL對待這些表和普通表一樣，但是這些“臨時表”是沒有任何索引的。

type

type列很重要，是用來說明表與表之間是如何進行關聯操作的，有沒有使用索引。MySQL中“關聯”一詞比一般意義上的要寬泛，MySQL認為任何一次查詢都是一次“關聯”，并不僅僅是一個查詢需要兩張表才叫關聯，所以也可以理解MySQL是如何訪問表的。主要有下面幾種類別。

• const

當確定最多只會有一行匹配的時候，MySQL優化器會在查詢前讀取它而且只讀取一次，因此非常快。const只會用在將常量和主鍵或唯一索引進行比較時，而且是比較所有的索引字段。people表在id上有一個主鍵索引，在(zipcode,firstname,lastname)有一個二級索引。因此 Query-8 的type是const而 Query-9 并不是：

Query-8 :explain select * from people where id=1;

+----+-------------+--------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-----------------+
|  1 | SIMPLE      | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | Using pk access |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-----------------+
1 row in set (0.00 sec)

Query-9 :explain select * from people where zipcode = 100000;

</div>

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | people | ALL  | zipcode       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

注意下面的 Query-10 也不能使用const table，雖然也是主鍵，也只會返回一條結果。

Query-10 :explain select * from people where id >2;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | PRIMARY       | PRIMARY | 8       | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

• system

</ul>

這是const連接類型的一種特例，表僅有一行滿足條件。

Query-11 :explain select * from (select * from people where id = 1 )b;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | people     | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)

已經是一個const table并且只有一條記錄。

• eq_ref

eq_ref類型是除了const外最好的連接類型，它用在一個索引的所有部分被聯接使用并且索引是UNIQUE或PRIMARY KEY。

需要注意InnoDB和MyISAM引擎在這一點上有點差別。InnoDB當數據量比較小的情況type會是All。我們上面創建的people 和 people_car默認都是InnoDB表。

Query-12 :explain select * from people a,people_car b where a.id = b.people_id;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | a     | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

我們創建兩個MyISAM表people2和people_car2試試：

CREATE TABLE people2(
    id bigint auto_increment primary key,
    zipcode char(32) not null default '',
    address varchar(128) not null default '',
    lastname char(64) not null default '',
    firstname char(64) not null default '',
    birthdate char(10) not null default ''
)ENGINE = MyISAM;
CREATE TABLE people_car2(
    people_id bigint,
    plate_number varchar(16) not null default '',
    engine_number varchar(16) not null default '',
    lasttime timestamp
)ENGINE = MyISAM;

Query-13 :explain select * from people2 a,people_car2 b where a.id = b.people_id;

</div>

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL             |    3 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 8       | test.b.people_id |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)

我想這是InnoDB對性能權衡的一個結果。

eq_ref可以用于使用 = 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式。如果關聯所用的索引剛好又是主鍵，那么就會變成更優的const了(測試發現M有SIAM和INNODB結果一樣)：

Query-14 :explain select * from people2 a,people_car2 b where a.id = b.people_id and b.people_id = 1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL        |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

• ref

</ul>

這個類型跟eq_ref不同的是，它用在關聯操作只使用了索引的最左前綴，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>（不等于）操作符的帶索引的列。

為了說明我們重新建立上面的people2和people_car2表，仍然使用MyISAM但是不給id指定primary key。然后我們分別給id和people_id建立非唯一索引。

create index people_id on people2(id);
create index people_id on people_car2(people_id);

然后再執行下面的查詢：

Query-15 :explain select * from peoplex a,people_carx b where a.id = b.people_id and a.id > 2;

MyASIM:
+----+-------------+-------+--------+-------------------+-----------+---------+------------------+------+-----------------------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref              | rows | Extra                 |
+----+-------------+-------+--------+-------------------+-----------+---------+------------------+------+-----------------------+
|  1 | SIMPLE      | b     | range  | people_id         | people_id | 9       | NULL             |    2 | Using index condition |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,people_id | PRIMARY   | 8       | test.b.people_id |    1 | NULL                  |
+----+-------------+-------+--------+-------------------+-----------+---------+------------------+------+-----------------------+
2 rows in set (0.00 sec)
InnoDB:
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | a     | range | PRIMARY       | PRIMARY | 8       | NULL |    1 | Using where                                        |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

Query-16 :explain select * from peoplex a,people_carx b where a.id = b.people_id and a.id = 2;

</div>

MyASIM:
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key       | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY,people_id | PRIMARY   | 8       | const |    1 | NULL  |
|  1 | SIMPLE      | b     | ref   | people_id         | people_id | 9       | const |    1 | NULL  |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
2 rows in set (0.00 sec)
InnoDB:
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL        |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Query-17 :explain select * from peoplex a,people_carx b where a.id = b.people_id;

</div>

MyASIM:
+----+-------------+-------+--------+-------------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | b     | ALL    | people_id         | NULL    | NULL    | NULL             |    3 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,people_id | PRIMARY | 8       | test.b.people_id |    1 | NULL        |
+----+-------------+-------+--------+-------------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)
InnoDB:
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | a     | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

Query-18 :explain select * from peoplex where id = 1;

</div>

MyASIM:
+----+-------------+---------+-------+-------------------+---------+---------+-------+------+-----------------+
| id | select_type | table   | type  | possible_keys     | key     | key_len | ref   | rows | Extra           |
+----+-------------+---------+-------+-------------------+---------+---------+-------+------+-----------------+
|  1 | SIMPLE      | people2 | const | PRIMARY,people_id | PRIMARY | 8       | const |    1 | Using pk access |
+----+-------------+---------+-------+-------------------+---------+---------+-------+------+-----------------+
1 row in set (0.00 sec)
InnoDB:
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-----------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 8       | const |    1 | Using pk access |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-----------------+
1 row in set (0.00 sec)

看上面的Query-15，Query-16和Query-17，Query-18我們發現MyISAM/InnoDB在ref類型上的處理也是有不同策略的。

• fulltext

鏈接是使用全文索引進行的。一般我們用到的索引都是B樹，這里就不舉例說明了。

• ref_or_null

該類型和ref類似。但是MySQL會做一個額外的搜索包含NULL列的操作。在解決子查詢中經常使用該聯接類型的優化。（詳見 這里 )。

Query-19 :mysql> explain select * from people where id = 2 or id is null;

+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Query-20 :mysql> explain select * from people where id = 2 or id is not null;

</div>

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | people | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

注意Query-19/20使用的并不是ref_or_null，而且InnnoDB和M有ASIM表現大致相同（數據量大的情況下有待驗證）。

• index_merger

該聯接類型表示使用了索引合并優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。關于索引合并優化看 這里 。

• unique_subquery

該類型替換了下面形式的IN子查詢的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery是一個索引查找函數，可以完全替換子查詢，效率更高。

• index_subquery

該聯接類型類似于unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range  只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range:

Query-21 :explain select * from people where id = 1 or id = 2;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

注意在我的測試中：發現只有id是主鍵或唯一索引時type才會為range。

explain select * from people where id >1;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

explain select * from people where id in (1,2);

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

這里順便挑剔下MySQL使用相同的range來表示范圍查詢和列表查詢。

我們不是挑剔：這兩種訪問效率是不同的。對于范圍條件查詢，MySQL無法使用范圍列后面的其他索引列了，但是對于“多個等值條件查詢”則沒有這個限制了。 ——出自《高性能MySQL第三版》

• index

該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小。這個類型通常的作用是告訴我們查詢是否使用索引進行排序操作。

Query-22 :explain select * from people order by id;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | people | index | NULL          | PRIMARY | 8       | NULL |    3 | NULL  |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

• ALL

</ul>

最慢的一種方式，即全表掃描。

總的來說：上面幾種連接類型的性能是依次遞減的（system>const），不同的MySQL版本、不同的存儲引擎甚至不同的數據量表現都可能不一樣。

possible_keys

possible_keys列指出MySQL能使用哪個索引在該表中找到行。

key

key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。使用的索引的長度。在不損失精確性的情況下，長度越短越好 。 計算方法： 1. 索引字段的附加信息： 1.1 當索引字段為變長數據類型，比如：varchar，需要有長度信息，需要占用2個字節，為定長數據類型，比如char，int，datetime時，不需要占用字節。 1.2 需要有是否為空的標記，這個標記需要占用1個字節；當字段為not null時，就不需要占用字節了。

1. 同時還需要考慮表所使用的字符集，不同的字符集，gbk編碼的為一個字符2個字節，utf8編碼的一個字符3個字節;

ref

ref列顯示使用哪個列或常數與key一起從表中選擇行。

rows

rows列顯示MySQL認為它執行查詢時必須檢查的行數。注意這是一個預估值。

Extra

Extra是EXPLAIN輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細信息，包含的信息很多，只選擇幾個重點的介紹下。

• Using filesort

MySQL有兩種方式可以生成有序的結果，通過排序操作或者使用索引，當Extra中出現了Using filesort 說明MySQL使用了前者，但注意雖然叫filesort但并不是說明就是用了文件來進行排序，只要可能排序都是在內存里完成的。大部分情況下利用索引排序更快，所以一般這時也要考慮優化查詢了。

• Using temporary 說明使用了臨時表，一般看到它說明查詢需要優化了，就算避免不了臨時表的使用也要盡量避免硬盤臨時表的使用。

• Not exists

MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行， 就不再搜索了。

• Using index

說明查詢是覆蓋了索引的，這是好事情。MySQL直接從索引中過濾不需要的記錄并返回命中的結果。這是MySQL服務層完成的，但無需再回表查詢記錄。

• Using index condition

這是MySQL 5.6出來的新特性，叫做“索引條件推送”。簡單說一點就是MySQL原來在索引上是不能執行如like這樣的操作的，但是現在可以了，這樣減少了不必要的IO操作，但是只能用在二級索引上，詳情點 這里 。

• Using where

使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。

注意：Extra列出現Using where表示MySQL服務器將存儲引擎返回服務層以后再應用WHERE條件過濾。

EXPLAIN的輸出內容基本介紹完了，它還有一個擴展的命令叫做EXPLAIN EXTENDED，主要是結合SHOW WARNINGS命令可以看到一些更多的信息。一個比較有用的是可以看到MySQL優化器重構后的SQL。

Explain extended 選項介紹

讓我們再次驗證一遍MySQL是如何優化不相關子查詢為相關子查詢。

Query-4 :explain extended select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );

+----+--------------------+------------+--------+-----------------+---------+---------+------+------+----------+-----------------+
| id | select_type        | table      | type   | possible_keys   | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+--------+-----------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY            | people     | ALL    | NULL            | NULL    | NULL    | NULL |    3 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | func |    1 |   100.00 | Using where     |
|  3 | DEPENDENT UNION    | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | func |    1 |   100.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | ALL    | NULL            | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+--------+-----------------+---------+---------+------+------+----------+-----------------+
4 rows in set, 5 warnings (0.00 sec)
mysql> show warnings ;                                                                                                                                      +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'zipcode' due to type or collation conversion on field 'zipcode'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Warning | 1739 | Cannot use range access on index 'zipcode' due to type or collation conversion on field 'zipcode'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Warning | 1739 | Cannot use ref access on index 'zipcode' due to type or collation conversion on field 'zipcode'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Warning | 1739 | Cannot use range access on index 'zipcode' due to type or collation conversion on field 'zipcode'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Note    | 1003 | /* select#1 */ select `test`.`people`.`id` AS `id`,`test`.`people`.`zipcode` AS `zipcode`,`test`.`people`.`address` AS `address`,`test`.`people`.`lastname` AS `lastname`,`test`.`people`.`firstname` AS `firstname`,`test`.`people`.`birthdate` AS `birthdate` from `test`.`people` where <in_optimizer>(`test`.`people`.`id`,<exists>(/* select#2 */ select 1 from `test`.`people` where ((`test`.`people`.`zipcode` = 100000) and (<cache>(`test`.`people`.`id`) = `test`.`people`.`id`)) union /* select#3 */ select 1 from `test`.`people` where ((`test`.`people`.`zipcode` = 200000) and (<cache>(`test`.`people`.`id`) = `test`.`people`.`id`)))) |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

看到這里，不知道各位是否意識到我們的帶有zipcode字段的測試都是用了整形，而實際他是一個字符型，我們的 Query-4、Query-5、Query-6 并沒有使用到正確的索引

mysql> explain select * from people where id = (select id from people where zipcode = 100000); 
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL                     |
|  2 | SUBQUERY    | people | index | zipcode       | zipcode | 480     | NULL  |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)
mysql> explain select * from people where id = (select id from people where zipcode = '100000');
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL                     |
|  2 | SUBQUERY    | people | ref   | zipcode       | zipcode | 96      | const |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)

同樣出現在了我們的Warning中

| Warning | 1739 | Cannot use ref access on index 'zipcode' due to type or collation conversion on field 'zipcode'

Explain extended作用

1. 分析select語句的運行效果,除了獲得select語句 使用的索引情況、排序的情況等，還可以在原本explain的基礎上額外的提供一些查詢優化的信息，進一步讓我們了解sql的運行過程。
2. 快速查看是否存在隱式的類型轉換，這個對于索引效率的影響是致命的，一定杜絕。

參考：

correlated subquery

ref_or_null

index_merger

mysql explain 中key_len的計算

Index Condition Pushdown Optimization

MYSQL EXPLAIN語句的extended 選項

MySQL優化—工欲善其事，必先利其器之EXPLAIN

</div>

 本文由用戶 iemk1543 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

 本文地址：http://www.baiduhome.net/lib/view/open1453549563620.html

MySQL 數據庫服務器

相關經驗

• 【MySQL】優化—工欲善其事，必先利其器之EXPLAIN
• MySQL查詢優化-explain
• SublimeText3 初探(工欲善其事,必先利其器)
• MySQL 之 優化
• MySQL 數據庫性能優化之緩存參數優化

相關資訊

• 構建高性能WEB之HTTP首部優化
• 前端性能優化之更平滑的動畫
• 比較全面的MySQL優化參考（下篇）
• 然之發布2.4版本，完善功能優化細節
• 然之2.7版本發布，調整優化細節

相關文檔

•  MySQL 優化
•  MySQL優化
•  mysql優化
•  MySQL 高級優化之 - 理解查詢執行
•  Mysql 查詢優化
•  MySQL 性能優化