Mysql: создать индекс на 1,4 миллиарда записей
У меня есть таблица с 1,4 миллиарда записей. Структура таблицы следующая:
CREATE TABLE text_page (
text VARCHAR(255),
page_id INT UNSIGNED
) ENGINE=MYISAM DEFAULT CHARSET=ascii
Требуется создать индекс по столбцу text,
Размер стола составляет около 34G.
Я попытался создать индекс с помощью следующего утверждения:
ALTER TABLE text_page ADD KEY ix_text (text)
После 10 часов ожидания я наконец отказался от этого подхода.
Есть ли реальное решение по этой проблеме?
ОБНОВЛЕНИЕ: таблица вряд ли будет обновлена или вставлена или удалена. Причина, по которой нужно создать индекс по столбцу text потому что этот вид SQL-запроса будет часто выполняться:
SELECT page_id FROM text_page WHERE text = ?
ОБНОВЛЕНИЕ: я решил проблему, разделив таблицу.
Стол разбит на 40 шт. По колонке text, Затем создание индекса для таблицы занимает около 1 часа.
Кажется, что создание индекса MySQL становится очень медленным, когда размер таблицы становится очень большим. А разбиение сокращает таблицу на меньшие стволы.
7 ответов
Может быть, ваша система просто не подходит для этой задачи? Я не использую MySQL (здесь SQL Server), но я знаю, как индексировать таблицу с 800 миллионами записей. В основном.... вам нужно подходящее оборудование для этого (как в: много быстрых дисков). Сейчас я использую почти дюжину велоцирапторов, и производительность великолепна;)
Серверы SQL (не как MS SQL Server, а как серверы баз данных, использующие SQL) живут и умирают с доступом к диску, а обычные диски просто не подходят для более крупных операций.
Я решил проблему, разделив таблицу.
Стол разбит на 40 шт. По колонке text, Затем создание индекса для таблицы занимает около 1 часа.
Кажется, что создание индекса MySQL становится очень медленным, когда размер таблицы становится очень большим. А разбиение сокращает таблицу на меньшие стволы.
Возможно, вы захотите создать индекс по первым (например, 10) символам текстового поля.
Из документов:
Могут быть созданы индексы, которые используют только начальную часть значений столбца, используя синтаксис col_name(length) для указания длины префикса индекса:
CREATE INDEX ix_text ON text_page (text(10))
Установите sort_buffer_size на 4 ГБ (или столько, сколько сможете, в зависимости от того, сколько у вас памяти).
Прямо сейчас создание индекса выполняет сортировку, но, поскольку у вас есть 32 МБ sort_buffer_size, в основном он без необходимости перебивает жесткий диск.
Если вам не нужно делать запросы, такие как:
SELECT page_id FROM text_page WHERE text LIKE '?%';
Я бы предложил создать новый столбец хеша и индексировать таблицу по столбцу. Общий размер таблицы + индекс может быть намного меньше.
UPD: Кстати, 1,4 миллиарда целых чисел первичного ключа занимают около 6 ГБ, то есть средняя длина строки составляет менее 30 символов, то есть индексирование по префиксу может быть более предпочтительным.
Вы также должны взглянуть на механизм хранения MERGE.
Один из способов сделать это - создать новую таблицу с установленным индексом и скопировать данные в новую таблицу.
Также убедитесь, что у вас достаточно временного пространства.
Если вам все еще интересно, как это сделать лучше всего, я бы посоветовал вам использовать онлайн-инструмент для изменения таблицы.
В интернете их много, одним из известных являются:
- Изменение схемы Percona онлайн (я использую это)
http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html - Facebook OSC, я не могу найти URL, где вы можете скачать его
У нас те же проблемы с большими таблицами (более 500 миллионов записей), и изменения идут отлично. Он создает новую таблицу tmp, добавляет триггер к исходной таблице (для новых записей обновления / удаления / вставки) и в то же время копирует все записи в новую таблицу (с новой структурой)
Удачи!