Индексы InnoDB до и после импорта

Question

Я пытаюсь импортировать большой файл SQL, который был сгенерирован mysqldump для таблицы InnoDB, но это занимает очень много времени даже после корректировки некоторых параметров в my.cnf и отключения AUTOCOMMIT (а также FOREIGN_KEY_CHECKS и UNIQUE_CHECKS, но таблица делает не иметь никаких внешних или уникальных ключей). Но мне интересно, если это займет так много времени из-за нескольких индексов в таблице.

Глядя на файл SQL, кажется, что индексы создаются в операторе CREATE TABLE до вставки всех данных. Основываясь на своем (ограниченном) исследовании и личном опыте, я обнаружил, что быстрее добавлять индексы после вставки всех данных. Не нужно ли проверять индексы для каждого INSERT? Я знаю, что mysqldump имеет --disable-keys опция, которая делает именно это - отключает ключи перед вставкой, но, видимо, это работает только с таблицами MyISAM, а не с InnoDB.

Но почему mysqldump не может включить ключи с оператором CREATE TABLE для таблиц InnoDB, а затем выполнить команду ALTER TABLE после вставки всех данных? Или InnoDB работает по-другому, и нет разницы в скорости?

Спасибо!

Answer 1

Я немного поэкспериментировал с этой концепцией на прошлой работе, где нам нужен был быстрый метод копирования схем между серверами MySQL.

Действительно, при вставке в таблицы с вторичными индексами снижается производительность. Вставкам необходимо обновить кластерный индекс (он же таблица), а также обновить вторичные индексы. Чем больше индексов в таблице, тем больше накладных расходов для вставок.

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

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

Percona Server, филиал MySQL, экспериментировал с mysqldump --optimize-keys вариант. Когда вы используете эту опцию, она изменяет вывод mysqldump на CREATE TABLE без индексов, затем вставляет все данные, затем ALTER TABLE, чтобы добавить индексы после загрузки данных. См. https://www.percona.com/doc/percona-server/LATEST/management/innodb_expanded_fast_index_creation.html

Но по моему опыту, чистое улучшение производительности было небольшим. Для вставки большого количества строк по-прежнему требуется некоторое время, даже для таблиц без индексов. Затем для восстановления необходимо запустить команду ALTER TABLE для построения индексов. Это займет некоторое время для большого стола. Когда вы подсчитываете время INSERT и дополнительное время для построения индексов, это всего лишь на несколько процентов (с низким однозначным числом) быстрее, чем при вставке традиционного способа в таблицу с индексами.

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

Я обнаружил, что гораздо эффективнее восстанавливать производительность, загружая несколько таблиц параллельно.

• Новый инструмент MySQL 8.0 mysqlpump поддерживает многопоточный дамп.
• Инструмент с открытым исходным кодом mydumper поддерживает многопоточный дамп, а также имеет многопоточный инструмент восстановления, который называется myloader, Наихудшим недостатком mydumper / myloader является то, что документация практически не существует, поэтому вам нужно быть бесстрашным опытным пользователем, чтобы понять, как его запустить.

Другая стратегия заключается в использовании mysqldump --tab создавать дампы файлов CSV вместо сценариев SQL. Массовая загрузка CSV-файлов выполняется намного быстрее, чем выполнение сценариев SQL для восстановления данных. Ну, это дамп файла SQL для определения таблицы и CSV для данных для импорта. Он создает отдельные файлы для каждой таблицы. Вы должны вручную воссоздать таблицы, загрузив все файлы SQL (это быстро), а затем использовать mysqlimport для загрузки файлов данных CSV. Инструмент mysqlimport даже имеет --use-threads вариант для параллельного исполнения.

Тщательно тестируйте с разным количеством параллельных нитей. Мой опыт показывает, что 4 темы являются лучшими. С большим параллелизмом InnoDB становится узким местом. Но ваш опыт может отличаться в зависимости от версии MySQL и производительности вашего серверного оборудования.

Самый быстрый метод восстановления из всех, когда вы используете физическое средство резервного копирования, наиболее популярным является Percona XtraBackup. Это обеспечивает быстрое резервное копирование и даже более быстрое восстановление. Файлы резервных копий буквально готовы к копированию на место и используются в качестве живых файлов табличного пространства. Недостатком является то, что вы должны закрыть свой MySQL Server, чтобы выполнить восстановление.