【MySQL】表分区

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

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

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

 

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

 

1. 创建分区表

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

Sql代码

 

1. create table t1 (  

2.   id int,  

3.   amount decimal(7,2),  

4.   tr_date date)  

5. Partition by hash(month(tr_date))  

6. Partitions 6;  

 

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

Sql代码

 

1. 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

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

Sql代码

1. create table test (  

2.   a int,  

3.   b int  

4. )  

5. Partition by Range columns(a,b) (  

6.   Partition p0 values less than (5,12),  

7.   Partition p3 values less than (maxvalue, maxvalue)  

8. );  

 

2.2 List

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

Sql代码

1. create table employee (  

2.   id int not null,  

3.   name varchar(30),  

4.   store_id int  

5. )  

6. Partition by List(store_id) (  

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

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

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

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

11. );  

 

2.3 Hash

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

Sql代码

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

2.   Partition By Hash( Year(c3) )  

3.   Partitions 4;  

 

Linear Hash

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

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

与常规Hash相比：

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

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

Sql代码

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

2.   Partition By Linear Hash( Year(c3)) )  

3.   Partitions 6;  

 

2.4 Key

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

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

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

Sql代码

1. create table test (  

2.   id int not null primary key,  

3.   name varchar(20)  

4. )  

5. Partition By Key()  

6. Partitions 2;  

 

2.5 Subpartition（复合分区）

就是对分区内还有分区。

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

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

Sql代码

1. create table test (id int, c1 date)  

2.   Partition By Range( Year(c1) )  

3.   Subpartition By Hash( To_Days(c1) )  

4.   Subpartitions 2 (  

5.     Partition p0 values less than (1990),  

6.     Partition p1 values less than (2000),  

7.     Partition p2 values less than maxvalue  

8.   );  

上述示例中：

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

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

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

 

3. 分区表维护

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

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

3.1 Rebuild

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

Sql代码

1. alter table t1 Rebuild Partition p0, p1;  

 

3.2 Optimize

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

Sql代码

1. alter table t1 Optimize Partition p0, p1;  

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

 

3.3 Analyze

优化分区中的索引分布。

Sql代码

1. alter table t1 Analyze Partition p3;  

 

3.4 Repair

修复腐化（corrupted）的分区。

Sql代码

1. alter table t1 Repair Partition p0,p1;  

 

3.5 Check

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

Sql代码

1. alter table t1 Check Partition p1;  

 

4. 分区表优点

• 方便对数据分而治之

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

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

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

• 提高数据查询效率

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

 

5. 分区表缺点/限制

• InnoDB 分区表不支持外键。

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

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

• 不支持全文索引（Fulltext Index）。

• 不支持空间类型数据（如，Point、Geometry）。

• 不支持对临时表分区。

• 分区索引不支持子查询。

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

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

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

  Key / Linear Key

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

  Sql代码

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

  2. Partition By Key(c1)  

  3. Partitions 3;  

  Range、List

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

  Sql代码

  1. create table t1 (c1 int, c2 Date)  

  2. Partition By Range Columns(c2) (  

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

  4.   Partition p1 Values Less Than (maxvalue)  

  5. );  

• 复合分区的类型有限制。

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

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

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

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

  Sql代码

  1. create table t1 (  

  2.   c1 int, c2 int, c3 int, c4 int,  

  3.   Unique Key uk_123 (c1, c2, c3),  

  4.   Unique Key uk_124 (c1, c2, c4))  

  5. Partition By Hash(c1+c3) Partitions 4;  

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

  Sql代码

  1. create table t1 (  

  2.   c1 int, c2 int, c3 int, c4 int,  

  3.   Unique Key uk_123 (c1, c2, c3),  

  4.   Unique Key uk_124 (c1, c2, c4))  

  5. Partition By Hash(c1+c2) Partitions 4;  

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

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

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