Подтвердить что ты не робот

MySQL - как долго создавать индекс?

Может ли кто-нибудь сказать мне, как добавить ключевые значения в MySQL? Я имею 500 000 000 строк в базе данных, trans, с столбцами я (INT UNSIGNED), j (INT UNSIGNED), nu (DOUBLE), A (DOUBLE). Я пытаюсь индексировать столбец, например.

ALTER TABLE trans ADD KEY idx_A (A);

и я жду. Для таблицы из 14 000 000 строк потребовалось около 2 минут для выполнения на моем MacBook Pro, но всего пол миллиарда она занимает 15 часов и рассчитывает. Я что-то делаю неправильно, или я просто наивно отношусь к тому, как индексирование базы данных масштабируется с количеством строк?

4b9b3361

ОТВЕТЫ

Ответ 1

Есть несколько факторов, которые следует учитывать:

  • Сортировка - это операция N.log(N).
  • Сорт для строк 14M вполне может вписываться в основную память; сортировка с 500M строк, вероятно, не делает, поэтому сортировка разливается на диск, что значительно замедляет работу.

Поскольку коэффициент составляет около 30, номинальное время сортировки для большого набора данных будет порядка 50 раз - менее двух часов. Тем не менее, вам нужно 8 байтов на каждое значение данных и еще около 8 байтов служебных данных (это предположение - настройте на mySQL, если вы знаете больше о том, что он хранит в индексе). Итак, 14M × 16 ≈ 220 Мб основной памяти. Но 500M × 16 ≈ 8 ГБ основной памяти. Если у вашей машины не хватило столько памяти (и MySQL настроен на ее использование), тогда большая сортировка разливается на диск, и это объясняет большую часть времени.

Ответ 2

Во-первых, определение таблицы может иметь большое значение здесь. Если вам не нужны значения NULL в ваших столбцах, определите их NOT NULL. Это позволит сэкономить место в индексе и, предположительно, время при его создании.

CREATE TABLE x ( 
  i INTEGER UNSIGNED NOT NULL, 
  j INTEGER UNSIGNED NOT NULL, 
  nu DOUBLE NOT NULL, 
  A DOUBLE NOT NULL 
);

Что касается времени, необходимого для создания индексов, для этого требуется сканирование таблицы и будет отображаться как REPAIR BY SORTING. В вашем случае (например, массивный набор данных) вам будет проще создать новую таблицу с необходимыми индексами и вставить в нее данные, поскольку это позволит избежать операции REPAIR BY SORTING, так как индексы будут последовательно построены на вставке. Существует аналогичная концепция, описанная в в этой статье.

CREATE DATABASE trans_clone;
CREATE TABLE trans_clone.trans LIKE originalDB.trans;
ALTER TABLE trans_clone.trans ADD KEY idx_A (A);

Затем script вставить в куски (согласно статье) или сбросить данные с помощью MYSQLDUMP:

mysqldump originalDB trans  --extended-insert --skip-add-drop-table --no-create-db --no-create-info > originalDB .trans.sql
mysql trans_clone < originalDB .trans.sql

Это приведет к вставке данных, но не потребует перестройки индекса (индекс создается по мере ввода каждой строки) и должен выполняться намного быстрее.

Ответ 3

Из моего опыта: если аппаратное обеспечение справится с этим, индексирование больших таблиц с MySQL обычно масштабируется довольно линейно. Я пробовал его с таблицами около 100 000 000 строк до сих пор, но не на ноутбуке - в основном на сильных серверах.

Я предполагаю, что это зависит в основном от аппаратных факторов, от типа используемого вами механизма таблицы (MyIsam, INNO или любого другого) и немного, если в противном случае эта таблица используется. Когда я это делал, обычно использование диска сильно повышалось, в отличие от использования ЦП. Не уверен в жестких дисках MacBook, но я думаю, что они не самые быстрые.

Если у вас есть таблицы MyISAM, возможно, поближе посмотрите на индексные файлы в каталоге таблицы и посмотрите, как они меняются с течением времени.