PolarDB:Usage instructions

When to use multi-master sharded tables

Multi-master sharded tables are designed for workloads that have outgrown a single-node MySQL setup. Consider this architecture when:

• Write throughput has hit the limits of vertical scaling

• A single table is large enough that query performance is degrading

• Your application requires horizontal scale-out with linear throughput gains

Multi-master sharded tables use a shared-nothing architecture combined with on-demand resource allocation from shared storage, so each node operates independently while storage costs remain proportional to actual use.

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for MySQL cluster running the Multi-master Cluster (Limitless) Edition with kernel version 8.0. See Custom purchase and Purchase a subscription cluster

• A privileged account. See Create and manage database accounts

• An active connection to the cluster. 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 queries that lack a filter on the distributed key, or that span multiple database shards, route them to the global read-only node. The global read-only node aggregates data from all partitions automatically, so you can query across all shards without setting up extra storage or synchronization links.

Global auto-increment column

Multi-master sharded tables support a globally unique sequence for auto-increment columns. Key behavioral differences from standard MySQL single-node auto-increment:

• Globally unique, not guaranteed consecutive. Values are unique across all nodes, but gaps are expected.

• Batch allocation per primary node. Each primary node allocates a range of values at a time. When multiple primary nodes insert concurrently, the resulting IDs from different nodes may not be in order.

• Partition-local monotonicity. Values increase monotonically within a single partition, but not globally.

The following table compares auto-increment behavior between MySQL single-node and multi-master sharded tables.

Example: range-sharded table with auto-increment

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

Insert a row without specifying the auto-increment value:

INSERT INTO t1 (c1, c2) VALUES (1, 1);

Limitations

The following DDL operations are not supported for multi-master sharded tables.