-- ============================================================================
-- /smartor/serviceSubtask/getSfStatisticsCountDetails 慢查询优化（南华附一 nhfy）
--                                                          2026-11-02
--
-- 【现象】满意度统计页点“查看详情”要等十几秒到二十几秒。
--   请求：{ "configKey":"joyCount", "leavehospitaldistrictcodes":["40003514"],
--           "serviceType":[6], "pageNum":1, "pageSize":20 }
--
-- 【这条接口实际发了 5 条 SQL】
--   ServiceSubtaskServiceImpl#getSfStatisticsScriptDetails 在 type 为空时会跑两趟：
--     type=1(语音)  → getSfStatisticsScriptDetails + getSfStatisticsScriptItemDetails
--     type=2(问卷)  → getSfStatisticsScriptDetails + getSfStatisticsScriptItemDetails
--   controller 上还有 PageUtils.startPageByPost(...)，PageHelper 会对第一条再补一次 COUNT。
--   每一条都在 service_subtask_detail（38 万行）上做「子任务 JOIN 题目明细 GROUP BY」。
--
-- 【根因：谓词把列包在函数里，索引全废】
--   为了「出院病区为空回退到在院病区」，条件写成了
--       AND IFNULL(ss.leavehospitaldistrictcode, ss.hospitaldistrictcode) IN ('40003514')
--   列被 IFNULL 包住 → 优化器用不了任何索引，只能 orgid='20001001' 的 27864 条子任务全扫，
--   每条再去 detail 表按 sub_id 捞 11 行（≈30 万行随机读）。
--   EXPLAIN 实测：ss 走 idx_service_subtask_orgid_visit_time，rows=27864。
--
-- 【改法 1：谓词展开成等价的 OR，让索引可用】
--       AND (ss.leavehospitaldistrictcode IN ('40003514')
--            OR (ss.leavehospitaldistrictcode IS NULL AND ss.hospitaldistrictcode IN ('40003514')))
--   语义与 IFNULL 完全等价（4 个病区逐一比对明细行数：8873/95/4307/8550 全部一致）。
--   只做这一步（不加索引）：5.1s → 1.9s，执行计划变成 ref_or_null，rows 27864 → 3644。
--
-- 【改法 2：补两个索引，把范围再收窄】
--   明细页固定按 (病区, service_type, type) 过滤，原来没有任何索引能覆盖这个组合
--   （service_subtask 上只有单列 index，或 visit_time 打头的复合索引）。
--
--   离院病区（含空值回退）→ idx_ss_district_pair
--   在院病区             → idx_ss_hosp_dist_svc_type
--
-- 【实测（192.168.99.192 / smartor_nhfy，同一条件下的单条 SQL）】
--   离院病区 40003514：5120ms → 1593ms(冷) / 728ms(热)
--   在院病区 40003514：4750ms → 1854ms
--   明细选项统计      ：6183ms → 629~1104ms
--   一次调用 5 条 SQL 合计：≈23s → ≈2s
-- ============================================================================


-- ----------------------------------------------------------------------------
-- 【一】DDL（每个环境都要单独执行；MySQL 8 支持 Online DDL，不会锁表）
-- ----------------------------------------------------------------------------
-- ALTER TABLE service_subtask
--     ADD INDEX idx_ss_district_pair
--         (leavehospitaldistrictcode, hospitaldistrictcode, service_type, type, del_flag),
--     ADD INDEX idx_ss_hosp_dist_svc_type
--         (hospitaldistrictcode, service_type, type, del_flag);

-- 单独执行写法（已在 192.168.99.192 执行过，两条各 5~8 秒）：
-- CREATE INDEX idx_ss_district_pair      ON service_subtask (leavehospitaldistrictcode, hospitaldistrictcode, service_type, type, del_flag);
-- CREATE INDEX idx_ss_hosp_dist_svc_type ON service_subtask (hospitaldistrictcode, service_type, type, del_flag);

-- 回滚：
-- DROP INDEX idx_ss_district_pair      ON service_subtask;
-- DROP INDEX idx_ss_hosp_dist_svc_type ON service_subtask;


-- ----------------------------------------------------------------------------
-- 【二】验证执行计划（执行 DDL 后应看到 key = idx_ss_district_pair / idx_ss_hosp_dist_svc_type）
-- ----------------------------------------------------------------------------
-- EXPLAIN
-- SELECT ssd.scriptid, COUNT(*) AS all_quantity
-- FROM service_subtask_detail ssd
-- INNER JOIN service_subtask ss ON ssd.sub_id = ss.id
-- WHERE ss.del_flag = 0 AND ssd.del_flag = 0
--   AND ss.orgid = '20001001' AND ss.campusid = '30001002'
--   AND (ss.leavehospitaldistrictcode IN ('40003514')
--        OR (ss.leavehospitaldistrictcode IS NULL AND ss.hospitaldistrictcode IN ('40003514')))
--   AND ss.service_type IN (6) AND ss.type = '2'
--   AND ssd.categoryid IN (62,1000000012,5,1000000015,465)
-- GROUP BY ssd.scriptid;


-- ----------------------------------------------------------------------------
-- 【三】等价性自检：换写法前后每个病区的明细行数必须一模一样
-- ----------------------------------------------------------------------------
-- SELECT w.code,
--        (SELECT COUNT(*) FROM service_subtask ss JOIN service_subtask_detail ssd ON ssd.sub_id = ss.id
--         WHERE ss.del_flag=0 AND ssd.del_flag=0
--           AND ss.orgid='20001001' AND ss.campusid='30001002'
--           AND ss.service_type IN (6) AND ss.type='2'
--           AND IFNULL(ss.leavehospitaldistrictcode, ss.hospitaldistrictcode) IN (w.code))     AS ifnull_rows,
--        (SELECT COUNT(*) FROM service_subtask ss JOIN service_subtask_detail ssd ON ssd.sub_id = ss.id
--         WHERE ss.del_flag=0 AND ssd.del_flag=0
--           AND ss.orgid='20001001' AND ss.campusid='30001002'
--           AND ss.service_type IN (6) AND ss.type='2'
--           AND (ss.leavehospitaldistrictcode IN (w.code)
--                OR (ss.leavehospitaldistrictcode IS NULL AND ss.hospitaldistrictcode IN (w.code)))) AS or_rows
-- FROM (SELECT '40003514' AS code UNION ALL SELECT '40003309'
--       UNION ALL SELECT '40011024' UNION ALL SELECT '40003317') w;
-- 实测：8873/8873、95/95、4307/4307、8550/8550 —— 完全一致。


-- ----------------------------------------------------------------------------
-- 【四】还没解决的两点（需要业务/前端配合，本次未动）
-- ----------------------------------------------------------------------------
-- 1) type 为空时固定跑两趟（语音 + 问卷）。南华满意度只可能是问卷(type=2)，
--    语音那一趟恒为 0 行。现在有索引后它只要 ~120ms，可以接受；
--    若要彻底省掉，让前端明确传 type，或后端按 serviceType 推断。
-- 2) PageHelper 的 COUNT 会对同一条重查询再跑一遍。这个结果集只有 19 行，
--    其实不需要分页。要省就得去掉 startPageByPost，接口返回结构会变，先不动。