mysql 调优-EXPLAIN 命令-526互联

一条 sql 的执行，会生成一个执行计划，这个执行计划是优化器认为最优的，然后执行器再执行这个执行计划
执行计划只是优化器自认为最优，不见得就是最优的
执行计划里面包括：可用的索引，真正用到的索引，表的读取顺序，每张表有多少行被优化等等
EXPLAIN 和 DESCRIBE 效果一样

EXPLAIN 每个列作用

列	描述	备注
id	1，select 对应的组，如果id相同代表同一组，顺序执行，如果不相同，id 值越大的优先级越高 2，每个id表示一趟独立的查询，一条sql查询的躺数越少越好	1，通常有几个select就会有几个id（子查询） 2，有时id的值少于select，这是因为优化器会对我们sql进行优化，执行计划里面可能会把一些子查询转换成连接查询等 3，多余有时也会多余select个数，比如 union 查询会生成中间表，此时 id 列是 null，Extra 列是 Using temporary，select_type 列是 UNION RESULT
select_type	SELECT关键字对应的那个查询的类型	1，SIMPLE：不包含 union 或者子查询 2，PRIMARY：①如果 union 查询，左边的查询类型是 PRIMARY，右边是 UNION，还会多一个 UNION RESULT（非 UNION ALL，要去重，临时表）。②如果子查询（优化器没优化的前提），外层就是 PRIMARY，内层是 UNION 3，SUBQUERY：非相关子查询（优化器没有优化或者优化不了） 4，DEPENDENT SUBQUERY：相关子查询，子查询可能会执行多次 5，DERIVED：把子查询的结果集作为一个表再次进行查询
table	表名	需要查询的表，可能会有临时表（不存在数据库中）如果是多个表，上面行是驱动表，下面的是被驱动表
partitions	partitions	分区，可略
type	针对单表的访问方法	访问类型，性能从高到低：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，all 阿里规范强制要求，最少是 range 级别
possible_keys	可能用到的索引	prossible_keys 在优化器优化时，越多反而越不好，因为优化器要取里面成本最小的
key	实际上使用的索引	key 不一定是 prossible_keys 子集，因为优化器后 sql 可能不是我们写的样子
key_len	实际使用到的索引长度	主要是针对联合索引才有意义，如果不是联合索引，关注 key 就好了具体要计算字节长度，和当前列类型所占空间对比，别忘了null要加1个字节和变长加2个字节
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息	1，const：`...where code = '123'` 2，具体的字段：`... t1 join t2 on t1.id = t2.id`被驱动表的ref就是 t1.id 3，func：`... t1 join t2 on UPPER(t1.code) = t2.id`
rows	预估的需要读取的记录条数	值越小越好。这是预估的，可能预估是100条，实际结果集是80条（filtered来决定）
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比	1，越大越好。如果是 100表示100% ，说明不会过滤，预估是多少结果集就是多少 2，单表查询意义不大，主要针对连接查询（被驱动表执行执行的次数 rows*filtered）
Extra	一些额外的信息

type

system：精确查找，这种情况实际中是遇不到的。比如 MyISAM，MEMORY 存储引擎下如果只有一条数据，进行查询的访问类型就是 system，MyISAM 下的 count(0) 也是，因为专门维护了一个字段来保存总量
const ：where 条件是主键或者唯一索引列查询

eq_ref：连接查询时，被驱动表通过主键或者唯一索引列进行访问

-- 被驱动表是 t2，on 的条件是 id，id 是 t2 的主键
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

ref：where 条件列是普通索引，值是常量

-- 假设 code 列添加了普通索引，值是字符串一个常量
EXPLAIN SELECT * FROM t1 WHERE code = 'abcd';

ref_or_null：和 ref 类似，除了常量外还能是 null

EXPLAIN SELECT * FROM t1 WHERE code = 'abcd' OR code IS NULL;

index_merge：where 后面是 or，并且两个字段都有索引（and 就不行）

-- code 和 name 都有索引，并且是 OR ，两个索引都会用到
EXPLAIN SELECT * FROM t1 WHERE code = 'abcd' OR name = 'Marry';

-- and 就不行，只能用到一个索引，具体是 code 的索引（因为 code 在前面嘛，先执行）
EXPLAIN SELECT * FROM t1 WHERE code = 'abcd' AND name = 'Marry';

unique_subquery：一般针对 in 查询，优化器把 in 转换成了 EXISTS 子查询，并且子查询用到了主键等值匹配

EXPLAIN 
    SELECT * FROM s1 
    WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) 
    OR key3 = 'a';
    
-- 优化器优化后
EXPLAIN
	SELECT * FROM s1 WHERE key3 = 'a' OR EXISTS(
        SELECT 1 FROM s2 WHERE s1.key1 = s2.key1 AND s1.key2 = s2.id
    )

range：普通索引的范围查询，比如 SELECT * FROM t1 WHERE code in ('a', 'b', 'c');

index：当可以使用索引覆盖（查询的字段就是索引，不会回表），但是要扫描全部的索引，不能精确根据索引进行查询

-- 假设有个联合索引：key_part1，key_part2，key_part3
-- 因为最左匹配，这条 sql 不会走索引
-- 但是需要查的字段就是联合索引的一部分，并且条件也是，所以就扫描全部的索引来查
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

-- 这个也是 index
EXPLAIN SELECT key_part2 FROM s1;

-- 这个不会走索引，是 all
EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';

all：全表扫描

key_length

使用索引的长度，可以判断是否用了联合索引，用的哪个列（越大越好，是跟自己比）

-- 创建表，创建一个 name 和 addr 的联合索引
CREATE TABLE `t_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` CHAR(20) DEFAULT NULL, 
`addr` VARCHAR(200),
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 这个执行计划会走索引，key_length 应该是 60
EXPLAIN SELECT * FROM t_user where `name` = 'Marry';

-- 如果走了联合索引，key_length 是 604，那么可以判断索引匹配到了联合索引的 name 字段

长度计算公式

varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

Extrl
- Impossible WHERE ：where 条件永远是假
- Using where ：全表扫描（意味着不走索引）的带 where 查询
- No matching min/max row ：用了 min() 或 max() 函数，但是并没有符合 where 的数据
```
-- 数据库没有 name = 123123 的 max 或 min 函数的 Extrl 就是 No matching min/max row 
EXPLAIN SELECT MAX(id) FROM t_user WHERE name = '123123';
```
- Select tables optimized away：类似上面，where 之后有数据的 min 或 max
- Using index：使用了覆盖索引
- Using index condition：使用了索引下推（where 条件有的列走索引，有的列不走索引）
- Using where：Using join buffer：连接查询时被驱动表不走索引的时候数据库为其分配一块内存来优化查询
- Using filesort：这时 sql 中应该有 ordery by，并且 order by 字段没有索引，所以需要在文件中（数据量大）或者内存中（数据量小）排序，这时需要考虑优化，比如给排序字段加索引
- Using temporary：使用了临时表。比如 union、 distinct、group gy 等，但反之并不成立，如比如distinct code 或 group by code，如果 code 字段有索引就不会用临时表了，而是 Using index
EXPLAIN 的 4 种格式

默认就是传统格式，如果要换 json 和 tree 需要在 explain 中指定 format

EXPLAIN fromat=json select ....

EXPLAIN format=tree select ...
- 传统格式：就是默认的格式，表格形式
- JSON：和传统格式相比格式换成了 json ，字段大致一样，好处是能看到执行成本，query_block.cost_info.query_cost
- TREE：8.0之后新增，感觉没什么卵用
- 可视化：要使用 workbench 工具
优化器后的sql
- 优化器会对我们的 sql 进行优化，最终执行的 sql 可能和我们写的有所不同
- 在 EXPLAIN SELECT... 之后，再执行 show warnings\G就能看到

mysql-explain explain mysql

526互联

mysql 调优-EXPLAIN 命令

EXPLAIN 每个列作用

EXPLAIN 的 4 种格式

优化器后的sql