Best Practices Guide for MySQL Partitioned Tables

Preface:

Partitioning is a table design pattern. Generally speaking, table partitioning is to divide a large table into several small tables based on conditions. But for the application, the partitioned table is the same as the non-partitioned table. In other words, partitioning is transparent to the application and is just a reorganization of data by the database. This article introduces partition tables in MySQL and their usage scenarios. Friends in need can refer to it. I hope it will be helpful to you.

1. Purpose and type of partition

When creating a table in MySQL, you can use the PARTITION BY clause to define the data stored in each partition. When executing a query, the optimizer filters out partitions that do not have the data we need based on the partition definition, so that the query does not need to scan all partitions, but only needs to find the partition that contains the required data.

Another purpose of partitioning is to store data in different tables at a coarser granularity. Doing so can store related data together. In addition, it will be convenient when we want to delete the data of an entire partition in batches at one time.

The following briefly introduces four common partition types:

• RANGE partitioning: The most commonly used, assigning multiple rows to partitions based on column values ​​belonging to a given continuous interval. The most common is based on the time field.
• LIST partition: LIST partition is similar to RANGE partition, the difference is that LIST is a collection of enumeration value lists, and RANGE is a collection of continuous interval values.
• HASH partitioning: A partition is selected based on the return value of a user-defined expression that is calculated using the column values ​​of the rows to be inserted into the table. This function can contain any expression valid in MySQL that produces a nonnegative integer value.
• KEY partitioning: Similar to HASH partitioning, the difference is that KEY partitioning only supports the calculation of one or more columns, and the MySQL server provides its own hash function. One or more columns must contain integer values.
  

Among the above four partition types, RANGE partitioning is the most commonly used. The characteristic of RANGE partitioning is that the ranges of multiple partitions must be continuous but cannot overlap. By default, the VALUES LESS THAN attribute is used, that is, each partition does not include the specified value.

2. Partition operation example

This section takes RANGE partitioning as an example to introduce operations related to partition tables.

# Create a partitioned tablemysql> CREATE TABLE `tr` (
 -> `id` INT, 
 -> `name` VARCHAR(50), 
 -> `purchased` DATE
 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 -> PARTITION BY RANGE( YEAR(purchased) ) (
 -> PARTITION p0 VALUES LESS THAN (1990),
 -> PARTITION p1 VALUES LESS THAN (1995),
 -> PARTITION p2 VALUES LESS THAN (2000),
 -> PARTITION p3 VALUES LESS THAN (2005),
 -> PARTITION p4 VALUES LESS THAN (2010),
 -> PARTITION p5 VALUES LESS THAN (2015)
 -> );
Query OK, 0 rows affected (0.28 sec)

# Insert datamysql> INSERT INTO `tr` VALUES
 -> (1, 'desk organiser', '2003-10-15'),
 -> (2, 'alarm clock', '1997-11-05'),
 -> (3, 'chair', '2009-03-10'),
 -> (4, 'bookcase', '1989-01-10'),
 -> (5, 'exercise bike', '2014-05-09'),
 -> (6, 'sofa', '1987-06-05'),
 -> (7, 'espresso maker', '2011-11-22'),
 -> (8, 'aquarium', '1992-08-04'),
 -> (9, 'study desk', '2006-09-16'),
 -> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0

After creation, you can see that each partition corresponds to an ibd file. The above creation statement is easy to understand. In this partition table, the year in the DATE date is extracted through the YEAR function and converted into an integer. Years less than 1990 are stored in partition p0, years less than 1995 are stored in partition p1, and so on. Note that each partition is defined in order from lowest to highest. In order to prevent the inserted data from reporting an error due to not finding the corresponding partition, we should create a new partition in time. The following will show other operations related to partition maintenance.

# View the data of a partitionmysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

# Add partitionmysql> alter table tr add partition(
 -> PARTITION p6 VALUES LESS THAN (2020)
 -> );
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

# Split partitionmysql> alter table tr reorganize partition p5 into(
 -> partition s0 values ​​less than(2012),
 -> partition s1 values ​​less than(2015)
 -> );
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

# Merge partitionsmysql> alter table tr reorganize partition s0,s1 into ( 
 -> partition p5 values ​​less than (2015) 
 -> );
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

# Clear the data of a partition mysql> alter table tr truncate partition p0;
Query OK, 0 rows affected (0.11 sec)

# Delete partition mysql> alter table tr drop partition p1;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

# Exchange partition # First create an exchange table with the same structure as the partition table mysql> CREATE TABLE `tr_archive` (
 -> `id` INT, 
 -> `name` VARCHAR(50), 
 -> `purchased` DATE
 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.28 sec)
# Execute exchange partition mysql> alter table tr exchange PARTITION p2 with table tr_archive;
Query OK, 0 rows affected (0.13 sec)

3. Partition considerations and applicable scenarios

In fact, there are many restrictions and precautions when using partition tables. Referring to the official documentation, a few points are briefly summarized as follows:

• The partition field must be of integer type or an expression that resolves to an integer.
• It is recommended to set the partition field to NOT NULL. If the partition field of a row of data is null, in RANGE partitioning, the row of data will be divided into the smallest partition.
• If there is a primary key or unique key in MySQL partitioning, the partitioning column must be included in it.
• Innodb partitioned tables do not support foreign keys.
• Changing the sql_mode setting may affect the performance of partitioned tables.
• Partitioned tables do not affect auto-increment columns.

From the above introduction, we can see that partition tables are suitable for some logging tables. This type of table is characterized by a large amount of data, distinction between cold and hot data, and data archiving according to the time dimension. This type of table is more suitable for using partition tables, because partition tables can maintain separate partitions, which is more convenient for data archiving.

4. Why partition tables are not commonly used

In our project development, partition tables are rarely used. Here are a few reasons:

• The choice of partition fields is restricted.
• If the query does not use the partition key, all partitions may be scanned, and efficiency will not be improved.
• If the data is unevenly distributed and the partition sizes vary greatly, the performance improvement may be limited.
• It is rather complicated to transform a normal table into a partitioned table.
• The partitions need to be maintained continuously, for example, a new partition for June needs to be added before June.
• Increased learning costs and unknown risks.
  

Summarize:

This article introduces MySQL partitioning in detail. If you want to use partitioned tables, it is recommended to plan ahead, create partitioned tables during initialization, and make maintenance plans. It is quite convenient if used properly, especially for tables that require historical data archiving. Using partitioned tables will make archiving more convenient. Of course, there is a lot more content about the partition table. Students who are interested can look for the official documentation, which contains a large number of examples.

refer to:

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

https://www.jb51.net/article/187690.htm

Well, this is the end of this article on the best practices guide for MySQL partitioned tables. For more relevant MySQL partitioned table practices, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!