DWS 索引的正确“打开姿势”

举报
hoholy 发表于 2024/01/15 15:30:36 2024/01/15
【摘要】 1. 前言适用版本:8.1.0及以上本文章先描述了常用的索引,并针对B-tree和Psort两种索引具体介绍,下面给出索引的利与弊。除了索引,还介绍了分区、PCK等其他查询提速的手段。最后给出各种索引和调优手段的使用场景。 2. 索引介绍 2.1 索引的作用索引能干什么呢,一言以蔽之:查询加速。常见的索引有下面几种:索引类型描述B-tree行存默认使用的索引,综合性索引,特别适用于点查、主...

1. 前言

  • 适用版本:8.1.0及以上

本文章先描述了常用的索引,并针对B-tree和Psort两种索引具体介绍,下面给出索引的利与弊。除了索引,还介绍了分区、PCK等其他查询提速的手段。最后给出各种索引和调优手段的使用场景。

2. 索引介绍

2.1 索引的作用

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

索引类型 描述
B-tree 行存默认使用的索引,综合性索引,特别适用于点查、主键
Psort 列存默认使用的索引,存储空间小,导入性能影响小,查询提升效果比较中庸
Gin 基于B-tree树结构的倒排索引,存储被索引字段的VALUE或VALUE的元素,主要适用于数组过滤、全文检索的场景
Gist 一个通用的索引接口,不同的类型支持不同的检索方式,主要适用于位置搜索
Hash 存储的是被索引字段VALUE的哈希值,只支持等值查询,特别适用于字段VALUE非常长的场景
特殊类型 表达式索引、部分索引、唯一索引

2.2 B-tree索引

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

图1 B-tree存储结构
  • B-tree是平衡树,有序存储索引KEY值和TID;

  • 对于索引上的过滤条件,通过KEY快速找到对应的叶子节点,然后再通过TID找到实际记录;

  • 索引中的数据以非递减的顺序存储(页之间以及页内都是这种顺序),同级的数据页由双向链表连接;

  • 支持单列索引和复合(多列)索引,多列复合索引适用于多列组合查询,B-tree索引对于查询条件的顺序有要求;

  • B-tree索引可以处理等值和范围查询;

  • 索引页面不存储事务信息

2.3 Psort索引

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

图3 Psort索引示意图
  • Psort索引本身是个列存表,包含索引列和tid,在索引列上局部排序,利用MIN/MAX块过滤加速TID获取;

  • Psort索引本身有可见性,但删除、更新数据不会作用到Psort索引;

  • Psort索引更适合做范围过滤,点查询速度较差;

  • 批量导入场景下有效,对于单条导入无效;

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

    对比项 Psort B-tree
    点查
    范围查询
    导入性能
    磁盘占用
    适用范围 列存 行存、列存

2.4 特殊索引

2.4.1 表达式索引

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

2.4.2 部分索引

比如创建一个部分索引“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’”就不能使用该索引。部分索引用来减少索引的大小,排除掉查询不感兴趣的数据,同时可以加速索引的检索效率。

2.4.3 唯一索引

  • 只有B-tree索引支持唯一索引;
  • 当一个索引被声明为唯一时,索引中不允许多个表行具有相同的索引值;
  • 空值被视为不相同,一个多列唯一索引将会拒绝在所有索引列上具有相同组合值的表行;
  • 对于主键列会自动创建一个唯一索引;
  • 唯一性检查会影响索引插入性能;

2.5 索引的利与弊

索引的优点如下:

  • 点查询提速显著,直接定位到需要的位置,减少无效IO;

  • 多条件组合查询,过滤大量数据,缩小扫描范围;

  • 利用倒排索引加速全文检索;

  • 利用等值条件索引查询速度快的优势,结合nestloop提高多表join效率;

  • 提供主键和唯一性约束,满足业务需要;

  • 利用btree索引天然有序的特点,优化查询计划;

    索引的缺点如下:

  • 索引页面占用额外空间,导致一定的磁盘膨胀;

  • 每次数据导入同时需要更新索引,影响导入性能;

  • 索引页面没有可见性,存在垃圾数据,需要定期清理;

  • 索引扫描性能并不总是比顺序扫描性能更好,一旦优化器判断有误,可能导致查询性能反向劣化;

  • 索引需要记录XLOG,增加日志量;

  • 每个索引至少一个文件,增加备份恢复、扩容等操作的代价;

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

3 DWS查询提速

3.1 分区

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

  • 目前支持的分区是range分区,分区支持merge、split、exchange等操作;

  • 在时间维度或者空间维度等具有一定数据规律的列上创建分区,分区列上的过滤条件会先做分区剪枝,减少物理扫描量;

  • 相比较索引,分区直接把原始数据物理划分,一旦分区剪枝生效,会极大的减少IO;

  • 使用分区和使用索引并不冲突,可以给分区创建索引;

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

  • 分区对于导入的影响是增加内存使用(内存不足时会下盘),但不产生额外的磁盘占用;

  • 使用分区一定要注意分区列的选择和分区数量的控制,分区过多会导致小文件问题,分区数量建议最多不超过1600个;

  • 分区剪枝适合范围查询,对于点查询效率提升有限;

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

图4 导入数据

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

图5 查询性能对比

3.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%:

图6 PCK查询示意

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

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

图7 列存表,非分区,无PCK查询计划

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

图8 列存表,非分区,有PCK查询计划

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

图9 列存表,有PCK,再创建btree索引查询计划

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

3.3 智能过滤

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

图10 智能过滤示意图

4 索引使用场景推荐

场景描述 使用推荐
对于批量导入性能要求很高的场景 使用列存表:
1. 如果要兼顾点查询和范围查询性能,可以创建Psort索引;
2. 对点查没有性能要求,建议不创建索引或者PCK
要支持主键或者唯一索引 使用B-tree索引
点查询要求很高的场景 1. 对于行存表推荐使用B-tree索引
2. 对于列存表推荐使用B-tree索引 + PCK
范围查询的场景 1. 对于行存表推荐使用B-tree索引
2. 对于列存表推荐使用B-tree索引 + PCK
多列组合查询的场景 1. 如果查询条件顺序固定,建议使用单个复合索引(B-tree或者Psort都可以)
2. 如果查询条件顺序变化较多,建议创建多个单列索引或者单个符合GIN索引
3. 在条件过滤效果最好的一列上创建PCK
4. 过滤效果不好的列或者重复数据较多的列不建议创建索引
数据有时间规律且可以过滤 1. 建议在时间列上创建分区
2. 如果有点查,可以组合使用索引
对特定SQL优化 1. 有表达式条件过滤,可以使用表达式索引
2. 有部分数据不感兴趣,可以使用部分索引
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。