MySQL Explain详解

核心提示：一.语法 explain < table_name >例如: explain select * from t3 where id=3952602;二.explain输出解释++-+---+---+---+-+-+---+--+---+|id|select_type|table|type|possible_k

一.语法

explain < table_name >

例如: explain select * from t3 where id=3952602;

二.explain输出解释

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+　
　　|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|　
　　+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+　
　　1.id

1.我的理解是SQL执行的顺利的标识,SQL从大到小的执行.

例如:

mysql>explainselect*from(select*from(select*fromt3whereid=3952602)a)b;　
　　+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+　
　　|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|　
　　+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+　
　　|1|PRIMARY||system|NULL|NULL|NULL|NULL|1||　
　　|2|DERIVED||system|NULL|NULL|NULL|NULL|1||　
　　|3|DERIVED|t3|const|PRIMARY,idx_t3_id|PRIMARY|4||1||　
　　+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

很显然这条SQL是从里向外的执行,就是从id=3 向上执行.

2. select_type就是select类型,可以有以下几种

(1) SIMPLE

简单select(不使用union或子查询等) 例如:　

mysql>explainselect*fromt3whereid=3952602;　
　　+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+　
　　|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|　
　　+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+　
　　|1|SIMPLE|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1||　
　　+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+