• mysql中EXPLAIN命令解析

    1. 前期数据准备

    1.1 创建新数据库CSDN

    为了测试explain命令，新创建数据库CSDN，直接调用下面这条语句即可

    create database CSDN

    • 1

    执行完成后可以看到多出来了这个数据库
    在这里插入图片描述

    1.2 创建测试用表

    在CSDN数据库里新建两张表，用来进行测试，建表语句如下

    --测试用表 s1
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;

--测试用表 s2
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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    1.3 准备测试数据

    为了模拟实际工作，需要往s1 s2两张表里放几百万条数据，所以需要创建出大量数据，可以通过函数配合存储过程实现

    --创建函数
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
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 ;

--创建往s1表中插入数据的存储过程
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_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

--创建往s2表中插入数据的存储过程
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 s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62

    创建好之后，就可以执行存储过程，向s1 s2两张表里各放10万条数据

    --向s1表里放10万条数据
CALL insert_s1(100001,100000);

--向s2表里放10万条数据
CALL insert_s2(100001,100000);

    • 1
    • 2
    • 3
    • 4
    • 5

    表里的数据基本长这样
    在这里插入图片描述

    2. EXPLAIN 用法

    2.1 EXPLAIN 基本含义

    如果需要看某个查询的执行计划，可以在具体的查询语句前边加一个EXPLAIN，比如

    explain select count(1) from s2

    • 1

    就会得到输出为

    id|select_type|table|partitions|type |possible_keys|key     |key_len|ref|rows |filtered|Extra      |
--+-----------+-----+----------+-----+-------------+--------+-------+---+-----+--------+-----------+
 1|SIMPLE     |s2   |          |index|             |idx_key2|5      |   |99620|   100.0|Using index|

    • 1
    • 2
    • 3

    输出的各个列的含义如下

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

    2.2 EXPLAIN 各字段作用

    2.2.1 table

    EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称），比如explain select count(1) from s1就会得到

    id|select_type|table|partitions|type |possible_keys|key     |key_len|ref|rows |filtered|Extra      |
--+-----------+-----+----------+-----+-------------+--------+-------+---+-----+--------+-----------+
 1|SIMPLE     |s1   |          |index|             |idx_key2|5      |   |99183|   100.0|Using index|

    • 1
    • 2
    • 3

    输出的table字段就是s1表

    2.2.2 id

    可以简单理解为一个select就会对应1个id，比如说EXPLAIN SELECT * FROM s1 INNER JOIN s2;虽然用到了两张表，输出的结果也有两条记录，但id却都是1

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra                                |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+-------------------------------------+
 1|SIMPLE     |s1   |          |ALL |             |   |       |   |99183|   100.0|                                     |
 1|SIMPLE     |s2   |          |ALL |             |   |       |   |99620|   100.0|Using join buffer (Block Nested Loop)|

    • 1
    • 2
    • 3
    • 4

    如果将内关联换成子查询EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';就会发现输出的结果里，会有两个id，正对应着sql里的两个select

    id|select_type       |table|partitions|type          |possible_keys|key     |key_len|ref |rows |filtered|Extra      |
--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+
 1|PRIMARY           |s1   |          |ALL           |idx_key3     |        |       |    |99183|   100.0|Using where|
 2|DEPENDENT SUBQUERY|s2   |          |index_subquery|idx_key1     |idx_key1|303    |func|    1|   100.0|Using index|

    • 1
    • 2
    • 3
    • 4

    但万事不绝对，比如EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a');这条语句，也有两个select，但输出的结果里id却都是1

    id|select_type|table|partitions|type|possible_keys|key     |key_len|ref         |rows |filtered|Extra                             |
--+-----------+-----+----------+----+-------------+--------+-------+------------+-----+--------+----------------------------------+
 1|SIMPLE     |s1   |          |ALL |idx_key1     |        |       |            |99183|   100.0|Using where                       |
 1|SIMPLE     |s2   |          |ref |idx_key2     |idx_key2|5      |CSDN.s1.key1|    1|    10.0|Using index condition; Using where|

    • 1
    • 2
    • 3
    • 4

    这个原因是虽然我们写的sql是子查询，但解析器进行解析的时候会将其进行优化，将它改成内关联，导致输出的结果里只有1个id，如果想看优化后的逻辑，就不用在这篇文章里找了，因为我也不知道去哪看。
    除此之外，执行EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;也会比预想的多一个id，虽然只有2个select，但却会输出3个id，第3个id为空

    id|select_type |table     |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra          |
--+------------+----------+----------+----+-------------+---+-------+---+-----+--------+---------------+
 1|PRIMARY     |s1        |          |ALL |             |   |       |   |99183|   100.0|               |
 2|UNION       |s2        |          |ALL |             |   |       |   |99620|   100.0|               |
  |UNION RESULT|<union1,2>|          |ALL |             |   |       |   |     |        |Using temporary|

    • 1
    • 2
    • 3
    • 4
    • 5

    这是因为union有去重的功能，相当于在两个select之后，又进行了一个去重的操作，就对应第3个id，也可以通过第三条记录的table字段看出来，表示的就是这一步用到的表是第一步和第二步结合的结果。
    如果这个地方换成union all那就和预期一致了，因为没有去重的功能EXPLAIN SELECT * FROM s1 UNION all SELECT * FROM s2;输出的结果就只有2条记录

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra|
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+-----+
 1|PRIMARY    |s1   |          |ALL |             |   |       |   |99183|   100.0|     |
 2|UNION      |s2   |          |ALL |             |   |       |   |99620|   100.0|     |

    • 1
    • 2
    • 3
    • 4

    关于id字段的理解，有下面3点

    1. id如果相同，可以认为是一组，从上往下顺序执行
    2. 在所有组中，id值越大，优先级越高，越先执行
    3. 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

    2.2.3 select_type

    这个字段的常见种类有以下几种

    名称描述
    SIMPLESimple SELECT (not using UNION or subqueries)
    PRIMARYOutermost SELECT
    UNIONSecond or later SELECT statement in a UNION
    UNION RESULTResult of a UNION
    SUBQUERYFirst SELECT in subquery
    DEPENDENT SUBQUERYFirst SELECT in subquery, dependent on outer query
    DEPENDENT UNIONSecond or later SELECT statement in a UNION, dependent on outer query
    DERIVEDDerived table
    MATERIALIZEDMaterialized subquery
    UNCACHEABLE SUBQUERYA subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
    UNCACHEABLE UNIONThe second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

    说白了，这个字段就是每个select对应的类型
    比如EXPLAIN SELECT * FROM s1;输出的select_type就会是SIMPLE

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra|
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+-----+
 1|SIMPLE     |s1   |          |ALL |             |   |       |   |99183|   100.0|     |

    • 1
    • 2
    • 3

    连接查询也是一样EXPLAIN SELECT * FROM s1 INNER JOIN s2;

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra                                |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+-------------------------------------+
 1|SIMPLE     |s1   |          |ALL |             |   |       |   |99183|   100.0|                                     |
 1|SIMPLE     |s2   |          |ALL |             |   |       |   |99620|   100.0|Using join buffer (Block Nested Loop)|

    • 1
    • 2
    • 3
    • 4

    而在使用union的时候EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;就会看到第一条记录的select_type是PRIMARY，第二条是UNION，而去重的一步就是UNION RESULT

    id|select_type |table     |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra          |
--+------------+----------+----------+----+-------------+---+-------+---+-----+--------+---------------+
 1|PRIMARY     |s1        |          |ALL |             |   |       |   |99183|   100.0|               |
 2|UNION       |s2        |          |ALL |             |   |       |   |99620|   100.0|               |
  |UNION RESULT|<union1,2>|          |ALL |             |   |       |   |     |        |Using temporary|

    • 1
    • 2
    • 3
    • 4
    • 5

    使用子查询EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';就会看到子查询的那条记录，会是DEPENDENT SUBQUERY，表示子查询的表和主表没有关联字段

    id|select_type       |table|partitions|type          |possible_keys|key     |key_len|ref |rows |filtered|Extra      |
--+------------------+-----+----------+--------------+-------------+--------+-------+----+-----+--------+-----------+
 1|PRIMARY           |s1   |          |ALL           |idx_key3     |        |       |    |99183|   100.0|Using where|
 2|DEPENDENT SUBQUERY|s2   |          |index_subquery|idx_key1     |idx_key1|303    |func|    1|   100.0|Using index|

    • 1
    • 2
    • 3
    • 4

    使用EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');会得到DEPENDENT UNION

    id|select_type       |table     |partitions|type|possible_keys|key     |key_len|ref  |rows |filtered|Extra                   |
--+------------------+----------+----------+----+-------------+--------+-------+-----+-----+--------+------------------------+
 1|PRIMARY           |s1        |          |ALL |             |        |       |     |99183|   100.0|Using where             |
 2|DEPENDENT SUBQUERY|s2        |          |ref |idx_key1     |idx_key1|303    |const|    1|   100.0|Using where; Using index|
 3|DEPENDENT UNION   |s1        |          |ref |idx_key1     |idx_key1|303    |const|    1|   100.0|Using where; Using index|
  |UNION RESULT      |<union2,3>|          |ALL |             |        |       |     |     |        |Using temporary         |

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    如果是嵌套查询EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;就会得到DERIVED

    id|select_type|table     |partitions|type |possible_keys|key     |key_len|ref|rows |filtered|Extra      |
--+-----------+----------+----------+-----+-------------+--------+-------+---+-----+--------+-----------+
 1|PRIMARY    |<derived2>|          |ALL  |             |        |       |   |99183|   33.33|Using where|
 2|DERIVED    |s1        |          |index|idx_key1     |idx_key1|303    |   |99183|   100.0|Using index|

    • 1
    • 2
    • 3
    • 4

    剩下几种，感觉平时没怎么接触过，就没列出来了

    2.2.4 partitions

    分区作用，感觉在mysql里不怎么用到，倒是hive里会使用分区

    2.2.5 type

    此字段表示访问类型，共有以下几种system,const,eq_ref,ref,fulltext,ref_or_null,index_merge,unique_subquery,index_subquery,range,index,ALL
    执行效果按顺序越来越差，system最好，ALL最差

    2.2.5.1 system

    这个类型效率最高，但没啥实际意义，只能是一条记录的表里进行查询才能是这个类型

    CREATE TABLE t(i int) Engine=MyISAM;

INSERT INTO t VALUES(1);

    • 1
    • 2
    • 3

    先建一张表，再往里面插一条数据EXPLAIN SELECT * FROM t;，在这种情况下解析执行计划就能看到type是system

    id|select_type|table|partitions|type  |possible_keys|key|key_len|ref|rows|filtered|Extra|
--+-----------+-----+----------+------+-------------+---+-------+---+----+--------+-----+
 1|SIMPLE     |t    |          |system|             |   |       |   |   1|   100.0|     |

    • 1
    • 2
    • 3

    但如果再往这张表里插一条数据INSERT INTO t VALUES(2);，此时在解析的话，就只能是ALL了

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
--+-----------+-----+----------+----+-------------+---+-------+---+----+--------+-----+
 1|SIMPLE     |t    |          |ALL |             |   |       |   |   2|   100.0|     |

    • 1
    • 2
    • 3
    2.2.5.2 const

    如果根据主键、普通唯一索引列等值匹配查询（is null除外），就会被定义为const，这种查询速度很快，查询速率认为是常数级别的，比如EXPLAIN SELECT * FROM s1 WHERE id = 100002;，就会得到

    id|select_type|table|partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
 1|SIMPLE     |s1   |          |const|PRIMARY      |PRIMARY|4      |const|   1|   100.0|     |

    • 1
    • 2
    • 3
    2.2.5.3 eq_ref

    在进行表关联时，被驱动表会使用这种方式进行访问，比如EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;，优化器会将s1作为驱动表去关联s2，关联的字段又是索引，所以输出就会是eq_ref

    id|select_type|table|partitions|type  |possible_keys|key    |key_len|ref       |rows |filtered|Extra|
--+-----------+-----+----------+------+-------------+-------+-------+----------+-----+--------+-----+
 1|SIMPLE     |s1   |          |ALL   |PRIMARY      |       |       |          |99183|   100.0|     |
 1|SIMPLE     |s2   |          |eq_ref|PRIMARY      |PRIMARY|4      |CSDN.s1.id|    1|   100.0|     |

    • 1
    • 2
    • 3
    • 4
    2.2.5.4 ref

    和const类似，如果使用普通索引直接进行筛选，就会得到这个类型EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';，输出的结果会是

    id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra|
--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
 1|SIMPLE     |s1   |          |ref |idx_key1     |idx_key1|303    |const|   1|   100.0|     |

    • 1
    • 2
    • 3
    2.2.5.5 fulltext

    感觉在实际工作里，也没怎么遇见过这种类型的查询

    2.2.5.6 ref_or_null

    在ref的基础上，增加一个条件就会得到这个类型EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

    id|select_type|table|partitions|type       |possible_keys|key     |key_len|ref  |rows|filtered|Extra                |
--+-----------+-----+----------+-----------+-------------+--------+-------+-----+----+--------+---------------------+
 1|SIMPLE     |s1   |          |ref_or_null|idx_key1     |idx_key1|303    |const|   2|   100.0|Using index condition|

    • 1
    • 2
    • 3
    2.2.5.7 index_merge

    这种类型实际就是使用联合索引EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

    id|select_type|table|partitions|type       |possible_keys    |key              |key_len|ref|rows|filtered|Extra                                      |
--+-----------+-----+----------+-----------+-----------------+-----------------+-------+---+----+--------+-------------------------------------------+
 1|SIMPLE     |s1   |          |index_merge|idx_key1,idx_key3|idx_key1,idx_key3|303,303|   |   2|   100.0|Using union(idx_key1,idx_key3); Using where|

    • 1
    • 2
    • 3
    2.2.5.8 unique_subquery

    使用唯一索引子查询的类型就会是这种EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';

    id|select_type       |table|partitions|type           |possible_keys   |key    |key_len|ref |rows |filtered|Extra      |
--+------------------+-----+----------+---------------+----------------+-------+-------+----+-----+--------+-----------+
 1|PRIMARY           |s1   |          |ALL            |idx_key3        |       |       |    |99183|   100.0|Using where|
 2|DEPENDENT SUBQUERY|s2   |          |unique_subquery|PRIMARY,idx_key1|PRIMARY|4      |func|    1|    10.0|Using where|

    • 1
    • 2
    • 3
    • 4
    2.2.5.9 index_subquery

    使用普通索引子查询的类型就会是这种EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';

    id|select_type       |table|partitions|type          |possible_keys    |key     |key_len|ref |rows |filtered|Extra      |
--+------------------+-----+----------+--------------+-----------------+--------+-------+----+-----+--------+-----------+
 1|PRIMARY           |s1   |          |ALL           |idx_key3         |        |       |    |99183|   100.0|Using where|
 2|DEPENDENT SUBQUERY|s2   |          |index_subquery|idx_key1,idx_key3|idx_key3|303    |func|    1|    10.0|Using where|

    • 1
    • 2
    • 3
    • 4
    2.2.5.10 range

    对索引的限制条件不是某个固定值，而是一个范围，就会得到这种类型EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

    id|select_type|table|partitions|type |possible_keys|key     |key_len|ref|rows|filtered|Extra                |
--+-----------+-----+----------+-----+-------------+--------+-------+---+----+--------+---------------------+
 1|SIMPLE     |s1   |          |range|idx_key1     |idx_key1|303    |   |   3|   100.0|Using index condition|

    • 1
    • 2
    • 3
    2.2.5.11 index

    如果使用的是联合索引中的部分字段，就会得到这种类型EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

    id|select_type|table|partitions|type |possible_keys|key         |key_len|ref|rows |filtered|Extra                   |
--+-----------+-----+----------+-----+-------------+------------+-------+---+-----+--------+------------------------+
 1|SIMPLE     |s1   |          |index|             |idx_key_part|909    |   |99183|    10.0|Using where; Using index|

    • 1
    • 2
    • 3
    2.2.5.12 all

    这种就没什么好说的了，啥也没用，查询效率最差

    2.2.6 possible_keys和key

    这两个字段其实就是解析器认为可能会使用的索引和实际使用的索引EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

    id|select_type|table|partitions|type|possible_keys    |key     |key_len|ref  |rows|filtered|Extra      |
--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+
 1|SIMPLE     |s1   |          |ref |idx_key1,idx_key3|idx_key3|303    |const|   1|     5.0|Using where|

    • 1
    • 2
    • 3

    2.2.7 key_len

    这个字段就是使用的索引字段长度，没啥特殊的地方，只不过是在使用联合索引的时候，这个字段的数值越大越好，其实也就是因为多个字段组合形成联合索引，那自然是这里的字段用的越多越好
    比如说 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';和EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
    对应的输出结果分别会是

    id|select_type|table|partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra|
--+-----------+-----+----------+----+-------------+------------+-------+-----+----+--------+-----+
 1|SIMPLE     |s1   |          |ref |idx_key_part |idx_key_part|303    |const|   1|   100.0|     |

    • 1
    • 2
    • 3
    id|select_type|table|partitions|type|possible_keys|key         |key_len|ref        |rows|filtered|Extra|
--+-----------+-----+----------+----+-------------+------------+-------+-----------+----+--------+-----+
 1|SIMPLE     |s1   |          |ref |idx_key_part |idx_key_part|606    |const,const|   1|   100.0|     |

    • 1
    • 2
    • 3

    这里606长度就会比303长度要好，这里只针对联合索引

    2.2.8 ref

    当使用索引或等值查询时，与索引列进行等值匹配的对象信息，比如EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;，输出的ref字段就会是

    id|select_type|table|partitions|type  |possible_keys|key    |key_len|ref       |rows |filtered|Extra|
--+-----------+-----+----------+------+-------------+-------+-------+----------+-----+--------+-----+
 1|SIMPLE     |s1   |          |ALL   |PRIMARY      |       |       |          |99183|   100.0|     |
 1|SIMPLE     |s2   |          |eq_ref|PRIMARY      |PRIMARY|4      |CSDN.s1.id|    1|   100.0|     |

    • 1
    • 2
    • 3
    • 4

    但如果在关联条件上增加函数EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);，那结果就会变成

    id|select_type|table|partitions|type|possible_keys|key     |key_len|ref |rows |filtered|Extra                |
--+-----------+-----+----------+----+-------------+--------+-------+----+-----+--------+---------------------+
 1|SIMPLE     |s1   |          |ALL |             |        |       |    |99183|   100.0|                     |
 1|SIMPLE     |s2   |          |ref |idx_key1     |idx_key1|303    |func|    1|   100.0|Using index condition|

    • 1
    • 2
    • 3
    • 4

    2.2.9 rows

    这个字段是预估的需要读取的记录条数，数值越小越好EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

    id|select_type|table|partitions|type |possible_keys|key     |key_len|ref|rows|filtered|Extra                |
--+-----------+-----+----------+-----+-------------+--------+-------+---+----+--------+---------------------+
 1|SIMPLE     |s1   |          |range|idx_key1     |idx_key1|303    |   |3837|   100.0|Using index condition|

    • 1
    • 2
    • 3

    2.2.10 filtered

    某张表经过搜索条件过滤后，剩余记录条数的百分比，这个数值越高越好，如果rows=100 filtered=100，那就意味着表里的100条记录都符合过滤条件，如果rows=100 filtered=50，就意味着表里本来有200条记录，过滤之后还剩50%，所以rows=100，简单来说就是，rows=100是过滤后的记录数，filtered是经过过滤的百分比，二者一除就是总数，所以分母越大则总数越小，总数越小，查询速度也就越快

    2.2.11 Extra

    这个字段用来展示额外信息，可以借助这个字段来理解优化器是如何执行给定的逻辑，这个字段的枚举值也很多，这里也就只列部分常用类型

    2.2.11.1 No tables used

    顾名思义，就是没有用到表EXPLAIN SELECT 1;

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra         |
--+-----------+-----+----------+----+-------------+---+-------+---+----+--------+--------------+
 1|SIMPLE     |     |          |    |             |   |       |   |    |        |No tables used|

    • 1
    • 2
    • 3
    2.2.11.2 Impossible WHERE

    当过滤条件肯定为假的时候，就会出现这个情况EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra           |
--+-----------+-----+----------+----+-------------+---+-------+---+----+--------+----------------+
 1|SIMPLE     |     |          |    |             |   |       |   |    |        |Impossible WHERE|

    • 1
    • 2
    • 3
    2.2.11.3 Using where

    当全表扫描时，也就是没有用索引，同时使用了过滤条件，就会得到这个额外信息EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra      |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+-----------+
 1|SIMPLE     |s1   |          |ALL |             |   |       |   |99183|    10.0|Using where|

    • 1
    • 2
    • 3
    2.2.11.4 No matching min/max row

    当查询语句里有min或max时，但并没有满足条件的数据，就会得到这个类型EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra                  |
--+-----------+-----+----------+----+-------------+---+-------+---+----+--------+-----------------------+
 1|SIMPLE     |     |          |    |             |   |       |   |    |        |No matching min/max row|

    • 1
    • 2
    • 3
    2.2.11.5 Using index

    使用索引进行搜索，并且查询的字段也是索引，就会得到这个类型EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';

    id|select_type|table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra      |
--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----------+
 1|SIMPLE     |s1   |          |ref |idx_key1     |idx_key1|303    |const|   1|   100.0|Using index|

    • 1
    • 2
    • 3
    2.2.11.6 Using index condition

    使用索引进行搜索，但最后并不能用到这个索引，就会得到这个类型EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';

    id|select_type|table|partitions|type |possible_keys|key     |key_len|ref|rows|filtered|Extra                |
--+-----------+-----+----------+-----+-------------+--------+-------+---+----+--------+---------------------+
 1|SIMPLE     |s1   |          |range|idx_key1     |idx_key1|303    |   |3837|   100.0|Using index condition|

    • 1
    • 2
    • 3
    2.2.11.7 Using join buffer (Block Nested Loop)

    在连接查询中，如果被驱动表不能利用索引加速，那优化器会自动分配join buffer的内存块进行加速EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra                                             |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+--------------------------------------------------+
 1|SIMPLE     |s1   |          |ALL |             |   |       |   |99183|   100.0|                                                  |
 1|SIMPLE     |s2   |          |ALL |             |   |       |   |99620|    10.0|Using where; Using join buffer (Block Nested Loop)|

    • 1
    • 2
    • 3
    • 4
    2.2.11.8 Not exists

    在进行左连接时，如果一个字段不能为空，但又被加条件是为空，那就会得到这个类型EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

    id|select_type|table|partitions|type|possible_keys|key     |key_len|ref         |rows |filtered|Extra                  |
--+-----------+-----+----------+----+-------------+--------+-------+------------+-----+--------+-----------------------+
 1|SIMPLE     |s1   |          |ALL |             |        |       |            |99183|   100.0|                       |
 1|SIMPLE     |s2   |          |ref |idx_key1     |idx_key1|303    |CSDN.s1.key1|    1|    10.0|Using where; Not exists|

    • 1
    • 2
    • 3
    • 4
    2.2.11.9 Using intersect(…) 、 Using union(…) 和 Using sort_union(…)

    其实就是一个合并的情况EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

    id|select_type|table|partitions|type       |possible_keys    |key              |key_len|ref|rows|filtered|Extra                                      |
--+-----------+-----+----------+-----------+-----------------+-----------------+-------+---+----+--------+-------------------------------------------+
 1|SIMPLE     |s1   |          |index_merge|idx_key1,idx_key3|idx_key1,idx_key3|303,303|   |   2|   100.0|Using union(idx_key1,idx_key3); Using where|

    • 1
    • 2
    • 3
    2.2.11.10 Zero limit

    这种情况可能只有在获取表结构的时候可能出现EXPLAIN SELECT * FROM s1 LIMIT 0;

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra     |
--+-----------+-----+----------+----+-------------+---+-------+---+----+--------+----------+
 1|SIMPLE     |     |          |    |             |   |       |   |    |        |Zero limit|

    • 1
    • 2
    • 3
    2.2.11.11 Using filesort

    当对结果集使用索引排序的时候，会展示这种类型EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra         |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+--------------+
 1|SIMPLE     |s1   |          |ALL |             |   |       |   |99183|   100.0|Using filesort|

    • 1
    • 2
    • 3
    2.2.11.12 Using temporary

    当优化器认为该使用临时表的时候，就会出现这种类型EXPLAIN SELECT DISTINCT common_field FROM s1;

    id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra          |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+---------------+
 1|SIMPLE     |s1   |          |ALL |             |   |       |   |99183|   100.0|Using temporary|

    • 1
    • 2
    • 3
  • 相关阅读:
    java中有哪些并发的List?只知道一种的就太逊了
    Java / Android 多线程和 synchroized 锁
    Spring Cloud Alibaba Sentinel 简单使用
    ArcGIS校园3D展示图制作详细教程
    java计算机毕业设计西安市城市绿地管理系统源程序+mysql+系统+lw文档+远程调试
    DockerCompose安装、使用 及 微服务部署实操
    前 3 名突然变了？揭秘 7 月编程语言最新排行榜
    C# Winform内嵌窗体(在主窗体上显示子窗体)
    kafka Streams消费最新的消息
    大数据技术之Hadoop：Yarn集群部署（七）
  • 原文地址：https://blog.csdn.net/weixin_44999258/article/details/126578058