你提供的 SQL 中，**最严重的性能瓶颈**正是这个子查询驱动的 `LEFT JOIN wycar_feedback d`： ```sql LEFT JOIN wycar_feedback d ON d.wycarriskwarnid = a.wycarriskwarnid AND d.id = ( SELECT MAX(s.id) FROM wycar_feedback s WHERE s.wycarriskwarnid = d.wycarriskwarnid AND s.validflag = '1' ) ``` ✅ **问题本质**： 这是一个 **“关联子查询 + 聚合 + 多条件过滤” 的嵌套执行模式**，Informix 对此类写法**无法有效下推谓词、无法利用索引优化连接顺序**，导致： - 对主表 `a` 每一行（尤其是分页跳过大量行后），都要执行一次 `SELECT MAX(id)` 子查询； - 若 `wycar_feedback` 表大（如百万级）、且 `wycarriskwarnid` 选择性差（一个 warnid 对应多条 feedback），则 `MAX(s.id)` 需全表扫描或索引回表； - `d.id = (subquery)` 是 **非SARGable 条件**，Informix 很难用索引高效定位 `d` 行。 --- ### ✅ 正确优化思路（核心原则） > **把“为每条 a 记录找最新 valid feedback” 从 “运行时逐行计算” → 改为 “预聚合 + 索引驱动连接”** --- ## ✅ 一、终极推荐方案：使用 `OUTER JOIN` + `ROW_NUMBER()` 窗口函数（Informix 12.10+） ✅ **前提**：你的 Informix 版本 ≥ **12.10**（支持 `ROW_NUMBER() OVER (...)`） ✅ **优势**：单次扫描 `wycar_feedback`，按 `wycarriskwarnid` 分组取最新 `id`，再与主表 `LEFT JOIN`，**性能提升 10x~100x** ### ✅ 优化后 SQL（推荐！） ```sql SELECT SKIP #{param.pageNo} FIRST #{param.pageSize} a.id, a.wycarriskwarnid, a.registno, a.policyno, a.subjectlicenseno, a.surveylicenseno, a.reportdate, a.damagedate, a.ckhrcode, a.dshrcode, a.hphrcode, a.hshrcode, a.ckname, a.dsname, a.hpname, a.hsname, a.claimstate, a.subjectrisklevel, a.subjectoperationscore, a.subjectoperationcity, a.surveyrisklevel, a.surveyoperationscore, a.surveyoperationcity, b.dicvalue AS subjectrisklevelname, c.dicvalue AS surveyrisklevelname, d.feedbackid FROM wycar_riskwarn a LEFT JOIN xzpt_dictionary b ON a.subjectrisklevel = b.diccode AND b.dictype = 'wycarRiskwarnRisklevel' AND b.flag = '0000000000' LEFT JOIN xzpt_dictionary c ON a.surveyrisklevel = c.diccode AND c.dictype = 'wycarRiskwarnRisklevel' AND c.flag = '0000000000' -- ✅ 关键优化：用预计算的最新 feedback 替代子查询 LEFT JOIN ( SELECT wycarriskwarnid, id AS feedbackid, ROW_NUMBER() OVER ( PARTITION BY wycarriskwarnid ORDER BY id DESC ) AS rn FROM wycar_feedback WHERE validflag = '1' ) d ON a.wycarriskwarnid = d.wycarriskwarnid AND d.rn = 1; ``` ### ✅ 为什么快？ | 步骤 | 说明 | |------|------| | **1. 子查询 `d` 单次执行** | 扫描 `wycar_feedback` 一次，按 `wycarriskwarnid` 分组，对每组按 `id DESC` 排序取 `rn=1`（即最大 `id`）； | | **2. 索引友好** | 只需 `wycar_feedback(wycarriskwarnid, id)` 复合索引即可极速完成（见下文建索引建议）； | | **3. 连接高效** | `d` 结果集极小（≈ 主表行数），`a.wycarriskwarnid = d.wycarriskwarnid` 是等值连接，可走哈希/嵌套循环； | | **4. 无重复计算** | 彻底避免了原写法中“对主表每行执行一次子查询”的灾难性开销。 | > 💡 `ROW_NUMBER() ... ORDER BY id DESC` 等价于 `MAX(id)`，但**可利用索引排序，无需聚合计算**，速度远超 `SELECT MAX()`。 --- ## ✅ 二、兼容老版本方案（Informix < 12.10，如 11.70） 若版本低于 12.10（不支持窗口函数），改用 **`JOIN` + `NOT EXISTS` 自连接排除法**（经典“Top 1 per group”技巧）： ### ✅ 兼容版 SQL（适用于所有 Informix 版本） ```sql SELECT SKIP #{param.pageNo} FIRST #{param.pageSize} a.id, a.wycarriskwarnid, a.registno, a.policyno, a.subjectlicenseno, a.surveylicenseno, a.reportdate, a.damagedate, a.ckhrcode, a.dshrcode, a.hphrcode, a.hshrcode, a.ckname, a.dsname, a.hpname, a.hsname, a.claimstate, a.subjectrisklevel, a.subjectoperationscore, a.subjectoperationcity, a.surveyrisklevel, a.surveyoperationscore, a.surveyoperationcity, b.dicvalue AS subjectrisklevelname, c.dicvalue AS surveyrisklevelname, d.feedbackid FROM wycar_riskwarn a LEFT JOIN xzpt_dictionary b ON a.subjectrisklevel = b.diccode AND b.dictype = 'wycarRiskwarnRisklevel' AND b.flag = '0000000000' LEFT JOIN xzpt_dictionary c ON a.surveyrisklevel = c.diccode AND c.dictype = 'wycarRiskwarnRisklevel' AND c.flag = '0000000000' -- ✅ 兼容版：找每个 wycarriskwarnid 下 id 最大的 feedback LEFT JOIN wycar_feedback d ON a.wycarriskwarnid = d.wycarriskwarnid AND d.validflag = '1' AND NOT EXISTS ( SELECT 1 FROM wycar_feedback d2 WHERE d2.wycarriskwarnid = d.wycarriskwarnid AND d2.validflag = '1' AND d2.id > d.id ); ``` ### ✅ 原理说明： - `d` 是候选反馈记录； - `NOT EXISTS (...)` 确保不存在比 `d.id` 更大的同 `wycarriskwarnid` 的 `validflag='1'` 记录 → 即 `d.id` 是该组最大值； - Informix 能对此类 `NOT EXISTS` 生成高效执行计划（尤其配合索引）。 > ⚠️ 注意：此写法在数据倾斜严重时（如某 `wycarriskwarnid` 有上万条 feedback）可能略慢于窗口函数版，但仍远优于原 `MAX()` 子查询。 --- ## ✅ 三、必须配套的索引（关键！否则优化无效） 无论用哪种 SQL，**必须为 `wycar_feedback` 创建以下复合索引**： ```sql -- ✅ 核心索引（必建！） CREATE INDEX idx_wycarfb_warnid_valid_id ON wycar_feedback (wycarriskwarnid, validflag, id DESC); ``` ### ✅ 索引设计理由： | 字段 | 作用 | |------|------| | `wycarriskwarnid` | 连接主表 `a` 的等值条件，放首位； | | `validflag` | 过滤条件 `validflag='1'`，放第二位，用于快速剪枝； | | `id DESC` | 支持 `ORDER BY id DESC`（窗口函数）或 `d2.id > d.id`（NOT EXISTS）的范围扫描，避免排序； | > 💡 Informix 中 `DESC` 在索引中是物理存储顺序，对 `MAX(id)` / `ORDER BY id DESC` 极其高效。 > ✅ 验证索引是否生效：用 `SET EXPLAIN ON` 查看执行计划，确认 `idx_wycarfb_warnid_valid_id` 被使用（`index key: 1,2,3`）。 --- ## ✅ 四、其他重要优化建议（锦上添花） ### 🔹 1. 为字典表 `xzpt_dictionary` 添加索引（防 join 慢） ```sql -- 加速 b/c 关联 CREATE INDEX idx_dict_type_code_flag ON xzpt_dictionary (dictype, diccode, flag); ``` ### 🔹 2. 主表 `wycar_riskwarn` 分区/聚簇（大数据量时） - 若按 `reportdate` 或 `damagedate` 时间范围查询频繁，考虑创建 **时间范围分区表**； - 或用 `CLUSTER` 将常一起访问的字段（如 `wycarriskwarnid`, `reportdate`）物理聚簇。 ### 🔹 3. 分页优化：避免 `SKIP ... FIRST` 大偏移 - `SKIP 10000 FIRST 20` 需扫描前 10020 行，当 `pageNo` 很大时变慢； - ✅ **替代方案：基于游标分页（Keyset Pagination）** ```sql -- 第一页查完记住最后的 a.id（或 a.reportdate + a.id 组合） SELECT ... FROM wycar_riskwarn a WHERE a.id > ? -- 上一页最后的 id ORDER BY a.id FIRST 20; ``` > ✅ 无 `SKIP`，性能恒定 O(1)；需应用层维护游标状态。 ### 🔹 4. 统计信息更新（确保优化器选对计划） ```sql UPDATE STATISTICS HIGH FOR TABLE wycar_riskwarn; UPDATE STATISTICS HIGH FOR TABLE wycar_feedback; UPDATE STATISTICS HIGH FOR TABLE xzpt_dictionary; ``` --- ## ✅ 五、执行计划验证方法（DBA 必做） ```sql -- 开启执行计划捕获 SET EXPLAIN ON; -- 执行你的优化后 SQL SELECT SKIP 0 FIRST 10 ... FROM wycar_riskwarn a LEFT JOIN (...); -- 查看 explain.out 文件 ``` ✅ **健康执行计划特征**： - `wycar_feedback` 访问方式为 `index key: 1,2,3`（走你建的复合索引）； - `NOT EXISTS` 或窗口函数子查询显示 `nested loop` 或 `hash join`，无 `table scan`； - `rows` 估算接近实际（非 `1000000` 这种离谱值）。 --- ## ✅ 总结：优化动作清单 | 动作 | 命令/操作 | 是否必须 | |------|------------|-----------| | ✅ **建核心索引** | `CREATE INDEX idx_wycarfb_warnid_valid_id ON wycar_feedback (wycarriskwarnid, validflag, id DESC);` | ✅ 必须 | | ✅ **升级 SQL 写法** | 用 `ROW_NUMBER()`（≥12.10）或 `NOT EXISTS`（<12.10）替代 `MAX()` 子查询 | ✅ 必须 | | ✅ **为字典表建索引** | `CREATE INDEX idx_dict_type_code_flag ON xzpt_dictionary (dictype, diccode, flag);` | ✅ 强烈推荐 | | ✅ **更新统计信息** | `UPDATE STATISTICS HIGH FOR TABLE ...` | ✅ 每次建索引后必做 | | ⚠️ **考虑游标分页** | 改 `SKIP/FIRST` 为 `WHERE id > ? ORDER BY id FIRST N` | ⚠️ 大数据量分页时推荐 | ---