MySQL - сколько времени создать индекс?
Кто-либо может сказать мне, как добавление ключа масштабируется в MySQL? У меня есть 500 000 000 строк в базе данных, сделке, со столбцами i (НЕПОДПИСАННЫЙ INT), j (НЕПОДПИСАННЫЙ INT), ню (ДВАЖДЫ), (ДВОЙНОЙ). Я пытаюсь индексировать столбец, например.
ALTER TABLE trans ADD KEY idx_A (A);
и я ожидаю. Для таблицы 14 000 000 строк потребовалось приблизительно 2 минуты для выполнения на моем MacBook Pro, но для целых пятисот миллионов, требуется 15 часов и подсчет. Я делаю что-то не так, или я просто наивен о том, как индексация базы данных масштабируется с количеством строк?
3 ответа
Необходимо учитывать несколько факторов:
- Сортировка - это операция N.log (N).
- Сортировка для 14 миллионов строк вполне может уместиться в основной памяти; сортировка с 500M строками, вероятно, этого не делает, поэтому сортировка переносится на диск, что сильно замедляет работу.
Поскольку размер фактора составляет около 30, номинальное время сортировки для набора больших данных будет примерно в 50 раз больше - менее двух часов. Однако вам нужно 8 байтов на значение данных и еще около 8 байтов накладных расходов (это предположение - настройтесь на mySQL, если вы знаете больше о том, что он хранит в индексе). Итак, 14M × 16 ≈ 220 МБ оперативной памяти. Но 500M × 16 ≈ 8 ГБ основной памяти. Если у вашей машины нет такого количества свободной памяти (и MySQL не настроен для ее использования), тогда большая сортировка перетекает на диск, и это составляет большую часть остального времени.
Из моего опыта: если оборудование может справиться с этим, индексация больших таблиц с MySQL обычно масштабируется довольно линейно. Я пробовал это до сих пор с таблицами примерно из 100000000 строк, но не на ноутбуке - в основном на сильных серверах.
Я предполагаю, что это зависит в основном от аппаратных факторов, типа используемого вами движка таблиц (MyIsam, INNO или что-то еще) и немного от того, используется ли таблица в промежутке между ними. Когда я делал это, обычно использование диска подскакивало до небес, в отличие от загрузки процессора. Не уверен насчет жестких дисков MacBook, но думаю, они не самые быстрые.
Если у вас есть таблицы MyISAM, возможно, присмотритесь к индексным файлам в каталоге таблиц и посмотрите, как они меняются с течением времени.
Во-первых, определение вашей таблицы может иметь здесь большое значение. Если вам не нужны значения 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 , поскольку индексы строятся последовательно на вставке. Подобная концепция объясняется в этой статье .
CREATE DATABASE trans_clone;
CREATE TABLE trans_clone.trans LIKE originalDB.trans;
ALTER TABLE trans_clone.trans ADD KEY idx_A (A);
Затем скрипт вставки в куски (согласно статье) или дамп данных с помощью 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
Это вставит данные, но не потребует перестроения индекса (индекс построен по мере вставки каждой строки) и должен выполняться намного быстрее.