插入7M行数据有无索引耗时差异大，如何实现插数禁用索引、查询可用？

核心逻辑是插入前先移除/禁用索引，插入完成后一次性重建索引，总耗时远低于带索引插入的耗时。原理是大数据量插入时，逐行维护索引结构的IO开销极高，而一次性构建索引的效率要高出数十倍。

各数据库具体操作 #

MySQL #

• 普通非主键索引：
  插入前删除索引：ALTER TABLE <表名> DROP INDEX <索引名>;
  数据插入完成后重建索引：ALTER TABLE <表名> ADD INDEX <索引名>(<字段名>);
• MyISAM引擎可直接禁用非唯一索引更新：
  插入前执行：ALTER TABLE <表名> DISABLE KEYS;
  插入完成后执行：ALTER TABLE <表名> ENABLE KEYS;

注意：主键索引无法删除，若插入数据按主键有序排列，可大幅降低主键索引的维护开销，建议导入前先对数据按主键排序。

PostgreSQL #

• 插入前删除索引：DROP INDEX <索引名>;
• 插入完成后重建索引：CREATE INDEX <索引名> ON <表名>(<字段名>);
• 也可临时标记索引为失效，插入后重建：
  标记失效：UPDATE pg_index SET indisvalid = false WHERE indexrelid = '<索引名>'::regclass;
  重建：REINDEX INDEX <索引名>;

Oracle #

• 插入前标记索引为不可用：ALTER INDEX <索引名> UNUSABLE;
• 插入完成后重建索引：ALTER INDEX <索引名> REBUILD;

注意事项 #

• 7M行数据的索引重建通常仅需数十秒，「插入+重建」的总耗时远低于带索引插入的数分钟
• 插入、重建索引期间，索引无法正常使用，查询会走全表扫描，建议在业务低峰期执行批量插入操作
• 重建唯一索引、主键索引时会校验数据唯一性，若插入数据存在重复值会导致重建失败，建议插入前先完成数据校验

内容的提问来源于stack exchange，提问作者banana_99