在学到OceanBase索引的时候，感觉很迷糊，因此进行了整理，加入了自己的理解，如果写的有不对的地方和有不同的理解，欢迎指出~

使用OceanBase不可避免的会用到分区表，分布式场景索引和集中式场景有所不同

本文主要对分区表的索引类型进行整理，非分区表很好理解，就不用赘述了~

官方链接：www.oceanbase.com/docs/commun…

索引分类

局部索引和全局索引

对于分区表，按照数据的分布范围，将OceanBase的索引分为：

• 局部索引：为每个分区的数据单独建立索引，索引的分布和数据分布保持一致（分区键等同于表的分区键，分区数等同于表的分区数），局部索引与子表一一对应 （一对一关系）

• 全局索引：为所有分区的数据建立索引，索引的分布规则和表分区相互独立，可以分区也可以不分区

  • 全局分区索引：索引按照规则进行分区，分区规则可以和表分区规则相同也可以不同

    • 如果分区规则和表分区规则不同时，可能会出现索引和数据不在同一个节点，出现分布式查询 （多对多关系）

  • 全局非分区索引：索引不分区，但由于表数据是分区的，索引和数据分区的关系是1对多

    • 全局非分区索引可以理解为内部的非分区表，其根据locality和zone属性分布，可能会出现索引和数据不在同一个节点，出现分布式查询 （一对多关系）

一开始纠结，全局非分区索引到底放在哪个节点上，是每个observer都放还是只放在1个上面，如果所有server都放的话会不会太浪费资源了。还去翻了一下源码（能力有限），找到下面的代码 后来豁然开朗，把OceanBase的全局索引理解成一张内部的数据表即可，然后根据租户locality和primary_zone属性分布，就不再纠结了~

ObDDLService::generate_global_index_locality_and_primary_zone
// primary zone and locality of global index is the same as its data table.

全局索引使用的必要性

全局索引可能带来以下问题：

• 出现分布式SQL：全局索引需要对所有分区的数据建立索引，无论是全局分区索引或全局非分区索引，很大可能会出现分布式SQL
• 维护成本高：全局索引的维护成本更大，对于数据的DML或分区的维护操作都需要维护这个相对局部索引更庞大的全局索引表

在官方文档中推荐使用全局索引场景是：

• 保证全局唯一：除了主键外，还有其他需要满足全局唯一的列，业务上无法保证，仅能通过全局唯一索引实现的情况
• 实现分区裁剪：业务查询无法添加分区键的条件谓词，为避免全分区扫描，可以根据条件构建全局索引

全局索引中包含主键，主键中包含分区键，因此全局索引中是包含分区键，在一些情况下可以实现分区裁剪，但如果查询的数据就是分布在很多个分区，实际上还是会有性能问题

非必要不创建全局索引，若一定要创建还需要满足下面条件：

• 表写入量少，且没有高并发的写入
• 没有频繁的分区维护操作
• 字段的NDV值要高

前缀索引和非前缀索引

按照索引中是否包含分区键及分区键的位置，索引可以分为：

• 前缀索引：分区键是索引的前缀
• 非前缀索引：索引中不包含表分区键或分区键不是索引的前缀

前面两种分类方式进行组合，因此：

对于局部索引，有：

• 局部前缀索引
• 局部非前缀索引

全局索引，有：

• 全局前缀索引
• 全局非前缀索引

示例表

create table t(
  id int auto_increment,
  hash_id int ,
  ukey int,
  ukey_1 int,
  col1 varchar(20),
  col2 varchar(20),
  col3 varchar(20),
  col4 varchar(20),
  primary key(hash_id,id),
  unique key uk_global(ukey) global,      -- 全局唯一索引
  unique key uk_local(hash_id,ukey_1),    -- 局部唯一索引
  key idx_col1(col1),                     -- 局部非前缀索引
  key idx_local_prefix(hash_id,col1),     -- 局部前缀索引       
  key idx_local_noprefix_1(col1,hash_id),  -- 局部非前缀索引
  key idx_col2_global(col2) global,       -- 全局非前缀索引
  key idx_col2_global_noprefix(col2,hash_id) global, -- 全局非前缀索引
  key idx_col2_global_prefix(hash_id,col2) global  -- 全局前缀索引  
)
partition by hash(hash_id) partitions 8;

局部前缀和局部非前缀索引

对于局部前缀索引，官方描述适用的场景是：

• 指定索引键的查询利用局部索引可以唯一定位到一个索引分区，非常适用于结果集非常小，但是需要进行分区裁剪的场景

我的疑问：只要指定了分区键，都可以进行分区裁剪，和是否前缀有什么关系？

比如：在SQL使用 where hash_id=xxx and col1='xxx' 查询时，使用下面2个索引有什么不同？

key idx_col1(col1),                     -- 局部非前缀索引
key idx_local_prefix(hash_id,col1),     -- 局部前缀索引 

我的理解：

• sql中指定了分区键，无论索引中是否包含分区键都可以实现分区裁剪，这里hash_id作为前缀更重要的一个作用是进行数据过滤，也就是“使用于结果集非常少”的场景，因此对于分区键在分区中的区分度又非常高的场景很适用

对于局部非前缀索引，官方描述适用的场景是：

• 如果分区键不是索引的子集，那么局部非前缀索引不能是唯一索引。（这里的前提是分区键不是索引的子集。但如果分区键是索引的子集，唯一索引也可以是局部非前缀索引）
• 指定索引键的查询利用局部非前缀索引，不能定位到索引分区，而是需要访问所有索引分区，因此比较适合访问数据量比较大，注重并发的场景

我的理解：

• 对于局部非前缀索引有两种形式：
  • 不包含分区键，例如：key idx_col1(col1),
  • 包含分区键，例如：key idx_local_noprefix_1(col1,hash_id),

这里说的适用场景主要是第一种形式，对于指定索引键，但没有指定分区键的SQL，可以利用分区间的并行执行，但是这里需要进行全分区扫描，对于分区个数比较多的场景，还是可能会有性能问题

因此，对于局部非前缀索引，还是建议要指定分区键进行分区裁剪后，再使用到该局部索引

对于局部前缀索引，则一定要加上分区键才能使用到该索引，就实现了分区裁剪

对于第2种形式，使用到全部的索引字段，需要指定分区键，hash_id可以起到过滤条件的作用，索引键中不加，索引中也包含了，不太有必要（分区键在主键中的位置不同，过滤效果有所不同，但如果想要起到数据过滤的作用，不如直接用局部前缀索引）

全局前缀和全局非前缀索引

全局前缀索引

key idx_col2_global_prefix(hash_id,col2) global,  -- 全局前缀索引

• 对于hash分区的情况下，如果查询条件是hash_id>=xxx and hash_id<=xxx and col2='xxx'或hash_id>=xxx and hash_id<=xxx and col2>'xxx'

  • 若使用该全局前缀索引，这种场景实际上没什么优化意义，范围内的hash_id很大可能还是分布在多个分区，且需要扫描还hash_id范围内的所有记录判断是否满足col2
  • 因此通常还是需要进行点查，指定分区键条件，是否为前缀没什么影响。比如：sql条件为hash_id=xx and col2=xx，使用(hash_id,col2)和(col2,hash_id)全局索引的效果是一样的

• 对于range分区，可以进行一定程度的分区裁剪

全局非前缀索引

文档中描述，不支持全局非前缀索引，但实际以下索引都可以创建成功：

key idx_col2_global(col2) global,          -- 全局非前缀索引
key idx_col2_global_noprefix(col2,hash_id) global, -- 全局非前缀索引

对于(col2,hash_id) ，主表根据hash_id分区，实际上的效果相当于局部索引了，如果要实现分区裁剪，需要用上所有的字段（包括分区键），不如直接建个局部索引

对于(col2)，有一些情况下可能会实现一定的分区裁剪

唯一索引

唯一索引有局部唯一索引和全局唯一索引两种

• 局部唯一则保证的是分区内唯一
• 全局唯一则保证全局唯一

说明：

局部唯一索引必须包含分区键，否则会报错：

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

因此，对于唯一索引有下面两种形式：

• unique key uk_local(hash_id,ukey_1), -- 局部前缀唯一索引
• unique key uk_local(ukey_1,hash_id), -- 局部非前缀唯一索引

唯一索引在数据写入的时候会进行唯一性约束检查，对性能有所损耗，具有唯一性约束的字段，建议创建为主键

小结

1. 全局索引使用上的两个主要作用是保证全局唯一和实现分区裁剪，但是维护成本很高，且实现分区裁剪的作用有限（需要满足一定的条件），非必要不使用，若需要使用全局索引一定要做好评估
2. 不要使用全局唯一索引，如果要保证唯一看下是否可以放在主键中，局部唯一索引带来的副作用要比全局的小
3. 如果主表的分区键是索引的子集，可以采用局部索引
4. 局部前缀索引可以最大程度地进行分区裁剪，减少索引分区数据的读取，在分区键选择性很高的情况下可以考虑
5. 包含分区键的局部非前缀索引，建议去掉分区键，实际上索引中已经包含了分区键，如果要实现过滤数据的作用，不如创建为局部前缀索引
6. 不包含分区键的局部非前缀索引，可以一定程度上利用分区间的并发，但分区个数比较多的时候可能会有性能问题