摘要：本文章先形容了罕用的索引，并针对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 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. 索引应用场景举荐

点击关注，第一工夫理解华为云陈腐技术~