PolarDB:Usage instructions

Background information

Multi-master sharded tables in PolarDB for MySQL are a new type of cloud-native database. They support automatic horizontal splitting, hybrid shared storage, and a shared-nothing architecture. Multi-master sharded tables also automatically distribute data across multiple database clusters based on preset sharding rules. This allows each node to handle both read and write operations and achieve true distributed processing power.

Through horizontal splitting, PolarDB can effectively scale out databases and linearly increase the overall concurrent throughput. The architecture of multi-master sharded tables also combines the benefits of on-demand resource allocation from shared storage databases to ensure maximum resource utilization.

Prerequisites

• The PolarDB for MySQL cluster must be a Multi-master Cluster (Limitless) Edition that runs kernel version 8.0. For more information, see Custom purchase and Purchase a subscription cluster.

• A privileged account must be created. For more information, see Create and manage database accounts.

• You must be connected to the database cluster. For more information, see Connect to a database cluster.

DDL statements

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
| DIST_DB [=] {'Y' | 'N'}
}

CREATE DATABASE test1 DIST_DB='Y';

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [dbdistribution_options]

dbdistribution_options:
    DBDISTRIBUTION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [(dbdistribution_definition [, dbdistribution_definition] ...)]

dbdistribution_definition:
    DBDISTRIBUTION distribution_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

CREATE database test;
CREATE TABLE t1(
 id bigint NOT NULL, 
 name varchar(30), 
 PRIMARY key(id)
);

CREATE TABLE dist1(
 id bigint NOT NULL, 
 c1 int, 
 c2 varchar(30), 
 PRIMARY key(id)
) dbdistribution BY hash(id);

CREATE TABLE t1 (
    id INT NOT NULL,
    store_id INT,
    partion_no INT,
    PRIMARY key(id, partion_no)
) DBDISTRIBUTION BY LIST(partion_no) (
  DBDISTRIBUTION a VALUES IN (1),
  DBDISTRIBUTION b VALUES IN (2)
);

CREATE TABLE t3 (
    id INT NOT NULL,
    store_id INT,
    partion_no varchar(20),
    PRIMARY key(id, partion_no)
) DBDISTRIBUTION BY LIST COLUMNS (partion_no) (
  DBDISTRIBUTION a values in ('id_1'),
  DBDISTRIBUTION b values in ('id_2')
);

Distributed query

For SQL queries that do not have a filter condition on the distributed key or that span multiple database or table shards, you can route them to a global read-only node. The node automatically aggregates data from all partitions. This lets you read all data without extra storage or synchronization links.

Global auto-increment column

A globally unique sequence is supported to generate values for a natural number sequence. However, these values are not guaranteed to be consecutive or monotonically increasing. It uses a batch allocation method where the primary node allocates a range of values each time. Therefore, when multiple primary nodes insert data at the same time, the generated auto-increment IDs may not be consecutive. Each time a batch is retrieved from the storage engine, the system first retrieves the last allocated ID and then retrieves a new batch of IDs to define a range.

CREATE TABLE t1( id int NOT NULL auto_increment, c1 int, c2 int, PRIMARY key(id, c1)) dbdistribution BY RANGE (c1) ( dbdistribution p0 VALUES less than (100), dbdistribution p1 VALUES less than (1000), dbdistribution p2 VALUES less than (10000), dbdistribution p3 VALUES less than (20000) );
Query OK, 0 rows affected (0.26 sec)

INSERT INTO t1 (c1, c2) VALUES (1, 1);
Query OK, 1 row affected (0.04 sec)

Development limitations

The following DDL syntax limitations apply only to multi-master sharded tables: