MySQL之
到底该查哪个分区？
PART 01
背景介绍
MySQL 在5.1以后的版本支持了分区表，从物理的角度上来看分区是将一个表分解成多个独立不相交的子表，但从逻辑的角度来看所有的分区共同组成一个独立的表。MySQL目前只支持水平分区（表的不同行分布在不同的子表中）并不支持垂直分区（表的不同列分布在不同的子表）。分区可以更方便的管理数据，比如：可以通过删除分区来快速的删除某部分数据；可以只扫描少量的几个分区来查询符合条件的结果；不同的分区可以使用不同的物理设备，更高效的利用查询物理设备；避免ext3文件系统中inode锁竞争等等。
对分区表进行搜索时，如果可以根据WHERE条件确定符合条件的数据分布在哪些分区中，那么只需要对这些分区上的索引进行搜索即可，不需要遍历所有的分区，如果符合条件的数据只分布在少数分区时可以极大的提高查询的速度。这就是本文主要介绍的内容：分区剪枝。
PART 02
什么是分区剪枝?
我们以一个简单的range类型的分区来简单说明什么是分区剪枝：
CREATE TABLE t1 (
...
)
PARTITION BY RANGE (F(a)) (
PARTITION P1 VALUES LESS THAN (L1),
PARTITION P2 VALUES LESS THAN (L2),
...
PARTITION Pn VALUES LESS THAN (Ln)
)根据以上的分区定义，我们可以得到分区的范围序列：(,L1), [L1, L2), [Ln-1, Ln)，其中F(*)是分区表达式，a是参与分区的列。对于如下的查询：
select * from t1 where
(t1.a > 10 AND t1.a <= 20) OR (t1.a > 0 AND t1.a < 5)在WHERE条件，其分区列a的取值范围的序列为(0, 5) (10, 20]，我们用S(RC)来表示这个序列。
分区剪枝就是找到分区范围与F(S(RC))有交集的分区的集合。根据范围进行剪枝时，分区表达式F(*)需要是一个单调函数，否则在优化器层无法根据WHERE条件进行剪枝。这里简单的总结一下MySQL分区剪枝的适用范围：
- WHERE条件的中的比较谓词为=, <>, <, <=, >, >=, BETWEEN, IN, IS NULL, IS NOT NULL，由AND-OR连接。
- 比较谓词中由分区列名与常量进行比较，否则优化器无法确定S(RC)的值。
- 分区表达式F(*)是一个单调函数或者S(RC)是一个单点区间（等值查询或者IN(a, b, c)这类的用法）才可以进行分区剪枝，否则在优化器层无法根据WHERE条件进行剪枝。对于hash类型的分区而言，同样S(RC)是一个单点区间才能进行剪枝。
PART 03
分区剪枝是怎么实现的?
1. 修剪分区后的信息存储在哪？
支持分区剪枝最原始的版本在JOIN::optimize中调用分区剪枝的代码，其剪枝操作发生在GROUP BY优化（optimize_aggregated_query）之前，因为在这里面可能会访问查询的表，这样就可以在访问表之前就可以确定哪些分区不需要访问。在WL#4443中，对这个流程又进行了优化，剪枝操作提前到了Query_block::prepare中，这是因为希望在lock_tables()之前就行剪枝，这样只对剪枝后的分区加锁即可。在包含子查询的query中，也可能会再次调用剪枝操作来对子查询进行剪枝。
MySQL使用partition_info来维护分区表相关的信息，分区剪枝通过一个bitmap来保存需要访问哪些分区来获取搜索结果。分区对应数据位为1则表示需要对该分区进行搜索，否则该分区内的记录不满足查询条件，可以直接跳过。在MySQL中这个bitmap通过两个bitmap对象来存储：
- read_partitions: 记录需要访问哪些分区
- lock_partitions: 记录需要锁住哪些分区
在ha_partition::open()函数中对它们进行初始化，如果用户指定查询某些分区，在open_and_lock_tables()函数中会显式地设置它们的值。
比如select * from t1 partition(p0, p1)会对p0, p1分区所在的数据位进行设置。在函数prune_partitions()也会对它们进行修改，WL#4443中允许了prune_partitions()对lock_partitions进行修改。
通常read_partitions与lock_partitions是一样的，但是在以下情景下会有所不同：
- UPDATE更新分区表达式中的字段，lock_partitions不会在prune_partitions()后进行修改，其包含所有的分区。因为这可能会导致记录更新到其他分区，会把所有的分区都锁住。read_partitions为分区剪枝后的分区。
- REPLACE/INSERT ... VALUES通常也只会锁住修剪后的分区，但是如果包含自增列且自增列位分区字段，那么lock_partitions同样也会锁住所有的分区。
- INSERT ... VALUES ... ON DUPLICATE KEY这个类似于UPDATE，如果更新分区字段，那么也是lock_partitions也是所有分区。
- INSERT SELECT中，对于select的表read_partitions与lock_partitions是一样的，但是对于insert的表，lock_partitions会锁住所有的分区。
- LOAD指令不会进行分区剪枝，因为LOAD通常是大批量的插入。对一大批量进行分区剪枝带来的收益可能并不明显。因为对于一批插入，每行都需要分区剪枝代价再汇总，这个代价也是很大的。
2. 剪枝流程
分区剪枝主要是利用SEL_TREE描述的区间范围进行剪枝，剪枝函数find_used_partitions处理的粒度为SEL_ARG，一个SEL_TREE包含一个或多个SEL_ARG。为方便理解，本章先讲解SEL_ARG的组织形式以及如何在上面进行剪枝，关于SEL_TREE的内容放在后面进行讨论。
下面是SEL_ARG的一个简单示例：
图中par1、par2、subpar1、subpar2分别表示一级分区和二级分区的分区键。垂直方向的连接表示由OR条件进行连接，代码中由其用SEL_ARG::left和SEL_ARG::right进行表示。水平方向的连接表示由AND条件进行连接，代码中由SEL_ARG::next_key_part进行表示。水平方向连接的是不同的分区字段，垂直方向连接的是相同字段的条件。所以图中表示的查询条件为：
图中通过虚线按照分区键将SEL_ARG切割成了4部分，虚线切割的每个部分中相连的区间组成了一个红黑树。图中可以看作是切割成了7个红黑树：
1) "par1=c1 or par1=c2"
2) "par2=c2"
3) "subpar1=c3 or subpar1=c4"
4) "subpar1=c10"
5) "subpar2=c5 or subpar2=c6"
6) "subpar2=c8"
7) "subpar2=c12"剪枝时在每个水平方向的路径得到一次剪枝的结果。比如会对图中的以下四个条件分别进行一次剪枝，最终剪枝的结果取并集:
1) "par1=c1 AND par2=c2 AND subpar1=c3 AND subpart2=c5",
2) "par1=c1 AND par2=c2 AND subpar1=c3 AND subpar2=c6",
3) "par1=c1 AND par2=c2 AND subpar1=c4 AND subpar2=c8"
4) "par1=c2 AND subpar1=c10 AND subpar2=c12"剪枝时递归调用find_used_partitions对每个范围进行处理，在遇到最后一个一级分区和二级分区的分区键时，分别对一级分区和二级分区进行剪枝。对于1）剪枝的时的堆栈为如下的形式：
在(**)处得到一级分区剪枝的条件，在(***)处得到二级分区剪枝的条件。find_used_partitions进行搜索时，递归的顺序大致如下：
那么在find_used_partitions中是如何确定哪些分区符合条件呢？在遍历到最后一个一级分区的的分区键或二级分区的分区键进行剪枝时，剪枝操作根据分区类型和剪枝条件是否为一个等值查询会有所区别：
1) 如果是一个等值查询，则直接根据具体的值定位到具体的分区即可。
2) 如果是一个范围查询，则需要确定哪些分区符合查询的条件，这需要针对不同的分区类型来判断：对于range类型的分区，首先会找到查询条件上下边界所在的分区，因为range类型中分区的范围是递增的，所以在包含在上下边界之内的分区都是需要被搜索的。对于list类型的分区，内部会将所有分区中的值进行排序并存到一个数组中，然后对于查询条件的上下边界定位到数组中的上下边界，根据数组中的值是可以定位到哪些分区需要搜索的。
3. SEL_TREE的组织方式以及剪枝
SEL_TREE可以简单的理解为由一个或多个SEL_ARG组成，在剪枝时，会先生成SEL_TREE，然后遍历SEL_TREE上的SEL_ARG进行剪枝，在本章后面介绍SEL_TREE的三种组织方式。在构造SEL_TREE之前，需要构造一个虚拟的分区索引（包含一级分区、二级分区的所有字段），构造虚拟分区索引时会将分区的字段信息添加到虚拟索引信息中。如果分区字段为GEOMETRY或者ENUM类型的话，将不会对其进行剪枝，因为对这两种类型字段无法进行区间范围的比较。比如一级分区的字段包含GEOMETRY类型，二级分区的字段不包含GEOMETRY和ENUM类型，则一级分区不会进行剪枝，只会对二级分区进行剪枝。
构造完虚拟索引后，调用get_mm_tree构造SEL_TREE。在get_mm_tree中会调用field->optimize_range()来判断索引能否处理当前的区间范围。对于分区剪枝而言，就是判断能否对当前的范围进行剪枝。MySQ在构造SEL_TREE时，如果是分区剪枝，那么field->optimize_range()都是为true，即在这个流程中先默认可以对范围进行剪枝。对于能否剪枝的真实判断放到find_used_partitions中。这样做的原因是在这里并不能100%确定一个范围是否与另一个范围组成一个单点区间。比如某个分区表达式下只能对单点区间进行剪枝，对于t.key >= 10 AND t.key <= 10这个表达式是一个单点区间，但是判断t.key >= 10时可能会错误的认为无法进行剪枝。
在无法对区间范围只创建一个SEL_ARG的时候，get_mm_tree对单个索引也会创建index_merge tree。比如对于keypart1=c1 OR keypart2=c2，会创建一个包含两个子树（SEL_ARG）的index_merge tree。这个特性对于分区剪枝而言，意味着可以对更广的范围区间进行剪枝。根据是否进行index_merge以及index_merge tree的数量，SEL_TREE可以分为三种情况：
- SEL_TREE只有一个树，通过SEL_ARG* graph来描述一个区间范围的序列S(RC)，比如part_field1 = c1 OR part_field1 = c2。
- SEL_TREE是一个index merge tree，由多个SEL_ARG用OR条件连接。可以描述成tree1 OR tree2 OR ... treeN的方式。每个tree的组织方式类似于第一种情况，比如part_field1 = c1 OR subpart_field1 = c2。
- SEL_TREE是多个index merge tree，可以描述成merge_tree1 AND merge_tree2 ... AND merge_treeN，每个merge tree类似于上面的第二中情况，比如(part_field1 < c1 OR subpart_field1 = c2) AND (part_field1 > c3 OR subpart_field1 = c4)。
分区剪枝的主要函数为find_used_partitions，其对SEL_ARG描述的区间范围序列进行分析并剪枝。上面描述的的第一种情况，直接调用find_used_partitions并传入传入SEL_ARG，对于第二种情况，需要调用find_used_partitions_imerge对每个子树调用find_used_partitions并对剪枝的结果取并集，对于第三种情况，会调用find_used_partitions_imerge_list对每个index merge tree调用find_used_partitions_imerge并对剪枝的结果取交集。其代码逻辑如下：
4. 一个例子
我们以range类型的分区为例来解释分区剪枝的流程，创建如下表：
CREATE TABLE test (a INT, b INT, C INT) PARTITION BY RANGE COLUMNS(a, b)
SUBPARTITION BY HASH( c )
SUBPARTITIONS 5 (
PARTITION p0 VALUES LESS THAN (0,0),
PARTITION p1 VALUES LESS THAN (10,10),
PARTITION p2 VALUES LESS THAN (20,20),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);并执行如下查询：
EXPLAIN SELECT * FROM test WHERE (a > 0 and a < 10 AND b = 0 and (c = 2 OR c = 1)) OR (b = 1 and (a > 20 or b = 12) and c = 2);我们可以得到如下的SEL_ARG：
那么剪枝的步骤如下：
- 从0 < a < 10开始进入剪枝函数find_used_partitions，因为SEL_ARG::left为空，所以先在水平方向进行遍历。因为字段a是一级分区的最后一个字段，所以a < 10可以对一级分区进行剪枝。剪枝时定位到10为p1的上界，因为a的范围不包含10，所以a范围的上界所在的分区为p1，a的下界为0，为分区p0的上界，但a的取值大于0，所以a的范围的下届所在的分区为p1。所以在这一步剪枝得到的结果为p1。
- 然后在水平方向继续往后，得到等值条件b = 0，这不是二级分区的最后一个字段，会继续向下寻找，找到条件c = 1。此时已经到二级分区的最后一个字段了，b+c的值1，计算所在的二级分区为sp1。所以此条路径剪枝的结果为p1sp1。
- 此时c = 2这个条件的find_used_partitions出栈，然后根据SEL_ARG::right指针进入条件c = 2，同理得到剪枝的结果为p1sp2。
- 跟上面的流程一样，在a > 20时，一级分区的剪枝结果为p3。在b = 1 and c = 2时，二级分区的剪枝结果为sp3，所以此条路径的剪枝结果为p3sp3。
- 汇总上面的剪枝结果，最终保留的分区为：p1sp1，p1sp2，p3sp3。
PART 04
总结
本文主要总结了MySQL分区表中根据查询条件进行分区剪枝的实现。通过原理我们可以了解到，对于分区表，查询条件应尽量包含分区字段，否则查询将会遍历所有的分区进行搜索。但是分区剪枝仍有诸多限制，很多情况下是不会进行分区剪枝的，我们应尽量避免以下情况的使用：
- 分区函数不是单调递增函数时，在进行范围查询时，是无法进行剪枝的。
- 比较谓词中分区字段与非常量进行比较时，是无法进行剪枝的。
- WHERE条件中只支持=, <>, <, <=, >, >=, BETWEEN, IN, IS NULL, IS NOT NULL等比较谓词，由AND-OR连接。
- 分区字段为GEOMETRY或者ENUM类型时是不支持分区剪枝的，因为这两种类型在mysql内部并不好处理其范围比较。
- 如果分区字段上有BEFORE INSERT/UPDATE类型的触发器，则也不会进行分区剪枝，因为触发器中可能会修改分区字段的值。
作者:腾讯数据库技术
来源:微信公众号:腾讯数据库技术
出处:https://mp.weixin.qq.com/s/jC0rCI2BnyYBORZwRIqbdg