對于mysql執行計劃的獲取，我們可以通過explain方式來查看，explain方式看似簡單，實際上包含的內容很多，尤其是輸出結果中的type類型列。理解這些不同的類型，對于我們SQL優化舉足輕重，本文僅描述explian輸出結果中的type列，同時給出其演示。有關explian輸出的全描述，可以參考：MySQL EXPLAIN SQL 輸出信息描述
一、EXPLAIN 語句中type列的值
type： ????連接類型 ????system??????????表只有一行????const???????????表最多只有一行匹配，通用用于主鍵或者唯一索引比較時 ????eq_ref??????????每次與之前的表合并行都只在該表讀取一行，這是除了system，const之外最好的一種， ????????????????????特點是使用=，而且索引的所有部分都參與join且索引是主鍵或非空唯一鍵的索引 ????ref?????????????如果每次只匹配少數行，那就是比較好的一種，使用=或，可以是左覆蓋索引或非主鍵或非唯一鍵 ????fulltext????????全文搜索 ????ref_or_null?????與ref類似，但包括NULL ????index_merge?????表示出現了索引合并優化(包括交集，并集以及交集之間的并集)，但不包括跨表和全文索引。 ????????????????????這個比較復雜，目前的理解是合并單表的范圍索引掃描（如果成本估算比普通的range要更優的話） ????unique_subquery?在in子查詢中，就是value?in?(select...)把形如“select?unique_key_column”的子查詢替換。 ????????????????????PS：所以不一定in子句中使用子查詢就是低效的！ ????index_subquery??同上，但把形如”select?non_unique_key_column“的子查詢替換 ????range???????????常數值的范圍????index???????????a.當查詢是索引覆蓋的，即所有數據均可從索引樹獲取的時候（Extra中有Using?Index）； ????????????????????b.以索引順序從索引中查找數據行的全表掃描（無?Using?Index）； ????????????????????c.如果Extra中Using?Index與Using?Where同時出現的話，則是利用索引查找鍵值的意思； ????????????????????d.如單獨出現，則是用讀索引來代替讀行，但不用于查找 ????all?????????????全表掃描
二、連接類型部分示例
1、all--?環境描述 (root@localhost)?[sakila]>?show?variables?like?'version'; +---------------+--------+ |?Variable_name?|?Value??| +---------------+--------+ |?version???????|?5.6.26?| +---------------+--------+ MySQL采取全表遍歷的方式來返回數據行，等同于Oracle的full?table?scan (root@localhost)?[sakila]>?explain?select?count(description)?from?film; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ |?id?|?select_type?|?table?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?Extra?| +----+-------------+-------+------+---------------+------+---------+------+------+-------+ |??1?|?SIMPLE??????|?film??|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|?1000?|?NULL??| +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 2、index MySQL采取索引全掃描的方式來返回數據行，等同于Oracle的full?index?scan (root@localhost)?[sakila]>?explain?select?title?from?film?G ***************************?1.?row?***************************??????????? id:?1?? select_type:?SIMPLE???????? table:?film????????? type:?indexpossible_keys:?NULL ??????????key:?idx_title?????? ??????????key_len:?767?????????? ??????????ref:?NULL????????? ??????????rows:?1000???????? ??????????Extra:?Using?index1?row?in?set?(0.00?sec) 3、??range 索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于between、等的查詢 等同于Oracle的index?range?scan (root@localhost)?[sakila]>?explain?select?*?from?payment?where?customer_id>300?and?customer_id?explain?select?*?from?payment?where?customer_id?in?(200,300,400)G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE???????? ??table:?payment????????? ??type:?rangepossible_keys:?idx_fk_customer_id?????????? ??key:?idx_fk_customer_id?????? ??key_len:?2?????????? ??ref:?NULL????????? ??rows:?86???????? ??Extra:?Using?index?condition1?row?in?set?(0.00?sec) 4、ref 非唯一性索引掃描或者，返回匹配某個單獨值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進行的查找 (root@localhost)?[sakila]>?explain?select?*?from?payment?where?customer_id=305G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE???????? ??table:?payment????????? ??type:?refpossible_keys:?idx_fk_customer_id?????????? ??key:?idx_fk_customer_id?????? ??key_len:?2?????????? ??ref:?const????????? ??rows:?25???????? ??Extra:?1?row?in?set?(0.00?sec) idx_fk_customer_id為表payment上的外鍵索引，且存在多個不不唯一的值，如下查詢 (root@localhost)?[sakila]>?select?customer_id,count(*)?from?payment?group?by?customer_id ????->?limit?2; +-------------+----------+ |?customer_id?|?count(*)?|+-------------+----------+ |???????????1?|???????32?||???????????2?|???????27?| +-------------+----------+--?下面是非唯一前綴索引使用ref的示例 (root@localhost)?[sakila]>?create?index?idx_fisrt_last_name?on?customer(first_name,last_name); Query?OK,?599?rows?affected?(0.09?sec) Records:?599??Duplicates:?0??Warnings:?0(root@localhost)?[sakila]>?select?first_name,count(*)?from?customer?group?by?first_name? ????->?having?count(*)>1?limit?2; +------------+----------+|?first_name?|?count(*)?| +------------+----------+|?JAMIE??????|????????2?||?JESSIE?????|????????2?| +------------+----------+2?rows?in?set?(0.00?sec) (root@localhost)?[sakila]>?explain?select?first_name?from?customer?where?first_name='JESSIE'G ***************************?1.?row?***************************??????????? id:?1??select_type:?SIMPLE???????? table:?customer????????? type:?refpossible_keys:?idx_fisrt_last_name?????????? key:?idx_fisrt_last_name?????? key_len:?137?????????? ref:?const????????? rows:?2???????? Extra:?Using?where;?Using?index1?row?in?set?(0.00?sec) (root@localhost)?[sakila]>?alter?table?customer?drop?index?idx_fisrt_last_name; Query?OK,?599?rows?affected?(0.03?sec) Records:?599??Duplicates:?0??Warnings:?0--下面演示出現在join是ref的示例 (root@localhost)?[sakila]>?explain?select?b.*,a.*?from?payment?a?inner?join????->?customer?b?on?a.customer_id=b.customer_idG ***************************?1.?row?***************************??????????? id:?1?? select_type:?SIMPLE???????? table:?b????????? type:?ALLpossible_keys:?PRIMARY ??????????key:?NULL ??????key_len:?NULL?????????? ??????ref:?NULL????????? ??????rows:?599???????? ??????Extra:?NULL ??????***************************?2.?row?***************************??????????? ??????id:?1?? ??????select_type:?SIMPLE???????? ??????table:?a????????? ??????type:?refpossible_keys:?idx_fk_customer_id?????????? ??????key:?idx_fk_customer_id?????? ??????key_len:?2?????????? ??????ref:?sakila.b.customer_id????????? ??????rows:?13???????? ??????Extra:?NULL2?rows?in?set?(0.01?sec) 5、eq_ref 類似于ref，其差別在于使用的索引為唯一索引，對于每個索引鍵值，表中只有一條記錄與之匹配。 多見于主鍵掃描或者索引唯一掃描。 (root@localhost)?[sakila]>?explain?select?*?from?film?a?join?film_text?b? ????->?on?a.film_id=b.film_id; +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |?id?|?select_type?|?table?|?type???|?possible_keys?|?key?????|?key_len?|?ref?????????|?rows?|?Extra???????| +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |??1?|?SIMPLE??????|?b?????|?ALL????|?PRIMARY???????|?NULL????|?NULL????|?NULL????|?1000?|?NULL????| |??1?|?SIMPLE??????|?a?????|?eq_ref?|?PRIMARY???????|?PRIMARY?|?2???????|?sakila.b.film_id?|????1?|?Using?where?| +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ (root@localhost)?[sakila]>?explain?select?title?from?film?where?film_id=5; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |?id?|?select_type?|?table?|?type??|?possible_keys?|?key?????|?key_len?|?ref???|?rows?|?Extra?| +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+|??1?|?SIMPLE?????? |?film??|?const?|?PRIMAR???|?PRIMARY?|?2???????|?const?|????1?|?NULL??| +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+6、const、system： 當MySQL對查詢某部分進行優化，這個匹配的行的其他列值可以轉換為一個常量來處理。 如將主鍵或者唯一索引置于where列表中，MySQL就能將該查詢轉換為一個常量 (root@localhost)?[sakila]>?create?table?t1(id?int,ename?varchar(20)?unique); Query?OK,?0?rows?affected?(0.05?sec) (root@localhost)?[sakila]>?insert?into?t1?values(1,'robin'),(2,'jack'),(3,'henry'); Query?OK,?3?rows?affected?(0.00?sec) Records:?3??Duplicates:?0??Warnings:?0 (root@localhost)?[sakila]>?explain?select?*?from?(select?*?from?t1?where?ename='robin')x; +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ |?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?????|?t1?????????|?const??|?ename?????????|?ename?|?23??????|?const?|????1?|?NULL??| +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ 2?rows?in?set?(0.00?sec) 7、type=NULL MySQL不用訪問表或者索引就可以直接得到結果 (root@localhost)?[sakila]>?explain?select?sysdate();+----+-------------+-------+------+---------------+------+---------+------+------+----------------+ |?id?|?select_type?|?table?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?Extra??????????| +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ |??1?|?SIMPLE??????|?NULL??|?NULL?|?NULL??????????|?NULL?|?NULL????|?NULL?|?NULL?|?No?tables?used?| +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1?row?in?set?(0.00?sec)</derived2>
對于MySQL執行計劃的獲取，我們可以通過explain方式來查看，explain方式看似簡單，實際上包含的內容很多，尤其是輸出結果中的type類型列。理解這些不同的類型，對于我們SQL優化舉足輕重，本文僅描述explian輸出結果中的type列，同時給出其演示。有關explian輸出的全描述，可以參考：MySQL EXPLAIN SQL 輸出信息描述
一、EXPLAIN 語句中type列的值
type： ????連接類型 ????system??????????表只有一行????const???????????表最多只有一行匹配，通用用于主鍵或者唯一索引比較時 ????eq_ref??????????每次與之前的表合并行都只在該表讀取一行，這是除了system，const之外最好的一種， ????????????????????特點是使用=，而且索引的所有部分都參與join且索引是主鍵或非空唯一鍵的索引 ????ref?????????????如果每次只匹配少數行，那就是比較好的一種，使用=或，可以是左覆蓋索引或非主鍵或非唯一鍵 ????fulltext????????全文搜索 ????ref_or_null?????與ref類似，但包括NULL ????index_merge?????表示出現了索引合并優化(包括交集，并集以及交集之間的并集)，但不包括跨表和全文索引。 ????????????????????這個比較復雜，目前的理解是合并單表的范圍索引掃描（如果成本估算比普通的range要更優的話） ????unique_subquery?在in子查詢中，就是value?in?(select...)把形如“select?unique_key_column”的子查詢替換。 ????????????????????PS：所以不一定in子句中使用子查詢就是低效的！ ????index_subquery??同上，但把形如”select?non_unique_key_column“的子查詢替換 ????range???????????常數值的范圍????index???????????a.當查詢是索引覆蓋的，即所有數據均可從索引樹獲取的時候（Extra中有Using?Index）； ????????????????????b.以索引順序從索引中查找數據行的全表掃描（無?Using?Index）； ????????????????????c.如果Extra中Using?Index與Using?Where同時出現的話，則是利用索引查找鍵值的意思； ????????????????????d.如單獨出現，則是用讀索引來代替讀行，但不用于查找 ????all?????????????全表掃描
二、連接類型部分示例
1、all--?環境描述 (root@localhost)?[sakila]>?show?variables?like?'version'; +---------------+--------+ |?Variable_name?|?Value??| +---------------+--------+ |?version???????|?5.6.26?| +---------------+--------+MySQL采取全表遍歷的方式來返回數據行，等同于Oracle的full?table?scan (root@localhost)?[sakila]>?explain?select?count(description)?from?film; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ |?id?|?select_type?|?table?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?Extra?| +----+-------------+-------+------+---------------+------+---------+------+------+-------+ |??1?|?SIMPLE??????|?film??|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|?1000?|?NULL??| +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 2、index MySQL采取索引全掃描的方式來返回數據行，等同于Oracle的full?index?scan (root@localhost)?[sakila]>?explain?select?title?from?film?G ***************************?1.?row?***************************??????????? id:?1?? select_type:?SIMPLE???????? table:?film????????? type:?indexpossible_keys:?NULL ??????????key:?idx_title?????? ??????????key_len:?767?????????? ??????????ref:?NULL????????? ??????????rows:?1000???????? ??????????Extra:?Using?index1?row?in?set?(0.00?sec) 3、??range 索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于between、等的查詢 等同于Oracle的index?range?scan (root@localhost)?[sakila]>?explain?select?*?from?payment?where?customer_id>300?and?customer_id?explain?select?*?from?payment?where?customer_id?in?(200,300,400)G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE???????? ??table:?payment????????? ??type:?rangepossible_keys:?idx_fk_customer_id?????????? ??key:?idx_fk_customer_id?????? ??key_len:?2?????????? ??ref:?NULL????????? ??rows:?86???????? ??Extra:?Using?index?condition1?row?in?set?(0.00?sec) 4、ref 非唯一性索引掃描或者，返回匹配某個單獨值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進行的查找 (root@localhost)?[sakila]>?explain?select?*?from?payment?where?customer_id=305G ***************************?1.?row?*************************** ???????????id:?1 ??select_type:?SIMPLE???????? ??table:?payment????????? ??type:?refpossible_keys:?idx_fk_customer_id?????????? ??key:?idx_fk_customer_id?????? ??key_len:?2?????????? ??ref:?const????????? ??rows:?25???????? ??Extra:?1?row?in?set?(0.00?sec) idx_fk_customer_id為表payment上的外鍵索引，且存在多個不不唯一的值，如下查詢 (root@localhost)?[sakila]>?select?customer_id,count(*)?from?payment?group?by?customer_id ????->?limit?2; +-------------+----------+ |?customer_id?|?count(*)?|+-------------+----------+ |???????????1?|???????32?||???????????2?|???????27?| +-------------+----------+--?下面是非唯一前綴索引使用ref的示例 (root@localhost)?[sakila]>?create?index?idx_fisrt_last_name?on?customer(first_name,last_name); Query?OK,?599?rows?affected?(0.09?sec) Records:?599??Duplicates:?0??Warnings:?0(root@localhost)?[sakila]>?select?first_name,count(*)?from?customer?group?by?first_name? ????->?having?count(*)>1?limit?2; +------------+----------+|?first_name?|?count(*)?| +------------+----------+|?JAMIE??????|????????2?||?JESSIE?????|????????2?| +------------+----------+2?rows?in?set?(0.00?sec) (root@localhost)?[sakila]>?explain?select?first_name?from?customer?where?first_name='JESSIE'G ***************************?1.?row?***************************??????????? id:?1?? select_type:?SIMPLE???????? table:?customer????????? type:?refpossible_keys:?idx_fisrt_last_name?????????? key:?idx_fisrt_last_name?????? key_len:?137?????????? ref:?const????????? rows:?2???????? Extra:?Using?where;?Using?index1?row?in?set?(0.00?sec) (root@localhost)?[sakila]>?alter?table?customer?drop?index?idx_fisrt_last_name; Query?OK,?599?rows?affected?(0.03?sec) Records:?599??Duplicates:?0??Warnings:?0--下面演示出現在join是ref的示例 (root@localhost)?[sakila]>?explain?select?b.*,a.*?from?payment?a?inner?join???? ->?customer?b?on?a.customer_id=b.customer_idG ***************************?1.?row?***************************??????????? id:?1?? select_type:? SIMPLE???????? table:?b????????? type:?ALLpossible_keys:?PRIMARY ??????????key:?NULL ??????key_len:?NULL?????????? ??????ref:?NULL????????? ??????rows:?599???????? ??????Extra:?NULL ??????***************************?2.?row?***************************??????????? ??????id:?1?? ??????select_type:?SIMPLE???????? ??????table:?a????????? ??????type:?refpossible_keys:?idx_fk_customer_id?????????? ??????key:?idx_fk_customer_id?????? ??????key_len:?2?????????? ??????ref:?sakila.b.customer_id????????? ??????rows:?13???????? ??????Extra:?NULL2?rows?in?set?(0.01?sec) 5、eq_ref 類似于ref，其差別在于使用的索引為唯一索引，對于每個索引鍵值，表中只有一條記錄與之匹配。 多見于主鍵掃描或者索引唯一掃描。 (root@localhost)?[sakila]>?explain?select?*?from?film?a?join?film_text?b? ????->?on?a.film_id=b.film_id; +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |?id?|?select_type?|?table?|?type???|?possible_keys?|?key?????|?key_len?|?ref??????????????|?rows?|?Extra???????| +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |??1?|?SIMPLE??????|?b?????|?ALL????|?PRIMARY???????|?NULL????|?NULL????|?NULL??????????|?1000?|?NULL???| |??1?|?SIMPLE??????|?a?????|?eq_ref?|?PRIMARY???????|?PRIMARY?|?2???????|?sakila.b.film_id?|????1?|?Using?where?| +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ (root@localhost)?[sakila]>?explain?select?title?from?film?where?film_id=5; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |?id?|?select_type?|?table?|?type??|?possible_keys?|?key?????|?key_len?|?ref???|?rows?|?Extra?| +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |??1?|?SIMPLE??????|?film??|?const?|?PRIMARY???????|?PRIMARY?|?2???????|?const?|????1?|?NULL??| +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 6、const、system： 當MySQL對查詢某部分進行優化，這個匹配的行的其他列值可以轉換為一個常量來處理。 如將主鍵或者唯一索引置于where列表中，MySQL就能將該查詢轉換為一個常量 (root@localhost)?[sakila]>?create?table?t1(id?int,ename?varchar(20)?unique); Query?OK,?0?rows?affected?(0.05?sec) (root@localhost)?[sakila]>?insert?into?t1?values(1,'robin'),(2,'jack'),(3,'henry'); Query?OK,?3?rows?affected?(0.00?sec) Records:?3??Duplicates:?0??Warnings:?0 (root@localhost)?[sakila]>?explain?select?*?from?(select?*?from?t1?where?ename='robin')x; +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ |?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?????|?t1?????????|?const??|?ename?????????|?ename?|?2 3??????|?const?|????1?|?NULL??| +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ 2?rows?in?set?(0.00?sec) 7、type=NULL MySQL不用訪問表或者索引就可以直接得到結果 (root@localhost)?[sakila]>?explain?select?sysdate(); +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ |?id?|?select_type?|?table?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?Extra??????????| +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ |??1?|?SIMPLE??????|?NULL??|?NULL?|?NULL??????????|?NULL?|?NULL????|?NULL?|?NULL?|?No?tables?used?| +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1?row?in?set?(0.00?sec)</derived2>
以上就是mysql explain type連接類型示例的內容，更多相關內容請關注php中文網（www.php.cn）！
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
