-- ============================================================================
-- smartor_sltd 数据库 满意度维度统计接口性能优化 —— 索引脚本
-- 生成日期: 20260924
-- 目标接口: POST /smartor/serviceSubtaskDetail/sltdMydTotalByDimension
--           (dimensiontType=1 → ServiceSubtaskMapper.sltdMydTotalByDimension
--            dimensiontType=2 → ServiceSubtaskMapper.sltdMydTotalByYQ，同样受益)
-- 目标 SQL: smartor/src/main/resources/mapper/smartor/ServiceSubtaskMapper.xml:3050
--
-- 【问题定位】（线上库实测：MySQL 5.7.34，service_subtask_detail 102,249 行 /
--  service_task 153 行，两张表除主键外没有任何二级索引）
--
--  EXPLAIN 结果显示 4 个访问路径全部是 type=ALL 全表扫描 + Block Nested Loop：
--    id=2 DERIVED  service_subtask_detail  ALL  rows=101778   -- period 时间段子查询
--    id=3 DERIVED  service_subtask_detail  ALL  rows=101778   -- dimension 维度子查询
--    id=1 PRIMARY  t (service_task)        ALL  rows=153
--    id=1 PRIMARY  d (service_subtask_detail) ALL rows=101778 -- Using join buffer
--
--  即：主查询里 d.taskid = t.taskid 没有索引，102,778 行的明细表被当成 BNL 内表
--  反复全表扫描，这是接口慢的根本原因；两个派生表也各自全表扫描一次。
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 【执行前检查】确认当前确实没有这些索引（正常应只看到 PRIMARY）
-- ----------------------------------------------------------------------------
-- SHOW INDEX FROM service_subtask_detail;
-- SHOW INDEX FROM service_task;
-- SELECT COUNT(*) FROM service_subtask_detail;   -- 约 10 万行，加索引约需 10~60 秒


-- ----------------------------------------------------------------------------
-- 【步骤 1】service_subtask_detail 增加 3 个索引
--
--  1) idx_ssd_taskid_flag_dim_ct (taskid, del_flag, dimension, create_time)
--     用途：主查询的 JOIN 条件 d.taskid = t.taskid
--           + d.del_flag = '0' + d.dimension = dim.dimension（等值）
--           + d.create_time 区间（范围）。
--     效果：由「全表扫描 10 万行」变为「按 taskid 精确定位约 700 行」。
--
--  2) idx_ssd_ct_flag (create_time, del_flag)
--     用途：period 时间段子查询
--           WHERE del_flag='0' AND create_time >= ? AND create_time < ?
--     效果：create_time 走范围扫描；del_flag 虽在索引第二位但仍可做
--           Index Condition Pushdown 过滤，不必回表，省掉 10 万行回表。
--     （注意：del_flag 必须放第二位，把 create_time 放第一位才能用上范围扫描）
--
--  3) idx_ssd_flag_dim (del_flag, dimension)
--     用途：dim 维度子查询
--           SELECT DISTINCT dimension FROM service_subtask_detail
--           WHERE del_flag='0' AND dimension IS NOT NULL
--     效果：索引覆盖该子查询，可走松散索引扫描（Using index for group-by），
--           dimension 只有 16 个不同值，基本瞬间返回。
--
--  MySQL 5.7 的 ALTER TABLE 加二级索引支持 ONLINE DDL（不锁表、不阻塞读写），
--  下面显式声明 ALGORITHM=INPLACE, LOCK=NONE；三条索引放在同一条 ALTER 里，
--  只需扫描一次表，比分开执行快。若中途报错不支持，去掉这两个选项重试即可。
-- ----------------------------------------------------------------------------
ALTER TABLE service_subtask_detail
    ADD INDEX idx_ssd_taskid_flag_dim_ct (taskid, del_flag, dimension, create_time),
    ADD INDEX idx_ssd_ct_flag (create_time, del_flag),
    ADD INDEX idx_ssd_flag_dim (del_flag, dimension),
    ALGORITHM = INPLACE,
    LOCK = NONE;


-- ----------------------------------------------------------------------------
-- 【步骤 2】service_task 增加 1 个索引
--
--  4) idx_st_type_flag (service_type, del_flag)
--     用途：LEFT JOIN service_task t ON t.service_type IN (6,14) AND t.del_flag='0'
--     效果：service_task 只有 153 行，收益有限（153 行 → 46 行），
--           但能让该表也走索引、避免作为 BNL 外表全扫，属于顺带优化。
-- ----------------------------------------------------------------------------
ALTER TABLE service_task
    ADD INDEX idx_st_type_flag (service_type, del_flag),
    ALGORITHM = INPLACE,
    LOCK = NONE;


-- ----------------------------------------------------------------------------
-- 【步骤 3】更新统计信息（加完索引后执行一次，让优化器选对索引）
-- ----------------------------------------------------------------------------
ANALYZE TABLE service_subtask_detail;
ANALYZE TABLE service_task;


-- ----------------------------------------------------------------------------
-- 【验证】执行下面两条 EXPLAIN，关键看：
--   * 不再出现 type=ALL 全表扫描（除派生表外）
--   * d 行出现 type=ref / key=idx_ssd_taskid_flag_dim_ct
--   * service_task 行 key=idx_st_type_flag
-- ----------------------------------------------------------------------------
-- EXPLAIN
-- SELECT p.period, dim.dimension, IFNULL(ROUND(SUM(d.score), 2), 0) AS totalScore
-- FROM (SELECT DISTINCT DATE_FORMAT(d.create_time, '%Y-%m') AS period
--       FROM service_subtask_detail d
--       WHERE d.del_flag = '0' AND d.create_time >= '2025-01-01' AND d.create_time < '2026-12-31') p
-- CROSS JOIN (SELECT DISTINCT dimension FROM service_subtask_detail
--             WHERE del_flag = '0' AND dimension IS NOT NULL) dim
-- LEFT JOIN service_task t ON t.service_type IN (6, 14) AND t.del_flag = '0'
-- LEFT JOIN service_subtask_detail d
--        ON d.taskid = t.taskid AND d.del_flag = '0' AND d.dimension = dim.dimension
--       AND DATE_FORMAT(d.create_time, '%Y-%m') = p.period
--       AND d.create_time >= '2025-01-01' AND d.create_time < '2026-12-31'
-- GROUP BY p.period, dim.dimension ORDER BY p.period, dim.dimension;


-- ----------------------------------------------------------------------------
-- 【回滚】如需撤销，执行以下语句
-- ----------------------------------------------------------------------------
-- ALTER TABLE service_subtask_detail DROP INDEX idx_ssd_taskid_flag_dim_ct;
-- ALTER TABLE service_subtask_detail DROP INDEX idx_ssd_ct_flag;
-- ALTER TABLE service_subtask_detail DROP INDEX idx_ssd_flag_dim;
-- ALTER TABLE service_task DROP INDEX idx_st_type_flag;


-- ============================================================================
-- 【重要】代码侧必须同步修正 del_flag 的写法，否则索引只能用上一半
--
-- ServiceSubtaskMapper.xml 中写的是：  d.del_flag = 0
-- 但 del_flag 的列类型是 char(10)（取值 '0' / '1'）。
-- MySQL 在「字符串列 = 数字常量」时会按 DOUBLE 隐式转换比较，
-- 导致该条件无法作为索引的等值条件使用（只能过滤，不能定位）。
-- 上面 3 个索引里 del_flag 都是等值列，改成下面这样才算真正生效：
--
--   d.del_flag  = 0   →   d.del_flag  = '0'
--   del_flag    = 0   →   del_flag    = '0'
--   t.del_flag  = 0   →   t.del_flag  = '0'
--
-- 适用范围（同一文件内同样问题的查询，建议一起改）：
--   * sltdMydTotalByDimension   (3050 行附近)
--   * sltdMydTotalByYQ          (3097 行附近)
--   * mydTrend                  (3030 行附近)
-- 语义影响：'0' 与 0 的匹配结果在本库中完全一致（del_flag 实测只有 '0'、'1'
-- 两种取值，无 NULL、无空串），改成字符串比较不会改变业务结果。
--
-- 另：本接口两条 SQL 的 LEFT JOIN service_task t 后面没有跟任何关联条件
-- （ON 里只有 t 自己的过滤条件），等价于先对 service_task 做一次过滤再交叉
-- 连接，本身写法不规范；本次只加索引、不动 SQL，避免影响返回结构。
-- ============================================================================