MySQL table Partition

MySQL table Partition

MySQL table partitions are the same as database/table sharding to improve database throughput. Partitions are similar to table shards. A table shard logically divides a table with a large amount of data into multiple partitions, which can be divided by water or vertical partitions. Partitions split a data file of a table into multiple partitions. Split different data into different files. In this way, a table with a large amount of data has multiple data files for storage, which improves the I/O performance of the database.

Since it is for the operation of data table files, we need to first understand the storage of MySQL tables. We know that MySQL has multiple storage engines, and different storage engines store different file formats. The two storage engines InnoDB and MyISAM are described here.

 

InnoDB

 

Structure of the. frm file data table

 

. Idb file table data file, exclusive tablespace, each table has a. idb File

 

. Ibdata file table data file, shared tablespace, all tables use this data

 

File

 

MyISAM

 

Structure of the. frm file data table

 

. Myd file data file

 

. Myi file index file

 

First, check whether the current database version supports partitioning.

1 show variables like '%partition%';

How to partition? When performing horizontal database sharding, we know that horizontal sharding can be divided into different tables based on the modulo of specified fields, or split by date or by id, 1-1 million in the first table, 1 million 0-1 to 2 million in the second table, and so on. In short, there are many ways to split. In this case, the database provides a variety of solutions for Table Partitioning.

 

MySQL table partition Policy

 

Based on the column value of a given continuous interval, a RANGE partition distributes multiple rows to the partition.

1 DROP TABLE IF EXISTS `p_range`;2 CREATE TABLE `p_range` (3 `id` int(10) NOT NULL AUTO_INCREMENT,4 `name` char(20) NOT NULL,5 PRIMARY KEY (`id`)6 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf87 /*!50100 PARTITION BY RANGE (id)8 (PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */;

Maximum Value

1 PARTITION BY RANGE (id)2 (3 PARTITION p0 VALUES LESS THAN (8),4 PARTITION p1 VALUES LESS THAN MAXVALUE)

Applicable scenarios:

 

This indicates that all data records with IDs greater than 7 exist in the p1 partition.

 

RANGE partitioning is particularly useful in the following scenarios:

 

· To delete "old" data. If you use the PARTITION scheme provided in the latest example above, you just need to simply use "alter table employees drop partition p0; to delete all the rows corresponding to the employees who stopped working before January 1, 1991. For tables with a large number of rows, this is worse than running a table such as "delete from employees where year (separated) <=

 

1990; "such a DELETE query is much more effective.

 

· You want to use a column that contains a date or time value, or contains a value that increases from some other levels.

 

· Regular operations depend directly on the query of columns used to split tables. For example

 

"Select count (*) FROM employees where year (separated) = 2000 group by store_id;" in such a query, MySQL can quickly determine that only partition p2 needs to be scanned, this is because the remaining partition cannot contain any records that comply with the WHERE clause.

 

LIST partitions are similar to partitions by RANGE. The difference is that LIST partitions are selected based on column values matching a value in a discrete value set.

1 DROP TABLE IF EXISTS `p_list`;2 CREATE TABLE `p_list` (3 `id` int(10) NOT NULL AUTO_INCREMENT,4 `typeid` mediumint(10) NOT NULL DEFAULT '0',5 `typename` char(20) DEFAULT NULL,6 PRIMARY KEY (`id`,`typeid`)7 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf88 /*!50100 PARTITION BY LIST (typeid)9 (PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM, PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;

HASH partitions are selected based on the return values of user-defined expressions. This expression uses the column values of these rows to be inserted into the table for calculation. This function can contain any expressions that are valid in MySQL and generate non-negative integer values. HASH partitions are mainly used to ensure that data is evenly distributed in pre-defined partitions. In the RANGE and LIST partitions, you must specify the partition in which a given column value or column value set should be stored. In the HASH partition, MySQL automatically completes these tasks, all you need to do is specify a column value or expression based on the column value to be hashed, and specify the number of partitions to be split into in the partitioned table.

1 DROP TABLE IF EXISTS `p_hash`;2 CREATE TABLE `p_hash` (3 `id` int(10) NOT NULL AUTO_INCREMENT,4 `storeid` mediumint(10) NOT NULL DEFAULT '0',5 `storename` char(255) DEFAULT NULL,6 PRIMARY KEY (`id`,`storeid`)7 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf88 /*!50100 PARTITION BY HASH (storeid)9 PARTITIONS 4 */;

Simply put, data can be stored by partition by hash (expr); expr here can

 

Is the key name or expression, such as YEAR (time ).

 

"But remember that this expression is counted every time a row is inserted or updated (or possibly deleted ).

 

Calculate once; this means that a very complex expression may cause performance problems, especially when executing

 

Affects the operation of a large number of rows (such as batch insert. "

 

This expression is calculated once when deletion, write, or update is executed.

 

The data distribution uses the modulus based on the user function result to determine which number partition is used. In other words, for an expression "expr", the Partition Number of the record to be saved is N, where "N = MOD (expr, num )".

 

For example, if the storeid above is 10, then N = MOD (10, 4); N is equal to 2, then this record is stored in the p2 partition.

 

If you insert a record with an expression column value of '2017-09-15 'into the table, the partition for storing the record is determined as follows: MOD (YEAR ('2017-09-01'), 4) = MOD () = 1; it is stored in the p1 partition.

 

 

Partition notes

 

1. When partitioning again, if maxvalue exists in the original partition, the new partition must also contain

 

Maxvalue. Otherwise, the error occurs.

 

Alter table p_range2x

 

Reorganize partition p1, p2

 

Into (partition p0 values less than (5), partition p1 values less than maxvalue );

 

[Err] 1520-Reorganize of range partitions cannot change total ranges limit t for last partition where it can extend the range

 

 

2. When a partition is deleted, the data will also be deleted. alter table p_range drop partition p0;

 

3. If there is no maxvalue in the range partition list, the data cannot be written to the database table if new data is greater than the current range value of the partition.

 

4. You do not need to delete a partition after modifying the table name. After modifying the table name, the corresponding partition storage myd myi will also be changed automatically.

 

If you want to delete all data from all partitions, but keep the TABLE definition and partition mode, use the truncate table command. (See section 13.2.9 "TRUNCATE Syntax ").

 

If you want to change the TABLE partition without losing data, use "alter table... Reorganize partition statement. Refer to the following content or refer to reorganize partition in "alter table Syntax" in section 13.1.2.

 

5. When partitioning a table, no matter which partition method is used, if the table has a primary key, the primary key must be in the partition column. Table partition limitations.

 

6. list-based partitioning is not similar to the less than maxvalue method of range. That is to say, all data in the list partition table must be in the Value list set of the partition field.

 

7. In MySQL 5.1, all partitions in the same partition table must use the same storage engine. For example, you cannot use MyISAM for one partition but InnoDB for the other.

 

8. The partition name is case-insensitive. myp1 and MYp1 are the same.

 

Reference video content: http://www.roncoo.com/course/view/658088f6e77541f5835b61800314083e