APシナリオにおけるデータテーブル設計とクエリ最適化の実践

データベースシステムの設計と最適化において、データテーブル設計は極めて重要な作業です。適切な設計は、データの完全性と一貫性を確保するだけでなく、クエリ性能、ストレージ効率、システムの拡張性を大幅に向上させることができます。データ量の増加とクエリパターンの複雑化に伴い、科学的なデータテーブル設計、適切なストレージ構造とインデックス戦略の選択は、データベース最適化の核心的な課題となっています。

本記事では、テーブルのストレージ形式の選択、主キーなしの単一カラムストアテーブル、パーティション設計、主キー設計、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";

主キーの設計

主キーの設計は、リレーショナルデータベースのテーブル設計における核心的な部分です。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歳以上
);

HTAPシナリオにおけるクエリの最適化

OceanBaseはHTAP（ハイブリッドトランザクション・アナリティクス処理）シナリオをサポートするためのさまざまな手法を提供しており、トランザクション型（TP）と分析型（AP）のビジネス要件を同時に満たすことができます。OceanBaseでは、データテーブルが行ストア、カラムストア、および行ストア・カラムストアの冗長ストレージ形式をサポートするほか、インデックステーブルでもストレージ形式を指定できます。これにより、対応するシナリオにおいて行ストアとカラムストアの利点を組み合わせて処理できます。

カラムストアインデックスの作成方法については、MySQLモードでのカラムストアインデックスの作成およびOracleモードでのカラムストアインデックスの作成を参照してください。

行ストアベーステーブル + カラムストアインデックス

適用シナリオ：トランザクション型業務が主体でありながら、一定の分析型クエリの要件があり、かつテーブルがワイドテーブルである場合。 最適化戦略：分析型クエリのパフォーマンスを向上させるために、カラムストアインデックスを作成します。

• メリット：一部の列に対してのみ冗長ストレージを行い、それもカラムストア形式です。
• デメリット：クエリステートメントに基づいて適切なフィールドを特定し、カラムストアインデックスを作成する必要があります。また、データ書き込み時にはインデックステーブルのデータをメンテナンスする必要があるため、書き込みパフォーマンスは相対的にやや低下します。

カラムストアベースライン + 行ストアインデックス

適用シナリオ：分析型業務が主体である一方で、効率的な単純クエリ（ポイントクエリなど）のサポートも必要な場合。 最適化戦略：インデックス作成時にテーブルへの再アクセスを回避するため、カバリングインデックスを作成できます。

• メリット：一部の列データのみを冗長的に保存します。現在、カラムストアテーブルから行ストアテーブルや行列冗長テーブルへのオンライン変換はサポートされていないため、行ストアインデックスを追加することでクエリの最適化を実現できます。
• デメリット：クエリステートメントに基づいて適切なフィールドを特定し、行ストアインデックスを作成する必要があります。また、データ書き込み時にはインデックステーブルのデータをメンテナンスする必要があるため、書き込みパフォーマンスは相対的にやや低下します。

行ストア・カラムストア冗長：行ストアベースライン + カラムストアベースライン

適用シナリオ：トランザクション型と分析型の両方のクエリを同時に扱う必要があり、かつクエリのパターンが多様である場合。 最適化戦略：ベースラインデータを直接2重に冗長保存し、システムがクエリの特性に応じて適切な実行計画を生成するようにします。クエリロジックは、デフォルトで範囲スキャンはカラムストアモードで行われ、ポイントクエリは行ストアモードにフォールバックします。

• メリット：業務のクエリ特性を分析する必要がなく、分析型クエリの結果は強整合性です。
• デメリット：より多くのディスク容量を消費し、実行計画が不正確になりやすい傾向があります。

カラムストアレプリカ：2F1A1C

適用シナリオ：典型的なHTAP業務において、分析型クエリがトランザクション型業務に影響を与えることを避けたい場合であり、分析クエリの弱整合性読み取りを許容できる場合。 最適化戦略：カラムストアレプリカの分離特性を活用することで、AP/TP業務が互いに影響せずに業務クエリを実行できます。

• メリット：
  1. 複数のレプリカで冗長データを保存する必要がないため、ディスク容量を節約できます。
  2. AP/TP業務が相互に分離されています。
  3. データ書き込みパフォーマンスに影響がありません。
• デメリット：
  1. カラムストアレプリカは弱整合性読み取りです。
  2. 独立したアクセスエントリが必要です。