OceanBase 数据表设计与索引优化最佳实践指南 - OceanBase 最佳实践

合理的数据表设计不仅能确保数据的完整性和一致性，还能大幅提升查询性能、存储效率以及系统的可扩展性。

本文将结合选择表的存储格式、表结构设计、主键设计，在 HTAP 场景下如何使用索引等方面，向您介绍数据表设计的最佳实践。

核心价值与适用场景

场景	优化方案	效果
海量数据 ETL 分析	无主键列存表 + 时间 Range 分区	提升批量导入性能，减少管理开销
HTAP 混合负载	行列混存存储 + 主键包含分区键	事务与分析双优，强一致性保证
JSON 多值查询	JSON 多值索引	加速 `JSON_CONTAINS` 等操作，提升过滤效率
文本模糊检索	全文索引	快速定位文本内容，避免全表扫描
多表 Join 性能优化	表组管理（数据同分布）	减少跨节点数据迁移，提升复杂查询效率
时间序列数据处理	Hash 分区（用户 ID） + 二级 Range 分区（时间字段）	分区裁剪加速时间范围查询，数据分布均匀

存储格式选择策略

行存与列存特性对比

存储类型	适用场景	性能特征	典型应用场景
纯列存	OLAP 分析型业务	高压缩比/列式扫描优化	数据仓库/复杂聚合查询
行列混存	HTAP 混合负载场景	事务与分析双优	实时报表/交易分析一体化系统
纯行存	OLTP 事务型业务	低延迟点查/高并发写入	订单系统/用户账户管理

行列存储配置方法

-- 查看当前存储格式配置
SHOW PARAMETERS LIKE '%store_format%';

-- 设置租户级默认存储格式
ALTER SYSTEM SET default_table_store_format = "column";  -- 列存模式
ALTER SYSTEM SET default_table_store_format = "compound"; -- 行列混存模式

表结构设计规范

索引组织表与堆组织表设计

索引组织表与堆组织表对比

特性	索引组织表（INDEX）	堆组织表（HEAP）
数据存储方式	数据按主键顺序存储，主键和数据绑定。	数据随机存储，索引与数据解耦。
主键索引类型	聚簇索引（主键直接关联数据位置）。	非聚簇索引（主键为二级索引）。
写入性能	较低（需维护排序顺序）。	更高（无需维护数据顺序）。
适用场景	适合频繁按主键访问的场景。	适合高频导入、复杂查询分析。

索引组织表（Index-Organized Table）

特点：

主键和数据绑定：数据按主键存储和排序，主键为聚簇索引（Clustered Index），无额外空间开销。
高效访问：主键直接关联数据存储位置，查询时通过主键定位数据更高效。
写入性能：插入数据需按主键顺序存储，可能影响写入速度（尤其在高并发场景）。

示例表结构：

CREATE TABLE customer (
user_id bigint NOT NULL,
login_time timestamp NOT NULL,
customer_name varchar(100) NOT NULL,    
phone_num bigint NOT NULL,
city_name varchar(50) NOT NULL,
sex int NOT NULL,
id_number varchar(18) NOT NULL,
home_address varchar(255) NOT NULL,
office_address varchar(255) NOT NULL,  
age int NOT NULL  
);

说明

此示例默认为索引组织表。OceanBase 数据库提供配置项 default_table_organization 控制默认创建表的表组织模式。设置配置项的值为 HEAP，表示表组织形式为堆组织表；设置配置项的值为 INDEX，表示表组织形式为索引组织表。详细信息，参考default_table_organization。

堆组织表（Heap-Organized Table）

OceanBase V4.3.5 BP1 及以上版本支持堆组织表。

特点：

数据无序存储：插入数据不依赖主键排序。
主键为二级索引：主键实际为非聚簇索引（Non-Clustered Index），数据与索引解耦。
写入性能优势：因无需维护数据顺序，写入速度更快。

示例表结构：

CREATE TABLE customer_heap (
    user_id BIGINT NOT NULL PRIMARY KEY,
    login_time TIMESTAMP NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    ...
) ORGANIZATION = HEAP;

分区设计策略

分区场景：

数据量过大：当数据表的记录数达到亿级甚至更高时，单表操作会导致查询性能下降，甚至引发数据库的资源瓶颈。
查询频繁依赖某些字段：例如，时间字段是常用的查询条件之一，按时间分区能够提高时间相关查询的效率。

分区类型选择策略：

分区策略	适用场景	数据分布特征	典型配置示例
HASH	分布式均匀存储	高基数字段（用户ID等）	`PARTITION BY HASH(user_id)`
RANGE	时间序列或数值范围数据	日期/数值范围	MySQL 模式：`PARTITION BY RANGE [COLUMNS] (create_date)` Oracle 模式：`PARTITION BY RANGE(create_date)`
LIST	离散值分类存储	地区/状态码等	`PARTITION BY LIST(region_code)`
KEY	多列组合键或非整型字段的均匀存储	通过对分区键应用 Hash 算法后得到的整型值进行取模操作	`PARTITION BY KEY(region, create_date)`

注意

OceanBase 数据库 KEY 分区仅 MySQL 模式支持。

在 AP 场景里，通常涉及多个维度的分析查询，如果没有一个维度可以将数据进行分区且适用于各查询，而又需要分区将数打散分布到多台机器利用分布式节点计算的能力，此时可按如下方式选择分区键做 Hash 分区，尽量将用户数据均匀打散：

NDV（即 number of distinct values，表示某一列中不同值的数量）远大于分区数。
此列数据没有倾斜，或只有少量倾斜，例如：交易 ID、用户 ID 或者自增列等。
优先选择整形列，时间列，再考虑 varchar/char。
优先选择高频率出现查询条件的字段，方便分区裁剪。

选择 HASH 分区示例如下：

CREATE TABLE customer (
    user_id BIGINT NOT NULL,
    login_time TIMESTAMP NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    phone_num BIGINT NOT NULL,
    city_name VARCHAR(50) NOT NULL,
    sex INT NOT NULL,
    id_number VARCHAR(18) NOT NULL,
    home_address VARCHAR(255) NOT NULL,
    office_address VARCHAR(255) NOT NULL,
    age INT NOT NULL
)
PARTITION BY HASH(user_id) PARTITIONS 128;

主键设计

加主键的场景：

保证数据唯一性：当表中的数据需要确保每条记录的唯一性时，可以设计主键。
提高查询效率：主键能够帮助优化器生成更高效的查询计划，尤其是当数据查询依赖于主键时，建议将查询一定会携带的字段作为主键字段，这样可以利用主键来提升查询性能。

示例如下所示：

CREATE TABLE customer (
    user_id BIGINT NOT NULL,
    login_time TIMESTAMP NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    phone_num BIGINT NOT NULL,
    city_name VARCHAR(50) NOT NULL,
    sex INT NOT NULL,
    id_number VARCHAR(18) NOT NULL,
    home_address VARCHAR(255) NOT NULL,
    office_address VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (user_id, age, login_time)
)
PARTITION BY HASH(user_id)
PARTITIONS 128
SUBPARTITION BY RANGE(age)
SUBPARTITION TEMPLATE (
    SUBPARTITION p_youth VALUES LESS THAN (25),
    SUBPARTITION p_adult VALUES LESS THAN (40),
    SUBPARTITION p_middle_aged VALUES LESS THAN (60),
    SUBPARTITION p_senior VALUES LESS THAN (MAXVALUE)
);

HTAP 混合负载优化方案

存储架构选型

架构类型	适用场景	性能特征	资源消耗
行存基表+列存索引	TP 为主，AP 轻量查询	写性能较高，AP 查询有限优化	索引存储增加 30%-50%
列存基表+行存索引	AP 为主，TP 点查需求	分析性能最优，TP 查询需索引辅助	全量数据双存储
行列混存存储	TP/AP 负载均衡	强一致性，自动路由	存储开销增加 100%
列存副本 2F1A1C	AP 独立分析	读写隔离，最终一致性	额外副本存储

特殊索引优化实践

JSON 多值索引

适用场景：

JSON多值索引是专门为JSON文档中数组字段设计的索引类型，适用于需要对多个值或属性进行查询的场景。典型应用包括：

多对多关联查询：

当两个实体之间存在多对多关系时，多值索引可以加速查询。例如，演员可以出演多部影片，而一部影片可能由多个演员参与。可以使用JSON数组保存影片涉及的所有演员，并利用 JSON 多值索引优化查询特定演员出演的影片。
标签和分类查询：

当实体具有多个标签或分类时，可以使用多值索引加速查询。例如，一个商品可能包含多个标签属性，通过 JSON 数组存储商品的多个标签，可以快速查询包含某个或多个标签的商品。

适用条件：JSON 多值索引则常用于加速基于 JSON 数组且 WHERE 条件中带有以下三种谓词的查询：

MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()

更多关于 JSON 多值索引的详细信息，参见多值索引。

实践案例：

场景描述：假设我们有一张用户信息表，记录用户ID、姓名、年龄和爱好，其中爱好以JSON数组形式保存。一个用户可能会有多个爱好，这些爱好可以理解为用户的标签。用于存储用户信息的表结构即数据如下：

create table user_info(user_id bigint, name varchar(1024), age bigint, hobbies json);
insert into user_info values(1, "LiLei", 18, '["reading", "knitting", "hiking"]');
insert into user_info values(2, "HanMeimei", 17, '["reading", "Painting", "Swimming"]');
insert into user_info values(3, "XiaoMing", 19, '["hiking", "Camping", "Swimming"]');

在商品广告投放中，需要根据用户的爱好进行精准定位。例如，在投放登山户外设备广告之前，需要查询哪些用户有“hiking”的爱好。对应的查询语句如下：

select user_id, name from user_info where JSON_CONTAINS(hobbies->'$[*]', CAST('["hiking"]' AS JSON));

查询返回结果如下所示：

+---------+----------+
| user_id | name     |
+---------+----------+
|       1 | LiLei    |
|       3 | XiaoMing |
+---------+----------+

现在我们执行以下语句，检查初次查询的效率：

-- 初次执行该查询时，可能需要全表扫描，效率较低
Explain select user_id, name from user_info where JSON_CONTAINS(hobbies->'$[*]', CAST('["hiking"]' AS JSON));

返回结果如下所示：

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================================                                                                                                                    |
| |ID|OPERATOR       |NAME     |EST.ROWS|EST.TIME(us)|                                                                                                                    |
| ----------------------------------------------------                                                                                                                    |
| |0 |TABLE FULL SCAN|user_info|2       |3           |                                                                                                                    |
| ====================================================                                                                                                                    |
| Outputs & filters:                                                                                                                                                      |
| -------------------------------------                                                                                                                                   |
|   0 - output([user_info.user_id], [user_info.name]), filter([JSON_CONTAINS(JSON_EXTRACT(user_info.hobbies, '$[*]'), cast('[\"hiking\"]', JSON(536870911)))]), rowset=16 |
|       access([user_info.hobbies], [user_info.user_id], [user_info.name]), partitions(p0)                                                                                |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                       |
|       range_key([user_info.__pk_increment]), range(MIN ; MAX)always true                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set

从上面的查询计划可以看出，整个查询需要对全表进行扫描（也就是 TABLE FULL SCAN），且逐行对 JSON 数组进行过滤比对。JSON 本身的过滤开销也不小，当需要过滤的记录行数达到一定数量的时候，会严重影响查询的效率。此时，通过在 hobbies 列上创建 JSON 多值索引，可显著提升该查询的效率。

当前 JSON 多值索引的后建功能默认关闭，需要在 sys 租户下打开后建JSON多值索引的开关。

Alter system set _enable_add_fulltext_index_to_existing_table = true;

-- 可以看到，查询计划显示需要对整个表进行扫描。为优化性能，建议在 hobbies 列上创建 JSON 多值索引：
CREATE TABLE user_info (
    user_id BIGINT,
    name VARCHAR(1024),
    age BIGINT,
    hobbies JSON,
    INDEX idx1((CAST(hobbies->"$[*]" AS UNSIGNED ARRAY)))
);

再次查询，查看查询效率是否提升：

-- 创建索引后，再次执行查询，性能显著提升
Explain select user_id, name from user_info where JSON_CONTAINS(hobbies->'$[*]', CAST('["hiking"]' AS JSON));

返回结果如下所示：

+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==========================================================                                                                                                   |
| |ID|OPERATOR       |NAME           |EST.ROWS|EST.TIME(us)|                                                                                                   |
| ----------------------------------------------------------                                                                                                   |
| |0 |TABLE FULL SCAN|user_info(idx1)|1       |10          |                                                                                                   |
| ==========================================================                                                                                                   |
| Outputs & filters:                                                                                                                                           |
| -------------------------------------                                                                                                                        |
|   0 - output([user_info.user_id], [user_info.name]), filter([JSON_CONTAINS(JSON_EXTRACT(user_info.hobbies, '$[*]'), cast('[\"hiking\"]', JSON(536870911)))]) |
|       access([user_info.__pk_increment], [user_info.hobbies], [user_info.user_id], [user_info.name]), partitions(p0)                                         |
|       is_index_back=true, is_global_index=false, filter_before_indexback[false],                                                                             |
|       range_key([user_info.SYS_NC_mvi_21], [user_info.__pk_increment], [user_info.__doc_id_1733716274684183]), range(hiking,MIN,MIN ; hiking,MAX,MAX)        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set

需要注意的是，JSON 多值索引会占用额外存储空间，并可能对写入性能造成影响，当对包含多值索引的 JSON 字段进行修改（如插入、更新、删除操作）时，索引也会随之更新，写入的开销会变大。因此在使用过程中需要权衡JSON 多值索引的优缺点，按需创建。

全文索引

适用场景：

在涉及大量文本数据需要进行模糊检索的场景，如果通过全表扫描来对每一行数据进行模糊查询，文本较大、数据量较多的情况下性能往往不能满足要求。另外一些复杂的查询场景，如近似匹配、相关性排序等，也难以通过改写 SQL 支撑。

为了更好的支撑这些场景，全文索引应运而生，它通过预先处理文本内容，建立关键词索引，有效提升全文搜索效率。全文索引适用于多种场景，下面列举几个具体的案例：

企业内部知识库：许多大型企业都会构建自己的内部知识库系统，用来存储项目文档、会议记录、研究报告等资料。使用全文索引可以帮助员工更加快速准确地找到所需信息，提高工作效率。
在线图书馆与电子书平台：对于提供大量书籍资源供用户阅读的服务来说，全文索引是极其重要的。用户可以输入书名、作者名字甚至是书中某段文字作为关键字来进行搜索，系统基于全文索引迅速定位到符合条件的结果。
新闻门户和社交媒体网站：这类平台上每天都会产生海量的新鲜内容，包括文章、帖子、评论等。利用全文索引可以让用户按照自己关心的话题、事件或是人物名称来过滤信息流，获取最相关的内容。
法律文书检索系统：法律行业涉及到大量的文件审阅工作，如合同、判决书、法律法规条文等。一个高效的全文搜索引擎能够极大地简化律师的工作流程，让他们能够更快地找到先例、引用条款以及相关的法律依据。
医疗健康信息系统：在医疗领域，医生经常需要查阅病人的历史病例、最新的医学研究论文以及其他参考资料。借助全文索引，医护人员可以更加便捷地访问相关信息，从而做出更为准确的诊断决策。

任何涉及到大量非结构化文本数据管理和查询的应用都可以考虑采用全文索引来提升检索效率。更多关于 OceanBase 全文搜索能力的详细信息，参见全文索引。

实践示例：

场景描述：我们定义一个表以保存文档资料，并为文档设置全文索引。利用全文索引可快速匹配包含期望关键字的文档，并按相似性从高到低排序。

CREATE TABLE Articles (
  id INT AUTO_INCREMENT,
  title VARCHAR(255) ,
  content TEXT ,
  PRIMARY KEY (id),
  FULLTEXT ft1 (content) WITH PARSER SPACE
  );

 INSERT INTO Articles (title, content) VALUES
   ('Introduction to OceanBase', 'OceanBase is an open-source relational database management system.'),
   ('Full-Text Search in Databases', 'Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly.'),
   ('Advantages of Using OceanBase', 'OceanBase offers several advantages such as high performance, reliability, and ease of use. ');

select * from Articles;

查询返回结果如下所示：

+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | title                         | content                                                                                                                                                      |
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Introduction to OceanBase     | OceanBase is an open-source relational database management system.                                                                                           |
|  2 | Full-Text Search in Databases | Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly. |
|  3 | Advantages of Using OceanBase | OceanBase offers several advantages such as high performance, reliability, and ease of use.                                                                  |
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in SET

再次执行下面的查询语句，查询匹配的文档：

-- 查询匹配的文档
select id,title, content,match(content) against('OceanBase database') score from Articles where match(content) against('OceanBase database');

返回结果如下所示：

+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| id | title                         | content                                                                                                                                                      | score               |
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
|  1 | Introduction to OceanBase     | OceanBase is an open-source relational database management system.                                                                                           |  0.5699481865284975 |
|  3 | Advantages of Using OceanBase | OceanBase offers several advantages such as high performance, reliability, and ease of use.                                                                  |   0.240174672489083 |
|  2 | Full-Text Search in Databases | Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly. | 0.20072992700729927 |
+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
3 rows in set

利用下面的 EXPLAIN 命令，您可以查看查询计划并分析其性能：

Explain select id,title, content,match(content) against('OceanBase database') score from Articles where match(content) against('OceanBase database');

执行结果如下所示：

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| ==============================================================                                                                                      |
| |ID|OPERATOR             |NAME         |EST.ROWS|EST.TIME(us)|                                                                                      |
| --------------------------------------------------------------                                                                                      |
| |0 |SORT                 |             |17      |145         |                                                                                      |
| |1 |└─TEXT RETRIEVAL SCAN|articles(ft1)|17      |138         |                                                                                      |
| ==============================================================                                                                                      |
| Outputs & filters:                                                                                                                                  |
| -------------------------------------                                                                                                               |
|   0 - output([articles.id], [articles.title], [articles.content], [MATCH(articles.content) AGAINST('OceanBase database')]), filter(nil), rowset=256 |
|       sort_keys([MATCH(articles.content) AGAINST('OceanBase database'), DESC])                                                                      |
|   1 - output([articles.id], [articles.content], [articles.title], [MATCH(articles.content) AGAINST('OceanBase database')]), filter(nil), rowset=256 |
|       access([articles.id], [articles.content], [articles.title]), partitions(p0)                                                                   |
|       is_index_back=true, is_global_index=false,                                                                                                    |
|       calc_relevance=true, match_expr(MATCH(articles.content) AGAINST('OceanBase database')),                                                       |
|       pushdown_match_filter(MATCH(articles.content) AGAINST('OceanBase database'))                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set

分布式环境优化

表组管理

对于分布式数据库, 多个表由于划分分区使得数据可能会分布在不同的机器上, 这样在执行 Join 查询等复杂操作时就需要涉及跨机器的通信，可以利用表组的能力来避免跨机访问带来的查询性能不优的问题。创建一个 sharding 属性为 ADAPTIVE 的表组 tg1，和两个一级分区表 customer、sales。在两个分区表的连接操作中，若连接条件包含分区键时，可以使用 Partition Wise Join 的方式提升性能。

示例如下：

CREATE TABLEGROUP tg1 SHARDING = 'ADAPTIVE';

CREATE TABLE customer (
    user_id BIGINT NOT NULL,
    login_time TIMESTAMP NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    phone_num BIGINT NOT NULL,
    city_name VARCHAR(50) NOT NULL,
    sex INT NOT NULL,
    id_number VARCHAR(18) NOT NULL,
    home_address VARCHAR(255) NOT NULL,
    office_address VARCHAR(255) NOT NULL,
    age INT NOT NULL)
  TABLEGROUP = tg1
  PARTITION BY HASH(user_id) PARTITIONS 128;

CREATE TABLE sales (
  order_id INT,
  user_id INT primary key,
  item_id INT,
  item_count INT)
  TABLEGROUP = tg1
  PARTITION BY HASH(user_id) PARTITIONS 128;


SELECT * FROM customer, sales where customer.user_id = sales.user_id;

二级分区管理

如果业务数据量很大，且查询特点清晰，可以创建二级分区，来进一步利用分区裁剪的能力加速查询。AP的查询特点通常是查询最近一天或者一个月的数据，通常是带有时间属性的查询，所以二级分区键建议选择时间类型的字段或时间函数，并且选择range分区方便做范围查询。

CREATE TABLE customer (
    user_id BIGINT NOT NULL,
    login_time TIMESTAMP NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    phone_num BIGINT NOT NULL,
    city_name VARCHAR(50) NOT NULL,
    sex INT NOT NULL,
    id_number VARCHAR(18) NOT NULL,
    home_address VARCHAR(255) NOT NULL,
    office_address VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    -- 主键包含所有分区键（user_id 和 age）
    PRIMARY KEY (user_id, age, login_time)
)
-- 主分区：按 user_id 哈希分布
PARTITION BY HASH(user_id)
PARTITIONS 128
SUBPARTITION BY RANGE(age)
SUBPARTITION TEMPLATE (
    -- 示例分区：按年龄段划分
    SUBPARTITION p_youth VALUES LESS THAN (25),
    SUBPARTITION p_adult VALUES LESS THAN (40),
    SUBPARTITION p_middle_aged VALUES LESS THAN (60),
    SUBPARTITION p_senior VALUES LESS THAN (MAXVALUE)
);

注意事项

索引权衡：
- JSON 多值索引和全文索引会增加存储开销，需根据写入频率与查询需求权衡。
分区键选择：
- 主键必须包含所有分区键。
- Hash/Key 分区键需选择高基数、无倾斜、高频查询的字段，如用户 ID、订单号等。
- Range 分区键可选择时间列、数值列按范围划定分区。
- LIST 分区键可选择低基数字段，如省份、公司名称等。
分布式设计：
- 关联表需同表组部署，确保数据同分布，降低 Join 操作的网络开销。
二级分区：
- AP 场景优先按时间或业务维度字段设计二级 Range 分区（如按季度划分），无需避免低基数字段，但需确保分区逻辑与查询模式匹配。