MySQL分析查询语句Explain

最新推荐文章于 2024-09-26 18:00:26 发布
最新推荐文章于 2024-09-26 18:00:26 发布
阅读量242 收藏 1
点赞数
文章标签： 概率论 机器学习 人工智能
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41146932/article/details/132123899
版权
1概述

​ 定位了查询慢的SQL之后，就可以使用EXPLAIN或者DESCRIBE工具做针对性的分析查询。两者使用方法相同，并且分析结果也是相同的。

​ MySQL中有专门负责SQL语句优化的优化器模块，主要功能是计算分析系统中收集到的统计信息，为客户端请求的Query提供它最优的执行计划（它认为的最优数据检索方案毕竟是自动分析成的，所以不一定是DBA或者开发人员认为的最优方案）

​ 这个执行计划展示了接下来进行具体查询的方式，比如多表连接的顺序、对每个表采用什么方式进行具体的查询等等，MySQL提供的EXPLAIN语句可以用来查询某个查询语句的具体执行计划，根据EXPLAIN语句的输出项，可以有针对性地提升查询SQL的性能。

能查到什么？
  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以被使用
  • 哪些索引实际被使用
  • 表之间的引用关系
  • 每张表有多少行被优化器查询
版本区别
  • MySQL5.6.3之前只能使用EXPLAIN SELECT，之后可以使用EXPLAIN SELECT， UPDATE， DELETE
  • 5.7之前的版本，想要查看partitions（分区）和filtered需要使用EXPLAIN partitions、filtered，而5.7之后直接默认显示
数据准备
创建表
 

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2(key2),
INDEX idx_key3(key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
)ENGINE=INNODB CHARSET=utf8
CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2(key2),
INDEX idx_key3(key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
)ENGINE=INNODB CHARSET=utf8
 

创建存储函数
 

 

-- 函数返回随机字符串
DELIMITER //
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4
BEGIN 
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO 
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
 

首先要确保相信函数的变量log_bin_trust_function_creators为1
 

 

SELECT @@log_bin_trust_function_creators variable;
SET GLOBAL log_bin_trust_function_creators = 1;
 

存储过程
 

​ 向s1、s2表添加数据的存储过程
 

 

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string(6),
(min_num + 30* i + 5),
rand_string(6),
rand_string(10),
rand_string(5),
rand_string(10),
rand_string(10)
);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER;
DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string(6),
(min_num + 30* i + 5),
rand_string(6),
rand_string(10),
rand_string(5),
rand_string(10),
rand_string(10)
);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER;
 

执行存储过程添加数据
 

 

CALL insert_s1(10001, 10000);
CALL insert_s2(10001, 10000);
 

Explain的输出列
 

 

image-20230413142058178
 

列名描述
id在一个大的查询语句中每个SELECT关键字都对应着一个唯一的id
select_typeSELECT关键字对应查询的类型
table表名
partitions匹配的分区信息
type针对单表的访问方法
possible_keys可能使用到的索引
key实际使用的索引
key_len实际使用到的索引长度
ref当使用索引列等值查询的时候，与索引列进行等值匹配的对象信息
rows预估需要读取的记录条数
filtered某个表经过搜索条件过滤后剩余记录条数的百分比
Extra一些额外的信息
 

1 id
 

​ id，在一个大的查询语句中每个SELECT关键字都对应着一个唯一的id，所以有几个select关键字就会有几个id：
 

 

EXPLAIN SELECT * FROM s1
 

 

image-20230413152527504
 

 

EXPLAIN SELECT * FROM s1 INNER JOIN s2
 

 

image-20230413152550172
 

 
 
上面的两个SQL都只有一个select所以只有一个id
 

 

 

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'
 

 
 
子查询有两个select，所以对应两个id1和2
 

 

 

image-20230413153710803
 

🌟查询优化器可能会对涉及子查询的查询语句进行重写：
 

 

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a')
 

 

image-20230413154055995
 

 
 
优化器在看到子查询后判断能够变为多表连接以降低复杂度（O(n^2) -> O(n)）:
 
 
​ SELECT * FROM s1, s2 ON s1.key1 = s2.key2 WHERE s2.common_field = 'a'
 
 
重写后的sql变成了一个select，所以查询结果仍然是一个id
 

 

​ 但是如果s2查的是key1，就会变成下面这样：
 

 

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE common_field = 'a')
 

 

image-20230413154648592
 

🌟UNION去重
 

 

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 

 

image-20230413155716716
 

 
 
union由于去重操作会使用到中间表，所以会有一个table<union, 1, 2>
 
 
但是我这里的临时表也有id = 3，看康师傅视频是没有的，是版本的问题吗？也就是还对中间表进行了select
 

 

​ 如果使用的是UNION ALL不进行去重，则是：
 

 

EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
 

 

image-20230413160443889
 

小结：
 

  • id如果相同，则会被认为是同一组查询，会按照从上往下的顺序执行
  • 如果不同，则id越大的优先级越高，越先执行
  • id的号码表示一趟独立的查询，一个sql的查询趟数越少越好
 

2 select_type
 

​ 一个大的查询里面可以包含多个select关键字，每个select关键字代表一个小的查询语句，而每个小的查询中都包含着若干的表进行连接操作，而每一张表都对应着EXPLAIN查询计划的一条记录，对于在同一个select关键字的表来说，他们的id是相同的。
 

​ select_type：SELECT关键字对应查询的类型，即我们只要知道了某个小查询的select_type属性，就能知道这个小查询在大查询中扮演的角色、起到的作用
 

常见的select_type：
 

  •  
    SIMPLE：不包含UNION或者子查询的查询都算是SIMPLE类型
     
  •  
    UNION、PRIMARY、UNION RESULT：对于包含UNION和UNION ALL的语句，它是由几个小的查询组成的，除了最左边的查询的select_type是PRIMARY，其余的均为UNION，而针对临时表的select则是UNION RESULT
     
     
    image-20230413155716716
     
  •  
    SUBQUERY：如果包含子查询的查询语句不能够转化为semi-join的方式（即优化器将子查询优化为表连接），并且子查询不是相关子查询(即用到了外表的子查询)，则该子查询的第一个select关键字代表的那个查询的select_type就是SUBQUERY
     
     
  
    explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a'
     
    image-20230413191457400
     
    首先这个子查询不是相关子查询，那么这个sql能不能优化成表连接的sql呢？
     
     
  
    select * from s1 INNER JOIN s2 on s1.key1 = s2.key1
    答案是不能，这两个sql是不同的：比如s1表中有一个key1值，s2表有两个重复的key1值，则第一条语句由于是in，所以只会匹配一次，而第二条sql是等于号，所以这种情况下会匹配两次，从而二个sql得到的结果是完全不同的，因此这个sql会用到两个select，也就出现两个id了，一个select为Primary，子查询的select为subquery。
     
  •  
    DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转化为semi-join的方式，但是子查询涉及到了外表，也就是为相关子查询，那么该子查询的第一个select关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 from s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'
     
    image-20230413192644328
     
  
     
   
    select_type 为 DEPENDENT SUBQUERY 的查询可能会被执行多次
     
  
     
  •  
    DEPENDENT UNION：在包含UNION和UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的小查询之外，其余查询的select_type均为DEPENDENT UNION
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b')
     
    image-20230413193342501
     
  
     
   
    第二个子查询UNION加上了DEPENDENT 好理解，因为用到了外表
     
   
    但是，为什么第一个子查询没有用到外表，也是DEPENDENT SUBQUERY呢？
     
   
    这是由于优化器对于in的改动：
     
   
    ​ where exists (s1.key1 = s2.key1 ...)，这样就变为了相关子查询，至于为啥这么做完全不知道了。。
     
  
     
  •  
    DERIVED：派生表对应子查询的select_type为DERIVED
     
     
  
    EXPLAIN SELECT * FROM (SELECT key1, count(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1
     
    image-20230413194529416
     
  
     
   
    <drived2>即为id为2的派生表
     
  
     
  •  
    MATERIALIZED（物化）：当查询优化器在执行包含子查询语句的时候，选择将子查询之后与外层查询进行连接时，该子查询对应的select_type就是MATERIALIZED
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2)
     
    image-20230413195516194
     
  
     
   
    SELECT key1 FROM s2的结果是一个个的记录然后与外表进行连接，则这些记录就可以被称作是物化表，查询方式为MATERIALIZED
     
   
    而外层select直接将子查询成的物化表看做普通的表，查询方式为SIMPLE
     
  
     
    这个和上面的非相关子查询有点像，后面添加了一个or key3 = 'a'，非相关子查询就变成物化表了？？？
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) or key3 = 'a'
     
    image-20230413191457400
     
 

3 table
 

​ table，即表名
 

  •  
    查询出来的每一行记录都对应着一个单表
     
     
  
    EXPLAIN SELECT * FROM s1
     
    image-20230413152527504
     
     
  
    EXPLAIN SELECT * FROM s1, s2
     
    image-20230413152550172
     
  
     
   
    可以看到两个记录的id是一样的，因为属于同一个大的查询语句（只有一个select）
     
   
    并且s2排在s1的前面，所以s2是驱动表，s1是被驱动表（并不能根据sql语句判断，因为sql的顺序有可能被优化器优化修改）
     
  
     
 

4 partitions
 

  • 代表分区表中的命中情况，非分区表，该值为NULL，一般情况下我们查询语句执行计划的partitions列的值也都是NULL
 

​
 

image-20230413200812010
 

5 type 🌟
 

​ 执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称访问类型，即这里的type。比如，type是ref，表名mysql将使用ref方法对改行记录的表进行查询。
 

​ 完整的访问方法如下：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all，越靠前代表效率越高
 

​ SQL性能优化的目标：至少要达到range级别，要求是ref级别，最好是const级别。
 

  •  
    system：当表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system
     
     
  
    CREATE TABLE t(i INT) ENGINE=MYISAM;
    INSERT INTO t VALUES(1);
    EXPLAIN SELECT * FROM t
     
    image-20230413202000688
     
  
     
   
    存储引擎的统计数据是精确的，意思是例如MyISAM存储存储引擎有记录的记录的个数
     
   
    system是性能最高的情况
     
  
     
    而如果再添加一条记录，会变为all，而InnoDB即使一条数据也是all
     
     
    image-20230413202320906
     
    于此同时，INNODB访问count()的数据也是all的
     
     
  
    CREATE TABLE tt(i INT) ENGINE=INNODB;
    INSERT INTO tt VALUES(1);
    EXPLAIN SELECT count(*) FROM tt
     
    image-20230413205132920
     
  •  
    const：当根据主键或者唯一的二级索引与常数进行等值匹配的时候，对单表的访问就是const，表示常数级别
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE id = 10005;
    EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
     
    image-20230413210639838
     
    如果是key3，则为all
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key3 = 1006;
     
    image-20230413210652051
     
  
     
   
    这里其实牵扯到隐式转换导致索引失效的问题：由于key3是varchar类型的，但是这里是数字进而进行了函数转换，进而索引失效只能all查询了
     
  
     
  •  
    eq_ref：在连接查询的时候，如果被驱动表是通过主键或者唯一的二级索引等值匹配的方式进行访问的（如果主键或者唯一的二级索引是联合索引，则要求索引的每一列进行联合匹配），则对于该被驱动表的访问方式就是eq_ref
     
     
  
    EXPLAIN SELECT * from s1 INNER JOIN s2 WHERE s1.key2 = s2.key2
     
    image-20230413211429876
     
  
     
   
    key2是带有唯一约束的二级索引，因此被驱动表s2的访问方式为eq_ref
     
   
    其中ref表示查询的值已经被指定：即通过all方式查询的s1表指定的
     
  
     
  •  
    ref：当通过普通的二级索引与常量进行等值匹配来查询某个表，对该表的访问方式可能是ref
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq';
     
    image-20230413212234596
     
  
     
   
    这里key3就是没有唯一约束的普通索引，可以看到用到了索引key3，因此type为ref
     
  
     
  •  
    ref_or_null：当通过普通的二级索引与常量进行等值匹配来查询某个表，当该值也可能是null值时，那么对该 表的访问方式可能就是ref_not_null
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq' OR key3 IS NULL;
     
    image-20230413212613015
     
  •  
    index_merge：单表访问在某些情况下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key2 = 123131
     
    image-20230413213029544
     
  
     
   
    key1和key2均为索引列，一个Select关键字只能使用一个索引，所以这里使用了合并索引为一个虚拟索引的办法，相当于扫描两个索引树取出主键并取并集再回表的操作
     
  
     
    但是，如果是AND的情况，只会使用一个索引（这里是唯一的二级索引。故而是const）
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 = 'rCLXEg' AND key2 = 10036
     
    image-20230413213831202
     
  •  
    unique_subquery：是针对一些包含IN子查询的查询语句中，如果查询优化器决定将In子查询语句变为EXISTS子查询，并且子查询可以使用到主键的等值匹配的话，那么子查询的type就是unique_subquery
     
     
  
    EXPLAIN SELECT * FROM s1
    WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a'
     
    image-20230413214650160
     
  •  
    range：如果使用索引获取某些范围区间的记录，就可能使用到range方法
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c')
     
    image-20230413214919640
     
    非索引列则为all
     
  •  
    index：当可以使用索引覆盖，并且需要扫描全部的索引记录，该表的访问方法就是index
     
     
  
    EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'
     
    image-20230413215536216
     
  
     
   
    可以看到在key里面还是用到了联合索引的，尽管根据最左前缀原则，只有检索条件为key_part1才能用到索引，这里是因为检索条件和select返回列都是和联合索引相关的列，所以使用了联合索引扫描了全部的索引记录，因为这样就不需要再回表找其他的列了（查的列都在索引上）
     
  
     
  
     
   
    不需要回表就能查找到所需要的数据，称作索引覆盖
     
  
     
    这时候再添加一个其他的列：
     
     
  
    EXPLAIN SELECT key1, key_part2 FROM s1 WHERE key_part3 = 'a'
     
  
     
   
    结果为ALL，因为联合索引列上没有key1的信息，需要回表去查key1
     
  
     
  •  
    all：全表扫描
     
 

6 possible_key 和 key
 

​ 在EXPLAIN语句输出的执行计划中，possible_key 表示在单表查询中可能会用到的索引，一般查询涉及到的字段上存在索引，则该索引就将被列出，但不一定被查询使用。
 

​ key则表示经过查询优化器计算使用不同索引的查询成本之后，最终确定使用的索引。
 

 

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'
 

 

image-20230414101530039
 

 
 
key1和key3均为普通的二级索引，但是key3是等值匹配因此耗费的成本较低，所以最终选择使用索引key3
 

 

 

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' OR key3 = 'a'
 

 
 
而如果这里改成OR，则会演变成之前讲的 index_merge 合并索引，即将两个索引树的主键提取取并集，然后统一到聚簇索引中执行一次回表操作
 

 

 

image-20230414101830623
 

 

EXPLAIN SELECT key1, key3 FROM s1 WHERE key1 > 'z' OR key3 = 'a'
 

 
 
再扩展一下，即使查询列可以使用覆盖索引（即查询列的值都可以在索引树中找到），仍然需要进行一次回表操作，因此两个查询的执行计划是相同的：
 

 

 

image-20230414102257106
 

7 index_len 🌟（联合索引分析）
 

​ 实际使用到的索引的长度（即字节数），用来查看是否充分利用了索引，index_len的值越大越好
 

 
 
这里的越大越好是跟自己进行的比较，因为主要是针对的联合索引，因为利用联合索引的长度越大，查询需要读入的数据页就越少，效率也就越高
 

 

 

EXPLAIN SELECT * FROM s1 WHERE id = 10005
 

 

image-20230414103528727
 

 
 
为什么是4：因为id列是int型所以真实数据占4个字节，同时行格式中主键非空因此不需要NULL值列表，定长不需要变长字段长度列表，故而是4
 

 

 

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
 

 

image-20230414104516060
 

 
 
key2是int类型，占4个字节，并且具有唯一性约束但是可能为空，因此行格式中null值列表占1个字节，总共5个字节
 

 

 

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 

 
 
首先key1是varchar(100)，并且表是utf8mb3格式的，因此真实数据存储占(100 * 3) = 300个字节，本身定长所以行格式的变长字段长度列表占2个字节，NULL值列表占1个字节，共计303个字节
 

 

同理下面的查询一个为303，另一个是606，这时候才体现出key_len的作用：第二个sql比第一个sql利用联合索引更加充分
 

 

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
 

8 ref
 

​ ref表示当我们使用索引列等值查询的时候，与索引列进行等值匹配的对象的信息。
 

 

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 

 

image-20230414110557413
 

 
 
key1是普通的二级索引，所以type是ref（唯一的二级索引是const），而等值的匹配类型是一个常量，因此ref列的值是const
 

 

 

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
 

 

image-20230414111603202
 

 
 
由于是表连接，所以只有一个select id，然后由于是主键进行的连接，所以对于第二个表的访问方式type是eq_ref（普通索引则为ref），同时等值比较的是s1的列，因此ref为atguigu1.s2.id
 

 

 

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
 

 

image-20230414111724590
 

 
 
key1是普通的二级索引，因此type为ref，等值比较的类型是一个函数返回值，因此ref列的值为func
 

 

9 rows 🌟
 

​ rows：预估需要读取的记录条数，值越小越好
 

 
 
值越小表示在同一个数据页中的可能性越大，IO的次数也就越少
 

 

10 filtered 🌟（结合rows分析）
 

​ filtered：表示某个表经过条件过滤之后，剩余记录条数的百分比，值越大越好
 

 

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
 

 

image-20230414112551508
 

 
 
如上表示经过条件过滤后，100%都是符合要求的
 
 
值越大越好的原因：假设条件过滤后是40条记录，如果filtered是100%，则原来有40条，如果filtered是10%，则原来有400条，相比之下40条需要读取的数据页要少一些
 

 

​ 而如果执行的是索引的单表扫描，那么计算的时候除了估计出满足对应索引的搜索条件，还应计算同时满足其他条件的记录是多少条
 

 

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'b';
 

 

image-20230414130019004
 

 
 
如上面的sql，rows303表示预估满足索引列key1需要读取的记录数，而filtered表示加上common_field字段后预估读取占全部的百分比
 

 

​ 🌟 对于单表查询这个filtered列其实没有太大作用，但是它在多表连接中驱动表对应的执行计划记录的filtered值，决定了被驱动表的执行次数。
 

 

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
 

 

image-20230414130633288
 

 
 
首先多表连接查询所以为同一个select id，其次连接条件是普通的二级索引，所以驱动表的访问类型type为all，被驱动表的访问类型type为ref，最后s1表预估读取的记录数rows为10152，再经过条件过滤10152 * 10%和s2做等值匹配，因此1015就是s2表的执行次数
 

 

11 Extra🌟
 

​ Extra用来说明一些不适合在其他列中展示但是十二分重要的额外信息。通过这些额外信息可以更准确地知道mysql导致是怎么执行给定的查询语句的。
 

  •  
    no tables used：没有from字句，即没有用到表的情况
     
     
  
    EXPLAIN select 1
     
    image-20230414132310640
     
  •  
    impossible where：where语句永远为false的情况
     
     
  
    EXPLAIN select * FROM s1 WHERE 1 != 1
     
    image-20230414132438010
     
  
     
   
    这样也没有用到表，反正条件都不对
     
  
     
  •  
    where：使用全表扫描来执行针对某个表的查询，字句中有针对该表的搜索条件，则在Extra中展现
     
     
  
    EXPLAIN select * FROM s1 WHERE common_field = 'a'
     
    image-20230414134955822
     
  
     
   
    common_field是一个不带索引的普通字段，因此type为all，Extra展现了语句是通过where执行的
     
  
     
  •  
    no matching min/max row当查询列表处有min或者max聚合函数，但是没有符合where条件的记录时，将会提示该额外信息
     
     
  
    EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'adqwdqweqwe'
     
    image-20230414135835116
     
    而当where条件符合（或者根本没有where条件的时候），则显示Select tables optimized away，表示选择优化后的表
     
     
  
    EXPLAIN SELECT MIN(key1) FROM s1
     
    image-20230414140049336
     
  •  
    using index：当出现索引覆盖，即查询和检索条件的列都在使用的索引里面，也即是不需要回表操作的情况
     
     
  
    EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'
     
    image-20230414140409229
     
  
     
   
    当出现主键的情况也是覆盖索引
     
  
     
  •  
    using index condition：即索引条件下推，考虑下面的sql查询：
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 like '%a%'
     
    image-20230414141228852
     
    ​ 这条sql执行的正常顺序应该是：首先使用idx_key1的索引树，查询key1 > z的所有主键值，这里找到了385条记录的主键，然后对这些主键进行回表操作，在聚簇索引中找到包含其他列的数据，然后判断剩下的过滤条件进行返回。
     
    ​ 而索引条件下推针对特殊情况进行了优化：就是如果剩余的过滤条件针对的是索引列，则不需要在回表后进行判断，这样就能够减少回表的操作，但是rows仍为385
     
  •  
    using join buffer：即基于块的嵌套循环算法：当被驱动表不能够有效利用索引加快访问速度，mysql就会为其在内存中分配一块join buffer的内存块来加快访问的速度
     
     
  
    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field
     
    image-20230414142120715
     
  
     
   
    common_field是一个没有索引的列
     
  
     
  •  
    not exists：在表连接的时候，当where条件中被驱动表的某个列等于null，而这个列又有非空约束的时候，Extra就会展现not exists
     
     
  
    EXPLAIN SELECT * FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.id IS NULL
     
    image-20230414142638304
     
  
     
   
    注意一定是被驱动表的列，如果是主驱动表出现这种情况，会直接显示为impossible where，就不会再看被驱动表了
     
  
     
  •  
    using union(index_merge)：or使用两个索引的情况，即前面type讲到的index_merge，这时候会将两个索引树查出的id取并集然后再回表在进行where条件过滤
     
     
  
    EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'
     
    image-20230414143026121
     
  •  
    zero limit：limit为0的情况
     
  •  
    file sort 文件排序：
     
  
    •  
      有一些情况排序是能够用到索引的：
       
       
    
      EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
       
      image-20230414144853029
       
      这个查询利用idx_key1索引直接取出key1列的10条记录（按照索引列排序的），然后再拿着记录的主键值进行回表得到全部列的值。但是更多情况下的排序操作无法利用到索引，只能在内存中（记录较少的情况）或者磁盘中进行排序，mysql把这种在内存或者磁盘中排序的方式统称为文件排序 file sort
       
      但是这里有个地方很不理解，为什么去掉limit或者limit较大的时候，就会变成文件排序？
       
       
    
      EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 97;
       
      image-20230414145427834
       
      个人猜测：有一个地方需要注意，就是随着limit的增大rows也在增大，尤其是在limit为95左右的时候突然增大了很多，这是不是因为：limit较小的时候，通过索引顺序得到的主键值也比较集中，这时候回表操作也是顺序查询的级别，但是limit过大甚至没有的时候，主键值就会特别分散（因为是按照key1索引列排序的，所以key1集中而主键值分散），因此这时候回表读取操作相当于是随机查找的级别了，那这样查询优化器判断成本后，还不如直接在内存或者磁盘中进行文件排序。
       
    •  
      对于没有索引的查询，自然只能文件排序了：
       
       
    
      EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
       
      image-20230414150229592
       
  •  
    using temporary：mysql在进行一些如去重、排序的功能的时候，如果不能够有效地利用索引，就可能需要通过建立内部的临时表来完成。
     
     
  
    EXPLAIN SELECT DISTINCT common_field FROM s1;
     
    image-20230414150617598
     
  
     
   
    执行计划中出现临时表不是一个很好的征兆，因为建立和维护临时表都需要很大的成本，应该尽量通过使用索引来替换临时表
     
  
     
 

 

小结
 

  • Explain不考虑Cache（不考虑记录的加载方式，只是考量sql语句）
  • Explain不能显示mysql在执行查询时做的优化工作
  • Explain不会显示关于触发器、存储过程或用户自定义函数对于查询的影响
  • 部分信息是估算的，并非精确的值
 

 

Explain的进一步使用
 

Explain的四种输出格式
 

​ Explain的四种输出格式：传统格式、Json格式、Tree格式、可视化格式
 

1 传统格式
 

​ 即上面一直在使用的EXPLAIN语句，概要说明查询计划
 

2 JSON格式
 

​ 传统的EXPLAIN语句的输出缺少了一个衡量执行计划好坏的重要属性--成本。JSON格式是四种格式里面信息最详尽的格式，包含了执行的成本信息。 接下来对比一下传统和JSON格式的EXPLAIN：
 

 

EXPLAIN SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'
 

 

image-20230414152305899
 

 

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'
 

 

{
"query_block": {
"select_id": 1, // 原来的id
"cost_info": {
"query_cost": "1394.77" // 查询成本
},
"nested_loop": [
{
"table": {
"table_name": "s1", // table
"access_type": "ALL", // type
"possible_keys": [
"idx_key1"
],
"rows_examined_per_scan": 10152, // rows
"rows_produced_per_join": 1015, // rows * filtered
"filtered": "10.00",
"cost_info": {
"read_cost": "937.93",
"eval_cost": "101.52",
"prefix_cost": "1039.45", // read + eval
"data_read_per_join": "1M" // 读取的数据量
},
"used_columns": [ // 查询字段
"id",
"key1",
"key2",
"key3",
"key_part1",
"key_part2",
"key_part3",
"common_field"
],
"attached_condition": "((`atguigudb1`.`s1`.`common_field` = 'a') and (`atguigudb1`.`s1`.`key1` is not null))" // 查询条件
}
},
{
"table": {
"table_name": "s2",
"access_type": "eq_ref",
"possible_keys": [
"idx_key2"
],
"key": "idx_key2",
"used_key_parts": [
"key2"
],
"key_length": "5",
"ref": [
"atguigudb1.s1.key1"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1015,
"filtered": "100.00",
"index_condition": "(cast(`atguigudb1`.`s1`.`key1` as double) = cast(`atguigudb1`.`s2`.`key2` as double))",
"cost_info": {
"read_cost": "253.80",
"eval_cost": "101.52",
"prefix_cost": "1394.77",
"data_read_per_join": "1M"
},
"used_columns": [
"id",
"key1",
"key2",
"key3",
"key_part1",
"key_part2",
"key_part3",
"common_field"
]
}
}
]
}
}
 

  • read_cost：由两部分组成： 
  
    • IO成本
    • rows * (1 - filtered)条记录的CPU成本
  • eval_cost： rows * filtered
 

3 Tree格式
 

​ Tree格式是8.0.16版本之后引入的新格式，主要根据各个部分之间的关系和各个部分的执行顺序来描述如何查询。
 

 

EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'
 

 

-> Nested loop inner join (cost=1394.77 rows=1015)
-> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1039.45 rows=1015)
-> Table scan on s1 (cost=1039.45 rows=10152)
-> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1)
 

4 可视化输出
 

​ 需要安装MySQL workbench
 

 

image-20230414153738475
 

Show Warnings的使用
 

​ 当我们使用Explain语句查看了某个查询语句的执行计划之后，紧接着还可以使用Show warnings来查看与这个查询计划有关的一些扩展信息，比如：
 

 

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
 

 

image-20230414154744913
 

​ 正常来说，我们使用s2 left join s1，那么s2应该是驱动表，s1是被驱动表，但是可以看到执行计划中实际上是反着的，这是由于优化器在判断两个表作为驱动表的执行成本的时候对sql进行的优化（where语句是针对的s2），使用show warnings可以看到这种优化：
 

 

mysql> show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `atguigudb1`.`s1`.`key1` AS `key1`,`atguigudb1`.`s2`.`key1` AS `key1` from `atguigudb1`.`s1` join `atguigudb1`.`s2` where ((`atguigudb1`.`s1`.`key1` = `atguigudb1`.`s2`.`key1`) and (`atguigudb1`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)
 

​ 看着挺别扭，即下面：
 

 
 

select s1.key1, s2.key1
from s1 join s2
where s1.key1 = s2.key1 and s2.common_field is not null;

                

        

        

    

  


    

      

        

            

              
                
                  qq_41146932
                
              
            

            

                关注
              
            

        

        

          
      

      









                



	

		

			

				
					
mysql查询优化之explain使用(一)

				
			

			

				

					h15637811412的博客
				

				

					08-16
					
					392
					
				

			

		

		

			
				
explain的所有字段

说明本次数据库版本5.5.49的，因为在不同数据库版本之间，同样的表，同样的查询语句，explain结果是不同的。



前期准备，建三个简单表

用户表 user_info （user_id,dept_id,name）

部门表 user_dept （dept_id,name）

用户详情表 user_info_detail（id,user_id,detail...

			
		

	



                

            
              



	

		

			

				
					
MySQL——分析查询语句：EXPLAIN

				
			

			

				

					xiaowanziddd的博客
				

				

					07-12
					
					913
					
				

			

		

		

			
				
定位了查询慢的SQL之后，我们就可以使用EXPLAIN或 DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。MysQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的这部分最耗费时间)。这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具

			
		

	



              


  
              

                


	

		

			

				
					
explain的使用

				
			

			

				

					shshyeagle的专栏
				

				

					06-30
					
					776
					
				

			

		

		

			
				
  在对某个Query优化过程中，须要不断地使用explain来验证各种调整是否有效。  MySQL explain功能展示的各种信息的解释如下：id： MySQL Query Optimizer 选定的执行计划中查询的序列号。select_type： 所使用的查询类型，主要有以下这几种查询类型。DEPENDENT SUBQUERY：子查询内层的第一个select，依赖于外部查

			
		

	





	

		

			

				
					
MySql Explain优化命令使用（数据库索引优化查询）

					
最新发布

				
			

			

				

					zhzjn的博客
				

				

					09-26
					
					1121
					
				

			

		

		

			
				
EXPLAIN 是一个用于获取 SQL 语句执行计划的命令，用于帮助理解查询的执行过程以及如何优化。这里主要是索引优化。

			
		

	



		

			
		



	

		

			

				
					
mysql慢查询原因分析与解决（二）——explain分析查询

				
			

			

				

					Mr__fang的专栏
				

				

					11-06
					
					3197
					
				

			

		

		

			
				
原文：http://www.javaranger.com/archives/1476




使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
– 表的读取顺序
– 数据读取操作的操作类型
– 哪些索引可以使用
– 哪些索引被实际使用
– 

			
		

	





	

		

			

				
					
MySQL高级篇——EXPLAIN分析查询语句

				
			

			

				

					★★光亭★★
				

				

					04-13
					
					8366
					
				

			

		

		

			
				
MySQL高级篇——EXPLAIN分析查询语句

			
		

	





	

		

			

				
					
Mysql 查询语句分析 explain 详解

				
			

			

				

					weixin_43452467的博客
				

				

					02-20
					
					248
					
				

			

		

		

			
				
explain:
可以模拟sql优化器执行sql语句，从而让开发人员知道自己的sql情况
优化方法 官网：optimization.html

explain使用:
explain + sql 语句

select t.* from teacher t,course c,teacherCart tc where …explain的类型分析:

① id:
id值相同，从上往下顺序执行；查询表顺序，表数据条数由少到多执行（笛卡尔积）
id不同，从内查到外层，id值越大越先查询
id相同又有不同，先大后小，相同

			
		

	





	

		

			

				
					
Mysql语句性能分析-EXPLAIN

				
			

			

				

					12-14
				

			

		

		

			
				
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 如下面SQL语句： EXPLAIN select * from system_area where status = 1  获得...

			
		

	





	

		

			

				
					
mysql的explain分析sql语句性能(慢查询和加没加索引)

				
			

			

				

					06-29
				

			

		

		

			
				
MySQL 的 Explain 分析 SQL 语句性能（慢查询和加没加索引）  MySQL 的 Explain 分析 SQL 语句性能是数据库性能优化的重要工具之一。通过 Explain 命令可以分析 SQL 语句的执行计划，了解 SQL 语句的执行过程，找出...

			
		

	





	

		

			

				
					
MySQL查询语句过程和EXPLAIN语句基本概念及其优化

				
			

			

				

					09-09
				

			

		

		

			
				
`EXPLAIN`语句是MySQL提供的一种性能分析工具，它能显示查询的执行计划，帮助开发者了解MySQL是如何处理SQL语句的。`EXPLAIN`的输出包括多列信息，其中最重要的是`type`列，它揭示了MySQL是如何连接各个表的。`const...

			
		

	





	

		

			

				
					
mysql开启慢查询(EXPLAIN SQL语句使用介绍)

				
			

			

				

					12-16
				

			

		

		

			
				
当我们去设计数据库表结构，对操作数据库时（尤其是查表时的SQL语句），我们都需要注意数据操作的性能。 1、开启慢查询 1> 查看慢查询是否开启   show variables like "%quer%"; slow_query_log = ON #已开启 2> ...

			
		

	





	

		

			

				
					
mysql之explain分析

				
			

			

				

					乐乐的博客
				

				

					04-20
					
					1113
					
				

			

		

		

			
				
在这里对explain的各个字段进行详细的分析，来帮助大家分析自己所写的sql是否最佳的使用了索引。
首先是select_type:将select查询分为简单(simple)和复杂两种类型
复杂类型又分为子查询(subquery)和from列表中包含子查询(drived)
simple:


SELECT * FROM tp_lifecircle_consume WHERE order_sn = ...

			
		

	





	

		

			

				
					
explain分析查询

				
			

			

				

					u012899746的博客
				

				

					04-11
					
					332
					
				

			

		

		

			
				
explain分析查询使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到: 
– 表的读取顺序 
– 数据读取操作的操作类型 
– 哪些索引可以使用 
– 哪些索引被实际使用 
– 表之间的引用 
– 每张表有多少行被优化器查询EXPLAIN字段： 
Table：显

			
		

	





	

		

			

				
					
MySQL—Explain分析

				
			

			

				

					影子
				

				

					10-10
					
					631
					
				

			

		

		

			
				
2、Explain
EXPLAIN可以帮助开发人员分析SQL问题，EXPLAIN显示了MySQL如何使用使用SQL执行计划，可以帮助开发人员写出更优化的查询语句。使用方法，在select语句前加上Explain就可以了。
EXPLAIN可以帮助开发人员分析SQL问题，EXPLAIN显示了MySQL如何使用使用SQL执行计划，可以帮助开发人员写出更优化的查询语句。使用方法，在select语句前加上Explain就可以了。
EXPLAIN可以帮助开发人员分析SQL问题，EXPLAIN显示了MySQL如何使用使用

			
		

	





	

		

			

				
					
mysql explain

				
			

			

				

					CAO的专栏
				

				

					03-27
					
					750
					
				

			

		

		

			
				
有必要解释一下这个长长的表格里每一列的含义：



id
SELECT识别符。这是SELECT的查询序列号


select_type


SELECT类型,可以为以下任何一种:

SIMPLE:简单SELECT(不使用UNION或子查询)PRIMARY:最外面的SELECTUNION:UNION中的第二个或后面的SELECT语句DEPENDENT UNION:UNION

			
		

	





	

		

			

				
					
详解 MYSQL 中 EXPLAIN 用法

				
			

			

				

					PJ码匠人
				

				

					12-07
					
					4457
					
				

			

		

		

			
				
执行顺序4：id列为1，表示是UNION里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为,表示查询结果来自一个衍生表，其中derived3中的3代表该查询衍生自第三个select查询，即id为3的select。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。子查询得到结果的d1临时表，子查询条件为id = 1 常量，type是const，id为1的相当于只查询一条记录，type为system。

			
		

	





	

		

			

				
					
分析查询语句 EXPLAIN 详解

				
			

			

				

					小怪的博客
				

				

					02-20
					
					6680
					
				

			

		

		

			
				
EXPLAIN 详解一、概述二、基本语法三、数据准备1. 建表2. 设置参数 log_bin_trust_function_creators3. 创建函数4. 创建存储过程5. 调用存储过程四、EXPLAIN各列作用1. table2. id3. select_type4 .partitions (可略)5.type ☆6. possible_keys和key7. key_len ☆8. ref9. rows ☆10. filtered11. Extra ☆12. 小结五、EXPLAIN的进一步使用1、 E

			
		

	





	

		

			

				
					
简单查询语句的分析

				
			

			

				

					cymyell的专栏
				

				

					10-15
					
					396
					
				

			

		

		

			
				
查询语句格式:SELECT [DISTINCT] {column1,column2,…}FROM tablename WHERE {conditions}GROUP BY {conditions}ORDER BY {expressions} [ASC/DESC];注：distinct 会触发排序 SELECT 是用来选择字段，一般不推荐使用"*"来表示全部字段，而是逐一

			
		

	





	

		

			

				
					
MySQL Explain 分析与查询优化

				
			

			

				

					
				

			

		

		

			
				
在数据库管理中，理解查询性能至关重要，`EXPLAIN`是MySQL提供的一种工具，用于分析和优化`SELECT`语句的执行效率。使用`EXPLAIN`，我们可以得知查询执行的详细步骤，识别可能存在的问题，比如是否有效利用了索引，...

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  





	
打赏作者

	

		

		
			
		
		

		

			
qq_41146932

			
你的鼓励将是我创作的最大动力

		

	

	

			

	

	

    ¥1
    ¥2
    ¥4
    ¥6
    ¥10
    ¥20
	

	

		

			

				

					扫码支付：¥1
				

				

					

					
					获取中
					

				

				

					
					
					扫码支付
				

			

		

		

			
您的余额不足，请更换扫码支付或充值

			
打赏作者

		

	



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值