Detailed introduction to table partitioning in MySQL

MySQL table partitioning is the same as database and table partitioning, both to improve the throughput of the database. Partitioning is similar to table partitioning. Table partitioning is to logically divide a table with a large amount of data into multiple tables, which can be divided horizontally or vertically. Partitioning is to split a data file of a table into multiple data files. Different data is split into different files. In this way, for a table with a very large amount of data, multiple data files are used for storage, which improves the IO performance of the database.

Since we are operating on the files of the data table, we need to first understand the storage of the MySQL table. We know that MySQL has multiple storage engines, and different storage engines store different file formats. Here we mainly use the two storage engines InnoDB and MyISAM for explanation.

InnoDB

.frm The structure of the file data table

.idb The data of the file table File, exclusive table space, each table has an .idb file

.ibdata file table data file, shared table space, all tables use this data

File

MyISAM

##.frm File data table structure

.myd file data file

.myi file index file

##First of all, we need to check whether our current database version supports partitioning

1 show variables like '%partition%';

How to partition? When performing horizontal segmentation of the database, we know that horizontal segmentation can be divided into different tables according to the modulus of specified fields, or it can be divided according to date, or segmented according to id, 1-1 million In the first table, 1 million and 1 to 2 million in the second table and so on. In short, we have many ways to do segmentation. Then the database also provides us with a variety of options for us to choose from on table partitions.

MySQL table partitioning strategy

RANGE partitioning

is based on belonging to a given Column values ​​in continuous intervals, assign multiple rows to partitions

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=utf8
7 /*!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 means that all data records with ID greater than 7 exist in the p1 partition.

RANGE partitioning is particularly useful in the following situations:

·When "old" data needs to be deleted. If you were using the partitioning scheme shown in the most recent example above, you could simply use "ALTER TABLE employees DROP PARTITION p0;" to delete all rows for employees who stopped working before 1991. For tables with a large number of rows, this is much more efficient than running a DELETE query such as "DELETE FROM employees WHERE YEAR(separated) <=

1990;"

·Want to use a column that contains date or time values, or values ​​that grow from some other series.

Frequently run queries that depend directly on the columns used to split the table. For example, when executing a query such as

“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”

, MySQL can quickly determine that only partition p2 needs to be scanned, because the remaining partitions cannot contain a query that matches the WHERE subsection. Any record of the sentence

LIST partitioning is similar to partitioning by RANGE. The difference is that LIST partitioning is based on matching a discrete column value. Select a value from a set of values.

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=utf8
8 /*!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 Partitioning Select partitions based on the return value of a user-defined expression that uses the values ​​to be inserted into the table. Row column values ​​are calculated. This function can contain any expression valid in MySQL that produces a non-negative integer value. HASH partitioning is primarily used to ensure that data is evenly distributed among a predetermined number of partitions. In RANGE and LIST partitioning, you must explicitly specify in which partition a given column value or set of column values ​​should be stored; in HASH partitioning, MySQL does this automatically, and all you have to do is based on the value to be hashed. Column value specifies a column value or expression and specifies the number of partitions into which the partitioned table will be split

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=utf8
8 /*!50100 PARTITION BY HASH (storeid)9 PARTITIONS 4 */;

简单点说就是数据的存入可以按 partition by hash(expr); 这里的 expr 可以

是键名也可以是表达式比如 YEAR(time)，如果是表达式的情况下

“但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计

算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时

影响大量行的运算（例如批量插入）的时候。 ”

在执行删除、写入、更新时这个表达式都会计算一次。

数据的分布采用基于用户函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为 N ，其中“N = MOD(expr, num)”。

比如上面的 storeid 为 10；那么 N=MOD(10,4) ;N 是等于 2 的，那么这条记录就存储在 p2 的分区里面。

如果插入一个表达式列值为'2005-09-15′的记录到表中，那么保存该条记录的分区确定如下：MOD(YEAR('2005-09-01′),4) = MOD(2005,4) = 1 ; 就存储在 p1 分区里面了。

分区注意点

1、重新分区时，如果原分区里面存在 maxvalue 则新的分区里面也必须包含

maxvalue 否则就错误。

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 except for last partition where it can extend the range

2、分区删除时，数据也同样会被删除 alter table p_range drop partition p0;

3、如果 range 分区列表里面没有 maxvalue 则如有新数据大于现在分区 range 数据值那么这个数据是无法写入到数据库表的。

 

4、修改表名不需要 删除分区后在进行更改，修改表名后分区存储 myd myi 对应也会自动更改。

 

如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用 TRUNCATE TABLE 命令。（请参见 13.2.9 节，“TRUNCATE 语法”）。

 

如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE … REORGANIZE PARTITION”语句。参见下面的内容，或者在 13.1.2 节，“ALTER TABLE 语法” 中参考关于 REORGANIZE PARTITION 的信息。

 

5、对表进行分区时，不论采用哪种分区方式如果表中存在主键那么主键必须在分区列中。表分区的局限性。

 

6、list 方式分区没有类似于 range 那种 less than maxvalue 的写法，也就是说 list 分区表的所有数据都必须在分区字段的值列表集合中。

 

7、在 MySQL 5.1 版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用 MyISAM，而对另一个使用 InnoDB。

 

8、分区的名字是不区分大小写的，myp1 与 MYp1 是相同的。

  

The above is the detailed content of Detailed introduction to table partitioning in MySQL. For more information, please follow other related articles on the PHP Chinese website!