Стратегии улучшения производительности и миграции данных в MySQL
Как позаботиться о производительности, когда нам нужно изменить столбец в таблице с миллиардами строк?
2 ответа
ОперацииDML в больших таблицах - действительно очень утомительная работа, требующая надлежащего анализа и хороших стратегий миграции при выполнении операций. Предположим, что в вашей базе данных MYSQL у вас есть гигантская таблица, содержащая 600 миллионов строк, с некоторыми операциями схемы, такими как добавление уникального ключа, изменение столбца, даже добавление в него еще одного столбца, - это очень громоздкий процесс, для обработки которого требуются часы и иногда происходит тайм-аут сервера. Чтобы преодолеть это, нужно придумать очень хороший план миграции, один из которых я описал ниже.
1) Предположим, есть таблица Orig_X, в которую я должен добавить новый столбец colNew со значением по умолчанию, равным 0.
2) Создается фиктивная таблица Dummy_X, которая является копией Orig_X, за исключением нового столбца colNew.
3) Данные вставляются из Orig_X в Dummy_X со следующими настройками.
4) Автоматическое принятие установлено на ноль, так что данные не фиксируются после каждого оператора вставки, препятствующего производительности.
5) Двоичные журналы установлены на ноль, так что данные не будут записываться в эти журналы.
6) После вставки данных бота функция устанавливается на единицу.
SET AUTOCOMMIT = 0;
SET sql_log_bin = 0;
Insert into Dummy_X(col1, col2, col3, colNew)
Select col1, col2, col3, from Orig_X;
SET sql_log_bin = 1;
SET AUTOCOMMIT = 1;
7) Теперь первичный ключ может быть создан с недавно вставленным столбцом, который теперь является частью первичного ключа.
8) Все уникальные ключи теперь могут быть созданы. 9) Мы можем проверить состояние сервера, выполнив следующую команду
SHOW MASTER STATUS
10) Также полезно выдавать FLUSH LOGS, чтобы MySQL очищал старые журналы.
11) Чтобы повысить производительность для выполнения запросов аналогичного типа, таких как приведенный выше оператор вставки, необходимо включить переменную кэша запросов.
SHOW VARIABLES LIKE 'have_query_cache';
query_cache_type = 1
Выше были шаги для стратегии миграции для большой таблицы, ниже я наблюдаю шаги для улучшения производительности базы данных / запросов. 1) Удалите все ненужные индексы в таблице, обратите особое внимание на УНИКАЛЬНЫЕ индексы, как они, когда отключите буферизацию изменений. Не используйте индекс UNIQUE, если у вас нет причин для этого ограничения, предпочитайте обычный INDEX.
2) При массовой загрузке новой таблицы отложите создание каких-либо индексов, кроме PRIMARY KEY. Если вы создадите их один раз после загрузки данных, то InnoDB сможет применить предварительную сортировку и процесс массовой загрузки, который быстрее и в результате приводит к более компактным индексам.
3) Больше памяти может реально помочь в оптимизации производительности. Если SHOW ENGINE INNODB STATUS показывает какие-либо операции чтения / чтения в буфере пула и памяти, а количество свободных буферов (также в буфере буфера и памяти) равно нулю, вы можете получить больше (если вы правильно настроили размер innodb_buffer_pool_size на своем сервере).
4) Обычно ваша таблица базы данных переиндексируется после каждой вставки. Это тяжелая работа для вашей базы данных, но когда ваши запросы помещаются в транзакцию, таблица не переиндексируется до тех пор, пока не будет обработана вся эта масса. Сохранение много работы.
5) На большинстве серверов MySQL включено кэширование запросов. Это один из наиболее эффективных методов повышения производительности, который незаметно обрабатывается ядром базы данных. Когда один и тот же запрос выполняется несколько раз, результат выбирается из кэша, что довольно быстро.
6) Использование ключевого слова EXPLAIN может дать вам представление о том, что делает MySQL для выполнения вашего запроса. Это может помочь вам определить узкие места и другие проблемы с вашим запросом или структурами таблиц. Результаты запроса EXPLAIN покажут, какие индексы используются, как сканируется и сортируется таблица и т. Д.
7) Если ваше приложение содержит много запросов JOIN, вам необходимо убедиться, что столбцы, к которым вы присоединяетесь, проиндексированы в обеих таблицах. Это влияет на то, как MySQL внутренне оптимизирует операцию соединения.
8) В каждой таблице есть столбец id, который является PRIMARY KEY, AUTO_INCREMENT и одним из видов INT. Также желательно UNSIGNED, поскольку значение не может быть отрицательным.
9) Даже если у вас есть таблица пользователя с уникальным полем имени пользователя, не делайте это своим основным ключом. Поля VARCHAR в качестве первичных ключей работают медленнее. И вы будете иметь лучшую структуру в своем коде, обращаясь ко всем пользователям с их внутренним идентификатором.
10) Обычно, когда вы выполняете запрос из скрипта, он будет ждать завершения этого запроса, прежде чем он сможет продолжить. Вы можете изменить это, используя небуферизованные запросы. Это экономит значительный объем памяти с запросами SQL, которые производят большие наборы результатов, и вы можете начать работать с набором результатов сразу же после извлечения первой строки, так как вам не нужно ждать, пока полный запрос SQL не будет выполнен.
11) С ядрами баз данных, диск, пожалуй, является наиболее существенным узким местом. Сокращение объема и компактность обычно помогают с точки зрения производительности уменьшить объем передаваемых данных.
12) Два основных механизма хранения в MySQL - это MyISAM и InnoDB. У каждого есть свои плюсы и минусы.MyISAM хорош для приложений с большим объемом чтения, но он не очень хорошо масштабируется при большом количестве записей. Даже если вы обновляете одно поле одной строки, вся таблица блокируется, и никакой другой процесс не может даже прочитать из нее, пока этот запрос не будет завершен.MyISAM очень быстро вычисляет типы запросов SELECT COUNT(*).InnoDB, как правило, является более сложным механизмом хранения и может работать медленнее, чем MyISAM, для большинства небольших приложений. Но он поддерживает блокировку на основе строк, которая лучше масштабируется. Он также поддерживает некоторые более сложные функции, такие как транзакции.