mysql执行计划分析
本实验employees数据库从https://launchpad.net/test-db/下载
1.id列
若一个select语句中要连接多个数据表，执行计划会根据连接的表数进行显示，并赋予相同的id。下面示例只有一个select语句，但连接多个表，此时id值不会增加，拥有相同id：
root@localhost:mysql3316.sock 11:58:51 [employees]>explain extended select e.emp_no,e.first_name,s.from_date,s.salary from employees e,salaries s where e.emp_no=s.emp_no limit 10; +----+-------------+-------+------+----------------+---------+---------+--------------------+--------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+----------------+---------+---------+--------------------+--------+----------+-------+ | 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 299290 | 100.00 | NULL | | 1 | SIMPLE | s | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.e.emp_no | 4 | 100.00 | NULL | +----+-------------+-------+------+----------------+---------+---------+--------------------+--------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
相反，下列查询执行计划中，整个查询语句由3个select语句组成，所以执行计划的各记录拥有不同id值：
root@localhost:mysql3316.sock 12:12:35 [employees]>explain select( (select count(*) from employees) + (select count(*) from departments) ) as total_count; +----+-------------+-------------+-------+---------------+-----------+---------+------+--------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+-----------+---------+------+--------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 3 | SUBQUERY | departments | index | NULL | dept_name | 122 | NULL | 9 | Using index | | 2 | SUBQUERY | employees | index | NULL | PRIMARY | 4 | NULL | 299290 | Using index | +----+-------------+-------------+-------+---------------+-----------+---------+------+--------+----------------+ 3 rows in set (0.00 sec)
2.select_type列
（1）simple：
进行不需要union操作或不含子查询的select查询时，相应select_type通常为simple。
root@localhost:mysql3316.sock 12:17:20 [employees]>explain select * from employees; +----+-------------+-----------+------+---------------+------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------+ | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299290 | NULL | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------+ 1 row in set (0.00 sec)
(2)primary:
一个需要union操作或含子查询的select查询计划中，位于最外层的查询select_type即为primary。select_type为primary的查询也只存在1个，位于查询最外侧的select查询即为primary：
root@localhost:mysql3316.sock 12:12:35 [employees]>explain select( (select count(*) from employees) + (select count(*) from departments) ) as total_count; +----+-------------+-------------+-------+---------------+-----------+---------+------+--------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+-----------+---------+------+--------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 3 | SUBQUERY | departments | index | NULL | dept_name | 122 | NULL | 9 | Using index | | 2 | SUBQUERY | employees | index | NULL | PRIMARY | 4 | NULL | 299290 | Using index | +----+-------------+-------------+-------+---------------+-----------+---------+------+--------+----------------+ 3 rows in set (0.00 sec)
（3）union：
由union操作联合而成的select查询中，除第一个外，第二个及以后的所有查询select_tpye都为union。第一个select查询的select_type为derived，它是临时表，用于存储联合（union）后的查询结果：
root@localhost:mysql3316.sock 12:41:57 [employees]>explain select * from ( (select emp_no from employees e1 limit 10) union all (select emp_no from employees e2 limit 10) union all (select emp_no from employees e3 limit 10) )zs; +----+--------------+--------------+-------+---------------+---------+---------+------+--------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+--------------+-------+---------------+---------+---------+------+--------+-----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 30 | NULL | | 2 | DERIVED | e1 | index | NULL | PRIMARY | 4 | NULL | 299290 | Using index | | 3 | UNION | e2 | index | NULL | PRIMARY | 4 | NULL | 299290 | Using index | | 4 | UNION | e3 | index | NULL | PRIMARY | 4 | NULL | 299290 | Using index | | NULL | UNION RESULT | <union2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+--------------+-------+---------------+---------+---------+------+--------+-----------------+ 5 rows in set (0.00 sec)
（4）dependent union:
dependent union出现在union或union all形成的集合查询中，此处的dependent表示union或union all 联合而成的单位查询受外部影响，引用外部值处理内部查询时，dependent关键字就会出现在select_type中。
子查询通常比外部查询先执行，这种处理方式速度非常快。但是对于select_type中出现dependent关键字的查询，由于它依赖外部查询，所以包含dependent关键字的子查询往往比较低效。
root@localhost:mysql3316.sock 16:32:28 [employees]>EXPLAIN SELECT * FROM employees e1 WHERE e1.emp_no IN ( SELECT e2.emp_no FROM employees e2 WHERE e2.first_name='Matt' UNION SELECT e3.emp_no FROM employees e3 WHERE e3.last_name='Matt' ); +----+--------------------+------------+--------+---------------+---------+---------+------+--------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+--------+---------------+---------+---------+------+--------+-----------------+ | 1 | PRIMARY | e1 | ALL | NULL | NULL | NULL | NULL | 299290 | Using where | | 2 | DEPENDENT SUBQUERY | e2 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where | | 3 | DEPENDENT UNION | e3 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+--------+---------------+---------+---------+------+--------+-----------------+ 4 rows in set (0.00 sec)
（5）union result
union result为包含union结果的数据表，mysql中union（distinct）或union all查询会将所有union结果创建为临时表，执行计划中select_type为union result，由于union result不是在实际查询中不是单位查询，所以没有单独的id。
table列显示<union1,2>,表示它是id分别1和2的查询的查询结果的联合。
root@localhost:mysql3316.sock 17:20:53 [employees]>EXPLAIN SELECT emp_no FROM salaries WHERE salary>10000 UNION ALL SELECT emp_no FROM dept_emp WHERE from_date>'2001-01-01'; +----+--------------+------------+------+---------------+------+---------+------+---------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+------+---------+------+---------+-----------------+ | 1 | PRIMARY | salaries | ALL | NULL | NULL | NULL | NULL | 2583437 | Using where | | 2 | UNION | dept_emp | ALL | NULL | NULL | NULL | NULL | 331143 | Using where | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------+---------------+------+---------+------+---------+-----------------+ 3 rows in set (0.00 sec)
（6）subquery
subquery仅指除from子句以外使用的子查询：
root@localhost:mysql3316.sock 17:39:10 [employees]>explain select e.first_name,(select count(*) from dept_emp de,dept_manager dm where dm.dept_no=de.dept_no) as cnt from employees e where e.emp_no=1001; +----+-------------+-------+-------+---------------+---------+---------+----------------------+-------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+----------------------+-------+-----------------------------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | 2 | SUBQUERY | dm | index | dept_no | emp_no | 4 | NULL | 24 | Using index | | 2 | SUBQUERY | de | ref | dept_no | dept_no | 12 | employees.dm.dept_no | 20696 | Using index | +----+-------------+-------+-------+---------------+---------+---------+----------------------+-------+-----------------------------------------------------+ 3 rows in set (0.02 sec)
(7)dependent subquery
若子查询要使用外部select查询中定义的列，则称子查询为dependent subquery：
root@localhost:mysql3316.sock 21:57:15 [employees]>explain select e.first_name,(select count(*) from dept_emp de,dept_manager dm where dm.dept_no=de.dept_no and de.emp_no=e.emp_no) as cnt from employees e where e.first_name='Matt'; +----+--------------------+-------+------+------------------------+---------+---------+----------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+------------------------+---------+---------+----------------------+--------+-------------+ | 1 | PRIMARY | e | ALL | NULL | NULL | NULL | NULL | 299290 | Using where | | 2 | DEPENDENT SUBQUERY | de | ref | PRIMARY,emp_no,dept_no | PRIMARY | 4 | employees.e.emp_no | 1 | Using index | | 2 | DEPENDENT SUBQUERY | dm | ref | dept_no | dept_no | 12 | employees.de.dept_no | 1 | Using index | +----+--------------------+-------+------+------------------------+---------+---------+----------------------+--------+-------------+
（8）derived
从mysql5.6开始根据优化器选项（optimizer_switch),可能会将from子句中的子查询与外部查询综合起来进行优化，derived意味着要为单位select查询的执行结果在内存或磁盘上创建临时数据表。select_type为derived时创建的临时数据表也称为派生表。
root@localhost:mysql3316.sock 22:29:36 [employees]> explain select * from (select de.emp_no from dept_emp de group by de.emp_no) tb,employees e where e.emp_no=tb.emp_no; +----+-------------+------------+--------+------------------------+---------+---------+-----------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+------------------------+---------+---------+-----------+--------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 331143 | NULL | | 1 | PRIMARY | e | eq_ref | PRIMARY | PRIMARY | 4 | tb.emp_no | 1 | NULL | | 2 | DERIVED | de | index | PRIMARY,emp_no,dept_no | emp_no | 4 | NULL | 331143 | Using index | +----+-------------+------------+--------+------------------------+---------+---------+-----------+--------+-------------+
（9）uncacheable subquery
有时可能会因为子查询中有如下一些情形而使缓存本身无法使用，此时，select_type为uncacheable subquery：
子查询有用户变量时
子查询包含有not-deterministic属性大的存储列时
子查询使用每次调用有不同结果值的函数时如,uuid（）或rand()
如下情况调用@status
root@localhost:mysql3316.sock 22:57:17 [employees]>EXPLAIN SELECT * FROM employees e WHERE e.emp_no = (select @status from dept_emp de where de.dept_no='d005'); +----+----------------------+-------+------+---------------+---------+---------+-------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+----------------------+-------+------+---------------+---------+---------+-------+--------+--------------------------+ | 1 | PRIMARY | e | ALL | NULL | NULL | NULL | NULL | 299290 | Using where | | 2 | UNCACHEABLE SUBQUERY | de | ref | dept_no | dept_no | 12 | const | 149978 | Using where; Using index | +----+----------------------+-------+------+---------------+---------+---------+-------+--------+--------------------------+
（10）uncacheable union是由uncacheable和union两个关键字的属性混合而成的select_tpye。
（11）materialized是mysql5.6开始引用的select_tpye,主要用于优化from子句或in（subquery)查询中的子句。
处理时会先进行优化处理，即子查询内容具体化（materialized）为临时表，然后再将临时表与employees数据表连接起来。
root@localhost:mysql3316.sock 23:13:19 [employees]>explain select * from employees e where e.emp_no in (select emp_no from salaries where salary between 100 and 1000); +----+--------------+-------------+--------+----------------+------------+---------+--------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+----------------+------------+---------+--------------------+---------+-------------+ | 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 299290 | Using where | | 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 4 | employees.e.emp_no | 1 | NULL | | 2 | MATERIALIZED | salaries | ALL | PRIMARY,emp_no | NULL | NULL | NULL | 2583437 | Using where | +----+--------------+-------------+--------+----------------+------------+---------+--------------------+---------+-------------+
(12)insert、update、delete
其实，无论select_type是否为insert、update、delete都不怎么重要。只要可以查看执行计划，这本身就非常有用。
root@localhost:mysql3316.sock 09:36:05 [employees]>explain insert into employees values(1,'2016-08-04','zhangshuo','zhangjie','zhangsanfeng','2016-08-05'); +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.03 sec) root@localhost:mysql3316.sock 09:36:16 [employees]>explain update employees set gender='F' where first_name='Matt'; +----+-------------+-----------+-------+---------------+---------+---------+------+--------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+---------+---------+------+--------+------------------------------+ | 1 | SIMPLE | employees | index | NULL | PRIMARY | 4 | NULL | 299290 | Using where; Using temporary | +----+-------------+-----------+-------+---------------+---------+---------+------+--------+------------------------------+ 1 row in set (0.02 sec) root@localhost:mysql3316.sock 09:42:22 [employees]>explain delete from employees where first_name='Matt'; +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299290 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.04 sec)
3.table列
table列中经常出现类似<derived N>或<union M,N>它表示该表为临时表。<>带有数字，它是select查询的id。
root@localhost:mysql3316.sock 11:00:32 [employees]>explain select * from (select de.emp_no from dept_emp de group by de.emp_no) tb,employees e where e.emp_no=tb.emp_no; +----+-------------+------------+--------+------------------------+---------+---------+-----------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+------------------------+---------+---------+-----------+--------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 331143 | NULL | | 1 | PRIMARY | e | eq_ref | PRIMARY | PRIMARY | 4 | tb.emp_no | 1 | NULL | | 2 | DERIVED | de | index | PRIMARY,emp_no,dept_no | emp_no | 4 | NULL | 331143 | Using index | +----+-------------+------------+--------+------------------------+---------+---------+-----------+--------+-------------+
4.type列 #进行查询优化时，非常重要的一步是查看索引是否使用高效，所以必须查看type列。
type列的12个值除ALL之外，所有的访问都走索引。一个select只能使用一种。除index_merge外其他方法只能使用一种索引。
（1）system
访问一条或空表时，使用的方法称为“system”，只适用于myisam和memory数据表。如果是innodb表通常是all或index。实际查询几乎看不到。
（2）const
不受数据表条数限制前提下，查询中使用主键或唯一列的where条件子句，通常采用const，查询一定返回1条数据。称之为唯一索引扫描。
root@localhost:mysql3316.sock 11:48:32 [employees]>explain select * from employees where emp_no = 10001; +----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | employees | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | +----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 se
只将一部分主键作为条件时，访问方式将为ref而非const。若将主键或唯一索引的所有列明确为条件时，则可以使用const。
root@localhost:mysql3316.sock 11:52:23 [employees]>show create table dept_emp\G *************************** 1. row *************************** Table: dept_emp Create Table: CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`), KEY `dept_no` (`dept_no`), CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE, CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@localhost:mysql3316.sock 11:52:36 [employees]>explain select * from dept_emp where dept_no = 'd005'; +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ | 1 | SIMPLE | dept_emp | ref | dept_no | dept_no | 12 | const | 149978 | Using index condition | +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ 1 row in set (0.00 sec) root@localhost:mysql3316.sock 11:54:56 [employees]>explain select * from dept_emp where dept_no = 'd005' and emp_no = 10001; +----+-------------+----------+-------+------------------------+---------+---------+-------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+------------------------+---------+---------+-------------+------+-------+ | 1 | SIMPLE | dept_emp | const | PRIMARY,emp_no,dept_no | PRIMARY | 16 | const,const | 1 | NULL | +----+-------------+----------+-------+------------------------+---------+---------+-------------+------+-------+ 1 row in set (0.00 sec)
(3)eq_ref
eq_ref访问方法只出现在要连接多个表的查询执行计划中。在连接中将先读取的列值用作接下来要读取的主键或唯一键列（若是多个列组成，需全部用作比较条件）的检索条件时，这种方法为eq_ref。此时从第二个以后读取的表的type列都为ea_ref。
root@localhost:mysql3316.sock 17:27:17 [employees]>explain select * from dept_emp de,employees e where e.emp_no=de.emp_no and de.dept_no='d005'; +----+-------------+-------+--------+------------------------+---------+---------+---------------------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+------------------------+---------+---------+---------------------+--------+-----------------------+ | 1 | SIMPLE | de | ref | PRIMARY,emp_no,dept_no | dept_no | 12 | const | 149978 | Using index condition | | 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | employees.de.emp_no | 1 | NULL | +----+-------------+-------+--------+------------------------+---------+---------+---------------------+--------+-----------------------+
(4)ref
与ea_ref不同，ref的访问方法与连接顺序无关，也没主键和唯一索引约束。ref无法保证只有一条记录返回，所以不比const或eq_ref快。但由于只比较相等条件，也可以说是种非常快的访问方法。
root@localhost:mysql3316.sock 17:42:59 [employees]>explain select * from dept_emp where dept_no='d005'; +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ | 1 | SIMPLE | dept_emp | ref | dept_no | dept_no | 12 | const | 149978 | Using index condition | +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+
(5)fulltext
5.6开始innodb数据表可以使用全文搜索，只支持基于分隔符的索引。
（6）ref_or_null
该访问方法与ref相似，但是增加了null的比较。实际业务用的并不多。
（7）unique_subquery
in条件的subquery不会返回重复值
（8）index_subquery
in条件的subquery可能返回重复值，但使用索引可以删除重复值。
（9）range
range 就是索引范围扫描。range表示不依据单个值，而是在一定范围内检索索引。主要使用“<、>、is null、between、in、like”等运算符。虽优先级比较低，但访问速度相当快，所有查询只使用该方法也能获得比较好的访问性能。
root@localhost:mysql3316.sock 11:40:16 [employees]>explain select * from employees where emp_no between 10002 and 10004; +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | employees | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where | +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
（10）index_merge
index_merge访问方式中，先使用两个以上的索引分别获得搜索结果，然后再将其合并。该访问方式并非十分高效，index_merge有一下几个特点：
~由于要读取多个索引，所以通常比range访问方式效率差
~and和or连接而成的复杂查询中，很多时候不能优化
~使用index_merge访问处理的结果总是处理两个以上的集合，并进行合并、删除等额外操作。
(11)index
index 是索引全扫描方法，会从头到尾读取索引，索引文件通常比数据文件要小，因而比全表扫描高效。
root@localhost:mysql3316.sock 12:06:16 [employees]>explain select * from departments order by dept_name desc limit 10; +----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | departments | index | NULL | dept_name | 122 | NULL | 9 | Using index | +----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+
(12)all
all就是全表扫描方式，该方式从头到尾读取数据表中的全部记录，然后删除不需要的记录（存在检测条件时），再返回符合要求的记录。非常低效
root@localhost:mysql3316.sock 12:12:28 [employees]>explain select * from employees; +----+-------------+-----------+------+---------------+------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------+ | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299290 | NULL | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
5.possible_keys
优化器为了创建最优执行计划，在选定的访问方式中使用候选索引列表，这就是possible_keys列中的内容。数据表的所有索引通常都会出现在该目录中。列值并非都会用到。
6.key
key列中显示的索引则是最终选用的执行计划所使用的索引，key列显示为primary时，表示使用主键。除此之外其他值均为创建数据表时或索引时赋予的固有名称。
执行计划type列列不是index_merge时，每个表只能使用1个索引。但使用index_merge执行计划时，则可以使用2个以上索引，此时key列将显示多个索引。
7.key_len
执行计划中的key_len列值会告诉我们用于处理查询的多列索引是由几个数据列构成，是告诉我们索引的各个记录用到了几字节，因此无论是单列索引还是多列索引，它都提供了相同指标。
root@localhost:mysql3316.sock 12:32:31 [employees]>show create table dept_emp\G *************************** 1. row *************************** Table: dept_emp Create Table: CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`), KEY `dept_no` (`dept_no`), CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE, CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@localhost:mysql3316.sock 12:32:45 [employees]>explain select * from dept_emp where dept_no = 'd005'; +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ | 1 | SIMPLE | dept_emp | ref | dept_no | dept_no | 12 | const | 149978 | Using index condition | +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+
由于dept_no数据类型为char(4),所以在主键中只能有效使用12字节，该表的dept_no列使用utf8字符集，而一个utf8占用空间是可变的约1~3字节，mysql为utf8分配内存空间时按固定3字节计算。因此上述执行计划中key_len列值为12字节（4*3字节）
root@localhost:mysql3316.sock 12:45:49 [employees]>explain select * from dept_emp where dept_no = 'd005' and emp_no=10001; +----+-------------+----------+-------+------------------------+---------+---------+-------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+------------------------+---------+---------+-------------+------+-------+ | 1 | SIMPLE | dept_emp | const | PRIMARY,emp_no,dept_no | PRIMARY | 16 | const,const | 1 | NULL | +----+-------------+----------+-------+------------------------+---------+---------+-------------+------+-------+
emp_no为int类型，因为int占4个字节所以上述key_len值为16字节。
root@localhost:mysql3316.sock 13:04:56 [employees]>explain SELECT * FROM titles WHERE emp_no<='d005'; +----+-------------+--------+-------+----------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | titles | range | PRIMARY,emp_no | PRIMARY | 4 | NULL | 1 | Using where | +----+-------------+--------+-------+----------------+---------+---------+------+------+-------------+
为什么date类型占用3个字节，key_len显示占用4字节?
因为使用date类型的同时列定义了null列，用于保存null，数据列为not null列时，mysql使用1字节存储列值是否为null。
8.ref
访问方法是ref时，ref列显示提供了哪种值作为参考条件。若指定常数值则ref列值显示为const。若为其他数据表的列值，则显示数据表名与列名。(不是很重要）
9.rows列
rows列值是mysql优化器根据存储引擎的统计信息推算出的数值，因而不准确，rows列中显示的数值并 不是可能返回的记录条数，而是指处理查询时要从磁盘读取与检查多少条记录。因此，执行计划中的rows列值通常与实际查询结果返回的记录数不一致。
root@localhost:mysql3316.sock 14:05:33 [employees]>explain select * from dept_emp where from_date>='1985-01-01'; +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | dept_emp | ALL | NULL | NULL | NULL | NULL | 331143 | Using where | +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
root@localhost:mysql3316.sock 14:06:28 [employees]>select count(*) from dept_emp; +----------+ | count(*) | +----------+ | 331603 | +----------+
上述示例查询，虽然可以使用from_date列生成的ix_fromdate索引，但执行计划选择了all。是因为rows列为331143，表示mysql优化器处理查询时大概要读取331143条记录。dept_emp表的全部记录为331603，由此可判断执行查询时要对数据表几乎所有记录比较。因此mysql优化器没有选择范围扫描，而选择了全表扫描。
更改查询语句：
root@localhost:mysql3316.sock 14:24:41 [employees]>explain select * from dept_emp where emp_no<'10010'; +----+-------------+----------+-------+----------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+----------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | dept_emp | range | PRIMARY,emp_no | PRIMARY | 4 | NULL | 9 | Using where | +----+-------------+----------+-------+----------------+---------+---------+------+------+-------------+
上述示例查询中，优化器推测emp_no列值小于10010的记录只有9条，这只占数据表全部记录数的很小比例，因此优化器采用range（索引范围扫描）处理emp_no索引。
10.extra
extra，查询计划中与性能相关的重要信息经常会出现在extra列。extra列中显示的语句只有固定几个，一般会同时显示2~3个。
（1）const row not found
执行计划中，虽然使用const访问方式读取了数据表，但若实际中没有1条符合条件的记录，则extra列将显示const row not found。
（2）distinct
root@localhost:mysql3316.sock 16:20:24 [employees]>explain select distinct d.dept_no from departments d,dept_emp de where de.dept_no=d.dept_no; +----+-------------+-------+-------+-------------------+-----------+---------+---------------------+-------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------+-----------+---------+---------------------+-------+------------------------------+ | 1 | SIMPLE | d | index | PRIMARY,dept_name | dept_name | 122 | NULL | 9 | Using index; Using temporary | | 1 | SIMPLE | de | ref | dept_no | dept_no | 12 | employees.d.dept_no | 20696 | Using index; Distinct | +----+-------------+-------+-------+-------------------+-----------+---------+---------------------+-------+------------------------------+
上述查询中，实际想访问的值是dept_no，而dept_no在departments与dept_emp表中都存在，执行查询时会先将两个表连接起来，然后再在结果中进行distinct处理，这样最终结果，dept_no就不会重复。
（3）full scan on null key
full scan on null key经常出现在col1 in （select col2 from .....)条件查询中。若col1值为null，则最终条件变为null in （select col2 from .....)。若col1为null，则使用全表扫描。若col1 in （select col2 from .....)条件中，col1列定义为not null，则不会使用全表扫描。
（4）impossible having
不存在满足查询having子句条件的记录时，执行计划extra列会显示impossible having。
root@localhost:mysql3316.sock 17:07:37 [employees]>explain select e.emp_no,count(*) as cnt from employees e where e.emp_no=10001 group by e.emp_no having e.emp_no 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 HAVING | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
emp_no为主键 为not null，所以不满足e.emp_no is null 条件。出现impossible having大部分表示查询编写有误。
（5）impossible where
与“impossible having”类似，where 条件总为false时，extra列将显示“impossible where”
root@localhost:mysql3316.sock 17:16:55 [employees]>explain select * from employees where emp_no 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 | +----+-------------+-------+------+---------------+------+---------+------+------+------------------+
因为emp_no为主键，所以不可能满足emp_no is null。
（6）impossible where noticed after reading const tables
root@localhost:mysql3316.sock 17:16:59 [employees]>explain select * from employees where emp_no=0; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
指定的where 条件 查询不存在
（7） No matching min/max row
若一个查询中含有类似min(),max()的集合函数，并且无任何符合查询条件的记录，则extra列返回No matching min/max row，并返回null作为min（）与max（）的结果。
root@localhost:mysql3316.sock 17:52:18 [employees]>EXPLAIN SELECT MIN(dept_no),MAX(dept_no) FROM dept_emp WHERE dept_no=''; +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
（8）no matching row in const table
使用const方式访问链接中的数据表时，若不存在符合条件的记录，则执行计划extra列将显示“no matching row in const table”
root@localhost:mysql3316.sock 12:24:39 [employees]>explain select * from dept_emp de,(select emp_no from employees where emp_no=0) tb1 where tb1.emp_no=de.emp_no and de.dept_no='d005'; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
（9）no table used
不带from子句的查询或from dual查询的执行计划中，extra列会显示“no table used”
root@localhost:mysql3316.sock 12:29:32 [(none)]>explain select 1; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
（10）not exists
应用程序中经常需要访问a表中存在但b表中不存在的值。此时主要使用not in或not exists运算符，这样的连接称为反连接。同样的处理也可使用左外连接（left outer join）实现。通常使用not in、not exists。但记录数很多时，使用外部连接能获得更快的处理性能。
下列查询中，先外部链接department数据表，再在where子句（非on子句）中检索并获取外部表（department）中dept_no列值为null的记录。也就是说，反连接方法常常只用于获取普通连接（inner join）时不会得到的结果。
root@localhost:mysql3316.sock 12:37:16 [employees]>explain select * from dept_emp de left join departments d on de.dept_no=d.dept_no where d.dept_no is null; +----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+-------------------------+ | 1 | SIMPLE | de | ALL | NULL | NULL | NULL | NULL | 331143 | NULL | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 12 | employees.de.dept_no | 1 | Using where; Not exists | +----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+-------------------------+
（11）range checked for each record（index map:N）
mysql 优化器会优先读取e1表，然后为进行连接而读取e2表时，在索引范围扫描与全表扫描中无法判断哪个更高效，也就是说每次从e1表中读取一条记录，e1.emp_no值就会变化，即用于查询代价的基准值一直在变化。因此优化器无法判断使用哪种访问方法读取e2数据表更有效率。（对每条记录都要从range与all中选择）。
因此我们采用的优化方法是：e1表的emp_no很小时，使用全表扫描访问e2数据表。而e1数据表很大时，就是用索引范围扫描访问e2数据表。
index map:0X1是候选索引号，用于判断是否使用候选索引。
root@localhost:mysql3316.sock 13:49:15 [employees]>explain select * from employees e1,employees e2 where e2.emp_no >= e1.emp_no; +----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------------------------+ | 1 | SIMPLE | e1 | ALL | PRIMARY | NULL | NULL | NULL | 299290 | NULL | | 1 | SIMPLE | e2 | ALL | PRIMARY | NULL | NULL | NULL | 299290 | Range checked for each record (index map: 0x1) | +----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------------------------+
(12)scanned N databases
只有从information schema中读取数据时才会显示 scanned N databases
（13）select table optimizer away
查询的select语句中只使用min()或max()，或者用group by访问min()或max()时，若无法使用合适索引，就会按升序或降序只读取1个索引，经过这种优化会出现select table optimizer away
root@localhost:mysql3316.sock 14:26:55 [employees]>explain select max(emp_no),min(emp_no) from employees; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
root@localhost:mysql3316.sock 14:37:04 [employees]>explain select max(from_date),min(from_date) from salaries where emp_no=10001; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
（14）skip_open_table、open_frm_only、open_trigger_only、open_full_table
与scanned N databases类似，只有select查询information_schema数据库的元数据信息时，extra列才会出现。
（15）union row not fount
由两个表各自的唯一列（含主键）执行外部链接的查询中，若外表不存在不一样的记录，则extra列会显示。
root@localhost:mysql3316.sock 14:51:00 [employees]>create table zs (id int,primary key(id)); root@localhost:mysql3316.sock 14:51:22 [employees]>create table zhangshuo (id int,primary key(id)); root@localhost:mysql3316.sock 14:51:59 [employees]>insert into zs values (1),(2); root@localhost:mysql3316.sock 14:52:48 [employees]>insert into zhangshuo values (1); root@localhost:mysql3316.sock 14:53:18 [employees]>explain select t1.id from zs t1 left join zhangshuo t2 on t2.id=t1.id where t1.id=2; +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------------------+ | 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 4 | const | 0 | unique row not found | +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------------------+
（16）using filesort
虽然处理order by时可以使用索引，但无合适的索引可用时，mysql会再次对访问的记录排序。只有无法使用索引处理order by时，执行计划的extra列才会显示using filesort。
这意味着将访问的记录复制到用于排序的内存缓冲区，然后采用快速排序算法进行排序，using filesort只显示在带有order by子句的查询计划中。
root@localhost:mysql3316.sock 18:02:14 [employees]>explain select * from employees order by last_name desc; +----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+ | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299290 | Using filesort | +----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
因为last_name列没有索引，所以无法使用索引处理查询的排序操作。mysql优化器先读取记录，再将其复制到排序缓冲区（sort buffer），排序后将结果发送给客户端。
（17）using index
若处理查询时完全不必读取数据，则可只读取索引，此时查询执行计划的extra列就会显示“using index”（覆盖索引），使用索引处理的查询中，最占负荷的是检索索引，读取索引文件，以获取符合条件的其余列值。最坏的情况就是通过索引检索到的每条记录都要读取一次磁盘。
root@localhost:mysql3316.sock 18:33:01 [employees]>explain select first_name,emp_no from employees where emp_no between '10001'and'10010'; +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | employees | range | PRIMARY | PRIMARY | 4 | NULL | 10 | Using where | +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
上述示例因为只有emp_no是主键，无法全部使用索引。
root@localhost:mysql3316.sock 18:47:43 [employees]>explain SELECT dept_no,emp_no FROM dept_emp where emp_no=10010;
+----+-------------+----------+------+----------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+----------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | dept_emp | ref | PRIMARY,emp_no | emp_no | 4 | const | 2 | Using index |
+----+-------------+----------+------+----------------+--------+---------+-------+------+-------------+
上述示例使用主键完成。
（18）using index for group-by
先使用分组基准列进行排序，然后将排序结果进行分组。若使用索引（b-tree）处理group-by，就会按已经排列好的顺序依次读取索引列，这样只要进行分组处理即可，像这样使用索引处理group-by时，由于不需要对记录排序，只读取索引必要的部分即可，所以处理非常高效，非常快。使用索引处理group-by时，执行计划extra列将显示using index for group-by。
处理group-by时，简单的顺序读取索引方式与只读取索引必要部分的松散扫描方式不同。
使用紧凑索引扫描（索引扫描）处理group-by：
带有avg，sum(),count(*)等函数时，处理查询时要读取所有索引。此时即使使用索引处理group-by，也无法只“松散的读取所需记录”，虽然正确使用group-by子句，但执行计划中不会显示“using index for group-by”
root@localhost:mysql3316.sock 23:02:21 [employees]>explain select emp_no,count(*) from employees group by emp_no; +----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | employees | index | PRIMARY | PRIMARY | 4 | NULL | 299290 | Using index | +----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
单列索引中除了分组外，若查询不访问任何内容，则可以使用松散索引扫描。多列索引中，group by子句必须能使用索引。一些查询中，访问的值只要读取索引的第一个或最后一个记录即可。此时可以使用松散索引扫描，并且读取索引时“松散地”读取所需部分。
root@localhost:mysql3316.sock 23:09:29 [employees]>explain select emp_no,min(from_date) as first_changed_date,max(from_date) as last_changed_date from salaries group by emp_no; +----+-------------+----------+-------+----------------+--------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+----------------+--------+---------+------+--------+--------------------------+ | 1 | SIMPLE | salaries | range | PRIMARY,emp_no | emp_no | 4 | NULL | 645860 | Using index for group-by | +----+-------------+----------+-------+----------------+--------+---------+------+--------+--------------------------+
无where条件子句时：
若查询不带where条件子句，只要group by子句的数据列与由select获取的数据列具备使用“松散索引扫描”的条件即可。
有where条件子句，但无法使用索引扫描：
group by子句可以使用索引，但where条件子句无法使用索引时，首先读取索引进行group by，然后读取数据记录处理where条件中的比较。此情况不能使用“松散索引扫描”，而是通过“紧凑索引扫描”（索引扫描）过程处理group by。
root@localhost:mysql3316.sock 10:55:28 [employees]>explain select first_name from employees where birth_date>'1994-01-01' group by emp_no; +----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | employees | index | PRIMARY | PRIMARY | 4 | NULL | 299290 | Using where | +----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
有where条件子句，且可以使用索引搜索树。
执行一个单位查询时，除index_merge外的访问方法只能使用一个索引。当where条件和group by处理相同索引时，才可以使用“松散索引扫描”。（注意，where条件需要大范围扫描）
root@localhost:mysql3316.sock 11:18:43 [employees]>explain select emp_no from salaries where emp_no between 10001 and 20009 group by emp_no; +----+-------------+----------+-------+----------------+--------+---------+------+-------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+----------------+--------+---------+------+-------+---------------------------------------+ | 1 | SIMPLE | salaries | range | PRIMARY,emp_no | emp_no | 4 | NULL | 47841 | Using where; Using index for group-by | +----+-------------+----------+-------+----------------+--------+---------+------+-------+---------------------------------------+
where 条件小范围扫描时：
root@localhost:mysql3316.sock 11:31:12 [employees]>explain select emp_no from salaries where emp_no between 10001 and 10099 group by emp_no; +----+-------------+----------+-------+----------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+----------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | salaries | range | PRIMARY,emp_no | PRIMARY | 4 | NULL | 983 | Using where; Using index | +----+-------------+----------+-------+----------------+---------+---------+------+------+--------------------------+
（19）Using join buffer (Block Nested Loop)，Using join buffer (batched key access)
进行表连接时，若被驱动表的连接列有合适的索引，则不会发生任何问题。但如果被驱动表中不存在适于检索的索引，则会根据从驱动表（连接时首先要读取的驱动表）中读取的记录条数，每次都对被驱动表进行全表扫描或索引全扫描。此时为了解决被驱动表（连接后读取的数据表）检索低效的问题，mysql会将从驱动表读取的记录保存到临时的内存空间，每当需要重新使用。用于临时保存所读取记录的内存空间称为“连接缓冲”（join buffer），使用“连接缓冲”的执行计划的extra列会显示Using join buffer。
通过join_buffer_size可以设置用于连接的最大缓冲区大小。默认2M
root@localhost:mysql3316.sock 11:43:26 [employees]>explain select * from dept_emp de,employees e where de.from_date>'2005-01-01' and e.emp_no<10904; +----+-------------+-------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+ | 1 | SIMPLE | e | range | PRIMARY | PRIMARY | 4 | NULL | 902 | Using where | | 1 | SIMPLE | de | ALL | NULL | NULL | NULL | NULL | 331143 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+
Using join buffer（xxx） #表示使用哪种连接算法
20.using sort_union、using union、using intersect、using sort_intersection
使用index_merge访问方式（执行计划的type列值为index_merge）执行查询时，可以同时使用两个以上的索引，此时为了进一步说明如何合并从两个索引读取的结果，会在执行计划extra列显示这4种信息之一。
（1）using intersect：使用and连接各个使用索引的条件时，该信息表示从各处结果获取交集。
（2）using union:使用or连接各个使用索引的条件时，该信息表示从各处结果获取并集。
（3）using sort_union：执行的处理与using union相同，但无法使用using union处理时（用or连接的量相对大的range条件），才使用该方式进行处理。using sort_union与using union的不同在于，using sort_union要先读取主键，进行排序合并后，才能读取记录并返回。
（4）using sort_intersect:mysql5.6中，using sort_intersect执行计划只能用于相等比较条件。sort_intersect类似于sort_union，也是先排序再取交集。
using union与using sort_union均可以用于使用or连接使用索引的条件。using union（）常用于匹配记录数不多（比如相等比较equal）的情况，而是用大于、小于这类会有很多记录条件时，常常使用using sort_union（）。但是，无论多少记录，若各where条件中使用的比较全为相等条件，则使用using union（），否则使用using sort_union。
21.using temporary
mysql处理查询时会使用临时表存储中间结果。临时表可以在内存中创建，也可以在磁盘上创建。若查询执行计划中显示using temporary，则表示使用临时表。此时，仅凭执行计划无法确定临时表创建在内存或是磁盘。
root@localhost:mysql3316.sock 16:23:41 [employees]>explain select * from employees group by gender order by min(emp_no); +----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+ | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299290 | Using temporary; Using filesort | +----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
22.using where
下述示例，限制条件为emp_no between 10001 and 10100，而gender= 'M'是检查条件。虽然满足emp_no between 10001 and 10100条件为100条记录，但同时满足两个条件的记录只有63条。存储引擎会从磁盘抓取100条记录，然后交给mysql引擎，mysql引擎从100条记录中过滤掉37条不符合条件的记录。using where 表示过滤掉不符合条件的37条记录。
root@localhost:mysql3316.sock 16:45:48 [employees]> explain select * from employees where emp_no between 10001 and 10100 and gender= 'M'; +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | employees | range | PRIMARY | PRIMARY | 4 | NULL | 100 | Using where | +----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
23.using where with pushed condition
条件下推，只会出现在ndb_cluster。由于ndb_cluster由多个节点组成。数据库引擎与存储引擎通过网络进行tcp连接，这种架构不使用条件下推会引发严重性能低下问题。
条件下推 #在存储引擎层面完成对数据的过滤，然后显示给客户端。不需要在mysql层面过滤数据（如检查条件）。
24.deleting all rows
像innodb存储引擎一样，从存储引擎的handler角度看，数据表所用的存储引擎提供删除数据表所有记录的功能时，extra列会显示deleting all rows。.
25.firstmatch（table_name）
firstmatch是mysql5.6开始引用的用于优化子查询的众多策略之一。
root@localhost:mysql3316.sock 17:38:10 [employees]>explain select * from departments d where d.dept_no in (select de.dept_no from dept_emp de); +----+-------------+-------+-------+---------------+-----------+---------+---------------------+-------+----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-----------+---------+---------------------+-------+----------------------------+ | 1 | SIMPLE | d | index | PRIMARY | dept_name | 122 | NULL | 9 | Using index | | 1 | SIMPLE | de | ref | dept_no | dept_no | 12 | employees.d.dept_no | 20696 | Using index; FirstMatch(d) | +----+-------------+-------+-------+---------------+-----------+---------+---------------------+-------+----------------------------+
上述示例，先从departments表读取一条记录，然后检索dept_no表中的记录，直到检索到符合条件的记录为止。如果在子查询中1次即可查找到符合条件的记录，那么执行代价会非常小；如果不存在任何符合条件的记录，那么执行代价可能会相当高。
26.LooseScan
5.6引用的用于优化子查询的众多策略之一，in（subquery）类型的查询中，子查询的结果可能产生重复记录时，使用该优化方法。
27.materialize、scan
该信息不会出现在稳定版本中
28.start temporary、end temporary
duplicate weedout是另一种优化子查询的方法，使用时extra会显示materialize、scan语句。使用duplicate weedout优化时，会先访问in（subquery)查询中的子查询，然后将与外部查询数据表连接后的结果存储到临时表，最后再删除重复记录。dulicate weedout优化过程与连接in（subquery)查询并用group by删除重复记录的过程一样。
29.mrr（多范围读）
“多范围读”（multi range read）是mysql5.6开始新增的优化功能。实际应用有时需要先通过索引范围扫描查找符合where条件的记录，然后在根据需要读取数据文件的其余记录。通过索引查找到的记录相当多时，对相关记录数据文件的读取每次都采用随机访问方式，影响性能。引入mrr正解决了这一问题，先通过索引一定量符合where条件的记录，然后使用主键值全部排序，再从实际数据文件中读取其余数据列。
select_type列需要注意：
derived，是from子句中的子查询形成的临时表，该表存储在内存中，也可以存储到磁盘。一般将临时表存储在内存不会对性能产生很大影响。但数据很大而将临时表存储在磁盘，就会导致性能下降。
uncacheable subquery，对于查询的from子句之外部分使用的子查询，其自身无法执行。从外部查询获取值执行时，就会显示uncacheable subquery。由于这种子查询无法先执行，且子查询依赖外部查询结果，所以将导致整个查询性能变慢。若可以，请去除子查询对外部查询的依赖。
type列需要注意：
all、index。all表示全表扫描，index表示全索引扫描。
extra列需要注意
执行计划不佳：
（1）range checked for each record
（2）using filesort
（3）using join buffer
（4）using temporary
（5）using where
执行计划很好：
（1）distinct
（2）using index
（3）using index for group-by
