Automatic partitioning - PolarDB - Alibaba Cloud Documentation Center

This topic describes how automatic partitioning works and how to this feature.

How it works

The transparent distributed common table sharding feature of PolarDB-X partitions all non-partitioned tables, which are created without explicitly using the MySQL partition syntax, in a logical database. The following list describes how automatic partitioning works:

The tables are horizontally partitioned by the primary keys of the tables. If no primary keys are specified, the default primary key _drds_implicit_id_ is automatically created and used.
All indexes are converted into global indexes based on automatic partitioning rules and the tables are horizontally partitioned by the index columns.

Note

The default number of partitions after automatic partitioning cannot be manually modified.

Scenarios

This feature converts regular indexes into global indexes. This may reduce the write performance of tables, because distributed transactions are performed to maintain data consistency between global index tables and primary tables for write operations. Therefore, this feature is suitable for applications that have low performance requirements and are developed based on standalone MySQL databases, such as new applications that need to be quickly released. In most cases, this type of services require a small number of databases and tables and simple queries, such as point queries by primary key.

For more information about the suitable scenarios of the automatic partitioning feature, see Best practices.

Prerequisites

Only AUTO databases support the transparent distributed automatic partitioning feature. You must specify MODE='AUTO' in the SQL statement for creating a database.
```
CREATE DATABASE db_name MODE='AUTO';
```
By default, databases in AUTO mode use manual partitioning. To enable automatic partitioning, you must manually set the following global parameter:
```
SET GLOBAL AUTO_PARTITION = true;
```

Examples

Create a database that uses the automatic partitioning feature

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

CREATE DATABASE autodb2 MODE='AUTO';

The default working mode of AUTO library transparent distribution is manual partitioning. You must manually execute the following global switch to enable the automatic partitioning function:

SET GLOBAL AUTO_PARTITION=true;

Create multiple non-partitioned MySQL tables and automatically partition the tables

Execute the following SQL statement to create a table that contains a primary key index (idx_name):

CREATE TABLE auto_t1(
  id BIGINT NOT NULL auto_increment,
  bid INT,
  name VARCHAR(30),
  birthday datetime,
  PRIMARY KEY(id),
  INDEX idx_name(name)
);

View the complete statement for creating a database that uses the automatic partitioning feature:

SHOW FULL CREATE TABLE auto_t1;

Sample result:

CREATE PARTITION TABLE `auto_t1` (
	`id` bigint NOT NULL AUTO_INCREMENT,
	`bid` int DEFAULT NULL,
	`name` varchar(30) DEFAULT NULL,
	`birthday` datetime DEFAULT NULL,
	PRIMARY KEY (`id`),
	GLOBAL INDEX /* idx_name_$578c */ `idx_name` (`name`)
		PARTITION BY KEY(`name`,`id`)
		PARTITIONS 16,
	LOCAL KEY `_local_idx_name` (`name`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci
PARTITION BY KEY(`id`)
PARTITIONS 16

Note

The result shows that the regular index idx_name is converted into a global index by the system based on the automatic partitioning rules, and the primary key id is used as the partition key for partitioning.