PolarDB:Common table sharding

How it works

PolarDB-X determines how to distribute each table based on whether you specify a MySQL partition definition at table creation time:

When to use

Common table sharding works best when:

• Your application uses a large number of databases and tables.

• Most tables have few or no JOIN relationships with each other.

If your tables have frequent cross-table JOINs, review Limitations before enabling this feature.

Create a database with common table sharding

To enable common table sharding, create a database in AUTO mode with DEFAULT_SINGLE='on':

CREATE DATABASE autodb1 MODE='auto' DEFAULT_SINGLE='on';

Database creation parameters

Verify the database was created correctly:

mysql> SHOW CREATE DATABASE autodb1;

Expected output:

+----------+------------------------------------------------------------------------------------------------------------------+
| DATABASE | CREATE DATABASE                                                                                                  |
+----------+------------------------------------------------------------------------------------------------------------------+
| autodb1  | CREATE DATABASE `autodb1` CHARSET = `utf8mb4` COLLATE = `utf8mb4_general_ci` MODE = 'auto' DEFAULT_SINGLE = 'on' |
+----------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Create non-partitioned tables

Create tables in autodb1 without specifying a partition scheme. PolarDB-X automatically shards and distributes them across DNs.

CREATE TABLE sin_t1(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  birthday datetime,
  primary key(id)
);

CREATE TABLE sin_t2(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  birthday datetime,
  primary key(id)
);

CREATE TABLE sin_t3(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  birthday datetime,
  primary key(id)
);

CREATE TABLE sin_t4(
  id bigint not null auto_increment,
  bid int,
  name varchar(30),
  birthday datetime,
  primary key(id)
);

Verify table distribution

Use SHOW TOPOLOGY FROM <table_name> to confirm which DN each table was assigned to.

sin_t1:

SHOW TOPOLOGY FROM sin_t1\G

           ID: 0
   GROUP_NAME: AUTODB1_P00000_GROUP
   TABLE_NAME: sin_t1_HnMx
PARTITION_NAME: p1
SUBPARTITION_NAME:
  PHY_DB_NAME: autodb1_p00000
       DN_ID: polardbx-storage-0-master
STORAGE_POOL_NAME: _default

sin_t2:

SHOW TOPOLOGY FROM sin_t2\G

           ID: 0
   GROUP_NAME: AUTODB1_P00001_GROUP
   TABLE_NAME: sin_t2_IT7l
PARTITION_NAME: p1
SUBPARTITION_NAME:
  PHY_DB_NAME: autodb1_p00001
       DN_ID: polardbx-storage-1-master
STORAGE_POOL_NAME: _default

sin_t3:

SHOW TOPOLOGY FROM sin_t3\G

           ID: 0
   GROUP_NAME: AUTODB1_P00000_GROUP
   TABLE_NAME: sin_t3_HmtN
PARTITION_NAME: p1
SUBPARTITION_NAME:
  PHY_DB_NAME: autodb1_p00000
       DN_ID: polardbx-storage-0-master
STORAGE_POOL_NAME: _default

sin_t4:

SHOW TOPOLOGY FROM sin_t4\G

           ID: 0
   GROUP_NAME: AUTODB1_P00001_GROUP
   TABLE_NAME: sin_t4_ab7e
PARTITION_NAME: p1
SUBPARTITION_NAME:
  PHY_DB_NAME: autodb1_p00001
       DN_ID: polardbx-storage-1-master
STORAGE_POOL_NAME: _default

The results show that PolarDB-X distributed the four tables across two DNs: sin_t1 and sin_t3 reside on polardbx-storage-0-master, while sin_t2 and sin_t4 reside on polardbx-storage-1-master.

Limitations

JOIN performance

After common table sharding, JOIN operations across sharded tables may be slower. Because data is distributed across different DNs, JOINs may no longer be efficiently pushed down to individual DNs.

To minimize this impact, use common table sharding for tables with few or no JOIN relationships.

Uneven storage distribution

Non-partitioned tables are randomly distributed, so the data volume per shard can vary. Storage space used on individual DNs may differ as a result.