Detailed explanation of the use and underlying principles of MySQL table partitions

What is a partition table

MySQL has supported partitioning since version 5.1. Partitioning is to divide the data of a table into multiple smaller and more manageable parts in some way, such as by month, but logically it is still a table.

Before the partition table appeared, all data was stored in one file. If the amount of data was too large, a large number of IO operations would inevitably be required when querying the data. After using the partition table, each partition stores different data. This not only reduces io. It can also speed up access to data;

To ensure the performance of MySQL, we recommend that a single MySQL table should not be too large. The recommendations are: a single table should be less than 2G, the number of records should be less than 10 million, and there should be 10 databases and 100 tables. If the number of records in a row is very small, then the number of records can be larger. Otherwise, the processing may start to become slow when the number of records reaches millions.

So, what should we do if the business volume is growing and the data reaches a bottleneck? In addition to using a distributed database, we can also divide the database and tables by ourselves, or use the partitioning function of MySQL to achieve it.

The partition table was created to support the concept of divide and conquer. The partition table is very useful, but many people still don't know it.
After a table is set as a partition table, a # sign is added to the file name of the data file .idb, indicating that this is a partition table;

Partition table application scenarios

1. The table is so large that it cannot be stored in memory, or only the last part of the table contains hot data, and the rest is historical data.
2. The data in the partition table is easier to maintain, and large amounts of data can be deleted in batches
3. Optimize, check, repair, etc. an independent partition
4. The data of the partition table can be distributed on different devices, never making efficient use of multiple hardware devices
5. Can back up and restore independent partitions;
   

Limitations of partitioned tables

1. A table can have a maximum of 1024 partitions, and 8196 partitions in versions 5.7 and above.
2. In the early days of MySQL, partitioning expressions had to be integers or expressions that returned integers. In MySQL 5.5, columns can be used directly for partitioning in some scenarios.
3. Partitioned tables cannot use external constraints
4. It is best not to modify the partition column
5. If the partitioning field has a primary key or unique index column, then all primary key columns and unique index columns must be included; like this:
   

-- Create partitions that must contain all primary keys create table user_11(
  id bigint(20) not null ,
  name varchar(20) ,
  age int(3),
	PRIMARY KEY (`id`,`age`)
)
-- Create partition partition by range columns(id,age)(
  partition p00 values ​​less than(6,30), -- values ​​less than 6 are in the P0 partition partition p11 values ​​less than(11,40), -- values ​​less than 11 are in the p1 partition partition p22 values ​​less than(16,50), -- values ​​less than 16 are in the p2 partition partition p33 values ​​less than (9999,9999) -- values ​​greater than 21 are in the p3 partition, or use a larger value);

-- Create partitions that must contain all unique keys create table user_22(
  id bigint(20) not null,
  name varchar(20) ,
  age int(3) not null ,
	unique key only_one_1(age,id )
)
-- Create partition partition by range columns(id,age)(
  partition p000 values ​​less than(6,30), -- values ​​less than 6 are in the P0 partition partition p111 values ​​less than(11,40), -- values ​​less than 11 are in the p1 partition partition p222 values ​​less than(16,50), -- values ​​less than 16 are in the p2 partition partition p333 values ​​less than (9999,9999) -- values ​​greater than 21 are in the p3 partition, or use a larger value);

Partition Type

1. Range Partitioning
2. List Partition
3. Column partitioning
4. Hash partition
5. Key partition
6. Subpartitions
   

Use of partition table

1. Range partitioning

In the following example, age is partitioned.

create table employees(
id bigint(20) not null,
age int(3) not null,
name varchar(20)
)
-- Create partition partition by range (age)(
  partition p0 values ​​less than(6), -- values ​​less than 6 are in the P0 partition partition p1 values ​​less than(11), -- values ​​less than 11 are in the p1 partition partition p2 values ​​less than(16), -- values ​​less than 16 are in the p2 partition partition p3 values ​​less than(21) -- values ​​less than 21 are in the p3 partition);

After creation, you can see the partition file in the data folder

[root@VM_0_5_centos test]# pwd
/var/lib/mysql/test
[root@VM_0_5_centos test]# ll
Total usage 8741504
-rw-rw---- 1 mysql mysql 61 October 31, 2018 db.opt
-rw-rw---- 1 mysql mysql 8614 August 1 21:30 employees.frm
-rw-rw---- 1 mysql mysql 32 August 1 21:30 employees.par
-rw-rw---- 1 mysql mysql 98304 August 1 21:30 employees#P#p0.ibd
-rw-rw---- 1 mysql mysql 98304 August 1 21:30 employees#P#p1.ibd
-rw-rw---- 1 mysql mysql 98304 August 1 21:30 employees#P#p2.ibd
-rw-rw---- 1 mysql mysql 98304 August 1 21:30 employees#P#p3.ibd

Because the age field can only be inserted with a number less than 21, if you insert a number 21, an error will be reported.

mysql> insert employees (id,name,age) values(1,'yexindong',21);
ERROR 1526 (HY000): Table has no partition for value 21

Therefore, in order to solve this problem, you can do this when creating a table. Use maxvalue for the maximum value. It is said that the value of maxvalue is 28 nines, that is, 9999999999999999999999999999999

create table employees(
id bigint(20) not null,
age int(3) not null,
name varchar(20)
)
-- Create partition partition by range (age)(
  partition p0 values ​​less than(6), -- values ​​less than 6 are in the P0 partition partition p1 values ​​less than(11), -- values ​​less than 11 are in the p1 partition partition p2 values ​​less than(16), -- values ​​less than 16 are in the p2 partition partition p3 values ​​less than maxvalue -- values ​​greater than 16 are in the p3 partition, or use a larger value);

Time range partitioning

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991), -- Data before 1991 is in partition P0 PARTITION p1 VALUES LESS THAN (1996), -- Data before 1996 is in partition P1 PARTITION p2 VALUES LESS THAN (2001), -- Data before 2001 is in partition P2 PARTITION p3 VALUES LESS THAN MAXVALUE -- Data after 2001 is in partition P3);

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

2. List partition (list partition)

The biggest difference between list partitioning and range partitioning is that list partitioning is equal, while range partitioning is within a certain range;

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17), -- The values ​​of 3,5,6,9,17 are placed in the pNorth partition PARTITION pEast VALUES IN (1,2,10,11,19,20), -- The values ​​of 1,2,10,11,19,20 are placed in the pEast partition PARTITION pWest VALUES IN (4,12,13,14,18), -- The values ​​of 4,12,13,14,18 are placed in the pWest partition PARTITION pCentral VALUES IN (7,8,15,16) -- The values ​​of 7,8,15,16 are placed in the pCentral partition);

3. Column partitioning

Column partitioning is a variant of range partitioning and list partitioning. That is to say, column partitioning is encapsulated by range partitioning and list partitioning. The only difference is that column partitioning has no data type restrictions. In other words, range partitioning and list partitioning are column partitioning.

4. Hash partition

Hash partitioning does not require specifying a range or list, but dynamically allocates the value to be inserted to determine which partition to insert into. It is very similar to the principle of hashMap. The difference is that hashMap uses a perturbation function to solve the hash collision problem, but MySQL's hash partitioning directly obtains the result through modulo operation; then inserts the value into the partition at the specified position.

-- Partitioning of common fields CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 5;--Create 5 partitions, 0, 1, 2, 3, 4

-- Create a partition of time type CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4; -- Create four partitions, 0, 1, 2, and 3

5. Secret key partition (key partition)

Key partitioning is less used

-- Partition by primary key CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2; -- Create two partitions named P0 and P1. This is a variant of hash partitioning. The storage method is the same as hash partitioning. -- Partition by unique key CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 3;-- Create three partitions, namely p0, p1, p2

-- Specify the primary key field for partitioning CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10; -- Create 10 partitions

6. Subpartitions

Subpartitioning can be understood as: partitioning on the basis of partitioning; for example, if a table is divided into three partitions, and each partition has three subpartitions, then there are a total of 3 * 3 = 9 partitions;

 -- There are 3 partitions in the table, and each partition has 2 subpartitions, so there are 6 partitions in total. CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

When you enter the mysql data file, you can see that there are 6 files. As the name suggests, 6 partitions are generated.

-rw-rw---- 1 mysql mysql 98304 August 2 22:37 ts#P#p0#SP#p0sp0.ibd
-rw-rw---- 1 mysql mysql 98304 August 2 22:37 ts#P#p0#SP#p0sp1.ibd
-rw-rw---- 1 mysql mysql 98304 August 2 22:37 ts#P#p1#SP#p1sp0.ibd
-rw-rw---- 1 mysql mysql 98304 August 2 22:37 ts#P#p1#SP#p1sp1.ibd
-rw-rw---- 1 mysql mysql 98304 August 2 22:37 ts#P#p2#SP#p2sp0.ibd
-rw-rw---- 1 mysql mysql 98304 August 2 22:37 ts#P#p2#SP#p2sp1.ibd

Add a partition

-- Add list partition alter table titles add partition(partition p7 values ​​in('CEO'));

Partition table principle

The partition table is implemented by multiple related underlying tables, which are also identified by handle objects. We can directly access each partition. The storage engine manages the underlying tables of the partitions in the same way as it manages ordinary tables (all underlying tables must use the same storage engine). The index of the partitioned table is simply adding an identical index to each underlying table. From the perspective of the storage engine, the underlying table is no different from a normal table, and the storage engine does not need to know whether this is a normal table or part of a partitioned table.

The operation of the partition table is performed according to the following operation logic:

Select query

When querying a partitioned table, the partition layer first opens and locks all underlying tables. The optimizer first determines whether some partitions can be filtered, and then calls the corresponding storage engine interface to access the data of each partition.

insert operation

When writing a record, the partition layer first opens and locks all underlying tables, then determines which partition accepts the record, and then writes the record to the corresponding underlying table.

Delete Operation

When deleting a record, the partition layer first opens and locks all underlying tables, then determines the partition corresponding to the data, and finally deletes the corresponding underlying table.

Update Operation

When updating a record, the partition layer first opens and locks all the underlying tables. MySQL first determines which partition the record to be updated is in, then retrieves the data and updates it, and then determines which partition the updated data should be in. Finally, it writes to the underlying table and deletes the underlying table where the source data is located.

Some operations support filtering. For example, when deleting a record, MySQL needs to find the record first. If the where condition happens to match the partition expression, all partitions that do not contain this record can be filtered out. This is also effective for update. If it is an insert operation, only one partition is hit and other partitions are filtered out. MySQL first determines which partition the record belongs to, and then writes the record to the corresponding partition table without having to operate on any other partitions.

Although each operation will "open and lock all underlying tables first", this does not mean that the partition table will lock the entire table during processing. If the storage engine can implement row-level locks by itself, such as InnoDB, the corresponding table lock will be released at the partition level.

How to Use Partition Table

1. The log system can be partitioned. Generally, the number of logs is relatively large, and they are partitioned by year or month. Generally speaking, it is necessary to query the historical records of a certain period of time in the log system. Because the amount of data is huge, a full table scan cannot be used. A full table scan will cause a large amount of random IO. When the amount of data is too large, the index will not work. At this time, partitioning should be considered to solve the problem;
2. Partitioning is not necessary only when the amount of data is large. Partitioning can also be used when the amount of data is small. In what scenarios is the amount of data small? The answer is that partitioning can be used when the data you query each time is a batch. For example, dictionaries, business dictionaries and user type dictionaries are generally stored in the same table. And each time you query, you query not just one business or one user type, but the entire business or user type. This is a batch, and partitioning can also be used to achieve this.
3. After using partitions, you don't need indexes, because partitions are generally used for range queries, and range queries do not need to use indexes; data has been distributed in different partitions;
4. If you want to use indexes, you can, but you need to separate hot data from cold data. Hot data is data that is frequently queried. Add indexes to hot data tables to speed up access.
   

Precautions

1. Null values ​​will invalidate partition filtering. Partitions require column names, and you need to ensure that the column names do not contain null values.
2. If the partition column and the index column are not the same column, the query will not be able to perform partition filtering. For example, if both your id and age fields are indexed, it is best to set these two columns as partition columns when partitioning: PARTITION BY RANGE COLUMNS(id,age)
3. The cost of adding, deleting, and modifying partitioned tables is very high. Each time a table is added, deleted, or modified, all underlying tables will be opened and locked. As long as one is locked, other operations cannot be performed.
4. When maintaining partitions, the cost may be very high, especially when you need to modify the partition, the cost is the highest.
   

Summarize

This is the end of this article about the use and underlying principles of MySQL table partitions. For more information about the underlying principles of MySQL table partitions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!