-- ===================================================================================== -- 南华线上(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 调回原值)