MySQL高级特性之分区表

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

意义

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

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

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

• 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据其他均是历史数据
• 分区表的数据更容易维护
• 分区表的数据可以分布在不同的物理设备上
• 可以使用分区表来避免某些特殊的瓶颈
• 如果需要，可以备份和回复独立的分区

分区表本身也有一些限制，下面几点尤为重要：

• 一张表最多只能有1024个分区
• 在MySQL5.1 中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5 中，某些场景可以直接使用列来进行分区
• 分区表中无法使用外键约束
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

分区表的原理

存储引擎管理分区的各个底层表和管理普通表并没有什么区别（所有的底层表都必须使用相同的存储引擎）
，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度看，底层表和一个普通表并没有什么区别，存储引擎也无需知道这是一个普通表还是一个分区表的一部分。

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

SELECT 查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

INSERT 操作

当写入一条记录的的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表

DELETE 操作

当删除一条记录的的时候，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

UPDATE 操作

当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

这些操作都是支持过滤的。

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

分区表的类型

MySQL支持多种分区表，我们看到最多的就是根据范围进行分区，每个分区存储落在某个范围内的记录。分区表达式可以是列，也可以是包含列的表达式。

例如，如下表就将每一年的销售额都存放在不同的分区中：

CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE;
)

PARTITION 分区子句中可以使用各种函数。但是有一个要求， 表达式返回的值必须是一个确定的整数，且不能是一个常数。

MySQL还支持键值、哈希和列表分区等。

如何使用分区表

如果我们希望从一个非常大的表中查询出一段时间的记录，我们应该如何查询这个表，如何才能更加高效？

因为数据量非常大，肯定不能在每次查询的时候都扫描全表，考虑到索引在空间和维护上的消耗，我们也不希望使用索引。即使真的使用索引，也会发现数据并不是按照想要的方式进行聚集，会产生大量的碎片，最终导致一个查询产生成千上万的随机I/O。而事实上，当数据量超级大时，B-Tree索引就已经无法祈祷作用了。

因此我们可以选择一些更粗粒度但消耗更少的方式检索数据，例如在大量的数据上只索引对应的一小块元数据。

这正是分区要做的事情，理解分区可以将其当作索引的最初形态。 因为分区无需额外的数据结构记录每个分区有哪些数据——分区不需要精确定位每条数据的位置，也就无须额外的数据结构——所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

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

1. 全量扫描数据，不需要任何索引： 只要能够使用 WHERE 条件，将需要的数据限制在少数分区中，则效率是很高的。使用这种策略假设不用将数据完全放入内存中，同时还假设需要的数据全部都在磁盘上。因为内存相对较小，数据很快会被挤出内存，所以缓存起不了任何作用。这个策略适用于以正常的方式访问大量数据的时候。
2. 索引数据，并分离热点： 如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据可以有机会都缓存在内存中。这样的查询可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存。

什么情况下会出问题

上面介绍的两个分区策略都基于两个非常重要的假设：查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代价。

事实证明，这两个假设在某些场景下会有问题：

• 分区列和索引列不匹配： 如果定义的索引列和分区列不匹配，会导致可查询无法进行分区过滤。
• 选择分区的成本可能很高： 不同类型的分区的实现方式也不同，所以它们的性能也各不相同。尤其是范围分区，对于查询符合条件的行在哪些分区的成本可能会非常高，因为服务器需要扫描所有的分区定义的列表来找到正确的答案。
• 打开并锁住所有底层表的成本可能很高： 当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。
• 维护分区的成本可能很高： 某些分区维护操作的速度会非常快，例如新增或者删除分区。而有些操作，比如重组分区或者类似ALTER语句的操作成本可能会很高，因为这类操作需要复制数据。