SoFunction
Updated on 2024-11-17

Best Practices Guide for MySQL Partitioned Tables

Preface:

Partitioning is a table design pattern, commonly known as table partitioning is a large table, according to the conditions of the partition into a number of small tables. But for the application, the partitioned table is the same as the unpartitioned table. In other words, partitioning is transparent to the application, only the database for the data reorganization. This article brings you content about the introduction of partitioned tables in MySQL and the use of scenarios, friends who need it can refer to it, I hope it will be helpful to you.

1. Purpose of zoning and types of zoning

MySQL allows you to define the data to be stored in each partition by using the PARTITION BY clause when creating a table. When the query is executed, the optimizer filters the partitions that do not have the data we need based on the partition definition, so that the query does not need to scan all the partitions, but only the ones that contain the data we need.

Another purpose of partitioning is to store data in separate tables at a coarser granularity. This allows us to store related data together, plus it makes it easier when we want to batch delete the entire partition at once.

Below is a brief description of the four common types of partitions:

  • RANGE Partitioning: most commonly used to assign multiple rows to partitions based on column values belonging to a given contiguous interval. Most commonly based on the time field.
  • LIST partitioning: LIST partitioning is similar to RANGE partitioning, with the difference that LIST is a collection of enumerated lists of values and RANGE is a collection of consecutive interval values.
  • HASH Partitioning: Partitioning for selection based on the return value of a user-defined expression that is computed using the column values of those rows that will be inserted into the table. This function can contain any expression valid in MySQL that produces a non-negative integer value.
  • KEY partitioning: similar to partitioning by HASH, the difference is that KEY partitioning only supports calculating one or more columns and the MySQL server provides its own hash function. One or more columns must contain integer values.

Of the four partition types mentioned above, RANGE partitions, or range partitions, are the most commonly used.RANGE partitions are characterized by multiple partitions with contiguous, but not overlapping, ranges, and by default use the VALUES LESS THAN attribute, which means that each partition excludes that value specified.

2. Example of partition operation

This section of the content of the RANGE partition as an example, to introduce the partition table related operations.

# Create partitioned tables
mysql> 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 data
mysql> 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 will correspond to 1 ibd file. Above create statement is still very good to understand, in this partition table, through the YEAR function to take out the DATE date in the year and converted to an integer, the year is less than 1990 is stored in the partition p0, less than 1995 is stored in the partition p1, and so on. Please note that the definition of the order of each partition is from the lowest to the highest. In order to prevent the inserted data from reporting an error because the corresponding partition cannot be found, we should create a new partition in time. The following section continues to show other operations on partition maintenance.

# View data on a partition
mysql> 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 partitions
mysql> 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 partitioning
mysql> 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 partitions
mysql> 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

# Empty the data in 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

# Switched partitions
# Start by creating a swap 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)
# Perform exchange swap partitioning
mysql> alter table tr exchange PARTITION p2 with table tr_archive;
Query OK, 0 rows affected (0.13 sec)

3. Precautions for Partitioning and Applicable Scenarios

In fact, there are many restrictions on the use of partitioned tables and matters needing attention, refer to the official document, briefly summarize a few points as follows:

  • The partition field must be an integer type or an expression that resolves to an integer.
  • The partition field is recommended to be set to NOT NULL. If the partition field of a row of data is null, the row will be divided into the smallest partition in the RANGE partition.
  • MySQL partitions must contain partition columns if a primary or unique key exists in the partition.
  • Innodb partitioned tables do not support foreign keys.
  • Changing the sql_mode mode may affect the performance of partitioned tables.
  • Partitioned tables do not affect self-incrementing columns.

As you can see from the above introduction, partitioned tables are suitable for some logging tables. These types of tables are characterized by large data volumes and have a distinction between hot and cold data, which can be archived according to the time dimension. This type of table is more suitable for the use of partitioned tables, because partitioned tables can be maintained on a separate partition, more convenient for data archiving.

4. Why partitioned tables are not commonly used

In our project development, partitioned tables are actually rarely used, the following briefly explains a few reasons:

  • The selection of partition fields is limited.
  • If the query does not go to 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 widely, the likelihood of being able to improve is limited.
  • Normal tables are more cumbersome to transform into partitioned tables.
  • Ongoing maintenance of partitions is required, such as adding a new partition for June by June.
  • Increased learning costs and unknown risks.

Summary:

In this article, a more detailed introduction to the MySQL partition related content, if you want to use the partition table, it is recommended that early planning, in the initialization of the time that is the creation of the partition table and the development of a maintenance plan, used properly or more convenient, especially with the historical data archiving needs of the table, the use of partition tables will make the archiving more convenient. Of course, there is a lot more about partitioned tables, interested students can look for official documents, official documents have a lot of examples.

Reference:

/doc/refman/5.7/en/

https:///article/

Well, this article on MySQL Partitioning Best Practices Guide describes this, for more information about MySQL Partitioning Practices, please search for my previous articles or continue to browse the following related articles I hope you will support me more in the future!