Step 3: Learn the key points in database and table design

In the Unique model, if a key column has the same value in multiple rows, the row written to the table later overwrites the previous one. The Unique model provides two implementation methods: Merge on Read (MoR) and Merge on Write (MoW).

The MoW method is mature and stable and provides excellent query performance. Therefore, we recommend that you use the MoW method in the Unique model. The following example describes how to use the MoW method to implement the Unique model. For more information about the MoR method, see the MoR section of the "Data models" topic.

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "The order ID.",
    `order_time` DATETIME NOT NULL COMMENT "The order time.",
    `customer_id` LARGEINT NOT NULL COMMENT "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."
)
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"
);

In the Duplicate model, multiple rows with the same value for a key column are stored in the system at the same time. This model does not support pre-aggregation or require unique primary keys.

For example, you can use this model to record and analyze log data generated by a business system and sort the data by log time, log type, and error code. The following sample code shows the SQL statement for creating the log table. In this example, the Duplicate model is selected for the log table, and data is sorted by the log_time, log_type, and error_code fields.

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

Range partitioning is the most commonly used partitioning method to manage data based on the range of fields. In a typical scenario, a large amount of time series data is partitioned by time to facilitate management and optimize queries.

The ultimate goal of partitioning and tableting is to divide data reasonably. Comply with the following standards when you specify partitioning rules:

• The data amount of each tablet must be in the range of 1 GB to 10 GB.

• The partition granularity must be determined based on the amount of data that you want to manage. For example, if you want to delete historical log data by day, a partition granularity of day is appropriate.

The following sample code shows how to create a partitioned table, data in which is filtered by time range and historical log data is deleted by time. In this example, the log_time field is used as the partition key column.

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

After the table is created, you can execute the following SQL statement to view the partition information of the table:

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 execute the following statement to query data, the partition p20240202: [("2024-02-02"), ("2024-02-03")) is hit. The system does not scan the data in the other two partitions. This accelerates data queries.

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

List partitioning divides and manages data based on the enumeration values of partition key columns. When you query data from a list partitioned table, you can prune partitions based on filter conditions to improve query performance.

You can select list partition key columns based on the fields that are commonly used to manage your business data. Data must be evenly distributed to the partitions to prevent serious data skew.

For example, a large amount of order data exists in e-commerce scenarios. In some scenarios, you want to query and analyze the order data based on the cities to which users belong. To ease data management and query, you can specify the customer_city field as a partition key column. In this example, the order data is distributed in the following cities:

• Beijing, Shanghai, and Hong Kong: 6 GB

• New York and San Francisco: 5 GB

• Tokyo: 5 GB

The following sample code shows how to create a list partitioned table for the order data:

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "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 "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"
);

After the table is created, you can execute the following SQL statement to view the partition information of the table. Three partitions are automatically generated for the table:

SHOW partitions FROM orders;

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

When you execute the following statement to query data, the partition p_jp: ("Tokyo") is hit. The system does not scan the data in the other two partitions. This accelerates data queries.

SELECT * FROM orders WHERE customer_city = 'Tokyo';

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

Create an inverted index when you create a table

In an e-commerce scenario, you need to frequently query order information based on keywords such as the user ID and user address. In this case, you can create an inverted index on the customer_id and customer_address fields to accelerate the queries. The following sample code shows how to create a table for such a scenario:

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "The order ID.",
    `order_time` DATETIME NOT NULL COMMENT "The order time.",
    `customer_id` LARGEINT NOT NULL COMMENT "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"
);

Create an inverted index on a column in an existing table

In an e-commerce scenario, you need to frequently query order information based on the user ID. However, no inverted index is created on the customer_id field when you create the orders table. In this case, you can execute the following statement to add an index for the table:

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

A prefix index is created on one or more key columns in underlying data that is sorted by key columns. Essentially, prefix indexing is binary search based on the data sorting feature. Prefix indexes are built-in indexes. They are automatically created by SelectDB after a table is created.

No dedicated syntax is available to define prefix indexes. The system selects the first one or more key column fields in a table as the prefix index. The total length of the prefix index cannot exceed 36 bytes. The key column fields after a field of the VARCHAR type are not added to the prefix index.

The order of fields in a table is particularly important. It determines the fields to be used as in the prefix index. We strongly recommend that you determine the order of key column fields based on the following rules:

• The key column fields with high cardinality that are frequently used as filter conditions are placed before other fields. For example, in the Use the Duplicate model section, the log_time field is placed before the error_code field.

• The key column fields that are used as equivalent filter conditions are placed before the key column fields that are used as range filter conditions. For example, in the Use an inverted index section, the order_time field for range filtering is placed after the order_id field.

• Ordinary-type fields are placed before VARCHAR-type fields. For example, the key column fields of the INT type are placed before the key column fields of the VARCHAR type.

Examples

In the Use an inverted index section, the order information table uses the prefix index order_id+order_time. If the query condition contains the order_id field or contains both the order_id and order_time fields, the query speed is greatly improved. The query speed in Example 1 is faster than that 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';