MySQL的`EXPLAIN`用于查看查询的执行计划，揭示了查询如何进行，包括表的访问顺序、查询类型等。输出中，`table`列展示访问的表，`type`列显示查询类型，从全表扫描到索引扫描，性能依次提升。优化查询时，关注`type`列，尽量使其使用`eq_ref`、`ref`或`index`，避免`all`和`range`。
-------------------------------------------
explain 用于查询执行计划，可以查看查询优化器如何执行查询
输出
每个表一行，这里的表可以是一个子查询，一个 union 结果
行是以 mysql 实际执行的顺序出现，有时与请求中的原始 sql 不一致
-------------------------------------------
-------------------------------------------
id
标识 select 所属的行
-------------------------------------------
select_type
-----------------------
simple
查询不包括 子查询 和 union
-----------------------
如果查询有复杂的子部分，则最外层部分标记为 primary
-----------------------
subquery
包含在 select 列表中的子查询
explain select (select 1 from actor limit 1) from film;
id select_type table
1 primary film
2 subquery actor
-----------------------
derived
包含在 from 子句中的子查询，mysql 会递归执行并将结果放在一个临时表中，叫派生表，从子查询中派生来的
explain select film_id from (select film_id from film) as der;
id select_type table
1 primary derived2
2 derived film
-----------------------
union
union 后面的 select 被标记为 union, union 中的第一个 select 被标记为 primary, 如果 union 在 from 子句的子查询里，那么第一个 select 被标记为 derived
explain select 1 union all select 1;
id select_type table
1 primary null
2 union null
null union_result union1,2
-----------------------
union result
从 union 的匿名临时表检索结果的 select
-----------------------
dependent
select 依赖于外层查询的数据
-----------------------
uncacheable
select 的某些特性阻止结果被缓存
-------------------------------------------
-------------------------------------------
table
-------------------------------------------
对应行访问的表的表名或别名，这一列从上往下看，是关联优化器为查询选择的关联顺序
< derivedN >
当from子句中有子查询时，N 是子查询id
< union1,2... >
当有 union 时，union_result 的 table 列包含一个参与 union 的 id 列表
-------------------------------------------
-------------------------------------------
type
-------------------------------------------
查询类型，分为 全表扫描 和 索引扫描
-----------------------
性能依次从最差到最优
all < index < range < ref < eq_ref < const/system < NULL
-----------------------
ALL 按行顺序 全表扫描
mysql 必须要扫描整张表，从头到尾，去找到需要的行
例外
查询中使用了 limit
extra 列中显示 using distinct / not exists
-----------------------
索引扫描
索引扫描的 type
index, range, ref, eq_ref, const(system), NULL
-----------------------
index 按索引顺序 全表扫描
select id from city
-----------------------
range 索引范围扫描，返回匹配这个值域的行
包含的符号 >= <= between and or in like
select * from city where id > 2000
select * from city where countrycode = 'CHN' or countrycode = 'USA'
-----------------------
or 中的每一项都需要重新遍历叶子节点，一般需要改写为 union all
-----------------------
select * from city where countrycode = 'CHN'
union all
select * from city where countrycode = 'USA'
-----------------------
改写后，type 值改为了 ref, 明显 ref 比 range 范围索引性能更高
-----------------------
ref 索引查找，返回匹配某个值的所有行，常见于非唯一性索引 或 唯一性索引的非唯一性前缀
select * from city where countrycode = 'CHN'
-----------------------
eq_ref 子表使用主键列或唯一列作为连接条件
在使用 join 连接多表时，驱动表就是 from 后面紧跟着的表，一般使用记录行少的表作为驱动表，右边的表都属于子表
A join B on A.bid = B.id
A 是驱动表，B 是子表
当 B.id 是主键或唯一列的时候，使用的是 eq_ref 方式查询, 原因是驱动表是不使用索引的，而是使用全表扫描的方式，
从第二张表是可以使用索引的
select CITY.name, COU.name,CITY.population from city as CITY join country as COU on CITY.countrycode = COU.code where CITY.population < 100;
-----------------------
const / system 主键或唯一索引等于常量的查询，system 是 const 的一种特例，当表只有一行数据的时候
select * from city where id = 100;
-----------------------
NULL 无需访问表或索引
当表中不存在要查询的记录时
select * from tmp_student where tel='1'
或从一个索引列查最小值
-------------------------------------------
对于辅助索引来说 !=(不等于), <>(不等于)，not in(), 使用的是全表扫描, 不走索引
对于主键索引来说 !=(不等于), <>(不等于)，not in(), 使用的是 range 索引范围扫描
对于 like ，如果 %(百分号)在前面，使用的是全表扫描，不走索引
如果 like 给定的条件太少导致可选范围过大，使用全表扫描，不走索引
-------------------------------------------
possible_keys
查询可以使用哪些索引，基于检索列和比较操作符来判断
-------------------------------------------
key
用到的索引，mysql 基于最小化查询成本选择索引
-------------------------------------------
key_len
mysql 在索引里使用的字节数，用于判断 where子句 条件过滤时，有多少列走了索引
key_len 由实际用到的索引列个数，和对应列的定义决定
当索引字段为 null 字段, 是否为空的标记, 占用 1 个字节
变长数据类型(如 varchar), 长度信息需要占用 1～2 个字节
对于 char、varchar、blob、text 等字符集来说，key len 的长度还和字符集有关,
latin1 一个字符占用 1 个字节, gbk 一个字符占用 2 个字节, utf8 一个字符占用 3 个字节
列类型 KEY_LEN 备注
id int 4+1 int为4字节, NULL加1字节
id bigint not null 8 bigint为8字节
user char(30) utf8 30*3+1 utf8每个字符为3字节, NULL加1字节
user varchar(30) not null utf8 30*3+2 变长数据类型加2字节
user varchar(30) utf8 30*3+2+1 NULL加1字节
detail text(10) utf8 10*3+2+1 TEXT截取部分,被视为动态列类型
例如,有个联合索引 idx(c1,c2,c3), 3列均是 int not null,
那么下面的 SQL 执行计划中, key_len 的值是 8 而不是 12
select ... from tb where c1=? and c2=? order by c1;
-------------------------------------------
ref
key 列中的索引查找值时所用的常量或列
-------------------------------------------
rows
mysql 执行查询需要检查的行数，不是结果集的行数，根据表的统计信息和索引的使用情况，这个估算可能很不精确
通过执行计划里每个表的 rows 列的值相乘，可以粗略的估算出整个查询会检查的行数
explain select f.film_id from film as f
inner join film_actor as fa using(film_id)
inner join actor as a using(actor_id);
rows
200
13
1
这个查询大约会检查 2600 行
-------------------------------------------
filtered
使用 explain extended 时出现
符合 where 子句或联结条件的记录数百分比估算值，和 rows 相乘得到估算过滤后的行数
-------------------------------------------
Extra 不适合在其他列显示的额外信息
-----------------------
Using filesort -- 需要优化
order by / GROUP BY 里的列没有走索引
文件排序 file sort
数据量小在内存中进行，数据量大需要使用磁盘，mysql 统称为文件排序
-----------------------
EXPLAIN select col1 from t1 where col1 = 'ac' order by col3;
key: idx_col1_col2_col3
extra: Using where; Using index; Using filesort
-----------------------
EXPLAIN SELECT col1 from t1 where col1='ac' order by col2, col3;
key: idx_col1_col2_col3
extra: Using where; Using index
-----------------------
Using temporary
temporary -- 内存或磁盘上的临时表，统称为 temporary
使用了临时表保存中间结果
常见于 order by / group by / union / 子查询 / 关联查询
group by 子句里的列最好遵循索引里的列的顺序与个数
EXPLAIN SELECT col1 from t1 where col1 in ('ac','ab') GROUP BY col2;
key: idx_col1_col2
extra: Using where; Using index; Using temporary; Using filesort
-----------------------
EXPLAIN SELECT col1 from t1 where col1 in ('ac','ab') GROUP BY col1, col2;
key: idx_col1_col2
extra: Using where; Using index
-----------------------
Using index
使用了覆盖索引 (covering index)
当使用索引查找数据时, extra 列会显示 Using where; Using index
EXPLAIN SELECT col1 from t1 where col1 in ('ac','ab');
key: idx_col1_col2
extra: Using where; Using index
当使用索引读取数据时, extra 列会显示 Using index
EXPLAIN select col1, col2 from t1;
key: idx_col1_col2
extra: Using index
-----------------------
Using where
mysql 先通过存储引擎返回数据，再在 mysql 的服务器层过滤不满足条件的记录，不是所有带 where 子句的 sql 都会显示 using where，有的覆盖索引查询就不会
EXPLAIN SELECT col1 from t1 where col1='ac';
key: idx_col1_col2_col3
extra: Using index
-----------------------
Using join buffer
使用了连接缓存
-----------------------
impossible where
where 子句的值总是 false，不能用来获取任何元组
EXPLAIN select col1 from t1 where col1 = 'ab' and col1 = 'ac';
extra: Impossible WHERE
-----------------------
select tables optimized away
在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MYSQL 存储引擎优化 COUNT（*）操作，
不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
-----------------------
distinct
优化 distinct 操作，在找到第一匹配的元组后即停止找同样的值的动作
-----------------------
using index for group-by
松散索引扫描
explain select actor_id, MAX(film_id) from film_actor GROUP BY actor_id;
key: (film_id, actor_id)
-----------------------
740
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
