Shulou(Shulou.com)05/31 Report--

This article introduces you how to use the MySQL partition table, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Partitioning is a design pattern of tables. Generally speaking, table partitioning is to divide a large table into several small tables according to conditions. But for applications, partitioned tables are the same as non-partitioned tables. In other words, partitioning is transparent to the application, just the rearrangement of the data by the database.

MySQL can define the data stored in each partition by using the PARTITION BY clause when creating the table. When executing the query, the optimizer filters those partitions that do not have the data we need according to the partition definition, so that the query does not need to scan all partitions, but only needs to find the partitions that contain the data we need.

Another purpose of partitioning is to store data in different tables according to a thicker granularity. By doing so, we can store the relevant data together, and it will be convenient when we want to delete the data of the entire partition in bulk.

Here are four common partition types:

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.

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: a partition that is selected based on the return value of a user-defined expression that uses the column values of the rows to be inserted into the table. This function can contain any valid expression in MySQL that produces a non-negative integer value.

KEY partitions: similar to partitioning by HASH, except that KEY partitions only support computing 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 partition, or range partition, is the most commonly used. The characteristic of RANGE partitions is that the ranges of multiple partitions should be continuous, but cannot overlap, and the VALUES LESS THAN attribute is used by default, that is, each partition does not include the specified value.

two。 Example of partition operation

This section takes the RANGE partition as an example to describe the operations related to the partition table below.

# create partition table mysql > CREATE TABLE `tr` (- > `id` INT,-> `name` VARCHAR (50),-> `uploased` DATE-> 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 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 by the YEAR function and converted to an integer. The year less than 1990 is stored in partition p0, the year less than 1995 is stored in partition p1, and so on. Note that each partition is defined from the lowest to the highest. In order to prevent the inserted data from reporting errors because the corresponding partition cannot be found, we should create a new partition in a timely manner. Let's continue to show other operations about partition maintenance.

# View the data of 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 partition 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: split partition 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: merge partition mysql > alter table tr reorganize partition s0mage S1 into (- > partition p5 values less than (2015)->) Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: clear the data of a partition mysql > alter table tr truncate partition p0 boot 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: swap partition # first create an exchange table with the same structure as the partition table mysql > CREATE TABLE `tr_ archive` (- > `id` INT,-> `name` VARCHAR (50),-> `uploased` DATE->) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.28 sec) # execute exchange swap partition mysql > alter table tr exchange PARTITION p2 with table tr_archive Query OK, 0 rows affected (0.13 sec) 3. Zoning considerations and applicable scenarios

In fact, there are many restrictions and matters needing attention in the use of partition tables. Refer to the official documents and briefly summarize the following points:

The partition field must be an integer type or an expression that resolves to an integer.

It is recommended to set the partition field to NOT NULL. If a row data partition field is null, the row data will be divided into the smallest partition in the RANGE partition.

If there is a primary or unique key in the MySQL partition, the partition column must be included in it.

Innodb partitioned tables do not support foreign keys.

Changing the sql_mode schema may affect the performance of the partitioned table.

Partitioned tables do not affect self-incrementing columns.

As you can see from the above introduction, partitioned tables apply to some logging tables. This kind of table is characterized by a large amount of data and a distinction between hot and cold data, which can be archived according to the time dimension. This type of table is more suitable for using partitioned tables because partitioned tables can maintain separate partitions and are more convenient for data archiving.

4. Why partition tables are not commonly used

Partition tables are rarely used in our project development. Here are a few reasons:

The selection of partition fields is limited.

If the query does not leave the partition key, all partitions may be scanned and the efficiency will not be improved.

If the data is unevenly distributed, the partition size varies greatly, and the performance improvement may be limited.

It is tedious to transform an ordinary table into a partition table.

There is a need for continuous maintenance of partitions, such as adding June partitions by June.

There is an unknown risk to increase the cost of learning.

If you want to use partition tables, it is recommended to plan ahead and create partition tables and make maintenance plans at initialization. It is convenient to use them properly, especially for tables with historical data archiving requirements. Using partition tables will make archiving more convenient.

On how to use the MySQL partition table to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.