PostgreSQL创建INDEX和并行原理

开源软件联盟PostgreSQL分会 2020-12-31

967

（本文阅读预计时间：5分钟）

It's a long long story, 从PG8.3引入了Heap-Only-Tuple，主要的作用在用于减少更新所需的I/O数量，基于postgreql的原理行的更新等于插入新的tuple，基于多版本控制MVCC，Postgres中的更新包括查找要更新的行，并将该行的新版本插入数据库，引入的问题就是显而易见的，索引，这就需要更多的I/O，数据要重新插入到表上的每个索引中。在插入的过程中需要先读取每个相关的索引，新版本行的物理位置与旧版本的物理位置不同。那一个表中有的索引越多，更改的数据量越大，牵扯的索引的消耗就越大。

从上图可以看，如果没有HOT，则索引页面在更新后，需要另一个指针指向修改后的INDEX数据，如果有了HOT则不需要通过另外的指针C去指向修改后的索引，仅仅通过原有的指针，在原有的索引tuple1上通过t_cid来指向新的index的数据即可。在数据结构中的样子参看下图：

HOT的效率虽然高，但需要达到某些需求，才可以实现，从上图看

数据必须（tuple）必须在一个页面
更新的数据中不能包含INDEX本身的数据

下面我们开始做相关的实验，看看HOT在实践中是怎样的

create table test (id int, name varchar(200), age float, datetime timestamp); 
insert into test select generate_series(1,10), random()*100, random()*1, now();  
create index idx_test_name on test (name);
create index idx_test_age on test (age);
create index idx_test_datetime on test (datetime);

通过pageinspect来对当前的index页面进行一个细节观测

测试1 将name中的ID=1的值进行update但更改的值和原来一样

测试2 将datetime中的ID=1的值进行update到新的值，可以注意到，只要更新到有索引的字段，无论是那个，都会触发所有的INDEX的更新，图中仅仅是更新了ID=1的datetime值，但其他的索引也都进行了更新，10行变成11行

我们对表test继续vacuum

可以从下图看到垃圾的版本的index tuple已经被回收了（部分截图由于屏幕原因，并未截图完全）

为什么更改同样的值的时候，索引不会被更新因为PG11时引入了recheck_on_update作为创建索引的默认值，这样索引会检测update时的值是否与现有的值不同，相同就不会触发更新索引tuple的操作。

讲到这里其实还没有到INDEX CONCURRENTLY，上面仅仅是粗略的介绍了HOT。

实际上索引并行建立，需要三个phase

开始在系统的catalog声明我们要开始建立新的索引，此时索引对于老的事务来说是不可见的，对于新的事务是可见的。
开始创建索引，通过对表的MVCC快照，对表中产生快照的MVCC中的可见行，开始建立建立索引。
当第二步创建索引成功后，其实在此刻与上次MVCC的之间间隔，有插入UPDATE，DELETE的操作，这些是当前新索引与当前行的数据不一致的地方，此时会开始第二次MVCC，在次MVCC做了快照，并修复新的索引后，后续就是开始维护索引，此时索引已经可以正常的工作了。

但整体的并行的索引的创建工作并未结束，因为新的索引对于老的事务是不可见的，所以不能推出工作，所以必须等待比这个索引老的事务都结束了才能进行整体的索引可见和正常工作。

所以大事务和长时间运行的事务，都是并行索引创建并尽快完成的阻碍，另外需要两次MVCC所以一般来说并行索引的创建的时间也就比一般非并行创建享有独占锁方式创建索引的时间要长。