MySQL 分区表操作示例 -- cnDBA.cn

1 分区表说明

MySQL 官网关于分区的说明如下：
https://dev.mysql.com/doc/refman/8.2/en/partitioning-overview.html

MySQL目前支持如下六种类型的分区：

RANGE分区：基于一个给定的连续区间范围，RANGE主要是基于整数的分区，对于非整形的字段需要利用表达式将其转换成整形。
LIST分区：是基于列出的枚举值列表进行分区。
COLUMNS分区：5.5开始引入的分区功能，支持整形、日期、字符串；RANGE和LIST的分区方式非常的相似，可以无需通过表达式进行转换直接对非整形字段进行分区，同时COLUMNS分区还支持多个字段组合分区，只有RANGE、LIST存在COLUMNS分区，COLUMNS是RANGE和LIST分区的升级。
HASH分区：基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。
KEY分区：KEY分区和HASH分区相似，但是KEY分区支持除text和BLOB之外的所有数据类型的分区,key分区可以直接基于字段做分区无需转换成整数。而HASH分区只支持数字分区，KEY分区不允许使用用户自定义的表达式进行分区，KEY分区使用系统提供的HASH函数进行分区。当表中存在主键或者唯一键时，如果创建key分区时没有指定字段系统默认会首选主键列作为分区字列,如果不存在主键列会选择非空唯一键列作为分区列,注意唯一列作为分区列唯一列不能为null。
子分区：子分区其实是对每个分区表的每个分区进行再次分隔，目前只有RANGE和LIST分区的表可以再进行子分区，子分区只能是HASH或者KEY分区。子分区可以将原本的数据进行再次的分区划分。

COLUMNS支持的类型：

整形支持：tinyint,smallint,mediumint,int,bigint;不支持decimal和float
时间类型支持：date,datetime
字符类型支持：char,varchar,binary,varbinary;不支持text,blob

官网说明如下：

https://dev.mysql.com/doc/refman/8.2/en/partitioning-columns.html

COLUMNS和RANGE/LIST分区的区别：

COLUMNS对日期字段的分区就不需要再使用函数进行转换了，例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。
COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键。

使用分区的相关注意事项：

基于时间类型的字段的转换函数mysql提供了”YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()”
针对非整形字段进行RANG、LIST分区建议使用COLUMNS分区。
COLUMNS分区不支持timestamp字段类型。
拆分合并分区后会导致修改的分区的统计信息失效,需要对表执行Analyze操作。没有修改的分区的统计信息还在,不影响新插入的值加入到统计信息。

确认MySQL服务器是否支持分区表：

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
……

2 Range 分区

RANGE分区基于一个给定的连续区间范围，早期版本RANGE主要是基于整数的分区。在5.7版本中DATE、DATETIME列也可以使用RANGE分区，同时在5.5以上的版本提供了基于非整形的RANGE COLUMN分区。RANGE分区必须的连续的且不能重叠。

2.1 创建int 类型 range 分区

mysql> 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 (21)
);

mysql> insert into employees(id,job_code,store_id) values(1,1001,1),(2,1002,2),(3,1003,3),(4,1004,4);

mysql> select partition_name,partition_method,partition_expression,partition_description,table_rows,subpartition_name,subpartition_method,subpartition_expression from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| partition_name | partition_method | partition_expression | partition_description | table_rows | subpartition_name | subpartition_method | subpartition_expression |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| p0             | RANGE            | store_id             | 6                     |          4 | NULL              | NULL                | NULL                    |
| p1             | RANGE            | store_id             | 11                    |          0 | NULL              | NULL                | NULL                    |
| p2             | RANGE            | store_id             | 16                    |          0 | NULL              | NULL                | NULL                    |
| p3             | RANGE            | store_id             | 21                    |          0 | NULL              | NULL                | NULL                    |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
4 rows in set (0.00 sec)

2.2 创建date类型 range 分区

对日期字段进行分区可以使用时间类型的函数进行转换成整形，但是如果查询语句需要利用分区那么查询语句也需要使用相同的时间函数进行查询。

使用YEAR()函数进行分区

mysql> CREATE TABLE employees1 (
    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 RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

mysql> insert into employees1(id,separated,job_code,store_id) values(1,'1990-03-04',1001,1),(2,'1995-03-04',1002,2),(3,'1998-03-04',1003,3),(4,'2016-03-04',1004,4);


mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='employees1';
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | SUBPARTITION_NAME | SUBPARTITION_METHOD | SUBPARTITION_EXPRESSION |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| p0             | RANGE            |  YEAR(separated)     | 1991                  |          1 | NULL              | NULL                | NULL                    |
| p1             | RANGE            |  YEAR(separated)     | 1996                  |          1 | NULL              | NULL                | NULL                    |
| p2             | RANGE            |  YEAR(separated)     | 2001                  |          1 | NULL              | NULL                | NULL                    |
| p3             | RANGE            |  YEAR(separated)     | MAXVALUE              |          1 | NULL              | NULL                | NULL                    |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
4 rows in set (0.00 sec)

对于日期字段分区，查询条件使用> 、< 、betnwen、=都会利用分区查询,如果条件使用函数转换则不会走分区，比如使用YEAR()。

mysql> explain select * from employees1 where separated='1995-03-04';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees1 | p1         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.3 创建TIMESTAMP类型range 分区

针对TIMESTAMP的日期类型的字段需要使用专门的UNIX_TIMESTAMP（）函数进行转换。http://www.cndba.cn/dave/article/131485

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

2.4 添加 / 删除分区

添加分区分两种情况，有默认分区和没有默认分区。

前面创建的employees 就没有默认分区，employees1 就有默认分区MAXVALUE。对于没有默认分区的分区表，直接添加分区即可，有默认分区的，需要先 drop 掉默认分区，添加分区后，在添加回来，当然在 drop 之前需要确认这里面有没有数据。

2.4.1 没有默认分区

mysql>  SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='employees';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| p0             | RANGE            | store_id             | 6                     |          4 |
| p1             | RANGE            | store_id             | 11                    |          0 |
| p2             | RANGE            | store_id             | 16                    |          0 |
| p3             | RANGE            | store_id             | 21                    |          0 |
+----------------+------------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)

mysql> alter table employees add PARTITION  (PARTITION p4 VALUES LESS THAN (28));
mysql> alter table employees add PARTITION  (PARTITION pmax VALUES LESS THAN MAXVALUE);

我们这里加了 2 个分区：28 和默认的 maxvalue：
mysql>  SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='employees';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| p0             | RANGE            | store_id             | 6                     |          3 |
| p1             | RANGE            | store_id             | 11                    |          0 |
| p2             | RANGE            | store_id             | 16                    |          0 |
| p3             | RANGE            | store_id             | 21                    |          0 |
| p4             | RANGE            | store_id             | 28                    |          0 |
| pmax           | RANGE            | store_id             | MAXVALUE              |          0 |
+----------------+------------------+----------------------+-----------------------+------------+
6 rows in set (0.00 sec)

2.4.2 有默认分区

employees1 表是有默认分区的，在添加之前需要先将默认分区 drop 表：

mysql>  SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='employees1';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| p0             | RANGE            |  YEAR(separated)     | 1991                  |          1 |
| p1             | RANGE            |  YEAR(separated)     | 1996                  |          1 |
| p2             | RANGE            |  YEAR(separated)     | 2001                  |          1 |
| p3             | RANGE            |  YEAR(separated)     | MAXVALUE              |          1 |
+----------------+------------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)

查看默认分区中的数据，先备份好，添加分区后在重新 insert 回来，我们这里测试环境，直接 drop 了：

mysql> select count(1) from cndba.employees1 partition(p3);
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> alter table cndba.employees1 drop partition p3;


mysql> ALTER TABLE cndba.employees1 ADD PARTITION ( PARTITION p3 VALUES LESS THAN(2023), PARTITION PMAX VALUES LESS THAN MAXVALUE);

我们这里加了 2023 和 MAXVALUE 分区：

mysql>  SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='employees1';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| p0             | RANGE            |  YEAR(separated)     | 1991                  |          0 |
| p1             | RANGE            |  YEAR(separated)     | 1996                  |          0 |
| p2             | RANGE            |  YEAR(separated)     | 2001                  |          0 |
| p3             | RANGE            |  YEAR(separated)     | 2023                  |          0 |
| PMAX           | RANGE            |  YEAR(separated)     | MAXVALUE              |          0 |
+----------------+------------------+----------------------+-----------------------+------------+
5 rows in set (0.00 sec)

2.5 拆分合并分区

拆分合并分区统称为重新定义分区。

查看分区情况：

mysql>  SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='employees';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| p0             | RANGE            | store_id             | 6                     |          3 |
| p1             | RANGE            | store_id             | 11                    |          0 |
| p2             | RANGE            | store_id             | 16                    |          0 |
| p3             | RANGE            | store_id             | 21                    |          0 |
| p4             | RANGE            | store_id             | 28                    |          0 |
| pmax           | RANGE            | store_id             | MAXVALUE              |          0 |
+----------------+------------------+----------------------+-----------------------+------------+
6 rows in set (0.00 sec)

将P0拆分成s1,s2两个分区
ALTER TABLE employees REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (3),
    PARTITION s1 VALUES LESS THAN (6)
);

mysql>  SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='employees';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| s0             | RANGE            | store_id             | 3                     |          0 |
| s1             | RANGE            | store_id             | 6                     |          0 |
| p1             | RANGE            | store_id             | 11                    |          0 |
| p2             | RANGE            | store_id             | 16                    |          0 |
| p3             | RANGE            | store_id             | 21                    |          0 |
| p4             | RANGE            | store_id             | 28                    |          0 |
| pmax           | RANGE            | store_id             | MAXVALUE              |          0 |
+----------------+------------------+----------------------+-----------------------+------------+
7 rows in set (0.00 sec)

将s1,p1,p2合并为a,b两个分区
ALTER TABLE employees REORGANIZE PARTITION s1,p1,p2 INTO (
    PARTITION a VALUES LESS THAN (5),
    PARTITION b VALUES LESS THAN (16)
);

mysql>  SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='employees';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| s0             | RANGE            | store_id             | 3                     |          0 |
| a              | RANGE            | store_id             | 5                     |          0 |
| b              | RANGE            | store_id             | 16                    |          0 |
| p3             | RANGE            | store_id             | 21                    |          0 |
| p4             | RANGE            | store_id             | 28                    |          0 |
| pmax           | RANGE            | store_id             | MAXVALUE              |          0 |
+----------------+------------------+----------------------+-----------------------+------------+
6 rows in set (0.00 sec)

2.6 创建date 类型 Range Columns 分区

range column对日期字段的分区不在需要再使用函数进行转换。

COLUMNS支持的类型：

整形支持：tinyint,smallint,mediumint,int,bigint;不支持decimal和float
时间类型支持：date,datetime
字符类型支持：char,varchar,binary,varbinary;不支持text,blob

mysql> CREATE TABLE members (
    id INT,
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION a VALUES LESS THAN ('1960-01-01'),
    PARTITION b VALUES LESS THAN ('1970-01-01'),
    PARTITION c VALUES LESS THAN ('1980-01-01'),
    PARTITION d VALUES LESS THAN ('1990-01-01'),
    PARTITION e VALUES LESS THAN MAXVALUE
);

mysql> insert into members(id,joined) values(1,'1950-01-01'),(1,'1960-01-01'),(1,'1980-01-01'),(1,'1990-01-01');


mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='members';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| a              | RANGE COLUMNS    | `joined`             | '1960-01-01'          |
| b              | RANGE COLUMNS    | `joined`             | '1970-01-01'          |
| c              | RANGE COLUMNS    | `joined`             | '1980-01-01'          |
| d              | RANGE COLUMNS    | `joined`             | '1990-01-01'          |
| e              | RANGE COLUMNS    | `joined`             | MAXVALUE              |
+----------------+------------------+----------------------+-----------------------+
5 rows in set (0.00 sec)

分析执行计划：
mysql> explain select id,joined from cndba.members where joined=YEAR(now());
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | members | a,b,c,d,e  | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 4 warnings (0.00 sec)

mysql>  explain select id,joined from cndba.members where joined='1963-01-01';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | members | b          | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

通过执行计划对比，当对日期进行函数处理后，就不能进行分区过滤了。http://www.cndba.cn/dave/article/131485

2.7 创建多字段类型 range columns分区

mysql>CREATE TABLE rcx (
    a INT,
    b INT
    )
PARTITION BY RANGE COLUMNS(a,b) (
     PARTITION p0 VALUES LESS THAN (5,10),
     PARTITION p1 VALUES LESS THAN (10,20),
     PARTITION p2 VALUES LESS THAN (15,30),
     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

多字段的分区键比较是基于数组的比较：

它先用插入的数据的第一个字段值和分区的第一个值进行比较，如果插入的第一个值小于分区的第一个值那么就不需要比较第二个值就属于该分区；
如果第一个值等于分区的第一个值，开始比较第二个值同样如果第二个值小于分区的第二个值那么就属于该分区。

RANGE COLUMN的多列分区第一列的分区值一定是顺序增长的，不能出现交叉值，第二列的值随便：

mysql> insert into rcx(a,b)values(1,20),(10,15),(10,30);http://www.cndba.cn/dave/article/131485

第一组值：(1,20);1<5所以不需要再比较20了，该记录属于p0分区。

第二组值:(10,15)，10>5,10=10且15<20，所以该记录属于P1分区

第三组值:(10,30),10=10但是30>20，所以它不属于p1,它满足10<15所以它属于p2

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='rcx';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| p0             | RANGE COLUMNS    | `a`,`b`              | 5,10                  |
| p1             | RANGE COLUMNS    | `a`,`b`              | 10,20                 |
| p2             | RANGE COLUMNS    | `a`,`b`              | 15,30                 |
| p3             | RANGE COLUMNS    | `a`,`b`              | MAXVALUE,MAXVALUE     |
+----------------+------------------+----------------------+-----------------------+
4 rows in set (0.00 sec)

3 LIST 分区

3.1 创建int 类型 List分区

mysql> CREATE TABLE tblist (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id)(
    PARTITION p1 VALUES IN (1,5,6),
    PARTITION p2 VALUES IN (2,7,8),
    PARTITION p3 VALUES IN (3,9,10),
PARTITION p4 VALUES IN (4,11,12)
);

在 Oracle 里面，会有个 default 分区，但是如果加上 default 创建 SQL 就会报错，查看了 mysql 的语法，从 5.7 之后，对于 list 分区，确实不支持default的语法了：

https://dev.mysql.com/doc/refman/8.0/en/create-table.html
partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}]

mysql> insert into tblist(id,store_id) values(1,1),(7,7); 

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| p1             | LIST             | store_id             | 1,5,6                 |
| p2             | LIST             | store_id             | 2,7,8                 |
| p3             | LIST             | store_id             | 3,9,10                |
| p4             | LIST             | store_id             | 4,11,12               |
+----------------+------------------+----------------------+-----------------------+
4 rows in set (0.00 sec)

3.2 创建时间类型 List 分区

mysql> CREATE TABLE listdate (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST( YEAR(hired) ) 
(
    PARTITION a VALUES IN (1990),
    PARTITION b VALUES IN (1991),
    PARTITION c VALUES IN (1992),
    PARTITION d VALUES IN (1993)
);

mysql> INSERT INTO listdate() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00');

mysql> EXPLAIN SELECT * FROM listdate WHERE hired='1990-01-01 10:00:00';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | listdate | a          | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3.3 创建非整形的 List Columns 分区

CREATE TABLE listvar (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(hired) 
(
    PARTITION a VALUES IN ('1990-01-01 10:00:00','1991-01-01 10:00:00'),
    PARTITION b VALUES IN ('1992-01-01 10:00:00'),
    PARTITION c VALUES IN ('1993-01-01 10:00:00'),
    PARTITION d VALUES IN ('1994-01-01 10:00:00')
);

INSERT INTO listvar() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00'),(1,'1993-01-01 10:00:00');

LIST COLUMNS分区对分整形字段进行分区就无需使用函数对字段处理成整形，所以对非整形字段进行分区建议选择COLUMNS分区。

mysql> EXPLAIN SELECT * FROM listvar WHERE hired='1990-01-01 10:00:00';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | listvar | a          | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3.4 创建多字段的 List Columns 分区

CREATE TABLE listvardou (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(id,hired) 
(
    PARTITION a VALUES IN ( (1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00') ),
    PARTITION b VALUES IN ( (2,'1992-01-01 10:00:00') ),
    PARTITION c VALUES IN ( (3,'1993-01-01 10:00:00') ),
    PARTITION d VALUES IN ( (4,'1994-01-01 10:00:00') )
);

INSERT INTO listvardou() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(2,'1992-01-01 10:00:00'),(3,'1993-01-01 10:00:00');

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='listvardou';
+----------------+------------------+----------------------+-----------------------------------------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION                               |
+----------------+------------------+----------------------+-----------------------------------------------------+
| a              | LIST COLUMNS     | `id`,`hired`         | (1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00') |
| b              | LIST COLUMNS     | `id`,`hired`         | (2,'1992-01-01 10:00:00')                           |
| c              | LIST COLUMNS     | `id`,`hired`         | (3,'1993-01-01 10:00:00')                           |
| d              | LIST COLUMNS     | `id`,`hired`         | (4,'1994-01-01 10:00:00')                           |
+----------------+------------------+----------------------+-----------------------------------------------------+
4 rows in set (0.00 sec)

3.5 增加分区

因为list 分区没有 defult，所以直接添加新分区即可：

mysql> ALTER TABLE tblist ADD PARTITION (PARTITION e VALUES IN (20));

3.6 合并分区

只能合并相邻的几个分区，不能跨分区合并。这里将分区p1,p2合并为分区m。

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| p1             | LIST             | store_id             | 1,5,6                 |
| p2             | LIST             | store_id             | 2,7,8                 |
| p3             | LIST             | store_id             | 3,9,10                |
| p4             | LIST             | store_id             | 4,11,12               |
| e              | LIST             | store_id             | 20                    |
+----------------+------------------+----------------------+-----------------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE tblist REORGANIZE PARTITION  p1,p2 INTO (PARTITION m VALUES IN (1,5,6,2,7,8));

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| m              | LIST             | store_id             | 1,5,6,2,7,8           |
| p3             | LIST             | store_id             | 3,9,10                |
| p4             | LIST             | store_id             | 4,11,12               |
| e              | LIST             | store_id             | 20                    |
+----------------+------------------+----------------------+-----------------------+
4 rows in set (0.00 sec)

3.7 拆分分区

mysql> ALTER TABLE tblist REORGANIZE PARTITION  m INTO ( PARTITION a VALUES IN (1,5,6),PARTITION b VALUES IN (2,7,8));

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| a              | LIST             | store_id             | 1,5,6                 |
| b              | LIST             | store_id             | 2,7,8                 |
| p3             | LIST             | store_id             | 3,9,10                |
| p4             | LIST             | store_id             | 4,11,12               |
| e              | LIST             | store_id             | 20                    |
+----------------+------------------+----------------------+-----------------------+
5 rows in set (0.00 sec)

3.8 删除分区

mysql> ALTER TABLE tblist DROP PARTITION e;

4 HASH 分区

HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。
MYSQL支持两种HASH分区，常规HASH(HASH)和线性HASH(LINEAR HASH)。

4.1 创建int 类型常规hash 分区

常规hash是基于分区个数的取模（%）运算。根据余数插入到指定的分区:

CREATE TABLE tbhash (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4
;

mysql> INSERT INTO tbhash() VALUES(1,100),(1,101),(2,102),(3,103),(4,104);
mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tbhash';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| p0             | HASH             | store_id             | NULL                  |          2 |
| p1             | HASH             | store_id             | NULL                  |          1 |
| p2             | HASH             | store_id             | NULL                  |          1 |
| p3             | HASH             | store_id             | NULL                  |          1 |
+----------------+------------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)

4.2 创建日期类型常规hash 分区

CREATE TABLE employees3 (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY HASH( YEAR(hired))
PARTITIONS 4;

4.3 创建时间类型线性HASH(LINEAR HASH)

LINEAR HASH和HASH的唯一区别就是PARTITION BY LINEAR HASH。线性HASH背后的原理这里不在描述，简单的说就是 hash 的算法不一样。http://www.cndba.cn/dave/article/131485 http://www.cndba.cn/dave/article/131485

CREATE TABLE tblinhash (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 6;

4.4 分区管理

常规HASH和线性HASH的增加收缩分区的原理是一样的。
增加和收缩分区后原来的数据会根据现有的分区数量重新分布。

HASH分区不能删除分区，所以不能使用DROP PARTITION操作进行分区删除操作；

可以通过ALTER TABLE … COALESCE PARTITION num来合并分区，这里的num是减去的分区数量；
可以通过ALTER TABLE … ADD PARTITION PARTITIONS num来增加分区，这里num是在原先基础上再增加的分区数量。

4.4.1 合并分区

减去3个分区：

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| p0             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p1             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p2             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p3             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p4             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p5             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
+----------------+------------------+----------------------+-----------------------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE tblinhash COALESCE PARTITION 3;
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| p0             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p1             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p2             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
+----------------+------------------+----------------------+-----------------------+
3 rows in set (0.00 sec)

4.4.2 增加分区

增加4个分区

mysql> ALTER TABLE tblinhash add PARTITION partitions 4;
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';
+----------------+------------------+----------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------------+----------------------+-----------------------+
| p0             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p1             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p2             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p3             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p4             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p5             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
| p6             | LINEAR HASH      |  YEAR(hired)         | NULL                  |
+----------------+------------------+----------------------+-----------------------+
7 rows in set (0.00 sec)

5 KEY 分区

5.1 创建常规 Key 分区

CREATE TABLE tb_key (
    id INT ,
    var CHAR(32) 
)
PARTITION BY KEY(var)
PARTITIONS 5;

mysql> INSERT INTO tb_key() VALUES(1,'星期一'),(2,'1998-10-19'),(3,'new'),(4,'非常好'),(5,'5');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tb_key';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| p0             | KEY              | `var`                | NULL                  |          2 |
| p1             | KEY              | `var`                | NULL                  |          0 |
| p2             | KEY              | `var`                | NULL                  |          1 |
| p3             | KEY              | `var`                | NULL                  |          0 |
| p4             | KEY              | `var`                | NULL                  |          2 |
+----------------+------------------+----------------------+-----------------------+------------+
5 rows in set (0.00 sec)

5.2 创建LINEAR KEY分区

CREATE TABLE tb_keyline (
    id INT NOT NULL,
    var CHAR(5)
)
PARTITION BY LINEAR KEY (var)
PARTITIONS 3;

5.3 分区管理

key分区管理和hash分区管理是一样的，只能删除和增加分区，这里不再做详细介绍。

1.删除2个分区
mysql> ALTER TABLE tb_key COALESCE PARTITION 2;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.增加三个分区
mysql> ALTER TABLE tb_key add PARTITION partitions 3;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

6 子分区

子分区其实是对每个分区表的每个分区进行再次分隔，目前只有RANGE和LIST分区的表可以再进行子分区，子分区只能是HASH或者KEY分区。子分区可以将原本的数据进行再次的分区划分。

6.1 创建组合分区：不定义每个子分区

CREATE TABLE tb_sub (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
);

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tb_sub';
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | SUBPARTITION_NAME | SUBPARTITION_METHOD | SUBPARTITION_EXPRESSION |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| p0             | RANGE            |  YEAR(purchased)     | 1990                  |          0 | p0sp0             | HASH                |  TO_DAYS(purchased)     |
| p0             | RANGE            |  YEAR(purchased)     | 1990                  |          0 | p0sp1             | HASH                |  TO_DAYS(purchased)     |
| p1             | RANGE            |  YEAR(purchased)     | 2000                  |          0 | p1sp0             | HASH                |  TO_DAYS(purchased)     |
| p1             | RANGE            |  YEAR(purchased)     | 2000                  |          0 | p1sp1             | HASH                |  TO_DAYS(purchased)     |
| p2             | RANGE            |  YEAR(purchased)     | MAXVALUE              |          0 | p2sp0             | HASH                |  TO_DAYS(purchased)     |
| p2             | RANGE            |  YEAR(purchased)     | MAXVALUE              |          0 | p2sp1             | HASH                |  TO_DAYS(purchased)     |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
6 rows in set (0.00 sec)

6.2 创建组合分区：定义每个子分区

CREATE TABLE tb_sub_ev (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
        )
);

子分区生产使用较少，这里不在过多描述，详细内容参考官方手册。

https://dev.mysql.com/doc/refman/8.2/en/partitioning-subpartitions.html

7 普通表转分区表

MySQL 可以直接将存在表转成分区表，和创建表建分区的语法一样。

注意事项：http://www.cndba.cn/dave/article/131485

主键必须包含分区键
分区键表达式只支持部分函数，存储过程
分区不支持外键
MySQL目前没有自动分区功能，所以需要监控分区的使用情况，通过job自动或者定时手动添加新分区
确定数据保留期限，定期归档分区数据

7.1 创建测试表

mysql> CREATE TABLE cndba(
    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,
primary key(id)
);

mysql> insert into cndba(id,hired ,job_code,store_id) values(1,'1950-01-01',1001,1),(2,'1960-01-01',1002,2),(3,'1980-01-01',1003,3),(4,'1990-01-01',1004,4);

mysql> select * from cndba;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | NULL  | NULL  | 1950-01-01 | 9999-12-31 |     1001 |        1 |
|  2 | NULL  | NULL  | 1960-01-01 | 9999-12-31 |     1002 |        2 |
|  3 | NULL  | NULL  | 1980-01-01 | 9999-12-31 |     1003 |        3 |
|  4 | NULL  | NULL  | 1990-01-01 | 9999-12-31 |     1004 |        4 |
+----+-------+-------+------------+------------+----------+----------+
4 rows in set (0.01 sec)

7.2 查看外键约束

查看表的外键信息：http://www.cndba.cn/dave/article/131485

mysql> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA ='cndba' AND TABLE_NAME='cbdba' AND REFERENCED_TABLE_NAME is not null;
Empty set (0.00 sec)

7.3 查看 null 值

对于存在的null值，需要应用对null数据进行处理，并且程序上需要确保数据写入not null。

mysql> select count(*) from cndba where joined  is null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

7.4 查看分区列时间区间

查看分区列日期最大值，最小值，根据这个范围按年或者月创建分区：

mysql> select max(hired),min(hired) from cndba;
+---------------------+---------------------+
| max(hired)         | min(hired)         |
+---------------------+---------------------+
| 1990-01-01 00:00:00 | 1950-01-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

7.5 主键重建添加分区列

SQL>alter table cndba drop primary key,add primary key (id,hired);

这个语法本身没有问题，但是在生产环境执行，虽然不会产生大量的binlog，但是会造成主从延时，对于主从实时性要求高的。建议采用第三方的在线工具pt-osc,gh-ost。

MySQL Online DDL 工具 gh-ost 使用示例

https://www.cndba.cn/dave/article/131461

MySQL 5.7 Online DDL 说明

https://www.cndba.cn/dave/article/131460

[dave@www.cndba.cn:~]# gh-ost --allow-on-master --allow-nullable-unique-key -user="root" -password='Qft3pRB7Z6Yf2SjB' -host=192.168.56.109 -port=3308 -database="cndba" -table="cndba" -alter="DROP PRIMARY KEY,ADD PRIMARY KEY(id,hired)" -skip-foreign-key-checks=true -chunk-size=1000 -max-lag-millis=3000 -critical-load=Threads_running=200 -max-load=Threads_running=80 -allow-master-master -initially-drop-ghost-table -initially-drop-old-table -execute

注意：

目标表上必须有主键或者唯一键，gh-ost 使用该键遍历表，否则会报如下错误：
2023-12-19 21:43:51 FATAL No shared unique key can be found after ALTER! Bailing out

mysql> show create table cndba.cndba/G
*************************** 1. row ***************************
       Table: cndba
Create Table: CREATE TABLE `cndba` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`hired`),
  UNIQUE KEY `idx_si` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

7.6 普通表转化为分区表

因为 MySQL 官方的 online ddl 还不支持partition by ，避免也系统的影响，生产环境这里还是建议采用pt-osc在线将表转化为分区表。

注意：

对于 pt-online-schema-change、gh-ost 会创建表的副本并拷贝数据，所以需要主机空余表空间大于表的大小已经产生的 binlog 大小才相对安全。

原始 SQL:

ALTER TABLE cndba.cndba
PARTITION BY RANGE COLUMNS(hired) (
    PARTITION a VALUES LESS THAN ('1960-01-01'),
    PARTITION b VALUES LESS THAN ('1970-01-01'),
    PARTITION c VALUES LESS THAN ('1980-01-01'),
    PARTITION d VALUES LESS THAN ('1990-01-01'),
    PARTITION e VALUES LESS THAN MAXVALUE
)

gh-ost：

[dave@www.cndba.cn:~]# gh-ost --allow-on-master --allow-nullable-unique-key -user="root" -password='Qft3pRB7Z6Yf2SjB' -host=192.168.56.109 -port=3308 -database="cndba" -table="cndba" -alter="PARTITION BY RANGE COLUMNS(hired) (PARTITION a VALUES LESS THAN ('1960-01-01'),
    PARTITION b VALUES LESS THAN ('1970-01-01'),
    PARTITION c VALUES LESS THAN ('1980-01-01'),
    PARTITION d VALUES LESS THAN ('1990-01-01'),
    PARTITION e VALUES LESS THAN MAXVALUE
)" -skip-foreign-key-checks=true -chunk-size=1000 -max-lag-millis=3000 -critical-load=Threads_running=200 -max-load=Threads_running=80 -allow-master-master -initially-drop-ghost-table -initially-drop-old-table -execute

7.7 验证分区表

查看表：

mysql> show create table cndba.cndba/G
*************************** 1. row ***************************
       Table: cndba
Create Table: CREATE TABLE `cndba` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`hired`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE  COLUMNS(hired)
(PARTITION a VALUES LESS THAN ('1960-01-01') ENGINE = InnoDB,
 PARTITION b VALUES LESS THAN ('1970-01-01') ENGINE = InnoDB,
 PARTITION c VALUES LESS THAN ('1980-01-01') ENGINE = InnoDB,
 PARTITION d VALUES LESS THAN ('1990-01-01') ENGINE = InnoDB,
 PARTITION e VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='cndba';
+----------------+------------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+----------------------+-----------------------+------------+
| a              | RANGE COLUMNS    | `hired`              | '1960-01-01'          |          0 |
| b              | RANGE COLUMNS    | `hired`              | '1970-01-01'          |          0 |
| c              | RANGE COLUMNS    | `hired`              | '1980-01-01'          |          0 |
| d              | RANGE COLUMNS    | `hired`              | '1990-01-01'          |          0 |
| e              | RANGE COLUMNS    | `hired`              | MAXVALUE              |          0 |
+----------------+------------------+----------------------+-----------------------+------------+
5 rows in set (0.00 sec)

8 分区表转单表

在 MySQL 中分区表也可以直接转成分区表，对于小表直接执行如下 SQL:

alter table cndba remove partitioning;

但是该命令会锁表，会影响业务，所以生产环境建议使用gh-ost 操作。http://www.cndba.cn/dave/article/131485

mysql> show create table cndba.cndba/G
*************************** 1. row ***************************
       Table: cndba
Create Table: CREATE TABLE `cndba` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`hired`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE  COLUMNS(hired)
(PARTITION a VALUES LESS THAN ('1960-01-01') ENGINE = InnoDB,
 PARTITION b VALUES LESS THAN ('1970-01-01') ENGINE = InnoDB,
 PARTITION c VALUES LESS THAN ('1980-01-01') ENGINE = InnoDB,
 PARTITION d VALUES LESS THAN ('1990-01-01') ENGINE = InnoDB,
 PARTITION e VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.00 sec)


[dave@www.cndba.cn:~]# gh-ost --allow-on-master --allow-nullable-unique-key -user="root" -password='Qft3pRB7Z6Yf2SjB' -host=192.168.56.109 -port=3308 -database="cndba" -table="cndba" -alter="REMOVE PARTITIONING" -skip-foreign-key-checks=true -chunk-size=1000 -max-lag-millis=3000 -critical-load=Threads_running=200 -max-load=Threads_running=80 -allow-master-master -initially-drop-ghost-table -initially-drop-old-table -execute

此时分区表就变成了单表：

mysql> show create table cndba.cndba/G                                                                                                                  *************************** 1. row ***************************
       Table: cndba
Create Table: CREATE TABLE `cndba` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`hired`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

9 交换分区

交换分区就是将分区表中的一个分区和一个普通表中的数据互换。通常用来对分区表的数据归档。http://www.cndba.cn/dave/article/131485

官网对交换分区的说明如下：

https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange.html

我们这里直接采用官网的示例。

mysql>CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

mysql>INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
(2005, "Linda", "Black");

mysql> CREATE TABLE e2 LIKE e;

mysql> ALTER TABLE e2 REMOVE PARTITIONING;

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> select * from e2;
Empty set (0.00 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

交换之后我们就发现 p0 分区的数据和 e2 表的数据发生了交换：
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

10 创建索引

在 Oracle 数据库中，分区表有全局索引和本地索引的概念，但是MySQL(包括PostgreSQL)只实现了本地索引，并且因为有全局约束的问题，MySQL分区表明确不支持外键，并且主键和唯一键必须要包含所有分区列，否则报错。

所以 MySQL分区表的索引和普通表的索引没有区别，反而要求更严格一些。因为和普通表索引没有区别，所以这里不在演示。

注：本文部分内容参考如下博客：

https://www.cnblogs.com/chenmh/p/5623474.html

签到成功

CNDBA社区