Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2758325
  • 博文数量: 389
  • 博客积分: 4177
  • 博客等级: 上校
  • 技术积分: 4773
  • 用 户 组: 普通用户
  • 注册时间: 2008-11-16 23:29
文章分类

全部博文(389)

分类: Mysql/postgreSQL

2015-04-06 17:37:14

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

 

 

 

 

阅读(4732) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~