数据库内核月报 - 2021 / 06

MySQL · 内核特性 · 分区表下的多种索引类型

分区表简述

对于MySQL的大表用户,分区表是非常重要的特性。分区表可以根据用户指定的分区键和分区规则,把一张大表划分为多个不相交的分区。相比于原本的一张大表,每个分区的数据就可以大大减少,对于表内数据的查询和修改等操作可以更加的高效。例如常见的把时间字段作为分区键,就可以完成新老数据的划分,对新数据的访问就会大大减少查找的数据量,让不常被访问的老数据进去到独立的分区,运维起来也会更加方便,可以高效地清理历史数据。而用户在使用分区表时,就和使用非分区表一样,访问分区表时不用在业务侧修改DML的语法。

分区表上多样的索引类型

在分区表上,可以有多种索引类型。除了传统的局部索引(Local Index)和完整索引(Full Index),本文还将介绍全局索引(Global Index)和部分索引(Partial Index)的概念。

Local Index和Global Index

分区表上传统的索引形式都是局部索引,在分区表上创建一个普通的索引时,实际上会在每个分区内都为这个索引建立一棵b+树。以下面的sql为例,创建一个拥有4个分区的分区表t1,当在字段b上创建索引时,会在每个分区的ibd文件内都生成一个索引的b+树,每个分区的b+树都只会包含对应分区内的数据。

create table t1(a int, b int) partition by hash(a) partitions 4;
alter table t1 add index(b);

对于Local Index,当DML操作的where条件包含了完整的分区键时,可以快速地找到具体要到哪个分区去做具体DML操作。比如下面的第一条sql,where条件包含了分区键字段a,就可以根据a的条件定位到具体分区,然后在该分区的索引b上进行查找。而如果是第二条sql,where条件中没有包含分区键,那就只能对所有分区的索引b进行查找。

select * where a = 1 and b > 5;
select * where b > 5;

从这个例子可以看出,对于条件中不包含分区键的DML操作,会对分区表内所有分区都进行操作,这样一次的开销会变得更大。在OLTP的应用场景下,DML往往是需要灵活的查询条件,可能就会频繁遇到这样的情况。

分区表的约束也是一个问题,在分区表中,主键必须包含全部的分区键,对于表t1,指定主键必须包含字段a。同样的,对于唯一约束(unique key),也是必须包含全部的分区键。这样一来,分区表的约束显然是不够灵活的。

而为了增强分区表的约束能力和不包含分区键的DML能力,Global Index就诞生了。如图所示,Global Index用全部分区的数据构建一个大的索引。当需要使用索引进行查询时,不需要定位到具体分区,因为一棵b+树上已经包含了全部分区的数据,直接进入Global Index的b+树进行查找就可以了,可以增强不包含分区键的DML能力。不过如果需要回表操作的话,还是需要到具体分区的主键索引上拿数据。

image-20210705175517184

而对于分区表的约束问题,Global Index可以做到支持不包含分区键的唯一约束。Local Index不支持唯一约束是因为当插入一条数据的时候,只会往对应分区内插入这条数据,而Local Index在一个分区内只会包含这个分区的数据,不足以支撑唯一性判断,如果需要达到这个目的,就得遍历local index在全部分区中的b+树,这样的开销就会非常大了。而Global Index因为只存在一棵b+树,插入时候的检查就足够了。

Full Index和Partial Index

在传统意义上,当我们在表t1上创建一个索引b的时候,不管是平时的Local Index,还是前文讲到的Global Index,这个索引b都会包含分区表字段b的全部数据(这里Local Index的索引b包含所有分区上该索引的b+树),这种对索引字段在表内的全部数据上建立的索引称为Full Index,相对的,如果只对索引字段在表内的部分数据上建立的索引称为Partial Index。

image-20210705202652419

Partial Index可以满足更灵活的索引需求,在一些情况下,Partial Index可以实现性能的提升或是成本的降低。例如用户可以只在一些经常需要用到的数据上建立索引,而不常用的数据就不在索引内,这样构建的Partial Index比起Full Index,数据量更小,节约存储成本也提升查询效率;又或者是用户可以只在较为稳定的数据上构建索引,这样在大量更新数据的时候,减少索引维护的性能开销。

更多的组合类型

Global Index、Local Index和Partial Index、Full Index在大部分情况下并不冲突,他们可以组合形成如Partial Global Index这样的索引,不过需要注意的是,Partial Index和唯一约束是冲突的,两者无法一同使用。