初識MySQL Explain

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

EXPLAIN SELECT * from user_info WHERE  id < 300;

為了接下來方便演示 EXPLAIN 的使用, 首先我們需要建立兩個測試用的表, 并添加相應的數(shù)據(jù):

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 = 2\G
*************************** 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 查詢的標識符. 每個 SELECT 都會自動分配一個唯一的標識符.

select_type: SELECT 查詢的類型.

table: 查詢的是哪個表

partitions: 匹配的分區(qū)

type: join 類型

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

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

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

rows: 顯示此查詢一共掃描了多少行. 這個是一個估計值.

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

extra: 額外的信息

接下來我們來重點看一下比較重要的幾個字段.

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

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

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

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

DEPENDENT UNION, UNION 中的第二個或后面的查詢語句, 取決于外面的查詢

UNION RESULT, UNION 的結(jié)果

SUBQUERY, 子查詢中的第一個 SELECT

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

最常見的查詢類別應該是 SIMPLE 了, 比如當我們的查詢沒有子查詢, 也沒有 UNION 查詢時, 那么通常就是 SIMPLE 類型, 例如:

mysql> explain select * from user_info where id = 2\G
*************************** 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> EXPLAIN (SELECT * FROM user_info  WHERE id IN (1, 2, 3))
    -> UNION
    -> (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,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

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

type 常用類型
type 常用的取值有:

  1. system: 表中只有一條數(shù)據(jù). 這個類型是特殊的 const 類型.
  2. const: 針對主鍵或唯一索引的等值查詢掃描, 最多只返回一行數(shù)據(jù). const 查詢速度非呈ぃ快, 因為它僅僅讀取一次即可.

例如下面的這個查詢, 它使用了主鍵索引, 因此 type 就是 const 類型的.

mysql> explain select * from user_info where id = 2\G
*************************** 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 查詢, 表示對于前表的每一個結(jié)果, 都只能匹配到后表的一行結(jié)果. 并且查詢的比較操作通常是 =, 查詢效率較高. 例如:

mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
*************************** 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)

index: 表示全索引掃描(full index scan), 和 ALL 類型類似, 只不過 ALL 類型是全表掃描, 而 index 類型則僅僅掃描所有的索引, 而不掃描數(shù)據(jù).

index 類型通常出現(xiàn)在: 所要查詢的數(shù)據(jù)直接在索引樹中就可以獲取到, 而不需要掃描數(shù)據(jù). 當是這種情況時, Extra 字段 會顯示 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 字段恰好是一個索引, 因此我們直接從索引中獲取數(shù)據(jù)就可以滿足查詢的需求了, 而不需要查詢表中的數(shù)據(jù). 因此這樣的情況下, type 的值是 index, 并且 Extra 的值是 Using index.

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

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)

range: 表示使用索引范圍查詢, 通過索引字段范圍獲取表中部分數(shù)據(jù)記錄. 這個類型通常出現(xiàn)在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
當 type 是 range 時, 那么 EXPLAIN 輸出的 ref 字段為 NULL, 并且 key_len 字段是此次查詢中使用到的索引的最長的那個.

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)

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

possible_keys

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

key

此字段是 MySQL 在當前查詢時所真正使用到的索引.

key_len

表示查詢優(yōu)化器使用了索引的字節(jié)數(shù). 這個字段可以評估組合索引是否完全被使用, 或只有最左部分字段被使用到.

key_len 的計算規(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é)

時間類型

DATE: 3字節(jié)

TIMESTAMP: 4字節(jié)

DATETIME: 8字節(jié)

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

我們來舉兩個簡單的栗子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: range
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: NULL
         rows: 5
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

上面的例子是從表 order_info 中查詢指定的內(nèi)容, 而我們從此表的建表語句中可以知道, 表 order_info 有一個聯(lián)合索引:

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

不過此查詢語句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 中, 因為先進行 user_id 的范圍查詢, 而根據(jù) 最左前綴匹配 原則, 當遇到范圍查詢時, 就停止索引的匹配, 因此實際上我們使用到的索引的字段只有 user_id, 因此在 EXPLAIN 中, 顯示的 key_len 為 9. 因為 user_id 字段是 BIGINT, 占用 8 字節(jié), 而 NULL 屬性占用一個字節(jié), 因此總共是 9 個字節(jié). 若我們將user_id 字段改為 BIGINT(20) NOT NULL DEFAULT '0', 則 key_length 應該是8.

rows

rows 也是一個重要的字段. MySQL 查詢優(yōu)化器根據(jù)統(tǒng)計信息, 估算 SQL 要查找到結(jié)果集需要掃描讀取的數(shù)據(jù)行數(shù).
這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好.

Extra

EXplain 中的很多額外的信息會在 Extra 字段顯示, 常見的有以下幾種內(nèi)容:

Using filesort
當 Extra 中有 Using filesort 時, 表示 MySQL 需額外的排序操作, 不能通過索引順序達到排序效果. 一般有 Using filesort, 都建議優(yōu)化去掉, 因為這樣的查詢 CPU 資源消耗大.

mysql> EXPLAIN SELECT * FROM order_info ORDER BY 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; Using filesort
1 row in set, 1 warning (0.00 sec)

我們的索引是

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

但是上面的查詢中根據(jù) product_name 來排序, 因此不能使用索引進行優(yōu)化, 進而會產(chǎn)生 Using filesort.

如果我們將排序依據(jù)改為 ORDER BY user_id, product_name, 那么就不會出現(xiàn) Using filesort 了. 例如:

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)

Using index
"覆蓋索引掃描", 表示查詢在索引樹中就可查找所需數(shù)據(jù), 不用掃描表數(shù)據(jù)文件, 往往說明性能不錯

Using temporary
查詢有使用臨時表, 一般出現(xiàn)于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優(yōu)化.

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市蚓炬，隨后出現(xiàn)的幾起案子松逊，更是在濱河造成了極大的恐慌，老刑警劉巖试吁，帶你破解...
    沈念sama閱讀 217,734評論 6贊 505
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異楼咳，居然都是意外死亡熄捍，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,931評論 3贊 394
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門母怜，熙熙樓的掌柜王于貴愁眉苦臉地迎上來余耽，“玉大人，你說我怎么就攤上這事苹熏〉郑” “怎么了？”我有些...
    開封第一講書人閱讀 164,133評論 0贊 354
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵轨域，是天一觀的道長袱耽。 經(jīng)常有香客問我，道長干发，這世上最難降的妖魔是什么朱巨？ 我笑而不...
    開封第一講書人閱讀 58,532評論 1贊 293
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮枉长，結(jié)果婚禮上冀续，老公的妹妹穿的比我還像新娘琼讽。我一直安慰自己，他們只是感情好洪唐，可當我...
    茶點故事閱讀 67,585評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布钻蹬。 她就那樣靜靜地躺著，像睡著了一般凭需。 火紅的嫁衣襯著肌膚如雪问欠。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 51,462評論 1贊 302
  • 城市分裂傳說
    那天功炮，我揣著相機與錄音溅潜，去河邊找鬼。 笑死薪伏，一個胖子當著我的面吹牛滚澜，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播嫁怀，決...
    沈念sama閱讀 40,262評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼设捐，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了塘淑？” 一聲冷哼從身側(cè)響起萝招，我...
    開封第一講書人閱讀 39,153評論 0贊 276
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎存捺，沒想到半個月后槐沼，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,587評論 1贊 314
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡捌治，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,792評論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年岗钩，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片肖油。...
    茶點故事閱讀 39,919評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡兼吓，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出森枪，到底是詐尸還是另有隱情视搏，我是刑警寧澤，帶...
    沈念sama閱讀 35,635評論 5贊 345
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布县袱，位于F島的核電站浑娜，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏式散。R本人自食惡果不足惜棚愤，卻給世界環(huán)境...
    茶點故事閱讀 41,237評論 3贊 329
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧宛畦，春花似錦瘸洛、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,855評論 0贊 22
  • 一樁弒父案反肋，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至踏施，卻和暖如春石蔗，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背畅形。 一陣腳步聲響...
    開封第一講書人閱讀 32,983評論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來泰國打工养距， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人日熬。 一個月前我還...
    沈念sama閱讀 48,048評論 3贊 370
  • 代替公主和親
    正文 我出身青樓棍厌，卻偏偏與公主長得像，于是被迫代替她去往敵國和親竖席。 傳聞我的和親對象是個殘疾皇子耘纱，可洞房花燭夜當晚...
    茶點故事閱讀 44,864評論 2贊 354