Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 40
Когда я вижу такие запросы мне становится нехорошо :)
for($i=0; $i<10000000; $i++) {
…
INSERT INTO `some_table` SET field1 = ${a}, field2 = '${b}', field4 = '${c}'
…
}
Даже для показательных скриптов так писать нельзя, так как подает плохой пример, позволю себе вас поправить, надо делать так (я правда не знаю php, поэтому пример на «псевдоязыке», но суть от этого не меняется):
INSERT INTO `some_table` (field1, field2, field3) VALUES
for($i=0; $i<10000000; $i++) {
…
('$a', '$b', '$c')
…
}[, ]
1. Это один запрос, вместо 10 миллионов :)
2. Здесь не нужно делать DISABLE/ENABLE KEYS, так как в процессе запроса MySQL не будет обновлять индексы
for($i=0; $i<10000000; $i++) {
…
INSERT INTO `some_table` SET field1 = ${a}, field2 = '${b}', field4 = '${c}'
…
}
Даже для показательных скриптов так писать нельзя, так как подает плохой пример, позволю себе вас поправить, надо делать так (я правда не знаю php, поэтому пример на «псевдоязыке», но суть от этого не меняется):
INSERT INTO `some_table` (field1, field2, field3) VALUES
for($i=0; $i<10000000; $i++) {
…
('$a', '$b', '$c')
…
}[, ]
1. Это один запрос, вместо 10 миллионов :)
2. Здесь не нужно делать DISABLE/ENABLE KEYS, так как в процессе запроса MySQL не будет обновлять индексы
А здесь Вы не правы :-)
То, что Вы пишете, это конечно правильно, что нужно стараться выполнить один запрос в место 10 000 000, и это будет на несколько порядков быстрей, но есть одно небольшое НО.
Не помню точно, может кто-то меня поправит. Сколько там буфер для запроса у MySQL. Почему-то у меня в голове крутится 4 MB. Число не точное, могу ошибаться. Так вот, если вы попытаетесь сконкатенировать большой запрос, который по объему будет превышать это число, MySQL выдаст вам ошибку и не выполнит ничего.
То, что Вы пишете, это конечно правильно, что нужно стараться выполнить один запрос в место 10 000 000, и это будет на несколько порядков быстрей, но есть одно небольшое НО.
Не помню точно, может кто-то меня поправит. Сколько там буфер для запроса у MySQL. Почему-то у меня в голове крутится 4 MB. Число не точное, могу ошибаться. Так вот, если вы попытаетесь сконкатенировать большой запрос, который по объему будет превышать это число, MySQL выдаст вам ошибку и не выполнит ничего.
Поправляю:
max_allowed_packet = 16M
в 16 мегабайт помещаются очень большие запросы.
max_allowed_packet = 16M
в 16 мегабайт помещаются очень большие запросы.
по умолчанию, конечно, меньше, но с таким молчанием большой проект из дампа, где однострочные инсерты не развернуть
Ну коль пошла такая «пьянка», тогда и упомяну про INSERT DELAYED, с помощью конструкции INSERT DELAYED, данные записываются в память, и попадают на диск, только тогда, когда таблица, в которую они должны записываться свободна от запросов на чтение. Соответственно, когда все инсерты будут писаться за раз, также будет оптимизирована работа с диском.
Возможны только проблемы при краше. Т.к. запросы заносятся в память, а также, то востановить их уже не удастся :-(
Возможны только проблемы при краше. Т.к. запросы заносятся в память, а также, то востановить их уже не удастся :-(
Ок, если 16, тогда признаю, что не прав. В 16 МБ даже вставка на 10 000 000 миллионов поместиться :-) Когда писал, почему-то думал о 4х.
По умолчанию он вообще один мегабайт :)
Но дело не в этом. Подобные бенчмарки не рассчитываются на то, что их будут делать на дешевом виртуальном хотинге, без доступа к конфигам сервера. Тем более, если такие запросы нужны реально, то сервер безусловно не должен работать в режиме по умолчанию. А если есть доступ к кофигам и нужны такие запросы — то поправить одну строчку max_allowed_packet = 16.32,64… — не проблема.
Важнее другое, так вообще делать нежелательно, существует специальный пакетный режим вставки — надо юзать его. Просто другие почитают — будут писать так и в реальной жизни. Даже вариант слить все в файл а потом сделать LOAD DATA INFILE — и то лучше.
Кстати если запросы еще и по сети идут, а не локально, например через ODBC драйвер — то разница в скорости будет просто ошеломительная между кучей INSERT-ов и INSERT… VALUES… Проверено на практике
Но дело не в этом. Подобные бенчмарки не рассчитываются на то, что их будут делать на дешевом виртуальном хотинге, без доступа к конфигам сервера. Тем более, если такие запросы нужны реально, то сервер безусловно не должен работать в режиме по умолчанию. А если есть доступ к кофигам и нужны такие запросы — то поправить одну строчку max_allowed_packet = 16.32,64… — не проблема.
Важнее другое, так вообще делать нежелательно, существует специальный пакетный режим вставки — надо юзать его. Просто другие почитают — будут писать так и в реальной жизни. Даже вариант слить все в файл а потом сделать LOAD DATA INFILE — и то лучше.
Кстати если запросы еще и по сети идут, а не локально, например через ODBC драйвер — то разница в скорости будет просто ошеломительная между кучей INSERT-ов и INSERT… VALUES… Проверено на практике
Можете кратко резюмировать, как лучше всего вставить 10 000 000 записей в таблицу? Я запутался в вашем споре ;)
Могу, вообще %maxshopen% прав относительно того, что большие объемы данных нужно вставлять за один запрос, а не каждая запись — новый запрос к БД.
Но если в среднем длинная записи, которую вы вставляете 100 байт (например, одно-два небольших varchar поля, пример с BLOB и TEXT я даже рассматривать не буду) и того у вас в итоге получится объем данных равный 100(байт) * 10 000 000 = 100MB. На самом деле Вам прийдется для этого изменить и параметр memory_limit в php.ini.
Поэтому я вам искренне советую не вставлять 10 000 000 записей в таблицу :-)
Но если в среднем длинная записи, которую вы вставляете 100 байт (например, одно-два небольших varchar поля, пример с BLOB и TEXT я даже рассматривать не буду) и того у вас в итоге получится объем данных равный 100(байт) * 10 000 000 = 100MB. На самом деле Вам прийдется для этого изменить и параметр memory_limit в php.ini.
Поэтому я вам искренне советую не вставлять 10 000 000 записей в таблицу :-)
Ну почему же, в такой вставке нет ничего страшного. Просто лучше в таком случае генерировать sql-код в файл, разбивая данные на достаточно большие insert-кусочки, чтобы каждый из них умещался в query-буфер и заливать целиком этот файл, хотя как показала практика — заливка csv с данными с помощью LOAD DATA INFILE работает в разы быстрее.
И снова спасибо за статью, лакончинчно и доступно. :)
И снова спасибо за статью, лакончинчно и доступно. :)
Тут еще выигрыш в том, что намного упрощается логика генерирования выходных данных, которые нужно залить в БД, плюс — на надо мучаться с размерами пакетов.
Ну да, правильно. Это и имелось в виду. Что нужно разбивать вставку на VALUES (...), (...), (....)
, скажем, по 10 000 инсертов. И выполнить 1 000 таких запросов :-). Ну или, через LOAD DATA INFILE, что в данном случае, пожалуй, будет работать быстрей.
только в этом в первом случае прийдется добавить еще проверку strlen, чтобы проверять не превысил ли наш запрос порог query-буфера. И как только он подошел к нему в плотную — запихивать в БД и составлять новый запрос заново…
, скажем, по 10 000 инсертов. И выполнить 1 000 таких запросов :-). Ну или, через LOAD DATA INFILE, что в данном случае, пожалуй, будет работать быстрей.
только в этом в первом случае прийдется добавить еще проверку strlen, чтобы проверять не превысил ли наш запрос порог query-буфера. И как только он подошел к нему в плотную — запихивать в БД и составлять новый запрос заново…
лучше запихивать в файл, чтобы не тормозить при генерации следующего пакета данных пока будет передаваться сделанный =)
LOAD DATA INFILE быстрее не будет. Самая медленные операции — дисковые, и тут их будет две, сначала запись в файл, потом запись в базу (для простоты считаем, что кэша нет). LOAD DATA хорош для переноса в базу очень больших данных, мы его используем для 8-10 гигабайтных таблиц и другим способом это не представляется возможным сделать.
В случае INSERT… VALUES… дисковая запись будет одна — только в файл таблицы базы данных.
На самом деле, мы уже не очень понятно что обсуждаем. Ситуация генерации тестовых данных — весьма редкое явление, для тестов можно выбрать любой способ (но не для написания статьи :-) ).
В реальности данные в большом количестве просто так не рождаются, они откуда то берутся, т.е. где то уже хранятся. Вот от этого и надо плясать. Если это лог-файлы пригодные для LOAD DATA — надо использовать его. Если это какие то данные требующие обработки, или находящиеся в другой БД то эффективнее и быстрее делать пакетную вставку. Если нужно перенести из одной базы mysql в другую — вообще можно обойтись INSERT INTO… SELECT FROM…
Ну а INSERT… SET… удобен для единичных записей
В случае INSERT… VALUES… дисковая запись будет одна — только в файл таблицы базы данных.
На самом деле, мы уже не очень понятно что обсуждаем. Ситуация генерации тестовых данных — весьма редкое явление, для тестов можно выбрать любой способ (но не для написания статьи :-) ).
В реальности данные в большом количестве просто так не рождаются, они откуда то берутся, т.е. где то уже хранятся. Вот от этого и надо плясать. Если это лог-файлы пригодные для LOAD DATA — надо использовать его. Если это какие то данные требующие обработки, или находящиеся в другой БД то эффективнее и быстрее делать пакетную вставку. Если нужно перенести из одной базы mysql в другую — вообще можно обойтись INSERT INTO… SELECT FROM…
Ну а INSERT… SET… удобен для единичных записей
Дабавлю офтопом, точней напомню (http://habrahabr.ru/blogs/php/38754/), что когда мы делаем конкатенацию в этом случае, то очень важно помнить, что её следует выполнять вот так
$sql .= «('$a', '$b', '$c'),»;
а не так
$sql = $sql.«('$a', '$b', '$c'),»;
Опять же… одно выделение памяти… два копирование… одно освобождение… и это Мегабайты данных… даже не смотря на то, что это оперативная память… хотя если несколько таких скриптов ворочают такие объемы данных в памяти, то не факт, что они не попадут в Своп… и тогда жизнь будет вообще не сахар…
$sql .= «('$a', '$b', '$c'),»;
а не так
$sql = $sql.«('$a', '$b', '$c'),»;
Опять же… одно выделение памяти… два копирование… одно освобождение… и это Мегабайты данных… даже не смотря на то, что это оперативная память… хотя если несколько таких скриптов ворочают такие объемы данных в памяти, то не факт, что они не попадут в Своп… и тогда жизнь будет вообще не сахар…
Советую посмотреть еще в сторону LOAD DATA INFILE:
Резюме в документации :)
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.
Резюме в документации :)
Вставляйте по 1000 записей, не сильно проиграете в скорости по сравнению со вставкой 10 000 000 и избавитесь от многих проблем
на самом деле проигрыш весьма ощутимый
Это проверялось как-либо или это на основе каких-то не озвученных умозаключений?
это проверялось на практике при вставке 800 с чем-то там тысяч записей…
но даже если просто логически подойти к этому вопросу, то получается 10 000 запросов, а это 10 000 разборов запроса, 10 000 — 1 ожиданий для каждого следующего запроса пока выполнится предыдущий, не говоря уже о затратах на межпроцессное взаимодействие при передаче данных.
но даже если просто логически подойти к этому вопросу, то получается 10 000 запросов, а это 10 000 разборов запроса, 10 000 — 1 ожиданий для каждого следующего запроса пока выполнится предыдущий, не говоря уже о затратах на межпроцессное взаимодействие при передаче данных.
То есть вы пробовали отключать перестроение индекса и импортировать записи блоками по 1000 и полученный результат сравнивали с экспортом в файл на диске и дальнейшим использованием LOAD DATA INFILE? Насколько велико было различие?
импортировались записи блоками разных размеров, 1000, 5000, 10000, 25000 и 50000, выходило, что чем меньше размер пакета данных, тем больше времени затрачивается на заливку данных.
Пробовалось как на InnoDB так и на MyISAM. На последнем при отключенной перестройке индексов скорость была выше, норазница сохранялась.
Точных цифр не скажу, так как было это больше полугода назад, но переход на генерацию sql-файла и его заливку вместо прямой передачи генерируемых данных в БД уменьшил время заливки данных в БД на порядок, а переход с sql-файла на CSV и LOAD DATA INFILE ещё на 30%.
Причём, чем больше необходимо залить данных, тем ощутимее становится разница.
Пробовалось как на InnoDB так и на MyISAM. На последнем при отключенной перестройке индексов скорость была выше, норазница сохранялась.
Точных цифр не скажу, так как было это больше полугода назад, но переход на генерацию sql-файла и его заливку вместо прямой передачи генерируемых данных в БД уменьшил время заливки данных в БД на порядок, а переход с sql-файла на CSV и LOAD DATA INFILE ещё на 30%.
Причём, чем больше необходимо залить данных, тем ощутимее становится разница.
Еще для аналитики полезно использовать команду
SHOW INDEX FROM `table`
Позволяет посмотреть cardinalility индексов в таблице, чтобы оценить будут они использоваться или нет.
SHOW INDEX FROM `table`
Позволяет посмотреть cardinalility индексов в таблице, чтобы оценить будут они использоваться или нет.
Чтобы не порождать лишнее число информации, приведу ссылку на то, что уже есть:
www.mysqlperformanceblog.com/2007/02/16/using-index-for-order-by-vs-restricting-number-of-rows/
If we force index as in second query explain will look scary with estimated million of rows to analyze but we got rid of filesort so MySQL can stop as soon as 10 rows are sent. In this case with seller_id being not really selective it is likely it will need to scan less than 100 rows to generate result.
Прочитал с удовольствием, как и предыдущие :)
а почему у меня индекс создается уже пол часа???
CREATE INDEX `fields1_2_3` ON `some_table` (field1, field2(8), `field3`);
ничего себе соптимизировал, боюсь и Ctrl+c нажать теперь, там живое что-нибудь останется после прерывания?
CREATE INDEX `fields1_2_3` ON `some_table` (field1, field2(8), `field3`);
ничего себе соптимизировал, боюсь и Ctrl+c нажать теперь, там живое что-нибудь останется после прерывания?
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
MySQL Performance real life Tips and Tricks. Part 3-rd.