如何加速Informix中查找MU前缀序列空缺的SQL查询？

AI 小北

2026-5-12

优化Informix查找'MU'开头cm_code后序空缺的查询

看起来你在查找coordman表中以'MU'开头的cm_code（char(8)类型）序列里的后序空缺值，原查询用了NOT EXISTS但性能不佳，我来帮你拆解问题并给出优化方案。

原查询的核心问题

你的原查询存在几个拖慢性能的点：

重复计算同一个表达式：多次调用HEX('0x'||SUBSTR(LPAD(NVL(l.cm_code, '0'), 7, '0') , 3))::INT，每次都要重新解析、转换，浪费CPU资源。
缺少针对性索引：NOT EXISTS子句会对每条匹配的记录做全表扫描，没有索引的话性能会随着数据量增长急剧下降。
冗余的NVL判断：NVL(l.cm_code, ' ')在SUBSTR(...,1,2)='MU'的条件下是多余的——NULL值的cm_code不可能截取到'MU'开头的字符串，可以直接排除。

优化方案分步走

1. 先创建针对性索引

首先创建一个过滤型函数索引，只包含'MU'开头的记录，同时包含我们需要的转换后整数值，这样查询可以直接从索引获取数据，无需回表：

CREATE INDEX idx_coordman_mu_code ON informix.coordman 
(
    SUBSTR(cm_code, 1, 2),
    HEX('0x' || LPAD(RTRIM(SUBSTR(cm_code, 3)), 6, '0'))::INT
)
WHERE SUBSTR(cm_code, 1, 2) = 'MU';

这里我调整了字符串截取逻辑：先用SUBSTR(cm_code,3)获取'MU'之后的部分，RTRIM去掉char类型自带的右侧空格，再用LPAD补0到6位，确保转十六进制时格式正确（原查询的LPAD到7位可能是笔误，因为cm_code是char(8)，前两位是'MU'，后面最多6位）。

2. 简化查询，用CTE预处理计算值

用CTE（公共表表达式）把重复的计算逻辑抽出来，只执行一次，再基于预处理后的结果查找空缺：

WITH mu_codes AS (
    SELECT 
        -- 预处理每个'MU'开头记录的十六进制转整数值
        HEX('0x' || LPAD(RTRIM(SUBSTR(cm_code, 3)), 6, '0'))::INT AS code_num
    FROM informix.coordman
    WHERE SUBSTR(cm_code, 1, 2) = 'MU'
      AND cm_code IS NOT NULL  -- 直接排除NULL值，避免无效计算
)
SELECT FIRST 1
    m.code_num + 1 AS dec,
    HEX(m.code_num + 1) AS hex,
    SUBSTR(HEX(m.code_num + 1)::CHAR(10), 6) AS str
FROM mu_codes m
WHERE NOT EXISTS (
    SELECT 1
    FROM mu_codes r
    WHERE r.code_num = m.code_num + 1
)
ORDER BY dec ASC;

这个版本的查询会利用我们刚才创建的索引，mu_codes CTE可以直接从索引读取code_num，不需要访问表的其他字段，NOT EXISTS子句的匹配也会更快。

3. 进阶：用生成序列快速找最小空缺

如果你的Informix版本支持CONNECT BY（大多数新版本都支持），可以用生成连续序列的方式直接定位最小空缺，这种方式在数据量大时可能更高效：

WITH mu_codes AS (
    SELECT 
        HEX('0x' || LPAD(RTRIM(SUBSTR(cm_code, 3)), 6, '0'))::INT AS code_num
    FROM informix.coordman
    WHERE SUBSTR(cm_code, 1, 2) = 'MU'
      AND cm_code IS NOT NULL
),
code_range AS (
    -- 获取现有'MU'序列的最小和最大值
    SELECT MIN(code_num) AS min_num, MAX(code_num) AS max_num
    FROM mu_codes
),
generated_codes AS (
    -- 生成从min_num到max_num的连续整数序列
    SELECT min_num + LEVEL - 1 AS code_num
    FROM code_range
    CONNECT BY LEVEL <= max_num - min_num + 1
)
-- 找第一个不在现有序列中的值
SELECT FIRST 1
    g.code_num AS dec,
    HEX(g.code_num) AS hex,
    SUBSTR(HEX(g.code_num)::CHAR(10), 6) AS str
FROM generated_codes g
LEFT JOIN mu_codes m ON g.code_num = m.code_num
WHERE m.code_num IS NULL
ORDER BY dec ASC;

这种方法的优势是直接遍历连续序列，找到第一个缺口，无需对每条记录做NOT EXISTS判断，尤其适合序列比较连续的场景。

额外注意事项

确认cm_code的存储格式：如果'MU'后面的部分确实是6位十六进制，那上面的LPAD(RTRIM(SUBSTR(cm_code,3)),6,'0')是正确的；如果实际格式不同，需要调整截取和补位逻辑。
测试索引效果：创建索引后可以用SET EXPLAIN ON查看查询计划，确认索引是否被正确使用。

内容的提问来源于stack exchange，提问作者famedoro