-- ============================================================================
-- /smartor/patinhosp/selectPatMedInhospList 慢 —— 一页诊断（把输出贴回来即可定位）
--
-- 现场入参：{"bedSort":1,"pageNum":1,"pageSize":20,
--           "deptcodeList":["40003378"],"hospitaldistrictcodeList":["40003380"],"cry":0}
--
-- 代码侧已经做完的三件事（不用再查）：
--   ① del_flag / inhospstate 已改成字符串常量 → 索引首列能用上了
--   ② 分页 count 已改成手写 _COUNT（pagehelper 实测解析到 5.3.2，该机制生效），
--      不再把 60 列查询整个包一层重跑
--   ③ 【在院口径】cry=0 现在同时带 endtime is null 和 inhospstate = '0'
--      —— 这是迁出前 PatMedInhospServiceImpl 的行为，也是让索引第三列
--      (del_flag, orgid, inhospstate) 真正等值命中的前提
-- 如果【还是慢】，剩下的时间必然在这两处之一：
--   (甲) 在院口径选错了列 —— endtime IS NULL 命中的行数远大于真实在院人数
--        （HIS 导入的历史数据 endtime 常年为空，这一条最容易被忽略）
--   (乙) 索引没建 / 建了但用不上（类型或字符集不一致，join 键被隐式转换）
--
-- 跑法：整段贴进 mysql 客户端。每个 SELECT 的结果里客户端会打印
--       "1 row in set (0.0xx sec)"，那个秒数就是我要的。
-- 需要替换的占位符：<ORGID>（= 登录用户的 orgid）、<DEPT>、<DISTRICT>。
-- ============================================================================


-- ----------------------------------------------------------------------------
-- 0) 版本（EXPLAIN ANALYZE 需要 8.0.17+；5.7 就走第 4 节的普通 EXPLAIN）
-- ----------------------------------------------------------------------------
SELECT VERSION() AS mysql_version, @@sql_mode AS sql_mode;


-- ----------------------------------------------------------------------------
-- 1) 索引现状 + 关键列类型/字符集（决定"索引能不能被用上"）
-- ----------------------------------------------------------------------------
SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('pat_med_inhosp', 'pat_archive')
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- ⚠️ 重点看两行：
--    b.patid  与 a.id  的 COLUMN_TYPE / COLLATION_NAME 是否【完全一致】
--    不一致 = join 键被隐式转换 = pat_archive 的主键索引用不上（见第 5 节）
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND ( (TABLE_NAME = 'pat_med_inhosp'
         AND COLUMN_NAME IN ('inhospid','patid','del_flag','orgid','campusid',
                             'inhospstate','endtime','starttime','bed_no',
                             'deptcode','hospitaldistrictcode'))
     OR (TABLE_NAME = 'pat_archive'
         AND COLUMN_NAME IN ('id','del_flag','idcardno','age','age_unit','telcode','sex')) )
ORDER BY TABLE_NAME, COLUMN_NAME;

SELECT TABLE_NAME, TABLE_ROWS, ROUND(DATA_LENGTH/1024/1024) AS data_mb,
       ROUND(INDEX_LENGTH/1024/1024) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('pat_med_inhosp', 'pat_archive');


-- ----------------------------------------------------------------------------
-- 2) ★最关键：在院到底有多少行（两种口径对比）
--    如果 in_by_endtime 远大于 in_by_state，说明 endtime IS NULL 不是"在院"，
--    那再怎么加索引也没救 —— 必须换成 inhospstate 口径（代码一行）。
-- ----------------------------------------------------------------------------
SELECT
    COUNT(*)                                                              AS total_rows,
    SUM(del_flag = '0')                                                   AS alive_rows,
    SUM(del_flag = '0' AND endtime IS NULL)                               AS in_by_endtime,
    SUM(del_flag = '0' AND inhospstate = '0')                             AS in_by_state,
    SUM(del_flag = '0' AND endtime IS NULL AND IFNULL(inhospstate,'') <> '0') AS conflict_endtime_null_but_not_state0,
    SUM(del_flag = '0' AND inhospstate = '0' AND endtime IS NOT NULL)     AS conflict_state0_but_has_endtime
FROM pat_med_inhosp;

-- 换成"该机构"再看一遍（口径分布可能因机构而异）
SELECT
    COUNT(*)                                                    AS org_alive_rows,
    SUM(endtime IS NULL)                                        AS org_in_by_endtime,
    SUM(inhospstate = '0')                                      AS org_in_by_state,
    SUM(endtime IS NULL AND IFNULL(inhospstate,'') <> '0')      AS org_conflict
FROM pat_med_inhosp
WHERE del_flag = '0' AND orgid = '<ORGID>';

-- 再看"该机构 + 该院区 + 该科室"（就是这次入参的过滤组合）
SELECT
    COUNT(*)                                                    AS scope_alive_rows,
    SUM(endtime IS NULL)                                        AS scope_in_by_endtime,
    SUM(inhospstate = '0')                                      AS scope_in_by_state
FROM pat_med_inhosp
WHERE del_flag = '0' AND orgid = '<ORGID>'
  AND hospitaldistrictcode = '<DISTRICT>' AND deptcode = '<DEPT>';


-- ----------------------------------------------------------------------------
-- 3) 分步计时（一条一条跑，看秒数落在哪一步）
-- ----------------------------------------------------------------------------

-- 3a 只扫 pat_med_inhosp，不 join、不 group by：这是"取数"的下限【新口径】
SELECT COUNT(*) AS c
FROM pat_med_inhosp b
WHERE b.del_flag = '0' AND b.orgid = '<ORGID>'
  AND b.hospitaldistrictcode = '<DISTRICT>' AND b.deptcode = '<DEPT>'
  AND b.endtime IS NULL AND b.inhospstate = '0';

-- 3g ★对照：同一条，但去掉 inhospstate（= 改造前的口径）
--    3a 与 3g 的秒数差 = "补上 inhospstate 到底救回来多少"。
--    如果 3g 还是几秒而 3a 是毫秒级，说明病因就是它，到此为止；
SELECT COUNT(*) AS c
FROM pat_med_inhosp b
WHERE b.del_flag = '0' AND b.orgid = '<ORGID>'
  AND b.hospitaldistrictcode = '<DISTRICT>' AND b.deptcode = '<DEPT>'
  AND b.endtime IS NULL;

-- 3b 加上 join pat_archive（判 a.del_flag / a.idcardno）：join 贵不贵
SELECT COUNT(*) AS c
FROM pat_med_inhosp b
JOIN pat_archive a ON a.id = b.patid
WHERE b.del_flag = '0' AND b.orgid = '<ORGID>'
  AND b.hospitaldistrictcode = '<DISTRICT>' AND b.deptcode = '<DEPT>'
  AND b.endtime IS NULL AND b.inhospstate = '0' AND a.del_flag = '0' AND a.idcardno IS NOT NULL;

-- 3c 改造【前】的 count（60 列 + GROUP BY 整个包一层）—— 这就是被优化掉的那条
SELECT COUNT(0) AS c FROM (
  SELECT a.age, a.telcode, a.sex, b.inhospid, b.patid, b.deptcode, b.bed_no, b.starttime
  FROM pat_med_inhosp b
  JOIN pat_archive a ON a.id = b.patid
  WHERE b.del_flag = '0' AND b.orgid = '<ORGID>'
    AND b.hospitaldistrictcode = '<DISTRICT>' AND b.deptcode = '<DEPT>'
    AND b.endtime IS NULL AND b.inhospstate = '0' AND a.del_flag = '0' AND a.idcardno IS NOT NULL
  GROUP BY b.inhospid, a.age, a.age_unit, a.telcode, a.id, a.sex
) tmp_count;

-- 3d 数据查询【去掉 GROUP BY 和 ORDER BY】：纯取 20 行要多久
SELECT a.age, a.telcode, a.sex, b.inhospid, b.patid, b.deptcode, b.bed_no, b.starttime
FROM pat_med_inhosp b
JOIN pat_archive a ON a.id = b.patid
WHERE b.del_flag = '0' AND b.orgid = '<ORGID>'
  AND b.hospitaldistrictcode = '<DISTRICT>' AND b.deptcode = '<DEPT>'
  AND b.endtime IS NULL AND b.inhospstate = '0' AND a.del_flag = '0' AND a.idcardno IS NOT NULL
LIMIT 20;

-- 3e 数据查询【加 GROUP BY，不加 ORDER BY】：group by 的代价
SELECT a.age, a.telcode, a.sex, b.inhospid, b.patid, b.deptcode, b.bed_no, b.starttime
FROM pat_med_inhosp b
JOIN pat_archive a ON a.id = b.patid
WHERE b.del_flag = '0' AND b.orgid = '<ORGID>'
  AND b.hospitaldistrictcode = '<DISTRICT>' AND b.deptcode = '<DEPT>'
  AND b.endtime IS NULL AND b.inhospstate = '0' AND a.del_flag = '0' AND a.idcardno IS NOT NULL
GROUP BY b.inhospid, a.age, a.age_unit, a.telcode, a.id, a.sex
LIMIT 20;

-- 3f 现状全貌（= 接口真正执行的那条）：再加 ORDER BY
SELECT a.age, a.telcode, a.sex, b.inhospid, b.patid, b.deptcode, b.bed_no, b.starttime
FROM pat_med_inhosp b
JOIN pat_archive a ON a.id = b.patid
WHERE b.del_flag = '0' AND b.orgid = '<ORGID>'
  AND b.hospitaldistrictcode = '<DISTRICT>' AND b.deptcode = '<DEPT>'
  AND b.endtime IS NULL AND b.inhospstate = '0' AND a.del_flag = '0' AND a.idcardno IS NOT NULL
GROUP BY b.inhospid, a.age, a.age_unit, a.telcode, a.id, a.sex
ORDER BY b.starttime DESC, b.bed_no ASC
LIMIT 20;

-- 读法：
--   3g 慢、3a 快      -> 病因就是"在院口径"，本次补 inhospstate='0' 已解决，收工
--   3a 慢             -> 取数就走错路了（索引缺失 / 类型字符集不一致），看第 1、2、5 节
--   3a 快、3b 慢      -> join 有问题（patid/id 类型或字符集不一致，看第 5 节）
--   3b 快、3e 慢      -> GROUP BY 的代价；可以确认"GROUP BY 是空操作"后删掉
--   3e 快、3f 慢      -> ORDER BY 的 filesort 代价；可考虑让索引顺序覆盖排序
--   3c 明显慢于 3b    -> 说明这次的手写 count 改造确实省掉了大头


-- ----------------------------------------------------------------------------
-- 4) 执行计划（MySQL 5.7 用普通 EXPLAIN；8.0.17+ 直接用 EXPLAIN ANALYZE 看真实耗时）
-- ----------------------------------------------------------------------------
EXPLAIN
SELECT a.age, a.telcode, a.sex, b.inhospid, b.patid, b.deptcode, b.bed_no, b.starttime
FROM pat_med_inhosp b
JOIN pat_archive a ON a.id = b.patid
WHERE b.del_flag = '0' AND b.orgid = '<ORGID>'
  AND b.hospitaldistrictcode = '<DISTRICT>' AND b.deptcode = '<DEPT>'
  AND b.endtime IS NULL AND b.inhospstate = '0' AND a.del_flag = '0' AND a.idcardno IS NOT NULL
GROUP BY b.inhospid, a.age, a.age_unit, a.telcode, a.id, a.sex
ORDER BY b.starttime DESC, b.bed_no ASC
LIMIT 20;

-- 8.0.17+ 才有：直接给出每个算子的实际行数/耗时，比 EXPLAIN 有用得多
-- EXPLAIN ANALYZE
-- SELECT ... (同上面这条)

-- 看 EXPLAIN 时只盯 4 列：
--   table  : 第一行是不是 b（如果第一行是 a，说明优化器反着驱动，最坏情况）
--   type   : ALL / index = 全扫（问题）；ref / range = 走索引（正常）
--   key    : 命中的索引名；NULL = 没用索引
--   rows   : 估算扫描行数（几万以上就是全机构历史，说明口径/索引仍不对）


-- ----------------------------------------------------------------------------
-- 5) join 键一致性（第 1 节看不明白就跑这条，直接给出"会不会隐式转换"的结论）
-- ----------------------------------------------------------------------------
SELECT
    b_col.COLUMN_TYPE   AS b_patid_type,
    b_col.COLLATION_NAME AS b_patid_collation,
    a_col.COLUMN_TYPE   AS a_id_type,
    a_col.COLLATION_NAME AS a_id_collation,
    CASE
        WHEN a_col.COLUMN_TYPE = b_col.COLUMN_TYPE
         AND IFNULL(a_col.COLLATION_NAME,'') = IFNULL(b_col.COLLATION_NAME,'')
        THEN '✅ 一致，join 可以用 a.id 主键'
        ELSE '❌ 不一致 -> join 键被隐式转换，pat_archive 可能被反复全扫'
    END AS verdict
FROM (SELECT COLUMN_TYPE, COLLATION_NAME FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pat_med_inhosp' AND COLUMN_NAME = 'patid') b_col
JOIN (SELECT COLUMN_TYPE, COLLATION_NAME FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pat_archive' AND COLUMN_NAME = 'id') a_col;


-- ============================================================================
-- 要贴回来的东西（如果补了 inhospstate 之后还是慢，按这个顺序，最省事的三项就够）：
--   ① 第 3 节 3a 和 3g 的秒数（这两个一对比就知道病因是不是"在院口径"）
--   ② 第 2 节第一组的四个数字（total_rows / in_by_endtime / in_by_state / conflict...）
--   ③ 第 4 节 EXPLAIN 的 table / type / key / rows 四列
--   ④ 需要时再补第 1 节索引清单与第 5 节的 join 键一致性结论
-- ============================================================================