MySQL高级特性 数据表分区 MySQL高级特性——数据表分区的概念及机制详解

MySQL 的分区的实现方式是对数据表进行一层包装，这意味着索引实际是基于每个分区定义的，而不是整张表。这个特性和 Oracle 是不同的，在 Oracle 中的索引和数据表可以使用更灵活和更复杂的方式进行分区。​

MySQL 的分区通过定义 PATITION BY 子句的条件来决定数据行所属分区的归属。在执行查询的时候，查询优化器会区分所在分区，这意味着查询不会检查全部分区，而仅仅是那些包含索要查询数据所在的分区。​

分区的主要目的是对数据表进行大致形式的索引和聚集。这样可以减少数据表的过大范围的访问，并可以将相关的数据行临近存储。分区的收益是显著的，尤其是对于下面的场景：

• 当数据表过大导致内存空间难以承载时，或者一张数据表中有很多历史数据以及热区行。
• 分区数据相比为分区数据更容易维护。例如，通过删除整个分区很容易将旧的数据清除，同时对于单个分区也可以很方便地进行优化、检查和修复操作。
• 分区数据在物理上可以分布存储，这使得服务器可以更高效地使用多个硬盘驱动器。
• 可以使用分区避免某些工作负荷的瓶颈。
• 对于数据备份而言，可以单独备份或恢复单个分区，这对于大的数据集来说十分有益。
  

MySQL 的分区实现细节十分复杂，弄清楚是很难的，我们只需要关注它的性能即可。如果想进一步了解，可以翻阅 MySQL 手册中关于分区的部分。有了分区后，也带来了其他问题以及限制：

• 创建表和更改表的命令更为复杂。
• 每张表最多只能有1024个分区。
• 在 MySQL 5.1版本中，分区表达式必须是整数或者返回一个整数；在 MySQL 5.5以后，在某些情况下可以使用列进行分区。
• 任主键或唯一索引都必须包含分区表达式中的全部列。
• 不可以使用外键约束。
  

分区机制

如前所述，分区表实际有多个隐藏的物理存储表，这通过句柄对象呈现。我们不能直接访问分区。通常，每个分区是通过存储引擎管理的（因此要求所有分区的存储引擎相同），而数据表中的索引实际上是隐藏的物理存储表的索引。从存储引擎的角度来看，分区也是数据表。存储引擎实际并不知道数据表是独立的还是一个大的数据表的一个分区。对于分区表的操作通过如下的逻辑操作实现：

SELECT 查询

当对分区表进行查询时，分区层会打开和锁定全部的隐藏分区，查询优化器会决定那些隐藏分区可以忽略，然后分区层通过句柄 API 调用管理分区的存储引擎获取查询结果。

INSERT 操作

当插入一行数据时，分区层会打开和锁定全部分区，然后决定那个分区存储当前的数据行，并将该数据行存入对应分区。

DELETE 操作

删除一行数据时，分区层会打开和锁定全部分区，检查哪个分区包含该行数据，再将删除请求发送到该分区。

UPDATE 操作

修改一行数据时，分区层打开和锁定所有分区，检查哪个分区包含该行数据，并获取该行数据进行修改，然后再确定哪个分区应当包含新的数据行，并把插入请求发送到该分区，同时发送删除请求到旧的分区。
上面的有些操作支持分区过滤（即忽略无关的分区）。例如，删除一行时，服务器需要首先定位数据行位置。如果在 WHERE 条件中指定了匹配的分区表达式条件，服务器可以忽略掉不包含该行的分区。对于 UPDATE 操作也是类似，而 INSERT 操作本身就是如此，服务器会只查找需要插入的一个分区，而不是全部。

虽然分区层打开和锁定了全部分区，但并不意味着分区会保持锁定。像 InnoDB 的存储引擎，可以支持行级别的锁定，会只是分区层解除分区的锁定。这个加锁和解锁的过程和普通的 InnoDB 数据表的锁定过程类似。

分区的类型

MySQL 支持几种类型的分区，最常用的类型是范围分区——也就是针对某些列的的值或表达式按不同的范围进行分区。例如，下面的语句就是根据年份将销售数据分到不同的分区中：

CREATE TABLE sales (
  order_date DATETIME NOT NULL
  --其他列定义
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
  PARTITION p_2018 VALUES LESS THAN (2018),
  PARTITION p_2019 VALUES LESS THAN (2019),
  PARTITION p_2020 VALUES LESS THAN (2020),
  PARTITION p_other VALUES LESS THAN MAXVALUE);

可以在分区子句中使用多种函数。最主要的要求是必须返回一个非常量的，确定的整数。在上面的例子中使用的是 YEAR 函数，也可以使用其他函数，例如 TO_DAYS()。使用时间间隔进行分区是基于日期数据的常用方式。​

MySQL 也支持键，哈希以及列表的分区方法，有些还支持子分区（实际很少用）。在 MySQL 5.5以后，可u一使用 RANGE COLUMNS 的分区类型直接按基于日期的列进行分区，而不需要使用函数将日期转换为整数。 其他常见的分区技巧包括：

• 使用键进行分区以减少 InnoDB 的互斥量的竞争；
• 可以使用取余计算的方法来循环构建范围的分区，例如如果只需要保持最近几天的数据，可以通过对日期对7取余，或者使用所在的周天数进行分区。
• 假设数据表没有自增的主键，但是也想对聚集在一起的热区数据分区。由于时间戳不在主键里，也无法使用时间戳分区。这时候可以使用 HASH(id DIV 1000000)，这会在每1000000行数据进行分区。这使得无需更改主键也能完成我们要的效果。同时这样还有附加的效果。那就是我们无需创建分区的常量去保留新的数据。