> 科技资讯 > MySQL(十四)分析查询语句Explain 七千字总结

MySQL(十四)分析查询语句Explain 七千字总结

科技资讯 投稿 5800℃ 0 评论

MySQL(十四)分析查询语句Explain 七千字总结

分析查询语句：EXPLAIN

1概述

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

执行计划（它认为的最优数据检索方案毕竟是自动分析成的，所以不一定是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的输出列
列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应着一个唯一的id
select_type SELECT关键字对应查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能使用到的索引
key 实际使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询的时候，与索引列进行等值匹配的对象信息
rows 预估需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息
1 id

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

EXPLAIN SELECT * FROM s1

EXPLAIN SELECT * FROM s1 INNER JOIN s2

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

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

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

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

​ SELECT * FROM s1, s2 ON s1.key1 = s2.key2 WHERE s2.common_field = 'a'

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

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

UNION去重

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

但是我这里的临时表也有id = 3，看康师傅视频是没有的，是版本的问题吗？也就是还对中间表进行了select

UNION ALL不进行去重，则是：

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

小结：

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

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

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

select_type：

    SIMPLE：不包含UNION或者子查询的查询都算是SIMPLE类型

  • UNION、PRIMARY、UNION RESULT：对于包含UNION和UNION ALL的语句，它是由几个小的查询组成的，除了最左边的查询的select_type是PRIMARY，其余的均为UNION，而针对临时表的select则是UNION RESULT

  • SUBQUERY：如果包含子查询的查询语句不能够转化为semi-join的方式（即优化器将子查询优化为表连接），并且子查询不是相关子查询(即用到了外表的子查询，则该子查询的第一个select关键字代表的那个查询的select_type就是SUBQUERY

    explain select * from s1 where key1 in (select key1 from s2 or key3 = 'a'

    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'

  • 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'

    但是，为什么第一个子查询没有用到外表，也是DEPENDENT SUBQUERY呢？

    ​ 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

  • MATERIALIZED（物化）：当查询优化器在执行包含子查询语句的时候，选择将子查询之后与外层查询进行连接时，该子查询对应的select_type就是MATERIALIZED

    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2

    物化表，查询方式为MATERIALIZED

    这个和上面的非相关子查询有点像，后面添加了一个or key3 = 'a'，非相关子查询就变成物化表了？？？

    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 or key3 = 'a'
3 table

表名

    单表

EXPLAIN SELECT * FROM s1

EXPLAIN SELECT * FROM s1, s2

并且s2排在s1的前面，所以s2是驱动表，s1是被驱动表（并不能根据sql语句判断，因为sql的顺序有可能被优化器优化修改）

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

执行查询时的访问方法，又称访问类型，即这里的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

system是性能最高的情况

all，而InnoDB即使一条数据也是all

CREATE TABLE tt(i INT ENGINE=INNODB;
INSERT INTO tt VALUES(1;
EXPLAIN SELECT count(* FROM tt

  • const：当根据主键或者唯一的二级索引与常数进行等值匹配的时候，对单表的访问就是const，表示常数级别

    EXPLAIN SELECT * FROM s1 WHERE id = 10005;
EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

    EXPLAIN SELECT * FROM s1 WHERE key3 = 1006;

  • eq_ref：在连接查询的时候，如果被驱动表是通过主键或者唯一的二级索引等值匹配的方式进行访问的（如果主键或者唯一的二级索引是联合索引，则要求索引的每一列进行联合匹配），则对于该被驱动表的访问方式就是eq_ref

    EXPLAIN SELECT * from s1 INNER JOIN s2 WHERE s1.key2 = s2.key2

    其中ref表示查询的值已经被指定：即通过all方式查询的s1表指定的

  • ref：当通过普通的二级索引与常量进行等值匹配来查询某个表，对该表的访问方式可能是ref

    EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq';

  • ref_or_null：当通过普通的二级索引与常量进行等值匹配来查询某个表，当该值也可能是null值时，那么对该 表的访问方式可能就是ref_not_null

    EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq' OR key3 IS NULL;

  • index_merge：单表访问在某些情况下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询

    EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key2 = 123131

    相当于扫描两个索引树取出主键并取并集再回表的操作

    EXPLAIN SELECT * FROM s1 WHERE key1 = 'rCLXEg' AND key2 = 10036

  • 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'

  • range：如果使用索引获取某些范围区间的记录，就可能使用到range方法

    EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'

  • index：当可以使用索引覆盖，并且需要扫描全部的索引记录，该表的访问方法就是index

    EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'

    检索条件和select返回列都是和联合索引相关的列，所以使用了联合索引扫描了全部的索引记录，因为这样就不需要再回表找其他的列了（查的列都在索引上）

    不需要回表就能查找到所需要的数据，称作索引覆盖

    EXPLAIN SELECT key1, key_part2 FROM s1 WHERE key_part3 = 'a'

    结果为ALL，因为联合索引列上没有key1的信息，需要回表去查key1

  • all：全表扫描

    • 6 possible_key 和 key

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

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

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

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

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

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

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

    7 index_len 🌟（联合索引分析）

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

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

    EXPLAIN SELECT * FROM s1 WHERE id = 10005

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

    EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

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

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

    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';

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

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

    type是eq_ref（普通索引则为ref），同时等值比较的是s1的列，因此ref为atguigu1.s2.id

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

    type为ref，等值比较的类型是一个函数返回值，因此ref列的值为func

    9 rows 🌟

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

    10 filtered 🌟（结合rows分析）

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

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

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

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

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

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

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

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

    11 Extra🌟

    十二分重要的额外信息。通过这些额外信息可以更准确地知道mysql导致是怎么执行给定的查询语句的。

      no tables used：没有from字句，即没有用到表的情况

    EXPLAIN select 1

  • impossible where：where语句永远为false的情况

    EXPLAIN select * FROM s1 WHERE 1 != 1

  • where：使用全表扫描来执行针对某个表的查询，字句中有针对该表的搜索条件，则在Extra中展现

    EXPLAIN select * FROM s1 WHERE common_field = 'a'

    


    


    

  • no matching min/max row当查询列表处有min或者max聚合函数，但是没有符合where条件的记录时，将会提示该额外信息

     EXPLAIN SELECT MIN(key1 FROM s1 WHERE key1 = 'adqwdqweqwe'

    


    Select tables optimized away，表示选择优化后的表
    

    EXPLAIN SELECT MIN(key1 FROM s1

  • using index：当出现索引覆盖，即查询和检索条件的列都在使用的索引里面，也即是不需要回表操作的情况

    EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'

  • using index condition：即索引条件下推，考虑下面的sql查询：

    EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 like '%a%'

    


    ​	而索引条件下推针对特殊情况进行了优化：就是如果剩余的过滤条件针对的是索引列，则不需要在回表后进行判断，这样就能够减少回表的操作，但是rows仍为385

  • using join buffer：即基于块的嵌套循环算法：当被驱动表不能够有效利用索引加快访问速度，mysql就会为其在内存中分配一块join buffer的内存块来加快访问的速度

    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.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

    


    

    impossible where，就不会再看被驱动表了

  • using union(index_merge：or使用两个索引的情况，即前面type讲到的index_merge，这时候会将两个索引树查出的id取并集然后再回表在进行where条件过滤

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

  • zero limit：limit为0的情况

  • file sort 文件排序：

      EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

      文件排序 file sort

      但是这里有个地方很不理解，为什么去掉limit或者limit较大的时候，就会变成文件排序？

      EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 97;

      个人猜测：有一个地方需要注意，就是随着limit的增大rows也在增大，尤其是在limit为95左右的时候突然增大了很多，这是不是因为：limit较小的时候，通过索引顺序得到的主键值也比较集中，这时候回表操作也是顺序查询的级别，但是limit过大甚至没有的时候，主键值就会特别分散（因为是按照key1索引列排序的，所以key1集中而主键值分散），因此这时候回表读取操作相当于是随机查找的级别了，那这样查询优化器判断成本后，还不如直接在内存或者磁盘中进行文件排序。

    • EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

  • using temporary：mysql在进行一些如去重、排序的功能的时候，如果不能够有效地利用索引，就可能需要通过建立内部的临时表来完成。

    EXPLAIN SELECT DISTINCT common_field FROM s1;
    • 小结
      Explain不考虑Cache（不考虑记录的加载方式，只是考量sql语句）
    • Explain不能显示mysql在执行查询时做的优化工作
    • Explain不会显示关于触发器、存储过程或用户自定义函数对于查询的影响
    • 部分信息是估算的，并非精确的值
    Explain的进一步使用
    Explain的四种输出格式

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

    1 传统格式
    2 JSON格式

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

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

    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

    Show Warnings的使用

    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;

    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;

    编程笔记 » MySQL(十四)分析查询语句Explain 七千字总结

    赞同 (29) or 分享 (0)
    游客 发表我的评论   换个身份
    取消评论

    表情
    (0)个小伙伴在吐槽