OceanBaseの分散パーティション分割機能による大規模テーブルのクエリ性能向上

MySQLデータベースを例に、分散パーティション機能を活用して大規模テーブルのクエリ性能を向上させる方法を紹介します。

シナリオ紹介

あるECシステムのクーポンテーブルでは、顧客が注文するたびに、その顧客が保有するクーポンを照会します(user_idで照会)。数年間のビジネス展開を経て、このテーブルのデータ量は5億件に達しました。現在のMySQLデータベースはuser_idにインデックスを作成していますが、データ量の増加に伴いクエリのパフォーマンスが徐々に低下し、MySQLの単一マシンの上限に近づいています。 この場合、OceanBaseのパーティション機能を活用してパフォーマンスを最適化できます。クーポンテーブルにuser_idフィールドを使用してHASHパーティションを行い、このテーブルを16個のパーティションに分割します。これにより、各パーティションには平均して3,000万件余りのデータしか存在せず、クーポンのクエリ性能が大幅に向上します。さらに、小売システムで一般的な大規模セールやイベントシナリオでは、OceanBaseデータベースの分散スケーラビリティ機能を活用し、ノードを追加して自動ロードバランシングを実現することもできます。

前提条件

• お客様は現在のインスタンスに対するインスタンス管理者、データ読み取り、およびデータサービス管理者権限を保有しています。権限がない場合は、組織管理者に連絡して追加してもらうことができます。

• お客様の環境には利用可能なトランザクション型(MySQL)クラスタインスタンスがあります。テナントの作成を参照してテナントの作成を完了した後、データベースとアカウントを作成してください。

パーティション設計

クーポンテーブルについて：

• RANGEパーティションの使用：クーポンの発行日時(create_time)に基づいてデータをパーティション化し、履歴データを分離しやすくします。
• HASHパーティションの使用：各時間範囲内で、user_idのHASH値に基づいてさらにサブパーティションに分割し、クエリ負荷を均等に分散します。 このパーティション設計により、各パーティションのデータ量は約3,000万件に抑えられ、クエリ性能が大幅に向上します。また、履歴データは時間範囲ごとに整理・管理しやすくなります。

操作手順

1. **SQLコンソールページ**にアクセスします。

2. 作成したアカウントを選択し、パスワードを入力してログインし、**OK**をクリックします。

3. 左側のデータベース（例：default_database）をダブルクリックして、新しいSQLウィンドウを開きます。

4. 電子商取引システムのクーポンテーブルを作成し、パーティションを作成します。

   CREATE TABLE coupon (
       coupon_id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'クーポンID（主キー）',
       user_id BIGINT NOT NULL COMMENT 'ユーザーID。ユーザーのクーポンを照会するために使用されます',
       coupon_code VARCHAR(64) NOT NULL COMMENT 'クーポンコード。一意の識別子です',
       status TINYINT NOT NULL DEFAULT 1 COMMENT 'クーポンの状態：1=未使用、2=使用済み、3=期限切れ',
       create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
       expire_time TIMESTAMP NOT NULL COMMENT '有効期限',
       PRIMARY KEY (user_id, create_time, coupon_id),  -- create_timeを主キーに追加
       KEY idx_coupon_user_id (user_id)
   )
   PARTITION BY RANGE COLUMNS (create_time)  -- RANGE COLUMNSを使用してcreate_timeでパーティション分割
   SUBPARTITION BY HASH (user_id)            -- user_idでHASHサブパーティション分割
   SUBPARTITIONS 8                           -- 各パーティションを8つのサブパーティションに分割
   (
       PARTITION p2021_q1 VALUES LESS THAN ('2021-04-01'),
       PARTITION p2021_q2 VALUES LESS THAN ('2021-07-01'),
       PARTITION p2021_q3 VALUES LESS THAN ('2021-10-01'),
       PARTITION p2021_q4 VALUES LESS THAN ('2022-01-01'),
       PARTITION p2022_q1 VALUES LESS THAN ('2022-04-01'),
       PARTITION p2022_q2 VALUES LESS THAN ('2022-07-01'),
       PARTITION p2023_q1 VALUES LESS THAN ('2023-04-01'),
       PARTITION p_future VALUES LESS THAN MAXVALUE
   );
   

   ここで：

   • RANGEパーティション：データはcreate_timeのタイムスタンプ範囲に基づいてパーティション化されます。例えば、半年ごとに1つのパーティションが作成されます。履歴データは時間範囲に基づいてアーカイブでき、フルテーブルスキャンを回避できます。

   • HASHパーティション：各時間パーティション内のデータは、さらにuser_idに基づいてHASHサブパーティションに分割されます。サブパーティション数は16に設定され、元のテーブルは6つの期間パーティション × 16のサブパーティション = 最終的に96個のパーティションに分割されます。

5. データを挿入すると、OceanBaseデータベースはcreate_timeとuser_idに基づいて、適切なRANGEパーティションおよびHASHサブパーティションに自動的にデータを書き込みます。

       INSERT INTO coupon (user_id, coupon_code, status, create_time, expire_time) 
       VALUES 
       (1001, 'DISCOUNT2023', 1, '2023-03-15 08:00:00', '2023-12-31 23:59:59'),
       (1002, 'DISCOUNT2023', 1, '2022-06-01 12:30:00', '2022-12-31 23:59:59'),
       (1003, 'DISCOUNT2023', 1, '2021-05-03 10:15:00', '2021-12-31 23:59:59'),
       (1004, 'DISCOUNT2024', 1, '2024-01-01 06:00:00', '2024-03-31 23:59:59');
   

6. クエリ最適化の例：単一ユーザーのクーポンを照会する場合、OceanBaseデータベースはまずパーティションルールを優先的に利用し、create_timeで指定されたRANGEパーティションを特定し、次にuser_idで対応するHASHサブパーティションを照会します。

   • 特定のユーザーの指定された期間内のクーポンを照会する：

         SELECT * 
         FROM coupon 
         WHERE user_id = 1001 AND create_time >= '2023-01-01' AND create_time < '2023-07-01';
     

   • すべてのユーザーのクーポンを期間で検索する：

         SELECT * 
         FROM coupon 
         WHERE create_time >= '2022-01-01' AND create_time < '2023-01-01';
     

   • 使用されていないクーポンを、パーティションルールとstatusフィールドを組み合わせてフィルタリングする：

         SELECT * 
         FROM coupon
         WHERE status = 1 AND create_time >= '2023-01-01' AND create_time < '2023-07-01';