昨天讨论了PG索引优化的统筹建设问题，其实PG索引优化有很多与Oracle不同的策略，主要是因为两方面的原因。首先，PG的MVCC实现方式存在的vaccum问题导致了索引维护的成本高于其他数据库。其次是PG DBA的一种幸福的烦恼，PG支持的索引种类太多了，不同的应用场景可以选择不同的索引。
今天和大家一起总结几条PG索引优化的建议。这些建议可能在你设计或者优化PG数据库的时候，对你会有帮助。因为不少数据库都是基于PG开源项目的，因此PG优化的很多方法在人大金仓、瀚高、Opengauss等国产数据库上也是有效的。1）统筹索引创建
针对应用的特点，以核心业务表为基础，根据访问这些表的主要SQL以及扫描方式，统一考虑索引的设计，尽可能设计一些可以在多场景使用的复合索引，避免创建不必要的单列索引，减少一张表上的索引总数是主要的设计原则。
我们该如何统筹创建索引呢？实际上这种统筹设计应该从研发开始，在做数据模型设计的时候，不仅仅要考虑表和字段，更重要的是要考虑数据的访问方式，通过理解数据的访问方式，对表做好索引的设计。除了主键外键索引外，一些和相关数据相关的访问都需要考虑是通过索引还是通过通过表扫描。如果某个访问必须通过表扫描来完成，那么我们要考虑是否可以通过表分区或者历史数据归档来提高该访问的效率。
对于主键索引，也可以考虑应用的访问特性，如果需要可以创建为复合索引，从而减少索引的数量。主键索引往往也是业务访问最为频繁的索引，因此对主键索引的设计尤为重要。
2）认真设计复合索引字段顺序设计
复合索引可以为多个应用场景服务，不过复合索引创建的时候一定要认真设计其字段顺序，经常被使用的某个字段应该放在第一顺序，从而让不同的应用场景都可以用到这个索引。对于多字段的复合索引，第二字段的选择也十分重要。对于某条SQL，如果有三个过滤条件，需要创建一个三字段的复合索引的时候，第二字段的选择的首选肯定是选择性更强的字段。而出于统筹考虑，针对整个系统设计索引的时候，不仅仅要考虑选择性的问题，还要考虑业务中使用频率较高的字段，以及为满足核心业务所作的妥协。比如a+b+c的复合索引，根据总体的访问特性，确定a是索引首字段，那么是创建（a,b,c)的索引还是（a,c,b)的索引呢？可能有些朋友觉得反正首字段都确定无误了，怎么建问题都不是很大吧。确实是的，对于大多数情况来说，无论怎么建，对系统来说关系并不大。不过在一些关键核心业务中，创建正确的索引还是十分关键的。比如说c的选择性远好于b，那么在不同的场景中，可能需要选择不同的策略。A)如果B的使用频率更高，那么毫不犹豫选择（a,b,c)B)如果C的使用频率更高，那么优先选择(a,c,b）C)如果C的使用频率更高，但是要确保效率的核心业务需要使用(a,b)的索引,那么优先考虑（a,b,c）上面并未列出所有的场景，每个用户的场景也可能各有不同，因此实际上的场景可能更为复杂，如果你对索引的设计上不是很确定，那也没关系，在测试阶段针对相关的模块多做模拟测试和压力测试，如果测试的效果不错，那么你就可以放心了。3)消除不必要的重复索引
消除不必要的索引是一项需要一定技能的工作，我们经常会同时在一张表上创建（a,b,c)/(a,b,c,d)/(a,c,b)等索引，这些索引在功能上是存在一定的重复性的，相同的字段在多个索引中出现。在PG数据库中，因为可以创建的索引种类更多，因此同时出现相同字段相同顺序，类型不同的索引的可能性也很大。对于这些索引，可能存在很多重复性的索引，其中某些索引很少使用，那么对于这些很少使用的重复性索引，我们就可以进行索引的清理。通过pg_stat_user_indexes统计视图我们可以很清晰的了解到某个索引最近是否使用过。4）定期审计未使用的索引
如果有些索引长期未被使用，那么有可能这些索引是错误的创建的，这些索引不会发挥任何作用，而且会占用不必要的空间让数据插入修改删除的成本变大，增加备份的开销。清理这些长期未使用的索引对系统整体性能提升帮助很大。通过pg_stat_user_indexes统计视图我们可以很清晰的了解到某个索引最近是否使用过。如果对于一段时间（比如几个月，半年）没有使用的索引，我们可以列入未使用索引清理备选清单，经过甄别确认后，进行清除。在清理索引的时候，一定要十分注意，某些索引可能是月统计、半年报或者年报使用的，平时可能没有被使用。因此我们在做分析的时候一定要注意这点。错误的清除索引会导致某些应用模块变慢，这也是索引清理工作变得十分困难的主要原因。5）用定期汇总数据来替代不常用的索引
在实际应用中，我们的很多大型数据表上的索引并不是为了交易系统创建的，而是为了满足一些定期报表任务而创建的。实际上这些索引可能每个月、每个季度、半年甚至一年才会偶尔使用一次，这些索引又不能简单的清除。如果要清理这些索引，可以考虑对系统的数据进行分级汇总，比如每天形成一个汇总记录，阅读汇总数据可以通过日汇总来生成，年汇总可以通过月汇总来生成。这样就避免了年度汇总应用的高开销。如果年度汇总不再从原始数据中产生，那么这些专用索引就可以清除了。6）为应用选择最适合的索引类别
PG的索引种类很多，因此PG的开发人员与DBA一定要了解PG索引的种类，并选择适当的索引种类，从而获得最佳的效率。具体如何选择合适的索引种类是个大课题，我们以后专门写一篇来讨论。覆盖索引、部分索引、BRIN索引等在某些应用场景中都有很好的应用效果。随后几点中我们重点介绍。7）巧用覆盖索引
覆盖索引是PG中的一个新的索引类别，从PG 11开始支持。覆盖索引可以在索引中附加其他字段，从而让某些应用的效率更高，更少的访问表数据，从而完成SQL扫描工作。与普通的复合索引相比，因为某些附加字段并不参与索引键，因此（a,b)+c的覆盖索引比（a,b,c)的覆盖索引在某些场景下的效率要高很多。8）通过部分索引提升索引效率
部分索引是一个十分有效的索引类型。如果你的应用只需要从一张上亿条记录的表中，和某个小部分的数据打交道（比如几万条），每次都是从这几万条数据中找到几百条数据来处理，或者总是重复扫描这几万条数据。而很少会访问其他的数据，那么针对这个应用创建部分索引是一个比较好的优化方案。虽然都是B树索引，不过部分索引的体量远远小于普通的全量索引，索引重建的效率也很高，因此对于只需要使用部分索引的场景，尽可能不要选择建全量索引。9）为全文检索使用适当的索引
用B树索引来做全文建索，只是一种权宜之计，因此对于真正需要全文建索的场景，一定要选择使用GIN/GiST等索引，而不要用B树索引凑合。GIN/Gist在不同的场景下的效率也是有差别的。普通的全文检索场景，GIN的效率要远高于GiST。具体如何选择针对你的具体数据做一下测试就很清楚了。本文篇幅有限，不做更细致的分析了。10）利用BRIN索引优化时序数据的访问
对于时序数据，B树索引虽然也能发挥作用，但是比起B树索引，BRIN索引对于时序数据更为有效。关于BRIN索引的一些特征，前几天写过一篇关于PG索引种类的文章，大家可以参考。实际上，说起PG索引优化来，这10条是无法涵盖的很全面的，不过大体上认真的思考过这10条，对于大多数PG索引优化场景来说就够用了。希望这10条能对大家有帮助。
