Determine the number of partitions - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to determine the number of partitions in a table partitioned by using the HASH, LIST DEFAULT HASH, or RANGE partitioning method.

HASH partitioning

When you create a partitioned table, you can try to create different numbers of partitions and select the optimal number that can distribute data as evenly as possible. The following sample commands can help you evaluate different numbers of partitions:

Run the following command to view the data volume of each partition after a table is split into 10 partitions by ID:
```
select id%10 as part_id , count(1) from table_name group by id%10; 
```

Run the following command to view the data volume that corresponds to each ID:

select id, count(1) as cnt from table_name group by shop_id order by cnt desc;

Note

You can use the same partition structure for different partitioned tables to facilitate joins of these tables.
When you plan a table partition structure, consider the requirements for partitioned tables in scenarios that involve a growing business volume.
You can run the following command to view the approximate data volume of each partition in a partitioned table:
```
select * from mysql.innodb_table_stats where table_name like 'table_name#%' order by n_rows desc;
```

LIST DEFAULT HASH partitioning

For a list default hash partitioned table, such as a multi-tenant business table of SaaS, you can refer to the following solutions to determine the number of partitions:

One tenant uses one list partition, or multiple tenants share one list partition.
Evaluate the data volume. We recommend that you partition a table if it exceeds 1 billion rows or 1 TB of data. Make sure that the size of a single partition does not exceed 64 TB, and plan partitions to accommodate future business growth.
You can allocate a dedicated LIST partition to a large tenant, group tenants with similar data sizes into a shared LIST partition, and use the DEFAULT HASH partition to handle the remaining tenants. For example, if tenant 1 has a data volume of 1 billion rows, allocate a dedicated LIST partition to it. Tenants 2 through 11 have smaller data volumes, such as 100 million, 80 million, ..., 50 million rows. These 10 tenants can share a single LIST partition. For smaller tenants, for exmaple, 10,000 tenants with a total data volume of 9 billion rows, their data can be stored in the DEFAULT HASH partition. The DEFAULT HASH partition can be further divided into 9, 11, or 13 sub-partitions.
Try to create different numbers of partitions and select the most appropriate number.
If an integer that is used as the number of partitions cannot meet the requirements for even data distribution, you can use a prime number, such as 7 or 13.

Note

You can use the same partition structure for different partitioned tables to facilitate joins of these tables.
When you plan a table partition structure, consider the requirements for partitioned tables in scenarios that involve a growing business volume.
You can run the following command to view the approximate data volume of each partition in a partitioned table:
```
select * from mysql.innodb_table_stats where table_name like 'table_name#%' order by n_rows desc;
```

RANGE partitioning

In most cases, RANGE partitioning is implemented by day, month, and year. The data volume of each partition is not strictly required and is mainly determined by the actual business requirements.