mysqlexplain不准确_最完整的Explain总结，SQL优化不再困
难
个⼈：⽉伴飞鱼，欢迎关注
先看看具体有哪些字段：mysql> EXPLAIN SELECT 1;
其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句前边都可以加上EXPLAIN这个词⼉，⽤来查看这些语句的执⾏计划
建两张测试表：CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 VARCHAR(100),
key3 VARCHAR(100),
name VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
KEY idx_key2_key3(key2, key3)
) Engine=InnoDB CHARSET=utf8;
CREATE TABLE t2 (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 VARCHAR(100),
key3 VARCHAR(100),
name VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
KEY idx_key2_key3(key2, key3)
) Engine=InnoDB CHARSET=utf8;
进度管理软件有哪些两个变种
mysql面试题sql优化
explain extended
spring事务隔离级别会在 explain 的基础上额外提供⼀些查询优化的信息。紧随其后通过 show warnings 命令可以 得到优化后的查询语句，从⽽看出优化器优化了什么explain extended SELECT * FROM t1 where key1 = '11';
show warnings;
explain partitions
相⽐ explain 多了个 partitions 字段，如果查询是基于分区表的话，会显⽰查询将访问的分区。EXPLA
IN PARTITIONS SELECT * FROM t1 INNER JOIN t2 ON t1.key3 = t2.key3;
table列
这⼀列表⽰ explain 的⼀⾏正在访问哪个表mysql> EXPLAIN SELECT * FROM t1;
这个查询语句只涉及对t1表的单表查询，所以EXPLAIN输出中只有⼀条记录，其中的table列的值是t1，表明这条记录是⽤来说明对t1表的单表访问。mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2;
可以看到这个连接查询的执⾏计划中有两条记录，这两条记录的table列分别是t1和t2，这两条记录⽤来分别说明对t1表和t2表的访问
注意：当 from ⼦句中有⼦查询时，table列是 格式，表⽰当前查询依赖 id=N 的查询，于是先执⾏ id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为，1和2表⽰参与 union 的 select ⾏id。
id列
id列的编号是 select 的序列号，有⼏个 select 就有⼏个id，并且id的顺序是按 select 出现的顺序增长的。
id列越⼤执⾏优先级越⾼，id相同则从上往下执⾏，id为NULL最后执⾏
⽐如下边这个查询中只有⼀个SELECT关键字，所以EXPLAIN的结果中也就只有⼀条id列为1的记录：mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'e038f672a8';
对于连接查询来说，⼀个SELECT关键字后边的FROM⼦句中可以跟随多个表，所以在连接查询的执⾏计划中，每个表都会对应⼀条记录，但是这些记录的id值都是相同的，⽐如：mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2;
可以看到，上述连接查询中参与连接的t1和t2表分别对应⼀条记录，但是这两条记录对应的id值都是1。
注意：在连接查询的执⾏计划中，每个表都会对应⼀条记录，这些记录的id列的值是相同的，出现在前边的表表⽰驱动表，出现在后边的表表⽰被驱动表。所以从上边的EXPLAIN输出中我们可以看出，查询优化器准备让t2表作为驱动表，让t1表作为被驱动表来执⾏查询
对于包含⼦查询的查询语句来说，就可能涉及多个SELECT关键字，所以在包含⼦查询的查询语句的执⾏计划中，每个SELECT关键字都会对应⼀个唯⼀的id值，⽐如这样：mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2) OR key3 =
'a1b6cee57a';
从输出结果中我们可以看到，t1表在外层查询中，外层查询有⼀个独⽴的SELECT关键字，所以第⼀条记录的id值就是1，t2表在⼦查询中，⼦查询有⼀个独⽴的SELECT关键字，所以第⼆条记录的id值就是2。
但是这⾥⼤家需要特别注意，查询优化器可能对涉及⼦查询的查询语句进⾏重写，从⽽转换为连接查
询。所以如果我们想知道查询优化器对某个包含⼦查询的语句是否进⾏了重写，直接查看执⾏计划就好了，⽐如说：mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key3 FROM t2 WHERE t1.key1 = 'a1b6cee57a');
可以看到，虽然我们的查询语句是⼀个⼦查询，但是执⾏计划中t1和t2表对应的记录的id值全部是1，这就表明了查询优化器将⼦查询转换为了连接查询。
对于包含UNION⼦句的查询语句来说，每个SELECT关键字对应⼀个id值也是没错的，不过还是有点⼉特别的东西，⽐⽅说下边这个查询：mysql> EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
UNION⼦句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了⼀个名为的临时表(就是执⾏计划第三条记录的table列的名称)，id为NULL表明这个临时表是为了合并两个查询的结果集⽽创建的。
跟UNION对⽐起来，UNION ALL就不需要为最终的结果集进⾏去重，它只是单纯的把多个查询的结果
集中的记录合并成⼀个并返回给⽤户，所以也就不需要使⽤临时表。所以在包含UNION ALL⼦句的查询的执⾏计划中，就没有那个id为NULL的记录，如下所⽰：mysql> EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2;
select_type列
MySQL每⼀个SELECT关键字代表的⼩查询都定义了⼀个称之为select_type的属性，意思是我们只要知道了某个⼩查询的select_type属性，就知道了这个⼩查询在整个⼤查询中扮演了⼀个什么⾓⾊
下⾯是官⽅⽂档介绍：
SIMPLE
查询语句中不包含UNION或者⼦查询的查询都算作是SIMPLE类型，⽐⽅说下边这个单表查询的select_type的值就是SIMPLE：mysql> EXPLAIN SELECT * FROM t1;
PRIMARY
对于包含UNION、UNION ALL或者⼦查询的⼤查询来说，它是由⼏个⼩查询组成的，其中最左边的那个查询的select_type值就是PRIMARY，⽐⽅说：mysql> EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
从结果中可以看到，最左边的⼩查询SELECT * FROM t1对应的是执⾏计划中的第⼀条记录，它的select_type值就是PRIMARY。
UNION
对于包含UNION或者UNION ALL的⼤查询来说，它是由⼏个⼩查询组成的，其中除了最左边的那个⼩查询以外，其余的⼩查询的
select_type值就是UNION，可以对⽐上⼀个例⼦的效果
UNION RESULT
MySQL选择使⽤临时表来完成UNION查询的去重⼯作，针对该临时表的查询的select_type就是UNION RESULT，同样对⽐上⾯的例⼦SUBQUERY
如果包含⼦查询的查询语句不能够转为对应的semi-join的形式，并且该⼦查询是不相关⼦查询，并且查询优化器决定采⽤将该⼦查询物化的⽅案来执⾏该⼦查询时，该⼦查询的第⼀个SELECT关键字代表的那个查询的select_type就是SUBQUERY，⽐如下边这个查询：
概念解释：semi-join⼦查询，是指当⼀张表在另⼀张表到匹配的记录之后，半连接(semi-jion)返回第⼀张表中的记录。与条件连接相反，即使在右节点中到⼏条匹配的记录，左节点 的表也只会返回⼀条记录。另外，右节点的表⼀条记录也不会返回。半连接通常使⽤IN 或 EXISTS 作为连接条件
物化：这个将⼦查询结果集中的记录保存到临时表的过程称之为物化(Materialize)。那个存储⼦查询结果集的临时表称之为物化表。正因为物化表中的记录都建⽴了索引(基于内存的物化表有哈希索引，基于磁盘的有B+树索引)，通过索引执⾏IN语句判断某个操作数在不在⼦查询结果集中变得⾮常快，从⽽提升了⼦查询语句的性能。mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2) OR key3 = 'a1b6cee57a';
可以看到，外层查询的select_type就是PRIMARY，⼦查询的select_type就是SUBQUERY。
DEPENDENT SUBQUERY
如果包含⼦查询的查询语句不能够转为对应的semi-join的形式，并且该⼦查询是相关⼦查询，则该⼦查询的第⼀个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY，⽐如下边这个查询：mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2 WHERE t1.key2 = t2.key2) OR key3 = 'a1b6cee57a';
DEPENDENT UNION
在包含UNION或者UNION ALL的⼤查询中，如果各个⼩查询都依赖于外层查询的话，那除了最左边的那个⼩查询之外，其余的⼩查询的select_type的值就是DEPENDENT UNION。⽐⽅说下边这个查询：mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2 WHERE key1 = 'a1b6cee57a' UNION SELECT key1 FROM t1 WHERE key1 = 'a1b6cee57a');
这个查询⽐较复杂啊，⼤查询⾥包含了⼀个⼦查询，⼦查询⾥⼜是由UNION连起来的两个⼩查询。从执⾏计划中可以看出来，SELECT
key1 FROM t2 WHERE key1 = 'a1b6cee57a'这个⼩查询由于是⼦查询中第⼀个查询，所以它的select_type是DEPENDENT SUBQUERY，⽽SELECT key1 FROM t1 WHERE key1 = 'a1b6cee57a'这个查询的select_type就是DEPENDENT UNION。
DERIVED
对于采⽤物化的⽅式执⾏的包含派⽣表的查询，该派⽣表对应的⼦查询的select_type就是DERIVED，⽐⽅说下边这个查询：mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as t FROM t1 GROUP BY key1) AS derived_t1 where t > 1;
从执⾏计划中可以看出，id为2的记录就代表⼦查询的执⾏⽅式，它的select_type是DERIVED，说明该⼦查询是以物化的⽅式执⾏的。id 为1的记录代表外层查询，⼤家注意看它的table列显⽰的是，表⽰该查询是针对将派⽣表物化之后的表进⾏查询的。
MATERIALIZED
当查询优化器在执⾏包含⼦查询的语句时，选择将⼦查询物化之后与外层查询进⾏连接查询时，该⼦查询对应的select_type属性就是MATERIALIZED，⽐如下边这个查询：mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2);
执⾏计划的第三条记录的id值为2，说明该条记录对应的是⼀个单表查询，从它的select_type值为MATERIALIZED可以看出，查询优化器是要把⼦查询先转换成物化表。然后看执⾏计划的前两条记录的id值都为1，说明这两条记录对应的表进⾏连接查询，需要注意的是第⼆条记录的table列的值是，说明该表其实就是id为2对应的⼦查询执⾏之后产⽣的物化表，然后将s1和该物化表进⾏连接查询。masm是高级语言吗
type列
这⼀列表⽰关联类型或访问类型，即MySQL决定如何查表中的⾏，查数据⾏记录的⼤概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
⼀般来说，得保证查询达到range级别，最好达到ref
NULL
mysql能够在优化阶段分解查询语句，在执⾏阶段⽤不着再访问表或索引。例如：在索引列中选取最⼩值，可以单独查索引来完成，不需要在执⾏时访问表mysql> explain select min(id) from t1;
eq_ref
primary key 或 unique key 索引的所有部分被连接使⽤ ，最多只会返回⼀条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
在连接查询时，如果被驱动表是通过主键或者唯⼀⼆级索引列等值匹配的⽅式进⾏访问的(如果该主键或者唯⼀⼆级索引是联合索引的话，所有的索引列都必须进⾏等值⽐较)，则对该被驱动表的访问⽅法就是eq_ref，⽐⽅说：mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;常量和变量的字母
从执⾏计划的结果中可以看出，MySQL打算将t2作为驱动表，t1作为被驱动表，重点关注t1的访问⽅法是eq_ref，表明在访问t1表的时候可以通过主键的等值匹配来进⾏访问。
ref
当通过普通的⼆级索引列与常量进⾏等值匹配时来查询某个表，那么对该表的访问⽅法就可能是ref
相⽐ eq_ref，不使⽤唯⼀索引，⽽是使⽤普通索引或者唯⼀性索引的部分前缀，索引要和某个值相⽐较，可能会到多个符合条件的⾏。mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'a';
可以看到type列的值是ref，表明MySQL即将使⽤ref访问⽅法来执⾏对t1表的查询
system，const
mysql能对查询的某部分进⾏优化并将其转化成⼀个常量(可以看show warnings 的结果)。⽤于 primary key 或 unique key 的所有列与常数⽐较时，所以表最多有⼀个匹配⾏，读取1次，速度⽐较快。system是const的特例，表⾥只有⼀条元组匹配时为systemmysql> EXPLAIN SELECT * FROM t1 WHERE id = 5;
ref_or_null
当对普通⼆级索引进⾏等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问⽅法就可能是ref_or_null，⽐如说：mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'a' OR key1 IS NULL;
index_merge
⼀般情况下对于某个表的查询只能使⽤到⼀个索引，但在某些场景下可以使⽤多种索引合并的⽅式来执⾏查询，我们看⼀下执⾏计划中是怎么体现MySQL使⽤索引合并的⽅式来对某个表执⾏查询的：mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'a' OR key2 = 'a';
ajax代码的意思
从执⾏计划的type列的值是index_merge就可以看出，MySQL打算使⽤索引合并的⽅式来执⾏对t1表的查询。
unique_subquery
类似于两表连接中被驱动表的eq_ref访问⽅法，unique_subquery是针对在⼀些包含IN⼦查询的查询语
句中，如果查询优化器决定将IN⼦查询转换为EXISTS⼦查询，⽽且⼦查询可以使⽤到主键进⾏等值匹配的话，那么该⼦查询执⾏计划的type列的值就是unique_subquery，⽐如下边的这个查询语句：mysql> EXPLAIN SELECT * FROM t1 WHERE key2 IN (SELECT id FROM t2 where t1.key1 =
t2.key1) OR key3 = 'a';

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。