mysql执行计划中的extra列，执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

对于extra列，官网上有这样一段话：

If you want to make your queries as fast as possible, look out for Extra column values of Using filesort and Using temporary, or, in JSON-formatted EXPLAINoutput, for using_filesort and using_temporary_table properties equal to true.

简单来说，如果你想要优化你的查询，那就要注意extra辅助信息中的using filesort和using temporary，这两项非常消耗性能，需要注意。

这个列可以显示的信息非常多，有几十种，常用的有：

1．distinct：在select部分使用了distinc关键字

2．no tables used：不带from字句的查询或者From dual查询

3．使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

4．using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中

5．using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。

6．using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

7．using sort_union，using_union，using intersect，using sort_intersection：

using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集

using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集

using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。

8．using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。

9．using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。不代表没用到索引过滤。

   表示MySQL服务器在存储引擎收到记录后进行"后过滤"（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。这个一般发生在MySQL服务器，而不是存储引擎层。一般发生在不能走索引扫描的情况下或者走索引扫描，但是有些查询条件不在索引当中的情况下。

Mysql体系结构分三层：客户端->服务层->存储引擎

Ø MySQL是插件式的存储引擎，其中存储引擎分很多种。只要实现符合mysql存储引擎的接口，可以开发自己的存储引擎!

Ø 所有跨存储引擎的功能都是在服务层实现的。

Ø MySQL的存储引擎是针对表的，不是针对库的。也就是说在一个数据库中可以使用不同的存储引擎。但是不建议这样做

10．using index condition：查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。

存储引擎在访问索引的时候检查筛选字段在索引中的WHERE条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从Server层下推到Storage Engine层。Storage Engine使用索引过过滤不相关的数据，仅返回符合Index Condition条件的数据给Server层。也是说数据过滤尽可能在Storage Engine层进行，而不是返回所有数据给Server层，然后后再根据WHERE条件进行过滤。

   ICP的一些使用限制：

1) 当SQL需要全表访问时，ICP的优化策略可用于range, ref, eq_ref, ref_or_null类型的访问数据方法 。

2) 支持InnoDB和MyISAM表。

3) ICP只能用于二级索引，不能用于主索引。

4) 并非全部WHERE条件都可以用ICP筛选，如果WHERE条件的字段不在索引列中，还是要读取整表的记录到Server端做WHERE过滤。

5) ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

6) MySQL 5.6版本的不支持分表的ICP功能，5.7版本的开始支持。

7) 当SQL使用覆盖索引时，不支持ICP优化方法。

11．firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个

12．loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息。

执行计划的生成与表结构，表数据量，索引结构，统计信息等等上下文等多种环境有关，无法一概而论，复杂情况另论。

延伸一个很重要的概念，SQL各关键字执行顺序：

（8）SELECT

（9）DISTINCT <select_list>

（1）FROM <left_table>

（3）<join_type> JOIN <right_table>

（2）ON <join_condition>

（4）WHERE <where_condition>--从左往右执行的

（5）GROUP BY <grout_by_list>

（6）WITH {CUTE|ROLLUP}

（7）HAVING <having_condition>

（10）ORDER BY <order_by_list>

（11）LIMIT <limit_number>

每步关键字执行的结果都会形成一个虚表(VT)，编号大的关键字执行的动作都是在编号小的关键字执行结果所得的虚表上进行，以此类推。

一、搭建测试环境

1、版本

V5.7.15

备注：select VERSION();/*mysql命令查看*/

2、测试环境表

t_order(订单表)和t_order_detail(订单详情表)

3、建表脚本

drop table if exists t_order;

drop table if exists t_order_detail;

CREATE TABLE t_order (

  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',

  user_id bigint(20) NOT NULL COMMENT '用户id',

  order_no char(32) NOT NULL COMMENT '订单编号',

  status tinyint NOT NULL COMMENT '订单状态',

  create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

  PRIMARY KEY (id) USING BTREE

)ENGINE=InnoDB AUTO_INCREMENT=1 CHARSET=utf8 COMMENT='订单表';

create table t_order_detail

(

    id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',

    order_no char(32),

    product_name varchar(100),

    cnt int,

    create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

    PRIMARY KEY (id) USING BTREE

)ENGINE=InnoDB AUTO_INCREMENT=1 CHARSET=utf8 COMMENT='订单详情';

4、建立索引

/**alter table t_order drop index idx_userid_order_no_createtime;

alter table t_order_detail drop index idx_orderno_productname;*/

create index idx_userid_order_no_createtime on t_order(user_id,order_no,create_time);

create index idx_orderno_productname on t_order_detail(order_no,product_name);

5、插入测试数据

建立存储过程：

DROP PROCEDURE IF EXISTS test_insertdata;

CREATE PROCEDURE test_insertdata(IN `loopcount` INT)

    LANGUAGE SQL

    NOT DETERMINISTIC

    CONTAINS SQL

    SQL SECURITY DEFINER

    COMMENT ''

BEGIN

    declare v_uuid  varchar(50);

    while loopcount>0 do

        set v_uuid = uuid();

        insert into t_order (user_id,order_no,status) values (rand()*1000,v_uuid,rand()*10);

        insert into t_order_detail(order_no,product_name,cnt) values (v_uuid,v_uuid,rand()*10);

        set loopcount = loopcount -1;

    end while;

END

调用，插入5万数据

CALL test_insertdata(50000);

二、模拟情况

简单通过sql模拟几个Extra中显示内容。

1. Using index 

表示索引覆盖，不会回表查询

EXPLAIN select user_id,order_no,create_time from t_order where user_id =1;

分析：where 全部命中索引，不会回表查询

   Select 全部命中索引，不会回表查询

 2. Using where; Using index

1> EXPLAIN select user_id,order_no,create_time from t_order where order_no ='d56e';

 分析：where 中的刷选条件不是索引的前导列，所以执行计划走全表扫描（ALL），然后在server 层进行过滤数据。

   Select 列全部命中索引，不会回表查询 

2> EXPLAIN select user_id,order_no,create_time from t_order where user_id >1 and user_id <10;

分析：where 一部分命中索引，一部分在server层过滤

   Select 全部命中索引，不会回表查询

 3. NULL

既没有Using index，也没有Using where Using index，也没有using where

EXPLAIN select user_id,order_no,create_time,status from t_order where user_id =1;

分析：where 全部命中索引，在存储引擎层过滤

   Select 拿数据，未命中索引，需要回表查询，二次索引查询

 4. Using where

表示进行了回表查询。

如果使用了过滤，没有索引参加，那就是using where，有索引参加但是最终不需要回表查询，也是using where。

1>EXPLAIN select user_id,order_no,create_time from t_order where status =1;

分析：where 未命中索引，执行计划走全表扫描（ALL），然后在server 层进行过滤数据。

   select 拿数据，命中索引，不需要回表查询

2>EXPLAIN select user_id,order_no,create_time from t_order where user_id =1 and status =10;

分析：where 部分命中索引，然后在server 层进行过滤数据

   Select 拿数据，命中索引，不需要回表查询   

3> EXPLAIN select user_id,order_no,create_time,status from t_order where user_id <92 ;

分析：当需要读取的数据超过一个临界值时，优化器会放弃从索引中读取而改为进行全表扫描，这是为了避免过多的 random disk。

  from ICP优化，在存储引擎过滤，但得到数据大于一个临界值，改成执行计划走全表扫描（ALL），然后在server 层进行过滤数据。

  Select  拿数据，未命中索引，需要回表查询，二次索引查询

 5. Using index condition

表示进行了ICP优化， 有索引参加且需要回表（这里说的回表有可能是为了拿数据，有可能是为了进一步过滤）

1>EXPLAIN select user_id,order_no,create_time,status from t_order where user_id<91;

分析：from ICP优化，在存储引擎过滤了，但得到数据小于临界值

   Select  拿数据，未命中索引，需要回表查询，二次索引查询

 6. Using index condition; Using where

EXPLAIN select user_id,order_no,create_time from t_order where user_id >1 and user_id <10 and status =1;

分析：from ICP优化，在存储引擎过滤了，但得到数据小于临界值；status=1 where 过滤

   Select  拿数据，命中索引，不需要回表查询

7. Using index condition; Using where

EXPLAIN select t.user_id,t.order_no,t.create_time,t.status,p.product_name from t_order t,t_order_detail p

 where t.order_no=p.order_no and t.user_id >1 and t.user_id <10;

分析如下：

t表：from ICP优化，在存储引擎过滤了，但得到数据小于临界值

    Select  拿数据，未命中索引，需要回表查询，二次索引查询

p表：where 全部命中索引，不会回表查询

    Select 全部命中索引，不会回表查询

sql优化还是多运用，才会熟练！

关注我们，精彩属于你