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='开发部'
![863e0000ff8a46d49f0cc037be01a53f.png](https://i-blog.csdnimg.cn/blog_migrate/4f648eea6f941e040bea6b51237be9f1.png)
执行计划的 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 产生的结果
- <subqueryN> 由 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;
![2246a889c46a7710f79e00b426208cc1.png](https://i-blog.csdnimg.cn/blog_migrate/e419f6439b0285e6858df100954028a0.png)
执行计划的 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
![c526ebeec69ec011d964f6304296a424.png](https://i-blog.csdnimg.cn/blog_migrate/90bb99d9dad93736c3986ce984fb3e52.png)
执行计划的 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 操作使用了关联查询或者子查询,且需要进行嵌套循环计算。MySQL执行计划