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算法，一次读取多个块.