MENU

MySQL 分区

2021 年 09 月 24 日 • 阅读: 64 • 编码,MySQL

MySQL 从 5.1 版本开始就支持分区功能,分区是指按照一定逻辑,将一个表分解成多个更小的、更容易管理的部分。 从应用的访问逻辑上来说,只有一张表或一个索引,但实际上可能这张表由数十个物理分区对象组成,每个分区都是独立的对象,可以独自处理,也可以做为表的一部分处理。

分区概述

分区有利于管理非常大的表,他采用 “分而治之” 的逻辑。分区引入了分区键 (partition Key)的概念,分区键用来根据某个区间值(或范围值)、特定值列或 HASH 函数执行数据的聚集,让数据根据规则分布在不同的分区中,让一个大对象变成一个小对象。

MySQL 分区的优势主要分为四个方面:

  • 和单磁盘或者文件系统分区相比,可以存储更多数据。
  • 优化查询

    • 在 WHERE 子句中包含分区条件是,可以只扫描必要的一个或多个分区来提高查询效率;
    • 在涉及 SUM()COUNT() 这类聚合函数的查询时,可以容易的在每个分区上并行处理,最终只需要汇总所有分区得到的结果。
    • 在 MySQL 5.7 还可以通过类似 SELECT * FROM t PARTITION(p0,p1) 这样的方式来显式的查询指定分区的数据。
  • 对于已经过期或不需要保存的数据,可以通过删除与这些数据有关的分区来快速删除数据。
  • 跨多个磁盘来分散数据查询,以获得更大的查询吞吐量。

MySQL 5.7 中,如果使用二进制安装包,会默认包含分区支持。如果以是通过源码编译安装,那么需要在编译的时候指定参数 DWITH_PARTITION_STORAGE_ENGINE

可以通过 SHOW PLUGINS 命令或查询 PLUGINS 表来确定当前的 MySQL 是否支持分区:

# 查询 PLUGINS 表
mysql> SELECT * FROM information_schema.plugins WHERE PLUGIN_NAME = 'partition'\G;
*************************** 1. row ***************************
           PLUGIN_NAME: partition
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 50730.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Mikael Ronstrom, MySQL AB
    PLUGIN_DESCRIPTION: Partition Storage Engine Helper
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.02 sec)

# SHOW PLUGINS
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
...
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
...
+----------------------------+----------+--------------------+---------+---------+
44 rows in set (0.00 sec)

通过查询的 partition 插件信息可以看到,如果 PLUGIN_STATUS 列的状态是 ACTIVE ,说明 MySQL 已经开启分区功能。

MySQL 5.7 支持创建分区的存储引擎:

名称是否支持创建分区表
MyISAM支持
InnoDB支持
Memory支持
MERGN不支持
CSV不支持
FEDERATED不支持

在 MySQL 5.7 中,同一个分区表的所有分区必须使用同一个存储引擎,且分区数量不能超过 8192 个。

使用 [STORAGE] ENGINE 分区表在设置存储引擎时,必须写在 CREATE TABLE 语句中的其他任何分区选项之前。

注意: MySQL 分区适用于一个表的所有数据和索引,不能只对数据分区而不对索引分区;反过来也是一样的。同时也不能只对表的一部分数据进行分区。MySQL 的分区表上创建的索引一定是本地 LOCAL 索引。

分区类型

在 MySQL 5.7 中可用的分区类型,主要分为以下 6 种。

  • RANGE 分区:基于一个给定连续区间范围,把数据分配到不同的分区。
  • LIST 分区: 类似于 RANGE 分区,区别在于 LIST 分区是基于枚举出来的值列表分区,RANGE 是基于给定的连续范围分区。
  • COLUMNS 分区:类似于 RANGE 和 LIST,区别在于分区键既可以是多列,又可以是非整数。
  • HASH 分区:基于给定的分区个数,将数据取模到不同分区。
  • KEY 分区:类似于 HASH 分区,但使用 MyQSL 提供的哈希函数。
  • 子分区:也叫复合分区或组合分区,即在主分区下在做一层分区,将数据再次分割。

MySQL 5.7 中,RANGE 分区、LIST 分区、HASH 分区要求分区键必须是 INT 类型,或通过表达式返回 INT 类型,但 KEY 分区、COLUMNS 分区 除外,可以使用其他类型(BLOB 和 TEXT 类型除外)做分区键。如果希望在 RANGE 或 LIST 分区中使用非 INT 列作为分区键,可以选择 COLUMNS 分区。

无论那种分区类型,要么分区表上没有主键索引/唯一索引,要么分区表的主键索引/唯一索引都必须包含分区键,也就是说不能使用主键/唯一键之外的其他字段分区,例如 emp 表的主键为 id 字段,尝试通过 store_id 字段分区的事件,MySQL 会返回失败:

mysql> CREATE TABLE emp_test (
    ->         id INT NOT NULL,
    ->         ename VARCHAR(30),
    ->         hired DATE NOT NULL DEFAULT "1970-01-01",
    ->         separated  DATE NOT NULL DEFAULT "9999-12-31",
    ->         job VARCHAR(30) NOT NULL,
    ->         store_id INT NOT NULL,
    ->         PRIMARK KEY(id)
    ->  )
    ->     PARTITION BY RANGE(store_id) (
    ->          PARTITIONS p0 VALUES LESS THAN(10),
    ->         PARTITIONS p1 VALUES LESS THAN(20),
    ->         PARTITIONS p2 VALUES LESS THAN(30),
    ->     );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'KEY(id)) PARTITION BY RANGE(store_id) (PARTITIONS p0 VALUES LESS THA' at line 8

如果去掉主键约束,创建表就会成功:

mysql> CREATE TABLE emp_test (
    ->         id INT NOT NULL,
    ->         ename VARCHAR(30),
    ->         hired DATE NOT NULL DEFAULT "1970-01-01",
    ->         separated  DATE NOT NULL DEFAULT "9999-12-31",
    ->         job VARCHAR(30) NOT NULL,
    ->         store_id INT NOT NULL
    ->  )
    ->     PARTITION BY RANGE(store_id) (
    ->         PARTITION p0 VALUES LESS THAN (10),
    ->         PARTITION p1 VALUES LESS THAN (20),
    ->         PARTITION p2 VALUES LESS THAN (30)
    ->     );
Query OK, 0 rows affected (0.04 sec)

创建表之后查看创表语句,会发现多出一些注释:/*!50100...*/,这个注释将分区部分的语句包裹其中,原因是如下几点:

  • /* ... */ 是 SQL 中的多行注释,标准SQL 一般不会执行
  • MySQL 对 标准的SQL进行了扩展,包含了自己的特性。
  • /*!...*/ 这种语句其他数据库不会执行,MySQL 经过特殊处理会选择性的执行。
  • 50100 表示 当数据库的版本高于 5.01.00 才会执行这段 SQL

分区的名字基本上遵循 MySQL 标识符的原则。

关于 MySQL 中大小写敏感的问题

在 MySQL 中,对于库名和表名是否大小写敏感,可以通过以下方式来查看:

mysql> show variables like '%lower_case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

这里 lower_case_file_system 是个不可修改的变量,代表了操作系统是否大小写敏感,OFF 代表敏感,ON 代表不敏感。lower_case_table_names 代表数据库的库名和表名是否大小写敏感, 1 代表敏感,0 代表敏感。如果操作系统对大小写不敏感,那么 lower_case_table_names 应该设置为 1,否则可能会遇到数据库实例挂起、崩溃、启动报错等问题。

注意: 列名、别名、分区名这些不区分大小写。

RANGE 分区

RANGE 分区的表是利用取值范围将数据分成分区,区间要连续并且不能互相重叠,利用 VALUES LESS THAN 操作符进行分区定义。

例如:雇员表 emp_test 根据商店id store_id 进行 RANGE 分区:

mysql> CREATE TABLE emp_test (
    ->         id INT NOT NULL,
    ->         ename VARCHAR(30),
    ->         hired DATE NOT NULL DEFAULT "1970-01-01",
    ->         separated  DATE NOT NULL DEFAULT "9999-12-31",
    ->         job VARCHAR(30) NOT NULL,
    ->         store_id INT NOT NULL
    ->  )
    ->     PARTITION BY RANGE(store_id) (
    ->         PARTITION p0 VALUES LESS THAN (10),
    ->         PARTITION p1 VALUES LESS THAN (20),
    ->         PARTITION p2 VALUES LESS THAN (30)
    ->     );
Query OK, 0 rows affected (0.04 sec)

在商店 1 ~ 9 工作的雇员对应的所有行,会存到分区 P0 中;在商店 10 ~ 19 工作的员工会存到 P1 中,依次类推。

每个分区都必须按顺序定义,从低到最高,这是 PARTITION BY RANGE 语法的要求:类似 java 中的 switch case 语句。

如果插入了商店 ID 大于等于 30 的 的行,会出现错误,因为规则中没有包含 大于等于 30 的行,服务器不知道把数据存在哪里。

mysql> INSERT INTO emp_test(id,ename,hired,job,store_id) VALUES(233,'MILLER','1982-01-23','CLERK',50);
ERROR 1526 (HY000): Table has no partition for value 50

可以在设置分区的时候,使用 VALUES LESS THAN MAXVALUE 该子句提供给所有大于明确指定最高值的值,MAXVALUE 表示最大的可能的整数值。例如增加 P3 分区存储所有商店大于 30 的行,之后再插入就没问题了:

mysql> ALTER TABLE emp_test ADD partition (partition p3 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO emp_test(id,ename,hired,job,store_id) VALUES(233,'MILLER','1982-01-23','CLERK',50);
Query OK, 1 row affected (0.00 sec)

MySQL 支持在 VALUE LESS THAN 子句中使用此表达式,比如,日期作为 RANGE 分区的分区列:

mysql> CREATE TABLE emp_date (
    ->         id INT NOT NULL,
    ->         ename VARCHAR(30),
    ->         hired DATE NOT NULL DEFAULT "1970-01-01",
    ->         separated  DATE NOT NULL DEFAULT "9999-12-31",
    ->         job VARCHAR(30) NOT NULL,
    ->         store_id INT NOT NULL
    ->  )
    ->     PARTITION BY RANGE(YEAR(separated)) (
    ->         PARTITION p0 VALUES LESS THAN (1995),
    ->         PARTITION p1 VALUES LESS THAN (2000),
    ->         PARTITION p2 VALUES LESS THAN (2005)
    ->     );
Query OK, 0 rows affected (0.04 sec)

注意:在 RANGE 分区中,如果分区键的值是 NULL,则会被当成最小值来处理。

RANGE 分区适用于以下两种情况:

  • 当需要删除过期的数据时,只需要简单 ALTER TABLE emp DROP PARTITON p0 来删除 p0 分区中的数据。对于具有上百万条记录的表来说,删除分区要比运行一个 DELETE 语句有效得多。
  • 经常运行包含分区键的查询,MySQL 可以很快确定有那些分区需要扫描,例如查询商店 id 大于 25 的记录数量,MySQL 只需要扫描 p2 分区即可。

LIST 分区

LIST 分区是建立离散的值列表来告诉数据库特定的值属于那个分区,LIST 分区在很多方面属于 RANGE 分区,区别在于 LIST 分区是从属于一个枚举列表的值的集合,而 RANGE 分区是一个连续区间枚举值的集合。

LIST 分区通过使用 PARTITION BY LIST (expr) 子句来实现,expr 是某列值或基于某列值返回整数的一个表达式,然后通过 VALUES IN(value_list) 的方式来定义分区,其中 value_list 是一个逗号分割的证书列表。与 RANGE 分区的不同,LIST 分区不需要声明任何特定的顺序。

LIST 分区创建方式:

mysql> CREATE TABLE expenses(
    ->     expenses_date DATE NOT NULL,
    ->     category INT,
    ->     amount DECIMAL(10,3)
    -> )PARTITION BY LIST(category) (
    ->     PARTITION p0 VALUES IN (3,5),
    ->     PARTITION p1 VALUES IN (1,10),
    ->     PARTITION p2 VALUES IN (4,9),
    ->     PARTITION p3 VALUES IN (2,6)
    -> );
Query OK, 0 rows affected (0.05 sec)

LIST 分区要求所有插入的记录的分区键,必须在 VALUES IN(value_list) 中能找到,否则操作会失败并报错。

如果要使用非整数分区,则可以创建 LIST COLUMNS 分区:

mysql> CREATE TABLE expenses(
    ->     expenses_date DATE NOT NULL,
    ->     category VARCHAR(30),
    ->     amount DECIMAL(10,3)
    -> )PARTITION BY LIST COLUMNS (category) (
    ->     PARTITION p0 VALUES IN ('lodging','food'),
    ->     PARTITION p1 VALUES IN ('filghst','ground transportation')
    -> );
Query OK, 0 rows affected (0.03 sec)

COLUMNS 分区

COLUMNS 是 MySQL 5.5 引入的分区类型,在这之前 RANGE 和 LIST 仅支持整数分区,从而需要通过额外的函数计算或者通过额外的转换为整数再分区。COLUMNS 分区解决了这一问题。

COLUMNS 分区可以分为 RANGE COLUMNS 和 LIST COLUMNS 分区,两者都支持 整数、日期时间、字符串三大数据类型:

  • 整数类型:tinyint、smallint、mediumint、int、bigint,其他类型均不支持,例如:decimail、float
  • 日期时间类型:date、datetime
  • 字符串类型:char、varchar、binary、varbinary 不支持 text、blob

注意: 在 MySQL 5.7 中,COLUMNS 分区仅支持一个或多个字段名作为分区键,不支持表达式作为分区,区别于 RANGE 和 LIST 分区。

多列分区创建方式:

mysql> CREATE TABLE rc3 (
    ->     a int,
    ->     b int
    -> ) PARTITION BY RANGE COLUMNS(a,b)(
    ->     PARTITION p01 VALUES LESS THAN (0,10),
    ->     PARTITION p02 VALUES LESS THAN (10,10),
    ->     PARTITION p03 VALUES LESS THAN (10,20),
    ->     PARTITION p04 VALUES LESS THAN (10,35),
    ->     PARTITION p05 VALUES LESS THAN (10, MAXVALUE),
    ->     PARTITION p06 VALUES LESS THAN (MAXVALUE, MAXVALUE)
    -> );
Query OK, 0 rows affected (0.11 sec)

RANGE COLUMNS 的分区键比较是记忆和字段组比较的,这个和之前的 RANGE 分区键有些差异。也可以说是多列排序,先根据 a 字段排序再根据 b字段排序,最后根据排序结果来存放数据的,

HASH 分区

HASH 分区主要是用来分散热点读,确保数据在预先确定的几个分区中尽可能的平均分布。对一个表使用 HASH 分区时,MySQL 会对分区键应用一个散列函数,以确保数据应当存放在 N 个分区中的那个分区中。

HASH 支持两种分区,即常规的 HASH 分区和线性 HASH 分区,常规 HASH 分区使用的是取模算法,线性 HASH 分区使用的是一个线性的 2 的幂运算法则。

常规 HASH 分区

使用 PARTITION BY HASH (expr) PARTITIONS num 子句对分区类型、分区键、分区数量进行定义,其中 expr 是某列值或返回整数的表达式;num 是一个非负数的整数,表示分割分区的数量,默认为 1。

创建常规 HASH 分区:

mysql> CREATE TABLE emp_hash (
    ->     id INT NOT NULL,
    ->     ename VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job VARCHAR(30) NOT NULL,
    ->     store_id INT NOT NULL
    -> ) PARTITION BY HASH(store_id) PARTITIONS 4;
Query OK, 0 rows affected (0.07 sec)

存储数据时会使用:N = MOD(expr,num),将数据存入 N 分区。

表达式 expr 可以是 MySQL 中有效的任何函数或其他表达式,只要他们返回一个既非常数也非随机数的整数。每当插入、更新、删除一行数据时,这个表达式都需要计算一次,这意味着非常复杂的表达式可能会引起线性问题。

线性 HASH 分区

常规分区可以通过取模让数据平均分布在每个分区中,每个分区管理的数据少了,提高了查询的效率;可是当我们需要合并、新增分区时就出现问题了。原先是五个分区,那么取模运算后 是 0 ~ 4 分布在五个分区中,新增一个分区后 取模运算的结果变成了 0 ~ 5 六个分区,原先的五个分区的大部分数据都需要重新计算分区。所以常规的 HASH 分区在管理上带来的代价太大,不适合需要灵活变动的分区。为了降低分区管理上的代价,MySQL 提供了线性 HASH 分区,分区函数是一个线性的 2 的幂的运算法则。

线性 HASH 分区和常规的 HASH 分区的唯一区别在 PARTITOIN BY 子句中添加了 LINEAR关键字:

创建线性 HASH 分区:

mysql> CREATE TABLE emp_hash_linear (
    ->     id INT NOT NULL,
    ->     ename VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job VARCHAR(30) NOT NULL,
    ->     store_id INT NOT NULL
    -> ) PARTITION BY LINEAR HASH(store_id) PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)

线性分区的优点是:在分区维护(新增、删除、合并、拆分分区)时, MySQL 能够更加快速的处理;缺点是:对比常规的 HASH 分区 取模的时候,线性 HASH 的各个分区之间的数据分布的不太均衡。

KEY 分区

KEY 分区 非常类似与 HASH 分区,只不过 HASH 分区允许用户使用自定义的表达式,而 KEY 分区却不允许,需要使用 MySQL 提供的 HASH 函数;同时 HASH 分区仅支持整数分区,而 KEY 分区支持 除 BLOB 和 TEXT 外的其他列类型作为分区键。

使用 PARTITIOM BY KEY(expr) 子句创建一个 KEY 分区表,expr 是 零个或多个字段名的列表,下面的 SQL 创建了一个基于 job 字段进行 KEY 分区的表,表被分为 4 个分区。

mysql> CREATE TABLE emp_key (
    ->     id INT NOT NULL,
    ->     ename VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job VARCHAR(30) NOT NULL,
    ->     store_id INT NOT NULL
    -> ) PARTITION BY KEY(job) PARTITIONS 4;
Query OK, 0 rows affected (0.06 sec)
  • 与 HASH 分区不同,KEY 分区创建的时候可以不指定分区键,默认会优先使用主键作为分区键。
  • 如果没有主键的情况,会选择非空唯一的字段作为分区键
  • 如果都没有就必须指定分区键,否则会报错。

注意:在使用 KEY 分区的表上不能执行 ALTER TABLE DROP PRIMARY KEY 语句来删除主键,MySQL 会返回错误。

与 HASH 分区一样的是 KEY 分区同样可以使用 LINEAR 关键字,分区编号是通过 2 的幂的算法得到的,而不是通过取模得到的。

KEY 分区在处理大量数据记录时,可以有效分散热点。

子分区

子分区是指对分区表的每个分区再次进行分割,又被称之为复合分区。MySQL 5.7 支持对已经通过 RANGE 或 LIST 分区的表再次进行分区。子分区既可以使用 HASH 分区,也可以使用 KEY 分区。

mysql> 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
    ->     );
Query OK, 0 rows affected (0.08 sec)

ts 表有 3 个 RANGE 分区,每个分区又被分为两个子分区,整张表实际保存时变成了 3 * 2 = 6 个分区。

复合分区适合用来存储非常大量的数据记录,在使用时要注意以下几点:

  • 每个分区的子分区数量必须一致
  • 如果要显式指定子分区,则每个分区都要显式指定。
  • 子分区的名称在一张表中是唯一的,不能重复。

显式指定子分区:

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) (
            SUBPARTITION s0,
            SUBPARTITION s1,
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3,
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5,
        )
    );

MySQL 分区处理 NULL 方式

MySQL 不禁止在分区键上使用 NULL,分区键可能是一个字段或一个用户定义的表达式。一般情况下 MySQL 的分区把 NULL 当成零值,或一个最小值进行处理。

  • RANGE 分区:NULL 被当成最小值处理
  • LIST 分区:NULL 必须出现在枚举列表中,否则报错
  • HASH/KEY 分区:NULL 值被当作一个零值来处理

分区管理

MySQL 提供了 添加、删除、重定义、拆分、交换分区的命令,都可以通过 ALTER TABLE 命令实现。

RANGE / LIST 分区管理

删除分区

删除分区后 分区对应的数据也会被删除

ALTER TABLE emp_date DROP PARTITION p2;

删除分区命令执行后,并不显示实际从表中删除的行数,但这并不代表没有数据被删除

添加分区

-- RANGE 分区只能从已有分区的最大区间往后添加,例如:目前最大是 2030 如果添加 2025 则会报错
alter table emp_date add partition (partition p5 values less than(2040))

-- LIST 分区的枚举列表不能与已有的分区枚举重复没否则将会报错
alter table expenses add partition (partition p5 values in (7,8))

拆分分区

MySQL 提供了在不丢失数据的情况下,通过重新定义分区的语句 ALTER TABLE REORGANIZE PARTITOIN INTO 重新定义分区。

emp_date 分区结构:

mysql> CREATE TABLE emp_date (
    ->         id INT NOT NULL,
    ->         ename VARCHAR(30),
    ->         hired DATE NOT NULL DEFAULT "1970-01-01",
    ->         separated  DATE NOT NULL DEFAULT "9999-12-31",
    ->         job VARCHAR(30) NOT NULL,
    ->         store_id INT NOT NULL
    ->  )
    ->     PARTITION BY RANGE(YEAR(separated)) (
    ->         PARTITION p0 VALUES LESS THAN (1995),
    ->         PARTITION p1 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2015)
    ->         PARTITION p4 VALUES LESS THAN (2030)
    ->     );

计划拆分 p3 分区(2000 - 2015)为 p2 分区(2000 - 2005)和 p3 分区(2005 - 2015):

alter table emp_date reoganize partition p3 into (
    PARTITION p2 VALUES LESS THAN (2005),
    PARTITION p3 VALUES LESS THAN (2015)
);

合并分区

也可以使用同样的命令合并分区:

alter table emp_date reoganize partition p1,p2,p3 into (
    PARTITION p1 VALUES LESS THAN (2015)
);

注意:

  • 重新定义分区时,只能重新定义相邻的分区,不可以跳过某个 RANGE 分区进行重新定义。
  • 重新定义的分区区间必须和原分区区间覆盖相同;
  • 重新定义时不能改变分区类型,例如将 RANGE 分区变成 HASH 分区

LIST 重新定义分区

mysql> CREATE TABLE expenses(
    ->     expenses_date DATE NOT NULL,
    ->     category VARCHAR(30),
    ->     amount DECIMAL(10,3)
    -> )PARTITION BY LIST COLUMNS (category) (
    ->     PARTITION p0 VALUES IN (1),
    ->     PARTITION p1 VALUES IN (2,3)
    -> );

比如现在调整 p0 分区的枚举列表为 1 和 4,通过之前的 add partition 的方式是不可行的。

我们可以通过新增分区和重新定义分区来实现:

  • 创建新分区 p2 枚举值为 4
  • 将 p0 和 p2 分区合并为 p0 分区
alter table expenses add partition (partition p2 values in (4));

alter table emp_date reoganize partition p0,p1,p2 into (
    PARTITION p1 VALUES IN (1,4),
    PARTITION p1 VALUES IN (2,3)
);

HASH / KEY 分区管理

HASH 和KEY 分区的操作是一样的,所以只列举 HASH 分区的操作方法。

删除分区

使用 ALTER TABLE COLALESCE PARTITION 语句来合并(减少/删除)分区。

将 hash 分区由四个减少为 2 个

alter table emp_hash coalesce partition 2;

注意:改子句只能用于合并分区,不能添加分区。

增加分区

使用 ALTER TABLE ADD PARTITION 语句来实现。

例如 现在 emp_hash 表有 2 个 hash 分区,需要增加到 10 个:

alter table emp_hash add partition partitions 8;

注意:这里是指 新增 8 个分区,而不是增加到 8 个分区。


笔记参考:《MySQL 深入浅出 第三版》

CC版权: 本篇博文采用《CC 协议》,转载必须注明作者和本文链接