产品指南
表设计
索引设计

索引设计

为什么需要索引

假设我们有一张表：

CREATE TABLE test1 (
    id integer,
    content varchar
);

应用程序发出许多如下模式的查询：

SELECT content FROM test1 WHERE id = constant;

如果没有提前准备，系统将不得不逐行扫描整个 test1 表，以找到所有匹配的条目。如果 test1 中有许多行，但只有很少的行（也许是零或一个）会被这样的查询返回，这显然是一种低效的方法。但如果已经在 id 列上创建了索引，它可以使用一种更高效的方法来查找匹配的行。例如，它可能只需要消耗几次 IO 即可定位到匹配的行。

可以使用以下命令在 id 列上创建索引，如下所述：

CREATE INDEX test1_id_index ON test1 (id);

索引类型

目前提供了多种索引类型：B-Tree、GIN 和 SPLIT_BITMAP，索引命名需要保证Schema下唯一。每种索引类型使用不同的算法，适合不同类型的查询。默认情况下，CREATE INDEX 命令创建 B-Tree 索引。可以通过在关键字 USING 后面指定索引类型名称来选择需要的索引类型。例如，要创建 SPLIT_BITMAP 索引：

CREATE TABLE hybrid_t
(
    a int PRIMARY KEY,
    b int
) USING hybrid;
 
CREATE INDEX hybrid_t_b_bitmap ON hybrid_t USING split_bitmap (b);

B-Tree

B-树可以处理可排序数据的等式查询和范围查询。下面两个创建索引的语句等效，

CREATE INDEX hybrid_t_b_btree ON hybrid_t (b);
CREATE INDEX hybrid_t_b_btree ON hybrid_t USING btree (b);

当索引列涉及到使用以下运算符之一进行比较时， ProtonBase 查询优化器就会考虑使用 B-tree 索引：

<   <=   =   >=   >

Bitmap

列存表支持位图索引。位图索引是数据库中用于提高查询性能的数据结构，特别适用于具有低基数的列，其中存在有限数量的不同值。

CREATE INDEX hybrid_t_b_bitmap ON hybrid_t USING split_bitmap (b);

从 V1.0 版本开始，引擎会自动为文本类型（TEXT/CHAR/VARCHAR/BPCHAR)，日期类型（DATE），枚举类型（ENUM）字段创建自适应的 Bitmap 索引，不再需要手动创建位图索引。引擎会根据数据的特征决策如何创建实际的索引文件，用户可以通过修改adaptive参数调整自适应行为。

CREATE INDEX hybrid_t_b_adp_bitmap ON hybrid_t USING split_bitmap (b) with (adaptive=true);

GIN

GIN（Generalized Inverted Index），通用倒排索引，适用于包含多个组合值的数据的“倒排索引”。

CREATE TABLE products (
    id serial PRIMARY KEY,
    name text,
    tags text[]
);
 
INSERT INTO products (name, tags) VALUES
    ('Product A', ARRAY['electronics', 'gadgets']),
    ('Product B', ARRAY['clothing', 'accessories']),
    ('Product C', ARRAY['electronics', 'accessories']),
    ('Product D', ARRAY['furniture']);
 
CREATE INDEX products_tags_gin ON products USING gin (tags);

Protonbase 支持使用以下运算符的索引查询：

<@   @>   =   &&

查询带 'furniture' 标签的产品：

SELECT count(*) FROM products WHERE tags @> '{"furniture"}';

多列索引

可以在表的多个列上定义一个索引（无论是 B-Tree 还是位图索引）。例如，如果您有一个如下形式的表格：

CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

并且经常发出如下查询：

SELECT name FROM test2 WHERE major = constant AND minor = constant;

那么在 major 和 minor 列上一起定义索引可能是合适的，例如：

CREATE INDEX test2_mm_idx ON test2 (major, minor);

您应该注意，B-Tree 索引中列的顺序显著影响底层索引查找效率。只有在主导（最左侧）列上有约束时才有效。然而，位图索引中列的顺序没有影响。注：对于位图索引，定义一个组合索引和在各个列上分开定义效果是一样的，只是写法上的区别。

并发构建索引

创建索引可能会干扰数据库的正常操作。通常情况下，ProtonBase 会锁定要对其进行索引的表，既针对读取也针对写入，并通过对表的单次扫描来执行整个索引构建。如果系统是一个实时生产数据库，这可能会产生严重的影响。

ProtonBase 支持在不阻塞写入的情况下构建索引。通过指定 CREATE INDEX 的 CONCURRENTLY 选项来调用此方法。

CREATE INDEX CONCURRENTLY test2_mm_idx ON test2 (major, minor);

最佳实践

  1. 选择正确的列：
  • 为经常出现在 WHERE 子句、JOIN 条件以及 ORDER BY 或 GROUP BY 子句中的列创建索引。
  • 考虑为具有高基数（许多不同值）的列创建 B-Tree 索引以进行过滤以及用于排序。
  • 考虑为具有低基数（许多相同值）的列创建 bitmap 索引以进行过滤。
  1. 避免过度创建索引：
  • 不要在很少或从不在查询中使用的列上创建索引。
  • 只为需要提高查询性能的场景创建索引。
  • 避免重复索引，如下所示：
CREATE INDEX ON t(a);
 
-- 这个组合索引已经包含上面的索引
CREATE INDEX ON t(a, b);
  1. 注意索引大小：
  • 请注意索引会占用存储空间。大型索引可能会影响磁盘 I/O 和写入性能。
  • 在索引的好处与数据库的空间限制之间取得平衡。

示例

创建一个 B-tree 索引

CREATE INDEX t_b_idx ON t(b);

创建一个 split_bitmap 索引

注意: 不能在行存表上创建 split_bitmap 索引。

CREATE INDEX t_b_bitmap ON t using split_bitmap(b);

创建索引时不阻塞读写

CREATE INDEX CONCURRENTLY t_b_idx ON t(b);

检查索引是否在查询中被使用

EXPLAIN SELECT a FROM t WHERE b = 5;
表结构设计表结构修改