HGDB中如何在线重建索引

文档用途

本文档主要用于指导瀚高数据库使用人员在线重建索引。

详细信息

情景一：单个索引/少量索引重建
查询某个表对应的索引信息：

highgo=# select * from pg_indexes where tablename = 'test01'; 

 schemaname | tablename |   indexname   | tablespace |                           indexdef                           

------------+-----------+---------------+------------+--------------------------------------------------------------

 public     | test01    | idx_test01_id |            | CREATE INDEX idx_test01_id ON public.test01 USING btree (id)

(1 row)

删除目标索引：

highgo=# drop index idx_test01_id ;

DROP INDEX

重建新索引：

highgo=# create index idx_test01_id on test01(id);

CREATE INDEX

情景二：大量索引重建
如果是对整个表所有索引重建：

highgo=# reindex table test01 ;

REINDEX

highgo=# select * from pg_indexes where tablename = 'test01'; 

 schemaname | tablename |   indexname   | tablespace |                           indexdef                           

------------+-----------+---------------+------------+--------------------------------------------------------------

 public     | test01    | idx_test01_id |            | CREATE INDEX idx_test01_id ON public.test01 USING btree (id)

(1 row)

注意事项：如果该表索引为空，则reindex不会创建任何索引。

知识拓展：

REINDEX语句对象还可以是数据库和索引自身，即：

REINDEX DATABASE testdb;

REINDEX TABLE my_table;

REINDEX INDEX aa_pkey;

情景三：大量索引重建，且部分索引不重建
通过SQL拼接，批量展示索引删除语句：

with inx_name as ( select * from pg_indexes where indexname like '%xxxxx%' )

select  'drop index  ' ||  inx_name.indexname  || ';' from inx_name;

选中自己所需索引进行删除操作即可。

补充说明

重建索引可能会影响数据库的常规操作。通常PostgreSQL会锁定重建的表以防止写操作，并通过单次扫描表来执行整个索引构建。其他事务仍可以读取表，但如果它们尝试在表中插入、更新或删除行，它们将被阻止，直到索引重建完成。如果系统是实时生产数据库，这可能会产生严重影响。非常大的表可能需要几个小时才能编制索引，即使对于较小的表，索引重建也会锁定编写器，这些时间段对于生产系统来说是不可接受的。PostgreSQL支持以最少的写入锁定来重建索引。此方法通过指定REINDEX的CONCURRENTLY选项来调用。使用此选项时，PostgreSQL必须对需要重新生成的每个索引执行两次表扫描，并等待可能使用索引的所有现有事务的终止。此方法需要比标准索引重建更大的工作量，并且需要相当长的时间才能完成，因为它需要等待可能修改索引的未完成的事务。但是，由于它允许在重建索引时继续正常操作，此方法可用于在生产环境中重建索引。当然，重建索引所需的额外 CPU、内存和 I/O 负载可能会减慢其他操作的速度。

create index concurrently idx_test01_id on test01 (id);



reindex table concurrently test01;