MySQL执行计划extra解析

                          MySQL执行计划extra解析

     mysql的执行计划最让人难以捉磨的地方就是extra栏位的提示了，这是由于其他的
栏位没有提供详细的信息，因此多一个栏位来附加额外的信息，以利于用户更好的理解
sql是怎么执行的.以下是一些最常见的出现值:

创建测试对像,并插入一些数据
create table t1 ( a int primary key,b int,c varchar(100));
create table t2 ( a int primary key,b int,c varchar(100));
create index idx_b on t1(b);
create index idx_b on t2(b);

1,extra为NULL

mysql>  explain select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 9980 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

一般这种情况下没有where条件，直接做全表扫描得到sql执行结果.

2,extra 的值是using where

mysql> explain select * from t1 where c='d';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 9980 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

where的列为非索引列，通过使用全表扫描，然后再使用where条件去过滤行.

3,extra的值是  Using index condition

mysql> explain select * from t1 where b>900;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t1    | range | idx_b         | idx_b | 5       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

表示使用索引条件去读取表中的数据，先扫描索引，然后根据索引指向的主健去读取对应的数据

4,extra的值是Using filesort

mysql> explain select * from t1 order by c;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 9980 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

表示语句中有排序操作

5,extra的值是Using temporary

mysql>  explain select c ,count(*) from t1 group by c;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 9980 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

表示使用了临时表或是文件来排取，一般出现在group by,distinct和集合操作语句中.

6,extra的值为Impossible WHERE

mysql> explain select * from t1 where a is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

出现在优化阶段，优化器根据表定义可以判断出where条件根本不可能成立，比如主健不可能为空

7，extra的值 Using join buffer (Block Nested Loop)

mysql> explain
    -> select *
    -> from t1 inner join t2
    -> on (t1.c=t2.c);
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 9980 | NULL                                               |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 9980 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql使用了优化过的nest loop算法，一次读取多个块.