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

 

2. 分区类型

2.1 Range

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

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

 

2.2 List

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

 

2.3 Hash

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

create table test (c1 int, c2 char(5), c3 date)
  Partition By Hash( Year(c3) )
  Partitions 4;

 

Linear Hash

特殊的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;

 

2.5 Subpartition（复合分区）

就是对分区内还有分区。

• 父分区类型必须是 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方式划分。

 

3. 分区表维护

类似普通表，分区表也支持 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）类型的分区。