-- 学员端“学习中心-我的课程”查询索引增量脚本(MySQL 5.7+/8.0)。 -- -- 本脚本不新增业务表或字段,只为“学员 -> 班级 -> 日期范围课程 -> 本人出勤”查询链路 -- 补齐前导索引。所有检查都比较索引列而不是固定索引名,因此可重复执行,也不会与 -- 其他模块中名称不同但等价的索引重复。 delimiter $$ drop procedure if exists migrate_student_course$$ create procedure migrate_student_course() begin if not exists ( select 1 from information_schema.tables where table_schema = database() and table_name = 'class_student' ) or not exists ( select 1 from information_schema.tables where table_schema = database() and table_name = 'class_subject' ) or not exists ( select 1 from information_schema.tables where table_schema = database() and table_name = 'class_subject_attendance' ) then signal sqlstate '45000' set message_text = '缺少班级、课程或出勤基础表,请先执行项目基础数据库脚本'; end if; -- 登录后首先按user_id反查学员所属班级,class_id同时可直接用于后续课程查询。 if not exists ( select 1 from ( select index_name, group_concat(column_name order by seq_in_index separator ',') as indexed_columns from information_schema.statistics where table_schema = database() and table_name = 'class_student' group by index_name ) member_indexes where member_indexes.indexed_columns = 'user_id,class_id' or member_indexes.indexed_columns like 'user_id,class_id,%' ) then alter table class_student add index idx_class_student_user_class (user_id, class_id); end if; -- 月历按班级集合和日期范围查询;已有相同前导列的课程管理索引可直接复用。 if not exists ( select 1 from ( select index_name, group_concat(column_name order by seq_in_index separator ',') as indexed_columns from information_schema.statistics where table_schema = database() and table_name = 'class_subject' group by index_name ) subject_indexes where subject_indexes.indexed_columns = 'class_id,date' or subject_indexes.indexed_columns like 'class_id,date,%' ) then alter table class_subject add index idx_subject_class_date (class_id, date, id); end if; -- 学员课程页面按user_id读取指定日期范围内的出勤;教师端唯一索引以subject_id -- 为前导列,不能覆盖该访问方向,因此单独补充反向查询索引。 if not exists ( select 1 from ( select index_name, group_concat(column_name order by seq_in_index separator ',') as indexed_columns from information_schema.statistics where table_schema = database() and table_name = 'class_subject_attendance' group by index_name ) attendance_indexes where attendance_indexes.indexed_columns = 'user_id,subject_id' or attendance_indexes.indexed_columns like 'user_id,subject_id,%' ) then alter table class_subject_attendance add index idx_attendance_user_subject (user_id, subject_id); end if; end$$ call migrate_student_course()$$ drop procedure if exists migrate_student_course$$ delimiter ; -- 执行后可使用以下只读查询核对最终索引,不会修改任何业务数据。 select table_name, index_name, non_unique, seq_in_index, column_name from information_schema.statistics where table_schema = database() and table_name in ('class_student', 'class_subject', 'class_subject_attendance') order by table_name, index_name, seq_in_index;