Using Broadcast and Unsharded Tables

Updated on 2022-09-15 GMT+08:00

View PDF

Unsharded Table

The DDM console does not allow creation of unsharded tables. To create unsharded tables, you can log in to your DDM instance using a MySQL client or an application.

If a table contains fewer than 10 million data records and has no requests for JOIN operations with other sharded tables for query, set the table type to Unsharded Table to store data in the default shard.

The following is an example SQL statement for creating an unsharded table:

CREATE TABLE single(
id int NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
name varchar(128),
PRIMARY KEY(id)
);

Broadcast Table

In business databases, some tables have a small amount of data and are not frequently updated, but they are often used for JOIN operations.

To join such tables and sharded tables, DDM provides broadcast tables, which have the following features:

The broadcast table stores the same data in each shard. Data insertion, update, and deletion in broadcast tables are executed in each shard in real time.
Queries of broadcast tables are only executed in one shard.
Any tables can JOIN with broadcast tables.
Before using any broadcast hint, ensure that there are available tables.

Example:

When the order management system of an e-commerce enterprise needs to query and collect statistics on order data in Guangdong province (China), JOIN query is performed for a regional table and an order flow table. The order data volume is large and the order flow table needs to be sharded. In this scenario, you can set the regional table as a broadcast table to avoid cross-database JOIN.

The following is an example SQL statement for creating a broadcast table:

CREATE TABLE broadcast_tbl (
id int NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
name varchar(128),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
BROADCAST;

NOTE:

Operate broadcast tables or perform full table scanning operations during off-peak hours, such as executing SQL statements that do not contain sharding conditions. Otherwise, an error may occur, indicating that the number of backend RDS connections is insufficient.

Previous topic: Determining the Number of Shards in a Schema

Next topic: Transaction Models