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
- 1
index
未使用任何索引,全表扫描index索引文件,遍历索引树
EXPLAIN SELECT * from sys_role_dept
- 1
const
使用主键查询,并且是where a = 1,的样式,a为主键或者唯一索引,若是普通字段仍为ALL
EXPLAIN SELECT * from sys_role where role_id = 100
- 1
EXPLAIN SELECT * from sys_role_dept where role_id = 2 and dept_id = 100
- 1
range
使用主键或者普通索引,利用范围查询时 type为range,即where a < 1 的样式(a如果是联合主键或者普通复合索引同样满足索引最左原则,详情请看下方演示)
EXPLAIN SELECT * from sys_role where role_id > 100
- 1
ref
当联表查询的关联字段或者单表查询的筛选字段为普通索引时 type为ref
EXPLAIN SELECT * from sys_role left join sys_role_dept on sys_role.role_id = sys_role_dept.role_id
- 1
这句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
- 1
这句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
- 1
当where后面的条件出现联合主键的全部字段时,type为const
EXPLAIN SELECT * from sys_role_dept where role_id = 2
- 1
当where后面的条件出现联合主键的第一个字段时,type为ref
EXPLAIN SELECT * from sys_role_dept where role_id > 2
- 1
当where后面的条件出现联合主键的第一个字段并且范围筛选时,type为range
EXPLAIN SELECT * from sys_role_dept where dept_id = 100
- 1
当where后面的条件出现联合主键的第二个字段时,type为index
总结
在实际应用中,type级别至少满足range级别,更优为ref及const
index比all更优,但是并不明显,性能都很差
————————————————
版权声明:本文为CSDN博主「来一碗剔尖儿」的原创文章
原文链接:https://blog.csdn.net/weixin_60589038/article/details/128369069