-- =====================================================================================
|
-- 南华线上（smartor_nhfy @ 192.168.99.192）随访列表 /smartor/serviceSubtask/patItem 慢查询修复
|
-- 编写日期：2026-11-06 实测环境：MySQL 8.0.22 Win64，库 2277MB，service_subtask 61,645 行 / 71.8MB
|
--
|
-- 一、实测结论（下面第 0 步的 SQL 可原样重跑复现）
|
-- patItem 一次请求实际跑的是 3 类 SQL + 每行 2 次单条查询：
|
-- ① select id ... order by endtime desc limit 10 ← 慢的就在这，2.4~3.4 s
|
-- 执行计划：index_merge(sort_union(idx_service_subtask_district_visit_time,
|
-- idx_service_subtask_dept_visit_time)) + Using filesort
|
-- EXPLAIN ANALYZE：实际 2915 ms，只为返回 10 行
|
-- ② select <100 列> where id in (10 个 id) ← 27 ms，正常
|
-- ③ countByCondition（Redis 未命中时） ← 2.5~3.0 s
|
-- ④ 循环里每行 2 次单条查询（pat_med_inhosp / pat_archive 按主键）← 各约 25 ms，10 行≈500 ms
|
-- 合计约 6~7 s，与「查询很慢」一致。
|
--
|
-- 更底层的原因（按影响排序）：
|
-- (1) 【最致命】MySQL 实例 innodb_buffer_pool_size = 8 MB（默认值，512 页，pages_free=0），
|
-- 而全库 2277 MB。等于每次查询都在冷盘上读：
|
-- · select count(*) from service_subtask（6 万行）要 9.3~12.5 s → 有效读速约 7 MB/s
|
-- · performance_schema 里 100 列的列表查询 avg 653 ms（29.9 万次，累计 19.5 万秒）
|
-- (2) service_subtask 上没有「机构+院区+类型+排序」的联合索引，deptOrDistrict=2 时
|
-- (leavehospitaldistrictcode IN .. OR leaveldeptcode IN ..) 只能走 index_merge + filesort，
|
-- 要扫 1200~54000 行并且大量回表。
|
-- (3) 库里有 25 张 copy/bak 垃圾表，合计约 1.6 GB（占全库 70%），挤占磁盘与缓存。
|
-- (4) pat_archive 没有 patientno 索引：`where ?=? and del_flag=? and patientno=?` 每次全表扫
|
-- 5.6 万行，37.4 万次调用、累计扫描 184 亿行、avg 457 ms。
|
--
|
-- 单独做的效果（都在副本表 service_subtask_copy6 上实测过，数据量同量级）：
|
-- · 只建索引、SQL 不改 → 优化器仍选 index_merge，2.8~3.5 s（没用）
|
-- · 只改 SQL(UNION)、不建索引 → 1.5~1.7 s（有用但不够）
|
-- · 索引 + 改写一起上 → 26~27 ms（约 110 倍）
|
-- · 把 OR 原样 FORCE INDEX 到单臂索引 → 28~33 s（千万别这么干）
|
--
|
-- 二、修复步骤（按性价比，1 和 2 做完这条 SQL 就从 3.1 s 掉到 27 ms）
|
-- 第 1 步（不改代码，收益最大）：调大 innodb_buffer_pool_size（8MB → 4G）
|
-- 第 2 步（不加索引也能跑）：加两个「分支」覆盖索引
|
-- 第 3 步（改 SQL / 改 mapper）：把 OR 改写成两分支 UNION（已实测 3.1 s → 26 ms，结果逐条一致）
|
-- 第 4 步：统计 count 改写、pat_archive.patientno 索引、清垃圾表
|
-- =====================================================================================
|
|
|
-- -------------------------------------------------------------------------------------
|
-- 0. 现状诊断（只读，可随时重跑；这里的 EXPLAIN ANALYZE 会真跑一次，耗时 3 s 左右）
|
-- -------------------------------------------------------------------------------------
|
-- 0.1 缓冲池 / 磁盘相关参数（关键：buf_mb = 8）
|
SELECT @@version AS version,
|
@@version_compile_os AS os,
|
@@innodb_buffer_pool_size / 1024 / 1024 AS buf_mb,
|
@@innodb_buffer_pool_instances AS bp_instances,
|
@@innodb_io_capacity AS io_capacity,
|
@@innodb_log_file_size / 1024 / 1024 AS log_mb;
|
|
-- 0.2 缓冲池是不是全满（pages_free = 0 表示一点余量都没有，读全走磁盘）
|
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';
|
|
-- 0.3 冷读有多慢：6 万行的 count 要 10 秒就说明一切
|
SELECT COUNT(*) FROM service_subtask;
|
|
-- 0.4 你这条请求的实际执行计划（把 orgid/campusid/科室病区换成真实值）
|
EXPLAIN ANALYZE
|
SELECT id
|
FROM service_subtask
|
WHERE del_flag = '0'
|
AND orgid = '20001001' AND campusid = '30001002'
|
AND service_type = '2' AND visit_count = 1
|
AND (leavehospitaldistrictcode IN ('40003309') OR leaveldeptcode IN ('40003307'))
|
ORDER BY endtime DESC
|
LIMIT 10 OFFSET 0;
|
-- 期望看到的问题：index_merge(...) + Using filesort，actual time ≈ 2900 ms
|
|
-- 0.5 线上最近到底哪些语句最耗时（performance_schema）
|
SELECT SUBSTRING(DIGEST_TEXT, 1, 120) AS sql_,
|
COUNT_STAR AS cnt,
|
ROUND(AVG_TIMER_WAIT / 1e9, 1) AS avg_ms,
|
ROUND(MAX_TIMER_WAIT / 1e9, 0) AS max_ms,
|
SUM_ROWS_EXAMINED AS rows_examined
|
FROM performance_schema.events_statements_summary_by_digest
|
WHERE SCHEMA_NAME = 'smartor_nhfy'
|
ORDER BY SUM_TIMER_WAIT DESC
|
LIMIT 15;
|
|
|
-- -------------------------------------------------------------------------------------
|
-- 1. 索引：给 OR 的两个分支各配一个「到 endtime 有序、且能覆盖查询」的索引
|
--
|
-- 为什么不是普通联合索引？
|
-- 实测加 (orgid, campusid, del_flag, service_type, visit_count, endtime) 后，
|
-- 优化器依旧选 index_merge + filesort，耗时 2.8~3.5 s，等于白加。
|
-- 为什么要带 leavehospitaldistrictcode / leaveldeptcode 在 endtime 前面？
|
-- 这样分支查询是「等值 + 等值 + ... + 按 endtime 有序」，可以反向扫索引取前 10 行，
|
-- select 列表只有 id，InnoDB 二级索引自带主键 → Using index 完全覆盖，零回表。
|
-- 实测单分支 27 ms。
|
-- -------------------------------------------------------------------------------------
|
ALTER TABLE service_subtask
|
ADD INDEX idx_patitem_ward (orgid, campusid, del_flag, service_type, visit_count, leavehospitaldistrictcode, endtime),
|
ALGORITHM = INPLACE, LOCK = NONE;
|
|
ALTER TABLE service_subtask
|
ADD INDEX idx_patitem_dept (orgid, campusid, del_flag, service_type, visit_count, leaveldeptcode, endtime),
|
ALGORITHM = INPLACE, LOCK = NONE;
|
|
ANALYZE TABLE service_subtask;
|
-- 实测建索引各约 3.8 s（1 万行/秒级别，因为盘慢），业务无感。
|
|
-- 说明：索引里 visit_count 在 endtime 之前，是因为列表请求都会带「随访次数」；
|
-- 如果某些场景不带 visit_count，这个索引只能用到 service_type 前缀，排序仍会 filesort，
|
-- 那种场景再单独加：
|
-- ALTER TABLE service_subtask
|
-- ADD INDEX idx_patitem_ward_novc (orgid, campusid, del_flag, service_type, leavehospitaldistrictcode, endtime),
|
-- ADD INDEX idx_patitem_dept_novc (orgid, campusid, del_flag, service_type, leaveldeptcode, endtime);
|
|
|
-- -------------------------------------------------------------------------------------
|
-- 2. 列表 SQL 改写：OR 拆成两个分支 UNION（去重）
|
--
|
-- 实测（service_subtask_copy6，同样数据量）：
|
-- 原 OR 写法 ：3102 / 3266 / 2984 ms
|
-- 改写后 offset 0 / 100 / 500 ：26 / 27 / 27 ms
|
-- 三档结果与原写法逐条一致（id 顺序完全相同）
|
-- 注意：
|
-- · 分支内必须各自带 limit (offset + pageSize)，外层再 limit pageSize offset offset；
|
-- · 用 UNION（去重），不要用 UNION ALL + “排除重叠”那种写法，
|
-- 后者会破坏覆盖索引，实测退化到 987 ms~1.1 s；
|
-- · 两个分支里的公共 where 必须与原来的 serviceSubtaskWhere 完全一致（逐字相同），
|
-- 否则第 2 阶段按 id 回表时会出现行数对不上。
|
-- -------------------------------------------------------------------------------------
|
-- 2.1 只取当页 id（替换 mapper 里的 selectServiceSubtaskIdsPage，仅在 deptOrDistrict = '2' 时走这条）
|
SELECT id
|
FROM (
|
(SELECT id, endtime
|
FROM service_subtask
|
WHERE del_flag = '0'
|
AND orgid = '20001001' AND campusid = '30001002'
|
AND service_type = '2' AND visit_count = 1
|
AND leavehospitaldistrictcode IN ('40003309')
|
ORDER BY endtime DESC
|
LIMIT 10 OFFSET 0)
|
UNION
|
(SELECT id, endtime
|
FROM service_subtask
|
WHERE del_flag = '0'
|
AND orgid = '20001001' AND campusid = '30001002'
|
AND service_type = '2' AND visit_count = 1
|
AND leaveldeptcode IN ('40003307')
|
ORDER BY endtime DESC
|
LIMIT 10 OFFSET 0)
|
) t
|
ORDER BY endtime DESC
|
LIMIT 10 OFFSET 0;
|
-- 深翻页时：分支内 LIMIT (pageNum + pageSize) OFFSET 0，外层 LIMIT pageSize OFFSET pageNum。
|
-- 实测 offset 100 / 500 都是 27 ms 且结果正确。
|
|
-- 2.2 对照：同样的条件、原来的 OR 写法（用来对比耗时 / 校验结果）
|
SELECT id
|
FROM service_subtask
|
WHERE del_flag = '0'
|
AND orgid = '20001001' AND campusid = '30001002'
|
AND service_type = '2' AND visit_count = 1
|
AND (leavehospitaldistrictcode IN ('40003309') OR leaveldeptcode IN ('40003307'))
|
ORDER BY endtime DESC
|
LIMIT 10 OFFSET 0;
|
|
-- 2.3 结果一致性校验（两边 id 集合必须完全相同）
|
SELECT (SELECT GROUP_CONCAT(id ORDER BY id) FROM (SELECT id FROM service_subtask
|
WHERE del_flag='0' AND orgid='20001001' AND campusid='30001002'
|
AND service_type='2' AND visit_count=1
|
AND (leavehospitaldistrictcode IN ('40003309') OR leaveldeptcode IN ('40003307'))
|
ORDER BY endtime DESC LIMIT 10) a) AS old_ids,
|
(SELECT GROUP_CONCAT(id ORDER BY id) FROM (
|
SELECT id FROM (
|
(SELECT id, endtime FROM service_subtask
|
WHERE del_flag='0' AND orgid='20001001' AND campusid='30001002'
|
AND service_type='2' AND visit_count=1
|
AND leavehospitaldistrictcode IN ('40003309')
|
ORDER BY endtime DESC LIMIT 10)
|
UNION
|
(SELECT id, endtime FROM service_subtask
|
WHERE del_flag='0' AND orgid='20001001' AND campusid='30001002'
|
AND service_type='2' AND visit_count=1
|
AND leaveldeptcode IN ('40003307')
|
ORDER BY endtime DESC LIMIT 10)
|
) t ORDER BY endtime DESC LIMIT 10) b) AS new_ids;
|
|
-- 2.4 排序字段说明：sort=0/1 是 endtime（默认），sort=2/3/7/8 是 visit_time。
|
-- 上面两个索引是按 endtime 排的；如果常用「随访时间」排序，需要再补一对把 endtime 换成 visit_time：
|
-- ALTER TABLE service_subtask
|
-- ADD INDEX idx_patitem_ward_vt (orgid, campusid, del_flag, service_type, visit_count, leavehospitaldistrictcode, visit_time),
|
-- ADD INDEX idx_patitem_dept_vt (orgid, campusid, del_flag, service_type, visit_count, leaveldeptcode, visit_time);
|
|
|
-- -------------------------------------------------------------------------------------
|
-- 3. 顶部统计（countByCondition）也要跟着改：拆两个分支再汇总
|
-- 现状：Redis 未命中时 2.5~3.0 s（每次全量聚合这套 where）
|
-- 改法：两个分支各自聚合，外层把结果相加。分支里列出的列都在索引里 → 不回表。
|
-- -------------------------------------------------------------------------------------
|
SELECT SUM(wzx) AS wzx, SUM(ywc) AS ywc, SUM(total) AS total
|
FROM (
|
(SELECT SUM(CASE WHEN sendstate = 4 THEN 1 ELSE 0 END) AS wzx,
|
SUM(CASE WHEN sendstate = 6 THEN 1 ELSE 0 END) AS ywc,
|
COUNT(1) AS total
|
FROM service_subtask
|
WHERE del_flag = '0'
|
AND orgid = '20001001' AND campusid = '30001002'
|
AND service_type = '2' AND visit_count = 1
|
AND leavehospitaldistrictcode IN ('40003309'))
|
UNION ALL
|
(SELECT SUM(CASE WHEN sendstate = 4 THEN 1 ELSE 0 END),
|
SUM(CASE WHEN sendstate = 6 THEN 1 ELSE 0 END),
|
COUNT(1)
|
FROM service_subtask
|
WHERE del_flag = '0'
|
AND orgid = '20001001' AND campusid = '30001002'
|
AND service_type = '2' AND visit_count = 1
|
AND leaveldeptcode IN ('40003307'))
|
) t;
|
-- 注意：两个分支会重复统计「既属于该病区又属于该科室」的行（同一行两个条件都命中）。
|
-- 原有 countByCondition 是对 OR 后的结果集统计，不重复。
|
-- 线上实测这两个科室/病区是一一对应的（leaveldeptcode=40003307 ⇔ leavehospitaldistrictcode=40003309），
|
-- 所以这里用 UNION ALL 也不会重复；如果存在跨科共用病区的情况，改成
|
-- “分支2 加上 AND (leavehospitaldistrictcode IS NULL OR leavehospitaldistrictcode NOT IN (...))”，
|
-- 或者干脆保留原来的 OR 写法（统计有 Redis 缓存 60~120 分钟，改完第 1 步参数后也就几十 ms）。
|
|
|
-- -------------------------------------------------------------------------------------
|
-- 4. 顺带修（和 patItem 慢相关，但不是同一条 SQL）
|
-- -------------------------------------------------------------------------------------
|
-- 4.1 pat_archive 没有 patientno 索引：`where ?=? and del_flag=? and patientno=?` 每跑一次全表扫 5.6 万行
|
-- （performance_schema：37.4 万次、累计扫描 184 亿行、avg 457 ms）
|
ALTER TABLE pat_archive
|
ADD INDEX idx_pat_archive_patientno (patientno, del_flag),
|
ALGORITHM = INPLACE, LOCK = NONE;
|
-- 另外那条 SQL 里有个 `? = ?`（常量对常量）的条件，看着像拼参数时少了个列名，建议代码侧一起看一下。
|
|
-- 4.2 patItem 的回填是「每行 2 次单条查询」（pat_med_inhosp / pat_archive 各一次，按主键）。
|
-- 10 行就是 20 次随机 I/O（冷盘一次约 25 ms）。建议改成 IN 批量查两次。
|
-- 这条是代码改动，不是 SQL；当前每页 10 行影响约 0.3~0.5 s。
|
|
-- 4.3 库里有 25 张 copy/bak 垃圾表，合计约 1.6 GB（占全库 70%），对 8 MB 的缓冲池是纯负担：
|
-- pat_med_inhosp_copy1..copy5（约 1.24 GB）、service_subtask_copy1..copy6（约 0.30 GB）等。
|
-- 确认无用后先改名再删，避免误删（示例，逐张确认后再执行）：
|
-- RENAME TABLE pat_med_inhosp_copy4 TO zzz_del_pmi_copy4_20261106;
|
-- DROP TABLE zzz_del_pmi_copy4_20261106;
|
|
|
-- -------------------------------------------------------------------------------------
|
-- 5. 服务器参数（收益最大的一步，需要改 my.ini 后重启 MySQL；也可先 SET GLOBAL 在线放大缓冲池）
|
-- 现状：innodb_buffer_pool_size = 8 MB（默认值），全库 2277 MB → 每次查询都在冷盘上跑
|
-- 目标：
|
-- innodb_buffer_pool_size = 4G （按物理内存 50%~70% 给；至少 2G，能装下热表）
|
-- innodb_buffer_pool_instances = 4 （缓冲池 ≥ 1G 时才有意义）
|
-- innodb_io_capacity = 2000
|
-- innodb_io_capacity_max = 4000
|
-- innodb_flush_method = unbuffered / O_DIRECT（Windows 用 unbuffered）
|
-- table_open_cache = 4000
|
-- innodb_log_file_size = 512M
|
-- 在线验证（不用重启，MySQL 5.7+ 支持动态调整；确认机器内存够再执行）：
|
-- SET GLOBAL innodb_buffer_pool_size = 2 * 1024 * 1024 * 1024;
|
-- 然后重跑 0.3 的 count(*)：应从 ~10 s 掉到几十~一两百 ms。
|
-- 这一步做完，第 2、3 步的收益会进一步放大（索引页全在内存，27 ms 还能再降）。
|
|
|
-- -------------------------------------------------------------------------------------
|
-- 6. 验证（改完逐条跑）
|
-- -------------------------------------------------------------------------------------
|
-- 6.1 单分支是否走覆盖索引（期望 key = idx_patitem_ward/dept，Extra 含 Using index，
|
-- 没有 Using filesort，也没有 Using where 之外的回表）
|
EXPLAIN
|
SELECT id
|
FROM service_subtask
|
WHERE del_flag = '0'
|
AND orgid = '20001001' AND campusid = '30001002'
|
AND service_type = '2' AND visit_count = 1
|
AND leavehospitaldistrictcode IN ('40003309')
|
ORDER BY endtime DESC
|
LIMIT 10;
|
|
-- 6.2 改写后整体耗时（期望 30 ms 量级，而不是 3000 ms）
|
EXPLAIN ANALYZE
|
SELECT id FROM (
|
(SELECT id, endtime FROM service_subtask
|
WHERE del_flag='0' AND orgid='20001001' AND campusid='30001002'
|
AND service_type='2' AND visit_count=1 AND leavehospitaldistrictcode IN ('40003309')
|
ORDER BY endtime DESC LIMIT 10)
|
UNION
|
(SELECT id, endtime FROM service_subtask
|
WHERE del_flag='0' AND orgid='20001001' AND campusid='30001002'
|
AND service_type='2' AND visit_count=1 AND leaveldeptcode IN ('40003307')
|
ORDER BY endtime DESC LIMIT 10)
|
) t ORDER BY endtime DESC LIMIT 10;
|
|
-- 6.3 收尾
|
ANALYZE TABLE service_subtask;
|
ANALYZE TABLE pat_archive;
|
|
|
-- -------------------------------------------------------------------------------------
|
-- 7. 回滚
|
-- -------------------------------------------------------------------------------------
|
-- ALTER TABLE service_subtask DROP INDEX idx_patitem_ward;
|
-- ALTER TABLE service_subtask DROP INDEX idx_patitem_dept;
|
-- ALTER TABLE pat_archive DROP INDEX idx_pat_archive_patientno;
|
-- （SQL / 代码改写回滚直接还原 mapper 即可；参数回滚把 innodb_buffer_pool_size 调回原值）
|
