ieplus/sql/add_indexes_for_learn_dz.sql

-- ============================================
-- 索引优化 SQL 脚本
-- 基于 learn 和 dz 目录下的 Mapper XML 分析生成
-- ============================================

-- ============================================
-- learn 目录相关表的索引
-- ============================================

-- learn_answer 表索引
CREATE INDEX idx_learn_answer_examinee_id ON learn_answer(examinee_id);
CREATE INDEX idx_learn_answer_student_id ON learn_answer(student_id);
CREATE INDEX idx_learn_answer_question_id ON learn_answer(question_id);
CREATE INDEX idx_learn_answer_knowledge_id ON learn_answer(knowledge_id);
CREATE INDEX idx_learn_answer_state ON learn_answer(state);
CREATE INDEX idx_learn_answer_create_time ON learn_answer(create_time);
CREATE INDEX idx_learn_answer_student_state ON learn_answer(student_id, state);
CREATE INDEX idx_learn_answer_examinee_state ON learn_answer(examinee_id, state);
CREATE INDEX idx_learn_answer_student_knowledge ON learn_answer(student_id, knowledge_id, state);
CREATE INDEX idx_learn_answer_student_time ON learn_answer(student_id, create_time);

-- learn_examinee 表索引
CREATE INDEX idx_learn_examinee_student_id ON learn_examinee(student_id);
CREATE INDEX idx_learn_examinee_paper_type ON learn_examinee(paper_type);
CREATE INDEX idx_learn_examinee_paper_id ON learn_examinee(paper_id);
CREATE INDEX idx_learn_examinee_state ON learn_examinee(state);
CREATE INDEX idx_learn_examinee_end_time ON learn_examinee(end_time);
CREATE INDEX idx_learn_examinee_student_paper_type ON learn_examinee(student_id, paper_type);
CREATE INDEX idx_learn_examinee_student_state ON learn_examinee(student_id, state);
CREATE INDEX idx_learn_examinee_paper_type_state ON learn_examinee(paper_type, state);
CREATE INDEX idx_learn_examinee_paper_ids ON learn_examinee(paper_type, paper_id);

-- learn_paper 表索引
CREATE INDEX idx_learn_paper_subject_id ON learn_paper(subjectId);
CREATE INDEX idx_learn_paper_paper_type ON learn_paper(paperType);
CREATE INDEX idx_learn_paper_direct_key ON learn_paper(direct_key);
CREATE INDEX idx_learn_paper_year ON learn_paper(year);
CREATE INDEX idx_learn_paper_status ON learn_paper(status);
CREATE INDEX idx_learn_paper_subject_type ON learn_paper(subjectId, paperType);
CREATE INDEX idx_learn_paper_direct_key_prefix ON learn_paper(direct_key(50));

-- learn_paper_question 表索引
CREATE INDEX idx_learn_paper_question_paper_id ON learn_paper_question(paper_id);
CREATE INDEX idx_learn_paper_question_question_id ON learn_paper_question(question_id);
CREATE INDEX idx_learn_paper_question_knowledge_id ON learn_paper_question(knowledge_id);
CREATE INDEX idx_learn_paper_question_seq ON learn_paper_question(paper_id, seq);
CREATE INDEX idx_learn_paper_question_paper_seq ON learn_paper_question(paper_id, question_id, seq);

-- learn_paper_real 表索引
CREATE INDEX idx_learn_paper_real_subject_id ON learn_paper_real(subjectId);
CREATE INDEX idx_learn_paper_real_paper_type ON learn_paper_real(paperType);
CREATE INDEX idx_learn_paper_real_year ON learn_paper_real(year);
CREATE INDEX idx_learn_paper_real_online ON learn_paper_real(online);

-- learn_questions 表索引
CREATE INDEX idx_learn_questions_subject_id ON learn_questions(subjectId);
CREATE INDEX idx_learn_questions_knowledge_id ON learn_questions(knowledgeId);
CREATE INDEX idx_learn_questions_qtpye ON learn_questions(qtpye);
CREATE INDEX idx_learn_questions_paper_id ON learn_questions(paperId);
CREATE INDEX idx_learn_questions_subject_knowledge ON learn_questions(subjectId, knowledgeId);
CREATE INDEX idx_learn_questions_knowledge_type ON learn_questions(knowledgeId, qtpye);
CREATE INDEX idx_learn_questions_md5 ON learn_questions(md5);
CREATE INDEX idx_learn_questions_md52 ON learn_questions(md52);

-- learn_knowledge_question 表索引
CREATE INDEX idx_learn_knowledge_question_knowledge_id ON learn_knowledge_question(knowledge_id);
CREATE INDEX idx_learn_knowledge_question_question_id ON learn_knowledge_question(question_id);
CREATE INDEX idx_learn_knowledge_question_type ON learn_knowledge_question(type);
CREATE INDEX idx_learn_knowledge_question_knowledge_type ON learn_knowledge_question(knowledge_id, type);

-- learn_knowledge_tree 表索引
CREATE INDEX idx_learn_knowledge_tree_pid ON learn_knowledge_tree(pid);
CREATE INDEX idx_learn_knowledge_tree_subject_id ON learn_knowledge_tree(subjectId);
CREATE INDEX idx_learn_knowledge_tree_ancestors ON learn_knowledge_tree(ancestors(100));
CREATE INDEX idx_learn_knowledge_tree_subject_pid ON learn_knowledge_tree(subjectId, pid);

-- learn_knowledge_course 表索引
CREATE INDEX idx_learn_knowledge_course_pid ON learn_knowledge_course(pid);
CREATE INDEX idx_learn_knowledge_course_ancestors ON learn_knowledge_course(ancestors(100));

-- learn_student 表索引
CREATE INDEX idx_learn_student_student_id ON learn_student(student_id);
CREATE INDEX idx_learn_student_class_id ON learn_student(class_id);
CREATE INDEX idx_learn_student_university_id ON learn_student(university_id);
CREATE INDEX idx_learn_student_major_plan_id ON learn_student(major_plan_id);
CREATE INDEX idx_learn_student_direct_key ON learn_student(direct_key);
CREATE INDEX idx_learn_student_school_id ON learn_student(school_id);
CREATE INDEX idx_learn_student_campus_id ON learn_student(campus_id);
CREATE INDEX idx_learn_student_major_group ON learn_student(major_group);
CREATE INDEX idx_learn_student_class_university ON learn_student(class_id, university_id);

-- learn_test 表索引
CREATE INDEX idx_learn_test_year ON learn_test(year);
CREATE INDEX idx_learn_test_creator_id ON learn_test(creator_id);
CREATE INDEX idx_learn_test_year_creator ON learn_test(year, creator_id);

-- learn_test_paper 表索引
CREATE INDEX idx_learn_test_paper_batch_id ON learn_test_paper(batch_id);
CREATE INDEX idx_learn_test_paper_build_type ON learn_test_paper(build_type);
CREATE INDEX idx_learn_test_paper_subject_id ON learn_test_paper(subject_id);
CREATE INDEX idx_learn_test_paper_teacher_id ON learn_test_paper(teacher_id);
CREATE INDEX idx_learn_test_paper_university_id ON learn_test_paper(university_id);
CREATE INDEX idx_learn_test_paper_direct_key ON learn_test_paper(direct_key);
CREATE INDEX idx_learn_test_paper_batch_build_subject ON learn_test_paper(batch_id, build_type, subject_id);
CREATE INDEX idx_learn_test_paper_batch_university ON learn_test_paper(batch_id, university_id);

-- learn_test_student 表索引
CREATE INDEX idx_learn_test_student_batch_id ON learn_test_student(batch_id);
CREATE INDEX idx_learn_test_student_student_id ON learn_test_student(student_id);
CREATE INDEX idx_learn_test_student_build_type ON learn_test_student(build_type);
CREATE INDEX idx_learn_test_student_subject_id ON learn_test_student(subject_id);
CREATE INDEX idx_learn_test_student_paper_id ON learn_test_student(paper_id);
CREATE INDEX idx_learn_test_student_status ON learn_test_student(status);
CREATE INDEX idx_learn_test_student_examinee_id ON learn_test_student(examinee_id);
CREATE INDEX idx_learn_test_student_class_id ON learn_test_student(class_id);
CREATE INDEX idx_learn_test_student_batch_student ON learn_test_student(batch_id, student_id);
CREATE INDEX idx_learn_test_student_batch_build_subject ON learn_test_student(batch_id, build_type, subject_id);
CREATE INDEX idx_learn_test_student_student_batch ON learn_test_student(student_id, batch_id);
CREATE INDEX idx_learn_test_student_student_status ON learn_test_student(student_id, status);

-- learn_plan 表索引
CREATE INDEX idx_learn_plan_student_id ON learn_plan(studentId);
CREATE INDEX idx_learn_plan_status ON learn_plan(status);

-- learn_plan_study 表索引
CREATE INDEX idx_learn_plan_study_student_id ON learn_plan_study(student_id);
CREATE INDEX idx_learn_plan_study_plan_id ON learn_plan_study(plan_id);
CREATE INDEX idx_learn_plan_study_report_date ON learn_plan_study(report_date);
CREATE INDEX idx_learn_plan_study_month_seq ON learn_plan_study(month_seq);
CREATE INDEX idx_learn_plan_study_student_plan ON learn_plan_study(student_id, plan_id);
CREATE INDEX idx_learn_plan_study_student_date ON learn_plan_study(student_id, report_date);
CREATE INDEX idx_learn_plan_study_plan_month ON learn_plan_study(plan_id, month_seq);

-- learn_wrong_book 表索引
CREATE INDEX idx_learn_wrong_book_student_id ON learn_wrong_book(student_id);
CREATE INDEX idx_learn_wrong_book_question_id ON learn_wrong_book(question_id);
CREATE INDEX idx_learn_wrong_book_subject_id ON learn_wrong_book(subject_id);
CREATE INDEX idx_learn_wrong_book_paper_id ON learn_wrong_book(paper_id);
CREATE INDEX idx_learn_wrong_book_knownledge_id ON learn_wrong_book(knownledge_id);
CREATE INDEX idx_learn_wrong_book_created_time ON learn_wrong_book(created_time);
CREATE INDEX idx_learn_wrong_book_student_question ON learn_wrong_book(student_id, question_id);
CREATE INDEX idx_learn_wrong_book_student_subject ON learn_wrong_book(student_id, subject_id);
CREATE INDEX idx_learn_wrong_book_student_time ON learn_wrong_book(student_id, created_time);

-- learn_wrong_detail 表索引
CREATE INDEX idx_learn_wrong_detail_wrong_id ON learn_wrong_detail(wrong_id);
CREATE INDEX idx_learn_wrong_detail_student_id ON learn_wrong_detail(student_id);
CREATE INDEX idx_learn_wrong_detail_examinee_id ON learn_wrong_detail(examinee_id);
CREATE INDEX idx_learn_wrong_detail_paper_id ON learn_wrong_detail(paper_id);

-- learn_question_correct 表索引
CREATE INDEX idx_learn_question_correct_question_id ON learn_question_correct(question_id);
CREATE INDEX idx_learn_question_correct_user_id ON learn_question_correct(user_id);
CREATE INDEX idx_learn_question_correct_state ON learn_question_correct(state);
CREATE INDEX idx_learn_question_correct_question_user ON learn_question_correct(question_id, user_id);

-- learn_culture_knowledge 表索引
CREATE INDEX idx_learn_culture_knowledge_year ON learn_culture_knowledge(year);
CREATE INDEX idx_learn_culture_knowledge_university_id ON learn_culture_knowledge(university_id);
CREATE INDEX idx_learn_culture_knowledge_year_university ON learn_culture_knowledge(year, university_id);

-- learn_directed_knowledge 表索引
CREATE INDEX idx_learn_directed_knowledge_year ON learn_directed_knowledge(year);
CREATE INDEX idx_learn_directed_knowledge_university_id ON learn_directed_knowledge(university_id);
CREATE INDEX idx_learn_directed_knowledge_direct_key ON learn_directed_knowledge(direct_key);
CREATE INDEX idx_learn_directed_knowledge_year_university ON learn_directed_knowledge(year, university_id);

-- ============================================
-- dz 目录相关表的索引
-- ============================================

-- dz_agent 表索引
CREATE INDEX idx_dz_agent_agent_id ON dz_agent(agent_id);
CREATE INDEX idx_dz_agent_user_id ON dz_agent(user_id);
CREATE INDEX idx_dz_agent_dept_id ON dz_agent(dept_id);
CREATE INDEX idx_dz_agent_parent_id ON dz_agent(parent_id);
CREATE INDEX idx_dz_agent_parent_dept ON dz_agent(parent_id, dept_id);

-- dz_cards 表索引
CREATE INDEX idx_dz_cards_card_no ON dz_cards(card_no);
CREATE INDEX idx_dz_cards_card_id ON dz_cards(card_id);
CREATE INDEX idx_dz_cards_agent_id ON dz_cards(agent_id);
CREATE INDEX idx_dz_cards_leaf_agent_id ON dz_cards(leaf_agent_id);
CREATE INDEX idx_dz_cards_dept_id ON dz_cards(dept_id);
CREATE INDEX idx_dz_cards_school_id ON dz_cards(school_id);
CREATE INDEX idx_dz_cards_class_id ON dz_cards(class_id);
CREATE INDEX idx_dz_cards_campus_id ON dz_cards(campus_id);
CREATE INDEX idx_dz_cards_campus_class_id ON dz_cards(campus_class_id);
CREATE INDEX idx_dz_cards_distribute_status ON dz_cards(distribute_status);
CREATE INDEX idx_dz_cards_pay_status ON dz_cards(pay_status);
CREATE INDEX idx_dz_cards_type ON dz_cards(type);
CREATE INDEX idx_dz_cards_status ON dz_cards(status);
CREATE INDEX idx_dz_cards_distribute_time ON dz_cards(distribute_time);
CREATE INDEX idx_dz_cards_open_time ON dz_cards(open_time);
CREATE INDEX idx_dz_cards_agent_leaf ON dz_cards(agent_id, leaf_agent_id);
CREATE INDEX idx_dz_cards_dept_type ON dz_cards(dept_id, type);
CREATE INDEX idx_dz_cards_type_status ON dz_cards(type, distribute_status);
CREATE INDEX idx_dz_cards_type_pay_status ON dz_cards(type, pay_status);
CREATE INDEX idx_dz_cards_school_class ON dz_cards(school_id, class_id);
CREATE INDEX idx_dz_cards_campus_class ON dz_cards(campus_id, campus_class_id);
CREATE INDEX idx_dz_cards_distribute_time_date ON dz_cards(DATE(distribute_time));

-- dz_cards_open 表索引
CREATE INDEX idx_dz_cards_open_agent_id ON dz_cards_open(agent_id);
CREATE INDEX idx_dz_cards_open_dept_id ON dz_cards_open(dept_id);
CREATE INDEX idx_dz_cards_open_school_id ON dz_cards_open(school_id);
CREATE INDEX idx_dz_cards_open_status ON dz_cards_open(status);
CREATE INDEX idx_dz_cards_open_card_type ON dz_cards_open(card_type);
CREATE INDEX idx_dz_cards_open_start_end ON dz_cards_open(start_no, end_no);

-- dz_classes 表索引
CREATE INDEX idx_dz_classes_class_id ON dz_classes(class_id);
CREATE INDEX idx_dz_classes_school_id ON dz_classes(school_id);
CREATE INDEX idx_dz_classes_dept_id ON dz_classes(dept_id);
CREATE INDEX idx_dz_classes_year ON dz_classes(year);
CREATE INDEX idx_dz_classes_school_year ON dz_classes(school_id, year);
CREATE INDEX idx_dz_classes_school_name ON dz_classes(school_id, name);
CREATE INDEX idx_dz_classes_dept_school ON dz_classes(dept_id, school_id);

-- dz_school 表索引
CREATE INDEX idx_dz_school_id ON dz_school(id);
CREATE INDEX idx_dz_school_dept_id ON dz_school(dept_id);
CREATE INDEX idx_dz_school_location ON dz_school(location);
CREATE INDEX idx_dz_school_exam_types ON dz_school(exam_types(50));
CREATE INDEX idx_dz_school_dept_location ON dz_school(dept_id, location);

-- dz_teacher 表索引
CREATE INDEX idx_dz_teacher_teacher_id ON dz_teacher(teacher_id);
CREATE INDEX idx_dz_teacher_user_id ON dz_teacher(user_id);
CREATE INDEX idx_dz_teacher_dept_id ON dz_teacher(dept_id);
CREATE INDEX idx_dz_teacher_agent_id ON dz_teacher(agent_id);
CREATE INDEX idx_dz_teacher_school_id ON dz_teacher(school_id);
CREATE INDEX idx_dz_teacher_campus_id ON dz_teacher(campus_id);
CREATE INDEX idx_dz_teacher_agent_school ON dz_teacher(agent_id, school_id);
CREATE INDEX idx_dz_teacher_dept_agent ON dz_teacher(dept_id, agent_id);

-- dz_teacher_class 表索引
CREATE INDEX idx_dz_teacher_class_teacher_id ON dz_teacher_class(teacher_id);
CREATE INDEX idx_dz_teacher_class_class_id ON dz_teacher_class(class_id);
CREATE INDEX idx_dz_teacher_class_school_id ON dz_teacher_class(school_id);
CREATE INDEX idx_dz_teacher_class_out_date ON dz_teacher_class(out_date);
CREATE INDEX idx_dz_teacher_class_teacher_class ON dz_teacher_class(teacher_id, class_id);
CREATE INDEX idx_dz_teacher_class_teacher_out_date ON dz_teacher_class(teacher_id, out_date);
CREATE INDEX idx_dz_teacher_class_class_out_date ON dz_teacher_class(class_id, out_date);

-- dz_subject 表索引
CREATE INDEX idx_dz_subject_subject_id ON dz_subject(subject_id);
CREATE INDEX idx_dz_subject_sort ON dz_subject(sort);
CREATE INDEX idx_dz_subject_locations ON dz_subject(locations(50));
CREATE INDEX idx_dz_subject_exam_types ON dz_subject(exam_types(50));
CREATE INDEX idx_dz_subject_sort_subject ON dz_subject(sort, subject_id);

-- dz_control 表索引
CREATE INDEX idx_dz_control_location ON dz_control(location);
CREATE INDEX idx_dz_control_is_valid ON dz_control(is_valid);
CREATE INDEX idx_dz_control_location_valid ON dz_control(location, is_valid);

-- dz_payment_orders 表索引
CREATE INDEX idx_dz_payment_orders_card_id ON dz_payment_orders(cardId);
CREATE INDEX idx_dz_payment_orders_card_no ON dz_payment_orders(cardNo);
CREATE INDEX idx_dz_payment_orders_code ON dz_payment_orders(code);
CREATE INDEX idx_dz_payment_orders_out_trade_no ON dz_payment_orders(outTradeNo);
CREATE INDEX idx_dz_payment_orders_status ON dz_payment_orders(status);
CREATE INDEX idx_dz_payment_orders_create_time ON dz_payment_orders(createTime);
CREATE INDEX idx_dz_payment_orders_pay_time ON dz_payment_orders(payTime);
CREATE INDEX idx_dz_payment_orders_customer_code ON dz_payment_orders(customerCode);

-- dz_select_subject 表索引
CREATE INDEX idx_dz_select_subject_group_id ON dz_select_subject(group_id);
CREATE INDEX idx_dz_select_subject_rank ON dz_select_subject(rank);

-- ============================================
-- 关联表和外键相关索引
-- ============================================

-- sys_user 表相关索引（用于 JOIN 查询）
-- 注意：如果 sys_user 表不在当前数据库，请根据实际情况调整
-- CREATE INDEX idx_sys_user_card_id ON sys_user(card_id);
-- CREATE INDEX idx_sys_user_exam_type ON sys_user(exam_type);
-- CREATE INDEX idx_sys_user_user_type ON sys_user(user_type);
-- CREATE INDEX idx_sys_user_user_type_id ON sys_user(user_type_id);

-- b_customer_video_watches 表相关索引（用于 JOIN 查询）
-- 注意：如果该表不在当前数据库，请根据实际情况调整
-- CREATE INDEX idx_b_customer_video_watches_customer_code ON b_customer_video_watches(customerCode);
-- CREATE INDEX idx_b_customer_video_watches_time ON b_customer_video_watches(time);
-- CREATE INDEX idx_b_customer_video_watches_customer_time ON b_customer_video_watches(customerCode, time);

-- mxjb_question_collection 表相关索引（用于 JOIN 查询）
-- 注意：如果该表不在当前数据库，请根据实际情况调整
-- CREATE INDEX idx_mxjb_question_collection_user_id ON mxjb_question_collection(user_id);
-- CREATE INDEX idx_mxjb_question_collection_question_id ON mxjb_question_collection(question_id);
-- CREATE INDEX idx_mxjb_question_collection_user_question ON mxjb_question_collection(user_id, question_id);

-- ============================================
-- 复合索引说明
-- ============================================
-- 1. 复合索引的顺序很重要，应该将选择性高的字段放在前面
-- 2. 对于经常一起使用的 WHERE 条件，创建复合索引可以提高查询效率
-- 3. 对于 JOIN 操作，在关联字段上创建索引可以显著提高性能
-- 4. 对于 ORDER BY 和 GROUP BY 操作，相应的字段索引也有帮助
-- 5. 对于日期范围查询，在日期字段上创建索引很重要
-- 6. 对于 LIKE 查询，如果使用前缀匹配（如 'prefix%'），索引仍然有效
-- 7. 对于字符串字段的部分索引（如 direct_key(50)），可以减少索引大小

-- ============================================
-- 注意事项
-- ============================================
-- 1. 在生产环境执行前，请先在测试环境验证
-- 2. 创建索引会占用存储空间，并可能影响 INSERT/UPDATE/DELETE 性能
-- 3. 建议在业务低峰期执行索引创建
-- 4. 对于大表，索引创建可能需要较长时间
-- 5. 可以使用 ALTER TABLE ... ADD INDEX 语法，如果索引已存在会报错
-- 6. 建议使用 SHOW INDEX FROM table_name 检查现有索引，避免重复创建