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

Editor to share with you this time is to talk about the basic types of partition tables in MySQL, the article is rich in content, interested friends can learn about it, I hope you can get something after reading this article.

Overview of MySQL partition tables

As MySQL becomes more and more popular, the data stored in Mysql becomes larger and larger. In our daily work, we often come across a table that holds hundreds of millions or even billions of records. A large number of historical records are kept in these tables. Cleaning up these historical data is a big headache, because all the data are in a common table. So you can only enable one or more delete statements with where conditions to delete (the general where condition is time). This puts a lot of pressure on the database. Even if we delete these, the underlying data files are not getting smaller. The most effective way to deal with this kind of problem is to use partition tables. The most common method of partitioning is to partition by time. One of the biggest advantages of partitioning is that it can clean up historical data very efficiently.

Partition Typ

MySQL currently supports range partitions (RANGE), list partitions (LIST), hash partitions (HASH) and KEY partitions. Let's look at each partition one by one:

RANGE partition

Assign multiple rows to a partition 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. The to_days function is used in this example

CREATE TABLE my_range_datetime (id INT, hiredate DATETIME) PARTITION BY RANGE (TO_DAYS (hiredate)) (PARTITION p1 VALUES LESS THAN (TO_DAYS ('20171202')), PARTITION p2 VALUES LESS THAN (TO_DAYS ('20171203')), PARTITION p3 VALUES LESS THAN (TO_DAYS ('20171204')), PARTITION p4 VALUES LESS THAN (TO_DAYS ('20171205')), PARTITION p5 VALUES LESS THAN (TO_DAYS ('20171206')), PARTITION p6 VALUES LESS THAN (TO_DAYS ('20171207')) PARTITION p7 VALUES LESS THAN (TO_DAYS ('20171208')), PARTITION p8 VALUES LESS THAN (TO_DAYS ('20171209')), PARTITION p9 VALUES LESS THAN (TO_DAYS ('20171210')), PARTITION p10 VALUES LESS THAN (TO_DAYS ('20171211')) PARTITION p11 VALUES LESS THAN (MAXVALUE))

P11 is a default partition, and all records greater than 20171211 are in this partition. MAXVALUE is an infinite value. P11 is an optional partition. If we do not specify this partition in defining the table, we will receive an error when inserting data greater than 20171211.

When we execute the query, we must take the partition field with us. This allows you to use the partition clipping function

Mysql > insert into my_range_datetime select * from test;Query OK, 1000000 rows affected (8.15 sec) Records: 1000000 Duplicates: 0 Warnings: 0mysql > explain partitions select * from my_range_datetime where hiredate > = '20171207124503' and hiredate insert into my_range_timestamp select * from test;Query OK, 1000000 rows affected (13.25 sec) Records: 1000000 Duplicates: 0 Warnings: 0mysql > explain partitions select * from my_range_timestamp where hiredate > = '20171207124503' and hiredate

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.