-- ============================================================================
|
-- 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，避免影响返回结构。
|
-- ============================================================================
|
