Shulou(Shulou.com)06/01 Report--

What the editor wants to share with you this time is to explain the MySQL partition table in detail. The article is rich in content. Interested friends can learn about it. I hope you can get something after reading this article.

Preface

In the recent project, we need to save a large amount of data, and the data is valid. In order to provide query efficiency and quickly delete expired data, we choose the partition mechanism of MySQL. Partition the data by time.

Partition Typ

Range partitions: most commonly, multiple rows are assigned to partitions based on column values that belong to a given contiguous interval. The most common is based on the time field. Partition-based columns are preferably integers, and date-based columns can be converted to integers using functions. List partition: the LIST partition is similar to the RANGE partition, except that LIST is a collection of enumerated values, and RANGE is a collection of consecutive interval values. Hash partition: allocates data to different partitions based on a given number of partitions. HASH partitions can only HASH integers, and non-shaping fields can only be converted to integers by expression. Key partition: KEY partition is actually similar to HASH partition, the difference is as follows: KEY partition allows multiple columns, while HASH partition allows only one column. If there is a primary key or unique key, the partitioned column in the key can be 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 a column-based expression. The algorithm of KEY partition is different from that of HASH partition. PARTITION BY HASH (expr), the object with MOD value is the value returned by expr, while PARTITION BY KEY (column_list) is based on the MD5 value of the column.

Partition command

Create 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 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 corresponds to an ibd file.

Partition table

New partition

Alter table access_log add partition (partition p4 values less than (to_days ('20190105')

Delete partition

Alter table access_log drop partition p1

Split 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 Partition

Alter table access_log reorganize partition s0J S1 into (partition p4 values less than (to_days ('20190105')

Matters needing attention

If there is a primary key or unique key in the MySQL partition, the partition column must be included (otherwise, when judging the primary key or unique, all partitions need to be scanned) the partition field cannot be NULL, otherwise how to determine the partition scope, so the maximum number of partitions in NOT NULL cannot exceed 1024. Foreign keys can only partition the integer columns of the data table. Or the data column can be converted into an integer column partition table through the partition function without affecting the self-incrementing column.

common problem

A PRIMARY KEY must include all columns in the table's partitioning function: in this way, you can determine whether the primary key is unique and can be done within a single partition, otherwise you need to cross all partitions and MAXVALUE can only be used in last partition definition:RANGE tables cannot be partitioned with MAXVALUE partitions, otherwise you cannot add partitions. Or it may have to be rezoned.

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 range of the partition does not contain the values of all possible records, if you think it is well written after reading this article on detailed interpretation of the MySQL partition table You can share it with more people.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.