mysql explain select_MySQL Explain 详解

【IT168技术文档】

一.语法

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

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

例如:

mysql> explain select * from (select * from ( select * from t3 where id=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> explain select * from t3 where id=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 | |

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

(2). PRIMARY

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

mysql> explain select * from (select * from t3 where id=3952602) a ;

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |

| 2 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(3).UNION

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

mysql> explain select * from t3 where id=3952602 union all select * from t3 ;

+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

| 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

| 2 | UNION | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | |

|NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | |

+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(4).DEPENDENT UNION

UNION中的第二个或后面的SELECT语句，取决于外面的查询

mysql> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3) ;

+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+

| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |

| 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |

| 3 | DEPENDENT UNION | t3 | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index |

|NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | |

+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+