索引的正确“打开姿势”

本文分享自华为云社区《DWS 索引的正确“打开姿势”》,原文作者:hoholy 。

索引能干什么呢,一言以蔽之:查询加速。常见的索引有下面几种:

1. 常用索引介绍

1.1 B-btree索引

B-tree存储结构示意如下:

  • B-tree是平衡树,有序存储索引KEY值和TID;
  • 对于索引上的过滤条件,通过KEY快速找到对应的叶子节点,然后再通过TID找到实际记录;
  • 索引中的数据以非递减的顺序存储(页之间以及页内都是这种顺序),同级的数据页由双向链表连接;
  • 支持单列索引和复合(多列)索引,多列复合索引适用于多列组合查询,B-tree索引对于查询条件的顺序有要求;
  • B-tree索引可以处理等值和范围查询;
  • 索引页面不存储事务信息;

在数据库里面举个例子,如何创建B-tree索引:

1.2 Psort索引

Psort索引数据结构示意如下图所示:

  • Psort索引本身是个列存表,包含索引列和tid,在索引列上局部排序,利用MIN/MAX块过滤加速TID获取;
  • Psort索引本身有可见性,但删除、更新数据不会作用到Psort索引;
  • Psort索引更适合做范围过滤,点查询速度较差;
  • 批量导入场景下有效,对于单条导入无效;

横向对比B-tree、Psort如下:

1.3 特殊索引

  • 表达式索引

比如对于查询“select * from test1 where lower(col1) = ‘value’;”可以建立在Lower表达式之上的索引“create index on test1(lower(col1));”,后续对于类似在lower(col1)表达式上的过滤条件,就可以直接使用这个索引加速,对于其他表达式该索引不会对查询生效。但需要注意的是:索引表达式的维护代价较为昂贵,因为在每一个行被插入或更新时都得为它重新计算相应的表达式。

  • 部分索引

比如创建一个部分索引“create index idx2 on test1(ip) where not (ip > ’10.185.178.100’ and ip < ’10.185.178.200’);”,使用该缩影加速的典型查询是这样“select * from test1 where ip = ’10.185.178.150’”,但是对于查询“select * from test1 where ip = ’10.185.178.50’”就不能使用该索引。部分索引用来减少索引的大小,排除掉查询不感兴趣的数据,同时可以加速索引的检索效率.

  • 唯一索引

(1)只有B-tree索引支持唯一索引;

(2)当一个索引被声明为唯一时,索引中不允许多个表行具有相同的索引值;

(3)空值被视为不相同,一个多列唯一索引将会拒绝在所有索引列上具有相同组合值的表行;

(4)对于主键列会自动创建一个唯一索引;

(5)唯一性检查会影响索引插入性能;

1.4 索引的利与弊

索引的优点如下:

  • 点查询提速显著,直接定位到需要的位置,减少无效IO;
  • 多条件组合查询,过滤大量数据,缩小扫描范围;
  • 利用倒排索引加速全文检索;
  • 利用等值条件索引查询速度快的优势,结合nestloop提高多表join效率;
  • 提供主键和唯一性约束,满足业务需要;
  • 利用btree索引天然有序的特点,优化查询计划;

索引的缺点如下:

  • 索引页面占用额外空间,导致一定的磁盘膨胀;
  • 每次数据导入同时需要更新索引,影响导入性能;
  • 索引页面没有可见性,存在垃圾数据,需要定期清理;
  • 索引扫描性能并不总是比顺序扫描性能更好,一旦优化器判断有误,可能导致查询性能反向劣化;
  • 索引需要记录XLOG,增加日志量;
  • 每个索引至少一个文件,增加备份恢复、扩容等操作的代价;

鉴于索引的使用是一把双刃剑,创建索引要谨慎,只给有需要的列创建,不能过滤大量数据的条件列不要创建索引。除了索引可以优化查询效率,存储层还有没有其他优化手段呢?下面给大家再介绍几种DWS查询提速的手段。

2. DWS查询提速

2.1 分区

分区是最常用的提速手段之一,而且效果很好,推荐大家结合场景多多使用。

  • 目前支持的分区是range分区,分区支持merge、split、exchange等操作;
  • 在时间维度或者空间维度等具有一定数据规律的列上创建分区,分区列上的过滤条件会先做分区剪枝,减少物理扫描量;
  • 相比较索引,分区直接把原始数据物理划分,一旦分区剪枝生效,会极大的减少IO;
  • 使用分区和使用索引并不冲突,可以给分区创建索引;

使用分区的注意事项如下:

  • 分区对于导入的影响是增加内存使用(内存不足时会下盘),但不产生额外的磁盘占用;
  • 使用分区一定要注意分区列的选择和分区数量的控制,分区过多会导致小文件问题,分区数量建议最多不超过1600个;
  • 分区剪枝适合范围查询,对于点查询效率提升有限;

下面举个例子,分别创建同样数据类型的分区表和非分区表,导入相同的数据640万条,用同样的查询会看到分区剪枝对性能提高了7倍多,准备数据:

分区和非分区查询耗时对比,其中test1是分区表,test2是非分区表,test1的查询scan耗时6ms,test2的查询scan耗时46ms,差距7倍还多:

2.2 PCK(partial cluster key)

PCK的本质就是通过排序提升查询过滤的效率,创建表时指定PCK列,该列上的数据会局部排序,有序的数据带来更好的数据聚簇性,每个数据块的min/max等稀疏索引就能更好的发挥作用,粗过滤掉大量的数据,提升IO效率,默认情况下420万行数据局部排序。

注意事项如下:

  • 只有列存表支持PCK,局部排序对每次导入的批量数据生效,不会做全排序;
  • PCK更适用于范围查询,点查场景下配套使用PCK和索引可以达到最佳效果;
  • 带PCK导入因为排序的原因会使用更多的内存,影响导入速度,需要权衡导入和查询性能;

举个例子,对于查询select * from tab where col > 65,如果不使用PCK,很可能一个CU都无法过滤掉,但如果使用了PCK,下图所示的5个CU就能过滤掉一半还多,提升查询性能至少50%:

再用上面分区的那组数据横向对比PCK的性能表现:

(1)列存表,非分区,无PCK,scan耗时46ms

(2)列存表,非分区,有PCK,scan耗时1.7ms

(3)列存表,有PCK,再创建btree索引,scan耗时0.1ms

PCK结合索引,可以将类似这种点查的性能提升100倍以上。

2.3 智能过滤

列存表数据从文件读出来,到反馈给执行层,中间会智能识别自动多层过滤,对用户完全透明,如下图所示:

3. 索引使用场景推荐

 

点击关注,第一时间了解华为云新鲜技术~

相关推荐