B+树索引实战篇-Part2(联合索引的扫描区间与边界条件)

此篇由於篇幅的关系为前文的连贯。
如没有看到前文请先去看看再来唷～

前情提要-我们前面为了方便解释，建了个表还有索引

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_field varchar(100),
    -> primary key (id),
    -> key idx_key1 (key1),
    -> unique key uk_key2 (key2),
    -> key idx_key3 (key3),
    -> key idx_key_part(key_part1, key_part2, key_part3)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引，且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是个联合索引

今儿先继续说明扫描区间与边界条件
使用联合索引执行查询时对应的扫描区间
这里需要多几个例子来说明，比较能够理解
1.

select * from single_table where key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';

联合索引idx_key_part是先照key_part1的值小到大排序，key_part1的值相等再照key_part2的值小到大排序，最後key_part1跟key_part2的值都相等，再照key_part3的值小到大排序。所以我们可以知道符合key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c'条件的纪录必定相邻。

因此搜寻的流程就是先快速定位到第一笔符合key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c'的纪录，然後沿着单向链结串列往後扫描，直到纪录不符合此条件为止。这个叙述的扫描区间就是[('a','b','c'),('a','b','c')]，形成的边界条件就是key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c'。

select * from single_table where key_part2 = 'a';

由於联合索引idx_key_part不是直接照key_part2的值来排序的，所以符合key_part2 = 'a'的纪录可能并不相邻，也就表示我们不能透过key_part2 = 'a'来减少需要扫描的纪录数量。在这样的情况下不用索引idx_key_part来查询反而是比较快的。

select * from single_table where key_part1 = 'a' and key_part3 = 'c';

由於联合索引idx_key_part是先照key_part1的值小到大排序，在key_part1的值相等再按照key_part2的值小到大排序，并不是照key_part2的值小到大来排序的，也就是说我们不能根据key_part3 = 'c'来进一步减少需扫描的纪录。

如果我们依然使用联合索引idx_key_part来执行查询，可以先定位到符合key_part1 = 'a'的第一笔纪录，然後沿着纪录的单向链结串列往後扫，直到某笔纪录不符合key_part1 = 'a'为止，所以其对应的扫描区间其实是['a','a']，形成该区间的边界条件是key_part1 = 'a'，与key_part3 = 'c'无关。
(额外补充说明：针对得到的每笔二级索引纪录，如果没有开启索引条件下推特性，则必须先执行回表操作，在得到完整的使用者纪录後再判断key_part3 = 'c'条件是否成立。如果开启了索引条件下推特性，可以立即判断二级索引纪录是否符合key_part3 = 'c'条件，符合再执行回表操作，不符合则不回表直接跳到下一笔二级索引纪录。索引条件下推特性是在MySQL5.6中引入的，预设为开启唷)

select * from single_table where key_part1 < 'b' and key_part2 = 'a';

由於联合索引idx_key_part是先照key_part1的值小到大排序，所以符合key_part1 < 'b'条件的纪录肯定相邻。但是对符合key_part1 < 'b'条件的纪录并不是直接照key_part2列来排序的(我们前面所说的必须是在key_part1的值相等时，再按照key_part2的值小到大排序，而现在key_part1 < 'b'条件表示key_part1的值并不一定会相等唷，因此可说是不直接照key_part2列来排序)。
也就是说，我们不能根据key_part2 = 'a'来进一步减少需要扫描的纪录。因此这个叙述对应的扫描区间其实是[-无限大,'b']，形成该区间的边界条件是key_part1 < 'b'，与key_part2 = 'a'无关。

select * from single_table where key_part1 <= 'b' and key_part2 = 'a';

很显然这个查询跟第4个查询很像，差别只在於key_part1 = 'b'，两者的差异也在於对符合key_part1 = 'b'的纪录来说，是按照key_part2的值来排序的，所以可以透过key_part2 = 'a'来减少须扫描的数量(只要扫描到不符合key_part1 = 'b'跟key_part2 = 'a'的第一笔纪录时，就可以结束扫描了)，如用联合索引idx_key_part来执行这个查询，形成的扫描区间是[(-无限大,+无限大),('b','a')]，形成该区间的边界条件就是key_part1 <= 'b' and key_part2 = 'a'。与第4个查询相比就是一个与key_part2 = 'a'有关，一个无关。

灿树来了休息一下吧～