MySQL 性能優(yōu)化–Explain 使用介紹

簡介

mysql 提供了一個(gè) explain 命令, 它可以對 select 語句進(jìn)行分析, 并輸出 select 執(zhí)行的詳細(xì)信息, 以供開發(fā)人員針對性優(yōu)化.
explain 命令用法十分簡單, 在 select 語句前加上 explain 就可以了, 例如:

EXPLAIN?SELECT?*?from?user_info?WHERE??id?<h2>準(zhǔn)備</h2><p>為了接下來方便演示 EXPLAIN 的使用, 首先我們需要建立兩個(gè)測試用的表, 并添加相應(yīng)的數(shù)據(jù):</p><pre class="brush:sql;toolbar:false;">CREATE?TABLE?`user_info`?(  ??`id`???BIGINT(20)??NOT?NULL?AUTO_INCREMENT,  ??`name`?VARCHAR(50)?NOT?NULL?DEFAULT?'',  ??`age`??INT(11)??????????????DEFAULT?NULL,  ??PRIMARY?KEY?(`id`),  ??KEY?`name_index`?(`name`)  )  ??ENGINE?=?InnoDB  ??DEFAULT?CHARSET?=?utf8    INSERT?INTO?user_info?(name,?age)?VALUES?('xys',?20);  INSERT?INTO?user_info?(name,?age)?VALUES?('a',?21);  INSERT?INTO?user_info?(name,?age)?VALUES?('b',?23);  INSERT?INTO?user_info?(name,?age)?VALUES?('c',?50);  INSERT?INTO?user_info?(name,?age)?VALUES?('d',?15);  INSERT?INTO?user_info?(name,?age)?VALUES?('e',?20);  INSERT?INTO?user_info?(name,?age)?VALUES?('f',?21);  INSERT?INTO?user_info?(name,?age)?VALUES?('g',?23);  INSERT?INTO?user_info?(name,?age)?VALUES?('h',?50);  INSERT?INTO?user_info?(name,?age)?VALUES?('i',?15);

CREATE?TABLE?`order_info`?(  ??`id`???????????BIGINT(20)??NOT?NULL?AUTO_INCREMENT,  ??`user_id`??????BIGINT(20)???????????DEFAULT?NULL,  ??`product_name`?VARCHAR(50)?NOT?NULL?DEFAULT?'',  ??`productor`????VARCHAR(30)??????????DEFAULT?NULL,  ??PRIMARY?KEY?(`id`),  ??KEY?`user_product_detail_index`?(`user_id`,?`product_name`,?`productor`)  )  ??ENGINE?=?InnoDB  ??DEFAULT?CHARSET?=?utf8    INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(1,?'p1',?'WHH');  INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(1,?'p2',?'WL');  INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(1,?'p1',?'DX');  INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(2,?'p1',?'WHH');  INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(2,?'p5',?'WL');  INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(3,?'p3',?'MA');  INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(4,?'p1',?'WHH');  INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(6,?'p1',?'WHH');  INSERT?INTO?order_info?(user_id,?product_name,?productor)?VALUES?(9,?'p8',?'TE');

EXPLAIN 輸出格式

EXPLAIN 命令的輸出內(nèi)容大致如下:

mysql>?explain?select?*?from?user_info?where?id?=?2G  ***************************?1.?row?***************************  ???????????id:?1  ??select_type:?SIMPLE  ????????table:?user_info  ???partitions:?NULL  ?????????type:?const  possible_keys:?PRIMARY  ??????????key:?PRIMARY  ??????key_len:?8  ??????????ref:?const  ?????????rows:?1  ?????filtered:?100.00  ????????Extra:?NULL  1?row?in?set,?1?warning?(0.00?sec)

各列的含義如下:

• id: SELECT 查詢的標(biāo)識(shí)符. 每個(gè) SELECT 都會(huì)自動(dòng)分配一個(gè)唯一的標(biāo)識(shí)符.

• select_type: SELECT 查詢的類型.

• table: 查詢的是哪個(gè)表

• partitions: 匹配的分區(qū)

• type: join 類型

• possible_keys: 此次查詢中可能選用的索引

• key: 此次查詢中確切使用到的索引.

• ref: 哪個(gè)字段或常數(shù)與 key 一起被使用

• rows: 顯示此查詢一共掃描了多少行. 這個(gè)是一個(gè)估計(jì)值.

• filtered: 表示此查詢條件所過濾的數(shù)據(jù)的百分比

• extra: 額外的信息

接下來我們來重點(diǎn)看一下比較重要的幾個(gè)字段.

select_type

select_type 表示了查詢的類型, 它的常用取值有:

• SIMPLE, 表示此查詢不包含 UNION 查詢或子查詢

• PRIMARY, 表示此查詢是最外層的查詢

• UNION, 表示此查詢是 UNION 的第二或隨后的查詢

• DEPENDENT UNION, UNION 中的第二個(gè)或后面的查詢語句, 取決于外面的查詢

• UNION RESULT, UNION 的結(jié)果

• SUBQUERY, 子查詢中的第一個(gè) SELECT

• DEPENDENT SUBQUERY: 子查詢中的第一個(gè) SELECT, 取決于外面的查詢. 即子查詢依賴于外層查詢的結(jié)果.

最常見的查詢類別應(yīng)該是 SIMPLE 了, 比如當(dāng)我們的查詢沒有子查詢, 也沒有 UNION 查詢時(shí), 那么通常就是 SIMPLE 類型, 例如:

mysql>?explain?select?*?from?user_info?where?id?=?2G  ***************************?1.?row?***************************  ???????????id:?1  ??select_type:?SIMPLE  ????????table:?user_info  ???partitions:?NULL  ?????????type:?const  possible_keys:?PRIMARY  ??????????key:?PRIMARY  ??????key_len:?8  ??????????ref:?const  ?????????rows:?1  ?????filtered:?100.00  ????????Extra:?NULL  1?row?in?set,?1?warning?(0.00?sec)

如果我們使用了 UNION 查詢, 那么 EXPLAIN 輸出 的結(jié)果類似如下:

mysql&gt;?EXPLAIN?(SELECT?*?FROM?user_info??WHERE?id?IN?(1,?2,?3))  ????-&gt;?UNION  ????-&gt;?(SELECT?*?FROM?user_info?WHERE?id?IN?(3,?4,?5));  +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+  |?id?|?select_type??|?table??????|?partitions?|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?filtered?|?Extra???????????|  +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+  |??1?|?PRIMARY??????|?user_info??|?NULL???????|?range?|?PRIMARY???????|?PRIMARY?|?8???????|?NULL?|????3?|???100.00?|?Using?where?????|  |??2?|?UNION????????|?user_info??|?NULL???????|?range?|?PRIMARY???????|?PRIMARY?|?8???????|?NULL?|????3?|???100.00?|?Using?where?????|  |?NULL?|?UNION?RESULT?|?<union1>?|?NULL???????|?ALL???|?NULL??????????|?NULL????|?NULL????|?NULL?|?NULL?|?????NULL?|?Using?temporary?|  +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+  3?rows?in?set,?1?warning?(0.00?sec)</union1>

table

表示查詢涉及的表或衍生表

type

type 字段比較重要, 它提供了判斷查詢是否高效的重要依據(jù)依據(jù). 通過 type 字段, 我們判斷此次查詢是 全表掃描 還是 索引掃描 等.

type 常用類型

type 常用的取值有:

• system: 表中只有一條數(shù)據(jù). 這個(gè)類型是特殊的 const 類型.

• const: 針對主鍵或唯一索引的等值查詢掃描, 最多只返回一行數(shù)據(jù). const 查詢速度非常快, 因?yàn)樗鼉H僅讀取一次即可.
  例如下面的這個(gè)查詢, 它使用了主鍵索引, 因此 type 就是 const 類型的.

  mysql>?explain?select?*?from?user_info?where?id?=?2G  ***************************?1.?row?***************************  ????????id:?1  select_type:?SIMPLE  ?????table:?user_info  partitions:?NULL  ??????type:?const  possible_keys:?PRIMARY  ???????key:?PRIMARY  ???key_len:?8  ???????ref:?const  ??????rows:?1  ??filtered:?100.00  ?????Extra:?NULL  1?row?in?set,?1?warning?(0.00?sec)

• eq_ref: 此類型通常出現(xiàn)在多表的 join 查詢, ?表示對于前表的每一個(gè)結(jié)果, 都只能匹配到后表的一行結(jié)果. 并且查詢的比較操作通常是 =, 查詢效率較高. 例如:

  mysql>?EXPLAIN?SELECT?*?FROM?user_info,?order_info?WHERE?user_info.id?=?order_info.user_idG  ***************************?1.?row?***************************  ????????id:?1  select_type:?SIMPLE  ?????table:?order_info  partitions:?NULL  ??????type:?index  possible_keys:?user_product_detail_index  ???????key:?user_product_detail_index  ???key_len:?314  ???????ref:?NULL  ??????rows:?9  ??filtered:?100.00  ?????Extra:?Using?where;?Using?index  ***************************?2.?row?***************************  ????????id:?1  select_type:?SIMPLE  ?????table:?user_info  partitions:?NULL  ??????type:?eq_ref  possible_keys:?PRIMARY  ???????key:?PRIMARY  ???key_len:?8  ???????ref:?test.order_info.user_id  ??????rows:?1  ??filtered:?100.00  ?????Extra:?NULL  2?rows?in?set,?1?warning?(0.00?sec)

• ref: 此類型通常出現(xiàn)在多表的 join 查詢, 針對于非唯一或非主鍵索引, 或者是使用了 最左前綴 規(guī)則索引的查詢.
  例如下面這個(gè)例子中, 就使用到了 ref 類型的查詢:

  mysql>?EXPLAIN?SELECT?*?FROM?user_info,?order_info?WHERE?user_info.id?=?order_info.user_id?AND?order_info.user_id?=?5G  ***************************?1.?row?***************************  ????????id:?1  select_type:?SIMPLE  ?????table:?user_info  partitions:?NULL  ??????type:?const  possible_keys:?PRIMARY  ???????key:?PRIMARY  ???key_len:?8  ???????ref:?const  ??????rows:?1  ??filtered:?100.00  ?????Extra:?NULL  ***************************?2.?row?***************************  ????????id:?1  select_type:?SIMPLE  ?????table:?order_info  partitions:?NULL  ??????type:?ref  possible_keys:?user_product_detail_index  ???????key:?user_product_detail_index  ???key_len:?9  ???????ref:?const  ??????rows:?1  ??filtered:?100.00  ?????Extra:?Using?index  2?rows?in?set,?1?warning?(0.01?sec)

• range: 表示使用索引范圍查詢, 通過索引字段范圍獲取表中部分?jǐn)?shù)據(jù)記錄. 這個(gè)類型通常出現(xiàn)在 =, , >, >=, , BETWEEN, IN() 操作中.
  當(dāng) type 是 range 時(shí), 那么 EXPLAIN 輸出的 ref 字段為 NULL, 并且 key_len 字段是此次查詢中使用到的索引的最長的那個(gè).
  例如下面的例子就是一個(gè)范圍查詢:

  mysql>?EXPLAIN?SELECT?*  ?->?????????FROM?user_info  ?->?????????WHERE?id?BETWEEN?2?AND?8?G  ***************************?1.?row?***************************  ????????id:?1  select_type:?SIMPLE  ?????table:?user_info  partitions:?NULL  ??????type:?range  possible_keys:?PRIMARY  ???????key:?PRIMARY  ???key_len:?8  ???????ref:?NULL  ??????rows:?7  ??filtered:?100.00  ?????Extra:?Using?where  1?row?in?set,?1?warning?(0.00?sec)

• index: 表示全索引掃描(full index scan), 和 ALL 類型類似, 只不過 ALL 類型是全表掃描, 而 index 類型則僅僅掃描所有的索引, 而不掃描數(shù)據(jù).
  index 類型通常出現(xiàn)在: 所要查詢的數(shù)據(jù)直接在索引樹中就可以獲取到, 而不需要掃描數(shù)據(jù). 當(dāng)是這種情況時(shí), Extra 字段 會(huì)顯示 Using index.

例如:

mysql>?EXPLAIN?SELECT?name?FROM??user_info?G  ***************************?1.?row?***************************  ???????????id:?1  ??select_type:?SIMPLE  ????????table:?user_info  ???partitions:?NULL  ?????????type:?index  possible_keys:?NULL  ??????????key:?name_index  ??????key_len:?152  ??????????ref:?NULL  ?????????rows:?10  ?????filtered:?100.00  ????????Extra:?Using?index  1?row?in?set,?1?warning?(0.00?sec)

上面的例子中, 我們查詢的 name 字段恰好是一個(gè)索引, 因此我們直接從索引中獲取數(shù)據(jù)就可以滿足查詢的需求了, 而不需要查詢表中的數(shù)據(jù). 因此這樣的情況下, type 的值是 index, 并且 Extra 的值是 Using index.

• ALL: 表示全表掃描, 這個(gè)類型的查詢是性能最差的查詢之一. 通常來說, 我們的查詢不應(yīng)該出現(xiàn) ALL 類型的查詢, 因?yàn)檫@樣的查詢在數(shù)據(jù)量大的情況下, 對數(shù)據(jù)庫的性能是巨大的災(zāi)難. 如一個(gè)查詢是 ALL 類型查詢, 那么一般來說可以對相應(yīng)的字段添加索引來避免.
  下面是一個(gè)全表掃描的例子, 可以看到, 在全表掃描時(shí), possible_keys 和 key 字段都是 NULL, 表示沒有使用到索引, 并且 rows 十分巨大, 因此整個(gè)查詢效率是十分低下的.

  mysql>?EXPLAIN?SELECT?age?FROM??user_info?WHERE?age?=?20?G  ***************************?1.?row?***************************  ????????id:?1  select_type:?SIMPLE  ?????table:?user_info  partitions:?NULL  ??????type:?ALL  possible_keys:?NULL  ???????key:?NULL  ???key_len:?NULL  ???????ref:?NULL  ??????rows:?10  ??filtered:?10.00  ?????Extra:?Using?where  1?row?in?set,?1?warning?(0.00?sec)

  type 類型的性能比較

  通常來說, 不同的 type 類型的性能關(guān)系如下:
  ALL ALL 類型因?yàn)槭侨頀呙? 因此在相同的查詢條件下, 它是速度最慢的.
  而 index 類型的查詢雖然不是全表掃描, 但是它掃描了所有的索引, 因此比 ALL 類型的稍快.
  后面的幾種類型都是利用了索引來查詢數(shù)據(jù), 因此可以過濾部分或大部分?jǐn)?shù)據(jù), 因此查詢效率就比較高了.

possible_keys

possible_keys 表示 MySQL 在查詢時(shí), 能夠使用到的索引. 注意, 即使有些索引在 possible_keys 中出現(xiàn), 但是并不表示此索引會(huì)真正地被 MySQL 使用到. MySQL 在查詢時(shí)具體使用了哪些索引, 由 key 字段決定.

key

此字段是 MySQL 在當(dāng)前查詢時(shí)所真正使用到的索引.

key_len

表示查詢優(yōu)化器使用了索引的字節(jié)數(shù). 這個(gè)字段可以評估組合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的計(jì)算規(guī)則如下:

• 字符串

• char(n): n 字節(jié)長度

• varchar(n): 如果是 utf8 編碼, 則是 3 n + 2字節(jié); 如果是 utf8mb4 編碼, 則是 4 n + 2 字節(jié).

• 數(shù)值類型:

• TINYINT: 1字節(jié)

• SMALLINT: 2字節(jié)

• MEDIUMINT: 3字節(jié)

• INT: 4字節(jié)

• BIGINT: 8字節(jié)

• 時(shí)間類型

• DATE: 3字節(jié)

• TIMESTAMP: 4字節(jié)

• DATETIME: 8字節(jié)

• 字段屬性: NULL 屬性 占用一個(gè)字節(jié). 如果一個(gè)字段是 NOT NULL 的, 則沒有此屬性.

我們來舉兩個(gè)簡單的栗子:

mysql&gt;?EXPLAIN?SELECT?*?FROM?order_info?WHERE?user_id?<p>上面的例子是從表 order_info 中查詢指定的內(nèi)容, 而我們從此表的建表語句中可以知道, 表 order_info 有一個(gè)聯(lián)合索引:</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false;">KEY?`user_product_detail_index`?(`user_id`,?`product_name`,?`productor`)

mysql>?EXPLAIN?SELECT?*?FROM?order_info?WHERE?user_id?=?1?AND?product_name?=?'p1'?G;  ***************************?1.?row?***************************  ???????????id:?1  ??select_type:?SIMPLE  ????????table:?order_info  ???partitions:?NULL  ?????????type:?ref  possible_keys:?user_product_detail_index  ??????????key:?user_product_detail_index  ??????key_len:?161  ??????????ref:?const,const  ?????????rows:?2  ?????filtered:?100.00  ????????Extra:?Using?index  1?row?in?set,?1?warning?(0.00?sec)

KEY?`user_product_detail_index`?(`user_id`,?`product_name`,?`productor`)

mysql>?EXPLAIN?SELECT?*?FROM?order_info?ORDER?BY?user_id,?product_name?G  ***************************?1.?row?***************************  ???????????id:?1  ??select_type:?SIMPLE  ????????table:?order_info  ???partitions:?NULL  ?????????type:?index  possible_keys:?NULL  ??????????key:?user_product_detail_index  ??????key_len:?253  ??????????ref:?NULL  ?????????rows:?9  ?????filtered:?100.00  ????????Extra:?Using?index  1?row?in?set,?1?warning?(0.00?sec)