Postgresql concurrently index 为什么可以在线加索引

提到在线加索引都是商业数据库的功能，例如SQL SERVER 在线加索引就是你花钱买的版本也必须是企业版， 标准版都不能在线加索引。POSTGRESQL 支持在线加索引的功能，在本文撰写期间MYSQL 是不支持 online add index 对于几千万的大表建立索引还是要使用工具，并且8.0 使用gh ost 是有我问题，所以对于大表加索引并且是8.0的情况还得是 pt-osc.

下面是一段POSTGRESQL 的关于concurrently 的功能解释:

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done.

当这个选择被使用时,POSTGRESQL 在添加索引时将对于当前表的操作中的插入,更新,删除不会在加锁,而一个标准的INDEX 创建的过程是对于以上操作加锁的直到操作完毕.

那么这个操作的原理是什么,带着好奇心我们来看看这个操作是怎么运行的,并且有什么影响.要说明这个问题还需要从 HEAP-ONLY-Tuple 说起,PG 与其他数据库最大的不同之一在于MVCC多版本的控制,在多版本的控制中,在一行TUPLE被更新后,并不是在原来的位置变更数据,而是重新添加一个新的行,老的行就被抛弃了.

这样的方式对于索引的工作就比较复杂了,因为MVCC的原因 INDEX 也会产生多个版本,并且指向不同版本的数据,同时如果要清理这些DEAD TUPLE 需要清理这些dead index, 不及时清理这些索引,会引起 index bloating ，table bloating 的问题。

那么POSTGRESQL 是怎么解决这个问题的

Postgresql 在8.3 后改进了相关的方式,索引不在跟随行的变化而是在非特定的情况下,索引针对一行只有一个, 而通过HOT chain的方式在表中通过链的方式将老的tuple和 新的tuple 进行连接.

在有以上的知识后,我们看看 concurrently index 是怎么建立的

1 建立索引的元数据信息,如索引的列信息,索引的名字等信息,此时索引的信息对外不可见

2 索引建立元数据信息后,索引就可见了并且对其他的进程可见, 相关的索引开始建立与TUPLE的关联以及HOT CHAIN的关系,此时有一个问题就是在建立索引时,已经有一些事务在处理,而这些事务是不会知道相关新的索引建立的事情,而这些事务修改数据会在cache中存在,直到check point点后,落入磁盘.所以建立索引的点必须是在所有的事务都可见索引的情况下,索引才能开始建立.

3 索引开始建立, 而此时索引建立时是通过SNAPSHOT 时刻来建立的索引,此时建立的索引只是基于这一个时刻SNAPSHOT 情况下数据表的情况建立的索引. 在对这一时刻SNAPSHOT 数据表建立完索引后,第一次建立索引结束.

4 此时索引还并不能投入使用,这时的索引和实际的表的索引的差距是SNAPSHOT 和实际表中数据之间的差别,也是建立索引时到索引建立完毕后这一段时间表的变化或添加的新的行,而这些变化并没有被记录到新的索引中.

5 因为索引建立是基于第一次SNAPSHOT,所以建立在线索引的活动会再次发起,这次还是要等待所有的事务都结束后,开始建立SNAPSHOT,再次对表进行扫描将实际表中的数据的变化与索引的数据进行同步和改变.

6 在完成第二次SNAPSHOT 对索引的修改后,索引开始接受数据表的修改与索引的同步的工作,此时索引暂时不对外进行工作,只是在持续的同步表与索引之间的同步工作.此时要解决的问题是每行数据与索引之间的对应关系,以及热链,数据的变化,在确认一行数据与索引之间是同步的状态后,后面这行索引就可以正式开始同工作了.

7 最后索引与表的行之间不断的进行状态和行版本的确认修改,直到所有的行与索引都已经处于同步的状态. 索引的添加完成,此时索引才正式的可以开始正式提供相关的查询工作

下面的这段代码的注释可以给我们一些提示

1 在创建在线索引的时候,会使用较低级别的锁,而这个锁仅仅控制在添加索引期间不能对表的行有任何的变动,至于DML 是可以进行任意的操作的.并且对于建立索引是INDEXOID 和 HeapOID (表的行,与索引的行之间的对应)

实际上在线加索引不引起表DML 的停滞,保证业务持续的工作的前提就是时间,如果我们不在线添加索引此时表会停止工作和响应,所以考虑的问题少, 而在线加索引考虑的问题多, 需要的步骤多, 上面中至少有三个大的步骤,以及两次SNAPSHOT 和两次等待 事务的完整的完成.

所以在线加索引最怕的是大事务,如果此时有大事务在工作,那么在线加索引等待的时间就是你大事务的时间,所以即使是在线加索引,也需要在业务低峰期进行,这样等待的时间会小,并且遇到的麻烦也少.