索引优化

在大部分传统的TP型数据库中，索引可以极大的提高数据的访问效率。但是在类似与AnalyticDB PostgreSQL版这样的分布式数据库中，应该谨慎的选择索引的使用。在大部分场景下，AnalyticDB PostgreSQL版更适合快速的顺序扫描，或者结合稀疏索引来进行减少数据的I/O操作。AnalyticDB PostgreSQL版会将数据尽量均匀地分布在所有的计算节点上，因此，在节点足够多的情况下，每一个计算节点只会扫描属于自己的一小部分数据。并且对于BI报表类查询，通常会返回很大的数据集，使用索引在这种场景并不一定有加速查询的效果。

在使用AnalyticDB PostgreSQL版时，首先应该尝试在没有增加任何索引的情况下执行您的查询。索引通常都是更适合于TP场景的，只返回一条记录或者返回极少量数据集的数据。使用索引也会给数据库带来一些额外的开销，比如需要更多的存储，以及数据的写放大，还有包括在进行数据update时的索引维护工作的开销。因此我们需要确保我们为表增加的索引相对于全表扫描，能够切实、有效地提高了查询效率，否则宁愿不建索引。

在当您想要建索引时应该需要考虑以下几点：

• 您的查询负载： 索引可以有效地提高返回单条或者小数据结果集的查询，比如TP类的查询。

• 压缩表或者适合使用稀疏索引的范围查询： 索引可以提高压缩的AO/AOCS表的查询效率，尤其是在进行范围查询时使用稀疏索引，可以有效地避免加载无效的数据，从而优化I/O效率。同理，更少的数据加载意味着更少的数据解压缩，可以有效地降低解压操作CPU的使用。

• 避免对频繁更新的列建索引：对于数据频繁更新的列，如果数据频繁更新，索引也会频繁更新，这会极大的降低数据更新的性能。

• 只对高选择率的列建B-Tree索引： 索引选择率是构建索引时选择什么索引类型的重要指标，B-Tree索引只推荐使用在选择率非常高的列上。比如您的表有1000行数据，需要建索引的列有800个distinct的值，那么这一列索引选择率就是800/1000=0.8,这种场景就很适合建B-Tree索引。如果您建索引的列的值是唯一的，建议选择B-Tree索引。

• 选择率低时考虑使用Bitmap索引：AnalyticDB PostgreSQL版支持Bitmap索引类型，其非常适合于distinct值范围在1000-100,000内的类的索引。尤其是结合排序键对数据聚集，可以达到用很小的索引达到很惊艳的索引加速效果。

• 考虑对经常进行join操作的列建索引：在执行join时使用索引可能会有更好的执行计划。

• 对于经常做限定条件查询的列建索引：对于经常更在WHERE后面做限定条件的列，可以考虑建索引。

• 防止对同一个列建多个索引：对于同一个列建同一类前缀索引是没有意义的，并且会在写入和更新时增加额外的开销。

• 结合排序键或者Cluster功能加速索引效率：大部分索引的效率都会极大的受到数据物理分布的影响，尤其是BRIN索引和稀疏索引。因此，根据索引的字段组织合适的数据物理分布可以有效地提高索引的性能。您可以使用排序键结合组合排序和多维排序改善数据的物理分布。同时对于行存表如果建立的是B-Tree类型的索引，可以考虑使用Cluster进行物理排序。

• 大数据集场景下优先考虑稀疏索引：如果您的数据量非常地大，并且您的查询限定条件为<、<=、=、>=、>，需要从大数据量的表中取出少于50%的数据，那么使用稀疏索引（BRIN Index或者AOCS表的metascan）可以极大地减少无效数据的加载。

• ： 对于载入大量数据到一个表中，请考虑先删掉索引并且在数据装载完成后重建它们。这常常比更新索引更快。

• 避免在频繁更新的表上建Bitmap索引： Bitmap索引最适合用户只查询数据而不更新数据的数据仓库应用，不适合有大量并发事务修改数据的OLTP应用。

• 合理使用表达式索引：索引列不必只是表的一列，而是可以是从表的一列或多列计算的函数或标量表达式。 此功能对于根据计算结果快速访问表非常有用。比如查询SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';可以收益于表达式索引CREATE INDEX people_names ON people ((first_name || ' ' || last_name));。