ylrz_his_scrm_java/sql/optimize_fs_course_watch_log.sql

-- ======================================================
-- fs_course_watch_log 索引优化（不锁表操作）
-- MySQL 8.0.30+，使用 ALGORITHM=INPLACE, LOCK=NONE
-- 建议在业务低峰期执行，逐条或分批
-- ======================================================

-- ==================== 第一批：删除冗余索引 ====================

-- 单列索引（被复合索引前缀覆盖，冗余）
ALTER TABLE fs_course_watch_log DROP INDEX user_id, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX index_video_id, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX index_log_type, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX index_company_id, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX index_company_user_id, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX index_reward_type, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX index_send_msg, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX qw_user_id, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX qw_external_contact_id, ALGORITHM=INPLACE, LOCK=NONE;

-- 重复/重叠复合索引
ALTER TABLE fs_course_watch_log DROP INDEX idx_user_video_qw_user, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_company_logid, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_create_company_user, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_w_ctime_course_vid_uid_log, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX sendTypeAndCreate, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX index_0, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_fwl_video_user_logtype, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX index_2, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_send_type_log, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_covering_watch, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_covering_date_stats, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_group_company, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE fs_course_watch_log DROP INDEX idx_ultimate_watch, ALGORITHM=INPLACE, LOCK=NONE;


-- ==================== 第二批：新增核心索引 ====================

-- 1. 看课状态查询 + 批量更新（video_id, user_id, company_user_id）
-- 涵盖：getWatchLogByFsUser, batchUpdateFsUserWatchLog, batchUpdateWatchLogIsOpen
ALTER TABLE fs_course_watch_log 
  ADD INDEX idx_video_user_cu (video_id, user_id, company_user_id),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 2. 统计列表 + 定时任务按公司统计
-- 涵盖：selectFsCourseWatchLogStatisticsListVONew, watchCourseStatisticsGroupByCompany
ALTER TABLE fs_course_watch_log 
  ADD INDEX idx_send_time_company (send_type, create_time, company_id),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 3. 用户维度聚合查询（7天/15天统计）
-- 涵盖：getUserWatchStatusAndLastWatchDate, FsUserCourseCountMapper 相关查询
ALTER TABLE fs_course_watch_log 
  ADD INDEX idx_uid_send_time_proj (user_id, send_type, create_time, project),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 4. 销售维度 count/统计（company_user_id + log_type + 时间范围）
-- 涵盖：countByMap, queryCompanyUserWatchCount, queryCompanyUserWatchCountCompleted, queryCompanyUserInterruptCount
ALTER TABLE fs_course_watch_log 
  ADD INDEX idx_cu_logtype_time (company_user_id, log_type, create_time),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 5. 营期 + 销售查询
-- 涵盖：queryCompanyUserWatchCount(camp_period_time过滤)
ALTER TABLE fs_course_watch_log 
  ADD INDEX idx_cu_camp_log (company_user_id, camp_period_time, log_type),
  ALGORITHM=INPLACE, LOCK=NONE;


-- ==================== 验证 ====================
-- 执行后查看最终索引列表
-- SHOW INDEX FROM fs_course_watch_log;
--
-- 最终保留索引（共12个）:
--   1. PRIMARY              (log_id)
--   2. UNIQUE one_user      (video_id, qw_external_contact_id, qw_user_id)
--   3. index_finish_time    (finish_time)
--   4. create_time          (create_time)
--   5. idx_period_id        (period_id)
--   6. index_1              (sop_id, log_type)
--   7. idx_video_user_cu    (video_id, user_id, company_user_id)      [NEW]
--   8. idx_send_time_company(send_type, create_time, company_id)      [NEW]
--   9. idx_uid_send_time_proj(user_id, send_type, create_time, project) [NEW]
--  10. idx_cu_logtype_time  (company_user_id, log_type, create_time)  [NEW]
--  11. idx_cu_camp_log      (company_user_id, camp_period_time, log_type) [NEW]
--  12. idx_finish_time      (finish_time)