Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PolarDB Hands-on | PolarDB for MySQL: List Default Hash Partitioning

PolarDB Hands-on | PolarDB for MySQL: List Default Hash Partitioning

In this article, we will introduce a multi tenant business system solution with LIST DEFAULT HASH Partitioning of PolarDB MySQL.

Business Scenario

If you want to use the LIST rule to partition, but the values of partition key fields cannot be all enumerated, or there are many enumerated values, and the corresponding data volume is small. The data distribution complies with the principle of "two eight". 20% of the partition key values contain 80% of the data, and the remaining 80% of the partition key values contain 20% of the data. In this scenario, you can choose the LIST DEFAULT HASH partition type. 80% of the data is partitioned according to the LIST rule. The data that does not conform to the LIST rule is placed in the DEFAULT partitions which partitioned according to the HASH rule.

1

For example, for a multi tenant business system, the amount of user data generated by each tenant is uneven. You can partition the tenants with large amounts of data according to the LIST rule, and then the tenants with small amounts of data are divided into multiple partitions according to the HASH rule

TENANT_ID RECORDS. PARTITION
VIP_CUST_1 300billion p1
VIP_CUST_2 260billion p2
VIP_CUST_3 240billion p3
VIP_CUST_4 200billion p4
other custs 300billion p_others

For conventional database, the table will be created like this:

CREATE TABLE cust_orders
(
  customer_id   VARCHAR(36),
  year      VARCHAR(60),
  order_id     INT,
  order_content text
) PARTITION BY LIST COLUMNS(customer_id)
(
  PARTITION p1 VALUES IN ('VIP_CUST_1'),
  PARTITION p2 VALUES IN ('VIP_CUST_2'),
  PARTITION p3 VALUES IN ('VIP_CUST_3'),
  PARTITION p4 VALUES IN ('VIP_CUST_4'),
  PARTITION p_others VALUES IN ('CUST_5', 'CUST_6',...)
);

Problems with LIST Partition Solution

There are too many values in partition p_others, it is not convenient to write the create table sql, and the performence of query like the following will try to find the 'cust_xx' throught amount of values to make sure it is in partition p_others in the processing of the sql execution.

select * from cust_orders where customer_id='cust_xx';

Also, the following insert sql also need to make sure 'cust_new' is one member of the list values,

insert into cust_orders values ( 'cust_new',  '20221228', 232342, 'context' );

And, sometimes the list values cannot be all enumerated, so the partitions cannot be defined.

The Solution with List Default Hash

The new partition type List Default Hash is a new feature of innovation on PolarDB which can solve this kind of problem perfectly.

2

For List Default Hash partition type, the front partitions are the normal LIST partitions. Data not in the LIST partition is placed in the DEFAULT partition. If the DEFAULT partition is too large, it can be divided into multiple DEFAULT partitions according to HASH rules

The steps is based on that we have an PolarDB MySQL instance, and we connected a session to the instance.

Step 1: create the table cust_orders

create database test

create database test;
use test

create the table cust_orders with List Default Hash partitions.

CREATE TABLE cust_orders
(
  customer_id   VARCHAR(36),
  year      VARCHAR(60),
  order_id     INT,
  order_content text
) PARTITION BY LIST COLUMNS(customer_id)
(
  PARTITION p1 VALUES IN ('VIP_CUST_1'),
  PARTITION p2 VALUES IN ('VIP_CUST_2'),
  PARTITION p3 VALUES IN ('VIP_CUST_3'),
  PARTITION p4 VALUES IN ('VIP_CUST_4'),
  PARTITION p_others DEFAULT PARTITIONS 3
);

Step 2: insert some values into table cust_orders

insert into cust_orders  values ( 'cust_new',  '20221228', 232342, 'context' ); 
insert into cust_orders  values ( 'cust_new',  '20221228', 232342, 'context' );
insert into cust_orders  values ( 'cust_new1',  '20221228', 232342, 'context' );
insert into cust_orders  values ( 'cust_new2',  '20221228', 232342, 'context' );
insert into cust_orders  values ( 'cust_new3',  '20221228', 232342, 'context' );
insert into cust_orders  values ( 'cust_n3',  '20221228', 232342, 'context' );

Step 3: split partitions from default partitions

When the records of 'cust_new' increasing fast, and its records is so many that it can be an independent partition.

With REORGANIZE PARTITION, you can separate some values from the DEFAULT partition and add new LIST partition for them:

explain select * from cust_orders where customer_id='cust_new';

mysql> explain select * from cust_orders where customer_id='cust_new';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | cust_orders | p_others2  | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Now customer_id 'cust_new' is in partition p_others2;

Execute the REORGANIZE PARTITION SQL to separate the value 'cust_new' from the DEFAULT partition and add new LIST partition p5 for it.

 ALTER TABLE cust_orders 
REORGANIZE PARTITION p_others0, p_others1,p_others2 
INTO (
   PARTITION p5 VALUES IN ('cust_new'),
   PARTITION p_others DEFAULT PARTITIONS 2);

Show create table to see the table structure descriptions, we can see it has partition p5.

mysql> show create table cust_orders;
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
| cust_orders | CREATE TABLE `cust_orders` (
  `customer_id` varchar(36) DEFAULT NULL,
  `year` varchar(60) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `order_content` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!99990 800020201 PARTITION BY LIST  COLUMNS(customer_id)
(PARTITION p1 VALUES IN ('VIP_CUST_1') ENGINE = InnoDB,
 PARTITION p2 VALUES IN ('VIP_CUST_2') ENGINE = InnoDB,
 PARTITION p3 VALUES IN ('VIP_CUST_3') ENGINE = InnoDB,
 PARTITION p4 VALUES IN ('VIP_CUST_4') ENGINE = InnoDB,
 PARTITION p5 VALUES IN ('cust_new') ENGINE = InnoDB,
 PARTITION p_others DEFAULT PARTITIONS 2 ENGINE = InnoDB) */ |

Check whether the record with the customer_id 'cust_new' is in partition p5, and not in the default partitions.

mysql> select * from cust_orders partition(p5);
+-------------+----------+----------+---------------+
| customer_id | year     | order_id | order_content |
+-------------+----------+----------+---------------+
| cust_new    | 20221228 |   232342 | context       |
+-------------+----------+----------+---------------+
1 row in set (0.00 sec)

mysql> select * from cust_orders partition(p_others0);
+-------------+----------+----------+---------------+
| customer_id | year     | order_id | order_content |
+-------------+----------+----------+---------------+
| cust_nn3    | 20221228 |   232342 | context       |
| cust_n3     | 20221228 |   232342 | context       |
| cust_new1   | 20221228 |   232342 | context       |
| cust_new2   | 20221228 |   232342 | context       |
| cust_new3   | 20221228 |   232342 | context       |
+-------------+----------+----------+---------------+
5 rows in set (0.00 sec)

mysql> select * from cust_orders partition(p_others1);
Empty set (0.00 sec)

Step 4: Use REORGANIZE PARTITION to merge a LIST partition into the DEFAULT partitions:

mysql> explain select * from cust_orders where customer_id='cust_new';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | cust_orders | p5         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from cust_orders where customer_id='cust_new';
+-------------+----------+----------+---------------+
| customer_id | year     | order_id | order_content |
+-------------+----------+----------+---------------+
| cust_new    | 20221228 |   232342 | context       |
+-------------+----------+----------+---------------+

Execute the REORGANIZE PARTITION SQL to merge the LIST partition p5 into the DEFAULT partitions.

ALTER TABLE cust_orders
REORGANIZE PARTITION
 p5, p_others0, p_others1
INTO (
 PARTITION p_others DEFAULT PARTITIONS 2);

Check whether the record with the customer_id 'cust_new' is in the default partitions, and not in partition p5.

mysql> select * from cust_orders where customer_id='cust_new';
+-------------+----------+----------+---------------+
| customer_id | year     | order_id | order_content |
+-------------+----------+----------+---------------+
| cust_new    | 20221228 |   232342 | context       |
+-------------+----------+----------+---------------+
1 row in set (0.00 sec)

mysql> explain select * from cust_orders where customer_id='cust_new';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | cust_orders | p_others0  | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Step 5: Use the REORGANIZE PARTITION operation to change the number of DEFAULT partitions:

mysql> explain select * from cust_orders;
+----+-------------+-------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions                      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | cust_orders | p1,p2,p3,p4,p_others0,p_others1 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Now table cust_orders has 2 default partitions: p_others0,p_others1

Execute the REORGANIZE PARTITION SQL to change the number of DEFAULT partitions to 4.

ALTER TABLE  cust_orders
REORGANIZE PARTITION
  p_others0, p_others1
INTO(
  PARTITION p_others DEFAULT PARTITIONS 4);

After REORGANIZE PARTITION operation, table cust_orders has 4 default partitions: p_others0,p_others1,p_others2,p_others3

mysql> explain select * from cust_orders;
+----+-------------+-------------+-----------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions                                          | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+-----------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | cust_orders | p1,p2,p3,p4,p_others0,p_others1,p_others2,p_others3 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------+-----------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Demo

MySQL Database Developers POLARDB Tutorials partitioning PolarDB for MySQL Multi Tenant LIST DEFAULT HASH
0 1 0
Share on

Read previous post:

[Infographic] Highlights | Database New Feature in March 2023

Read next post:

PolarDB Hands-on | PolarDB for MySQL: Automatic Interval Partitioning

ApsaraDB

377 posts | 59 followers

You may also like

Comments

ApsaraDB

377 posts | 59 followers

Related Products

More Posts by ApsaraDB

See All