Step 3: Learn the key points of database table design

Unique model

In the Unique model, if rows have the same key values, the value columns of the latest row overwrite the previous ones. The Unique model offers two implementations: Merge on Read (MOR) and Merge on Write (MOW).

We recommend that you prioritize the Merge on Write (MOW) implementation because it is mature, stable, and provides excellent query performance. This topic provides a brief introduction to the MOW implementation for the Unique model. For detailed information about Merge on Read, see 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

In the Duplicate model, rows with the same key values are stored together in the system. This model has no pre-aggregation or primary key uniqueness constraints.

For example, if you want to record and analyze log data from a business system and sort the data by log time, log type, and error code, you can choose this model. The following statement creates the log table. It specifies that the log table uses the Duplicate model and that data is 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.

In a log scenario, you often filter and query data by time range and need to delete historical partitions by time. You can specify the log_time field as the partition key column and partition the data by day, as shown in the following example.

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`);

After the table is created, you can run the following SQL statement to view the table's 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"))

When you run the following statement to query data, it hits the partition p20240202: [("2024-02-02"), ("2024-02-03")). The system does not scan the data in the other two partitions, which improves query speed.

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

List partitioning

List partitioning divides and manages data based on the enumerated values of a partition field. When you query a list-partitioned table, the system can quickly perform partition pruning based on filter conditions to improve query performance.

You can choose the list partition column based on the fields you commonly use to operate on business data. Note that the data volume should be evenly distributed among the partitions to avoid significant data skew.

For example, in an e-commerce scenario, the volume of order data is usually very large. In some scenarios, you often need to query and analyze this data based on the city of the user who placed the order. To manage and query data more easily, you can specify the customer_city field as the partition key column. Assume the data is distributed by region as follows:

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

After the table is created, you can run the following SQL statement to view the table's partition information. The following three partitions are automatically generated.

SHOW partitions FROM orders;

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

When you run the following statement to query data, it hits the partition p_jp: ("Tokyo"). The system does not scan the data in the other two partitions, which improves query speed.

SELECT * FROM orders WHERE customer_city = 'Tokyo';

Inverted index

SelectDB supports inverted indexes. You can use inverted indexes to perform full-text searches on text fields and equality or range queries on ordinary fields. This lets you quickly retrieve data that meets specific conditions from a large amount of data. This topic describes how to create an inverted index. For more information, see Inverted indexes.

Create an index when you create a table

In an e-commerce scenario, querying order information by user ID and user address keywords is a frequent operation. In this case, you can create an inverted index on the customer_id and customer_address fields to improve query speed. The specific table creation statement is as follows.

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

In an e-commerce scenario, querying order information by user ID is a frequent operation. If you did not create an inverted index for the customer_id field when you created the table, you can use the following statement to add one.

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.

There is no special syntax to define a prefix index. The system selects the fields that can be covered by the first 36 bytes as the prefix index, based on the order of key columns defined during table creation. However, when a VARCHAR type is encountered, the prefix index is truncated, and subsequent key columns are not added to the prefix index.

The order of fields defined during table creation is very important. It determines which fields will be used for the prefix index. We strongly recommend that you determine the order of key columns based on the following 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';