在用数据填充表之前还是在数据到位之后创建索引更好?

本文介绍了在用数据填充表之前还是在数据到位之后创建索引更好?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个包含大约 100M 行的表，我将复制该表以进行更改，并添加一个索引.我不太关心创建新表所需的时间，但是如果我在插入任何数据之前更改表或先插入数据然后添加索引，创建的索引是否会更有效?

I have a table of about 100M rows that I am going to copy to alter, adding an index. I'm not so concerned with the time it takes to create the new table, but will the created index be more efficient if I alter the table before inserting any data or insert the data first and then add the index?

推荐答案

数据插入后创建索引是更有效的方式(甚至经常推荐在批量导入前删除索引，导入后重新创建).

Creating index after data insert is more efficient way (it even often recomended to drop index before batch import and after import recreate it).

合成示例(PostgreSQL 9.1，缓慢的开发机器，一百万行):

Syntetic example (PostgreSQL 9.1, slow development machine, one million rows):

CREATE TABLE test1(id serial, x integer); INSERT INTO test1(id, x) SELECT x.id, x.id*100 FROM generate_series(1,1000000) AS x(id); -- Time: 7816.561 ms CREATE INDEX test1_x ON test1 (x); -- Time: 4183.614 ms

插入然后创建索引 - 大约 12 秒

Insert and then create index - about 12 sec

CREATE TABLE test2(id serial, x integer); CREATE INDEX test2_x ON test2 (x); -- Time: 2.315 ms INSERT INTO test2(id, x) SELECT x.id, x.id*100 FROM generate_series(1,1000000) AS x(id); -- Time: 25399.460 ms

创建索引然后插入 - 大约 25.5 秒(慢两倍多)

Create index and then insert - about 25.5 sec (more than two times slower)