Mysql分区表

---

分区表

分区概述

分区功能并不是在存储引擎层完成的，因此不是只有InnoDB存储殷勤支持分区，常见的存储引擎 MyISAM、NDB等都支持。但也不是所有的存引擎都支持，如CSV、FEDORATED、MERGE等就不支持。

Mysql在5.1版本时添加了对分区的支持。分区的过程是将一个表或索引分解成多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

Mysql支持的分区类型为水平分区，并不支持垂直分区。此外，Mysql的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前Mysql不支持全局分区。

可以通过命令来查看数据库是否开启了分区功能：

mysql> SHOW VARIABLES LIKE '%partition%'\G;

Mysql分区主要用于数据库高可用性管理，同时可能会给某些sql语句性能带来提高，但是不代表只要使用分区，数据库就能运行得更快。

---

分区类型

• RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。Mysql5.5开始支持RANGE COLUMNS的分区。
• LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。Mysql5.5开始支持LIST COLUMNS的分区。
• HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
• KEY分区：根据Mysql数据库提供的哈希函数来进行分区。

不论创建何种分区，如果表中存在主键或唯一索引（允许NULL值）时，分区列必须是唯一索引的一个组成部分，不需要整个（多个）唯一索引列都是分区列；如果建表时没有指定主键，唯一索引，可以指定任何一个列作为分区列。

RANGE分区

上图表示的是创建了一个id列的区间分区表。当id小于10时，数据插入p0分区。当id大于等于10小于20时，数据插入p1分区。即底层的物理文件不再是同一个文件。

可以通过查询 information_schema 架构下的 PARTITIONS 表来查看每个分区的具体信息：

mysql> SELECT * FROM information_schema.PARTITIONS WHERE table_schema=database() AND table_name='t'\G;

注意： 由于我们定义了分区，因此对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，Mysql会抛异常。所以当我们在上图中插入一个大于等于20的值时，会报错。

对于该问题，可以对分区添加一个MAXVALUE值的分区。MAXVALUE可以理解为正无穷，因此所有大于等于20且小于MAXVALUE的值被放入p2分区。命令如下图：

用途： RANGE分区主要用于日期列的分区，例如对于销售类的表，可以根据年来分区存放销售记录。

在查询 sql 前使用 EXPLAIN PARTITIONS 命令，sql 优化器只需要去搜索某个分区，而不会去搜索所有的分区——称为 Partition Pruning（分区修剪），故查询的速度得到了大幅提升。

注意： 对于RANGE分区的查询，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择。

LIST分区

LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的。

如上图，不同于RANGE分区中定义的VALUES LESS THAN 语句，LIST分区使用VALUES IN。因为每个分区的值是离散的，因此只能定义值。

注意： 同样，插入的值不在分区的定义中，Mysql同样会抛出异常。

另外，在用 INSERT 插入多个行数据的过程中遇到分区未定义的值时，MyISAM 和 InnoDB 存储引擎的处理完全不同。MyISAM引擎会将之前的行数据都插入，但之后的数据不会被插入。而InnoDB存储引擎将其视为一个事务，因此没有任何数据插入。

HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。在HASH分区中，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量，默认是1，如下图：

Mysql还支持一种称为 LINEAR HASH 的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置。如下图：

优点： LINEAR HASH 分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷。这有利于处理含有大量数据的表。

缺点： 与使用的HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。

KEY分区

KEY分区和 HASH 分区相似，不同之处在于HASH 分区使用用户自定义的函数进行分区，KEY 分区使用 Mysql 数据库提供的函数进行分区。对于NDB Cluster 引擎，Mysql 数据库使用 MD5 函数来区分；对于其他存储引擎，Mysql 使用其内部的哈希函数，这些函数基于与 PASSWORD() 一样的运算法则。如下图：

在 KEY 分区中使用关键字 LINEAR 和在 HASH 分区中使用具有同样的效果，分区的编号是通过2的幂算法得到的，而不是通过模数算法。

COLUMNS分区

在前面介绍的四个分区中，分区的条件是：数据必须是整型（integer），如果不是整型，则需要通过函数将其转化为整型。如YEAR()，TO_DAYS()，MONTH() 等函数。Mysql5.5版本开始支持 COLUMNS 分区，可视为 RANGE 分区和 LIST 分区的一种进化。COLUMNS 分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。此外，RANGE COLUMNS 分区可以对多个列的值进行分区。

COLUMNS 分区支持以下的数据类型：

• 所有的整型类型，如 INT、SMALLINT、TINYINT、BIGINT。FLOAT 和 DECIMAL 则不予支持。
• 日期类型，如 DATE 和 DATETIME。其余的日期类型不予支持。
• 字符串类型。如 CHAR、VARCHAR、BINARY 和 VARBINARY。BLOB 和 TEXT 类型不予支持。

对于COLUMNS 分区的定义：

• RANGE COLUMNS 分区（单列）
  
• RANGE COLUMNS 分区（多列）
  
• LIST COLUMNS 分区
  

---

子分区

子分区是在分区的基础上再进行分区，有时也称这种分区为复合分区。Mysql 允许在 RANGE 和 LIST 的分区上再进行 HASH 或 KEY 的子分区。如下图：

注意：

• 每个子分区的数量必须相同。
• 在任意一个子分区使用 SUBPARTITION 来明确定义任何子分区，就必须定义所有的子分区。
• 每个 SUBPARTITION 子句必须包括子分区的一个名字。
• 子分区的名字必须是唯一的。

---

分区中的NULL值

Mysql 允许对 NULL 值做分区，但是处理的方法与其他数据库可能完全不同。Mysql 数据库的分区总是视 NULL 值是小于任何的一个非 NULL 值，这和 Mysql 数据库中处理 NULL 值的 ORDER BY 操作是一样的。

每种分区对 NULL 值的处理是不一致的：

• 对于RANGE 分区，如果向分区列插入了NULL值，则 Mysql 数据库会将该值放入最左边的分区。
• 在 LIST 分区下要使用 NULL 值，则必须显式地指出哪个分区中放入 NULL 值，否则会报错。
• HASH 和 KEY 分区对于 NULL 的处理方式和 RANGE 分区、LIST 分区不一样。任何分区函数都会将含有 NULL 值的记录返回为0。

---

在表和分区间交换数据

Mysql5.6开始支持 ALTER TABLE … EXCHANGE PARTITION 语法。该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空，则相当于将外部表中的数据导入到分区中。

使用条件：

• 要交换的表需和分区表有着相同的表结构，但是表不能包含分区。
• 在非分区表中的数据必须在交换的分区定义内。
• 被交换的表不能含有外键，或者其他的表含有对该表的外键引用。
• 用户除了需要 ALTER、INSERT 和 CREATE 权限外，还需要 DROP 的权限。

注意：

• 使用该语句时，不会触发交换表和被交换表上的触发器。
• AUTO_INCREMENT 列将被重置。