Extra 疑惑

龙雪刚

sakila数据库中actor表，表结构如下：

1. mysql> show create table actor  \G
   
2. *************************** 1. row ***************************
   
3.        Table: actor
   
4. Create Table: CREATE TABLE `actor` (
   
5.   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
   
6.   `first_name` varchar(45) NOT NULL,
   
7.   `last_name` varchar(45) NOT NULL,
   
8.   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   
9.   PRIMARY KEY (`actor_id`),
   
10.   KEY `idx_actor_last_name` (`last_name`)
    
11. ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
    
12. 1 row in set (0.00 sec)

复制代码

1. mysql> select count(*) from actor;
   
2. +----------+
   
3. | count(*) |
   
4. +----------+
   
5. |      200 |
   
6. +----------+

复制代码

1. mysql> select count(*) from actor where last_name = "HOPPER";
   
2. +----------+
   
3. | count(*) |
   
4. +----------+
   
5. |        2 |
   
6. +----------+

复制代码

现在explain 一条SQL explain select last_name from actor where last_name = "HOPPER";

1. mysql> explain select last_name from actor where last_name = "HOPPER";
   
2. +----+-------------+-------+------+---------------------+---------------------+---------+-------+------+--------------------------+
   
3. | id | select_type | table | type | possible_keys       | key                 | key_len | ref   | rows | Extra                    |
   
4. +----+-------------+-------+------+---------------------+---------------------+---------+-------+------+--------------------------+
   
5. |  1 | SIMPLE      | actor | ref  | idx_actor_last_name | idx_actor_last_name | 137     | const |    2 | Using where; Using index |
   
6. +----+-------------+-------+------+---------------------+---------------------+---------+-------+------+--------------------------+

复制代码

问题？Extra列显示Using where; Using index.  这是为什么？
根据我个人的理解，当MySQL服务器在从底层存储引擎接收记录后需要进行“后过滤”（Post-filter）,或者查询未能使用索引，则会出现Using where.
以及，当只需要通过覆盖索引就能取得所需要的数据时才会出现Using index 。

horizonhyg

回复 1# 龙雪刚

Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index

估计是有where条件就会出现这个，我也不清楚。

龙雪刚

自己顶下，求解答

龙雪刚

版主能不能帮忙解释一下？？？谢谢。

justlooks

问题是　你的查询有何意义？我知道你的名字我还问你名字干嘛？
explain select last_name from actor where last_name = "HOPPER";

龙雪刚

回复 5# justlooks

本身查询的确没有意义。但就是这个没意义的查询，给出的explain,无法解释。不能因为没有意义，就忽视问题。


   

cenalulu

Using where 表示你的SQL语句中出现了where过滤条件
Using Index 表示mysql没有去读取原数据，只用了索引数据就得到了结果集

这两条都符合SQL的执行计划，楼主是哪点理解不了？

龙雪刚

回复 7# cenalulu

你理解错了"using where" 了吧。

Using where

A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.




   

cenalulu

帮楼主人肉翻译一下吧：

Using where

A WHERE clause is used to restrict which rows to match against the next table or send to the client. （Extra中出现using where 表示SQL语句中使用了where条件来约束传回客户端的结果集）
Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.  （除非你是有意识的想获取整表的数据，否则extra中没有出现using where表示你的SQL存在的某些错误，同时 join type 中会显示ALL或 INDEX）

简而言之就是，出现Using where表示你过滤了结果集，属于正常现象。
否则就应该引起警觉，因为出现了全表扫描
楼主哪点有疑议？

龙雪刚

回复 9# cenalulu

按照你的说法，就是只要有where条件就Extra必会出现Using where。那么下面这个例又该如何解释？

1. mysql> show create table T_IN \G
   
2. *************************** 1. row ***************************
   
3.        Table: T_IN
   
4. Create Table: CREATE TABLE `T_IN` (
   
5.   `id` int(11) NOT NULL AUTO_INCREMENT,
   
6.   `sex` int(11) NOT NULL,
   
7.   `age` int(11) NOT NULL,
   
8.   `content` varchar(200) DEFAULT NULL,
   
9.   PRIMARY KEY (`id`),
   
10.   KEY `sex_age` (`sex`,`age`),
    
11.   KEY `idx_age` (`age`)
    
12. ) ENGINE=InnoDB AUTO_INCREMENT=262125 DEFAULT CHARSET=utf8

复制代码

1. 
   
2. mysql> analyze local table T_IN;
   
3. +-----------+---------+----------+----------+
   
4. | Table     | Op      | Msg_type | Msg_text |
   
5. +-----------+---------+----------+----------+
   
6. | test.T_IN | analyze | status   | OK       |
   
7. +-----------+---------+----------+----------+
   
8. 1 row in set (0.00 sec)

复制代码

1. 
   
2. mysql> select count(*) from T_IN;
   
3. +----------+
   
4. | count(*) |
   
5. +----------+
   
6. |   131072 |
   
7. +----------+

复制代码

1. mysql> select count(*) from T_IN where age = 21;
   
2. +----------+
   
3. | count(*) |
   
4. +----------+
   
5. |     9871 |
   
6. +----------+

复制代码

1. mysql> explain select age from T_IN where age =21;
   
2. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
   
3. | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
   
4. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
   
5. |  1 | SIMPLE      | T_IN  | ref  | idx_age       | idx_age | 4       | const | 9870 | Using index |
   
6. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

复制代码

1. mysql> explain select * from T_IN where age =21;
   
2. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
   
3. | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
   
4. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
   
5. |  1 | SIMPLE      | T_IN  | ref  | idx_age       | idx_age | 4       | const | 9870 |       |
   
6. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------+

复制代码

同样SQL后面有where,Extra列就没有显示Using where. 不知道是不是我没明白你说的意思。