mysql 分区表 外键_【MySQL】表分区

MySQL表分区是MySQL提供的一种简单的数据表水平拆分方法。

分区表是一个逻辑表，它由多个物理子表组成。

目前只有 InnoDB 和 NDB 正式支持表分区。

注：表分区虽然可以缓解单表数据量过大的问题，但对于减轻数据库服务器压力的作用不大，所有请求还是在争用同一服务器的资源。

1. 创建分区表

可在创建表时指定分区方式。例：

create table t1 (

id int,

amount decimal(7,2),

tr_date date)

Partition by hash(month(tr_date))

Partitions 6;

也可以 Alter Table 设置分区。例：

alter table t1 Partition by key(id) Paritions 2;

alter table t1 Add Partition (Partition p2 values in (7,14,21));

为字段值划分区间；字段在在某个区间内的记录就被存放在对应的分区中。

create table test (

a int,

b int

)

Partition by Range columns(a,b) (

Partition p0 values less than (5,12),

Partition p3 values less than (maxvalue, maxvalue)

);

类似 Range，List是把分区字段可能的值都列举出来。

create table employee (

id int not null,

name varchar(30),

store_id int

)

Partition by List(store_id) (

Partition p_North values In (3,5,6,9,17),

Partition p_East values In (1,2,10,11,19,20),

Partition p_West values In (4,12,13,14,18),

Partition p_South values In (7,8,15,16)

);

用MySQL的Hash方法对字段进行计算，以确定分区。

create table test (c1 int, c2 char(5), c3 date)

Partition By Hash( Year(c3) )

Partitions 4;

特殊的Hash。与 Hash 类似，常规 Hash 是对 Hash方法计算所得值进行 取模 运算；Liner Hash 则采用线性2的幂运算。

MySQL官网使用手册有详细的运算步骤讲解。

与常规Hash相比：

优点：对分区的增、删、合并、拆分效率更高。

缺点：数据的分布没有常规Hash均匀。

create table test (c1 int, c2 char(5), c3 date)

Partition By Linear Hash( Year(c3)) )

Partitions 6;

2.4 Key

与 Hash 类似，Key使用的 哈希表达式/算法 是MySQL自己决定的；而且只能对列直接计算，不支持复杂的自定义表达式。

NDB Cluster 用的是 MD5(), 其它引擎用的算法与 PASSWORD()方法相同。

Linear Key 是特殊的 Key。Linear Key 与 Key 的关系 如同 Linear Hash 与 Hash 的关系。

create table test (

id int not null primary key,

name varchar(20)

)

Partition By Key()

Partitions 2;

就是对分区内还有分区。

父分区类型必须是 Range 或 List；

子分区类型必须是 Hash 或 Key。

create table test (id int, c1 date)

Partition By Range( Year(c1) )

Subpartition By Hash( To_Days(c1) )

Subpartitions 2 (

Partition p0 values less than (1990),

Partition p1 values less than (2000),

Partition p2 values less than maxvalue

);

上述示例中：

有三个一级分区，每个一级分区都有2个二级分区，一共是6个分区；

一级分区根据字段 c1 的年份值所属范围划分；

二级分区根据字段 c1 的天数 以Hash方式划分。

类似普通表，分区表也支持 Check Table、Optimize Table、Analyze Table、Repair Table。

此外，分区表还有特有的 Alter Table 扩展指令：

3.1 Rebuild

Bebuild 的效果就是，先移除分区中的所有数据记录，再重新插入。主要用于碎片整理。

alter table t1 Rebuild Partition p0, p1;

3.2 Optimize

当分区中有大量记录被删除，或对包含变长字段的表数据改动较大时，可用此指令回收未使用的空间，对分区数据文件进行碎片整理。

alter table t1 Optimize Partition p0, p1;

InnoDB 暂不支持此操作，可用 Rebuild 和 Analyze partition 代替。

3.3 Analyze

优化分区中的索引分布。

alter table t1 Analyze Partition p3;

3.4 Repair

修复腐化(corrupted)的分区。

alter table t1 Repair Partition p0,p1;

3.5 Check

检查分区是否有腐化(corrupted)。如果有腐化，可以用 Repair 指令修复。

alter table t1 Check Partition p1;

4. 分区表优点

方便对数据分而治之

可以通过删除分区来删除无用的数据。

为新增数据新开分区可以加快执行效率。

可以对分区单独优化、检查、修复、备份、恢复。

提高数据查询效率

可通过查询条件排除不符合条件的分区，提高效率。

5. 分区表缺点/限制

InnoDB 分区表不支持外键。

分区表不能引用其它表中的列作为外键；

其它表不能引用分区表中的列作为外键。

不支持全文索引(Fulltext Index)。

不支持空间类型数据(如，Point、Geometry)。

不支持对临时表分区。

分区索引不支持子查询。

分区索引对字段类型有限制。

只有 整数列 或 计算结果为整数的列表达式 可作为分区索引。(分区表达式可选函数)

不同分区类型对字段类型的要求也不同：

Key / Linear Key

除了 Text 和 Blob，其它字段类型都支持。因为 Key 的内部哈希函数可以生成整数。例：

create table t1 (c1 Enum('a','b','c'))

Partition By Key(c1)

Partitions 3;

Range、List

可用于字符串(string)、Time、Date、DateTime。不能用于 Text、Blob、Timestamp、Year。例：

create table t1 (c1 int, c2 Date)

Partition By Range Columns(c2) (

Partition p0 Values Less Than ('2001-01-01'),

Partition p1 Values Less Than (maxvalue)

);

复合分区的类型有限制。

父分区类型必须是 Range 或 List；

子分区类型必须是 Hash 或 Key。

分区表达式中涉及的列必须都是 每一唯一索引 包含的列。

不合法的分区 —— 唯一索引 uk_124 未引用 c3：

create table t1 (

c1 int, c2 int, c3 int, c4 int,

Unique Key uk_123 (c1, c2, c3),

Unique Key uk_124 (c1, c2, c4))

Partition By Hash(c1+c3) Partitions 4;

合法的分区 —— 两个唯一索引 都引用了 c1 和 c2：

create table t1 (

c1 int, c2 int, c3 int, c4 int,

Unique Key uk_123 (c1, c2, c3),

Unique Key uk_124 (c1, c2, c4))

Partition By Hash(c1+c2) Partitions 4;

只有部分存储引擎支持分区表。

表分区特性是存储引擎提供的，而非MySQL Server。

目前只有 InnoDB 和 NDB 支持表分区。其中 NDB 支持 Key(包括 Linear Key)类型的分区。