1. 环境
windows 10 + mysql 8.0.21
安装步骤参见 https://www.jianshu.com/p/fbd548f85e5f
2. select_type的解释
| 名称 | JSON 名 | 含义 |
|---|---|---|
| SIMPLE | 无 | 简单的SELECT(没有使用UNION或者子查询) |
| PRIMARY | 无 | 最外层的查询 |
| UNION | 无 | 在一个UNION中第二或后面的SELECT语句 |
| DEPENDENT UNION | dependent (true) | 在一个UNION中第二或后面的SELECT语句，并且依赖于外层查询 |
| UNION RESULT | union_result | UNION的结果 |
| SUBQUERY | 无 | 子查询中的第一个SELECT |
| DEPENDENT SUBQUERY | dependent (true) | 子查询中的第一个SELECT，并且依赖于外层查询 |
| DERIVED | 无 | 派生表（Derived table） |
| MATERIALIZED | materialized_from_subquery | 实例化子查询（Materialized subquery） |
| UNCACHEABLE SUBQUERY | cacheable (false) | 不能缓存结果的子查询，并且必须为外部查询的每一行重新计算结果 |
| UNCACHEABLE UNION | cacheable (false) | 在一个UNCACHEABLE SUBQUERY的UNION语句中第二或后面的SELECT语句 |
3.建表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`parentId` int NULL DEFAULT NULL COMMENT '上级主键',
`name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs NOT NULL COMMENT '名称',
`code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_dept_parentId`(`parentId` ASC) USING BTREE,
INDEX `idx_dept_code`(`code` ASC) USING BTREE
) ENGINE = InnoDB COMMENT = '组织机构';
INSERT INTO `dept` VALUES (1, 0, '射雕英雄传', '01');
INSERT INTO `dept` VALUES (2, 1, '桃花岛', '0101');
INSERT INTO `dept` VALUES (3, 1, '襄阳城', '0102');
INSERT INTO `dept` VALUES (4, NULL, '这是哪儿', '02');
4.select_type示例
-- [SIMPLE]
EXPLAIN SELECT * FROM dept;
-- [PRIMARY] [SUBQUERY]
EXPLAIN SELECT * FROM dept WHERE id = (SELECT max(id) FROM dept WHERE NAME = '桃花岛');
-- [DERIVED]
EXPLAIN SELECT * from (SELECT parentId,count(1) from dept GROUP BY parentId ) a ;
-- [UNION] [UNION RESULT]
EXPLAIN SELECT * FROM dept WHERE id=1 UNION SELECT * FROM dept WHERE id=2;
-- [MATERIALIZED]
EXPLAIN SELECT * FROM dept t WHERE EXISTS (SELECT id FROM dept d WHERE d.parentId = t.id AND d.NAME = '桃花岛' );
-- [DEPENDENT SUBQUERY] [DEPENDENT UNION]
EXPLAIN SELECT * FROM dept t WHERE EXISTS (SELECT id FROM dept e WHERE t.id = e.parentId UNION ALL SELECT * FROM dept d WHERE t.id = d.parentId );
-- [UNCACHEABLE UNION]
EXPLAIN SELECT * FROM dept t WHERE EXISTS (SELECT id FROM dept e WHERE t.id = e.parentId UNION ALL SELECT * FROM dept d WHERE d.id=1);
-- [UNCACHEABLE SUBQUERY]
EXPLAIN SELECT * FROM dept t WHERE EXISTS (SELECT id FROM dept e WHERE name='桃花岛' UNION ALL SELECT * FROM dept d WHERE t.id = d.parentId);
5. type的解释
| 名称 | 含义 | 备注 |
|---|---|---|
| system | 系统表，少量数据，往往不需要进行磁盘IO | mysql 8没能出现 |
| const | 常量连接 | |
| eq_ref | 主键索引（primary key）或者非空唯一索引（unique not null）等值扫描 | |
| ref | 非主键非唯一索引等值扫描 | |
| range | 范围扫描 | |
| index | 索引树扫描 | |
| ALL | 全表扫描（full table scan） |
6.type示例
执行效率 system > const > eq_ref > ref > range > index > ALL。
在mysql 8上system没有实验出来，有实验出来的同学欢迎留言，谢谢。
-- [index]
EXPLAIN SELECT count(1) FROM dept t;
-- [const]
EXPLAIN SELECT * FROM dept t WHERE t.id=1;
-- [range]
EXPLAIN SELECT * FROM dept t WHERE t.parentId<3 and t.parentId>1;
-- [ALL]
EXPLAIN SELECT * FROM dept WHERE name='桃花岛';
-- [eq_ref]
EXPLAIN SELECT count(1) FROM dept t JOIN dept d ON t.id=d.parentId;
-- [ref]
EXPLAIN SELECT count(1) FROM dept t WHERE t.parentId=1;
-- [Null]
EXPLAIN SELECT 1;
-- [ref_or_null]
EXPLAIN SELECT count(1) FROM dept where parentId=1 or parentId is null ;
-- [index_merge]
EXPLAIN SELECT * FROM dept where code='01' or id=1;
-- [fulltext] 没能出现
-- [unique_subquery][index_subquery] 由于Mysql会对select进行优化，无法出现这个场景
