centos informix索引使用指南

CentOS 上 Informix 索引使用指南

一 环境准备与常用工具

• 以 informix 用户登录，确保环境变量正确：
  • 设置 INFORMIXDIR、INFORMIXSERVER、ONCONFIG、INFORMIXSQLHOSTS，并将 $INFORMIXDIR/bin 加入 PATH。
  • 使用 dbaccess 连接数据库执行 DDL/DML；使用 onstat 观察内存、锁、SQL 等运行时信息。
  • 性能分析建议开启 set explain on，输出执行计划到 sqexplain.out；定期执行 update statistics 更新统计信息，避免优化器选错计划。
  • 索引与数据一致性检查与修复可用 oncheck（如：oncheck -ci 检查索引，oncheck -cd 检查数据行）。

二 索引类型与适用场景

• B-tree 索引：通用选择，适合点查、范围查询、排序与分组（=、<、>、BETWEEN、ORDER BY/GROUP BY）。
• R-tree 索引：适合多维数据检索（如地理空间、GIS 场景）。
• 函数索引：对列的函数/表达式结果建立索引，用于“大小写不敏感搜索”“日期格式化”“表达式过滤”等；函数需声明为 NOT VARIANT（不可变）。
• DataBlade 二级访问方法：为自定义数据类型提供专用索引（由模块实现）。
• 索引一般用于：连接条件列、过滤条件列、ORDER BY/GROUP BY 列、以及作为过滤条件的函数结果列。

三 创建与维护索引的要点

• 基本创建与复合索引：
  • 单列：CREATE INDEX idx_name ON tab(col);
  • 唯一：CREATE UNIQUE INDEX ux_name ON tab(col);
  • 复合：CREATE INDEX cx_name ON tab(c1, c2, c3); 将高选择性、常用于 WHERE 的前导列放在左侧。
• 函数索引示例（大小写不敏感查询）：
  • 定义函数：CREATE FUNCTION toUpper(name VARCHAR(100)) RETURNS VARCHAR(100) WITH (NOT VARIANT) RETURN upper(name);
  • 建立索引：CREATE INDEX ucname_idx ON t1(toUpper(name));
  • 查询：SELECT * FROM t1 WHERE toUpper(name) LIKE ‘ANTHONY % HOPKINS’;
  • 注意：用于函数索引的 UDR 必须是 NOT VARIANT；不支持返回 LOB 的 UDR；集合类型（SET/MULTISET/LIST）不能作为索引参数；创建/删除函数索引时不可使用 ONLINE 关键字。
• 外键与索引：
  • 外键本身不自动创建索引；为提高 JOIN 与 DELETE/UPDATE 父表时的性能，建议在被引用列（子表外键）上建立索引。
• 统计信息与执行计划：
  • 导入/批量加载后务必执行 update statistics；使用 set explain on 验证索引是否被使用。
• 在线创建限制：
  • 函数索引不支持 ONLINE 创建/删除；普通索引是否支持 ONLINE 取决于版本与配置，请以实际版本手册为准。

四 索引使用与查询优化的实践

• 让索引生效的写法：
  • 避免对索引列做运算或函数封装（如 WHERE UPPER(name)=… 将无法使用 name 上的普通索引；应使用函数索引或改写条件）。
  • 复合索引遵循“最左前缀”原则；覆盖索引可减少回表（SELECT 列表尽量包含在索引列中）。
  • 高选择性列优先索引；低选择性（如性别）单列索引收益有限，可考虑联合索引或位图索引（若使用相关特性/版本）。
• 典型场景建议：
  • 连接键、过滤高频列、排序/分组列优先建立索引。
  • 范围查询与排序并存时，复合索引列序通常为：范围列在后、排序列更靠后（视具体访问计划而定）。
• 多维数据（如经纬度）优先考虑 R-tree 或相应 DataBlade 索引。

五 索引维护与问题排查

• 例行维护：
  • 定期 update statistics；在大批量 DML 后及时更新统计信息，避免计划退化。
  • 监控空间与碎片：使用 onstat -d/-D 观察 dbspace 与 chunk 使用；必要时重建或重组索引。
• 一致性检查与修复：
  • 使用 oncheck -ci :# 检查索引一致性；oncheck -cd 检查数据行；发现异常可先导出数据、重建索引后导入。
• 执行计划验证：
  • 使用 set explain on 输出 sqexplain.out，核对是否走索引扫描、是否出现顺序扫描，并据此调整索引或 SQL。