データベースシステムの設計と最適化において、データテーブルの設計は非常に重要な作業です。適切な設計は、データの完全性と整合性を確保するだけでなく、クエリのパフォーマンス、ストレージ効率、システムの拡張性を大幅に向上させることができます。データ量の増加とクエリパターンの複雑化に伴い、データテーブルを科学的に設計し、適切なストレージ構造とインデックス戦略を選択することが、データベースを最適化する上で重要な課題となっています。
本記事では、テーブルのストレージ形式の選択、主キーなしの単一テーブルの列指向設計、パーティション設計、主キー設計、そして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型で保存する
);
データテーブルパーティションニングを選択する場合
パーティションニング技術は、一つの大きなテーブルを複数のより小さく、管理しやすいサブテーブルに分割することで、クエリとデータ管理の面で大きな利点をもたらします。パーティショニングは通常、時間や地域など、データの特定のフィールドに従って行われます。
パーティショニングが有効なシナリオ
- データ量が非常に大きい場合：データテーブルのレコード数が億単位、あるいはそれ以上に達すると、単一テーブルでの操作はクエリ性能の低下を招き、さらにはデータベースのリソースボトルネックを引き起こす可能性があります。
- クエリが特定のフィールドに頻繁に依存する場合：例えば、時間フィールドはよく使われるクエリ条件の1つであり、時間でパーティション分割を行うことで、時間関連のクエリ効率を向上させることができます。
パーティション戦略の選択
- Rangeパーティション：時間範囲に基づいて分割するのに適しており、データが時間順に生成され、時間でのフィルタリングが多い場合に有効です。
- Hashパーティション：ハッシュアルゴリズムを用いてデータを複数のパーティションに均等に分散させる方法で、明確なクエリパターンが存在しないシナリオに適しています。
- Listパーティション：離散的なデータに適しており、例えば地域ごとにパーティションを分ける場合に用いられます。
APシナリオでは、通常、多次元の分析クエリが含まれます。もし、データを分割して各クエリに適用できるような単一の次元が存在せず、それでも分散ノードの計算能力を活用するためにデータを複数のマシンに分散させるパーティショニングが必要な場合、以下のようにパーティションキーを選択してHashパーティショニングを行い、ユーザーデータをできるだけ均等に分散させることができます：
- NDV（Number of Distinct Values：個別値の数）がパーティション数よりもはるかに大きいです。
- この列のデータに偏りはない、もしくはごくわずかな偏りがあるのみです。例：取引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を作成し、その中に二つのパーティションテーブル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系のクエリは、直近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モードでのカラムストア・インデックスの作成を参照してください。
行ストアのベーステーブル + カラムストアのインデックス
適用シナリオ：トランザクション型の業務が主で、同時に一定の分析系のクエリ要件が存在し、かつテーブルが多数の列を持つワイドテーブルである場合。 最適化戦略：分析系クエリのパフォーマンスを向上させるために、カラムストア・インデックスを作成します。
- メリット：一部の列に対してのみ冗長ストレージを行い、カラムストア形式を採用しています。
- デメリット：クエリステートメントに応じて適切なフィールドを特定し、カラムストア・インデックスを作成する必要があり、データ書き込み時にはインデックステーブルデータのメンテナンスも行うため、書き込みパフォーマンスは相対的に若干劣ります。
カラムストアベースライン + 行ストアインデックス
適用シナリオ：分析系の業務が主であるが、効率的な単純クエリ（ポイントルックアップクエリなど）のサポートも必要な場合。 最適化戦略：インデックスを作成する際、テーブルへの再アクセスを回避するために、カバリングインデックスを作成できます。
- メリット：一部の列データのみを冗長的に格納します。現在、オンラインでの列指向テーブルから行指向テーブルや行列冗長テーブルへの変換はサポートされていないため、行指向インデックスを追加することでクエリの最適化を図ることができます。
- デメリット：クエリステートメントに応じて適切なフィールドを特定し、行ストアインデックスを作成する必要があり、データ書き込み時にはインデックステーブルデータのメンテナンスも行うため、書き込みパフォーマンスは相対的に若干劣ります。
行・列指向の冗長化：行ストアベースライン + カラムストアベースライン
適用シナリオ：トランザクション型と分析型の両方のクエリを同時に扱う必要があり、さらにクエリのパターンも多様である場合。
最適化戦略：ベースラインデータを2つ冗長的に直接格納し、システムがクエリの特性に応じて適切な実行計画を生成するようにします。クエリロジックは、デフォルトでは範囲スキャンは列指向モードで行われ、ポイントルックアップクエリは行指向モードにフォールバックします。
- メリット：業務のクエリ特性を分析する必要はなく、分析系クエリの結果は強整合性を持っています。
- デメリット：より多くのディスク容量を消費し、実行計画が不正確になる可能性があります。
カラムストアレプリカント(columnstore replica)：2F1A1C
適用シナリオ：典型的なHTAPビジネスで、分析系のクエリがトランザクション系の業務に影響を与えることを望まず、同時に分析クエリの弱い整合性読み取りを許容できる場合。 最適化戦略：カラムストアレプリカントの分離特性を利用することで、AP/TP業務が互いに影響することなく、業務のクエリを実行できます。
- メリット：
- 複数のレプリカで冗長データを格納する必要がなく、ディスクスペースの使用量を節約できます。
- AP/TP業務が相互に分離されています。
- データ書き込みのパフォーマンスへの影響はありません。
- デメリット：
- カラムストアレプリカントは弱い整合性読み取りを行います。
- 独立したアクセスエントリーが必要です。