PolarDB:Common table sharding

Overview

The transparent distributed common table sharding feature of PolarDB-X shards all tables in a logical database based on whether the MySQL partition syntax is explicitly used for creating tables. The following list and figure describe how common table sharding works:

• If the partition definition of logical tables is not explicitly specified in MySQL, the tables are used as a non-partitioned tables and randomly distributed to different data nodes of PolarDB-X to balance loads.

• If the partition definition of logical tables is explicitly specified in MySQL, the tables are used as partitioned tables and evenly distributed to different data nodes.

Scenarios

This feature shards all MySQL tables of the original service and automatically allocates the tables to data nodes. This resolves the bottleneck of single-server resources and balances loads. This feature is suitable for scenarios where a large number of databases and tables are used in your applications. This feature is more suitable for tables that have fewer join relationships.

Examples

Create a database that uses the common table sharding feature

If you want to use the common table sharding feature in PolarDB-X, you can use the following SQL statement to create a database:

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

View the complete statement for creating a database that uses the common table sharding feature:

mysql> show create database autodb1;
+----------+------------------------------------------------------------------------------------------------------------------+
| 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 multiple non-partitioned MySQL tables and automatically shard the tables

You can use the following SQL statements to create multiple tables in the my_autodb database:

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

View the topology of sharded tables

You can use the SHOW TOPOLOGY FROM #tableName statement to view the distribution of tables:

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

According to the results of the SHOW TOPOLOGY statements for the preceding tables, sin_t1 and sin_t3 are distributed to the polardbx-storage-0-master data node. sin_t2 and sin_t4 are distributed to the polardbx-storage-1-master data node.

Usage notes

After a common table is sharded across multiple data nodes, the performance of JOIN operations that involves the table may be affected. The data for the JOIN operation may be distributed across different DNs. As a result, the JOIN operation may no longer be efficiently pushed down to the DNs.

If you do not explicitly specify the MySQL partitoning scheme for a logical table, the table is randomly distributed across different nodes in the PolarDB-X instance. The amount of data in each table shard can vary. As a result, the storage space used on each node may differ.