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

本文分享自华为云社区《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 * fromtest1 where ip = ’10.185.178.150’”，但是对于查询“select * fromtest1 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(partialcluster 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. 索引使用场景推荐

​

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