表结构设计指南

表结构设计指南

在现代数据库系统中,良好的表结构设计至关重要,能够显著提升查询性能并优化存储效率。ProtonBase 是一个高性能、灵活的数据库平台,支持多种存储模式和索引类型。本文将深入探讨 ProtonBase 的表结构设计,包括表存储类型的选择和索引设计等关键方面,帮助您充分发挥 ProtonBase 的优势。

一、表的存储类型选择

ProtonBase 支持三种主要的表存储类型:行存(Row)列存(Columnar)行列混存(Hybrid)。每种存储类型有其特定的适用场景和优缺点,选择合适的存储模式能够大幅提升性能。

1.1 行存(Row)

行存 是 ProtonBase 的默认存储模式。在行存模式下,数据按行存储,每一行的数据都存储在一起。行存非常适合 OLTP(在线事务处理)型应用场景,其中需要频繁进行插入、更新和基于整行数据的查询。它能够提供较高的写入性能和较低的查询延迟。

适用场景:

  • 数据插入和更新频繁的场景。

  • 需要基于单行数据进行查询的应用。

  • 传统的事务型应用,例如电商订单管理、银行系统等。

示例:创建行存表

CREATE TABLE row_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
);

1.2 列存(Columnar)

列存 将数据按列而不是按行存储。在列存模式下,每列的数据都存储在一起。这种存储模式对分析型查询非常高效,因为查询通常会集中在某些特定的列,而不需要访问整个表的所有行。列存能够显著减少 I/O 操作,尤其是在大数据量的查询中。

适用场景:

  • 数据查询较为复杂,涉及大量数据的统计、分析和聚合操作。

  • 需要读取大量数据中的部分列进行计算,且列间的数据变化较小。

  • OLAP(在线分析处理)型应用,如数据仓库和日志分析。

示例:创建列存表

CREATE TABLE columnar_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
) USING columnar;

1.3 行列混存(Hybrid)

行列混存 是 ProtonBase 中的特殊存储模式,结合了行存和列存的优点,适合一些特定的混合工作负载(HTAP:混合事务分析处理)。在这种模式下,表的某些列会以行存的方式存储,而其他列则以列存的方式存储。这种混合模式适用于需要既支持 OLTP 又支持 OLAP 的场景。

适用场景:

  • 需要同时支持事务处理和大规模数据分析的混合型应用。

  • 在同一表中既有频繁更新的数据,也有需要高效查询的分析型数据。

示例:创建行列混存表

CREATE TABLE hybrid_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
) USING hybrid;

存储模式选择总结

  • 行存:适用于需要频繁进行插入、更新和基于整行数据的查询场景,如 OLTP 系统。

  • 列存:适用于大数据量的查询和分析场景,如 OLAP 系统,数据仓库等。

  • 行列混存:适用于需要同时支持 OLTP 和 OLAP 的应用,通常出现在 HTAP 场景中。

存储模式的转换

ProtonBase 支持 Schema Evolution(模式演进),可以在线的修改表的存储格式,在修改期间不会锁表,对于表的读写操作不会受影响。

修改示例:

-- 改为行存
alter table xx set access method row;
 
-- 改为列存
alter table xx set access method columnar;
 
-- 改为行列混存
alter table xx set access method hybrid;

二、索引设计

索引是提高查询性能的关键工具。在 ProtonBase 中,有多种索引类型,可以根据数据的特点和查询需求选择最合适的索引。

2.1 主键索引

主键索引是最常见的索引类型,通常用于确保数据的唯一性和完整性。每张表都应该有一个主键(Primary Key),并且通常会在主键列上自动创建索引。主键索引特别适合于根据主键快速定位某一行数据。

注意事项:

  • 主键必须具有唯一性,且不可为空。

  • 对于多列主键,索引的顺序非常重要。查询优化器会优先使用最左边的列进行匹配,所以列的顺序应根据查询的条件顺序来决定。

示例:创建主键索引

CREATE TABLE user_table (
    user_id int,
    username varchar(50),
    email varchar(100),
    PRIMARY KEY (user_id)
);

联合主键设计

  • 如果主键是多个列组成的联合主键,查询时应该遵循 最左匹配原则,即查询条件中必须包含联合主键的最左列。例如,如果主键是 (a, b, c),查询时应该优先在 a 上进行等值查询,若包含 bc 的范围查询,索引效率将会显著降低。

2.2 全局二级索引(Global Secondary Index,GSI)

全局二级索引适用于需要在非主键列上进行高效查询的场景。与主键索引不同,二级索引并不要求数据唯一,但它可以提高特定列的查询效率。

适用场景:

  • 查询频繁使用非主键列作为过滤条件的情况。

  • 需要通过某些非主键列进行排序、分组或者连接的情况。

示例:创建二级索引

CREATE INDEX idx_username ON user_table (username);

2.3 Bitmap 索引

位图索引适用于列中值的种类比较少(低基数)的情况,例如性别、状态等。这类数据的值可能是男、女 或其他有限的几种状态,使用 Bitmap 索引能够极大提高查询效率。

注意:ProtonBase内部会按照列内数据的分布进行bitmap索引的自动构建,因此绝大多数情况不需要手动创建bitmap索引。

适用场景:

  • 列的数据值种类较少,通常为有限的离散值(如布尔值、性别、分类等)。

  • 进行条件过滤查询时,性能要求较高。

示例:创建 Bitmap 索引

CREATE INDEX idx_gender_bitmap ON user_table USING split_bitmap (gender);

2.4 GIN 索引(倒排索引)

GIN(Generalized Inverted Index,通用倒排索引)适用于数组、JSON 或其他复杂类型的数据。这种索引通过将数据中的每个元素作为独立的索引项来加速查询,适合用来处理多值列或文本搜索。

适用场景:

  • 数据类型是数组或 JSON 等具有多值的列。

  • 需要支持快速的包含查询(如 @> 运算符)或全文搜索等。

示例:创建 GIN 索引

CREATE INDEX idx_username_gin ON user_table USING gin (username);

2.5 向量索引

向量索引通常用于处理高维数据或需要进行相似度搜索的应用,例如图像处理、自然语言处理(NLP)等场景。这类索引允许高效地查找相似度高的记录(例如最相似的文本或图像)。

有关向量索引的使用,参考文档向量检索

适用场景:

  • 高维数据,如机器学习模型生成的向量。

  • 相似度搜索,例如基于文本、图像或声音的检索。

索引选择总结

  • 主键索引:用于唯一标识一行数据,通常自动创建。

  • 全局二级索引:适用于频繁查询的非主键列。

  • Bitmap 索引:适用于低基数列(例如性别、状态等)。

  • GIN 索引:适用于处理多值列(如数组、JSON 等)或全文搜索。

  • 向量索引:适用于高维数据的相似度搜索。

三、主键设计注意事项

3.1 选择合适的主键列

  • 唯一性:主键列必须是唯一的,确保每一行数据都能通过主键唯一标识。

  • 稳定性:避免选择会频繁更新的列作为主键。例如,避免使用自增的 ID 字段作为主键,除非它是唯一且不会频繁变化。

3.2 联合主键的顺序选择

对于联合主键,需要遵循 最左匹配原则。查询优化器会优先利用联合主键中的最左列来限制数据,因此联合主键列的顺序应根据查询条件的使用频率来决定。

例如,对于主键 (a, b, c)

  • 查询 WHERE a = ? AND b = ? 时,索引会首先通过 a 列筛选。

  • 查询 WHERE b = ? 时,不会走索引