Informix 函數索引全解析：從原理到實戰的性能調優利器

Informix 函數索引全解析：從原理到實戰的性能調優利器

函數索引基於用戶定義例程（UDR）的返回值構建，能將表達式計算結果預存有序，查詢時直接掃描索引避免全表掃描與重複計算，大幅提升 SELECT 性能。本文系統介紹優勢、類型、限制、創建注意事項，並通過三個完整實戰案例（圓面積、圖像 RGB、空間 R-Tree）演示創建、驗證與優化器行為。

核心優勢

• 消除查詢時對每行調用 UDR 的開銷。
• 應用程式無需維護冗餘列（如同時存大小寫混合名與大寫名）。
• DB2 自動維護索引一致性（INSERT/UPDATE/DELETE 同步更新）。

UDR 關鍵要求

• 不可變：相同輸入必然返回相同輸出，不修改數據庫/變量狀態。創建時必須指定 WITH (NOT VARIANT)。
• 可用 SPL、C/C++、Java 實現；支持返回標準 SQL 類型及 UDT；不可返回大對象（BLOB/CLOB）。
• 參數數量受版本/語言限制（如 IDS 9.4：C UDR ≤102 列，Java/SPL ≤341 列）。
• 不支持集合類型參數。內置代數/對數/十六進制函數需封裝後使用。

索引類型與訪問方法

• B-Tree（默認）、R-Tree（空間/多維）、用戶定義二級方法。
• 支持單列、派生值、多列、復合索引（如 CREATE INDEX idx1 ON tab(c1, f(c3))）。

創建與維護限制

• 不支持 ONLINE 模式：CREATE/DROP INDEX ... ONLINE 無效，操作期間持有排他鎖，表不可訪問。
• oncheck -ci 驗證，-y 修復，同普通索引。
• 開銷：創建時全表掃描執行 UDR；DML 時每行執行 UDR。需做成本收益分析——小表、低頻查詢不宜創建。

實戰案例 1：圓面積函數索引（SPL + B-Tree）

CREATE TABLE circles (radius FLOAT);

CREATE FUNCTION circleArea(radius FLOAT) RETURNS FLOAT
WITH (NOT VARIANT)
RETURN 3.14159 * radius * radius;
END FUNCTION;

CREATE INDEX areaOfCircleIndex ON circles (circleArea(radius));

-- 查詢自動使用索引
SELECT radius FROM circles WHERE circleArea(radius) < 500;

實戰案例 2：圖像平均 RGB 函數索引（C UDR + B-Tree，含 EXPLAIN 驗證）

環境準備

• IDS 11.10 on 64-bit Linux，配置 INFORMIXDIR、LD_LIBRARY_PATH。
• 創建 sbspace sbsp2（匹配 ONCONFIG SBSPACENAME）。
• 加載測試圖像至 imagedb:images 表（BLOB 列 image）。
• 編譯 imageUDR.c → imageUDR.so，部署至 $INFORMIXDIR/extend，註冊四個函數：avgRGB/Red/Green/Blue（HANDLESNULLS）。

驗證優化器行為

-- 無索引：全表掃描，Estimated Cost 2
SET EXPLAIN ON;
SELECT name, avgRed(image), avgGreen(image), avgBlue(image), avgRGB(image)
FROM images
WHERE avgRGB(image) > 150 AND avgBlue(image) > 160;
-- sqexplain.out 顯示 SEQUENTIAL SCAN

-- 創建四個函數索引
CREATE INDEX avgRGBIndex ON images (avgRGB(image));
CREATE INDEX avgRedIndex ON images (avgRed(image));
CREATE INDEX avgGreenIndex ON images (avgGreen(image));
CREATE INDEX avgBlueIndex ON images (avgBlue(image));

-- 再次查詢：索引掃描，Estimated Cost 1
SET EXPLAIN ON;
SELECT ... FROM images WHERE avgRGB(image) > 150 AND avgBlue(image) > 160;
-- sqexplain.out 顯示 INDEX PATH，Index Keys: avgRGB(image)，Lower Index Filter: avgRGB > 150

指令控制

-- 強制避免索引
SELECT {+AVOID_INDEX(images avgRGBIndex)} * FROM images WHERE avgRGB(image) > 100;
-- EXPLAIN 顯示 DIRECTIVES FOLLOWED: AVOID_INDEX，回退全表掃描

實戰案例 3：空間數據 R-Tree 函數索引（Spatial DataBlade）

場景

查找海嘯淹沒範圍內的城市：需計算城市幾何中心 st_centroid(citylim)，判斷是否與海嘯邊界 st_envelope(inundation) 相交。創建 R-Tree 索引加速 st_centroid 計算。

關鍵步驟

1. 安裝 Spatial DataBlade 8.21，註冊 ifxrltree.2.00、spatial.8.21.FC1 至 spatialdb。
2. 創建 sbspace syssbspace（匹配 SYSSBSPACENAME）。
3. 用 loadshp 載入 Shapefile（城市邊界、海嘯線），生成表 cities(citylim)、tsunami(inundation)。
4. 創建 R-Tree 函數索引：

CREATE INDEX citycenterindex
ON cities (st_centroid(citylim) st_geometry_ops)
USING rtree;

• st_geometry_ops 操作符類包含 ST_Intersects、ST_Contains 等空間函數，優化器據此匹配索引。
• 查詢 WHERE st_intersects(st_centroid(citylim), st_envelope(inundation)) 觸發索引掃描（Estimated Cost 從 25671 降至 12920，INDEX PATH）。
• 注意：st_distance 不屬於該操作符類，同樣查詢模式不會使用索引。
• 前提：表統計信息最新（載數據後或創建索引前執行 UPDATE STATISTICS）。

結語

函數索引是 Informix 強大的調優工具，通過預計算與有序存儲，同時減少掃描行數與計算開銷。適用於大表、高頻、含確定性表達式的查詢場景。創建前務必做成本收益分析，避免過度索引拖慢寫入。B-Tree 覆蓋通用場景，R-Tree 釋放空間查詢潛能，靈活組合可應對多樣業務需求。