索引设计

本文介绍在 OceanBase 数据库中进行索引设计的推荐设计。

索引简介

OceanBase 支持主键索引，唯一索引，也支持二级索引，构成以上索引的可以是单一列，也可以是多个列（复合索引）。在 OceanBase 数据库中，索引可以分为两种类型：本地索引和全局索引。默认创建的是全局索引(global)。

两者之间的区别在于：本地索引与分区数据共用分区，全局索引为单独分区。

• 创建本地索引：

  obclient> create table t1(id number  primary key, name1 varchar(10), name2 varchar(10));
  obclient> create index idx_t1_name1 on t1(name1) local;
  

  注意

  这里以非分区表为示例。对分区表创建索引时，如果不指定 local 或 global，默认创建全局索引（global）。

• 创建全局索引：

  obclient> create index idx_t1_name2 on t1(name2) global;
  

索引设计

单值索引要求和建议

• 索引建立需满足最左前缀原则。

• 表中所有带索引的字段建议都是 NOT NULL 属性，通常建议业务根据需要定义 DEFAULT 值。

• 业务上具有唯一特性的字段，即使是组合字段，建议创建为主键。

  说明

  即使唯一索引影响了 insert 速度，但这个速度损耗可以忽略，唯一索引提高查找速度是明显的；另外，即使在应用层做了非常完善的校验和控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

• 需要 join 的字段，数据类型需保持一致；多表关联查询时，保证被关联的字段建有索引。

  说明

  在多表关联（join）的场景中，也要注意表索引的使用可以很好地提升 SQL 性能。。

• 页面搜索严禁左模糊或者全模糊，如果需要的话可以通过搜索引擎来解决。同时尽量将过滤后的字段作为输入条件，可以避免后台产生大量对结果集的查找。

• 说明

  索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

  反例：表存在索引 idx_t2_abc(a,b,c)，但 where 条件为：b = ? and c = ?，因条件中并未包含 a，无法使用该索引。t2 表和索引idx_t2_abc(a,b,c) 的创建语句如下。

  obclient> create table t2(a number primary key, b int, c varchar(10));
  obclient> create index idx_t2_abc on t2(a,b,c);
  

  现在看下 where 条件为：b = ? and c = ? 的语句的执行计划。从执行计划明显可以看出，name 列为 t2，该语句没有走到该索引，走了全表扫，cost 为 409。

  obclient> explain select a,b,c from t2 where b=8889 and c='a(mbmtwm';
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Query Plan                                                                                                                                                                                                                                                                                                                                                                              |
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | ===================================
  |ID|OPERATOR  |NAME|EST. ROWS|COST|
  -----------------------------------
  |0 |TABLE SCAN|t2  |1        |409 |
  ===================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t2.a], [t2.b], [t2.c]), filter([t2.b = 8889], [t2.c = 'a(mbmtwm']),
        access([t2.b], [t2.c], [t2.a]), partitions(p0)
   |
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set 
  

  正例：上述索引调整字段顺序为 idx_bca(b,c,a) 或 idx_cba(c,b,a)，过滤条件仍为 b = ? and c = ?，则该复合索引会被使用。t2 表和索引 idx_t2_bca(b,c,a) 的创建语句如下。

  obclient> create table t2(a number  primary key, b int, c varchar(10));
  obclient> create index idx_t2_bca on t2(b,c,a) ;
  

  现在看下 where 条件为：b = ? and c = ? 的语句的执行计划。从执行计划明显可以看出，name 列为 t2(idx_t2_bca)，该语句走了正确的索引，cost 为 46，明显下降了很多。

  obclient> explain select a,b,c from t2 where b=8889 and c='a(mbmtwm';
  +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Query Plan                                                                                                                                                                                                                                                                                                                                                                                                 |
  +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | =============================================
  |ID|OPERATOR  |NAME          |EST. ROWS|COST|
  ---------------------------------------------
  |0 |TABLE SCAN|t2(idx_t2_bca)|1        |46  |
  =============================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t2.a], [t2.b], [t2.c]), filter(nil),
        access([t2.b], [t2.c], [t2.a]), partitions(p0)
   |
  +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set 
  

• 如果有 order by 的场景，请注意利用索引的有序性，避免出现 file_sort。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。

  正例：where a=? and b=? order by c; 索引：idx_t3_abc(a,b,c)。t3 表和索引 idx_t3_abc(a,b,c) 的创建语句如下。

  obclient> create table t3(a number  primary key, b int, c int);
  obclient> create index idx_t3_abc on t3(a,b,c);
  

  现在来看下 where a=? and b=? order by c; 语句的执行计划，OPERATOR 为 TABLE GET，COST 为 46。

  obclient> explain select a,b,c from t3 where a=117 and b=67176 order by c;
  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Query Plan                                                                                                                                                                                                                                                                                                                                                     |
  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | ==================================
  |ID|OPERATOR |NAME|EST. ROWS|COST|
  ----------------------------------
  |0 |TABLE GET|t3  |1        |46  |
  ==================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t3.a], [t3.b], [t3.c]), filter([t3.b = 67176]),
        access([t3.a], [t3.b], [t3.c]), partitions(p0)
   |
  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set 
  

  反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。因为索引中可能有以下数据已经按升序排好： {a = 11, b = 2} {a = 11, b = 3} {a = 12, b = 1} 此时若直接按序输出，则 b=1 排在 b=2 和 b=3 之后，顺序错误。

• 利用覆盖索引进行查询操作，可以避免回表操作。

组合索引要求和建议

• 如果索引包含多个列，要明智选择列的顺序，一般来说，索引的第一列应当是一个高基数列（基数：数据列所包含不同值的数量）即区分度最高的在最左边。

  示例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。

• 对 ORDER BY、GROUP BY、DISTINCT 子句中频繁使用的列添加到索引后面，形成覆盖索引避免回表。

• 若存在 where a=? and b=?的查询条件，使用组合索引 idx_ab(a,b)，而不要分别在 a、b 字段上建立 idx_a(a)，idx_b(b) 两个索引，后者将无法同时用到两个索引

• 合理设计组合索引同时满足多场景的查询，减少索引的冗余性：

  • 多条语句可以共用同一个索引，其中某些语句只要覆盖索引前缀即可。如有两条语句条件分别为：a = ? and b = ?，和b = ?，那么组合索引 idx_ba(b,a) 就可以同时为两条语句使用，不必再创建索引 idx_ab(a,b) 和 idx_b(b) 上分别建立索引。

  • 非必要情况下不要使用全局索引，尤其要杜绝 NDV 值小而且使用频率不高的全局索引。

  • where 条件中如包含有分区键字段，则可以在分区键字段和条件里的其它字段上建立联合本地索引。

  • 全局分区索引适合查询返回数据量较少的场景，对于返回数据量较大的场景建议在全局索引与本地索引间进行比较测试。

  • 选择 NDV 值大的字段作为全局分区索引的分区键。

  • 建议不要对分区表分区键、全局分区索引分区键所在字段进行 update 操作，若业务确有需要务必打开分区表的 row movement 功能。

    obclient> alter table XXX enable row movement;
    

  • 避免重复索引：冗余的索引影响数据的增删改效率，同时浪费存储成本。

  • 主键默认创建索引和唯一性约束。

  • 索引 idx_abc(a,b,c) 已创建的情况下不需要再创建索引 idx_a(a) 和 idx_ab(a,b)。

分区表索引建议

• 按照本地索引->全局分区索引->全局索引的顺序进行选择，非必要情况下不建议使用全局索引。

• 减少不必要的全局索引定义。

  全局索引维护代价很高，数据的增删改都需要维护全局索引， 不适宜大量使用，数据的查询要尽量使用主键或只要数据能在分区内保证唯一，则没必要使用全局索引。

  说明

  全局索引会降低 DML 的性能，可能会因此产生分布式事务。对于分区表的索引定义，如果默认不加 local 关键字即为全局索引定义，所以如果定义本地索引语法为 create index <index_name> on <table_name> (column, column) local。

• 分区表上的主键必须包括表的分区键。

• 分区表上的本地唯一键的交集必须包括表的分区键。

索引使用注意事项

• 上线 SQL 前确认新建索引已生效。

• 索引修改流程为：先新建索引待新索引生效后，确保旧索引无用再删除

• 索引过多时，请根据需要删除不使用的索引，避免索引越建越多。

  注意

  这个操作风险很大，必须确认删除的索引没有其他 SQL 使用。

• 禁止 alter table add index/drop index 同时混用在一条 DDL。

• 创建索引时避免有如下极端误解：

  • 误认为一个查询就需要建一个索引。

  • 误认为索引会消耗空间、严重拖慢更新和新增速度。

  • 误认为唯一索引一律需要在应用层通过"先查后插"方式解决。

• 全局索引使用注意。

  对 partition 进行运维时，请注意 drop 或 truncate 操作会导致全局索引失效。