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