データベースシステムの設計と最適化において、テーブル設計は極めて重要な作業です。適切な設計は、データの完全性と一貫性を確保するだけでなく、クエリパフォーマンス、ストレージ効率、システムの拡張性を大幅に向上させることができます。データ量の増加とクエリパターンの複雑化に伴い、科学的なテーブル設計、適切なストレージ構造とインデックス戦略の選択は、データベース最適化の核心的な課題となっています。
本記事では、テーブルのストレージ形式の選択、主キーなしの単一カラムストアテーブル、パーティション設計、主キー設計、HTAPシナリオにおけるインデックスの活用方法などに焦点を当て、テーブル設計のベストプラクティスを紹介します。
テーブルのストレージ形式の選択
OceanBaseは、テーブルデータの列指向ストレージと行指向・列指向の冗長ストレージをサポートしており、業務シナリオに応じて適切なデータストレージ形式を選択できます。
- 列指向ストレージの適用シナリオ: 業務が単純なOLAP分析業務のみの場合、列指向ストレージを選択します。
- 行指向・列指向混合ストレージの適用シナリオ: 業務がHTAPシナリオであり、OLAP分析業務とOLTPトランザクション業務の両方を含む場合、分析クエリのパフォーマンスを保証しつつ、ポイントルックアップクエリのパフォーマンスも確保するために、行指向・列指向の冗長ストレージを設定できます。
テナントレベルの構成パラメータ default_table_store_format を使用して、テーブルのデフォルトストレージ形式を設定します。
-- テナント内のテーブルのデフォルトストレージ形式を列指向ストレージに設定
ALTER SYSTEM SET default_table_store_format = "column";
-- テナント内のテーブルのデフォルトストレージ形式を行指向・列指向混合ストレージに設定
ALTER SYSTEM SET default_table_store_format = "compound";
主キーなしの単一テーブルの列指向ストレージの選択
データウェアハウスやオンライン分析処理 (OLAP) シナリオでは、業務データテーブルのデータ量は非常に膨大であり、ほとんどのクエリは特定の列のみに関わります。データの一意性の保証、重複書き込みの回避、またはトランザクション更新・挿入操作などの特別な業務要件がない場合、主キーなしの列指向ストレージテーブルを作成する方が効率的です：
- データインポート：データを主キーでソートする必要がないため、大規模データのインポートパフォーマンスは主キーを持つテーブルよりも高速です。
- 主キーの管理コストの削減。
show parameters like "%store_format%";
CREATE TABLE customer (
user_id bigint NOT NULL,
login_time timestamp NOT NULL,
customer_name varchar(100) NOT NULL, -- 顧客名は最大100文字と仮定する
phone_num bigint NOT NULL, -- 電話番号はbigint型で保存する
city_name varchar(50) NOT NULL, -- 都市名は最大50文字と仮定する
sex int NOT NULL, -- 性別はint型で保存する (例：0は女性、1は男性)
id_number varchar(18) NOT NULL, -- 身分証番号は最大18文字と仮定する
home_address varchar(255) NOT NULL, -- 自宅住所は最大255文字と仮定する
office_address varchar(255) NOT NULL, -- 会社住所は最大255文字と仮定する
age int NOT NULL -- 年齢はint型で保存する
);
データテーブルのパーティショニングの選択
パーティショニング技術は、大規模なテーブルを複数のより小さく管理しやすいサブテーブルに分割することで、クエリとデータ管理において大きな利点を提供します。パーティションは通常、時間、地域など、データの特定フィールドに基づいて行われます。
パーティショニングのシナリオ
- データ量が膨大な場合: データテーブルのレコード数が億単位、あるいはそれ以上に達すると、単一テーブルでの操作はクエリパフォーマンスの低下を招き、データベースのリソースボトルネックを引き起こす可能性があります。
- クエリが特定のフィールドに頻繁に依存する場合: 例えば、時間フィールドは一般的なクエリ条件の一つであり、時間によるパーティショニングは時間関連クエリの効率を向上させることができます。
パーティショニング戦略の選択
- RANGEパーティション：時間範囲に基づいてデータを分割するのに適しており、特にデータが時間順に生成され、通常は時間でフィルタリングされる場合に有効です。
- HASHパーティション：ハッシュアルゴリズムを使用してデータを複数のパーティションに均等に分散させる方法で、明確なクエリパターンがないシナリオに適しています。
- LISTパーティション：地域別にパーティション分けするような離散的なデータに適しています。
APシナリオでは、通常、複数の次元にわたる分析クエリが含まれます。データを分割し、かつ各クエリに適用できるような次元が存在しない場合でも、分散ノードの計算能力を活用するためにデータを複数のマシンに分散させるパーティショニングが必要な場合、以下のようにパーティションキーを選択してHASHパーティションを作成し、ユーザーデータを可能な限り均等に分散させることができます：
- NDVがパーティション数よりもはるかに大きいこと。
- この列のデータに偏りがない、またはわずかな偏りしかないこと。例：取引ID、ユーザーID、または自動インクリメント列など。
- 整数列、日時列を優先的に選択し、次にvarchar/charを検討すること。
- クエリ条件として頻繁に現れるフィールドを優先的に選択し、パーティションプルーニングを容易にすること。
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;
分散データベースでは、複数のテーブルがパーティション分割されているため、データが異なるマシンに分散される可能性があります。そのため、JOINクエリなどの複雑な操作を実行する際には、マシン間の通信が必要となります。テーブルグループの機能を利用することで、マシン間アクセスによるクエリ性能の低下を回避できます。
sharding属性をADAPTIVEに設定したテーブルグループtg1と、2つのパーティションテーブルcustomer、salesを作成します。2つのパーティションテーブルを結合する際、結合条件にパーティションキーが含まれる場合、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のクエリ特性は、通常、直近1日または1か月のデータを対象とするものであり、時間属性を伴うクエリが多いことです。そのため、サブパーティションキーには時間型のフィールドや時間関数を選択し、範囲クエリを容易にするために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), -- 年齢 <25
SUBPARTITION p_adult VALUES LESS THAN (40), -- 25 ≤ age <40
SUBPARTITION p_middle_aged VALUES LESS THAN (60),-- 40 ≤ age <60
SUBPARTITION p_senior VALUES LESS THAN (MAXVALUE)-- 60歳以上
);
主キーの設計
主キーの設計は、リレーショナルデータベースのテーブル設計における核心的な部分です。OceanBaseでは、主キーはデータの一意性を保証するだけでなく、データが主キー順にソートされるため、範囲クエリのパフォーマンス最適化にも活用できます。異なる主キー設計案は、異なるビジネスシナリオに適用されます。
主キーを設定するシナリオ
- データの一意性の保証：テーブル内のデータで各レコードの一意性を保証する必要がある場合、主キーを設計する必要があります。
- クエリ効率の向上：主キーはオプティマイザーがより効率的なクエリプランを生成するのに役立ちます。特にデータクエリが主キーに依存する場合、クエリで必ず指定されるフィールドを主キーフィールドとして設定することを推奨します。これにより、主キーを活用してクエリ性能を向上させることができます。
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), -- 年齢 <25
SUBPARTITION p_adult VALUES LESS THAN (40), -- 25 ≤ age <40
SUBPARTITION p_middle_aged VALUES LESS THAN (60),-- 40 ≤ age <60
SUBPARTITION p_senior VALUES LESS THAN (MAXVALUE)-- 60歳以上
);
説明：
- 主キーを持つテーブルのデータインポート性能は、主キーがないテーブルよりも低くなります。これは、テーブルデータが主キーに基づいてソートされるためです。
- 主キーを持つパーティションテーブルの主キーには、すべてのパーティションキーを含める必要があります。
HTAPシナリオにおけるクエリの最適化
OceanBaseは、HTAP（ハイブリッドトランザクション/アナリティクス処理）シナリオをサポートするための多様な手段を提供し、トランザクション型（TP）と分析型（AP）のビジネスニーズを同時に満たすことができます。OceanBaseは、データテーブルが行ストア、カラムストア、および行ストア・カラムストアの冗長ストレージ形式をサポートするほか、インデックステーブルでも指定ストレージ形式をサポートしています。これにより、対応するシナリオにおいて行ストアとカラムストアの利点を組み合わせて処理できます。
カラムストアインデックスの作成方法については、MySQLモードでのカラムストアインデックスの作成およびOracleモードでのカラムストアインデックスの作成をご参照ください。
行ストアのベーステーブル + カラムストアのインデックス
適用シナリオ：トランザクション型業務が主体でありながら、一定の分析型クエリの要件もあり、かつテーブルがワイドテーブルである場合。 最適化戦略：分析型クエリのパフォーマンスを向上させるために、カラムストアインデックスを作成します。
- メリット：一部の列に対してのみ冗長ストレージを行い、それもカラムストア形式です。
- デメリット：クエリステートメントに応じて適切なフィールドを特定し、カラムストアインデックスを作成する必要があります。また、データ書き込み時にはインデックステーブルのデータをメンテナンスする必要があるため、書き込みパフォーマンスは相対的にやや低下します。
カラムストアベースライン + 行ストアインデックス
適用シナリオ：分析型業務が主体ですが、効率的な単純クエリ（ポイントクエリなど）のサポートも必要な場合。 最適化戦略：インデックス作成時にテーブルへの再アクセスを回避するため、カバリングインデックスを作成できます。
- メリット：一部の列データのみを冗長的に保存します。現在、カラムストアテーブルから行ストアテーブルや行列冗長テーブルへのオンライン変換はサポートされていないため、行ストアインデックスを追加することでクエリの最適化を実現できます。
- デメリット：クエリステートメントに応じて適切なフィールドを特定し、行ストアインデックスを作成する必要があります。また、データ書き込み時にはインデックステーブルのデータをメンテナンスする必要があるため、書き込みパフォーマンスは相対的にやや低下します。
行ストア・カラムストア冗長：行ストアベースライン + カラムストアベースライン
適用シナリオ：トランザクション型と分析型の両方のクエリを同時に扱う必要があり、かつクエリのパターンが多様である場合。 最適化戦略：直接に二つのベースラインデータを冗長的に保存し、システムがクエリの特性に応じて適切な実行計画を生成するようにします。クエリロジックは、デフォルトで範囲スキャンはカラムストアモードで行われ、ポイントクエリは行ストアモードにフォールバックします。
- メリット：業務のクエリ特性を分析する必要がなく、分析型クエリの結果は強整合性です。
- デメリット：より多くのディスク容量を消費し、実行計画が不正確になりやすい傾向があります。
カラムストアレプリカ：2F1A1C
適用シナリオ：典型的なHTAP業務において、分析型クエリがトランザクション型業務に影響を与えることを避けたい場合で、分析クエリの弱整合性読み取りを許容できる場合。 最適化戦略：カラムストアレプリカの分離特性を活用することで、AP/TP業務が互いに影響せずに業務クエリを実行できます。
- メリット：
- 複数のレプリカで冗長データを保存する必要がないため、ディスク容量を節約できます。
- AP/TP業務が相互に分離されています。
- データ書き込みパフォーマンスに影響がありません。
- デメリット：
- カラムストアレプリカは弱整合性読み取りです。
- 独立したアクセスエントリが必要です。