mysql EXPLAIN语法 type类型分析

建表语句

CREATE TABLE `sys_role_dept`  (
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  `dept_id` bigint(20) NOT NULL COMMENT '部门ID',
  PRIMARY KEY (`role_id`, `dept_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色和部门关联表' ROW_FORMAT = Dynamic;
INSERT INTO `sys_role_dept` VALUES (2, 100);
INSERT INTO `sys_role_dept` VALUES (2, 101);
INSERT INTO `sys_role_dept` VALUES (2, 105);


CREATE TABLE `sys_role`  (
  `role_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名称',
  `role_key` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色权限字符串',
  `role_sort` int(4) NOT NULL COMMENT '显示顺序',
  `data_scope` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '数据范围（1：全部数据权限 2：自定数据权限 3：本部门数据权限 4：本部门及以下数据权限）',
  `menu_check_strictly` tinyint(1) NULL DEFAULT 1 COMMENT '菜单树选择项是否关联显示',
  `dept_check_strictly` tinyint(1) NULL DEFAULT 1 COMMENT '部门树选择项是否关联显示',
  `status` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色状态（0正常 1停用）',
  `del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`role_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 102 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色信息表' ROW_FORMAT = Dynamic;

INSERT INTO `sys_role` VALUES (1, '超级管理员', 'admin', 1, '1', 1, 1, '0', '0', 'admin', '2022-01-13 11:44:44', '', NULL, '超级管理员');
INSERT INTO `sys_role` VALUES (2, '普通角色', 'common', 2, '2', 1, 1, '0', '0', 'admin', '2022-01-13 11:44:44', '', NULL, '普通角色');
INSERT INTO `sys_role` VALUES (100, '厉家乐', 'w', 1, '1', 1, 1, '0', '2', 'admin', '2022-01-13 15:22:42', '', NULL, NULL);
INSERT INTO `sys_role` VALUES (101, '测试0725', 'test0725', 1, '1', 1, 1, '0', '0', 'admin', '2022-07-25 14:43:17', '', NULL, '测试0725');

角色部门表设置一个联合主键，角色表设置一个主键

all

当不使用任何索引和主键时，全表扫描，type为all，但是若表中所有字段为联合主键或者查询字段中含主键字段时，为index，看下方案例

EXPLAIN SELECT * from sys_role

index

未使用任何索引，全表扫描index索引文件，遍历索引树

EXPLAIN SELECT * from sys_role_dept 

const

使用主键查询，并且是where a = 1，的样式，a为主键或者唯一索引，若是普通字段仍为ALL

EXPLAIN SELECT * from sys_role where role_id = 100

EXPLAIN SELECT * from sys_role_dept where role_id = 2 and dept_id = 100

range

使用主键或者普通索引，利用范围查询时 type为range，即where a < 1 的样式（a如果是联合主键或者普通复合索引同样满足索引最左原则，详情请看下方演示）

EXPLAIN SELECT * from sys_role where role_id > 100

ref

当联表查询的关联字段或者单表查询的筛选字段为普通索引时 type为ref

EXPLAIN SELECT * from sys_role left join sys_role_dept on sys_role.role_id = sys_role_dept.role_id

这句sql先查角色表，后查角色部门表，第一张表未使用主键和索引，type为all，第二张表筛选字段为role_id ，在第二张表中仅是联合主键中的第一位，并不是唯一主键，所以type为ref

eq_ref

当联表查询的关联字段为唯一索引或者主键时 type为eq_ref

EXPLAIN SELECT * from sys_role right join sys_role_dept on sys_role.role_id = sys_role_dept.role_id

这句sql先查角色部门表，后查角色表，第一张表type为index，第二张表筛选条件为role_id，在角色表中为唯一主键，所以type为eq_ref

联合主键和普通复合索引的最左原则区别

联合主键若满足最左原则，type为const/range/ref，不满足为index
普通复合索引满足最左原则为ref/range，不满足为all（这里不做演示）

EXPLAIN SELECT * from sys_role_dept where dept_id = 100 and role_id = 2

当where后面的条件出现联合主键的全部字段时，type为const

EXPLAIN SELECT * from sys_role_dept where role_id = 2

当where后面的条件出现联合主键的第一个字段时，type为ref

EXPLAIN SELECT * from sys_role_dept where role_id > 2

当where后面的条件出现联合主键的第一个字段并且范围筛选时，type为range

EXPLAIN SELECT * from sys_role_dept where dept_id = 100

当where后面的条件出现联合主键的第二个字段时，type为index

总结

在实际应用中，type级别至少满足range级别，更优为ref及const
index比all更优，但是并不明显，性能都很差