Basic introductory tutorial on MySQL partition tables

Preface

In a recent project, we need to save a large amount of data, and this data has an expiration date. In order to improve query efficiency and quickly delete expired data, we chose the MySQL partitioning mechanism. Partition the data by time.

Partition Type

• Range partitioning: The most commonly used, assigning multiple rows to partitions based on column values ​​belonging to a given continuous interval. The most common one is based on the time field. The partition-based column is preferably an integer. If it is a date type, a function can be used to convert it to an integer.
• 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: Based on the given number of partitions, data is allocated to different partitions. Hash partitioning can only perform Hash operations on integers. Non-integer fields can only be converted into integers through expressions.
• Key partition: KEY partition is similar to HASH partition, with the following differences:
  • KEY partitioning allows multiple columns, while HASH partitioning allows only one column.
  • If there is a primary key or unique key, the partition column in the key can be left unspecified. The default is the primary key or unique key. If not, the column must be explicitly specified.
  • The KEY partition object must be a column, not an expression based on a column.
  • The algorithms for KEY partitioning and HASH partitioning are different. In PARTITION BY HASH (expr), the MOD value is the value returned by expr, while in PARTITION BY KEY (column_list), it is based on the MD5 value of the column.
    

Partition Commands

Create a partition

CREATE TABLE `access_log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `access_time` datetime NOT NULL,
 PRIMARY KEY (`id`,`access_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(access_time))
(PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) */;

After creation, you can see that each partition will correspond to an ibd file

Partition Table

Add a new partition

alter table access_log add partition(
 partition p4 values ​​less than (to_days('20190105'))
);

Deleting a Partition

alter table access_log drop partition p1;

Split a partition

alter table access_log reorganize partition p4 into(
 -> partition s0 values ​​less than(to_days('20190104')),
 -> partition s1 values ​​less than(to_days('20190105'))
 -> );

Merge partitions

alter table access_log reorganize partition s0,s1 into ( 
 partition p4 values ​​less than (to_days('20190105')) 
);

Precautions

• If there is a primary key or unique key in the MySQL partition, the partition column must be included in it (otherwise, all partitions need to be scanned to determine the primary key or unique key)
• The partition field cannot be NULL, otherwise how to determine the partition range, so try to use NOT NULL
• The maximum number of partitions cannot exceed 1024
• Foreign keys are not supported
• Only integer columns of the data table can be partitioned, or the data column can be converted into an integer column through the partition function
• Partition tables do not affect auto-increment columns
  

Frequently asked questions

• A PRIMARY KEY must include all columns in the table's partitioning function: In this way, the determination of whether the primary key is unique can be completed within a single partition, otherwise it needs to be completed across all partitions.
• MAXVALUE can only be used in last partition definition: A RANGE table cannot have a MAXVALUE partition after partitioning; otherwise, partitions cannot be added. Or you can just repartition it.
  

alter table access_log partition by range(to_days(access_time))(
partition p1 values ​​less than (to_days('20191202')), 
partition p2 values ​​less than (to_days('20191203')), 
 partition po values ​​less than (maxvalue)
)

• Table has no partition for value 737425: because the partition range does not contain all possible record values

refer to

• MySQL · Best Practices · Partition Table Basic Types
• Why don't Internet companies use MySQL partition tables?
• How to Resolve ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

Summarize

This is the end of this article about the basic introduction tutorial of MySQL partition table. For more relevant MySQL partition table content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!