ApsaraDB for SelectDB:ステップ 3：データベースとテーブル設計の要点について学習する

Unique モデルを使用する

Unique モデルでは、キー列に複数の行で同じ値がある場合、テーブルに後で書き込まれた行が前の行を上書きします。Unique モデルには、Merge on Read（MoR）と Merge on Write（MoW）の 2 つの実装方法があります。

MoW メソッドは成熟して安定しており、優れたクエリパフォーマンスを提供します。そのため、Unique モデルでは MoW メソッドを使用することをお勧めします。次の例では、MoW メソッドを使用して Unique モデルを実装する方法について説明します。MoR メソッドの詳細については、「データモデル」トピックの「MoR」セクションをご参照ください。

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "注文 ID。",
    `order_time` DATETIME NOT NULL COMMENT "注文時間。",
    `customer_id` LARGEINT NOT NULL COMMENT "ユーザー ID。",
    `total_amount` DOUBLE COMMENT "注文の合計金額。",
    `status` VARCHAR(20) COMMENT "注文ステータス。",
    `payment_method` VARCHAR(20) COMMENT "支払い方法。",
    `shipping_method` VARCHAR(20) COMMENT "配送方法。",
    `customer_city` VARCHAR(20) COMMENT "ユーザーが居住する都市。",
    `customer_address` VARCHAR(500) COMMENT "ユーザーの住所。"
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Duplicate モデルを使用する

Duplicate モデルでは、キー列の値が同じ複数の行がシステムに同時に保存されます。このモデルは、事前集計をサポートしておらず、一意のプライマリキーを必要としません。

たとえば、このモデルを使用して、業務システムによって生成されたログデータを記録および分析し、ログ時間、ログタイプ、およびエラーコードでデータをソートできます。 次のサンプルコードは、log テーブルを作成するための SQL 文を示しています。この例では、log テーブルに Duplicate モデルが選択され、データは log_time、log_type、および error_code フィールドでソートされます。

CREATE TABLE IF NOT EXISTS log
(
    `log_time` DATETIME NOT NULL COMMENT "ログが生成された時間。",
    `log_type` INT NOT NULL COMMENT "ログのタイプ。",
    `error_code` INT COMMENT "エラーコード。",
    `error_msg` VARCHAR(1024) COMMENT "エラーメッセージ。",
    `op_id` BIGINT COMMENT "オーナー ID。",
    `op_time` DATETIME COMMENT "エラーが処理された時間。"
)
DUPLICATE KEY(`log_time`, `log_type`, `error_code`)
PARTITION BY RANGE(`log_time`) ()
DISTRIBUTED BY HASH(`log_type`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Aggregate モデルを使用する

CREATE TABLE IF NOT EXISTS user_behavior
(
    `user_id` LARGEINT NOT NULL COMMENT "ユーザー ID。",
    `date` DATE NOT NULL COMMENT "テーブルにデータが書き込まれる日付。",
    `city` VARCHAR(20) COMMENT "ユーザーが居住する都市。",
    `age` SMALLINT COMMENT "ユーザーの年齢。",
    `sex` TINYINT COMMENT "ユーザーの性別。",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "ユーザーが最後に訪問した時間。",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "ユーザーが費やした金額。",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "ユーザーの最大滞在時間。",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "ユーザーの最小滞在時間。"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
PARTITION BY RANGE(`date`) ()
DISTRIBUTED BY HASH(`user_id`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

範囲パーティション分割の使用

範囲パーティション分割は、フィールドの範囲に基づいてデータを管理するために最も一般的に使用されるパーティション分割方法です。典型的なシナリオでは、大量の時系列データが時間でパーティション分割され、管理を容易にし、クエリを最適化します。

パーティション分割とタブレット化の最終的な目標は、データを合理的に分割することです。パーティション分割ルールを指定するときは、次の基準に従ってください。

• 各タブレットのデータ量は 1 GB ～ 10 GB の範囲内である必要があります。

• パーティションの粒度は、管理するデータ量に基づいて決定する必要があります。たとえば、履歴ログデータを日単位で削除する場合、日単位のパーティション粒度が適切です。

次のサンプルコードは、パーティションテーブルを作成する方法を示しています。このテーブルのデータは時間範囲でフィルタリングされ、履歴ログデータは時間で削除されます。この例では、log_time フィールドがパーティションキー列として使用されています。

CREATE TABLE IF NOT EXISTS log
(
 `log_time` DATETIME NOT NULL COMMENT "ログが生成された時間", /* The time when the log was generated. */
 `log_type` INT NOT NULL COMMENT "ログのタイプ", /* The type of the log. */
 `error_code` INT COMMENT "エラーコード", /* The error code. */
 `error_msg` VARCHAR(1024) COMMENT "エラーメッセージ", /* The error message. */
 `op_id` BIGINT COMMENT "オーナー ID", /* The owner ID. */
 `op_time` DATETIME COMMENT "エラーが処理された時間" /* The time when the error was handled. */
)
DUPLICATE KEY(`log_time`, `log_type`, `error_code`)
PARTITION BY RANGE(`log_time`)
(
 PARTITION `p20240201` VALUES [("2024-02-01"), ("2024-02-02")),
 PARTITION `p20240202` VALUES [("2024-02-02"), ("2024-02-03")),
 PARTITION `p20240203` VALUES [("2024-02-03"), ("2024-02-04"))
)
DISTRIBUTED BY HASH(`log_type`)
PROPERTIES ();

テーブルが作成された後、次の SQL 文を実行して、テーブルのパーティション情報を表示できます。

SHOW partitions FROM log;

p20240201: [("2024-02-01"), ("2024-02-02"))
p20240202: [("2024-02-02"), ("2024-02-03"))
p20240203: [("2024-02-03"), ("2024-02-04"))

次の文を実行してデータをクエリすると、パーティション p20240202: [("2024-02-02"), ("2024-02-03")) がヒットします。システムは他の 2 つのパーティションのデータをスキャンしません。これにより、データクエリが高速化されます。

SELECT * FROM orders WHERE order_time = '2024-02-02';

リストパーティション分割の使用

リストパーティションは、パーティションキー列の列挙値に基づいてデータを分割および管理します。リストパーティションテーブルからデータをクエリする場合、フィルター条件に基づいてパーティションをプルーニングして、クエリのパフォーマンスを向上させることができます。

ビジネスデータの管理に一般的に使用されるフィールドに基づいて、リストパーティションキー列を選択できます。深刻なデータスキューを防ぐために、データはパーティションに均等に分散される必要があります。

たとえば、e コマースのシナリオでは、大量の注文データが存在します。一部のシナリオでは、ユーザーが属する都市に基づいて注文データをクエリおよび分析する必要があります。データ管理とクエリを容易にするために、customer_city フィールドをパーティションキー列として指定できます。この例では、注文データは次の都市に分散されています。

• 中国の北京、上海、香港：6 GB

• ニューヨークとサンフランシスコ：5 GB

• 東京：5 GB

次のサンプルコードは、注文データのリストパーティションテーブルを作成する方法を示しています。

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "注文 ID", /* The order ID. */
    `order_time` DATETIME NOT NULL COMMENT "注文時間", /* The order time. */
    `customer_city` VARCHAR(20) COMMENT "ユーザーが居住する都市", /* The city in which the user resides. */
    `customer_id` LARGEINT NOT NULL COMMENT "ユーザー ID", /* The user ID. */
    `total_amount` DOUBLE COMMENT "注文の合計金額", /* The total amount of the order. */
    `status` VARCHAR(20) COMMENT "注文ステータス", /* The order status. */
    `payment_method` VARCHAR(20) COMMENT "支払い方法", /* The payment method. */
    `shipping_method` VARCHAR(20) COMMENT "配送方法", /* The shipping method. */
    `customer_address` VARCHAR(500) COMMENT "ユーザーの住所" /* The address of the user. */
)
UNIQUE KEY(`order_id`, `order_time`, `customer_city`)
PARTITION BY LIST(`customer_city`)
(
    PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
    PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
    PARTITION `p_jp` VALUES IN ("Tokyo")
)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 16
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true"
);

テーブルが作成された後、次の SQL 文を実行して、テーブルのパーティション情報を表示できます。テーブルには 3 つのパーティションが自動的に生成されます。

SHOW partitions FROM orders;

p_cn: ("Beijing", "Shanghai", "Hong Kong")
p_usa: ("New York", "San Francisco")
p_jp: ("Tokyo")

次の文を実行してデータをクエリすると、パーティション p_jp: ("Tokyo") がヒットします。システムは他の 2 つのパーティションのデータをスキャンしません。これにより、データクエリが高速化されます。

SELECT * FROM orders WHERE customer_city = 'Tokyo';

転置インデックスを使用する

ApsaraDB for SelectDB は転置インデックスをサポートしています。転置インデックスを使用して、TEXT タイプのデータに対して全文検索を実行したり、通常のフィールドのデータに対して同等クエリまたは範囲クエリを実行したりできます。このようにして、大量のデータから特定の条件を満たすデータをすばやく取得できます。このセクションでは、転置インデックスを作成する方法について説明します。詳細については、「転置インデックス」をご参照ください。

テーブルの作成時に転置インデックスを作成する

e コマースのシナリオでは、ユーザー ID やユーザーアドレスなどのキーワードに基づいて注文情報を頻繁にクエリする必要があります。この場合、customer_id フィールドと customer_address フィールドに転置インデックスを作成して、クエリを高速化できます。次のサンプルコードは、このようなシナリオのテーブルを作成する方法を示しています。

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "注文ID", /* The order ID. */
    `order_time` DATETIME NOT NULL COMMENT "注文時間", /* The order time. */
    `customer_id` LARGEINT NOT NULL COMMENT "ユーザーID", /* The user ID. */
    `total_amount` DOUBLE COMMENT "注文の合計金額", /* The total amount of the order. */
    `status` VARCHAR(20) COMMENT "注文ステータス", /* The order status. */
    `payment_method` VARCHAR(20) COMMENT "支払い方法", /* The payment method. */
    `shipping_method` VARCHAR(20) COMMENT "配送方法", /* The shipping method. */
    `customer_city` VARCHAR(20) COMMENT "ユーザーが居住する都市", /* The city in which the user resides. */
    `customer_address` VARCHAR(500) COMMENT "ユーザーの住所", /* The address of the user. */
    INDEX idx_customer_id (`customer_id`) USING INVERTED,
    INDEX idx_customer_address (`customer_address`) USING INVERTED PROPERTIES("parser" = "chinese")
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

既存のテーブルの列に転置インデックスを作成する

e コマースのシナリオでは、ユーザー ID に基づいて注文情報を頻繁にクエリする必要があります。ただし、orders テーブルの作成時に customer_id フィールドに転置インデックスは作成されません。この場合、次のステートメントを実行して、テーブルにインデックスを追加できます。

ALTER TABLE orders ADD INDEX idx_customer_id (`customer_id`) USING INVERTED;

プレフィックスインデックスを使用する

プレフィックスインデックスは、キー列でソートされた基になるデータの1つ以上のキー列に作成されます。基本的に、プレフィックスインデックスは、データソート機能に基づくバイナリ検索です。 プレフィックスインデックスは組み込みインデックスです。テーブルの作成後に SelectDB によって自動的に作成されます。

プレフィックスインデックスを定義するための専用の構文はありません。システムは、テーブルの最初の1つ以上のキー列フィールドをプレフィックスインデックスとして選択します。プレフィックスインデックスの全長は 36 バイトを超えることはできません。VARCHAR タイプのフィールドの後のキー列フィールドは、プレフィックスインデックスに追加されません。

テーブル内のフィールドの順序は特に重要です。プレフィックスインデックスで使用されるフィールドが決まります。次のルールに基づいてキー列フィールドの順序を決定することを 強くお勧めします。

• フィルター条件として頻繁に使用されるカーディナリティの高いキー列フィールドは、他のフィールドの前に配置されます。たとえば、「重複モデルを使用する」セクションでは、log_time フィールドは error_code フィールドの前に配置されます。

• 同等フィルター条件として使用されるキー列フィールドは、範囲フィルター条件として使用されるキー列フィールドの前に配置されます。たとえば、「転置インデックスを使用する」セクションでは、範囲フィルタリング用の order_time フィールドは order_id フィールドの後に配置されます。

• 通常タイプのフィールドは VARCHAR タイプのフィールドの前に配置されます。たとえば、INT タイプのキー列フィールドは、VARCHAR タイプのキー列フィールドの前に配置されます。

例

転置インデックスを使用する セクションでは、注文情報テーブルはプレフィックスインデックス order_id+order_time を使用します。クエリ条件に order_id フィールドが含まれている場合、または order_id フィールドと order_time フィールドの両方が含まれている場合、クエリ速度が大幅に向上します。例1のクエリ速度は例2よりも高速です。

例 1

SELECT * FROM orders WHERE order_id = 1829239 and order_time = '2024-02-01';

例 2

SELECT * FROM orders WHERE order_time = '2024-02-01';