主キー設計はリレーショナルデータベースのテーブル設計における核心的な部分です。OceanBaseでは、主キーはデータの一意性を保証するだけでなく、データが主キー順にソートされるため、範囲クエリのパフォーマンス最適化にも利用できます。異なる主キー設計案は、異なるビジネスシナリオに適用されます。
主キーなしのシナリオ
単一テーブルで主キーなしのカラムストアテーブルを選択する場合
データウェアハウスやオンライン分析処理（OLAP）シナリオでは、業務データテーブルのデータ量は非常に膨大であり、ほとんどのクエリは特定の列のみに関わります。業務上、データの一意性の保証、重複書き込みの回避、トランザクション更新や挿入操作などの特別な要件がない場合、主キーなしのカラムストアテーブルを作成する方が効率的です：
- データインポート：APシナリオではデータを主キーでソートする必要がないため、大規模データのインポートパフォーマンスは主キーを持つテーブルよりも高速です。
- 主キーの管理コストを削減します。
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型で保存する
);
主キーありのシナリオ
APシナリオではデフォルトで ヒープテーブル が使用されます。その特徴は以下の通りです：
データストレージ： 挿入順に格納され、物理的なソートはありません。
主キーの役割：
- データの一意性を保証し、レコードを一意に識別できるフィールド（例：user_id）を含める必要があります。
- パーティションキーの埋め込み をサポートします。主キーにはすべてのパーティションキー（例：user_idとage）を含める必要があり、データの均等な分散を保証します。
- インデックスやパーティション戦略と組み合わせて、クエリの高速化（例：
ORDER BY）を実現する必要があります。
例：主キーを持つヒープ構造型テーブルの設計
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歳以上
);
主キーを設定するシナリオ
- データの一意性の保証： テーブル内のデータで各行の一意性を保証する必要がある場合、主キーを設計する必要があります。
- クエリ効率の向上： 主キーはオプティマイザーがより効率的なクエリ実行計画を生成するのに役立ちます。特に、データクエリが主キーに依存する場合、クエリで必ず指定されるフィールドを主キーフィールドとして設定することを推奨します。これにより、主キーを利用してクエリのパフォーマンスを向上させることができます。
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歳以上
);