Лучше создать индекс перед заполнением таблицы данными или после того, как данные на месте?

Question

У меня есть таблица около 100 миллионов строк, которые я собираюсь скопировать, чтобы изменить, добавив индекс. Меня не очень беспокоит время, необходимое для создания новой таблицы, но будет ли созданный индекс более эффективным, если я изменю таблицу перед вставкой каких-либо данных или сначала вставлю данные, а затем добавлю индекс?

Answer 1

Создание индекса после вставки данных является более эффективным способом (даже часто рекомендуется отбрасывать индекс перед пакетным импортом и после импорта пересоздать его).

Пример синтаксиса (PostgreSQL 9.1, машина медленной разработки, миллион строк):

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 секунд

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 секунд (более чем в два раза медленнее)

Answer 2

Вероятно, лучше создать индекс после добавления строк. Мало того, что это будет быстрее, но балансировка дерева, вероятно, будет лучше.

Редактировать "балансировка", вероятно, не лучший выбор терминов здесь. В случае b-дерева оно сбалансировано по определению. Но это не значит, что b-дерево имеет оптимальную компоновку. Распределение дочерних узлов в пределах родительского узла может быть неравномерным (что приведет к увеличению затрат в будущих обновлениях), и глубина дерева может оказаться глубже, чем необходимо, если во время обновлений балансировка не выполняется осторожно. Если индекс создается после добавления строк, он, скорее всего, будет иметь лучшее распределение. Кроме того, индексные страницы на диске могут иметь меньшую фрагментацию после построения индекса. Немного больше информации здесь

Answer 3

Это не имеет значения для этой проблемы, потому что:

1. Если вы сначала добавите данные в таблицу, а затем добавите индекс. Ваше время создания индекса будет O(n*log(N)) дольше (где n добавлены строки). Потому что время образования деревьев O(N*log(N)) затем, если вы разделите это на старые данные и новые данные, которые вы получите O((X+n)*log(N)) это может быть просто преобразовано в O(X*log(N) + n*log(N)) и в этом формате вы можете просто посмотреть, чего вы будете ждать дополнительно.
2. Если вы добавляете индекс и после него ставите данные. Каждый ряд (у вас есть n новые строки) вы получите больше вставить дополнительное время O(log(N)) необходимо восстановить структуру дерева после добавления в него нового элемента (индексный столбец из новой строки, поскольку индекс уже существует и добавлена ​​новая строка, затем индекс должен быть восстановлен до сбалансированной структуры, эта стоимость O(log(P)) где P является степенью индекса [элементы в индексе]). У тебя есть n новые строки, то, наконец, у вас есть n * O(log(N)) затем O(n*log(N)) Краткое дополнительное время.

Answer 4

Индексы, созданные после, в большинстве случаев работают намного быстрее. Пример: 20 миллионов строк с полным текстом на varchar(255) - (Business Name) Index на месте при импорте строк - совпадение с продолжительностью до 20 секунд в худшем случае. Удалить индекс и пересоздать - сопоставить с тем, чтобы занимать менее 1 секунды каждый раз

Answer 5

Я не уверен, что это действительно будет иметь значение для эффективности индекса, поскольку в обоих случаях вы вставляете новые данные в индекс. Сервер не будет знать, насколько неравномерным будет индекс, до тех пор, пока он не будет построен. По скорости, очевидно, делать вставки без указателя.