索引的正确“打开姿势”

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

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

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. 索引使用场景推荐

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

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

索引的正确“打开姿势”

原文：https://www.cnblogs.com/huaweiyun/p/14713517.html