执行计划
什么是执行计划
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是 如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈
执行计划的作用
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
执行计划的语法
执行计划的语法其实非常简单： 在 SQL 查询的前面加上 EXPLAIN 关键字就行。
比如：EXPLAIN select * from table1
重点的就是 EXPLAIN 后面你要分析的 SQL 语句
执行计划详解
通过 EXPLAIN 关键分析的结果由以下列组成，接下来挨个分析每一个列
ID 列
ID 列：描述 select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺 序
根据 ID 的数值结果可以分成一下三种情况
- id 相同：执行顺序由上至下
- id 不同：如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
- id 相同不同：同时存在
分别举例来看
Id 相同
如上图所示，ID 列的值全为 1，代表执行的允许从 t1 开始加载，依次为 t3 与 t2
EXPLAIN select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';
Id 不同
如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
EXPLAIN select t2.* from t2 where id = ( select id from t1 where id = (select t3.id from t3 where t3.other_column='') );
Id 相同又不同
id 如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id 值越大，优先级越高，越先执行
EXPLAIN select t2.* from ( select t3.id from t3 where t3.other_column = '' ) s1 ,t2 where s1.id = t2.id
select_type 列
Select_type:查询的类型，
要是用于区别:普通查询、联合查询、子查询等的复杂查询
类型如下
SIMPLE
EXPLAIN select * from t1
简单的 select 查询,查询中不包含子查询或者 UNION
PRIMARY 与 SUBQUERY
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为
SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询
EXPLAIN select t1.*,(select t2.id from t2 where t2.id = 1 ) from t1
DERIVED
在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)
MySQL 会递归执行这些子查询, 把结果放在临时表里。
select t1.* from t1 ,(select t2.* from t2 where t2.id = 1 ) s2 where t1.id = s2.id
UNION RESULT 与 UNION
UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；
UNION RESULT：从 UNION 表获取结果的 SELECT
#UNION RESULT ,UNION
EXPLAIN select * from t2 UNION select * from t2
table 列
显示这一行的数据是关于哪张表的
Type 列
type 显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
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
一般来说，得保证查询至少达到 range 级别，最好能达到 ref。
System 与 const
System：表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也 可以忽略不计
Const：表示通过索引一次就找到了 const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快
如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量
EXPLAIN SELECT * from (select * from t2 where id = 1) d1;
eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引 扫描
EXPLAIN SELECT * from t1,t2 where t1.id = t2.id
Ref
非唯一性索引扫描，返回匹配某个单独值的所有行.
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合 条件的行，所以他应该属于查找和扫描的混合体
EXPLAIN select count(DISTINCT col1) from t1 where col1 = 'ac'
或者
EXPLAIN select col1 from t1 where col1 = 'ac'
Range
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的 where 语句中出现了 between、<、>、in 等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一 点，不用扫描全部索引。
EXPLAIN select * from t1 where id BETWEEN 30 and 60
EXPLAIN select * from t1 where id in(1,2)
Index
当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询 数据。
EXPLAIN select c2 from testdemo
All
Full Table Scan，将遍历全表以找到匹配的行
possible_keys 与 Key
possible_keys:可能使用的 key
Key:实际使用的索引。如果为 NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠
这里的覆盖索引非常重要!!!
EXPLAIN select col1,col2 from t1
其中 key 和 possible_keys 都可以出现 null 的情况（结婚邀请朋友的例子）
key_len
desc select * from ta where col1 ='ab';
desc select * from ta where col1 ='ab' and col2 = 'ac'
Key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精 确性的情况下，长度越短越好
key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义 计算而得，不是通过表内检索出的
- key_len 表示索引使用的字节数，
- 根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字 段是否都被查询用到。
- char 和 varchar 跟字符编码也有密切的联系,
- latin1 占用 1 个字节，gbk 占用 2 个字节，utf8 占用 3 个字节。（不同字符编码占用的 存储空间不同）
字符类型
以上这个表列出了所有字符类型，但真正建所有的类型常用情况只是 CHAR、VARCHAR
字符类型-索引字段为 char 类型+不可为 Null 时
CREATE TABLE `s1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(10) NOT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain select * from s1 where name='enjoy';
name 这一列为 char(10),字符集为 utf-8 占用 3 个字节
Keylen=10*3
字符类型-索引字段为 char 类型+允许为 Null 时
CREATE TABLE `s2` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(10) DEFAULT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain select * from s2 where name='enjoyedu';
name 这一列为 char(10),字符集为 utf-8 占用 3 个字节,外加需要存入一个 null 值
Keylen=10*3+1(null) 结果为 31
索引字段为 varchar 类型+不可为 Null 时
CREATE TABLE `s3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain select * from s3 where name='enjoyeud';
Keylen=varchar(n)变长字段+不允许 Null=n*(utf8=3,gbk=2,latin1=1)+2
索引字段为 varchar 类型+允许为 Null 时
CREATE TABLE `s3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain select * from s3 where name='enjoyeud';
Keylen=varchar(n)变长字段+允许 Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2
数值类型
CREATE TABLE `numberKeyLen ` (
`c0` int(255) NOT NULL ,
`c1` tinyint(255) NULL DEFAULT NULL ,
`c2` smallint(255) NULL DEFAULT NULL ,
`c3` mediumint(255) NULL DEFAULT NULL ,
`c4` int(255) NULL DEFAULT NULL ,
`c5` bigint(255) NULL DEFAULT NULL ,
`c6` float(255,0) NULL DEFAULT NULL ,
`c7` double(255,0) NULL DEFAULT NULL ,
PRIMARY KEY (`c0`),
INDEX `index_tinyint` (`c1`) USING BTREE ,
INDEX `index_smallint` (`c2`) USING BTREE ,
INDEX `index_mediumint` (`c3`) USING BTREE ,
INDEX `index_int` (`c4`) USING BTREE ,
INDEX `index_bigint` (`c5`) USING BTREE ,
INDEX `index_float` (`c6`) USING BTREE ,
INDEX `index_double` (`c7`) USING BTREE
)ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPACT ;
EXPLAIN select * from numberKeyLen where c1=1
EXPLAIN select * from numberKeyLen where c2=1
EXPLAIN select * from numberKeyLen where c3=1
EXPLAIN select * from numberKeyLen where c4=1
EXPLAIN select * from numberKeyLen where c5=1
EXPLAIN select * from numberKeyLen where c6=1
EXPLAIN select * from numberKeyLen where c7=1
日期和时间
datetime 类型在 5.6 中字段长度是 5 个字节
datetime 类型在 5.5 中字段长度是 8 个字节
CREATE TABLE `datatimekeylen ` ( `c1` date NULL DEFAULT NULL , `c2` time NULL DEFAULT NULL , `c3` year NULL DEFAULT NULL , `c4` datetime NULL DEFAULT NULL , `c5` timestamp NULL DEFAULT NULL , INDEX `index_date` (`c1`) USING BTREE , INDEX `index_time` (`c2`) USING BTREE , INDEX `index_year` (`c3`) USING BTREE , INDEX `index_datetime` (`c4`) USING BTREE , INDEX `index_timestamp` (`c5`) USING BTREE )ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPACT ;
EXPLAIN SELECT * from datatimekeylen where c1 = 1
EXPLAIN SELECT * from datatimekeylen where c2 = 1
EXPLAIN SELECT * from datatimekeylen where c3 = 1
EXPLAIN SELECT * from datatimekeylen where c4 = 1
EXPLAIN SELECT * from datatimekeylen where c5 = 1
总结
字符类型
变长字段需要额外的 2 个字节（VARCHAR 值保存时只保存需要的字符数，另加一个字节来 记录长度(如果列声明的长度超过 255，则使用两个字节)，所以 VARCAHR 索引长度计算时候 要加 2），固定长度字段不需要额外的字节。
而 NULL 都需要 1 个字节的额外空间,所以索引字段最好不要为 NULL，因为 NULL 让统计更加 复杂并且需要额外的存储空间。
复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之 和，这也可以用来判定复合索引是否部分使用，还是全部使用。
整数/浮点数/时间类型的索引长度
NOT NULL=字段本身的字段长度
NULL=字段本身的字段长度+1(因为需要有是否为空的标记，这个标记需要占用 1 个字节)
datetime 类型在 5.6 中字段长度是 5 个字节，datetime 类型在 5.5 中字段长度是 8 个字节
Ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列 上的值
EXPLAIN select * from s1 ,s2 where s1.id = s2.id and s1.name = 'enjoy'
由 key_len 可知 t1 表的 idx_col1_col2 被充分使用，col1 匹配 t2 表的 col1，col2 匹配了一个 常量，即 ‘ac’
其中 【shared.t2.col1】 为 【数据库.表.列】
Rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra
包含不适合在其他列中显示但十分重要的额外信息。
Using filesort
说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL 中无法利用索引完成的排序操作称为“文件排序”
当发现有 Using filesort 后，实际上就是发现了可以优化的地方
上图其实是一种索引失效的情况，后面会讲，可以看出查询中用到了个联合索引，索引分别 为 col1,col2,col3
当我排序新增了个 col2，发现 using filesort 就没有了。
EXPLAIN select col1 from t1 where col1='ac' order by col3
EXPLAIN select col1 from t1 where col1='ac' order by col2,col3
Using temporary
使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
尤其发现在执行计划里面有 using filesort 而且还有 Using temporary 的时候，特别需要注意
EXPLAIN select col1 from t1 where col1 in('ac','ab','aa') GROUP BY col2
EXPLAIN select col1 from t1 where col1 in('ac','ab','aa') GROUP BY col1,col2
Using index
表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不 错！
如果同时出现 using where，表明索引被用来执行索引键值的查找;
如果没有同时出现 using where，表明索引用来读取数据而非执行查找动作
EXPLAIN select col2 from t1 where col1 = 'ab'
EXPLAIN select col2 from t1
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
