Mysql执行计划

MySQL执行计划

我们经常使用 MySQL 的执行计划来查看 SQL 语句的执行效率，接下来分析执行计划的各个显示内容。

EXPLAIN
SELECT
	( SELECT t2.NAME FROM tb_dept t2 WHERE t2.id = t1.dept_id ) 
FROM
	tb_user t1,
	tb_user_role t3,
	tb_role t4 
WHERE t1.id=t3.user_id
	AND t3.role_id = t4.id
	AND t4.name='开发部'

执行计划的 id

select 查询的序列号，标识执行的顺序

• id相同，执行顺序由上至下
• id不同， 如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

执行计划的 select_type

• SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION

• PRIMARY：为复杂查询创建的首要表(也是最外层的表) 这种类型通常与DERIVED 或者 UNION 混合使用见到
• UNION： 当SELECT 之前的关键字为UNION 或 UNION ALL时 会出现UNION 关键字
• DEPENDENT UNION： 当子查询中存在UNION时UNION 后的 select_type 会出现 DEPENDENT UNION 而union 语句的第一行为 DEPENDENT SUBQUERY
• UNION RESULT： 出现在UNION 或UNION ALL语句中 代表把所有结果集联合起来
• SUBQUERY：出现在复杂非相关子查询中 ,简单相关子查询MySQL会进行改写
• DEPENDENT SUBQUERY： 出现在相关子查询中而非相关子查询MySQL可以进行改写
• DERIVED： 衍生表当查询使用内联视图时会出现此关键字
• MATERIALIZED： 子查询物化 ,当表出现在非相关子查询中并且需要进行物化时会出现MATERIALIZED关键词
• UNCACHEABLE SUBQUERY：表示子查询不可被物化需要逐次运行
• UNCACHEABLE UNION： 子查询中出现UNION并且不可被缓存在UNION 后的 SELECT 语句出现此关键词

执行计划的 table

查询涉及到的表。

• 直接显示表名或者表的别名
• <union M,N> 由 id 为 M，N 查询 union 产生的结果
• 由 id 为 N 查询产生的结果

执行计划的 partitions

表分区、表创建的时候可以指定通过那个列进行表分区。

create table tmp (
  id   int unsigned not null AUTO_INCREMENT,
  name varchar(20),
  PRIMARY KEY (id)
)engine = innodb partition by key (id) partitions 5;

执行计划的 type

访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般常用和需要重点关注的 system > const > eq_ref > ref > range > index > ALL。

• system：系统表，少量数据，不进行磁盘IO，这是 const 类型的特列，平时不会出现，这个也可以忽略不计
• const： PK 或者 unique 上的等值查询
• eq_ref： PK 或者 unique 上的 join 查询，等值匹配，对于前表的每一行，后表只有一行命中
• ref： 非唯一索引，等值匹配，可能有多行命中
• range： 索引上的范围扫描，例如：between、in、>
• index：索引树扫描， 例如：Innodb 的 count
• ALL： 全表扫描（full table scan）

执行计划的 possible_keys

查询过程中有可能用到的索引。（不重要）

执行计划的 key

实际使用的索引，如果为 NULL ，则没有使用索引 。

执行计划的 key_len

key_len 表示索引使用的字节数，根据这个值就可以判断索引使用的情况。特别是在组合索引的时候，判断索引的索引字段是否都被查询到。那么key_len是怎么计算的呢，计算方法如下：

1. 整数类型，浮点数类型，时间类型的索引长度

NOT NULL = 字段本身的字段长度

NULL = 字段本身的字段长度+1，因为需要有是否为空的标记，这个标记需要占用1个字节

datetime = datetime不存毫秒的情况下是5个字节,存毫秒的情况下是8个字节

2.字符类型

varchr(n)变长字段且允许NULL    =  n * (utf8=3,gbk=2,latin1=1)+1(NULL)+2
varchr(n)变长字段且不允许NULL  =  n * (utf8=3,gbk=2,latin1=1)+2

char(n)固定字段且允许NULL      =  n * (utf8=3,gbk=2,latin1=1)+1(NULL)
char(n)固定字段且不允许NULL    =  n * (utf8=3,gbk=2,latin1=1)

变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。

而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间。

复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。下面举个例子：

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT ,
  `username` varchar(10) NOT NULL ,
  `password` varchar(20) NOT NULL ,
  `name` varchar(10) NOT NULL ,
  `idCard` char(10) DEFAULT NULL ,
  `age` int NOT NULL ,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_user` (`name`,`idCard`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM users WHERE name='admin' AND idcard='1234' AND age = 20;

name 类型为 varchar，长度为10

idcard 类型为 char, 长度为10，可为空

age 类型为 int

字符集类型 utf8

key_len = ( 10 * 3 + 2 ) + （10 * 3 + 1 ） + 4 = 67

执行计划的 ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

执行计划的 rows

根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数。

执行计划的 filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

执行计划的 Extra

十分重要的额外信息。

• Using filesort：MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。

• Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于 order by 或 group by。

• Using index：表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。

• Using index condition：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

• Using where：表示 SQL 操作使用了 where 过滤条件。

• Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

• Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。

• Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

• Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。