Detailed analysis of table partitioning technology in MySQL_MySQL

MySQL partitioning technology (used since mysql 5.1 -> is a technology inserted into mysql by Oracle mysql technical team maintainers in the form of a plug-in)

1. Overview

After a single database table reaches a certain size, the performance will decline, which is obvious for mysqlsql server, etc., so these data need to be partitioned. At the same time, sometimes data stripping may occur, and the partition table is even more useful!

The new partition function in MySQL 5.1 has begun to increase, and the advantages have become more and more obvious:

1. Can store more data than a single disk or file system partition
2. Easily delete unused or outdated data
3. Some queries can be greatly optimized
4. When it comes to aggregate functions such as SUM()/COUNT(), it can be done in parallel
5. Greater IO throughput
6. Partitioning allows rules that can be set to any size to allocate multiple parts of a single table across the file system. In effect, different parts of the table are stored as separate tables in different locations.
   

2. Partition technical support

Before 5.6, use this parameter to check whether the current configuration supports partitioning:

mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
|Variable_name     | Value |
+-----------------------+-------+
| have_partition_engine | YES  |
+-----------------------+-------+

If yes, it means your current configuration supports partitioning. After adoption in 5.6 and above, use the following method to view:

mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name            | Status  | Type        | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog           | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   |
| mysql_native_password   | ACTIVE  | AUTHENTICATION   | NULL  | GPL   |
..................................................................................
| INNODB_LOCKS        | ACTIVE  | INFORMATION SCHEMA | NULL  | GPL   |
| INNODB_LOCK_WAITS     | ACTIVE  | INFORMATION SCHEMA | NULL  | GPL   |
| partition         | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   |
+----------------------------+----------+--------------------+---------+---------+

42 rows in set (0.00 sec) In the last row, you can see that partition is ACTIVE, indicating that partitioning is supported.

3. Partition types and examples

3.1 Range Partitioning

RANGE partitioning : Assigns multiple rows to partitions based on column values ​​belonging to a given continuous range. Such as time, continuous constant values, etc. --Partition by year

mysql> use mytest;
Database changed
mysql> create table range_p( 
  -> perid int(11), 
  -> pername char(12) not null, 
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by range(year(credate))( 
  -> partition p2011 values less than (2011), 
  -> partition p2012 values less than (2012), 
  -> partition p2013 values less than (2013), 
  -> partition p2014 values less than (2014),
  -> partition p2015 values less than maxvalue 
  -> );
Query OK, 0 rows affected (0.12 sec)

3.2 List partitions

LIST partitioning : Similar to partitioning by RANGE, the difference is that LIST partitioning is selected based on the column value matching a certain value in a discrete value set. For example, attribute values ​​such as gender (1,2).

mysql> create table list_p( 
  -> perid int(11), 
  -> pername char(12) not null,
  -> sex int(1) not null,
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by list(sex) (
  -> partition psex1 values in(1),
  -> partition psex2 values in(2));
Query OK, 0 rows affected (0.06 sec)

Note that list can only be numbers. Using characters will result in error ERROR 1697 (HY000): VALUES value for partition 'psex1' must have type INT.

3.3 Discrete Partition

HASH Partitioning: A partition selected based on the return value of a user-defined expression calculated 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.

--Partition by int field hash

create table hash_p( 
perid int(11), 
pername char(12) not null,
sex int(1) not null,
monsalary DECIMAL(10,2),
credate datetime 
) partition by hash (perid) 
partitions 8;

--Hash partition by time function

mysql> create table hash_p( 
  -> perid int(11), 
  -> pername char(12) not null,
  -> sex int(1) not null,
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by hash (year(credate)) 
  -> partitions 8;
Query OK, 0 rows affected (0.11 sec)

3.4 Key-value partition

KEY partitioning: Similar to partitioning by HASH, the difference is that KEY partitioning only supports calculation of one or more columns, and the MySQL server provides its own hash function. There must be one or more columns containing >integer values. Its partitioning method is very similar to hash:

mysql> create table key_p( 
  -> perid int(11), 
  -> pername char(12) not null,
  -> sex int(1) not null,
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by key (perid) 
  -> partitions 8;
Query OK, 0 rows affected (0.12 sec)

3.5 Other instructions

mysql-5.5 begins to support COLUMNS partition, which can be regarded as the evolution of RANGE and LIST partitions. COLUMNS partition can be partitioned directly using non-integer data. The COLUMNS partition supports the following data types: All integers, such as INT SMALLINT TINYINT BIGINT. FLOAT and DECIMAL are not supported. Date types such as DATE and DATETIME. Other date types are not supported. String types such as CHAR, VARCHAR, BINARY, and VARBINARY. BLOB and TEXT types are not supported. COLUMNS can be partitioned using multiple columns.

mysql> create table range_p( 
  -> perid int(11), 
  -> pername char(12) not null, 
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) PARTITION BY RANGE COLUMNS (credate)( 
  -> partition p20151 values less than ('2015-04-01'), 
  -> partition p20152 values less than ('2015-07-01'), 
  -> partition p20153 values less than ('2015-10-01'), 
  -> partition p20154 values less than ('2016-01-01'),
  -> partition p20161 values less than ('2016-04-01'),
  -> partition partlog values less than maxvalue 
  -> );
Query OK, 0 rows affected (0.12 sec)

Summary:

Partition table is a new function in MySQL5.1. As of MySQL5.1.22-rc, partitioning technology is not very mature, and many partition maintenance and management functions have not been implemented. For example, recovery of data storage space in a partition, partition repair, partition optimization, etc. MySQL partitions can be used in tables that can be deleted by partition, and the modification operation to the database is not large, and queries based on partition fields are frequently performed. Table (for example, the statistical table in the malicious code is partitioned by day, often queried, grouped, etc. based on time, and partitions can be deleted by day). In addition, since MySQL does not have a global index but only a partitioned index, when a table has two unique indexes [z5], the table cannot be partitioned, and the partition column must contain the primary key. Otherwise MySQL will report an error.

In short, MySQL has many restrictions on partitioning, and I personally think that hash and key partitioning are not very meaningful in practice.

Partitioning introduces a new way to optimize queries (of course, there are corresponding disadvantages). The optimizer can use partitioning functions to trim partitions, or to remove partitions from the query entirely. It achieves this optimization by inferring whether the data can be found on a specific partition. So in the best case, trimming allows queries to access less data. It is important to define the partition key in the WHERE clause, even though it may seem redundant. Through the partition key, the optimizer can remove unused partitions. Otherwise, the execution engine will access all partitions of the table like a merge table, which will be very slow on large tables. Partitioned data is easier to maintain than non-partitioned data, and old data can be removed by deleting partitions. Partitioned data can be distributed to different physical locations so that the server can use multiple hard drives more efficiently.

[z1] The return value of the partition function must be an integer. The return value of the partition function of the new partition should be greater than the return value of the partition function of any existing partition.
[z2] Error message for tables with primary keys: #1503
A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION, if there is no primary key, there is no such constraint
[z3] Note: For tables partitioned by RANGE, you can only use ADD PARTITION to add new partitions to the high end of the partition list. That is, you cannot add a partition smaller than the range of this partition.
[z4] For tables partitioned according to RANGE, only adjacent partitions can be reorganized; RANGE partitions cannot be skipped. You cannot use REORGANIZEPARTITION to change the partition type of a table; that is, for example, you cannot change a RANGE partition into a HASH partition or vice versa. You also cannot use this command to change partition expressions or columns.
[z5] Pay attention to the difference between primary key and unique index

Official information: https://dev.mysql.com/doc/refman/5.5/en/partitioning.html