Mysql执行计划

最新推荐文章于 2024-11-01 11:00:05 发布

TYY_TX

最新推荐文章于 2024-11-01 11:00:05 发布

阅读量593

点赞数

分类专栏： Mysql从入门到精通文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/baidu_38339840/article/details/121954317

版权

Mysql从入门到精通专栏收录该内容

13 篇文章 0 订阅

订阅专栏

执行计划

什么是执行计划

使用Example关键字可以模拟优化器执行SQL查询语句，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

执行计划的作用

1）查看表的读取顺序
2）数据读取操作的操作类型
3）哪些索引可以使用
4）哪些索引被实际使用
5）表之间的引用
6）每张表有多少行被优化器查询

执行计划的语法

在SQL查询的前面加上EXPLAIN关键字就行
比如：EXPLAIN select * from table1；
重点的就是EXPLAIN后面你要分析的SQL语句

执行计划的详解

通过EXPLAIN关键分析的结果由以下列组成，接下来挨个分析每一个列。
在这里插入图片描述

ID列

ID列：描述select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序。
根据ID的数值结果可以分成以下三种情况：
ID相同：执行顺序由上至下
ID不同：如果是子查询，ID的序号会递增，ID值越大优先级越高，越先被执行
ID相同不同：同时存在

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `other_column` char(20) DEFAULT NULL,
  `col1` char(20) DEFAULT NULL,
  `col2` varchar(20) DEFAULT NULL,
  `col3` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_col1_col2_col3` (`col1`,`col2`,`col3`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `other_column` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `other_column` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1）ID相同
EXPLAIN SELECT t2.* FROM t1,t2,t3 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.other_column= ’ ';
在这里插入图片描述
ID列的值全为1，表示执行的顺序是从t2开始加载，依次是t3和t1
2）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值越大优先级越高，越先被执行。

3）ID相同又不同
EXPLAIN SELECT t2.* FROM
( SELECT t3.id FROM t3 WHERE t3.other_column = ‘’ ) s1,t2
WHERE s1.id = t2.id
在这里插入图片描述
id 如果相同，可以默认是同一组，从上往下顺序执行；在所有数据中，id值越大，优先级越高，越先执行。

Select_type列

select_type:查询的类型
是用于区别：普通查询、联合查询、子查询等的复杂查询
类型如下：
simple 简单的select查询，查询中不包含子查询或者union联合查询
EXPLAIN select * from t1；
最简单的select查询，查询中不包含子查询或者union；
在这里插入图片描述
primary 查询中若包含任何复杂的子部分，最外层查询则被标记为primary
subquery 在select或者where列表中包含了子查询
EXPLAIN SELECT t1.,( SELECT t2.id FROM t2 WHERE t2.id = 1 ) FROM t1;

derived 在from列表中包含的子查询会被标记为derived，mysql会递归执行这些子查询，把结果放在临时表里
EXPLAIN SELECT t1. FROM t1,( SELECT t2.* FROM t2 WHERE t2.id = 1 ) s2
WHERE t1.id = s2.id
在这里插入图片描述
union 若第二个select出现在union后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived
unionresult 从union表获取结果的select
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级别。
1）system和const：
system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计。
const：表示通过索引一次就能找到了
const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。
如果主键置于where列表中，Mysql就能将该查询转换为一个常量。
EXPLAIN SELECT * from (select * from t2 where id = 1) d1;
在这里插入图片描述
2）eq_ref:
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
EXPLAIN SELECT * from t1,t2 where t1.id = t2.id；

3）ref：
非唯一性索引扫描，返回匹配某个单独值的所有行。
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
在这里插入图片描述

EXPLAIN select col1 from t1 where col1 = ‘ac’；

4）range：
只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你where语句中出现了between、<、>、in等的查询，这种范围扫描索引比你全表扫描要好，因为只需要开始于索引的某一点、而结束于另一点，不用扫描全部数据。
EXPLAIN SELECT * FROM t1 WHERE id BETWEEN 30 AND 60;
EXPLAIN SELECT * FROM t1 WHERE id IN ( 1, 2 );
在这里插入图片描述

5）index：
当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的是索引库，而没有去查询数据。
EXPLAIN SELECT id FROM t1 ;

6）ALL：
FULL TABLE SCAN：将遍历的全表以找到匹配的行。

Possible_keys和Key

possible_keys:可能使用的key
key:实际中使用的索引。如果为null，表示实际中没有使用索引。
在这里插入图片描述
EXPLAIN SELECT id, col1,col2,col3 FROM t1 ;
其中key和possible_key都可以出现null的情况。

Key_len

key_len表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。在不损失精度的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的。
根据底层使用的不同存储引擎，受影响的行数这个指标可能是一个估计值，也可能是一个精确值。即使受影响的行数是一个估计值，但是key_len表示索引使用的字节数，根据这个值，就可以判断索引的使用情况，特别是组合索引的时候，判断所有的索引字段你是否都被查询用到。Char和varchar跟字符编码也有密切的关系，Latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。

1）字符类型
在这里插入图片描述
1、字符类型-索引字段为 char 类型+不可为 Null 时

explain select * from s1 where name=‘tan’;
name这一列为char（10），字符集utf-8占用3个字节；
keylen = 10 *3；
2、字符类型-索引字段为 char 类型+允许为 Null 时

explain select * from s2 where name=‘tan’;
name这一列为char（10），字符集utf-8占用3个字节，外加需要存入一个null值；
keylen = 10 3+1（null）结果为31；
3）索引字段为 varchar 类型+不可为 Null 时

explain select * from s3 where name=‘tan’;
Keylen=varchar（n）变长字段+不允许null=103+2=32

4)索引字段为 varchar 类型+允许为 Null 时
在这里插入图片描述

EXPLAIN SELECT * FROM s4 WHERE name = ‘tan’;
Keylen=varchar(n)变长字段+允许 Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

2）数值类型
在这里插入图片描述

CREATE TABLE `numberkeylen` (
  `c0` int(255) NOT NULL,
  `c1` tinyint(255) DEFAULT NULL,
  `c2` smallint(255) DEFAULT NULL,
  `c3` mediumint(255) DEFAULT NULL,
  `c4` int(255) DEFAULT NULL,
  `c5` bigint(255) DEFAULT NULL,
  `c6` float(255,0) DEFAULT NULL,
  `c7` double(255,0) DEFAULT NULL,
  PRIMARY KEY (`c0`),
  KEY `index_tinyint` (`c1`) USING BTREE,
  KEY `index_smallint` (`c2`),
  KEY `index_mediumint` (`c3`),
  KEY `index_int` (`c4`),
  KEY `index_bigint` (`c5`),
  KEY `index_float` (`c6`),
  KEY `index_double` (`c7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

3）日期和时间

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

Ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值。
在这里插入图片描述
EXPLAIN select * from s1 ,s2 where s1.id = s2.id and s1.name = ‘tan’;
由此可知s1.name的索引被使用，并且是一个常量值 tan；其中s2.id使用索引的列为s1.id

Rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
在这里插入图片描述

Extra

在这里插入图片描述

包含不适合在其他列中显示但是十分重要的额外信息。
1）using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。
Mysql中无法利用索引完成的排序操作称为“文件排序”。
当发现有using filesort后，实际上就是发现了可以优化的地方。
EXPLAIN select col1 from t1 where col1=‘ac’ order by col3
在这里插入图片描述
上图其实是一种索引失效的情况，可以看出查询中用到了联合索引，索引分别是col1，col2，col3；
EXPLAIN select col1 from t1 where col1=‘ac’ order by col2,col3

当我排序新增了个col2，发现using filesort 就没有了。

2）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

3）using index：是否用来覆盖索引
EXPLAIN select col2 from t1 where col1 = ‘ab’

如果同时出现usingwhere，表名索引被用来执行索引键值的查找；
EXPLAIN select col2 from t1
在这里插入图片描述
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作；
覆盖索引
覆盖索引也就是索引覆盖
就是select的数据列只用从索引中就能去获取，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所见的索引覆盖。

4）using where：表明使用了where过滤
5）using join buffer：使用了连接缓存
show VARIABLES like ‘%join_buffer_size%’
在这里插入图片描述
EXPLAIN select * from t1 JOIN t2 on t1.other_column = t2.other_column

6）impossible where :where子句的值总是false，不能用来获取任何元组
EXPLAIN select * from t1 where 1=2

EXPLAIN select * from t1 where t1.other_column =‘123’ and t1.other_column = ‘456’
在这里插入图片描述