Mysql partitioning technology details _ MySQL

This article mainly introduces mysql's partition technology in detail. This article describes the partition Technology Overview, partition types, partition operations, and other content. For more information, see I. Overview

When the total number of MySQL records exceeds 1 million, will the performance be greatly reduced? The answer is yes, but the ratio of performance degradation> varies depending on the system architecture, applications, and> index, server hardware, and other factors. When a user asks me this question, my most common answer is: table sharding, which can be divided by multiple rules such as id interval or chronological order. Table Sharding is easy. However, the resulting changes to the application and even the architecture are not too small, but also include future scalability.

In the past, a solution was to use MERGE
Type, which is very convenient for cooking. There is basically no need to change the architecture and program. However, its disadvantages are obvious:

1. only MyISAM tables with the same structure can be used
2. you cannot enjoy all the functions of MyISAM. for example, you cannot perform FULLTEXT search on the MERGE type.
3. it requires more file descriptors
4. slower index reading

At this time, the advantages of the Partition function added in MySQL 5.1 are obvious:

1. you can store more data than a single disk or file system partition.
2. it is easy to delete unnecessary or outdated data.
3. some queries can be greatly optimized
4. When aggregate functions such as SUM ()/COUNT () are involved
5. higher IO throughput

Partitions can be set to rules of any size, and multiple parts of a single table are allocated across file systems. In fact, different parts of a table are stored as separate tables in different locations.

Precautions for partitioning:

1. when partitioning, either the primary key is not defined or the partition field is added to the primary key.
2. the partition field cannot be NULL. Otherwise, how can we determine the partition range?

II. partition type

1. RANGE partition: multiple rows are allocated to the partition based on the column values that belong to a given continuous interval.
2. LIST partitioning: Similar to partitioning by RANGE, the difference is that LIST partitioning is based on column values matching a value in a discrete value set.
2. HASH partition: Select a partition based on the return value of the user-defined expression. this expression uses the column values of the rows to be inserted into the table for calculation. This function can include any expressions that contain valid non-negative integer values in MySQL.
3. KEY partitioning: Similar to HASH partitioning, the difference is that KEY partitioning only supports computing one or more columns, and the MySQL server provides its own HASH function. One or more columns must contain> integer values.

You can use the show variables command to determine whether MySQL supports partitions. for example:

The code is as follows:

Mysql> show variables like '% partition % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Have_partition_engine | YES |
+ ----------------------- + ------- +
1 row in set (0.00 sec)

The code is as follows:

Mysql> show variables like '% partition % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Have_partition_engine | YES |
+ ----------------------- + ------- +
1 row in set (0.00 sec)

1. range partitioning

The code is as follows:

Create table t_range (
Id int (11 ),
Money int (11) unsigned not null,
Date datetime
) Partition by range (year (date ))(
Partition p2007 values less than (2008 ),
Partition p2008 values less than (2009 ),
Partition p2009 values less than (2010)
Partition p2010 values less than maxvalue
);

2. list partitions

The code is as follows:

Create table t_list (
A int (11 ),
B int (11)
) (Partition by list (B)
Partition p0 values in (1, 3, 5, 7, 9 ),
Partition p1 values in (2, 4, 6, 8, 0)
);

For innodb and myisam engines, when multiple records are inserted in one statement, innodb will roll back if there is a value in the middle that cannot be inserted. data before the error value of myisam can be inserted into the table. For innodb and myisam engines, when multiple records are inserted in one statement, innodb will roll back if there is a value in the middle that cannot be inserted. data before the error value of myisam can be inserted into the table.

3. hash partition

Hash partitions are used to evenly distribute data to pre-defined partitions to ensure that the data volume of each partition is roughly the same.

The code is as follows:

Create table t_hash (
A int (11 ),
B datetime
) Partition by hash (YEAR (B)
Partitions 4;

The hash partition function page must return an integer. The value in the partitions clause is a non-negative integer. if the partitions clause is not added, the default value is 1.

4. key partition

Key partitions are similar to hash partitions. The difference is that hash partitions are partitioned by user-defined functions. key partitions are partitioned by functions provided by mysql databases, and NDB cluster uses MD5 functions to partition, for other storage engines, mysql uses internal hash functions, which are based on the same password () algorithm.

The code is as follows:

Create table t_key (
A int (11 ),
B datetime)
Partition by key (B)
Partitions 4;

5. Columns partition

In the preceding RANGE, LIST, HASH, and KEY partitions, the partition condition must be an integer. if it is not an integer, you need to convert it to an integer using a function.

The mysql-5.5 started to support COLUMNS partitions, which can be seen as the evolution of RANGE and LIST partitions, where COLUMNS partitions can be directly partitioned using non-integer data. COLUMNS partitions support the following data types:
All integer types, such as int smallint tinyint bigint. FLOAT and DECIMAL are not supported.
DATE type, such as DATE and DATETIME. Other date types are not supported.
String type, such as CHAR, VARCHAR, BINARY, and VARBINARY. BLOB and TEXT types are not supported.
COLUMNS can be partitioned using multiple COLUMNS.

Add partition

The code is as follows:

Mysql> alter table sale_data

-> Add partition (PARTITION p201010 values less than (201011 ));

Query OK, 0 rows affected (0.36 sec)

Records: 0 Duplicates: 0 Warnings: 0

Delete partition

The code is as follows:

-- When a partition is deleted, all data in the partition is also deleted.

Mysql> alter table sale_data drop partition p201010;

Query OK, 0 rows affected (0.22 sec)

Records: 0 Duplicates: 0 Warnings: 0

Merge partitions

The following SQL statement combines p201001-p201009 into three partitions: p2010Q1-p2010Q3

The code is as follows:

Mysql> alter table sale_data

-> Reorganize partition p201001, p201002, p201003,

-> P201004, p201005, p201006,

-> P201007, p201008, p201009

-> (

-> PARTITION p2010Q1 values less than (201004 ),

-> PARTITION p2010Q2 values less than (201007 ),

-> PARTITION p2010Q3 values less than (201010)

-> );

Query OK, 0 rows affected (1.14 sec)

Records: 0 Duplicates: 0 Warnings: 0