Preface:

Partition is a kind of table design mode, generally speaking, table partition is a large table, according to the conditions of the partition into several small tables. But to an application, a partitioned table is the same as an unpartitioned table. In other words, partitioning is transparent to the application, just a rearrangement of data by the database. This article gives you the content is about MySQL partition table introduction and use scenarios, there is a need for friends can refer to it, I hope to help you.

1. Partition purpose and type

The PARTITION BY clause can be used to define the data stored in each PARTITION. When the query is executed, the optimizer filters the 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 and only needs to find the partitions that contain the data we need.

Another purpose of partitioning is to separate data into different tables at a coarser granularity. This keeps related data together and is also handy when we want to batch delete entire partitions at once.

Here are four common partition types:

  • RANGE partition: Most commonly used to assign multiple rows to a partition based on column values belonging to a given continuous RANGE. The most common is based on the time field.
  • LIST partition: A LIST partition is similar to a RANGE partition, except that LIST is a LIST of enumerated values and RANGE is a collection of consecutive interval values.
  • HASH partition: A partition selected based on the return value of a user-defined expression computed using 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 partitioning: Similar to HASH partitioning, except that KEY partitioning supports only one or more columns and the MySQL server provides its own HASH function. One or more columns must contain integer values.

Of the above four partition types, the RANGE partition is the most commonly used. By default, the VALUES LESS THAN attribute is used, that is, each partition does not include the specified value.

2. Partition operation example

This section uses the RANGE partition as an example to describe how to partition a table.

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 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: 0Copy the code

After creation, you can see that each partition corresponds to one IBD file. In this table, the YEAR function is used to fetch the years in DATE and convert them to integers. The years less than 1990 are stored in partition P0, the years less than 1995 are stored in partition P1, and so on. Note that each partition is defined from lowest to highest. In order to prevent the inserted data from failing to find the corresponding partition, we should create a new partition in time. Other operations on partition maintenance continue below.

Mysql > SELECT * FROM tr PARTITION (p2); +------+-------------+------------+ | id | name | purchased | +------+-------------+------------+ | 2 | alarm clock | 1997-11-05 | | | 10 lava lamp | 1998-12-25 | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the 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: 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: 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: Alter table tr truncate partition P0; Alter table tr drop partition P1; alter table tr drop partition p1; Query OK, 0 rows affected (0.06 SEC) Records: 0 Duplicates: 0 Warnings: Mysql > CREATE TABLE 'tr_archive' (-> 'id' INT, -> 'name' VARCHAR(50), -> `purchased` DATE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, Alter table tr exchange PARTITION P2 with table tr_archive; alter table tr exchange PARTITION p2 with table tr_archive; Query OK, 0 rows affected (0.13 SEC)Copy the code

3. Zoning precautions and application scenarios

In fact, there are many restrictions on the use of partition tables and matters needing attention, refer to the official documentation, briefly summarize the following points:

  • The partition field must be of integer type or an expression that resolves to integer.
  • You are advised to set the partition field to NOT NULL. If the partition field of a row is NULL, the row will be allocated to the smallest partition in the RANGE partition.
  • If a primary key or unique key exists in a MySQL partition, the partition column must be included in it.
  • 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 increment columns.

As you can see from the introduction above, partitioned tables are suitable for some logging tables. This type of table is characterized by large amount of data, and has hot and cold data distinction, data can be archived according to the time dimension. Partitioned tables are preferred because they can be maintained on separate partitions and are easier to archive.

4. Why aren’t partition tables used

In our project development, partition tables are rarely used. Here are a few reasons:

  • The selection of partition fields is limited.
  • If the partition key is not selected, all partitions may be scanned, which does not improve the efficiency.
  • If data is unevenly distributed and partition sizes vary greatly, performance improvement may be limited.
  • The transformation of the common table component table is more cumbersome.
  • Partitions need to be maintained on an ongoing basis, such as adding partitions for June by June.
  • Increased learning costs and unknown risks.

Conclusion:

If you want to use a partition table, it is recommended to make a good plan in advance, in the initialization time that is to create a partition table and make a maintenance plan, it is more convenient to use properly, especially with historical data archiving requirements of the table, using a partition table will make archiving more convenient. Of course, there is a lot more to partition table content, if you are interested in the official documentation, the official documentation has a lot of examples.

Reference:

  • Dev.mysql.com/doc/refman/…
  • www.jianshu.com/p/b61d767f4…