索引设计

为什么需要索引

假设我们有一张表：

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 INDEX idx_content_bitmap ON test1 USING split_bitmap (content);

B-tree

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

CREATE INDEX idx_b ON tbl (b);
CREATE INDEX idx_b ON tbl USING btree (b);

假设你有一张包含用户交易记录的表 transactions，结构如下：

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    user_id INT,
    amount DECIMAL,
    transaction_date DATE
);

如果你想按日期查询交易，比如 WHERE transaction_date = '2023-04-01'，使用 B-Tree 索引会非常高效。

CREATE INDEX idx_transaction_date ON transactions (transaction_date);

• 适用场景: B-Tree 索引适合范围查询、等值查询及排序语句等场景，比如 =, <, >, BETWEEN 的操作。常用于数值、字符串或日期类型的字段。

Bitmap

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

CREATE INDEX idx_b_bitmap ON tbl USING split_bitmap (b);

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

CREATE INDEX idx_b_bitmap ON tbl USING split_bitmap (b) with (adaptive=true);

在数据仓库场景中，你有一张 sales 表，包含多列为分类字段，例如 region 和 product_category：

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    region TEXT,
    product_category TEXT,
    sales_amount DECIMAL
);

当查询 WHERE region = 'North America' AND product_category = 'Electronics' 时，创建多个 Bitmap 索引可以加速这类组合条件的查询：

CREATE INDEX idx_region ON sales USING split_bitmap (region);
CREATE INDEX idx_product_category ON sales USING split_bitmap (product_category);

• 适用场景: Bitmap 索引适合低基数（distinct 值较少）的字段，特别是在数据分析场景中，用于像 AND 或 OR 的多条件查询。

GIN

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

假设有一张记录产品标签的表 products，每个产品可能关联多项标签：

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);

• 适用场景: GIN 索引适用于数组（如 TEXT[]）、JSONB 数据类型或全文搜索场景。常见的运算符包括 @>, && 及全文索引中的 to_tsvector()。

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

<@   @>   =   &&

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

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

多列索引

可以在表的多个列上定义一个索引（无论是 B-tree 还是位图索引）。

示例

如果我们有一张表 users，其包含字段：id、firstname 和 lastname。假设在这张表上，可创建多列（复合）索引：

CREATE INDEX idx_fullname ON users (firstname, lastname);

此索引基于 firstname 和 lastname 两个字段的组合加速查询。

示例查询场景：

SELECT * FROM users WHERE firstname = 'John' AND lastname = 'Doe';

此查询能够完全利用我们设置的多列索引 idx_fullname，因而提升性能。

使用场景

• 当一个 查询过滤 涉及两列或更多列时（例如上文示例）。
• 适合用在多列的联合查询（AND 逻辑连接）行为频繁的情况，比如用户组合搜索条件的 Web 应用。
• 适合 高选择性字段（Selectivity），即字段组合后能够筛选出较小范围的数据。

具体场景示例：

• 电商平台按多个字段查询商品（如分类 + 价格区间）时能较好加速查询。
• 某社交平台同时根据用户的 姓 和 名 排查重复记录时使用同样合适。

注意事项

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

2. 组合过多字段的索引要谨慎 如果创建了很多字段的大型索引，会增加索引表的体积，造成记录插入和更新变慢，索引管理成本增高。因此建议严格控制复合索引长度，避免异常字段组合。

3. 谨防覆盖索引与单列索引冲突 在设计多列索引时，要配合实际使用判断是否仍需要额外的单列索引。例如，假设多列索引无任何单列键可覆盖，可能导致单一列使用被浪费。

部分索引（Partial Index）

部分索引(Partial Index)，也称为条件索引或过滤索引，是 ProtonBase 中的一种高级索引技术，它只为表中满足特定条件的行创建索引，而不是为所有行建立索引。

Partial Index 的特点是：

• 选择性索引：只包含符合指定条件的行，避免为所有行建索引，特别是很多行很少被查询
• 条件表达式：通过WHERE子句定义哪些行需要被索引，避免每次数据修改都需要更新索引，即使这些数据不在查询条件内
• 空间效率：比完整索引占用更少存储空间，节省30-90%存储空间
• 提高查询性能：更小的索引带来更快的搜索速度
• 解决特殊场景问题：如稀疏数据、高频查询特定子集

以下是创建 Partial Index 的基本语法：

CREATE INDEX index_name
ON table_name (column_name [, ...])
WHERE condition;

示例

假设有一个名为 orders 的表，其中存储了所有的订单数据。我们经常查询那些状态为 completed 的订单，但很少涉及其他状态的数据。可以像下面这样创建 Partial Index：

CREATE INDEX idx_completed_orders
ON orders (order_date, customer_id)
WHERE status = 'completed';

当查询符合索引条件时，数据库会利用这个部分索引，从而加速查询操作：

SELECT customer_id, order_date
FROM orders
WHERE status = 'completed'
  AND order_date > '2023-01-01';

此查询将只使用部分索引中的记录，而数据量显著小于整表扫描，因此性能优异。

而如果查询条件不符合索引约束（即 WHERE status != 'completed'），数据库会自动回退为其他显式的索引或采用全表扫描方式。

使用场景

稀疏数据：某列大量NULL或默认值时

CREATE INDEX idx_non_null_phone ON customers(phone)
WHERE phone IS NOT NULL;

业务分区：只查询特定状态的数据

CREATE INDEX idx_unpaid_orders ON orders(total)
WHERE payment_status = 'unpaid';

时间范围：只索引近期数据

CREATE INDEX idx_recent_logs ON logs(timestamp)
WHERE timestamp > '2023-01-01';

排除重复值：只为唯一值建索引

CREATE INDEX idx_unique_emails ON users(email)
WHERE email IN (SELECT email FROM users GROUP BY email HAVING COUNT(*) = 1);

注意事项

1. 查询必须匹配索引条件： 数据库优化器只能使用符合 Partial Index 条件的 SQL 查询语句，否则将无法利用该索引。
2. 运维复杂度略有提升： 部分索引的范围和条件设计需要根据业务场景精准规划，否则可能因范围不当影响预期效果。
3. 条件选择性：条件应该过滤掉足够多的数据（通常>90%）
4. 多列索引：可以组合条件过滤和多列索引

并发构建索引

创建索引可能会干扰数据库的正常操作。通常情况下，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 索引以进行过滤。

2. 避免过度创建索引：

• 不要在很少或从不在查询中使用的列上创建索引，过多的 B-tree 索引由于引起写放大，会降低写入和更新的吞吐。
• 只为需要提高查询性能的场景创建索引。
• 避免重复索引，如下所示：

CREATE INDEX ON t(a);
 
-- 这个组合索引已经包含上面的索引
CREATE INDEX ON t(a, b);

3. 注意索引大小：

• 请注意索引会占用存储空间。大型索引可能会影响磁盘 I/O 和写入性能。
• 在索引的好处与数据库的空间限制之间取得平衡。