介绍

对于用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象（Handler Object）的封装，对于分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。

MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的子表定义的，而没有全局索引。

MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候，优化器根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区——只需要查找包含需要数据的分区就可以了。

分区的一个主要目的就是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。

在下面的场景中，分区可以起大很大的作用：

• 表非常大，以至于无法全部都放在内存中，或者只在表的最后部分有热点数据 ，其他均是历史数据。
• 分区表的数据更容易维护。例如，可以对一个独立分区进行优化、检查、修复、批量删除等操作。
• 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
• 可以使用分区表来避免某些特殊的瓶颈。

分区表本身也有一些限制，其中比较重要的几点：

• 一个表最多只能有1024个分区。
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
• 分区表中无法使用外键约束。

分区表的原理

分区表上的操作按照下面的操作逻辑进行：

• SELECT查询：当查询一个分区表时，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
• INSERT操作：当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。
• DELETE操作：当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。
• UPDATE操作：当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。

分区表的类型

MySQL支持多种分区表。最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分表表达式可以是列，也可以是包含列的表达式。还支持键值、哈希和列表分区，不过很少用到。

如何使用分区表

为了保证大数据量的可扩展性，一般有以下两个策略：

• 全量扫描数据，不要任何索引。

  可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用WHERE条件，将需要的数据限制在少数分区中，则效率是很高的。使用该策略假设不用将数据完全放入内存中，同时还假设需要的数据全部在磁盘上，因为内存相对较小，数据很快会被挤出内存，所以缓存起不来任何作用。适用于以正常的方式访问大量数据的时候。警告：必须将查询需要扫描的分区个数限制在一个很小的数量。

• 索引数据，并分离热点。

  如果数据又明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区能够有机会都缓存中内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存。

什么情况下会出问题

上面介绍的分区策略都基于两个非常重要的假设：查询都能够过滤（prunning）掉很大额外的分区、分区本身并不会带来很多额外的代价。而事实证明，这两个假设在某些场景下会有问题，下面介绍一些可能会遇到的问题。

• NULL值会使分区过滤无效

  关于分区表一个容易让人误解的地方就是分区的表达式的值可以是NULL：第一个分区是一个特殊分区。如果第一个分区非常大，特别是当使用“全量扫描数据，不要任何索引”的策略时，代价会非常大，而且扫描两个分区来查找列也不是我们使用分区表的初衷。为了避免这种情况，可以创建一个“无用”的第一分区。

• 分区列和索引列不匹配

  如果定义的分区列和索引列不匹配，会导致查询无法进行分区过滤。

• 选择分区的成本可能很高

  如范围分区。

• 打开或锁住所有底层表的成本可能很高

  当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。

• 维护分区的成本可能很高

  某些分区维护操作速度可能很慢，例如重组分区或者类似ALTER语句的操作：这类操作需要复制数据。重组分区的原理与ALTER类似，先创建一个临时的分区，然后将数据复制到其中，最后再删除原分区。

其他一些限制：

• 所有分区都必须使用相同的存储引擎。
• 分区函数中可以使用的函数和表达式也有一些限制。
• 某些存储引擎不支持分区。

MySQL之分区表