不阻塞事务创建索引

PG环境

create index 是会阻塞 dml 操作的。在生产环境需要添加 concurrently 参数不阻塞事务创建索引

create index concurrently idx_index_name on table_name(column_name);

• CONCURRENTLY：使用此选项时，PostgreSQL 将在不使用任何锁的情况下构建索引，以防止在表上并发插入、更新或删除；而标准索引构建会锁定表上的写入（但不会读取），直到完成为止。
  • 使用此选项时需要注意几个注意事项
    • 它不能在事务块中执行create index concurrently命令
    • 引发DB扫两次表，同时等待所有潜在会读到该索引的事务结束，消耗更多的系统CPU，IO和内存资源
    • 尽量避免创建索引过程中，两次SCAN之前对被创建索引表实施长事务，并且长事务中包含修改被创建索引的表
    • 在第二次SCAN前，尽量避免开启长事务

CONCURRENTLY 实现原理

使用CREATE INDEX CONCURRENTLY创建索引，分为三个阶段，扫描两次TABLE。

create index CONCURRENTLY需要2次扫描，三次等待。三次等待分别是2次扫描表前，结束创建索引前。

create index CONCURRENTLY idx_tb1_id on tb1 (id);

1. 开启事务1，拿到当前snapshot1。
2. 扫描tb1表前，等待所有修改过tb1表（写入、删除、更新）的事务结束。
3. 扫描tb1表，并建立索引。
4. 结束事务1。
5. 开启事务2，拿到当前snapshot2。
6. 再次扫描tb1表前，等待所有修改过tb1表（写入、删除、更新）的事务结束。
7. 在snapshot2之后启动的事务对tb1表执行的DML，会修改这个idx_tb1_id的索引。
8. 再次扫描tb1表，更新索引。（从TUPLE中可以拿到版本号，在snapshot1到snapshot2之间变更的记录，将其合并到索引）
9. 上一步更新索引结束后，等待事务2之前开启的持有snapshot的事务结束。
10. 结束索引创建。索引可见。

Oracle 环境

使用online参数，CREATE INDEX ONLINE 和 DROP INDEX ONLINE

create index  idx_table_name on table_name(col_name) online ;

MySQL 环境

5.6 开始支持在线DDL创建索引

在线DDL限制