-- ============================================================================
|
-- /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 键一致性结论
|
-- ============================================================================
|
