达梦数据库聚集索引和非聚集索引的区别

问题16：达梦聚集索引和非聚集索引的区别

一、概念说明

聚集索引（Clustered Index）：数据行的物理存储顺序与索引键的逻辑顺序一致。一张表只能有一个聚集索引。达梦默认使用索引组织表，主键即为聚集索引。数据直接存储在索引树的叶子节点中，查询主键时无需回表。

非聚集索引（Secondary Index）：索引键的顺序与数据行的物理存储顺序无关。一张表可以有多个非聚集索引。非聚集索引的叶子节点存储的是指向数据行的 ROWID，查询时需要通过 ROWID 回表获取完整数据。

二、实操环境

实例：TESTDB（端口 5239）
测试表：TEST.EMPLOYEE（约 100004 行）
聚集索引：ID 列（主键）
非聚集索引：IDX_EMP_NAME（在 NAME 列上创建的普通索引）

三、表结构确认

SP_TABLEDEF('TEST', 'EMPLOYEE');

输出：

CREATE TABLE "TEST"."EMPLOYEE" (
    "ID" INT NOT NULL,
    "NAME" VARCHAR(50),
    "AGE" INT,
    "DEPT" VARCHAR(50),
    NOT CLUSTER PRIMARY KEY("ID")
) STORAGE(ON "MAIN", CLUSTERBTR);

NOT CLUSTER PRIMARY KEY("ID") 表示 ID 是聚集索引（主键），数据按 ID 顺序物理存储。

四、执行计划对比

4.1 使用聚集索引查询所有列

EXPLAIN SELECT * FROM TEST.EMPLOYEE WHERE ID = 1;

输出：

1   #NSET2: [1, 1, 116] 
2     #LOCAL COLLECT: [1, 1, 116]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [1, 1, 116]; exp_num(5), is_atom(FALSE); INFO_BITS(0); BATCH_EXP_NUM(0); spl_info(NULL)
4         #BLKUP2: [1, 1, 116]; INDEX33555477(EMPLOYEE); use_clu_addr(0)
5           #SSEK2: [1, 1, 116]; scan_type(ASC), INDEX33555477(EMPLOYEE), scan_range[1,1], is_global(0)

解读：

#SSEK2：对 INDEX33555477（唯一非聚集索引，基于 ID）进行范围扫描，定位 ID=1 的 ROWID
#BLKUP2：根据 ROWID 回表获取完整行数据
此处 INDEX33555477 是系统自动为 ID 列创建的唯一非聚集索引，而非聚集索引本身。虽然 ID 是主键（聚集索引），但达梦会额外创建一个同名的唯一非聚集索引。

关键点：由于使用的是 INDEX33555477（非聚集索引），需要 #BLKUP2 回表获取数据。

4.2 使用非聚集索引查询所有列

EXPLAIN SELECT * FROM TEST.EMPLOYEE WHERE NAME = '张三';

输出：

1   #NSET2: [1, 1, 116] 
2     #LOCAL COLLECT: [1, 1, 116]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [1, 1, 116]; exp_num(5), is_atom(FALSE); INFO_BITS(0); BATCH_EXP_NUM(0); spl_info(NULL)
4         #BLKUP2: [1, 1, 116]; IDX_EMP_NAME(EMPLOYEE); use_clu_addr(0)
5           #SSEK2: [1, 1, 116]; scan_type(ASC), IDX_EMP_NAME(EMPLOYEE), scan_range['张三','张三'], is_global(0)

解读：

#SSEK2：对 IDX_EMP_NAME（非聚集索引）进行范围扫描，定位 NAME='张三' 的 ROWID
#BLKUP2：根据 ROWID 回表获取完整行数据

关键点：非聚集索引查询需要 #BLKUP2 回表操作。#SSEK2 是精确范围扫描（scan_range['张三','张三']），定位效率高。

4.3 仅查询聚集索引列（无需回表）

EXPLAIN SELECT ID FROM TEST.EMPLOYEE WHERE ID = 1;

输出：

1   #NSET2: [1, 1, 16] 
2     #LOCAL COLLECT: [1, 1, 16]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [1, 1, 16]; exp_num(2), is_atom(FALSE); INFO_BITS(0); BATCH_EXP_NUM(0); spl_info(NULL)
4         #SSEK2: [1, 1, 16]; scan_type(ASC), INDEX33555477(EMPLOYEE), scan_range[1,1], is_global(0)

解读：

#SSEK2：对 INDEX33555477 进行范围扫描
没有 #BLKUP2 回表操作，因为查询列 ID 已经在索引中，索引覆盖了查询，不需要回表

关键点：当查询列全部在索引中时，即使使用非聚集索引也不需要回表，这就是索引覆盖。

4.4 仅查询非聚集索引列（索引覆盖）

EXPLAIN SELECT NAME FROM TEST.EMPLOYEE WHERE NAME = '张三';

输出：

1   #NSET2: [1, 1, 60] 
2     #LOCAL COLLECT: [1, 1, 60]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [1, 1, 60]; exp_num(2), is_atom(FALSE); INFO_BITS(0); BATCH_EXP_NUM(0); spl_info(NULL)
4         #SSEK2: [1, 1, 60]; scan_type(ASC), IDX_EMP_NAME(EMPLOYEE), scan_range['张三','张三'], is_global(0)

解读：

#SSEK2：对 IDX_EMP_NAME 进行范围扫描
没有 #BLKUP2 回表操作，因为查询列 NAME 已经在索引中，索引覆盖了查询，不需要回表

关键点：非聚集索引也能实现索引覆盖，前提是查询列全部在索引键中。

4.5 查询非索引列（需要回表）

EXPLAIN SELECT ID, NAME, AGE FROM TEST.EMPLOYEE WHERE NAME = '张三';

输出：

1   #NSET2: [1, 1, 68] 
2     #LOCAL COLLECT: [1, 1, 68]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [1, 1, 68]; exp_num(4), is_atom(FALSE); INFO_BITS(0); BATCH_EXP_NUM(0); spl_info(NULL)
4         #BLKUP2: [1, 1, 68]; IDX_EMP_NAME(EMPLOYEE); use_clu_addr(0)
5           #SSEK2: [1, 1, 68]; scan_type(ASC), IDX_EMP_NAME(EMPLOYEE), scan_range['张三','张三'], is_global(0)

解读：

#SSEK2：对 IDX_EMP_NAME 进行范围扫描，定位 NAME='张三' 的 ROWID
#BLKUP2：根据 ROWID 回表获取完整行数据（AGE 列不在索引中，必须回表）

关键点：当查询列包含索引中不存在的列时，必须回表获取数据，增加了 I/O 开销。

五、执行计划对比总结

查询	索引类型	操作符	是否回表	原因
`SELECT * WHERE ID=1`	非聚集索引（唯一，基于ID）	`SSEK2` → `BLKUP2`	是	查询所有列，需要回表取完整行
`SELECT * WHERE NAME='张三'`	非聚集索引（NAME）	`SSEK2` → `BLKUP2`	是	查询所有列，需要回表取完整行
`SELECT ID WHERE ID=1`	非聚集索引（唯一，基于ID）	`SSEK2`（无 BLKUP2）	否	索引覆盖（查询列在索引中）
`SELECT NAME WHERE NAME='张三'`	非聚集索引（NAME）	`SSEK2`（无 BLKUP2）	否	索引覆盖（查询列在索引中）
`SELECT ID,NAME,AGE WHERE NAME='张三'`	非聚集索引（NAME）	`SSEK2` → `BLKUP2`	是	查询列包含非索引列 `AGE`，需要回表

六、对比总结

对比项	聚集索引	非聚集索引
物理存储	数据按索引键顺序存储	索引与数据分离
每表数量	只能有 1 个	可以有多个
叶子节点	存储完整数据行	存储 ROWID（指向数据行）
查询是否需要回表	不需要（但达梦主键会额外创建非聚集索引）	需要（除非索引覆盖）
数据更新影响	影响大（数据物理位置可能变化）	影响小（仅更新索引）
适用场景	主键查询、范围查询	频繁作为查询条件的列

重要说明：达梦默认使用索引组织表，主键即为聚集索引。但达梦也会为主键自动创建一个同名的唯一非聚集索引（如 INDEX33555477），用于快速定位。因此在实际执行计划中，主键查询可能仍显示 SSEK2 + BLKUP2，这是达梦的内部机制，不影响结论。

七、核心要点

聚集索引只有一个：主键自动成为聚集索引，数据按主键物理排序。
非聚集索引可以有多个：在 NAME 列上创建的 IDX_EMP_NAME 就是非聚集索引。
回表操作（#BLKUP2）：非聚集索引查询时，如果查询列不在索引中，需要回表获取完整行数据，增加 I/O 开销。
索引覆盖：当查询列全部在索引键中时，非聚集索引也可以不回表，直接返回索引中的值。
达梦特性：达梦默认主键会额外生成一个同名的唯一非聚集索引，因此主键查询的执行计划中也可能出现 SSEK2 + BLKUP2。

八、完整操作命令清单

-- 1. 查看表定义
SP_TABLEDEF('TEST', 'EMPLOYEE');

-- 2. 查看索引信息
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 
FROM DBA_INDEXES 
WHERE OWNER='TEST' AND TABLE_NAME='EMPLOYEE';

-- 3. 聚集索引查询（主键 ID）
EXPLAIN SELECT * FROM TEST.EMPLOYEE WHERE ID = 1;

-- 4. 非聚集索引查询（NAME）
EXPLAIN SELECT * FROM TEST.EMPLOYEE WHERE NAME = '张三';

-- 5. 索引覆盖查询（仅查询索引列）
EXPLAIN SELECT NAME FROM TEST.EMPLOYEE WHERE NAME = '张三';

-- 6. 非索引列查询（需要回表）
EXPLAIN SELECT ID, NAME, AGE FROM TEST.EMPLOYEE WHERE NAME = '张三';