索引类型与基础使用

索引分类

聚簇索引与非聚簇索引

┌─────────────────────────────────────────────────────────────────┐
│                    聚集索引检索流程                              │
├─────────────────────────────────────────────────────────────────┤
│  查询条件：ID = 2                                               │
│                                                                 │
│  聚集索引（按ID排序）                                           │
│  ┌─────┬─────┬─────┬─────┐                                     │
│  │ ID=1│ ID=2│ ID=3│ ID=4│  → 直接命中，返回完整数据           │
│  └─────┴─────┴─────┴─────┘                                     │
└─────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐
│                    非聚集索引检索流程                            │
├─────────────────────────────────────────────────────────────────┤
│  查询条件：NAME = '张三'                                        │
│                                                                 │
│  非聚集索引（按NAME排序）          聚集索引（按ROWID排序）       │
│  ┌──────────┬──────────┐          ┌──────────┬──────────┐     │
│  │ NAME='李四'│ ROWID=2 │          │ ROWID=1 │ 完整数据 │     │
│  │ NAME='王五'│ ROWID=3 │          │ ROWID=2 │ 完整数据 │     │
│  │ NAME='张三'│ ROWID=1 │  ──→     │ ROWID=3 │ 完整数据 │     │
│  │ NAME='赵六'│ ROWID=4 │          │ ROWID=4 │ 完整数据 │     │
│  └──────────┴──────────┘          └──────────┴──────────┘     │
│                                                                 │
│  步骤1：在非聚集索引中找到 NAME='张三' 对应的 ROWID=1           │
│  步骤2：根据 ROWID=1 回聚集索引查找完整数据                      │
└─────────────────────────────────────────────────────────────────┘

按索引功能角度分类

索引类型	说明	适用场景
唯一索引	索引数据根据索引键唯一	需要保证列值唯一性（如身份证号）
函数索引	包含函数/表达式的预先计算值	频繁对列进行函数计算后查询
位图索引	对低基数的列创建位图索引	列的取值很少（如性别、状态）
位图连接索引	针对两个或多个表连接的位图索引	数据仓库、星型模型连接查询
全文索引	在表的文本列上创建	文本内容的模糊搜索
数组索引	在单个数组成员的对象列上创建	对象数组类型的查询
普通索引	上述以外的索引类型	常规查询加速

实索引与虚索引

索引类型	说明
实索引	存储数据的索引，除虚索引外的所有索引均为实索引
虚索引	不存储数据的索引，由系统自动并隐式创建，目的是保证约束的正确性

虚索引自动创建的场景：

场景	说明
场景1	为普通表创建外键的引用约束且未使用 WITH INDEX 时
场景2	为非事务型HUGE表创建 PRIMARY KEY 或 UNIQUE 约束时
场景3	HUGE_UNIQUE_CHECK=0 时，为事务型HUGE表创建 PRIMARY KEY 或 UNIQUE 约束时

-- 查看索引是否为虚索引（FLAG=2代表虚索引）
SELECT FLAG FROM SYSINDEXES WHERE ID = 索引ID;

单列索引和复合索引

从索引键值的个数进行分类，可分为单列索引和复合索引。

单列索引：只有一个索引键的索引。
复合索引：含有多个索引键的索引。

全局索引和局部索引

全局索引：

属性	说明
创建方式	指定 GLOBAL 关键字
索引范围	以整张表的数据为对象
索引结构	在主表创建全局索引，每个子表创建全局本地索引

全局索引进一步分为：

类型	说明	适用环境
全局非分区索引	索引数据存储在一个B树中	除DMDPC外的所有环境
全局分区索引	索引按分区子句分区存储	仅DMDPC环境

局部索引：

属性	说明
创建方式	未指定 GLOBAL 关键字（默认）
索引范围	在每个分区上单独创建
索引结构	主表创建局部索引，每个子表创建子表局部索引

索引使用的基本原则

在插入数据后创建索引 — 避免每次插入都维护索引，提升装载效率
为正确的表和列创建索引 — 大表、高频查询列、连接列
为性能安排索引列顺序 — 最常用的列放在最前面
限制每个表的索引数量 — 索引越多，DML操作开销越大
为每个索引指定表空间 — 可与表分开存储，减少I/O竞争

创建索引

显式创建索引

示例：在 emp 表的 ename 列上创建索引

CREATE INDEX emp_ename ON emp(ename) 
STORAGE (
    INITIAL 50,
    NEXT 50,
    ON users
);

创建聚集索引

-- 以 ename 列新建聚集索引（会重建表数据）
CREATE CLUSTER INDEX clu_emp_name ON emp(ename);

重要说明

说明项	内容
每表限制	每张表只允许有一个聚集索引
重建影响	新建聚集索引会重建整个表及其所有索引（代价非常大）
删除行为	删除聚集索引时，默认以 ROWID 排序，自动重建所有索引
默认索引	默认的 ROWID 聚集索引不允许删除
不支持的场景	函数索引、列存储表、堆表不支持聚集索引

所以在建表时就确定聚集索引键，在表中数据比较少时新建聚集索引

创建唯一索引

CREATE UNIQUE INDEX dept_unique_index ON dept(dname) 
STORAGE (ON users);

创建函数索引

-- 创建基于 UPPER 函数的索引
CREATE INDEX idx_upper_name ON example_tab(UPPER(name));

-- 使用函数索引的查询
SELECT * FROM example_tab WHERE UPPER(name) = 'JOHN';

创建函数索引的限制

限制项	说明
不允许的类型	时间间隔类型
不允许的列	半透明加密列
长度限制	表达式长度理论值不超过816个字符
不允许的类型	CLUSTER 或 PRIMARY KEY
不允许的函数	集函数、不确定函数（如 RAND、SYSDATE、NOW 等）
快速装载	不支持含有函数索引的表

⚠️ 注意事项

若函数索引中使用用户自定义函数，函数必须指定 DETERMINISTIC 属性（确定性函数）
确定性函数变更或删除后，用户需手动重建函数索引

创建位图索引

适用场景：

位图索引适用于低基数（不同值很少）的列，如性别、状态、地区等。

位图索引的限制

限制项	说明
支持的表类型	普通表、堆表、水平分区表
不支持的类型	大字段、计算表达式列
不支持的列	UNIQUE 列、PRIMARY KEY
不支持的表	存在 CLUSTER KEY 的表
列数限制	单列或不超过63个组合列
环境限制	MPP 环境下不支持
全局索引	不支持全局位图索引
并发限制	包含位图索引的表不支持并发插入、删除、更新
分区表限制	不支持在间隔分区表上创建

创建位图连接索引

适用场景

数据仓库环境中，针对两个或多个表的连接建立的位图索引，提高连接查询效率。

CREATE BITMAP INDEX 索引名 ON 事实表(维度表.列名)
FROM 事实表, 维度表
WHERE 连接条件;

隐式创建索引

触发条件：

当创建 UNIQUE KEY 或 PRIMARY KEY 约束时，DM8 会自动隐式创建唯一索引。

https://eco.dameng.com