mysql执行计划
mysql的执行计划是对查询语句进行分析。了解查询语句的执行情况，找出查询语句执行的瓶颈，从而对查询语句进行优化。MySQL提供了EXPLAIN和DESCRIBE语句用来查看SELECT语句执行过程。语法如下：
EXPLAIN [EXTENDED] SELECT select_options;
使用EXTENDED关键字，EXPLAIN语句将产生附加信息。执行EXPLAIN语句，可以分析后面的查询语句的情况。并能分析出所查询表的一些特征。
例：
mysql> EXPLAIN SELECT * FROM student; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
执行计划 id:
SELECT识别符，这是SELECT的查询序列号。表示的是查询中执行select子句或者是操作表的顺序。
1、id相同，执行顺序自上而下
2、id不同， 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
3、id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
4、id为null， 表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中
执行计划select_type
表示SELECT语句的类型，有以下几种取值：
（1）、SIMPLE表示简单查询，其中不包括子查询和UNION
（2）、PRIMARY表示主查询，或者是最外层的查询语句
（3）、UNION 表示UNION中的第二个或更后面的的SELECT语句
（4）、DEPENDENT UNION 表示UNION查询中的第二个或更后面的SELECT语句，并且依赖外面的查询
解析，这条SQL的 select id from t_role where role_name = '学生' 并没有关联外部查询，但是被标记了DEPENDENT UNION。这是因为MySQL优化器会将 IN 操作符进行优化，将IN操作符中的非关联子查询优化为关联子查询。这种优化方式会导致对外层表全表扫描，如果外表太大性能并不是太好。例：
SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
类似语句会被该写为
SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a)
所以该SQL会被重写为这样
（5）、UNION RESULT表示UNION查询的结果
（6）、SUBQUERY表示子查询中的第一个SELECT语句。外层查询SELECT或者WHERE列表中包含子查询
（7）、DEPENDENT SUBQUERY表示子查询中的第一个SELECT ,依赖于外面的查询。
（8）、DERIVED表示派生表的SELECT(from子句中的子查询)
MySQL 5.7 之前的处理都是对 Derived table(派生表) 进行 Materialize(物化)，生成一个 临时表 用于保存 Derived table(派生表) 的结果，然后利用 临时表 来协助完成其他父查询的操作，比如 JOIN 等操作。
MySQL 5.7 中对 Derived table(派生表) 做了一个新特性，该特性允许将符合条件的 Derived table(派生表) 中的子表与父查询的表合并进行直接 JOIN，类似于 Oracle 中的 子查询展开，由优化器参数 optimizer_switch='derived_merge=ON' 来控制，默认为 打开。
但是 derived_merge 特性存在很多限制，当派生子查询存在以下操作时，该特性无法生效。DISTINCT、 GROUP BY、 UNION/UNION ALL 、 HAVING、 关联子查询、 LIMIT/OFFSET 以及 聚合操作 等。
5.7之后版本执行计划
（9）、MATERIALIZED 物化子查询
如下所示，如果dep_id字段没有索引，就会使用物化子查询。如果存在所有，则不会使用物化子查询。
create table department (id int primary key auto_increment); create table employee (id int primary key auto_increment, dep_id int, key(dep_id));
（10）、UNCACHEABLE SUBQUERY 结果不能被缓存的子查询，外层查询需要使用的时候都要重新执行一次
（11）、UNCACHEABLE UNION union中的第二个或者后面的不能被缓存的子查询
执行计划table
展示这一行的信息是关于哪张表的，不一定是实表，也有可能是以下几种值：
（1）、<unionM,N> 该行指id值为M和N的行的并集。多出现在UNION语句中
（2）、<derivedN> 该行引用id值为N的行的派生表结果
（3）、<subqueryN> 该行是指id值为N的行的物化子查询的结果
执行计划prititions
查询将从中匹配记录的分区。非分区表的值为 NULL
执行计划TYPE
type: 表示表的访问类型，下面按照最佳类型到最差类型的顺序给出各种连接类型。
（1）、null ： 不访问任何表，索引、直接返回结果
（2）、system: 该表是仅有一行记录（等于系统表），是const类型的一个特例。该例子在mysql5.7版本将 optimizer_switch 变量的 derived_merge 设置为off。
（3）、const: 表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const于将"主键" 或 "唯一" 索引的所有部分与常量值进行比较。
（4）、eq_ref: primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录，简单的 select 查询不会出现这种 type。类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
（5）、ref: 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
（5）、fulltext : 连接使用全文索引
（6）、ref_or_null：类似ref，但是可以搜索值为NULL的行
（7）、index_merge: 表示使用了索引合并的优化方法。
（8）、unique_subquery: 该类型替换了下面形式的IN子查询的ref： value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高
（9）、index_subquery: 该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr)
（10）、range: 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行
（11）、index：和ALL类似，不同就是mysql只需扫描索引树，这通常比ALL快一些
（12）、ALL: 即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
执行计划possible_keys
这一列显示查询可能使用哪些索引来查找。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能。
执行计划key
这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force key 、 use key 、ignoe key等
执行计划key_len
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
执行计划ref
ref列显示使用哪个列或常数与key一起从表中选择行。常见的有：const（常量），func，NULL，字段名（例：film.id）
执行计划rows
这一列是mysql估计要扫描并检测的行数，注意这个不是结果集里的行数
执行计划filtered
指返回结果的行占需要读到的行(rows列的值)的百分比，该值越大说明查询效果越好。
执行计划extra
extra: 表示查询时的额外信息。常见的主要信息如下：
（1）、const row not found： 类似于select …. from tbl_name，而表记录为空
（2）、Deleting all rows ： 于DELETE，一些存储引擎（如MyISAM）支持一种处理方法可以简单而快速地删除所有的表行。 如果引擎使用此优化，则会显示此额外值
（3）、Distinct ：MySQL正在寻找不同的值，因此在找到第一个匹配行后，它将停止搜索当前行组合的更多行。
（4）、FirstMatch(tbl_name)
（5）、Full scan on NULL key： 子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用
（6）、Impossible HAVING ： HAVING子句总是为false，不能选择任何行
（7）、Impossible WHERE ： WHERE子句始终为false，不能选择任何行
（8）、Impossible WHERE noticed after reading const tables： MySQL读取了所有的const和system表，并注意到WHERE子句总是为false
（9）、LooseScan(m..n) ：
（10）、No matching min/max row： 没有满足SELECT MIN（…）FROM … WHERE查询条件的行。
（11）、no matching row in const table： 表为空或者表中根据唯一键查询时没有匹配的行
（12）、No matching rows after partition pruning： 对于DELETE或UPDATE，优化器在分区修剪后没有发现任何删除或更新。 对于SELECT语句，它与Impossible WHERE的含义相似
（13）、No tables used： 没有FROM子句或者使用DUAL虚拟表
（14）、Not exists： MySQL能够对查询执行LEFT JOIN优化，并且在找到与LEFT JOIN条件匹配的一行后，不会在上一行组合中检查此表中的更多行
（15）、 Plan isn't ready yet：
（16）、Range checked for each record (index map: N)：MySQL发现没有使用好的索引，但是发现在前面的表的列值已知之后，可能会使用一些索引。 对于上表中的每一行组合，MySQL检查是否可以使用range或index_merge访问方法来检索行。 这不是很快，但比执行没有索引的连接更快。 index map N索引的编号从1开始，按照与表的SHOW INDEX所示相同的顺序。 索引映射值N是指示哪些索引是候选的位掩码值。 例如，0x19（二进制11001）的值意味着将考虑索引1,4和5。 其中name属性为varchar类型；但是条件给出整数型，涉及到隐式转换。 图中t2也没有用到索引，是因为查询之前我将t2中name字段排序规则改为utf8_bin导致的链接字段排序规则不匹配
（17）、Scanned N databases：
（18）、Select tables optimized away： 当我们使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询。在使用某些聚合函数如min, max的query，直接访问存储结构(B树或者B+树)的最左侧叶子节点或者最右侧叶子节点即可，这些可以通过index解决。Select count(*) from table(不包含where等子句)，MyISAM保存了记录的总数，可以直接返回结果，而Innodb需要全表扫描。Query中不能有group by操作
（19）、Skip_open_table, Open_frm_only, Open_full_table 这些值表示适用于INFORMATION_SCHEMA表查询的文件打开优化；
Skip_open_table：表文件不需要打开。信息已经通过扫描数据库目录在查询中实现可用。
Open_frm_only：只需要打开表的.frm文件。
Open_full_table：未优化的信息查找。必须打开.frm，.MYD和.MYI文件
（20）、Start temporary, End temporary：
（21）、unique row not found 对于诸如SELECT … FROM tbl_name的查询，没有行满足表上的UNIQUE索引或PRIMARY KEY的条件
（22）、Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
（23）、Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
（24）、 Using index condition： Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行
（25）、Using index for group-by： 数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。注：和Using index一样，只需读取覆盖索引
（26）、Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)：
Block Nested-Loop Join算法：将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。优化器管理参数optimizer_switch中中的block_nested_loop参数控制着BNL是否被用于优化器。默认条件下是开启，若果设置为off，优化器在选择 join方式的时候会选择NLJ(Nested Loop Join)算法。
Batched Key Access原理：对于多表join语句，当MySQL使用索引访问第二个join表的时候，使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后，批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的（mrr目的是较为顺序）MRR使得查询更有效率，要使用BKA，必须调整系统参数optimizer_switch的值，batched_key_access设置为on，因为BKA使用了MRR，因此也要打开MRR
（27）、 Using MRR：
使用MRR策略优化表数据读取，仅仅针对二级索引的范围扫描和 使用二级索引进行 join 的情况；
过程：先根据where条件中的辅助索引获取辅助索引与主键的集合，再将结果集放在buffer(read_rnd_buffer_size 直到buffer满了)，然后对结果集按照pk_column排序，得到有序的结果集rest_sort。最后利用已经排序过的结果集，访问表中的数据，此时是顺序IO。即MySQL 将根据辅助索引获取的结果集根据主键进行排序，将无序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，减少IO操作，提高查询效率。
注：MRR原理：Multi-Range Read Optimization，是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段，这对IO-bound类型的SQL语句性能带来极大的提升，适用于range ref eq_ref类型的查询
（28）、 Using sort_union(...), Using union(...), Using intersect(...)：
（29）、Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
（30）、Using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃
（31）、Using where with pushed condition ： 仅用在ndb上。Mysql Cluster用Condition Pushdown优化改善非索引字段和常量之间的直接比较。condition被pushed down到cluster的数据节点，并在所有数据节点同时估算，把不合条件的列剔除避免网络传输
（32）、Zero limit : 查询具有LIMIT 0子句，无法选择任何行。
上述SQL使用到的表：
CREATE TABLE `t_role` ( `id` VARCHAR ( 32 ) NOT NULL, `role_name` VARCHAR ( 255 ) DEFAULT NULL, `role_code` VARCHAR ( 255 ) DEFAULT NULL, `description` VARCHAR ( 255 ) DEFAULT NULL, PRIMARY KEY ( `id` ), UNIQUE KEY `unique_role_name` ( `role_name` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8; CREATE TABLE `t_user` ( `id` VARCHAR ( 32 ) NOT NULL, `username` VARCHAR ( 45 ) NOT NULL, `password` VARCHAR ( 96 ) NOT NULL, `name` VARCHAR ( 45 ) NOT NULL, PRIMARY KEY ( `id` ), UNIQUE KEY `unique_user_username` ( `username` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8; CREATE TABLE `user_role` ( `id` INT ( 11 ) NOT NULL auto_increment, `user_id` VARCHAR ( 32 ) DEFAULT NULL, `role_id` VARCHAR ( 32 ) DEFAULT NULL, PRIMARY KEY ( `id` ), KEY `fk_ur_user_id` ( `user_id` ), KEY `fk_ur_role_id` ( `role_id` ), CONSTRAINT `fk_ur_role_id` FOREIGN KEY ( `role_id` ) REFERENCES `t_role` ( `id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_ur_user_id` FOREIGN KEY ( `user_id` ) REFERENCES `t_user` ( `id` ) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = INNODB DEFAULT CHARSET = utf8; INSERT INTO `t_user` ( `id`, `username`, `password`, `name` ) VALUES ( '1', 'super', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '超级管理员' ); INSERT INTO `t_user` ( `id`, `username`, `password`, `name` ) VALUES ( '2', 'admin', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '系统管理员' ); INSERT INTO `t_user` ( `id`, `username`, `password`, `name` ) VALUES ( '3', 'itcast', '$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui', 'test02' ); INSERT INTO `t_user` ( `id`, `username`, `password`, `name` ) VALUES ( '4', 'stu1', '$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa', '学生1' ); INSERT INTO `t_user` ( `id`, `username`, `password`, `name` ) VALUES ( '5', 'stu2', '$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm', '学生2' ); INSERT INTO `t_user` ( `id`, `username`, `password`, `name` ) VALUES ( '6', 't1', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '老师1' ); INSERT INTO `t_role` ( `id`, `role_name`, `role_code`, `description` ) VALUES ( '5', '学生', 'student', '学生' ); INSERT INTO `t_role` ( `id`, `role_name`, `role_code`, `description` ) VALUES ( '7', '老师', 'teacher', '老师' ); INSERT INTO `t_role` ( `id`, `role_name`, `role_code`, `description` ) VALUES ( '8', '教学管理员', 'teachmanager', '教学管理员' ); INSERT INTO `t_role` ( `id`, `role_name`, `role_code`, `description` ) VALUES ( '9', '管理员', 'admin', '管理员' ); INSERT INTO `t_role` ( `id`, `role_name`, `role_code`, `description` ) VALUES ( '10', '超级管理员', 'super', '超级管理员' ); INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'), (NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;