112 lines
4.8 KiB
SQL
112 lines
4.8 KiB
SQL
-- 系统用户分页查询索引优化。
|
|
--
|
|
-- 适用接口：POST /admin/user/page、POST /admin/user/select。
|
|
-- 执行影响：仅新增普通索引，不修改字段、约束和历史数据；建索引期间会消耗 IO，建议在业务低峰期执行。
|
|
-- 执行前建议：先备份数据库，并使用文末查询核对生产库已有索引及 EXPLAIN 计划。
|
|
|
|
-- 用户分页的默认排序索引：覆盖租户、逻辑删除和 create_time + user_id 稳定倒序分页。
|
|
SET @index_ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'sys_user'
|
|
AND index_name = 'idx_sys_user_tenant_del_created'
|
|
),
|
|
'SELECT ''idx_sys_user_tenant_del_created already exists'' AS message',
|
|
'ALTER TABLE `sys_user` ADD INDEX `idx_sys_user_tenant_del_created` (`tenant_id`, `del_flag`, `create_time`, `user_id`)'
|
|
);
|
|
PREPARE index_stmt FROM @index_ddl;
|
|
EXECUTE index_stmt;
|
|
DEALLOCATE PREPARE index_stmt;
|
|
|
|
-- 部门数据权限和用户状态分页索引：先缩小部门及审核状态范围，再读取稳定排序列。
|
|
SET @index_ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'sys_user'
|
|
AND index_name = 'idx_sys_user_tenant_del_dept_status_created'
|
|
),
|
|
'SELECT ''idx_sys_user_tenant_del_dept_status_created already exists'' AS message',
|
|
'ALTER TABLE `sys_user` ADD INDEX `idx_sys_user_tenant_del_dept_status_created` (`tenant_id`, `del_flag`, `dept_id`, `process_status`, `create_time`, `user_id`)'
|
|
);
|
|
PREPARE index_stmt FROM @index_ddl;
|
|
EXECUTE index_stmt;
|
|
DEALLOCATE PREPARE index_stmt;
|
|
|
|
-- 角色反向筛选索引：现有主键通常为 user_id + role_id，本索引用于从角色快速定位用户。
|
|
SET @index_ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'sys_user_role'
|
|
AND index_name = 'idx_sys_user_role_role_user'
|
|
),
|
|
'SELECT ''idx_sys_user_role_role_user already exists'' AS message',
|
|
'ALTER TABLE `sys_user_role` ADD INDEX `idx_sys_user_role_role_user` (`role_id`, `user_id`)'
|
|
);
|
|
PREPARE index_stmt FROM @index_ddl;
|
|
EXECUTE index_stmt;
|
|
DEALLOCATE PREPARE index_stmt;
|
|
|
|
-- 当前页人脸聚合及有无人脸 EXISTS 筛选索引，避免按用户查询时回表扫描租户全部人脸。
|
|
SET @index_ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'sys_user_face'
|
|
AND index_name = 'idx_sys_user_face_tenant_user_del'
|
|
),
|
|
'SELECT ''idx_sys_user_face_tenant_user_del already exists'' AS message',
|
|
'ALTER TABLE `sys_user_face` ADD INDEX `idx_sys_user_face_tenant_user_del` (`tenant_id`, `user_id`, `del_flag`)'
|
|
);
|
|
PREPARE index_stmt FROM @index_ddl;
|
|
EXECUTE index_stmt;
|
|
DEALLOCATE PREPARE index_stmt;
|
|
|
|
-- 身份类型反向筛选索引：支持 identity_type 条件先定位用户，用户 ID 同时用于半连接。
|
|
SET @index_ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'sys_user_identity'
|
|
AND index_name = 'idx_sys_user_identity_type_user'
|
|
),
|
|
'SELECT ''idx_sys_user_identity_type_user already exists'' AS message',
|
|
'ALTER TABLE `sys_user_identity` ADD INDEX `idx_sys_user_identity_type_user` (`identity_type`, `user_id`)'
|
|
);
|
|
PREPARE index_stmt FROM @index_ddl;
|
|
EXECUTE index_stmt;
|
|
DEALLOCATE PREPARE index_stmt;
|
|
|
|
-- 数据权限计算会读取当前租户有效部门并在内存构建子树，本索引降低部门全集读取成本。
|
|
SET @index_ddl = IF(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'sys_dept'
|
|
AND index_name = 'idx_sys_dept_tenant_del_parent'
|
|
),
|
|
'SELECT ''idx_sys_dept_tenant_del_parent already exists'' AS message',
|
|
'ALTER TABLE `sys_dept` ADD INDEX `idx_sys_dept_tenant_del_parent` (`tenant_id`, `del_flag`, `parent_id`, `dept_id`)'
|
|
);
|
|
PREPARE index_stmt FROM @index_ddl;
|
|
EXECUTE index_stmt;
|
|
DEALLOCATE PREPARE index_stmt;
|
|
|
|
-- 执行后核对新增索引；确认无重复或同前缀冗余索引后，再按生产变更流程决定是否清理旧索引。
|
|
SELECT table_name, index_name, seq_in_index, column_name
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name IN ('sys_user', 'sys_user_role', 'sys_user_face', 'sys_user_identity', 'sys_dept')
|
|
ORDER BY table_name, index_name, seq_in_index;
|
|
|
|
-- 建议在执行索引后更新统计信息，并用真实租户、部门和状态参数执行 EXPLAIN ANALYZE。
|
|
-- ANALYZE TABLE sys_user, sys_user_role, sys_user_face, sys_user_identity, sys_dept;
|