ANBOB™

在关系型数据库中，索引是SQL查询性能优化的常用技术，mysql可能不会创建太大的数据库，但是pg和oracle表达到100G或TB以上时，慢查询可能会比较明显，需要考虑创建适当的索引，但随着时间的推移，索引可能需要一些维护来保持性能. 通常当涉及到表时更新时，Oracle管理UNDO的方式和PostgreSQL中的MVCC工作方式略有不同，然而索引仍然可能碎片化，因此需要重新构建。

为什么要rebuild index 在postgresql和Oracle?

1，索引corrupted

当oracle因为bug或硬件写丢失等原因可能会导致索引产生损坏，导致索引与表不一致或无法使用。

2， 索引膨胀

在oracle中在做了大量delete或update后索引条目“空洞”碎片化，包括表上的碎片化，同样需要重构数据或重建索引，oracle数据库的online 选项的维护功能非常丰富，table对象可以move online对表及索引的在线重构， index对象可以使用alter index xx rebuild online仅对索引在线重构。生产环境中常使用online选项减少对在线业务的影响。

在PG中的非inplace更新方式导致更容易产生索引快速增长，Vacuum(和autovacuum)将清除删除的(和更新的)条目，但不会重新组合大部分为空的页面。因此，随着时间的推移，索引的大小会增加，而效率会降低:这种效应通常被称为膨胀。您可以监视膨胀的迹象，也可以在查询计划中看到线索(当您使用BUFFERS时)，上一篇有提到《PostgreSQL explain解析(一): buffers》。

在postgresql中，要完全消除索引中的膨胀，可以使用REINDEX。在Postgres 12中，我们有非常有用的REINDEX concurrent 减少对于写的影响，但是如果它失败了，你需要小心，因为它会留下无效的索引。Pg_repack和pg_squeeze是涉及最小锁定的其他选项对表数据或索引的重构。

3， 索引失效

在Oracle中如表做了move 、split partition等DDL操作导致index key记录的ROWID变化时，会导致相关索引invalid，导致索引无法使用, 此时需要重建索引，重建索引online中止还可能遇到Oracle-8104错误。

在postgresql中表上DDL不会产生索引失效，但是在如果在重新构建索引时出现了问题，例如唯一索引中违反了惟一性，REINDEX命令将失败， 会在原有索引之外留下一个“invalid”的新索引（pg_index.indisvalid确认），和在  psql \d command也可以查看索引的状态，如果invalid索引名后缀为ccnew可以删除该索引，再次执行REINDEX CONCURRENTLY，如果后缀是cold表示索引已重建完成，cold对应的是原索引，删除即可。

重构在PostgreSQL和Oracle不同之处

oracle rebuild index 支持

• ·Online Index Rebuild
• ·Normal Index Rebuild

Postgresql reindex 支持

• ·Reindex CONCURRENTLY
• ·Reindex Normal

oracle自带的alter table move，postgresql中使用第三方插件pg_repack。oracle中rebuild index只可以单个索引,在postgresql中REINDEX除了索引粒度，还有TABLE | SCHEMA | DATABASE | SYSTEM 更大粒度或系统表索引。

postgresql中重建 index normal有点像drop 、create index, 数据是全表扫描table 数据，使用REINDEX命令，REINDEX锁定索引父表的写操作，但不锁定读操作。它还接受正在处理的特定索引上的ACCESS EXCLUSIVE锁，该锁将阻止试图使用该索引的读操作。特别是，查询计划器尝试在表的每个索引上使用ACCESS SHARE锁，而不管查询是什么，因此REINDEX实际上阻塞了任何查询，除了一些计划已被缓存的准备好的查询。相反，DROP INDEX暂时在父表上使用ACCESS EXCLUSIVE锁，阻塞写入和读取。随后的CREATE INDEX锁定写操作，但不锁定读操作;由于索引不存在，所以没有读操作会尝试使用它，这意味着不会出现阻塞，但读操作可能会被迫进行代价高昂的顺序扫描。而Oracle在rebuild index过程中会堵塞写，但是rebuild期间t使用该索引的selec不会被堵塞。

通常情况下，PostgreSQL会锁定在写时重新构建索引的表，如果系统是活动的生产数据库，这可能会产生严重的影响。非常大的表可能需要花费许多小时来建立索引，可以增加CONCURRENTLY在生产系统中类似oracle online，最小化锁请求，PostgreSQL必须对需要重建的每个索引执行两次表扫描，并等待所有可能使用该索引的现有事务的终止 ，重建索引时间会增加 ；而oracle中alter index rebuild 是读index 数据，而alter index rebuild online是读table 数据，所以当索引有miss key逻辑corrupted时加online选项重建。

为了执行（稍慢的）索引创建，Postgres将执行以下操作：

• 1,扫描一次表以构建索引;
• 2,对自第一次传递以来添加或更新的内容再次运行索引。

如果在两个阶段之间创建的是create unique index concurrently, insert一条重复记录，违背了唯一特性将会出现index 无效状态.可以通过运行以下查询来查找所有无效索引：

 SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

Postgresql中每个运行REINDEX的后端将在pg_stat_progress_create_index视图中报告其进度，Oracle中可以从v$session_longops查看进度。

Oracle要求您拥有企业许可证才能在线重建索引。而PostgreSQL允许我们在线重建索引，而不需要任何许可证，因为它是开源的。

打赏

微信扫一扫，打赏作者吧～