В этой статье представлены стратегии MySQL по повышению эффективности операций загрузки данных. Часто вас беспокоит оптимизация запросов SELECT, поскольку они являются наиболее часто используемыми запросами, и определить, как их оптимизировать, не всегда просто. Загрузка данных в базу данных относительно проста. Часто вас беспокоит оптимизация запросов SELECT, поскольку они являются наиболее часто используемыми запросами, и определить, как их оптимизировать, не всегда просто. Загрузка данных в базу данных относительно проста. Однако существуют стратегии, которые можно использовать для повышения эффективности операций загрузки данных, основные принципы которых заключаются в следующем:
Массовая загрузка выполняется быстрее, чем загрузка одной строки, поскольку кэш индекса не нужно очищать после загрузки каждой записи; его можно очистить после загрузки пакета записей.
Загрузка таблицы без индекса происходит быстрее, чем загрузка после индекса. Если есть индексы, в файлы данных необходимо не только добавлять записи, но и каждый индекс должен быть изменен, чтобы отразить добавление новых записей.
Более короткие операторы SQL выполняются быстрее, чем более длинные, поскольку они требуют меньше анализа на стороне сервера и потому что их быстрее пересылать по сети от клиента к серверу. Некоторые из этих факторов могут показаться тривиальными (особенно последний), но если вы загружаете большой объем данных, даже небольшие факторы могут существенно повлиять на результаты. Мы можем использовать приведенные выше общие принципы, чтобы сделать несколько практических выводов о том, как загружать данные быстрее всего:
LOAD DATA (во всех его формах) более эффективен, чем INSERT, поскольку он загружает строки пакетами. Обновляется индекс меньше, и серверу приходится анализировать и интерпретировать только один оператор вместо нескольких.
ЗАГРУЗИТЬ ДАННЫЕ более эффективно, чем ЗАГРУЗИТЬ ДАННЫЕ ЛОКАЛЬНО. При использовании LOAD DATA файл должен находиться на сервере и иметь разрешения FILE, но сервер может читать файл непосредственно с диска. При использовании LOAD DATA LOCAL клиент читает файл и отправляет его по сети на сервер, что происходит медленно.
Если вам необходимо использовать INSERT, вам следует использовать форму, которая позволяет указать несколько строк в одном операторе, например:
Чем больше строк вы можете указать в операторе, тем лучше. Это уменьшает количество требуемых операторов и уменьшает количество обновлений индекса. Если вы используете mysqldump для создания файла резервной копии базы данных, вам следует использовать опцию --extended-insert, чтобы файл дампа содержал многострочные инструкции INSERT. Вы также можете использовать --opt (оптимизация), который включает опцию --extended-insert. И наоборот, следует избегать использования опции --complete-insert в mysqldump; эта опция приводит к тому, что инструкции INSERT будут однострочными, будут выполняться дольше и требовать большего анализа, чем инструкции, созданные без опции --complete-insert.
Используйте сжатые протоколы клиент/сервер для уменьшения сетевого трафика данных. Для большинства клиентов MySQL это можно указать с помощью параметра командной строки --compress. Обычно он используется только в медленных сетях, поскольку сжатие требует много процессорного времени.
Разрешите MySQL вставлять значения по умолчанию; не указывайте в инструкции INSERT столбцы, которым каким-либо образом будут присвоены значения по умолчанию. В среднем это приводит к сокращению операторов и уменьшению количества символов, отправляемых по сети на сервер. Кроме того, операторы, содержащие меньше значений, требуют меньшего анализа и преобразования со стороны сервера.
Если таблица индексирована, вы можете использовать массовые вставки (LOAD DATA или многострочные операторы INSERT), чтобы уменьшить накладные расходы на индекс. Это сводит к минимуму влияние обновлений индекса, поскольку индекс необходимо обновлять только после обработки всех строк, а не после каждой строки.
Если вам нужно загрузить большой объем данных в новую таблицу, вам следует создать таблицу и загружать ее, когда она не проиндексирована, а затем создать индекс после загрузки данных. Это быстрее. Создание индекса один раз (вместо изменения его один раз для каждой строки) происходит быстрее.
Если вы удалите или отключите индекс перед загрузкой, повторное создание или включение индекса после загрузки данных может ускорить загрузку. Если вы хотите использовать стратегию удаления или отключения для загрузки данных, обязательно проведите несколько экспериментов, чтобы понять, стоит ли оно того (если вы загружаете небольшой объем данных в большую таблицу, перестроение и индексирование могут занять больше времени, чем загрузка). данные)).
DROP INDEX и CREATE INDEX можно использовать для удаления и перестроения индексов. Альтернативой является отключение и включение индексов с помощью myisamchk или isamchk. Для этого требуется учетная запись на хосте сервера MySQL с доступом на запись к файлам таблиц. Чтобы отключить индексы таблиц, войдите в соответствующий каталог базы данных и выполните одну из следующих команд:
Используйте myisamchk для таблиц MyISAM с индексными файлами с расширением .MYI и isamchk для таблиц ISAM с индексными файлами с расширением .ISM. После загрузки данных в таблицу активируйте индекс следующим образом:
Если вы решите использовать отключение и активацию индекса, вам следует использовать протокол блокировки восстановления таблицы, описанный в главе 13, чтобы предотвратить одновременную смену блокировок сервером (хотя таблица в это время не восстанавливается, она модифицируется как таблица процесс восстановления, поэтому вам необходимо использовать тот же протокол блокировки).
Описанные выше принципы загрузки данных также применимы к фиксированным запросам, связанным с клиентами, которым необходимо выполнять различные операции. Например, обычно нежелательно выполнять длинные запросы SELECT к часто обновляемым таблицам. Длительное выполнение запросов SELECT может создать множество конфликтов и снизить производительность записи. Одно из возможных решений — сначала сохранить записи во временной таблице, а затем периодически добавлять записи в основную таблицу, если записи в основном выполняются операциями INSERT. Это неосуществимый подход, если требуется немедленный доступ к новым записям. Но этот метод можно использовать до тех пор, пока к ним не будет доступа в течение короткого периода времени. Использование временных таблиц имеет два преимущества. Во-первых, это уменьшает конфликты с оператором запроса SELECT в основной таблице и, следовательно, выполняется быстрее. Во-вторых, общее время загрузки записей из временной таблицы в основную меньше, чем общее время загрузки записей по отдельности; соответствующий кэш индекса необходимо обновлять только в конце каждой пакетной загрузки, а не после каждой строки. нагрузка. Одним из применений этой стратегии является доступ к базе данных MySQL с веб-страницы веб-сервера. В этом сценарии может не быть более высокого уровня полномочий, гарантирующего немедленный ввод записи в основную таблицу.
Если данные не являются той единственной записью, которая будет вставлена в случае выключения системы, другая стратегия уменьшения обновления индекса — использовать опцию создания таблицы DELAYED_KEY_WRITE для таблиц MyISAM (что может быть возможно, если MySQL используется для некоторая работа по вводу данных). Это произойдет). Эта опция приводит к тому, что кэш индекса обновляется только время от времени, а не после каждой вставки.
Если вы хотите воспользоваться преимуществами отложенного обновления индекса на уровне всего сервера, просто запустите mysqld с опцией --delayed-key-write. В этом сценарии запись индексного блока задерживается до тех пор, пока блок не будет очищен, чтобы освободить место для других значений индекса, до тех пор, пока не будет выполнена команда сброса таблиц или пока индексная таблица не будет закрыта.
-