PHP.SU

Программирование на PHP, MySQL и другие веб-технологии

PHP.SU Портал

Главная

Помощь

Поиск

Поиск Яндекс Вакансии

Пользователи

Здравствуйте, Гость

( Вход · Регистрация · Правила форума )

Забыли пароль?

Максимальная скорость INSERT и UPDATE

Форумы портала PHP.SU » PHP » SQL и Архитектура БД (Модератор: SAD)

Страниц (2): [1] 2 »

Без описания

Поиск в теме | Версия для печати

White	Отправлено: 17 Октября, 2011 - 11:57:31
Частый посетитель Покинул форум Сообщений всего: 830 Дата рег-ции: Июнь 2011 Откуда: Днепропетровск Помог: 28 раз(а)	какие возможные пути увеличения скорости INSERT и UPDATE выражений в mysql? прочитал этот ман http://dev[dot]mysql[dot]com/doc/refman/[dot][dot][dot]ptimization[dot]html кое-какие выводы для себя сделал. хочу написать небольшой Active Record класс, беспокоюсь о скорости его работы. ----- if(time()>1356048000) die();

Champion	Отправлено: 17 Октября, 2011 - 15:34:31
Активный участник Покинул форум Сообщений всего: 4350 Дата рег-ции: Авг. 2008 Откуда: Москва Помог: 57 раз(а)	White пишет: увеличения скорости INSERT Чем меньше индексов - там скорее инсерт). Если инсерты происходят группами, то можно объединять их в порции 100-1000 инсертов в одну транзакцию. Это будет ощутимый прирост. Еще небольшой прирост будет если использовать prepare + execute. Для апдейтов это тоже справедливо +, по скольку в апдейте присутствует where, должна оптимизироваться скорость выборки - т.е. индексы тоже должны быть созданы удачно

White	Отправлено: 17 Октября, 2011 - 16:10:22
Частый посетитель Покинул форум Сообщений всего: 830 Дата рег-ции: Июнь 2011 Откуда: Днепропетровск Помог: 28 раз(а)	Champion а как насчет такой конструкции CODE (SQL): скопировать код в буфер обмена UPDATE `table` SET `value`= CASE `id` WHEN "0" THEN "value1" WHEN "1" THEN "value2" ... END WHERE `id` IN ("0","1") будет ли такой запрос быстрее, если требуется обновить > 100 строк, и будет ли это быстрее prepare + execute (выборка ведь делается один раз). ----- if(time()>1356048000) die();

Champion	Отправлено: 17 Октября, 2011 - 16:57:15
Активный участник Покинул форум Сообщений всего: 4350 Дата рег-ции: Авг. 2008 Откуда: Москва Помог: 57 раз(а)	Один запрос с prepare-execute нет смысла делать с точки зрения ускорения. Не важно, сколько строк, важно сколько запросов. А вообще update с case-ом - тоже вариант ускорения апдейта, потому что запрос всего один.

Мелкий	Отправлено: 17 Октября, 2011 - 17:03:16
Активный участник Покинул форум Сообщений всего: 11926 Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург Помог: 618 раз(а)	Champion пишет: А вообще update с case-ом - тоже вариант ускорения апдейта, потому что запрос всего один. Но стоит проверить, не окажется ли он медленнее нескольких простых в рамках mysqli::multi_query ----- PostgreSQL DBA

White	Отправлено: 17 Октября, 2011 - 17:07:55
Частый посетитель Покинул форум Сообщений всего: 830 Дата рег-ции: Июнь 2011 Откуда: Днепропетровск Помог: 28 раз(а)	Champion пишет: Один запрос с prepare-execute ну я не об одном запросе говорю. просто насколько я понимаю при prepare выигрыш по большей части идет за счет того, что компилятор проходится по запросу всего один раз, а при execute просто подставляет в "скомпилированный" запрос значения. по факту ведь количество запросов не уменьшается, уменьшается лишь время затраченное на компиляцию запросов. Возможно я не прав, поправьте если так. (Добавление) Мелкий пишет: Но стоит проверить, не окажется ли он медленнее нескольких простых в рамках mysqli::multi_query да, наверное стоит провести маленький тест, если результаты будут интересными отпишусь. П.С. единственная проблема, что такой вариант применим только в рамках mysqli ----- if(time()>1356048000) die();

Champion	Отправлено: 17 Октября, 2011 - 18:34:07
Активный участник Покинул форум Сообщений всего: 4350 Дата рег-ции: Авг. 2008 Откуда: Москва Помог: 57 раз(а)	White пишет: Возможно я не прав, поправьте если так. Всё правильно. Но комбинирование группы запросов в одну транзакцию дает еще гораздо больший прирост. Мелкий пишет: Но стоит проверить, не окажется ли он медленнее нескольких простых в рамках mysqli::multi_query Да, проверить конечно надо.

White	Отправлено: 17 Октября, 2011 - 20:06:14
Частый посетитель Покинул форум Сообщений всего: 830 Дата рег-ции: Июнь 2011 Откуда: Днепропетровск Помог: 28 раз(а)	Сделал небольшой тест на INSERT код теста: Спойлер (Отобразить) PHP: скопировать код в буфер обмена <?PHP $conn = mysqli_connect("localhost", "root", "crRrtphde"); mysqli_select_db($conn, "new_table"); $insert_statements = 1000; //Start tests //Insert test //Single statements $insert_start['single_statement'] = microtime(); for($i=0;$i<$insert_statements;$i++) { mysqli_query($conn, "INSERT INTO `test` (`name`,`value`) VALUES ('name_{$i}','value_{$i}')"); } $insert_end['single_statement'] = microtime(); //Prepare statements $insert_start['prepare_statement'] = microtime(); mysqli_query($conn, "PREPARE `insert1` FROM 'INSERT INTO `test` (`name`,`value`) VALUES (?,?)'"); for($i=0;$i<$insert_statements;$i++) { mysqli_query($conn, "SET @a = 'name_{$i}'"); mysqli_query($conn, "SET @b = 'value_{$i}'"); mysqli_query($conn, "EXECUTE `insert1` USING @a, @b"); } mysqli_query($conn, "DEALLOCATE PREPARE `insert1`"); $insert_end['prepare_statement'] = microtime(); //Long statement $insert_start['long_statement'] = microtime(); $values = array(); for($i=0;$i<$insert_statements;$i++) { $values[] = "('name_{$i}','value_{$i}')"; } $query = "INSERT INTO `test` (`name`,`value`) VALUES ".implode(',',$values); mysqli_query($conn, $query); $insert_end['long_statement'] = microtime(); //Multi query statement $insert_start['multiquery_statement'] = microtime(); $query = ''; for($i=0;$i<$insert_statements;$i++) { $query .= "INSERT INTO `test` (`name`,`value`) VALUES ('name_{$i}','value_{$i}');"; } mysqli_multi_query($conn, $query); $insert_end['multiquery_statement'] = microtime(); echo "Mysqli INSERT test <br />STATEMENTS NUMBER: {$insert_statements}<br />"; foreach($insert_start as $index=>$value) { echo "Test '{$index}' time:".($insert_end[$index] - $value)."<br />"; } ?> структура таблицы - 3 поля id(AI, PRIMARY), name и value(TEXT) результаты: Спойлер (Отобразить) Mysqli INSERT test STATEMENTS NUMBER: 100 Test 'single_statement' time:0.014084 Test 'prepare_statement' time:0.029131 Test 'long_statement' time:0.0015109999999999 Test 'multiquery_statement' time:0.00034699999999999 Mysqli INSERT test STATEMENTS NUMBER: 1000 Test 'single_statement' time:0.076283 Test 'prepare_statement' time:0.259191 Test 'long_statement' time:0.008228 Test 'multiquery_statement' time:0.001462 Mysqli INSERT test STATEMENTS NUMBER: 100000 Test 'single_statement' time:9 Test 'prepare_statement' time:21 Test 'long_statement' time:0 Test 'multiquery_statement' time:0 Самым неожиданным для меня стал пожалуй результат prepare (может я неправильно его сделал?). провел ряд тестов, результат пожалуй везде повторяемый (последний тест с использованием time()). Ну а победитель здесь по-моему явный, Мелкий оказался прав. ----- if(time()>1356048000) die();

Мелкий	Отправлено: 17 Октября, 2011 - 20:34:12
Активный участник Покинул форум Сообщений всего: 11926 Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург Помог: 618 раз(а)	Хм, а я не ожидал от multi_query результата лучше мультивставки. А для prepare - ожидал использования http://ru2.php.net/manual/en/cla....mysqli-stmt.php ----- PostgreSQL DBA

Champion	Отправлено: 17 Октября, 2011 - 20:34:57
Активный участник Покинул форум Сообщений всего: 4350 Дата рег-ции: Авг. 2008 Откуда: Москва Помог: 57 раз(а)	White пишет: Самым неожиданным для меня стал пожалуй результат prepare Это потому что в цикле происходит аж 3 mysqli_query. Интересно посмотреть на результаты теста, если использовать myqli_prepare() + mysqli_stmt_bind_param() + mysqli_stmt_execute(). Я думаю, что при этом не будет происходить обращение к серверу на каждый бинд и скорость будет быстрее. Конечно до myltiquery не дотянет. Еще не увидел теста с кучей запросов в транзакции. Тоже будет довольно быстро. Хотя multiquery - всего одно обращение к серверу. Но у него есть минус - он накапливает строку запроса в памяти до тех пор, пока не выплонится запрос (Добавление) Тест на long_statement проведен не совсем корректно из-за накопления массива и implode. Это его искусственно замедляет. Лучше сделать его как и другие через .= (Добавление) Еще каждый следующий тест оказывается в более проигрышных ситуациях потому что переменные после предыдущих тестов не очищаются, поэтому процессу становится сложнее управлять памятью) Хотя это не мешает multiquery лидировать) (Добавление) А еще лучше использоать microtime(true), а то могут вылезти результаты с отрицательным временем

White	Отправлено: 17 Октября, 2011 - 22:39:00
Частый посетитель Покинул форум Сообщений всего: 830 Дата рег-ции: Июнь 2011 Откуда: Днепропетровск Помог: 28 раз(а)	использованный PREPARE был не зря. я был уверен что результаты с ним окажутся хуже, как о том говорят маны самого мускула, однако это решение работающее не только в рамках mysql Champion пишет: Еще каждый следующий тест оказывается в более проигрышных ситуациях потому что переменные после предыдущих тестов не очищаются убрал массив, теперь память выделенная под переменные минимальна, доработал немного тест, согласно рекомендациям, хотя общая тенденция результата все равно не изменилась код: Спойлер (Отобразить) PHP: скопировать код в буфер обмена <?PHP $conn = mysqli_connect("localhost", "root", "crRrtphde"); mysqli_select_db($conn, "new_table"); $insert_statements = 100000; //Start tests //Insert test //Single statements $insert_start['single_statement'] = microtime(true); for($i=0;$i<$insert_statements;$i++) { mysqli_query($conn, "INSERT INTO `test` (`name`,`value`) VALUES ('name_{$i}','value_{$i}')"); } $insert_end['single_statement'] = microtime(true); //Prepare statements $insert_start['prepare_statement'] = microtime(true); $stmt = mysqli_prepare($conn, "INSERT INTO `test` (`name`,`value`) VALUES (?,?)"); mysqli_stmt_bind_param($stmt, 'ss', $name, $value); for($i=0;$i<$insert_statements;$i++) { $name = "name_{$i}"; $value = "value_{$i}"; mysqli_stmt_execute($stmt); } mysqli_stmt_close($stmt); $insert_end['prepare_statement'] = microtime(true); //Long statement $insert_start['long_statement'] = microtime(true); $values = ''; for($i=0;$i<$insert_statements;$i++) { $values .= "('name_{$i}','value_{$i}'),"; } $query = "INSERT INTO `test` (`name`,`value`) VALUES ".rtrim($values, ','); mysqli_query($conn, $query); $insert_end['long_statement'] = microtime(true); //Multi query statement $insert_start['multiquery_statement'] = microtime(true); $query = ''; for($i=0;$i<$insert_statements;$i++) { $query .= "INSERT INTO `test` (`name`,`value`) VALUES ('name_{$i}','value_{$i}');"; } mysqli_multi_query($conn, $query); $insert_end['multiquery_statement'] = microtime(true); echo "Mysqli INSERT test <br />STATEMENTS NUMBER: {$insert_statements}<br />"; foreach($insert_start as $index=>$value) { echo "Test '{$index}' time:".($insert_end[$index] - $value)."<br />"; } ?> результат: Спойлер (Отобразить) Mysqli INSERT test STATEMENTS NUMBER: 100 Test 'single_statement' time:0.0075118541717529 Test 'prepare_statement' time:0.0076069831848145 Test 'long_statement' time:0.00041508674621582 Test 'multiquery_statement' time:0.00018882751464844 Mysqli INSERT test STATEMENTS NUMBER: 1000 Test 'single_statement' time:0.069777011871338 Test 'prepare_statement' time:0.077234983444214 Test 'long_statement' time:0.0030629634857178 Test 'multiquery_statement' time:0.0030210018157959 Mysqli INSERT test STATEMENTS NUMBER: 100000 Test 'single_statement' time:6.9121239185333 Test 'prepare_statement' time:13.802839994431 Test 'long_statement' time:0.37067294120789 Test 'multiquery_statement' time:0.062988042831421 Ну и чтобы окончательно убедиться, что позиция теста никак не влияет на результативность, поменял первый и второй местами Спойлер (Отобразить) Mysqli INSERT test STATEMENTS NUMBER: 100000 Test 'prepare_statement' time:13.739002943039 Test 'single_statement' time:7.7719440460205 Test 'long_statement' time:0.374675989151 Test 'multiquery_statement' time:0.084534883499146 напрашиваются странные выводы не в пользу prepare. остается некое ощущение, что в тесте допущена ошибка, либо сама конструкция здесь не к месту. ----- if(time()>1356048000) die();

Champion	Отправлено: 18 Октября, 2011 - 08:18:44
Активный участник Покинул форум Сообщений всего: 4350 Дата рег-ции: Авг. 2008 Откуда: Москва Помог: 57 раз(а)	Возможно, что тут и с php-ми функциями все равно идет обращение к серверу на каждый бинд... A файрберде например делается prepare, а потом вызов exec с параметрами, т.е. не делается отдельных биндов. Там точно за счет prepare прирост около 10%. А здесь, видимо, из-за отдельных биндов только хуже

White	Отправлено: 18 Октября, 2011 - 08:31:46
Частый посетитель Покинул форум Сообщений всего: 830 Дата рег-ции: Июнь 2011 Откуда: Днепропетровск Помог: 28 раз(а)	интересно какой механизм использует multi_query, ее результат оказался значительно быстрей одного запроса. может здесь что-то близкое к bulk insert? надо проверить на SELECT ----- if(time()>1356048000) die();

Champion	Отправлено: 18 Октября, 2011 - 08:58:28
Активный участник Покинул форум Сообщений всего: 4350 Дата рег-ции: Авг. 2008 Откуда: Москва Помог: 57 раз(а)	multi_query я думаю, что заключает всё в одну транзакцию и перестройка индексов происходит только один раз

White	Отправлено: 18 Октября, 2011 - 12:10:16
Частый посетитель Покинул форум Сообщений всего: 830 Дата рег-ции: Июнь 2011 Откуда: Днепропетровск Помог: 28 раз(а)	Champion пишет: перестройка индексов происходит только один раз да, но в случае с одним запросом транзакция ведь тоже одна, и индексы перестраиваются тоже один раз? может она распараллеливается на несколько процессов самим сервером? (Отредактировано автором: 18 Октября, 2011 - 12:10:53) ----- if(time()>1356048000) die();

Поиск в теме | Версия для печати

Страниц (2): [1] 2 »

Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)

« SQL и Архитектура БД »

Все гости форума могут просматривать этот раздел.
Только зарегистрированные пользователи могут создавать новые темы в этом разделе.
Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.