У меня есть таблица из примерно 100M строк, которую я собираюсь скопировать для изменения, добавив индекс. Я не очень беспокоюсь о времени, которое требуется для создания новой таблицы, но будет ли созданный индекс более эффективным, если я изменю таблицу перед тем, как вставлять какие-либо данные или сначала вставлять данные, а затем добавлять индекс?
Лучше ли создавать индекс перед заполнением таблицы данными или после того, как данные будут на месте?
Ответ 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 секунд (более чем в два раза медленнее)
Ответ 2
Вероятно, лучше создать индекс после добавления строк. Это будет не только быстрее, но и балансировка дерева, вероятно, будет лучше.
Изменить "балансировка", вероятно, не самый лучший выбор здесь. В случае b-дерева он сбалансирован по определению. Но это не означает, что b-дерево имеет оптимальную компоновку. Распределение дочерних node внутри родителей может быть неравномерным (что приведет к увеличению затрат в будущих обновлениях), а глубина дерева может оказаться более глубокой, чем необходимо, если балансировка не будет выполняться тщательно во время обновлений. Если индекс создается после добавления строк, у него, скорее всего, будет лучшее распределение. Кроме того, индексные страницы на диске могут иметь меньшую фрагментацию после создания индекса. Немного больше информации здесь
Ответ 3
Это не имеет значения для этой проблемы, потому что:
- Если вы сначала добавите данные в таблицу, а после этого добавьте индекс. Время генерации индекса будет
O(n*log(N))
дольше (гдеn
- это добавленные строки). Поскольку время сортировки дереваO(n*log(N))
, тогда, если вы разделите это на старые данные и новые данные, вы получитеO((X+n)*log(N))
, это можно просто преобразовать вO(X*log(N) + n*log(N))
, и в этом формате вы можете просто увидеть, что вы будете ждать дополнительно. - Если вы добавите индекс и после него поместите данные. Каждая строка (у вас есть
n
новые строки), вы получаете больше времени вставки дополнительного времениO(log(N))
, необходимого для восстановления структуры дерева после добавления в него нового элемента (индексный столбец из новой строки, потому что индекс уже существует, а новая строка добавлена индекс должен быть восстановлен до сбалансированной структуры, эта стоимостьO(log(P))
, гдеP
- мощность индекса [элементы в индексе]). У вас естьn
новые строки, и, наконец, у вас естьn * O(log(N))
, а затемO(n*log(N))
сводка дополнительного времени.
Ответ 4
В большинстве случаев индексы, созданные после этого, намного быстрее. Пример: 20 миллионов строк с полным текстом на varchar (255) - (название компании) Индекс на месте при импорте строк - совпадение с занятием до 20 секунд в худших случаях. Индекс падения и воссоздание - совпадение с тем, чтобы принимать менее 1 секунды каждый раз
Ответ 5
Я не уверен, что это действительно важно для эффективности индекса, так как в обоих случаях вы вставляете новые данные в индекс. Сервер не знал бы, как неуравновешенный индекс будет до тех пор, пока он не будет построен, в основном. Скоростной, очевидно, делать вставки без индекса.