PostgreSQL关于唯一索引的几个细节

原创阎书利云和恩墨技术服务团队 2024-10-23

898

1.验证唯一索引的索引文件里是否可以存储多个版本的值

结论：PG唯一索引里可以存储多个版本的值。

更新后只有一个索引值，是因为没有checkpoint。对应的新值没有落盘。手动做checkpoint，新的值会写入索引文件。

2.验证唯一索引的索引文件是否可以存储重复值

结论：唯一索引的索引文件可以存储重复值

3.索引的可见性问题

PostgreSQL数据库执行计划为IndexOnlyScan时可能并没有真的不发生回表。可以根据
Heap Fetches进一步确认。Heap Fetch表明了在index-only-scan期间，需要扫描表的数据页个数。

索引是不包含可见性信息的，可见性信息存在元组 tuple 之上 (xmin、xmax)，总是需要回表去确认可见性，在 9.2 之后，支持了 Index only scan，搭配 visibility map，可以无需回表确认可见性就返回数据。

VM是用BIT位标识page中是否有dead tuple的。

/* Number of bits for one heap page */
#define BITS\_PER\_HEAPBLOCK 2

VM中为表的每个文件块设置了2bits，用来标记该文件块是否存在无效元组

/* Flags for bit map */
#define VISIBILITYMAP_ALL_VISIBLE  0x01//全部可见（该page所有元组都对事务可见）
#define VISIBILITYMAP_ALL_FROZEN 0x02//全部冻结

Postgresql中如果执行计划走IndexOnlyScan通常说明扫描的字段都在索引中了，意味着不必回表直接返回结果。但PG中索引页面是没有多版本信息的，表上才有，可见行也是根据vm文件或者表的隐藏列决定的。

IndexOnlyScan访问vm页面判断如果页面的可见性为VM_ALL_VISIBLE，说明页面内没有修改过的元组，不会出现dead tuple，可以直接使用索引数据（这才是真的index only scan），不必去回表读表的数据并找根据隐藏列判断可见行。

如果VM_ALL_VISIBLE为假，说明页面内修改过元组，有dead tuple，需要去扫堆页面找到可见的元组（虽然可能执行计划是index only scan，但是由于索引指向的堆元组，无法确定可见性，所以还是要去扫堆页面，是假的index only scan）。所以PostgreSQL数据库执行计划为IndexOnlyScan但有时候并没有真的不发生回表。从图中的Heap Fetches可以看到还是扫了一个数据页。

4.唯一索引的“唯一”是怎么区分的

先扫索引，索引如果有重复值，去进一步根据索引记录的的ctid找表对应的块，看这条记录在块里存储的tuple，根据隐藏列判断是否是活跃的（即不是历史的死元组）。

如下部分是截取PG官方文档（翻译版本）

_bt_check_unique是在存在唯一索引时，插入值时一个关键的函数，去做唯一约束的检查。
可以看到因为唯一索引的原因，插入前，checkUnique=UNIQUE_CHECK_YES。

这个类型中共有四种，分别是：不进行任何唯一性检查、在插入时强制唯一性、测试唯一性但不会报错、检查现有元组是否唯一。

typedef enum IndexUniqueCheck
{
    UNIQUE_CHECK_NO,            /* Don't do any uniqueness checking */
    UNIQUE_CHECK_YES,           /* Enforce uniqueness at insertion time */
    UNIQUE_CHECK_PARTIAL,       /* Test uniqueness, but no error */
    UNIQUE_CHECK_EXISTING,      /* Check if existing tuple is unique */
} IndexUniqueCheck;

判断的逻辑是如下：


        if (!indexRelation->rd_index->indisunique)
            checkUnique = UNIQUE_CHECK_NO;
        else if (applyNoDupErr)
            checkUnique = UNIQUE_CHECK_PARTIAL;
        else if (indexRelation->rd_index->indimmediate)
            checkUnique = UNIQUE_CHECK_YES;
        else
            checkUnique = UNIQUE_CHECK_PARTIAL;

根据索引视图里的状态信息做判断。