你提供的 Informix 分页 SQL 已经**整体结构合理、使用了 `FIRST 1` 关联最新反馈**，是 Informix 下的正确方向。但存在 **多个关键性能与健壮性隐患**，尤其在「分页 + 关联子查询」场景下极易引发严重性能退化（如全表扫描、重复排序、索引失效）。下面从 **问题诊断 → 深度优化方案 → 执行计划验证要点 → 最终推荐写法** 全面解析，并给出 **Informix 环境下真正最优的分页+关联最新记录写法**。
---
### ⚠️ 当前 SQL 的核心问题（Informix 特定）
| 问题 | 说明 | 后果 |
|------|------|------|
| ❌ **`LEFT JOIN (SELECT FIRST 1 ...)` 在分页中被多次执行** | Informix **不支持关联子查询的物化（materialization）**，`d` 子查询对 `wycar_riskwarn` 的**每一行都会独立执行一次**（即 N+1 查询），且每次都要 `ORDER BY s1.id DESC` → 若主表返回 1000 行，`wycar_feedback` 就被扫描/排序 1000 次！ | 🐢 极慢！数据量稍大（如主表万级）直接超时 |
| ❌ **`ORDER BY a.id DESC` 在分页前未加索引覆盖** | `SKIP/FIRST` 分页**必须依赖有序索引**，否则 Informix 会先全表扫描再内存排序，`SKIP` 无法跳过，性能灾难 | ⏱️ `SKIP 10000 FIRST 20` 可能扫描 10020 行甚至全表 |
| ❌ **字典表 `xzpt_dictionary` 关联无有效索引或过滤低效** | `b.dictype = '...' AND b.flag = '...'` 若未建复合索引，每次关联都可能全表扫字典表（即使字典小，高并发下也成瓶颈） | 🔁 字典表被反复扫描，拖慢整体 |
| ❌ **`wycar_feedback` 子查询未加 `validflag = '1'` 过滤** | 你之前已意识到需取“有效最新”，但当前子查询缺失该条件 → 可能关联到 `validflag='0'` 的无效反馈，业务逻辑错误 | ❌ 数据错！例如预警有 5 条反馈，其中最新 ID 是无效的，结果返回空反馈字段 |
> ✅ **根本矛盾**：Informix 的 `FIRST 1` 子查询在 `JOIN` 中无法“提前聚合”，必须改用 **驱动表预聚合 + 高效关联** 模式。
---
### ✅ Informix 分页 + 关联最新记录的最优解：**两步走 —— 先分页主表，再批量关联最新反馈**
#### ✅ 方案：`WITH` CTE（Informix 11.50+）或派生表 + `IN` 关联（兼容所有版本）
> ✅ **Informix 11.50+ 推荐（CTE + `FIRST` 预聚合）**
```sql
-- Step 1: 先分页获取主表ID列表（带排序和SKIP/FIRST）
-- Step 2: 基于这些ID，批量查出每条对应的最新有效反馈（一次聚合，非N次）
WITH paginated_main AS (
SELECT FIRST #{param.pageSize} SKIP #{param.pageNo}
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.claimstate,
a.subjectrisklevel, a.subjectoperationscore, a.subjectoperationcity,
a.surveyrisklevel, a.surveyoperationscore, a.surveyoperationcity,
a.fulltimestamp
FROM wycar_riskwarn a
<where>
<include refid="pageWhereSql"/>
</where>
ORDER BY a.id DESC
),
latest_feedback AS (
SELECT s1.*
FROM wycar_feedback s1
WHERE s1.rowid IN (
SELECT FIRST 1 s2.rowid
FROM wycar_feedback s2
WHERE s2.wycarriskwarnid IN (SELECT wycarriskwarnid FROM paginated_main)
AND s2.validflag = '1' -- ✅ 关键：只取有效反馈
GROUP BY s2.wycarriskwarnid
ORDER BY MAX(s2.id) DESC -- 每组取MAX(id)，再按MAX排序（确保rowid对应最新）
)
)
SELECT
pm.*,
b.dicvalue AS subjectrisklevelname,
c.dicvalue AS surveyrisklevelname,
lf.id AS feedback_id, lf.content AS feedback_content, lf.create_time AS feedback_time
FROM paginated_main pm
LEFT JOIN latest_feedback lf ON pm.wycarriskwarnid = lf.wycarriskwarnid
LEFT JOIN xzpt_dictionary b
ON pm.subjectrisklevel = b.diccode
AND b.dictype = 'wycarRiskwarnRisklevel'
AND b.flag = '0000000000'
LEFT JOIN xzpt_dictionary c
ON pm.surveyrisklevel = c.diccode
AND c.dictype = 'wycarRiskwarnRisklevel'
AND c.flag = '0000000000';
```
✅ **优势**：
- 主表分页仅执行 **1次**（`paginated_main`），`SKIP/FIRST` 走索引，极快
- 反馈关联仅执行 **1次聚合查询**（`latest_feedback`），通过 `GROUP BY wycarriskwarnid` + `MAX(id)` 批量计算所有目标预警的最新反馈 ID，避免 N 次子查询
- `rowid` 关联精准高效，零回表
- `validflag = '1'` 过滤前置，逻辑绝对正确
⚠️ 注意：`ORDER BY MAX(s2.id) DESC` 是 Informix 兼容写法（`GROUP BY` 后允许 `MAX()` 排序），确保取到的是每组最大 ID 对应的 `rowid`。
---
#### ✅ 方案（兼容 Informix 9.x / 10.x，无 CTE）：**两层子查询 + `IN` 关联**
```sql
SELECT
pm.*,
b.dicvalue AS subjectrisklevelname,
c.dicvalue AS surveyrisklevelname,
lf.id AS feedback_id, lf.content AS feedback_content, lf.create_time AS feedback_time
FROM (
-- Step 1: 分页主表（必须有 ORDER BY + 索引！）
SELECT FIRST #{param.pageSize} SKIP #{param.pageNo}
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.claimstate,
a.subjectrisklevel, a.subjectoperationscore, a.subjectoperationcity,
a.surveyrisklevel, a.surveyoperationscore, a.surveyoperationcity,
a.fulltimestamp
FROM wycar_riskwarn a
<where>
<include refid="pageWhereSql"/>
</where>
ORDER BY a.id DESC
) pm
-- Step 2: 批量关联最新反馈（关键优化！）
LEFT JOIN wycar_feedback lf ON lf.wycarriskwarnid = pm.wycarriskwarnid
AND lf.id = (
SELECT MAX(s2.id)
FROM wycar_feedback s2
WHERE s2.wycarriskwarnid = pm.wycarriskwarnid
AND s2.validflag = '1' -- ✅ 有效过滤
)
LEFT JOIN xzpt_dictionary b
ON pm.subjectrisklevel = b.diccode
AND b.dictype = 'wycarRiskwarnRisklevel'
AND b.flag = '0000000000'
LEFT JOIN xzpt_dictionary c
ON pm.surveyrisklevel = c.diccode
AND c.dictype = 'wycarRiskwarnRisklevel'
AND c.flag = '0000000000';
```
✅ **为什么比原写法快？**
- `MAX(s2.id)` 子查询虽仍存在，但 **Informix 优化器通常能将其转换为索引范围查找**（尤其当 `wycarriskwarnid + validflag + id` 有复合索引时），且只对 `pm` 中实际返回的 N 行（如 20 行）执行，而非全主表扫描。
- 相比原 `FIRST 1` 子查询（每次都要 `ORDER BY`），`MAX(id)` 更轻量（无需排序，仅找最大值）。
---
### 🔑 必建索引（Informix 性能基石）
| 表 | 索引语句 | 说明 |
|-----|-----------|------|
| `wycar_riskwarn` | `CREATE INDEX idx_riskwarn_page ON wycar_riskwarn (id DESC);` | ✅ 支撑 `ORDER BY a.id DESC` + `SKIP/FIRST`，必须！若 `pageWhereSql` 有其他条件（如 `status`），需扩展为 `(status, id DESC)` |
| `wycar_feedback` | `CREATE INDEX idx_fb_warn_valid_id ON wycar_feedback (wycarriskwarnid, validflag, id DESC);` | ✅ 支撑 `WHERE wycarriskwarnid = ? AND validflag = '1' ORDER BY id DESC` 或 `MAX(id)`，全覆盖 |
| `xzpt_dictionary` | `CREATE INDEX idx_dict_type_flag_code ON xzpt_dictionary (dictype, flag, diccode);` | ✅ 加速字典关联，避免全表扫描 |
> 💡 验证：执行 `SET EXPLAIN ON;` 后运行 SQL，检查执行计划中：
> - `wycar_riskwarn` 是否出现 `INDEX PATH` + `Keys: 1`（`id DESC` 索引命中）
> - `wycar_feedback` 是否出现 `INDEX PATH` + `Keys: 3`（三列索引全用）
> - 无 `TABLE SCAN` 或 `SORT`（表示未触发内存排序）
---
### ✅ 最终推荐（MyBatis XML 完整版，Informix 11.50+）
```xml
<select id="selectRiskwarnWithLatestFeedback" resultType="map">
WITH paginated_main AS (
SELECT FIRST #{param.pageSize} SKIP #{param.pageNo}
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.claimstate,
a.subjectrisklevel, a.subjectoperationscore, a.subjectoperationcity,
a.surveyrisklevel, a.surveyoperationscore, a.surveyoperationcity,
a.fulltimestamp
FROM wycar_riskwarn a
<where>
<include refid="pageWhereSql"/>
</where>
ORDER BY a.id DESC
),
latest_feedback AS (
SELECT s1.*
FROM wycar_feedback s1
WHERE s1.rowid IN (
SELECT FIRST 1 s2.rowid
FROM wycar_feedback s2
WHERE s2.wycarriskwarnid IN (SELECT wycarriskwarnid FROM paginated_main)
AND s2.validflag = '1'
GROUP BY s2.wycarriskwarnid
ORDER BY MAX(s2.id) DESC
)
)
SELECT
pm.*,
b.dicvalue AS subjectrisklevelname,
c.dicvalue AS surveyrisklevelname,
lf.id AS feedback_id,
lf.content AS feedback_content,
lf.create_time AS feedback_time
FROM paginated_main pm
LEFT JOIN latest_feedback lf ON pm.wycarriskwarnid = lf.wycarriskwarnid
LEFT JOIN xzpt_dictionary b
ON pm.subjectrisklevel = b.diccode
AND b.dictype = 'wycarRiskwarnRisklevel'
AND b.flag = '0000000000'
LEFT JOIN xzpt_dictionary c
ON pm.surveyrisklevel = c.diccode
AND c.dictype = 'wycarRiskwarnRisklevel'
AND c.flag = '0000000000';
</select>
```
---
博主向大家请教Informix各个版本在大数据量情况下的分页情况，希望了解相关内容。
扫一扫
2690
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
