ieplus/optimize_study_record_statistics_indexes.sql

-- ============================================
-- 学习记录统计查询优化索引
-- 用于优化 statisticStudyRecord 查询性能
-- ============================================

-- 1. learn_answer 表索引优化
-- 支持按时间范围和状态过滤
CREATE INDEX IF NOT EXISTS idx_learn_answer_state_time ON learn_answer(state, create_time);
CREATE INDEX IF NOT EXISTS idx_learn_answer_examinee_state_time ON learn_answer(examinee_id, state, create_time);
CREATE INDEX IF NOT EXISTS idx_learn_answer_student_state_time ON learn_answer(student_id, state, create_time);

-- 2. learn_examinee 表索引优化
-- 支持按状态和时间范围过滤，以及JOIN条件
CREATE INDEX IF NOT EXISTS idx_learn_examinee_state_end_time ON learn_examinee(state, end_time);
CREATE INDEX IF NOT EXISTS idx_learn_examinee_examinee_state_time ON learn_examinee(examinee_id, state, end_time);
CREATE INDEX IF NOT EXISTS idx_learn_examinee_student_state_time ON learn_examinee(student_id, state, end_time);
CREATE INDEX IF NOT EXISTS idx_learn_examinee_paper_type_state ON learn_examinee(paper_type, state, paper_id);

-- 3. b_customer_video_watches 表索引优化
-- 支持按时间范围过滤和JOIN
CREATE INDEX IF NOT EXISTS idx_customer_video_watches_time ON b_customer_video_watches(time);
CREATE INDEX IF NOT EXISTS idx_customer_video_watches_customer_time ON b_customer_video_watches(customerCode, time);

-- 4. sys_user 表索引优化（如果还没有）
-- 支持JOIN操作
CREATE INDEX IF NOT EXISTS idx_sys_user_user_id ON sys_user(user_id);
CREATE INDEX IF NOT EXISTS idx_sys_user_card_id ON sys_user(card_id);

-- 5. dz_cards 表索引优化
-- 支持WHERE条件过滤和JOIN
CREATE INDEX IF NOT EXISTS idx_dz_cards_card_id ON dz_cards(card_id);
CREATE INDEX IF NOT EXISTS idx_dz_cards_dept_id ON dz_cards(dept_id);
CREATE INDEX IF NOT EXISTS idx_dz_cards_school_id ON dz_cards(school_id);
CREATE INDEX IF NOT EXISTS idx_dz_cards_class_id ON dz_cards(class_id);
CREATE INDEX IF NOT EXISTS idx_dz_cards_campus_id ON dz_cards(campus_id);
CREATE INDEX IF NOT EXISTS idx_dz_cards_campus_class_id ON dz_cards(campus_class_id);
CREATE INDEX IF NOT EXISTS idx_dz_cards_agent_id ON dz_cards(agent_id);
CREATE INDEX IF NOT EXISTS idx_dz_cards_leaf_agent_id ON dz_cards(leaf_agent_id);
CREATE INDEX IF NOT EXISTS idx_dz_cards_card_no ON dz_cards(card_no);

-- 6. dz_teacher_class 表索引优化（用于teacherId过滤）
CREATE INDEX IF NOT EXISTS idx_dz_teacher_class_teacher_id ON dz_teacher_class(teacher_id);
CREATE INDEX IF NOT EXISTS idx_dz_teacher_class_teacher_class ON dz_teacher_class(teacher_id, class_id, out_date);

-- 7. learn_student 表索引优化（如果还没有）
CREATE INDEX IF NOT EXISTS idx_learn_student_student_id ON learn_student(student_id);

-- ============================================
-- 索引使用说明
-- ============================================
-- 1. 复合索引的顺序很重要，应该按照查询条件的使用频率和选择性排序
-- 2. 时间字段的索引可以显著提升范围查询性能
-- 3. 如果表数据量很大，建议定期分析表并更新统计信息：
--    ANALYZE TABLE learn_answer;
--    ANALYZE TABLE learn_examinee;
--    ANALYZE TABLE b_customer_video_watches;
-- 4. 如果某些索引使用率很低，可以考虑删除以节省存储空间