MySQL EXPLAIN 命令詳解學習

UPDATE?table1  SET?col1?=?X,?col2?=?Y  WHERE?id1?=?9  AND?dt?>=?'2010-01-01';

SELECT?col1,?col2  FROM?table1  WHERE?id1?=?9  AND?dt?>=?'2010-01-01';

mysql>?EXPLAIN?SELECT?*?FROM?inventory?WHERE?item_id?=?16102176G;  ??*********************?1.?row?***********************  ??id:?1  ??select_type:?SIMPLE  ??table:?inventory  ??type:?ALL  ??possible_keys:?NULL  ??key:?NULL  ??key_len:?NULL  ??ref:?NULL  ??rows:?787338  ??Extra:?Using?where

??*********************?1.?row?***********************  ??id:?1  ??select_type:?SIMPLE  ??table:?inventory  ??type:?ref  ??possible_keys:?item_id  ??key:?item_id  ??key_len:?4  ??ref:?const  ??rows:?1  ??Extra:

?*********************?1.?row?***********************  ?id:?1  ?select_type:?SIMPLE  ?table:?p  ?type:?const  ?possible_keys:?PRIMARY  ?key:?PRIMARY  ?key_len:?4  ?ref:?const  ?rows:?1  ?Extra:  ?*********************?2.?row?***********************  ?id:?1  ?select_type:?SIMPLE  ?table:?c  ?type:?ref  ?possible_keys:?parent_id  ?key:?parent_id  ?key_len:?4  ?ref:?const  ?rows:?10  ?Extra:

?mysql>?SHOW?SESSION?STATUS?LIKE?'Handler_read%';  ??+-----------------------+-------+  ??|?Variable_name?????????|?Value?|  ??+-----------------------+-------+  ??|?Handler_read_first????|?0?????|  ??|?Handler_read_key??????|?0?????|?  ??|?Handler_read_last?????|?0?????|  ??|?Handler_read_next?????|?0?????|  ??|?Handler_read_prev?????|?0?????|  ??|?Handler_read_rnd??????|?0?????|  ??|?Handler_read_rnd_next?|?11????|  ??+-----------------------+-------+  ??7?rows?in?set?(0.00?sec)

?*********************?1.?row?***********************  ??id:?1  ??select_type:?SIMPLE  ??table:?p  ??type:?ALL  ??possible_keys:?NULL  ??key:?NULL  ??key_len:?NULL  ??ref:?NULL  ??rows:?160  ??Extra:  ?*********************?2.?row?***********************  ??id:?1  ??select?type:?SIMPLE  ??table:?c  ??type:?ref  ??possible_keys:?PRIMARY,parent_id  ??key:?parent_id  ??key_len:?4  ??ref:?test.p.parent_id  ??rows:?1  ??Extra:?Using?where

?mysql>?SHOW?SESSION?STATUS?LIKE?'Handler_read%';  ?+--------------------------------------+---------+  ?|?Variable_name?|?Value?|  ?+--------------------------------------+---------+  ?|?Handler_read_first?|?1?|  ?|?Handler_read_key?|?164?|  ?|?Handler_read_last?|?0?|  ?|?Handler_read_next?|?107?|  ?|?Handler_read_prev?|?0?|  ?|?Handler_read_rnd?|?0?|  ?|?Handler_read_rnd_next?|?161?|  ?+--------------------------------------+---------+  ?相關的QEP?列還包括key列。

?CREATE?TABLE?`wp_posts`?(  ??`ID`?bigint(20)?unsigned?NOT?NULL?AUTO_INCREMENT,  ??`post_date`?datetime?NOT?NULL?default?'0000-00-00?00:00:00',  ??`post_status`?varchar(20)?NOT?NULL?DEFAULT?'publish'?,  ??`post_type`?varchar(20)?NOT?NULL?DEFAULT?'post',  ??PRIMARY?KEY?(`ID`),  ??KEY?`type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)  ?)?DEFAULT?CHARSET=utf8  ?  ??CREATE?TABLE?`wp_posts`?(  ??`ID`?bigint(20)?unsigned?NOT?NULL?AUTO_INCREMENT,  ??`post_date`?datetime?NOT?NULL?DEFAULT?'0000-00-00?00:00:00',  ??`post_status`?varchar(20)?NOT?NULL?DEFAULT?'publish'?,  ??`post_type`?varchar(20)?NOT?NULL?DEFAULT?'post',  ??PRIMARY?KEY?(`ID`),  ??KEY?`type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)  ?)?DEFAULT?CHARSET=utf8

?mysql>?EXPLAIN?SELECT?ID,?post_title  ?->?FROM?wp_posts  ?->?WHERE?post_type='post'  ?->?AND?post_status='publish'  ?->?AND?post_date?>?'2010-06-01';

?mysql>?EXPLAIN?SELECT?p.*  ?->?FROM?parent?p  ?->?WHERE?p.id?NOT?IN?(SELECT?c.parent_id?FROM?child  ?c)G  ?*********************?1.?row?***********************  ?id:?1  ?select?type:?PRIMARY  ?table:?p  ?type:?ALL  ?possible_keys:?NULL  ?key:?NULL  ?key_len:?NULL  ?ref:?NULL  ?rows:?160  ?Extra:?Using?where  ?*********************?2.?row?***********************  ?id:?2  ?select_type:?DEPENDENT?SUBQUERY  ?table:?c  ?type:?index_subquery  ?possible_keys:?parent_id  ?key:?parent_id  ?key_len:?4  ?ref:?func  ?rows:?1  ?Extra:?Using?index  ?2?rows?in?set?(0.00?sec)    ?EXPLAIN?SELECT?p.*?FROM?parent?p?LEFT?JOIN?child?c?ON?p.id?=?c.parent_id?WHERE?c.child_id?IS?NULLG  ?*********************?1.?row?***********************  ?id:?1  ?select_type:?SIMPLE  ?table:?p  ?type:?ALL  ?possible_keys:?NULL  ?key:?NULL  ?key_len:?NULL  ?ref:?NULL  ?rows:?160  ?Extra:  ?*********************?2.?row?***********************  ?id:?1  ?select_type:?SIMPLE  ?table:?c  ?type:?ref  ?possible_keys:?parent_id  ?key:?parent_id  ?key_len:?4  ?ref:?test.p.id  ?rows:?1  ?Extra:?Using?where;?Using?index;?Not?exists  ?2?rows?in?set?(0.00?sec)