msyql中Explain的用法详解

Explain在mysql的作用我想大家都明白,显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句.

一.语法:explain < table_name >

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

二.explain输出解释,代码如下:

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

1.id

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

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

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

2.select_type

就是select类型,可以有以下几种:

(1) SIMPLE

简单SELECT(不使用UNION或子查询等) 例如:

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

(2).PRIMARY

我的理解是最外层的select.例如:

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

(3).UNION

UNION中的第二个或后面的SELECT语句.例如

1. mysql>explainselect*fromt3whereid=3952602unionallselect*fromt3;
2. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
4. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
5. |1|PRIMARY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1||
6. |2|UNION|t3|ALL|NULL|NULL|NULL|NULL|1000||
7. |NULL|UNIONRESULT|<union1,2>|ALL|NULL|NULL|NULL|NULL|NULL||
8. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(4).DEPENDENT UNION

UNION中的第二个或后面的SELECT语句,取决于外面的查询,代码如下:

1. mysql>explainselect*fromt3whereidin(selectidfromt3whereid=3952602unionallselectidfromt3);
2. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
4. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
5. |1|PRIMARY|t3|ALL|NULL|NULL|NULL|NULL|1000|Usingwhere|
6. |2|DEPENDENTSUBQUERY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1|Usingindex|
7. |3|DEPENDENTUNION|t3|eq_ref|PRIMARY,idx_t3_id|PRIMARY|4|func|1|Usingwhere;Usingindex|
8. |NULL|UNIONRESULT|<union2,3>|ALL|NULL|NULL|NULL|NULL|NULL||
9. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+

(4).UNION RESULT

UNION的结果,代码如下:

1. mysql>explainselect*fromt3whereid=3952602unionallselect*fromt3;
2. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
3. |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
4. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
5. |1|PRIMARY|t3|const|PRIMARY,idx_t3_id|PRIMARY|4|const|1||
6. |2|UNION|t3|ALL|NULL|NULL|NULL|NULL|1000||
7. |NULL|UNIONRESULT|<union1,2>|ALL|NULL|NULL|NULL|NULL|NULL||
8. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(5).SUBQUERY

子查询中的第一个SELECT,代码如下:

上一页 1 2 3 下一页