MySQL Partition 相关使用总结

目录

一、Range分区

二、List分区

三、Hash分区

四、Key分区

五、子分区

六、分区常用操作

---

概述

概念：分区可以根据规则在文件系统中分散存储各个表的部分。实际上，表的不同部分作为单独的表存储在不同的位置。如下图所示：其中p代表分区，sp代表子分区。

优点：
1.分区使一张表中存储的数据比单个磁盘或文件系统分区上存储的数据更多。
2.通过删除仅包含该数据的分区（或多个分区），可以轻松地从分区表中删除数据。相反，通过添加一个或多个新分区更方便存储数据。
3.某些查询可以极大地优化，因为满足给定WHERE子句的数据只能存储在一个或多个分区上，这会自动从搜索中排除其他的分区。

一、Range分区

Range分区是按连续不重叠的范围划分的，并使用VALUES LESS THAN运算符定义 。区间为左闭右开。
1. 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 NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN MAXVALUE );

也可以使用函数表达式对表进行分区

CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ), PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ), PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ), PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ), PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ), PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE) );

当以下一个或多个条件成立时，范围分区特别有用：
（1）想要按时间删除数据。
（2）想要使用包含日期或区间值的列。
（3）经常运行直接对表进行分区的列的查询。例如，在执行EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;查询时 ，MySQL 可以快速确定只需要扫描p2分区。

2. 多列Range分区
RANGE COLUMNS分区RANGE在以下方面与分区有一些不同：

(1)RANGE COLUMNS 不接受表达式，只接受列名。

(2)RANGE COLUMNS 接受一列或多列的列表。

(3)RANGE COLUMNS分区基于 元组（列值列表）之间的比较，而不是标量值之间的比较。RANGE COLUMNS分区中行的放置也是基于元组之间的比较；

(4)RANGE COLUMNS分区列不限于整数列；字符串、DATE、DATETIME列也可以作为分区列。

例如:

CREATE TABLE rc1 ( a INT, b INT ) PARTITION BY RANGE COLUMNS(a, b) ( PARTITION p0 VALUES LESS THAN (5, 12), PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE) );

插入时比较的是元组（列表）
mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);

查看表partition分布：
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'r1';
+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p | p0 | 2 |
| p | p1 | 1 |
+--------------+----------------+------------+

可以通过使用mysql客户端比较元组和分区定义的大小
mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
| 1 | 1 | 0 |
+-----------------+-----------------+-----------------+

也可以使用非整数列作为分区列

PARTITION BY RANGE COLUMNS (lname) ( PARTITION p0 VALUES LESS THAN ('g'), PARTITION p1 VALUES LESS THAN ('m'), PARTITION p2 VALUES LESS THAN ('t'), PARTITION p3 VALUES LESS THAN (MAXVALUE) );

ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired) ( PARTITION p0 VALUES LESS THAN ('1970-01-01'), PARTITION p1 VALUES LESS THAN ('1980-01-01'), PARTITION p2 VALUES LESS THAN ('1990-01-01'), PARTITION p3 VALUES LESS THAN ('2000-01-01'), PARTITION p4 VALUES LESS THAN ('2010-01-01'), PARTITION p5 VALUES LESS THAN (MAXVALUE) );

二、List分区

在列表分区中，每个分区是根据一组值列表定义和选择的。
1. List分区

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), PARTITION pEast VALUES IN (1,2,10,11,19,20), PARTITION pWest VALUES IN (4,12,13,14,18), PARTITION pCentral VALUES IN (7,8,15,16) );

删除一个partition的数据：

ALTER TABLE employees TRUNCATE PARTITION pWest;

这比DELETE FROM employees WHERE store_id IN (4,12,13,14,18);语句执行效率高得多

插入数据：

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3

若INSERT包含不匹配的分区列值的语句失败并显示错误。

可以使用IGNORE关键字来忽略此类错误
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5 Duplicates: 2 Warnings: 0

2.List多列分区
LIST分区的一种拓展，它允许使用多个列作为分区键，并支持字符串类型、DATE、DATETIME列用作分区列。（经测试不支持默认值DEFAULT，默认大小写不敏感)。

CREATE TABLE customers_1 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE ) PARTITION BY LIST COLUMNS(city) ( PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'), PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'), PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'), PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo') );

与RANGE COLUMNS分区一样，可以在COLUMNS()子句中使用多个列

CREATE TABLE lc ( a INT NULL, b INT NULL ) PARTITION BY LIST COLUMNS(a,b) ( PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) );

三、Hash分区

1.Hash分区
按Hash分区主要用于确保数据在分区之间均匀分布。

对store_id列使用Hash分为4个分区
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 4;

CREATE TABLE lc ( a INT NULL, b INT NULL ) PARTITION BY LIST COLUMNS(a,b) ( PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) );

还可以使用返回整数的SQL表达式

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;

当使用PARTITION BY HASH时，存储引擎使用基于表达式结果的模确定目标分区。
N = MOD(expr, num).

如要插入 hired 的 值为 '2005-09-15'时 他的目标分区为:
MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1

2. 线性hash分区
它与常规散列的不同之处在于，线性散列使用线性二次幂算法，而常规散列使用散列函数值的模数。

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 LINEAR HASH( YEAR(hired) ) PARTITIONS 4;

计算方法：
(1) V = POWER(2, CEILING(LOG(2, num)))
其中num为分区个数，假设num是 13。那么 LOG(2,13)是 3.7004397181411。 CEILING(3.7004397181411)是 4，而V= POWER(2,4)是 16。
(2) N = F(column_list) & (V - 1)
(3) 若 N >= num 则V = V / 2 ; N = N & (V - 1) 重复执行第(2)步

假设t1使用线性哈希分区并具有6个分区
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;

插入一条数据，其中col3值为 '2003-04-14'
V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(3 >= 6 is FALSE: record stored in partition #3)

通过线性散列进行分区的优势在于，分区的添加、删除、合并和拆分速度更快，这在处理包含大量（TB）数据的表时非常有用。
缺点是与使用常规散列分区获得的分布相比，数据不太可能在分区之间均匀分布。

四、Key分区

Key分区类似于Hash分区，不同之处在于Hash分区使用用户定义的表达式，Key分区的哈希函数由 MySQL 服务器提供。
Key分区语法规则类似于Hash分区。主要区别为：
KEY只接受零个或多个列名的列表。任何用作分区键的列都必须包含表的部分或全部主键（如果表有主键）。如果没有指定列名作为分区键，则使用表的主键（如果有）。

CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2;

如果没有主键但有唯一键，则使用唯一键作为分区键：

CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) ) PARTITION BY KEY() PARTITIONS 2;

在这两种情况下，分区键就是 id列。

与其他分区类型的情况不同，用于Key分区的列不限于整数或NULL值，例如，以下CREATE TABLE语句是有效的

CREATE TABLE tm1 ( s1 CHAR(32) PRIMARY KEY ) PARTITION BY KEY(s1) PARTITIONS 10;

也可以通过线性键对表进行分区

CREATE TABLE tk ( col1 INT NOT NULL, col2 CHAR(5), col3 DATE ) PARTITION BY LINEAR KEY (col1) PARTITIONS 3;

分区编号是使用二的幂算法得出的。

五、子分区

子分区（也称为 复合分区）是对分区表中每个分区的进一步划分。

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 );

表ts有3个RANGE分区。每个分区的p0，p1和p2进一步分成2个hash子分区。实际上，整个表被划分为3 * 2 = 6分区。

还可以对RANGE分区的表进行List子分区。

还可以使用SUBPARTITION子句明确定义子分区， 以指定各个子分区的选项。例如，

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) );

注意：
（1）每个分区必须具有相同数量的子分区。

（2）如果SUBPARTITION在分区表的任何分区上显式定义任何子分区 ，则所有的分区的子分区都需要显示定义。换句话说，以下语句失败：

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2, SUBPARTITION s3 ) );

（3）每个SUBPARTITION子句必须（至少）包括子分区的名称。
（4）子分区名称在整个表中必须是唯一的。

六、分区常用操作

1. 查看表是否为分区表: SHOW TABLE STATUS WHERE Name='trb3' and Create_options = "partitioned";

2. 查看表分区信息：SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='trb3' AND TABLE_SCHEMA='test'\G;
（1）查看表partition分布：SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'r1';
对于分区InnoDB表，列中给出的行数TABLE_ROWS 只是 SQL 优化中使用的估计值。
（2）查看表分区列：SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='trb3' AND TABLE_SCHEMA='kuntools';

3. 查看对表的操作涉及了哪些分区：EXPLAIN SELECT * FROM trb3;

4. 查看分区中储存的行：SELECT * FROM employees PARTITION (p1);

从多个分区获取行，以逗号分隔列表的形式提供它们的名称。SELECT * FROM employees PARTITION (p1, p2);
可以使用 WHERE条件ORDER BY 和LIMIT选项等。还可以使用带有HAVING和 GROUP BY选项的聚合函数。
SELECT/DELETE/INSERT/REPLACE/UPDATE/LOAD DATA/LOAD XML 均支持显式分区选择。 

 以上内容摘录翻译于mysql官方文档MySQL :: MySQL 8.0 Reference Manual :: 24 Partitioning