ApsaraDB for SelectDB:Step 3: Database table design

Unique model

The Unique model keeps only the latest value columns for duplicate keys. Two implementations: Merge on Read (MOR) and Merge on Write (MOW).

MOW is recommended for its maturity and query performance. The alternative is Merge on Read (MOR).

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "Order ID",
    `order_time` DATETIME NOT NULL COMMENT "Order time",
    `customer_id` LARGEINT NOT NULL COMMENT "User ID",
    `total_amount` DOUBLE COMMENT "Total order amount",
    `status` VARCHAR(20) COMMENT "Order status",
    `payment_method` VARCHAR(20) COMMENT "Payment method",
    `shipping_method` VARCHAR(20) COMMENT "Shipping method",
    `customer_city` VARCHAR(20) COMMENT "User's city",
    `customer_address` VARCHAR(500) COMMENT "User's address"
)
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 model

The Duplicate model stores rows with identical keys together, with no pre-aggregation or uniqueness constraints.

To record and analyze log data sorted by time, type, and error code, use the Duplicate model. This creates a log table with the Duplicate model, sorted by log_time, log_type, and error_code.

CREATE TABLE IF NOT EXISTS log
(
    `log_time` DATETIME NOT NULL COMMENT "Log time",
    `log_type` INT NOT NULL COMMENT "Log type",
    `error_code` INT COMMENT "Error code",
    `error_msg` VARCHAR(1024) COMMENT "Error details",
    `op_id` BIGINT COMMENT "Owner ID",
    `op_time` DATETIME COMMENT "Processing time"
)
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 model

CREATE TABLE IF NOT EXISTS user_behavior
(
    `user_id` LARGEINT NOT NULL COMMENT "User ID",
    `date` DATE NOT NULL COMMENT "Date and time of data write",
    `city` VARCHAR(20) COMMENT "User's city",
    `age` SMALLINT COMMENT "User's age",
    `sex` TINYINT COMMENT "User's gender",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "User's last visit time",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "User's total cost",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "User's maximum dwell time",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "User's minimum dwell time"
)
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"
);

Range partitioning

Range partitioning divides and manages data based on the range of a partition field. It is the most common partitioning method. A typical scenario is to partition data by time. This makes it easier to manage and optimize queries on large amounts of time-series data.

The ultimate goal of partitioning and bucketing is to divide data reasonably. The main criteria for a reasonable partitioning rule are as follows:

• The data volume of each tablet should be between 1 GB and 10 GB.

• Determine the partition granularity based on your data management needs. For example, in a log scenario, you typically need to delete historical data daily. In this case, partitioning by day is appropriate.

For log data with time-range queries and daily retention, partition by day using log_time:

CREATE TABLE IF NOT EXISTS log
(
 `log_time` DATETIME NOT NULL COMMENT "Log time",
 `log_type` INT NOT NULL COMMENT "Log type",
 `error_code` INT COMMENT "Error code",
 `error_msg` VARCHAR(1024) COMMENT "Error details",
 `op_id` BIGINT COMMENT "Owner ID",
 `op_time` DATETIME COMMENT "Processing time"
)
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`);

View partition information:

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"))

This query hits only partition p20240202: [("2024-02-02"), ("2024-02-03")), skipping the others:

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

List partitioning

List partitioning groups data by enumerated values. The system prunes non-matching partitions during queries to boost performance.

Choose partition columns used frequently in queries. Distribute data evenly across partitions to avoid skew.

In an e-commerce scenario with large order volumes and frequent city-based queries, partition by customer_city. Assume data is distributed by region:

• Beijing, Shanghai, and Hong Kong (China): 6 GB

• New York and San Francisco: 5 GB

• Tokyo: 5 GB

In this case, you can partition the data as shown in the following example.

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "Order ID",
    `order_time` DATETIME NOT NULL COMMENT "Order time",
    `customer_city` VARCHAR(20) COMMENT "User's city",
    `customer_id` LARGEINT NOT NULL COMMENT "User ID",
    `total_amount` DOUBLE COMMENT "Total order amount",
    `status` VARCHAR(20) COMMENT "Order status",
    `payment_method` VARCHAR(20) COMMENT "Payment method",
    `shipping_method` VARCHAR(20) COMMENT "Shipping method",
    `customer_address` VARCHAR(500) COMMENT "User's address"
)
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"
);

View partition information:

SHOW partitions FROM orders;

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

This query hits only partition p_jp: ("Tokyo"), skipping the others:

SELECT * FROM orders WHERE customer_city = 'Tokyo';

Inverted index

SelectDB inverted indexes support full-text search on text fields and equality or range queries on other fields. Inverted indexes.

Create an index when you create a table

To speed up queries by user ID and address keywords, create inverted indexes on customer_id and customer_address:

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "Order ID",
    `order_time` DATETIME NOT NULL COMMENT "Order time",
    `customer_id` LARGEINT NOT NULL COMMENT "User ID",
    `total_amount` DOUBLE COMMENT "Total order amount",
    `status` VARCHAR(20) COMMENT "Order status",
    `payment_method` VARCHAR(20) COMMENT "Payment method",
    `shipping_method` VARCHAR(20) COMMENT "Shipping method",
    `customer_city` VARCHAR(20) COMMENT "User's city",
    `customer_address` VARCHAR(500) COMMENT "User's address",
    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"
);

Create an index on an existing table

To add an inverted index on customer_id to an existing table:

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

Prefix index

A prefix index is built on one or more prefix key columns and relies on the underlying data being sorted by those key columns. It is essentially a binary search based on the sorted nature of the data. Prefix indexes are built-in indexes that SelectDB creates automatically after table creation.

No special syntax defines a prefix index. The system selects fields covered by the first 36 bytes of key columns. A VARCHAR column truncates the prefix index; subsequent key columns are excluded.

Key column order determines the prefix index. Order key columns by these principles:

• Place high-cardinality key columns that are frequently used for filtering before other fields. For example, in the log scenario in the Duplicate model section, the log time log_time is placed before the error code error_code.

• Place key columns for equality filters before key columns for range filters. For example, in the e-commerce scenario in the Inverted index section, the time order_time is usually filtered by range and is placed after the order ID order_id.

• Place regular type fields before VARCHAR type fields. For example, place INT type key columns before VARCHAR type key columns.

Example

In the e-commerce scenario in the Inverted index section, the prefix index for the order information table is order_id+order_time. When the query condition is a prefix of the prefix index (that is, the query condition includes order_id, or both order_id and order_time), the query speed can be significantly increased. As shown in the following two examples, the query in Example 1 is much faster than the query in Example 2.

Example 1

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

Example 2

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