表结构设计指南
在现代数据库系统中,良好的表结构设计至关重要,能够显著提升查询性能并优化存储效率。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
上进行等值查询,若包含b
和c
的范围查询,索引效率将会显著降低。
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 = ?
时,不会走索引