InnoDB - Partition Table

Created by : Mr Dk.

2020 / 10 / 15 17:02

Nanjing, Jiangsu, China

About

分区功能不是在存储引擎层面完成的，MySQL 中常见的存储引擎都能支持分区 (但不是所有)。分区实际上是将一个表或索引分解为多个更小、可管理的部分。

水平分区 - 不同行的记录分配到不同的物理文件中
垂直分区 - 不同列的记录分配到不同的物理文件中

MySQL 的分区类型为水平分区。另外，MySQL 的分区是 局部分区索引，即一个分区中既存放了数据又存放了索引。用于分区的列必须是 唯一索引 (unique) 的一个组成部分。

Type

RANGE 分区

根据分区列的值范围进行分区。启用分区后，表不再由一个 ibd 文件组成，而是由各个分区的 ibd 文件组成。这样分区可能会使 SQL 优化器进行 分区修剪 (Partition Pruning) 而不去搜素所有的分区，从而得到查询速度上的提升。另外，如果想要删除某个分区范围内的数据，不需要执行 DELETE 语句，直接删除分区文件即可。

LIST 分区

与 RANGE 分区类似，但是分区列的值是离散不连续的。如果多行插入遇到分区未定义的值时，InnoDB 引擎会将之前所有正确的插入 undo，而 MyISAM 不会。

HASH 分区

目的是将数据均匀地分配到预定定义的各个分区中。用户需要指定一个计算 hash 值的 hash 函数，以及 hash 表中的桶数量。Hash 函数使得列值能够被计算为一个可比较的整数 - 比如对于一个 DATETIME 类型的列 time 来说，YEAR(time) 就是一个 hash 函数；桶数量用于将计算出 hash 值的行映射 (mod) 到不同的桶中。

KEY 分区

KEY 分区与 HASH 分区不同的点是，HASH 分区使用用户定义的函数进行分区，而 KEY 分区使用 MySQL 提供的 hash 函数进行分区 - MD5 等内部 hash 函数。

COLUMNS 分区

对于前四种分区，被分区数据必须是 integer 的，可能列本身就是 integer，也可能需要调用类似 YEAR() 的函数将列数据转换为 integer。COLUMNS 分区可以使用非 integer 类型的数据进行分区：

所有整数类型
日期类型
字符串类型

NULL in Partition

MySQL 中，NULL 值总被视为小于任何一个非 NULL 值，也就是说会出现在 RANGE 分区的最左边分区中。在 LIST 分区下使用 NULL 值，必须要显式指出在哪个分区中放入 NULL 值，否则将会报错。HASH 和 KEY 分区会将含有 NULL 值的记录直接返回 0。

对于 OLAP 的应用来说，分区可以很好地提高查询的性能，因为 OLAP 的大部分查询需要扫描一张很大的表，而分区修剪能够减少需要查询的数据。而对于 OLTP 应用来说，通常不太可能获取一张表中超过 10% 的数据，大部分是通过查询索引返回几条记录即可。单表查询 B+ 树索引一般需要两到三 (树的层数) 次 I/O 就能完成操作；而如果分了 10 个区，每个分区的查询开销为 2 次 I/O，那么将会带来 20 次 I/O 的开销。

所以在使用 InnoDB 存储引擎作为 OLTP 应用的表时，应当 确认数据的访问模式，谨慎使用分区的功能。