创建和管理索引
在传统数据库中,索引常用于显著提升数据访问性能。但在 Apache Cloudberry 这样的分布式数据库中,索引的使用需要更加谨慎。Apache Cloudberry 的顺序扫描速度非常快,而索引查找则依赖磁盘的随机访问模式。由于数据分布在多个 Segment 上,每个 Segment 只需扫描自己持有的数据子集即可返回结果。再加上表分区机制,查询所需扫描的数据量可能会进一步减少。因此,在商业智能(BI )类查询中,由于通常返回大量数据,使用索引反而可能带来性能负担。
推荐的做法是,在没有索引的情况下先运行查询工作负载。如有必要,再根据实际情况添加索引。索引通常更适合 OLTP 场景,因为这类查询往往只访问单条记录或小范围数据。对于返回目标行集的查询,索引在 AO 表上也能带来性能优势,执行器在合适情况下会选择使用索引,而非全表扫描。对于压缩数据,索引访问意味着只解压必要的行,从而提升整体效率。
当表定义了主键时,Apache Cloudberry 会自动创建 PRIMARY KEY
约束。在分区表上创建索引时,需在根分区表上直接创建,系统会自动将该索引扩展到所有子表。注意,不能在系统自动创建的子表上单独添加索引。
另外,UNIQUE CONSTRAINT
(如 PRIMARY KEY CONSTRAINT
)会隐式创建一个 UNIQUE INDEX
,该索引必须包含分布键和分区键的所有列,并在整个表(包括子分区)范围内保持唯一性。
索引会引入额外开销:它们占用存储空间,并在更新表数据时需要同步维护。因此,请务必确认查询工作负载确实使用了所创建的索引,并验证其是否带来了性能提升。要确定是否使用了索引,可以检查查询计划(通过 EXPLAIN
打印查询计划)。
索引类型
Apache Cloudberry 支持 Postgres 的多种索引类型,包括 B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN 索引。不同类型的索引适用于不同查询场景。B-tree 是默认类型,适用范围最广。关于各类索引的详细说明,可参考 PostgreSQL 官方文档中的索引类型。
Apache Cloudberry 中,唯一索引的索引键列必须与分布键相同或包含分布键所有列。在分区表上,唯一索引不能跨所有分区强制唯一性,仅在单个子表内有效。
Bitmap 索引
Bitmap 索引特别适用于数据量大、查询频繁且更新较少的典型数据仓库场景。相较于普通索引,它在节省存储空间的同时,也能高效处理多条件查询。
Bitmap 索引为每个键值维护一个位图,位图中的每一位表示对应位置上的行是否包含该键值。多个位图之间可以进行布尔运算(如 AND
、OR
),从而支持高效的多条件组合过滤。在访问数据前,Bitmap 运算可提前过滤大量无关行,显著提升查询性能。
使用建议:
-
适用于数据仓库类的查询型负载;
-
最 适合基数中等(大约 100 到 100000 个唯一值)的列;
-
尤其适用于
WHERE
子句中包含多个AND
或OR
条件的查询; -
自 v2.0.0 起,支持通过数组谓词(如
col IN (...)
或col = ANY(array)
)触发 Bitmap Index Scan:- 可应用于 B-tree 和 Hash 索引;
- Hash 索引原先仅支持等值匹配,现在也支持数组比较谓词;
- 执行器会基于代价模型自动判断是否使用 Bitmap 路径。
示例:以下查询可以触发 Bitmap Index 扫描,从而有效利用 Hash 索引处理多值条件,在大数据量场景下提升查询效率。
CREATE TABLE users(id int, name text) DISTRIBUTED BY (id);
CREATE INDEX ON users USING hash (name);
SELECT * FROM users WHERE name IN ('alice', 'bob', 'carol');
使用限制:
- 不适用于唯一性列或高基数字段(如用户 ID、电话号码)。
- 不推荐用于频繁更新的 OLTP 场景。
- 建议在实际测试确认性能收益后再添加 Bitmap 索引。
管理索引
对索引进行聚簇
可以使用 CLUSTER
命令按照索引对表数据进行物理重排。不过,对于非常大的表,这一过程可能耗时较长。为了更高效地实现相同目的,也可以通过手动方式对表数据进行重新排序:创建一个中间表,并按预期顺序插入数据。例如:
CREATE TABLE new_table (LIKE old_table)
AS SELECT * FROM old_table ORDER BY myixcolumn;
DROP old_table;
ALTER TABLE new_table RENAME TO old_table;
CREATE INDEX myixcolumn_ix ON old_table;
VACUUM ANALYZE old_table;
创建索引
使用 CREATE INDEX
命令可以在表上创建索引。默认索引类型为 B-tree。例如,在 employee 表的 gender 列上创建 B-tree 索引:
CREATE INDEX gender_idx ON employee (gender);
在 films 表的 title 列上创建位图索引(Bitmap index):
CREATE INDEX title_bmp_idx ON films USING bitmap (title);
重建表上的所有索引
可以使用 REINDEX
命令重建表或某个索引:
REINDEX my_table;
重建表上单个索引:
REINDEX my_index;
删除索引
使用 DROP INDEX
命令可删除索引。例如:
DROP INDEX title_idx;
在数据加载过程中,先删除所有索引,完成数据加载后再重新创建索引,通常可以加快整体加载速度。
仅索引扫描和覆盖索引
Apache Cloudberry 仅在新建表上启用仅索引扫描(index-only scan)和覆盖索引(covering index)功能。
什么是仅索引扫描
在 Apache Cloudberry 中,所有索引都是辅助索引,单独存储在主数据(堆表)之外。
在一般的索引扫描中,执行器会先通过索引定位符合条件的元组位置,然后再通过堆表指针访问堆表以读取实际数据。由于堆表中的数据通常是非连续存储的,这种方式会引发大量随机 I/O,尤其在传统机械硬盘上表现更明显。尽管位图扫描在一定程度上可以缓解这一问题,但仍无法完全避免堆表访问的开销。
仅索引扫描(index-only scan)是一种可以完全通过索引返回结果的扫描方式,无需访问堆表表,从而显著提高查询性能。
从 v2.0.0 起,Apache Cloudberry(基于 ORCA 执行器)在 AO 表和 PAX 表上支持仅索引扫描,有效提升这些表在重读场景下的查询性能。
使用条件
要启用仅索引扫描,需同时满足以下两个基本条件:
-
使用支持该功能的索引类型:
- B-tree 索引始终支持。
- GiST 和 SP-GiST 索引在部分操作符类下支持。
- GIN 索引不支持(仅存储部分字段信息)。
- 其他索引类型通常不支持。
-
查询中引用的列全部包含在索引中:
可使用仅索引扫描的查询示例:
SELECT x, y FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND y < 42;无法使用仅索引扫描的查询示例:
SELECT x, z FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND z < 42;
额外注意:MVCC 可见性检查
即便满足以上两个条件,系统仍需确认每条记录是否对当前事务“可见”,即符合多版本并发控制(MVCC)规则。由于可见性信息并不存储在索引中,通常仍需访问堆表进行判断。
为减少堆表访问带来的性能开销,Apache Cloudberry 提供了可见性映射(visibility map)机制:
- 当某个堆表数据页内的所有元组对所有事务均可见时,该数据页会被标记为“全可见”。
- 查询执行时,会首先查阅可见性映射。
- 若数据页为“全可见”,则跳过堆表访问,直接返回结果。
- 否则仍需访问堆表确认可见性。
可见性映射占用空间较小,通常可以完全缓存在内存中,大大降低了查询中的随机 I/O 成本。因此,仅当堆表中大多数页面为“全可见”时,仅索引扫描才能真正带来性能提升。
覆盖索引的作用
为了更好地支持仅索引扫描,可以显式创建覆盖索引(covering index),即将查询所涉及的所有列都包含在索引中。Apache Cloudberry 支持使用 INCLUDE
子句将非过滤条件列一并添加进索引中:
示例:
-- 传统索引,仅加速 WHERE 子句,不支持仅索引扫描
CREATE INDEX tab_x ON tab(x);
-- 覆盖索引,包含返回列 y,支持仅索引扫描
CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);
-- 查询示例
SELECT y FROM tab WHERE x = 'key';
通过 INCLUDE
添加的列不会参与索引匹配,仅用于覆盖查询返回字段,因此:
- 不需要支持索引操作;
- 在唯一索引中不参与唯一性判断。