mysql分区_mysql 分区

如果一个表有任何唯一键，那么该表的分区表达式中使用的每个列都必须是每个唯一键的一部分，包括主键
删除分区也会删除该分区中的所有数据

概述

• 分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件
• 分区是将一个表或索引分解成多个更小，更可管理的部分。
• 每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。
• 对于分区的使用应该非常小心，如果不清楚如何使用分区可能会对性能产生负面的影响。
• 无论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

在 MySQL 8.0 中，唯一支持分区的存储引擎是InnoDB和 NDB. 不支持分区的存储引擎不能使用分区；其中包括
MyISAM、MERGE、 CSV和FEDERATED存储引擎。

MySQL 8.0 不支持 垂直分区，其中一个表的不同列被分配到不同的物理分区。目前没有计划将垂直分区引入 MySQL。

要创建分区表，您必须使用支持它们的存储引擎。在 MySQL 8.0
中，同一个分区表的所有分区必须使用相同的存储引擎。但是，没有什么可以阻止您对同一 MySQL
服务器甚至同一数据库中的不同分区表使用不同的存储引擎。

分区适用于表的所有数据和索引；您不能只分区数据而不分区索引，反之亦然，也不能只分区表的一部分。
可以使用用于创建分区表 的语句的子句 的DATA DIRECTORY and INDEX DIRECTORY选项将 每个分区的数据和索引分配给特定目录

在MySQL 8.0中，分区支持由InnoDB和NDB存储引擎提供。
MySQL 8.0目前不支持使用InnoDB或NDB以外的任何存储引擎（如MyISAM）对表进行分区。

优点

• 分区可以在一个表中存储比单个磁盘或文件系统分区上更多的数据。
• 通过删除仅包含该数据的分区（或多个分区），通常可以轻松地从分区表中删除失去其用途的数据。相反，在某些情况下，通过添加一个或多个新分区来专门存储该数据，可以极大地促进添加新数据的过程。
• 由于满足给定WHERE子句的数据只能存储在一个或多个分区上，因此可以极大地优化某些查询，这会自动从搜索中排除任何剩余的分区。因为在创建分区表后可以更改分区，所以您可以重新组织数据以增强在首次设置分区方案时可能不经常使用的频繁查询。这种排除不匹配分区（以及它们包含的任何行）的能力通常称为 分区修剪

分区限制

分区键、主键和唯一键的

分区表的分区表达式中使用的所有列都必须是该表可能具有的每个唯一键的一部分。

错误例子

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
) ENGINE = INNODB
PARTITION BY HASH(col3)
PARTITIONS 4;
# A PRIMARY KEY must include all columns in the table's partitioning function
# col3并不是唯一键的一部分   将col3也设为唯一键即可成功


CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
) ENGINE = INNODB
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
# A PRIMARY KEY must include all columns in the table's partitioning function   
# PRIMARY KEY必须包括表分区函数中的所有列 

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1,col3),
    UNIQUE KEY (col2,col4)
) ENGINE = INNODB
PARTITION BY HASH(col1 + col4)
PARTITIONS 4;
# 语法错误  不存在包含唯一键的共同列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

正确例子

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;


CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

因为根据定义，每个主键都是唯一的键，所以如果表有主键，那么这个限制还包括该表的主键(也就是唯一键和主键都得有共同列)

错误例子

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)ENGINE=INNODB
PARTITION BY HASH(col3)
PARTITIONS 4;
# A PRIMARY KEY must include all columns in the table's partitioning function
# PRIMARY KEY必须包括表分区函数中的所有列
# col3 不在主键列表中

CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col3),
    UNIQUE KEY(col2)
)ENGINE=INNODB
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;
# A PRIMARY KEY must include all columns in the table's partitioning function
# PRIMARY KEY必须包括表分区函数中的所有列
# 主键列表未包含col2列，虽然col2列为唯一键依然不行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

主键并不包括分区表达式中引用的所有列

如果表没有唯一键（包括没有主键），则此限制不适用，并且只要列类型与分区类型兼容，就可以在分区表达式中使用任何列。

出于同样的原因，除非键包括表的分区表达式所使用的所有列，否则以后不能向分区表添加唯一键。

存储引擎相关的分区限制

在MySQL 8.0中，分区支持实际上不是由MySQL Server提供的，而是由表存储引擎自己的或本机分区处理程序提供的。在MySQL 8.0中，只有InnoDB和NDB存储引擎提供本机分区处理程序。这意味着分区表不能使用除这些之外的任何其他存储引擎创建。

函数相关的分区限制

分区表达式中只允许使用下表所示的MySQL函数：

ABS()

CEILING() (see CEILING() and FLOOR())

DATEDIFF()

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

EXTRACT() (see EXTRACT() function with WEEK specifier)

FLOOR() (see CEILING() and FLOOR())

HOUR()

MICROSECOND()

MINUTE()

MOD()

MONTH()

QUARTER()

SECOND()

TIME_TO_SEC()

TO_DAYS()

TO_SECONDS()

UNIX_TIMESTAMP() (with TIMESTAMP columns)

WEEKDAY()

YEAR()

YEARWEEK()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

分区类型

目前MySQL支持一下几种类型的分区，RANGE分区，LIST分区，HASH分区，KEY分区。

如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。

RANGE分区

按范围分区的表的分区方式是，每个分区都包含分区表达式值位于给定范围内的行。范围应连续但不重叠，并使用VALUES LESS THAN运算符定义

当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。

语法 PARTITION BY RANGE()

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)ENGINE=INNODB
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

对timestamp分区

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
4
5
6
7
8
9
10
11
12
13
14
15
16
17

使用DATE或DATETIME列作为分区列，按RANGE COLUMNS对表进行分区

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

RANGE columns不支持使用除date或DATETIME之外的日期或时间类型的分区列

MySQL的分区实现将NULL视为小于任何非NULL值
使用RANGE分区处理NULL。如果在按RANGE分区的表中插入一行，使用于确定分区的列值为NULL，则该行将插入最低的分区

范围列分区

RANGE COLUMNS分区与RANGE分区有以下显著区别：

• RANGE COLUMNS不接受表达式，只接受列的名称。
• RANGE COLUMNS接受一个或多个列的列表。
• RANGE COLUMNS分区基于元组（列值列表）之间的比较，而不是标量值之间的比较。RANGE COLUMNS分区中的行的放置也基于元组之间的比较；
• RANGE COLUMNS分区列不限于整数列；字符串、DATE和DATETIME列也可以用作分区列。

语法：

CREATE TABLE table_name
PARTITION BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)

column_list: #一个或多个列的列表
    column_name[, column_name][, ...]

value_list:#一个值列表
    value[, value][, ...]
1
2
3
4
5
6
7
8
9
10
11
12

分区列列表和定义每个分区的值列表中的元素必须以相同的顺序出现。此外，值列表中的每个元素必须与列列表中的相应元素具有相同的数据类型。
例：

CREATE TABLE rcx (
  a INT,
  b INT,
  c CHAR(3),
  d INT
 )
 	PARTITION BY RANGE COLUMNS(a,d,c) (
    PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
    PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );
1
2
3
4
5
6
7
8
9
10
11
12

在RANGE分区中，生成的表达式值等于VALUES LESS THAN中的限制值的行永远不会放在相应的分区中；但是，在使用RANGE COLUMNS分区时，有时可能会将分区列列表的第一个元素的值与VALUES LESS THAN值列表中第一个元素值相等的行放在相应的分区中。

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

LIST分区

LIST分区和RANGE分区很相似，只是分区列的值是离散的，不是连续的。LIST分区使用VALUES IN，因为每个分区的值是离散的，因此只能定义值。

• 当按list进行分区时，可以只匹配整数列表
• 使用LIST column分区时，值列表中可能会使用其他列类型
• 与按范围定义分区的情况不同，列表分区不需要按任何特定顺序声明

语法例子

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)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

分区表达式的所有预期值都应该包含在PARTITION…values in（…）子句中。包含不匹配的分区列值的INSERT语句失败，并出现错误

当使用单个INSERT语句将多行插入到单个InnoDB表中时，InnoDB将该语句视为单个事务，因此如果存在任何不匹配的值，则会导致语句完全失败，因此不会插入任何行。

可以使用IGNORE关键字忽略此类错误。如果这样做，则不会插入包含不匹配的分区列值的行，但会插入具有匹配值的任何行，并且不会报告错误

使用LIST分区处理NULL。由LIST分区的表如果且仅当其中一个分区是使用包含NULL的值列表定义的，则允许NULL值。相反，由LIST分区的表如果在值列表中未显式使用NULL，则会拒绝导致分区表达式为NULL值的行
也就是分区包含null可以插入否则插入失败

LIST COLUMNS partitioning

例

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')
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

与按RANGE COLUMNS进行分区一样，不需要在COLUMNS（）子句中使用表达式将列值转换为整数。

CREATE TABLE customers_2 (
    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(renewal) (
    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

HASH分区

HASH分区主要用于确保在预定数量的分区之间均匀分布数据。

该类型的扩展LINEAR HASH也可用

使用HASH分区对表进行分区，需要在CREATE table语句中附加partition BY HASH（expr）子句，其中expr是返回整数的表达式

语法例子

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;
1
2
3
4
5
6
7
8
9
10
11

如果不包含PARTITIONS子句，分区数默认为1；使用后面没有数字的PARTITIONS关键字会导致语法错误。

使用PARTITION BY HASH时，存储引擎根据表达式结果的模数确定要使用num分区中的哪个分区。换句话说，对于给定的表达式expr，存储记录的分区是分区号N，其中N=MOD（expr，num）

线性HASH分区

线性哈希使用两种算法的线性幂，而常规哈希使用哈希函数值的模。

从语法上讲，线性哈希分区和常规哈希之间的唯一区别是在PARTITION BY子句中添加了linear关键字
语法：

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
2
3
4
5
6
7
8
9
10
11

优点：分区的添加、删除、合并和拆分速度快得多，这在处理包含大量（TB）数据的表时非常有用。
缺点：与使用常规哈希分区获得的分布相比，数据不太可能在分区之间均匀分布

KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用数据库提供的函数进行分区。

提供了一个或多个要评估的列，这些列可以包含除整数值以外的其他值，因为MySQL提供的哈希函数保证了整数结果，而不管列数据类型如何。

分区键的任何列必须包含表主键的一部分或全部（如果表有主键的话）。如果没有指定列名作为分区键，则使用表的主键（如果有），如果没有主键，但有唯一键，那么唯一键用于分区键：但是，如果唯一键列未定义为NOTNULL 则语句失败

该类型的扩展LINEAR KEY也可用。

按KEY或LINEAR KEY进行分区时，可以使用DATE、TIME或DATETIME列作为分区列，而无需对列值进行任何修改。

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)ENGINE=INNODB
PARTITION BY KEY(joined)
PARTITIONS 6;
1
2
3
4
5
6
7
8
9

使用HASH和KEY分区处理NULL。对于由HASH或KEY分区的表，NULL的处理有些不同。在这些情况下，任何产生NULL值的分区表达式都被视为其返回值为零

线性key分区

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

子分区

子分区也称为复合分区，是对分区表中每个分区的进一步划分
例句：

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
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意：

1. 每个分区必须具有相同数量的子分区
2. 如果在分区表的任何分区上使用SUBPARITION显式定义任何子分区，则必须全部定义
3. 子分区名称在整个表中必须是唯一的

分区维护

MySQL分区针对TO_DAYS（）、YEAR（）和TO_SECONDS（）函数进行了优化。但是，您可以使用其他返回整数或NULL的日期和时间函数，例如WEEKDAY（）、DAYOFYEAR（）或MONTH（）。

分区名称通常遵循管理其他MySQL标识符的规则，例如表和数据库的标识符。但是，应该注意分区名称不区分大小写。

删除分区 比delete删除效率要高很多

ALTER table 表名 DROP PARTITION p0；
1

对已存在的表进行分区

ALTER TABLE 表名 PARTITION BY 分区类型() (分区语句)
1

查看每个分区的记录行数和占用大小

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION ,DATA_LENGTH
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='md_rfid';
1
2

##清理分区数据为空，保留分区不删除，仅仅是清理数据

alter table bm_scenes_data_reminder truncate partition 分区名; 
1

增加分区

范围分区

ALTER TABLE 表名 ADD PARTITION (PARTITION 分区名 VALUES LESS THAN (条件) ENGINE = InnoDB);
1

CREATE TABLE t5 (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)ENGINE=INNODB
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

ALTER TABLE t5 ADD PARTITION (PARTITION p5 VALUES LESS THAN (500) );
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

因此如果需要在中间添加分区只能将需要添加的分区后边的分区都删除，再增加要加的分区再将删除的分区加回去。
但是这种方式会将删除的分区数据删除

list分区

list分区添加分区和rabge分区一样，只是list分区添加分区时不能将包含现有分区的值列表中的值添加进新分区否则会报错
解决：
可以通过添加一个只包含不冲突值的分区来处理此问题，然后重新组织新分区和现有分区，以便将存储在现有分区中的值移到新分区中

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);
1
2
3
4
5

在不丢失数据的情况下，重新定义分区

ALTER TABLE t5 REORGANIZE PARTITION p0 INTO (
	PARTITION n0 VALUES LESS THAN (1950),
	PARTITION n1 VALUES LESS THAN (1960)
);
1
2
3
4

这个命令实际上是将p0分区拆分为两个新分区n0和n1,它还根据两个partition…VALUES…子句中包含的规则，将存储在p0中的数据移动到新分区中

REORGANIZE PARTITION子句也可以用于合并相邻分区

ALTER TABLE t5 REORGANIZE PARTITION n0,n1 INTO(PARTITION p0 VALUES LESS THAN (1960))
1

使用REORGANIZE PARTITION拆分或合并分区时不会丢失任何数据。在执行上述语句时，MySQL将存储在分区n0和n1中的所有记录移动到分区p0中

HASH和KEY

不能像从按RANGE或LIST分区的表中删除分区一样，从按HASH或KEY分区的表删除分区。然而，您可以使用ALTER TABLE…COALESCE PARTITION合并HASH或KEY分区

#创建一张表同时生成12个分区
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

#将分区从12个降为8个
ALTER TABLE clients COALESCE PARTITION 4;
# 将分区从12个添加到18个
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

COALESCE同样适用于按HASH、KEY、LINEAR HASH或LINEAR KEY分区的表

COALESCE PARTITION后面的数字是要合并到其余分区中的分区数，换句话说，它是要从表中删除的分区数

使用语法

查询

SELECT id, CONCAT(fname, ' ', lname) AS name FROM employees PARTITION (p0) ORDER BY lname;
# 关联写法
SELECT e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name, s.city AS City, d.name AS department
   FROM employees AS e
   JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
   JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
   ORDER BY e.lname;
1
2
3
4
5
6
7

修改

UPDATE employees PARTITION (p0)  SET store_id = 2 WHERE fname = 'Jill';
1

取消分区

ALTER TABLE 表名 REMOVE PARTITIONING
1

分区的限制

分区表达式中不允许使用以下构造：

• 存储过程、存储函数、可加载函数或插件。
• 声明的变量或用户变量。
• 不允许使用/运算符。
• 分区表达式中不允许使用位运算符|、&、^、<<、>>和~。

性能注意事项：

1. 文件系统操作。分区和重新分区操作（如ALTER TABLE with PARTITION BY…、REORGANIZE PARTITION或REMOVE PARTTIONING）的实现依赖于文件系统操作。这意味着这些操作的速度受文件系统类型和特征、磁盘速度、交换空间、操作系统的文件处理效率以及MySQL服务器选项和与文件处理相关的变量等因素的影响。特别是，您应该确保启用了large_files_support，并正确设置了open_files_limit。通过启用InnoDB_file_per_table，可以提高涉及InnoDB表的分区和重新分区操作的效率。
2. 在表上执行分区操作的进程会对该表进行写锁定。这些表的读数相对不受影响；挂起的INSERT和UPDATE操作将在分区操作完成后立即执行。
3. 分区修剪。与非分区表一样，正确使用索引可以显著加快对分区表的查询。
4. 分区表支持索引条件下推。
5. MySQL 8.0中，LOAD DATA使用缓冲来提高性能。您应该知道，缓冲区每个分区使用130 KB内存来实现这一点。
6. 最大分区数。不使用NDB存储引擎的给定表的最大可能分区数为8192。此数字包括子分区。
7. 使用NDB存储引擎的表的用户定义分区的最大可能数量是根据所使用的NDB群集软件的版本、数据节点数和其他因素确定的
8. 使用用户定义分区的InnoDB表的定义不能包含外键引用；不能对定义中包含外键引用的InnoDB表进行分区。
9. InnoDB表定义不能包含对用户分区表的外键引用；具有用户定义分区的InnoDB表不能包含外键引用的列。
10. ALTER TABLE…排序依据。对分区表运行的ALTER TABLE…ORDER BY列语句只会导致对每个分区内的行进行排序。
11. 添加列…ALGORITHM=INSTANT。一旦对分区表执行ALTER TABLE…ADD COLUMN…ALGORITHM=INSTANT，就无法再与该表交换分区。
12. 修改主键对REPLACE语句的影响。在某些情况下，可以修改表的主键。请注意，如果您的应用程序使用REPLACE语句，并且您这样做了，那么这些语句的结果可能会发生很大变化。
13. FULLTEXT索引。分区表不支持FULLTEXT索引或搜索。
14. 空间列。分区表中不能使用空间数据类型为POINT或GEOMETRY的列。
15. 临时表。无法对临时表进行分区。
16. 日志表。无法对日志表进行分区；此类表上的ALTER TABLE…PARTITION BY…语句失败，并返回错误。
17. 分区键的数据类型。分区键必须是整数列或解析为整数的表达式。不能使用使用ENUM列的表达式。列或表达式值也可以为NULL；

当按[LINEAR]KEY进行分区时，可以使用除TEXT或BLOB之外的任何有效MySQL数据类型的列作为分区键，因为内部密钥哈希函数从这些类型中生成正确的数据类型。
按RANGE COLUMNS或LIST COLUMNS分区时，可以使用字符串、DATE和DATETIME列