Прежде всего, когда дело доходит до импорта данных в MySQL, у вас есть несколько вариантов импорта данных:

  • Можно использовать INSERT INTO и указать имя таблицы вместе со столбцами и данными, которые необходимо импортировать в данный экземпляр базы данных.
  • Можно также использовать LOAD DATA INFILE и указать конкретный файл, из которого он или она хочет загрузить данные в данную таблицу.

Теперь мы могли бы сказать вам, что «нет, LOAD DATA INFILE — не единственный вариант при импорте данных в экземпляры на основе MySQL или MariaDB», и оставить все как есть, однако вы здесь не для этого — вы здесь, чтобы узнать лучший механизм для использования для импорта ваших данных в экземпляры на основе MySQL.

К настоящему моменту вы знаете, что LOAD DATA INFILE и INSERT INTO — ваши единственные варианты, однако вы также можете знать, что мы рассмотрели некоторые функции, предоставляемые LOAD DATA INFILE, в одном из наших предыдущих постов в блоге, поэтому вы можете задаться вопросом, почему мы пишем еще один. . Ну, мы еще не все рассмотрели!

Например, мы говорили вам, что LOAD DATA INFILE быстрее, и это так. Но если вам нужно быстро импортировать данные в экземпляры вашей базы данных, у вас также есть пара других вариантов. Например, вы можете использовать операторы COMMIT следующим образом:

SET autocommit=0;
INSERT INTO arctype (demo_column) VALUES (‘Demo Column’);
COMMIT;

Выполнение запросов указанным выше способом поможет вашей базе данных работать лучше при вставке данных, поскольку операторы COMMIT сохраняют текущее состояние базы данных. Другими словами, преобразование autocommit в значение 0, импорт ваших данных и последующая фиксация могут быть лучшим вариантом с точки зрения импорта данных в MySQL, потому что вы будете сохранять изменения на диск только тогда, когда захотите, и разгрузите MySQL (или MariaDB) таких неприятностей. Кроме того, рассмотрите возможность установки unique_checks равным 0 и foreign_key_checks равным 0: установка этих параметров в значение off (0) также должна улучшить производительность вашей базы данных.

Еще один быстрый способ импортировать данные в MySQL, если вы используете механизм хранения MyISAM, — это просто скопировать файлы .frm, .MYD и .MYI в заданную папку на другом сервере базы данных. Однако есть предостережение — вы должны делать это только с MyISAM, потому что InnoDB работает по-другому. Никогда не применяйте к нему такой подход, если вы полностью не осознаете последствия.

Вы можете прочитать нашу запись в блоге о InnoDB и больших данных, чтобы узнать больше, но, по сути, у InnoDB есть один основной файл — ibdata1 — который играет центральную роль в его производительности. Простое копирование таких файлов принесет больше вреда, чем пользы, поскольку идентификатор табличного пространства в ibdata1 не будет соответствовать идентификатору табличного пространства в файлах базы данных, скопированных на новый сервер.

Еще один быстрый способ ускорить импорт данных — заблокировать таблицу во время импорта. Запустите LOCK TABLES запрос перед импортом данных и UNLOCK TABLES, когда закончите, и все готово. Такой подход рекомендуется, если у вас есть ситуация, когда вам может потребоваться запретить определенным сеансам изменять таблицы.

Вы также можете использовать функцию массового импорта, предоставляемую операторами INSERT. INSERT INTO arctype (demo_column) VALUES (‘demo’), (‘demo’); будет вставляться быстрее, чем обычные операторы INSERT.

Однако это не единственные варианты, которые вы можете использовать. Если вы используете MyISAM (вы не должны этого делать, но если вы вынуждены использовать такой механизм хранения…), вы также можете подумать об увеличении значения bulk_insert_buffer_size. Согласно MySQL, переменная ограничивает размер дерева кеша в байтах на один поток, поэтому это должно помочь, если вы обнаружите, что импортируете довольно много данных при работе с MyISAM.

Соображение: переменная secure_file_priv

Переменная secure_file_priv тесно связана с массовым импортом данных внутри ваших экземпляров базы данных. В предыдущих сообщениях мы говорили, что LOAD DATA INFILE значительно быстрее, чем INSERT, из-за того, что он содержит множество так называемых «прибамбасов, уникальных для него самого». Частью этой магии является load_data_infile. Переменная обычно находится в вашем файле my.cnf (который сам находится в вашем каталоге /var/bin/mysql) и выглядит следующим образом (следующий пример относится к переменной, используемой в средах Windows):

secure_file_priv=”c:/wamp64/tmp”

Проще говоря, эта переменная накладывает ограничение на то, какие каталоги можно использовать для загрузки данных в ваш экземпляр базы данных MySQL. Другими словами, как только вы запустите запрос LOAD DATA INFILE, а файл, который вы загружаете в экземпляр вашей базы данных, не находится в этом каталоге, MySQL вернется с ошибкой, подобной этой:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Чтобы устранить эту ошибку, вам нужно либо удалить параметр --secure-file-priv из вашего файла my.cnf, либо загрузить данные из каталога, указанного в значении переменной. Как только вы это сделаете, ваши данные должны быть загружены (и экспортированы) без каких-либо проблем!

Другие проблемы

Помимо загрузки данных в ваши экземпляры базы данных MySQL с использованием LOAD DATA INFILE и использования этой привилегии, значение такой переменной также может иметь значение при экспорте данных из MySQL с использованием SELECT … INTO OUTFILE. эту переменную также можно использовать для отключения операций импорта и экспорта с использованием LOAD DATA INFILE и SELECT ... INTO OUTFILE: в таком случае просто установите для этой переменной значение NULL. Однако в целом вы также можете запустить запрос типа SHOW QUERY VARIABLES LIKE ‘secure_file_priv’ или SELECT @@secure_file_priv, чтобы узнать имя каталога, для которого установлена ​​эта переменная.

Время импортировать некоторые данные!

Итак, вы готовы импортировать некоторые данные. Можете ли вы просто использовать запросы INSERT и вставлять все строки одну за другой? Или, возможно, вы бы использовали возможности массовой вставки, предоставляемые INSERT?

Технически да — вы можете это сделать. Вы можете вставить свои строки из файла CSV, скопировав их в операторы INSERT, а затем запустив их один за другим, но это займет довольно много времени, также вам нужно убедиться, что вы копируете правильные значения строки из CSV-файла в ваш оператор INSERT и т. д. — это было бы довольно хлопотно!

К счастью, есть инструменты, которые могут помочь вам в выполнении таких задач. Некоторые из этих инструментов являются клиентами SQL. Например, клиент SQL, разработанный Arctype, может помочь вам редактировать ваши таблицы, такие как электронные таблицы, где вы выбираете любую ячейку, которую хотите редактировать, и удаляете строки, просто нажимая Удалить и т. д.

Однако у Arctype есть еще одна функция, которая должна иметь большее отношение к теме этой статьи — Arctype также позволяет вам импортировать CSV-файлы в ваши экземпляры базы данных. Давайте посмотрим, как это сделать!

Идем дальше и запускаем Arctype. Справа от стола и над его структурой вы найдете три горизонтальные точки. Нажмите на них:

Эти точки обозначают параметры, доступные для использования: вы можете либо создать таблицу, либо обновить их, либо вы также можете импортировать содержимое данного CSV-файла в свою таблицу. Нажмите Импорт CSV и выберите файл, который хотите импортировать:

Arctype предоставит вам некоторую информацию о вашем файле, а также предоставит первые пять строк, которые содержит ваш CSV-файл. Когда вы будете удовлетворены результатами, которые вы видите, пришло время импортировать ваши данные — продолжайте и нажмите Принять:

После того, как вы нажмете «Принять», вы сможете определить столбцы своей таблицы, в которые вы хотите импортировать свои данные, вы сможете создать новую таблицу или выбрать загрузку своих данных в существующую таблицу.

Когда вы будете удовлетворены тем, что видите на экране, нажмите Импортировать CSV, чтобы импортировать файл: теперь ваши данные должны существовать внутри ваших таблиц! Вот именно — это действительно так просто.

Иногда, когда ваши данные слишком длинные для импорта, и вы можете столкнуться с некоторыми ошибками, подобными приведенной выше (в этом случае, Arctype импортирует все строки, ведущие к ошибке), но в целом процесс импорта должен пройти гладко.

Нижняя линия

Когда вам нужно быстро импортировать данные в ваши экземпляры MySQL, LOAD DATA INFILE далеко не единственный вариант. Вы также можете использовать возможности массовой вставки, предоставляемые операторами INSERT, изменять функциональность MySQL таким образом, чтобы фиксация выполнялась только после полной загрузки данных в таблицы базы данных, и загружать данные только в определенные столбцы, используя также LOAD DATA INFILE.

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

Лукас — этичный хакер, администратор базы данных MySQL и частый докладчик на конференциях. С 2014 года Лукас обнаружил и ответственно раскрыл недостатки безопасности на некоторых из самых посещаемых веб-сайтов в Литве и за рубежом, включая рекламные, подарочные, игровые, хостинговые веб-сайты, а также некоторые веб-сайты государственных учреждений. Лукас управляет одной из самых больших и быстрых поисковых систем для взлома данных в мире: BreachDirectory.com и часто ведет блоги в нескольких местах, обучая людей информационной безопасности и другим темам. Он также ведет собственный блог на lukasvileikis.com.