Manual partitioning - PolarDB - Alibaba Cloud Documentation Center

Scenarios

The manual partitioning feature of PolarDB-X is suitable for applications that require high performance, especially core applications with high concurrency and throughput. To use the manual partitioning feature, you must familiarize yourself with how distributed databases work. Manual partitioning allows you to horizontally partition tables in the dimension that best suits your service. Properly used, manual partitioning gives the extensibility and performance of distributed databases to full play.

Create a database that uses the manual partitioning feature

If you want to use the transparent distributed manual partitioning feature in PolarDB-X, you can use the SQL statement to create an AUTO database:

CREATE DATABASE autodb1 MODE='auto'

Types and usage of manually partitioned tables

The manual partitioning feature in PolarDB-X allows you to manually create three types of logical tables: non-partitioned table, broadcast table, and partitioned table.

The three types of tables have different physical table topologies and are suitable in different scenarios. The following table describes the tables:

Logical table type	Physical table topology	Scenario	Read and write loads
Non-partitioned table	A non-partitioned table corresponds to a physical table.	Small tables that have a small amount of data and low concurrency.	Reads and writes are concentrated in one data node.
Broadcast table	A broadcast table has an image in each data node, and the data between the images is always consistent.	Broadcast tables are suitable for tables that have a large number of reads and a small number of writes, such as configuration tables.	Read balance: Reads can be randomly allocated to different data nodes. Write amplification: You need to write data to images in all data nodes to maintain consistency.
Partitioned table	A partitioned table has multiple partitions and is distributed to multiple data nodes. Each partition corresponds to a physical table.	Partitioned tables are suitable for tables that have a large mount of data, high concurrency, and high throughput.	Reads and writes can be automatically routed to different data nodes based on the partition key column to balance loads.

Method 1: Manually specify partition schemes for all tables

Manually create a non-partitioned table

Syntax

CREATE TABLE xxx (...)
SINGLE

Example

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

Manually create a broadcast table

Syntax

CREATE TABLE xxx (...)
BROADCAST

Example

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

Manually create a partitioned table

For more information about how to create a partitioned table, see Manually create a partitioned table (AUTO mode).

Method 2: Manually disable the default automatic partitioning feature

Configure a global variable to disable the default automatic partitioning feature:

SET GLOBAL AUTO_PARTITION=false;

After you configure the global variable, you do not need to explicitly specify the SINGLE keyword when you create a non-partitioned table.