数据库

记一次给大表加索引

Posted by 呆贝斯 on August 25, 2023

问题场景

业务表随数据增加已达亿级，需要加索引进行优化查询。

思考

直接添加索引会消耗很长时间，添加索引阶段会不会影响业务

解决方案

分批次添加索引

对于大表，可以考虑将添加索引的过程分为多个步骤，每次处理一部分数据。这可以减少单个操作的负载，以减小对生产环境的影响。

  1. 创建部分索引 创建部分索引，该索引仅包括部分数据，而不是整个表。这可以通过在索引创建语句中使用 WHERE 子句来实现。

     create index index_tb_col_name_1 ON tb (col_name) where id < 10000;
 create index index_tb_col_name_2 ON tb (col_name) where id < 20000 and id >= 10000;
 create index index_tb_col_name_3 ON tb (col_name) where id < ... and id >= ...;
  2. 等待索引构建完成 让部分索引在后台构建完成，这通常需要一些时间，具体取决于表的大小和复杂性。你可以使用 PostgreSQL 的系统视图 pg_stat_progress_create_index 来监视索引构建进度。
  3. 合并索引 合并索引可以通过创建一个新的索引并将部分索引中的数据插入到新索引中来实现。然后，你可以删除部分索引。

影子策略

  1. 创建一张与原表（tb）结构相同的新表（tb_bak）

     create table tb_new as select * from tb where false;

  2. 重命名原表为其他表名（tb => tb_tmp），新表重命名为原表名（tb_new => tb），此时新表（tb）承担业务

     alter table tb rename to tb_tmp;
 alter table tb_new rename to tb;

  3. 为原表（tb_tmp）新增索引

     create index index_tb_col_name ON tb_tmp (col_name);

  4. 交换表，新表改回最初的名称（tb => tb_new），原表改回最初的名称（tb_tmp => tb），原表（tb）重新承担业务

     alter table tb rename to tb_new;
 alter table tb_tmp rename to tb;

  5. 把新表数据导入原表（即把新表承担业务期间产生的数据和到原表中）

     insert into tb select * from tb_new;

步骤2之后，新表改为原表名后（tb）开始承担业务，步骤4到结束之前这段时间的新产生的数据都是存在新表中的， 但是如果有业务对老数据进行修改或删除操作，那将无法实现，所以步骤3到结束这段时间可能会产生数据（更新和删除）丢失。

影子策略 + 触发器

利用三个触发器（DELETE\UPDATE\INSERT触发器）解决了“影子策略”存在的问题，让新老表数据同步时发生的数据变动也能得到同步。

  1. 创建一张与原表结构相同的新表

     create table tb_new as select * from tb where false;

  2. 对新表进行DDL操作（如加索引）

     create index index_tb_col_name ON tb_tmp (col_name);

  3. 在原表上创建3个触发器（DELETE\UPDATE\INSERT），用来原表复制到新表时（步骤4）的数据改动时的同步

  4. 将原表数据以数据块（chunk）的形式复制到新表

  5. 表交换，原表重命名为old表，新表重命名原表名

     alter table tb rename to tb_old;
 alter table tb_new rename to tb;

  6. 删除旧表，删除触发器

     drop table tb_old;

在线无锁加索引

PostgreSQL允许在不停机的情况下使用CONCURRENTLY选项来创建索引。这意味着索引的创建将在后台进行，不会阻塞表的正常操作。

create index concurrently index_tb_col_name ON tb(col_name);

使用CONCURRENTLY选项时要注意以下几点：

  1. 在创建索引期间可能会增加一些额外的系统资源消耗，因此需要监视系统性能。
  2. 索引创建可能需要一些时间，具体取决于表的大小和负载。
  3. 如果有其他并发操作正在修改表的数据，可能会导致索引创建失败。因此，建议在低负载时执行此操作，或者在非高峰时段执行。

主备切换

CATALOG