性能调优2. Mysql的Explain详解（学习笔记）

jsliucode

已于 2023-11-08 16:41:02 修改

阅读量54

点赞数

文章标签： mysql 学习笔记

于 2023-11-04 19:12:35 首次发布

本文链接：https://blog.csdn.net/xingwanganfang/article/details/134075501

版权

1. 前言准备

1.1. 派生表（衍生表）

‌‌‌　　如下面SQL语句，子查询放在FROM后面，这个子查询的结果相当于一个派生表，表的名称为t。


	select * from (select a, b from t1) as t;

1‌‌.2. Mysql的模式

1.2.1. 查看当前模式

‌‌‌　　在会话中执行下面语句可以查看MySQL设置的模式。


‌‌‌　　SELECT @@GLOBAL.sql_mode;
‌‌‌　　或用
‌‌‌　　show variables like 'sql_mode';

在这里插入图片描述

1.2.2. ONLY_FULL_GROUP_BY模式

‌‌‌　　MySQL 5.7.5之后版本默认启用了ONLY_FULL_GROUP_BY模式。该模式要求在使用GROUP BY时，SELECT列表中的每一列都必须要么出现在GROUP BY子句中，或者是一个聚合函数的参数。
‌‌‌　　(‌‌‌常见的聚合函数包括：
‌‌‌　　COUNT：用于计算符合指定条件的行数。
‌‌‌　　SUM：用于计算指定列的总和。
‌‌‌　　AVG：用于计算指定列的平均值。
‌‌‌　　MAX：用于获取指定列的最大值。
‌‌‌　　MIN：用于获取指定列的最小值。）

‌‌‌　　例如

‌‌‌　　先用DDL语句创建表。

‌‌‌　　CREATE TABLE `tb_score` (
  `id` int NOT NULL,
  `stu_name` varchar(255) DEFAULT NULL,
  `course_name` varchar(255) DEFAULT NULL,
  `score` int DEFAULT NULL,
	 PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

‌‌‌　　下面SQL语句执行报错。


‌‌‌　　SELECT course_name,score
‌‌‌　　FROM
‌‌‌　　tb_score
‌‌‌　　GROUP BY
‌‌‌　　course_name

‌‌‌　　报错：
SELECT course_name,score
FROM tb_score GROUP BY course_name
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mytest.tb_score.score' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> 时间: 0.002s

‌‌‌　　查询字段都在GROUP BY里头，不报错。


‌‌‌　　SELECT course_name,score FROM tb_score GROUP BY course_name,score

‌‌‌　　查询的字段不在GROUP BY里头，在聚合函数里头，不报错。


‌‌‌　　SELECT course_name,MAX(score) FROM tb_score GROUP BY course_name

‌‌‌

1.2.2.1. 关闭ONLY_FULL_GROUP_BY模式

‌‌‌　　
‌‌‌　　　1. 只关闭当前会话方式

‌‌‌　　在会话中，设置模式属性的值，去掉ONLY_FULL_GROUP_BY就完事了。


‌‌‌　　SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

‌‌‌　　或者替换该字符为空
	SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

‌‌‌　　　2. 永久关闭的方式

‌‌‌　　Windows下修改my.ini，Linux下修改my.cnf。对应文件添加配置，重启MySQL生效。


	[mysqld]
‌‌‌　　sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

‌‌‌1.3. Mysql的derived_merge特性

‌‌‌　　MySql5.7引入了新的特性derived_merge(派生类合并)。

‌‌‌　　当derived_merge参数被开启时，MySQL优化器会尝试将派生表与父查询合并成一个查询，以减少查询的执行时间和资源消耗（派生表会创建临时表占用资源等）。但是，在某些情况下，合并操作可能会导致错误的结果。
‌‌‌　　
例如

‌‌‌　　先用DDL语句创建表。

‌‌‌　　CREATE TABLE `tb_score` (
  `id` int NOT NULL,
  `stu_name` varchar(255) DEFAULT NULL,
  `course_name` varchar(255) DEFAULT NULL,
  `score` int DEFAULT NULL,
	 PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

‌‌‌　　插入数据。


‌‌‌　　INSERT INTO `tb_score`(`id`, `stu_name`, `course_name`, `score`) VALUES (1, '张三', '数学', 90);
‌‌‌　　INSERT INTO `tb_score`(`id`, `stu_name`, `course_name`, `score`) VALUES (2, '李四', '语文', 94);
‌‌‌　　INSERT INTO `tb_score`(`id`, `stu_name`, `course_name`, `score`) VALUES (3, '张三', '语文', 98);
‌‌‌　　INSERT INTO `tb_score`(`id`, `stu_name`, `course_name`, `score`) VALUES (4, '李四', '数学', 97);
‌‌‌　　INSERT INTO `tb_score`(`id`, `stu_name`, `course_name`, `score`) VALUES (5, '李四', '英语', 99);
‌‌‌　　INSERT INTO `tb_score`(`id`, `stu_name`, `course_name`, `score`) VALUES (6, '张三', '英语', 100);

在这里插入图片描述

‌‌‌　　查询

‌‌‌　　select stu_name,course_name,max(score) from tb_score group by course_name;

在这里插入图片描述
‌‌‌　　显然与表中的正确数据不匹配，group by 后显示的列只会根据所有组的第一行来显示。

‌‌‌　　改成正确语句如下。


# 注意ORDER BY 不要对字符串排序有问题

‌‌‌　　SELECT t.stu_name,t.course_name,max( t.score ) FROM
	( SELECT stu_name, course_name, score FROM tb_score ORDER BY score DESC ) t GROUP BY t.course_name

‌‌‌　　执行结果还是不正确。

在这里插入图片描述
　借助执行计划，然后查看优化器优化的sql语句(执行计划后面会说到)，下面语句要一起执行不能分开。


‌‌‌　　explain SELECT t.stu_name,t.course_name,max( t.score ) FROM
	( SELECT stu_name, course_name, score FROM tb_score ORDER BY score DESC ) t GROUP BY t.course_name;
	
‌‌‌　　show warnings;

‌‌‌　　可以看到派生表合并到外部查询中。因为derived_merge开启导致派生类合并，导致子查询不生效。


‌‌‌　　/* select#1 */ select `mytest`.`tb_score`.`stu_name` AS `stu_name`,`mytest`.`tb_score`.`course_name` AS `course_name`,max(`mytest`.`tb_score`.`score`) AS `max( t.score )` from `mytest`.`tb_score` group by `mytest`.`tb_score`.`course_name`

‌‌‌　　查看当前optimizer_switch设置的值，里头可以查看derived_merge是否开启。


‌‌‌　　SHOW VARIABLES LIKE 'optimizer_switch';

在这里插入图片描述
‌‌‌　　会话中关闭derived_merge


    #关闭mysql5.7新特性对衍生表的合并优化
    
‌‌‌　　set session optimizer_switch='derived_merge=off';

‌‌‌　　永久关闭derived_merge

‌‌‌　　Windows下修改my.ini，Linux下修改my.cnf。对应文件添加配置，重启MySQL生效。

	[mysqld]
	
‌‌‌　　optimizer_switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

‌‌‌　　关闭derived_merge后再次查询，数据正确。


# 注意ORDER BY 不要对字符串排序有问题

‌‌‌　　SELECT t.stu_name,t.course_name,max( t.score ) FROM
	( SELECT stu_name, course_name, score FROM tb_score ORDER BY score DESC ) t GROUP BY t.course_name

在这里插入图片描述

‌‌‌　　官网手册说到

在这里插入图片描述

‌‌‌　　也有多种办法不需要修改 derived_merge 参数而使合并派生类失效。

	可以通过在子查询中使用任何阻止合并的构造来禁用合并，
	尽管这些构造对实现的影响并不明确。 防止合并的构造对于派生表和视图引用是相同的：
	
   1.聚合函数（ SUM() ， MIN() ， MAX() ， COUNT()等）
   2.DISTINCT
   3.GROUP BY
   4.HAVING
   5.LIMIT
   6.UNION或UNION ALL
   7.选择列表中的子查询
   8.分配给用户变量
   9.仅引用文字值（在这种情况下，没有基础表）

‌‌‌　　参考文档

‌‌‌　　https://blog.csdn.net/undefinedAuth/article/details/99291739

‌‌‌

2. MySql的Explain工具

2.1. Explain工具介绍

‌‌‌　　MySQL默认提供性能分析工具，可以获取SQL语句的执行计划，分析使用了什么索引，扫描了多少行，以此来判断结构性能瓶颈，是否可以优化等。

‌‌‌　　用法

‌‌‌　　在 SELECT 语句之前增加 EXPLAIN 关键字，MySQL 会在查询上设置一个标记，执行查询时会返回执行计划的信息，而不是执行该条 SQL。

‌‌‌　　如


‌‌‌　　EXPLAIN 你的sql语句

在这里插入图片描述

‌‌‌　　注意

‌‌‌　　1. 如果 SQL 语句中的 FROM 中包含子查询，仍会执行该子查询，将结果放入临时表中。

‌‌‌　　2. 不同版本的 MySQL Explain 执行结果可能有微小差异，只需了解大概意思即可。

‌‌‌2.2. Explain分析示例

‌‌‌　　Explain 分析要结合当前存储引擎，组织表数据和索引结构进行分析。接下来的操作都是基于 MySQL 的 InnoDB 存储引擎。

2.2.1. 示例

‌‌‌　　1. 创建示例数据


DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

‌‌‌　　2. 查看执行计划


‌‌‌　　explain select * from actor;

在这里插入图片描述

2.2.2. 查看优化后的查询语句

‌‌‌　　explain extended(MySQL5.7 以后就不需要加extended，直接explain就行)。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。


‌‌‌　　explain extended select * from film where id = 1;
‌‌‌　　show warnings;

	# MySql5.7 以后
‌‌‌　　explain select * from film where id = 1;
‌‌‌　　show warnings;

‌‌‌　　Message里头是优化后的sql语句(不一定能直接执行sql语句，只是说明这样优化了)。

在这里插入图片描述

2.3. Explain 两个变种命令

‌‌‌　　MySQL 5.7及以后这两种命令被explain取代，直接使用explain就已经包含了该效果。

2.3.1. explain extended

‌‌‌　　会在 explain 的基础上额外提供一些查询优化的信息。

‌‌‌　　SQL语句执行前，MySQL优化器会进行优化。紧随其后，通过使用show warnings命令可以得到优化后的查询语句，从而可以看出优化器优化了什么（返回的优化后的SQL语句未必能直接执行）。

‌‌‌　　额外还有 filtered 列，是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。


‌‌‌　　explain extended select * from film where id = 1;

在这里插入图片描述

‌‌‌　　查看优化的sql语句。


‌‌‌　　explain extended select * from film where id = 1;

‌‌‌　　show warnings;

在这里插入图片描述

2.3.2. explain partitions

‌‌‌　　相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

2.4. Explain中的列说明

2.4.1. id列

‌‌‌　　id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

‌‌‌　　id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

2.4.2. select_type列

‌‌‌　　表示对应sql语句是简单还是复杂的查询。

‌‌‌　　1. simple：简单查询，查询不包含子查询和union。


‌‌‌　　explain select * from film where id = 2;

在这里插入图片描述

‌‌‌　　2. primary：复杂查询中最外层的select。

‌‌‌　　3. subquery：在select后的子查询（不在 from后的查询）。

‌‌‌　　4. derived：包含在from后的查询，MySQL会将结果存放在一个临时表中，也称为派生表(衍生表)（derived的英文含义）。

‌‌‌　　用个例子来了解 primary、subquery 和 derived 类型


	# 关闭mysql5.7新特性对衍生表的合并优化
	
‌‌‌　　set session optimizer_switch='derived_merge=off';

‌‌‌　　执行下面语句


	# 查询字段是常量值，则结果固定是常量值
	
‌‌‌　　explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

‌‌‌　　执行计划如下
在这里插入图片描述

‌‌‌　　解释

‌‌‌　　1. table：sql语句查询的表。

‌‌‌　　2. id：可以看成sql语句执行的优先级。

‌‌‌　　3. select_type：

‌‌‌　　3.1. subquery，子查询，对应select (select 1 from actor where id = 1) ，在select后的查询语句(select 1 from actor where id = 1) 叫子查询，不创建临时表。

‌‌‌　　3.2. derived，衍生查询，from (select * from film where id = 1)，在from 后的查询语句(select * from film where id = 1)，叫衍生查询。会创建临时表(衍生表)。

‌‌‌　　3.3. PRIMARY，复杂查询中最外层的select的查询，这种查询关联其它子查询，select
(select 1 from actor where id = 1) from (select * from film where id = 1) der，最外层的select就是复杂查询。
其table值为<derived3>表示从衍生表去查询，derived对应衍生查询的select_type的值，3对应其id的值。

‌‌‌　　5. union：在 union 中的，第二个或者随后的select的查询。

‌‌‌　　explain select 1 union all select 1;

在这里插入图片描述

‌‌‌　　UNION是复合查询。指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。

‌‌‌　　UNION规则：

‌‌‌　　UNION必须由两条以上的SELECT语句组成，语句之间用关键字UNION分割。

‌‌‌　　UNION中的每个查询必须包含相同的列、表达式或聚集函数（各个列不需要以相同的次序列出）。

‌‌‌　　列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型。

‌‌‌　　如果取出来的数据不需要去重，使用UNION ALL。

‌‌‌　　参考

‌‌‌　　https://blog.csdn.net/qq_33704186/article/details/81909276

2.4.3. table列

‌‌‌　　这一列表示 explain 的一行正在访问哪个表。

‌‌‌　　当 from 子句中有子查询时，table列是 <derivedN> 格式，表示查询数据来源是，select_type值为derived，id是N的衍生查询。

‌‌‌　　当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的select 行id。

2.4.4. type列

‌‌‌　　这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
‌‌‌　　依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。sql语句查询需要扫描的范围越大，效率递减。

‌‌‌　　一般来说，得保证查询达到range级别，最好达到ref。

‌‌‌　　1. NULL

‌‌‌　　MySQL能够在优化阶段分解查询语句，在执行阶段无需再访问表或索引。

‌‌‌　　例如

‌‌‌　　在索引列中选取最小值，直接取索引根节点数据返回，不需要在执行时访问表。


‌‌‌　	explain select min(id) from film;

在这里插入图片描述

‌‌‌　　
‌‌‌　　2. const，system

‌‌‌　　const：MySQL能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。


‌‌‌　　explain select * from film where id = 1;
‌‌‌　　SHOW WARNINGS;

在这里插入图片描述

‌‌‌　　system：是const的特例，表里只有一条元组匹配时为system。

‌‌‌

‌‌‌　　explain  select * from (select * from film where id = 1) tmp;

‌‌‌　　
在这里插入图片描述

‌‌‌　　提示

‌‌‌　　primary key：代表主键。

‌‌‌　　unique key：代表唯一索引，字典值不重复，创建如下。

在这里插入图片描述

‌‌‌　　3. eq_ref

‌‌‌　　primary key 或 unique key 索引的所有部分被连接使用，**最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

‌‌‌　　film_actor的类型是All，而film的类型是eq_ref。这是因为根据Join的NLJ算法(后面索引优化会说到)，film_actor是驱动表，每行数据需要磁盘扫描，去被驱动表film走索引树匹配数据。

‌‌‌　　explain select * from film_actor left join film on film_actor.film_id = film.id;

在这里插入图片描述

‌‌‌　　4. ‌‌‌ref

‌‌‌　　相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

‌‌‌　　1. 简单 select 查询，name是普通索引（非唯一索引）。

‌‌‌　　explain select * from film where name = 'film1';

在这里插入图片描述

‌‌‌　　2. 关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。


‌‌‌　　explain select film_id from film left join film_actor on film.id = film_actor.film_id;

在这里插入图片描述

‌‌‌　　3. 这种查询都是从索引数据根节点开始查询，效率会快。

5. range

‌‌‌　　范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行，借助索引排好序获取指定范围数据。

‌‌‌　　这种直接从索引叶节点开始查找数据。


‌‌‌　　explain select * from actor where id > 1;

在这里插入图片描述

‌‌‌　　6‌‌‌. index

‌‌‌　　1. 扫描全索引(没有使用算法)就能拿到结果，一般是扫描某个二级(普通)索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级(普通)索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引(后面索引优化文章会说)，二级(普通)索引一般比较小，所以这种通常比ALL快一些。

‌‌‌　　2. 遍历叶节点的数据，需要触发IO操作一个个取出节点比对。

‌‌‌　　3. 如果查询数据，在主键索引或者普通索引(二级)里头都能直接拿到。则优先走普通索引(覆盖索引下)，因为普通索引数据叶节点的数据量更小，查找更快，且覆盖索引下直接能返回查找数据，不需要回表查找等操作取完整数据。

‌‌‌　　当然也不是绝对，也有可能走普通索引，回表查数据或者走主键索引直接取完整数据，给看MySQL优化器计算成本走哪个(后面文章会说)。

 	explain select * from film;

在这里插入图片描述

‌‌‌　　7. ALL

‌‌‌　　即全表扫描，扫描你的聚簇索引(InnoDB存储引擎下就是主键索引)的所有叶节点，数据量大。通常情况下这需要增加索引来进行优化了。

‌‌‌　　explain select * from actor;

在这里插入图片描述

2.4.5. possible_keys列

‌‌‌　　这一列显示查询可能使用哪些索引来查找。

‌‌‌　　explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。

‌‌‌　　如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

2.4.6. key列

‌‌‌　　这一列显示MySQL实际采用哪个索引来优化对该表的访问。

‌‌‌　　如果没有使用索引，则该列是 NULL。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

2.4.7. key_len列

‌‌‌　　这一列显示了MySQL在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

‌‌‌　　例如，film_actor表的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列(最左前缀原理)，film_id列来执行索引查找。

 	explain select * from film_actor where film_id = 2;

在这里插入图片描述

‌‌‌　　索引字段的key_len计算规则如下

‌‌‌　　字符串类型：

‌‌‌　　1. char(n)：如果存汉字长度就是 3n 字节。

‌‌‌　　2. varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串。

‌‌‌　　注意

‌‌‌　　1. 对于字符串char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数。

‌‌‌　　2. 如果存储字符串数据使用utf-8编码，对于一个数字或字母占1个字节，一个汉字占3个字节。如果使用utf8mb4，对于一个数字或字母占1个字节，一个汉字占3个字节，一个生僻汉字、冷门符号、emoji表情符号等需要4字节表示。

‌‌‌　　3. utf-8在mysql存储引擎中也叫utf8mb3，最多3字节表示一个字符。对于生僻汉字、冷门符号、emoji表情符号等需要4字节表示一个字符的，则需要使用到utf8mb4。

‌‌‌　　4. 索引的值，最大长度是768字节，当索引值的字符串过长时，MySQL会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

‌‌‌　　数值类型：

‌‌‌　　1. tinyint：1字节。

‌‌‌　　2. smallint：2字节。

‌‌‌　　3. int：4字节。

‌‌‌　　4. bigint：8字节　。

‌‌‌　　时间类型：

‌‌‌　　1. date：3字节。

‌‌‌　　2. timestamp：4字节。

‌‌‌　　3. datetime：8字节。

‌‌‌　　4. 如果字段允许为 NULL，需要再用1字节记录是否为 NULL。这种情况索引字段计算字节会再加1字节。

2.4.8. ref列

‌‌‌　　这一列显示了在key列记录的索引中，查找条件的值的类型，用到是列或常量，常见的有：const（常量），字段名（数据库名.表名.字段名）。

‌‌‌　　explain select * from actor where name = '2';

在这里插入图片描述

2.4.9. rows列

‌‌‌　　这一列是MySQL预估要读取并检测的行数，注意这个不是结果集里返回的行数，而且也只是预估值。

2.4.10. filtered 列

‌‌‌　　该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指explain 中的id值比当前表id值小的表）。

2.4.11. Extra列

2.4.11.1. 前言

‌‌‌　　有时Extra列会出现多个值，这些值组合主要意思不变。比如 Using where 和Using index同时出现，主要意思还是查询列全在索引中不需要回表查找。Using where表示使用where过滤数据，Using index 表示使用覆盖索引避免回表。
在这里插入图片描述

2.4.11.2. Using index 使用覆盖索引

‌‌‌　　覆盖索引定义：MySQL执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值（回表查找）。


 	EXPLAIN select film_id from film_actor where film_id = 1

在这里插入图片描述

2.4.11.3. Using where

‌‌‌　　使用 where 语句来处理结果，并且查询的列未被索引覆盖。


‌‌‌　　EXPLAIN select * from actor where name = 'a';

在这里插入图片描述

2.4.11.4. Using index condition

‌‌‌　　查询的列不完全被索引覆盖，where条件存在是一个前导列(联合索引的前导列)的范围或者单值索引的范围查找。


‌‌‌　　EXPLAIN select * from film_actor where film_id > 1;

在这里插入图片描述

2.4.11.5. Using temporary

‌‌‌　　MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

‌‌‌　　actor表的name字段没有索引，此时创建了张临时表来distinct。将数据取出到临时表去重。


‌‌‌　　EXPLAIN select distinct name from actor

在这里插入图片描述

‌‌‌　　film表的name建立了idx_name索引，此时查询时extra是using index,没有用临时表。就是通过扫描索引边数据去重，因为索引是排好序的。


‌‌‌　　EXPLAIN select distinct name from film

‌‌‌　　ffilm表的name建立了idx_name索引，此时查询时extra是using index。没有用临时表，通过扫描叶节点的索引数据去重，借助索引排好序。


‌‌‌　　EXPLAIN select distinct name from film

在这里插入图片描述

2.4.11.6. Using filesort(文件排序)

‌‌‌　　将用外部排序而不用索引排序，数据较小时完全加载到内存排序，否则需要借助磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。（后面文章会讲文件排序）

‌‌‌　　例如：文件排序的<sort_key, rowid>模式下，actor表的name字段未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name，根据排序后name对应id回表一个个查找完整数据返回。

‌‌‌　　explain select * from actor order by name;

在这里插入图片描述

‌‌‌　　film表的name字段，建立了idx_name索引,此时查询时extra是using index。因为索引是排好序的，此时type是index，直接从叶节点从左到右取数据就行。

 	explain select * from film order by name;

在这里插入图片描述

2.4.11.7. Select tables optimized away

‌‌‌　　使用某些聚合函数（比如 max、min）来访问存在索引的某个字段，通过索引就能实现聚合函数效果（比如直接取索引根节点数据返回就行根本不需要优化）。这边其实是用到索引的，但是key没有指明使用的索引。


‌‌‌　　explain select min(id) from film;

‌‌‌　　在这里插入图片描述

jsliucode

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
性能调优2. Mysql的Explain详解（学习笔记）

‌‌‌　　MySQL默认提供性能分析工具，可以获取SQL语句的执行计划，分析使用了什么索引，扫描了多少行，以此来判断结构性能瓶颈，是否可以优化等。‌‌‌用法‌‌‌　　在 SELECT 语句之前增加 EXPLAIN 关键字，MySQL 会在查询上设置一个标记，执行查询时会返回执行计划的信息，而不是执行该条 SQL。‌‌‌　　如‌‌‌ EXPLAIN 你的 sql 语句‌‌‌注意‌‌‌　　1. 如果 SQL 语句中的 FROM 中包含子查询，仍会执行该子查询，将结果放入临时表中。
复制链接

扫一扫