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:

mysql> show topology from sin_t1\G
*************************** 1. row ***************************
               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
1 row in set (0.02 sec)

mysql> show topology from sin_t2\G
*************************** 1. row ***************************
               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
1 row in set (0.01 sec)

mysql> show topology from sin_t3\G
*************************** 1. row ***************************
               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
1 row in set (0.01 sec)

mysql> show topology from sin_t4\G
*************************** 1. row ***************************
               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
1 row in set (0.01 sec)

According to the show topology results of 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 tables are sharded and distributed to different data nodes, the join performance of some tables may change because the join operation that used to be performed on the same data node now cannot be directly performed on tables across data nodes.