Есть таблица на сотни тысяч записей, периодически требуется удалять от нескольких сотен до нескольких тысяч записей.
Если удалять по одной в цикле - справляется довольно шустро.
Запрос
Полная Версия: Оптимизация sql-запроса
| SQL |
| DELETE FROM table WHERE id IN (5,7,9,25,37, ... 250123) |
по моим представлениям должен работать быстрее; а на практике - в разы медленнее.
Ткните, что я делаю не так.
Да.
Точнее, вида IN(1000,999,998,997,...) - по убыванию.
Но и ранжировать по возрастанию не проблема.
Я расскажу чуть подробнее.
Есть программа "упаковки" топика, когда все сообщения топика сбрасываются в текстовый файл. Сообщения "подшиваются" снизу вверх, чтобы верно определялся и писался интервал времени между сообщениями.
По мере упаковки топика, упакованные сообщения удаляются в самом цикле, это работает.
Решил оптимизировать, и не удалять сообщения в цикле, а собрать строку айдишников через запятую и удалить все скопом.
Вот цифры затрат времени:
Упаковка топика в 800 сообщений на локалке под денвером (раз в 10 медленнее, чем на реальном сервере):
В цикле - 7 секунд.
Одним запросом - вылет по таймауту 30 секунд.
Я не спрашиваю как разрешить процессу жить вечно, я спрашиваю, почему он не укладывается в 30 секунд.
Функцию set_time_limit(TIME_LIMIT) знаю и использую когда это нужно.
Я могу снять лимиты, но на реальном сервере делать этого принципиально не буду.
уверен?
Первое, что сделал, - попробовал в phpMyAdmin.
Через 30 секунд ожидания белая страница.
Гы!
Внутри цикла удаляется быстро.
Удаление по условию IN (".$idz.") - тормоз.
Не понимаю...
сам не проверял, как ты понимаешь, но до сих пор мануал не ошибался...
Я проверил это на таблице с почти 3 млн. записей - действительно, если задаешь пару десятков значений, то EXPLAIN говорит, что используется ключ... если же закинуть сразу 500, то идет сканирование таблицы.
Но тем менее:
Это ошибка или так можно писать ?
_____________
Бесплатному сыру в дырки не заглядывают...
Ткните, что я делаю не так.
Спустя 25 минут, 57 секунд (9.12.2008 - 23:49) kirik написал(а):
а поле id является первичным ключем?
Спустя 40 минут, 16 секунд (10.12.2008 - 00:29) FatCat написал(а):
Да, первичный ключ, по инкременту.
Если более конкретно, таблица постов форума.
Если более конкретно, таблица постов форума.
Спустя 16 минут, 51 секунда (10.12.2008 - 00:46) kirik написал(а):
темболее должно быстрее работать...
может тогда стоит юзать менее "красивый" запрос - DETETE FROM `table` WHERE `id` = 1 OR `id` = 3 OR `id` = ...
может тогда стоит юзать менее "красивый" запрос - DETETE FROM `table` WHERE `id` = 1 OR `id` = 3 OR `id` = ...
Спустя 2 часа, 5 минут, 26 секунд (10.12.2008 - 02:52) Alchemist написал(а):
Уточнение: запрос именно вида IN (1,2,3,4,...), т.е. уже с готовыми числами, или вида IN (SELECT `id` FROM ...), когда числа определяются динамически по ряду параметров ?
Спустя 9 часов, 15 минут, 9 секунд (10.12.2008 - 12:07) vasa_c написал(а):
Темы пхпфорума по рандому трёте? 
Сколько id-ов в списке?
Сколько id-ов в списке?
Спустя 3 часа, 10 минут, 11 секунд (10.12.2008 - 15:17) FatCat написал(а):
| Цитата (Alchemist @ 10.12.2008 - 02:52) |
| запрос именно вида IN (1,2,3,4,...) |
Да.
Точнее, вида IN(1000,999,998,997,...) - по убыванию.
Но и ранжировать по возрастанию не проблема.
| Цитата (vasa_c @ 10.12.2008 - 12:07) |
| Сколько id-ов в списке? |
| Цитата (FatCat @ 9.12.2008 - 23:23) |
| от нескольких сотен до нескольких тысяч |
Я расскажу чуть подробнее.
Есть программа "упаковки" топика, когда все сообщения топика сбрасываются в текстовый файл. Сообщения "подшиваются" снизу вверх, чтобы верно определялся и писался интервал времени между сообщениями.
По мере упаковки топика, упакованные сообщения удаляются в самом цикле, это работает.
Решил оптимизировать, и не удалять сообщения в цикле, а собрать строку айдишников через запятую и удалить все скопом.
Вот цифры затрат времени:
Упаковка топика в 800 сообщений на локалке под денвером (раз в 10 медленнее, чем на реальном сервере):
В цикле - 7 секунд.
Одним запросом - вылет по таймауту 30 секунд.
Спустя 42 минуты, 48 секунд (10.12.2008 - 16:00) Alchemist написал(а):
Вылетает по таймауту ??? Так это проблема скрипта, а не SQL !
| Цитата |
| ...configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running |
Спустя 50 минут, 21 секунда (10.12.2008 - 16:50) FatCat написал(а):
| Цитата (Alchemist @ 10.12.2008 - 16:00) |
| Вылетает по таймауту ??? Так это проблема скрипта, а не SQL ! |
Я не спрашиваю как разрешить процессу жить вечно, я спрашиваю, почему он не укладывается в 30 секунд.
Функцию set_time_limit(TIME_LIMIT) знаю и использую когда это нужно.
Я могу снять лимиты, но на реальном сервере делать этого принципиально не буду.
Спустя 17 минут, 8 секунд (10.12.2008 - 17:07) Alchemist написал(а):
Я не предлагал снять лимиты. Я имел в виду, что проблема не в SQL запросе, а где-то в пхп-коде. Бесконечный цикл, например, где-то получается или еще что-то. А запрос выполняется быстро, просто ты этого не видишь...
SQL запрос в принципе не может быть причиной вылета, т.к. его время не учитывается при вычислении таймаута.
SQL запрос в принципе не может быть причиной вылета, т.к. его время не учитывается при вычислении таймаута.
Спустя 2 часа, 3 минуты, 9 секунд (10.12.2008 - 19:10) Sylex написал(а):
| Цитата (Alchemist @ 10.12.2008 - 20:07) |
| SQL запрос в принципе не может быть причиной вылета, т.к. его время не учитывается при вычислении таймаута. |
уверен?
Спустя 1 час, 47 минут, 57 секунд (10.12.2008 - 20:58) kirik написал(а):
при 800 записях 30 секунд??! что-то здесь точно не ладное...
FatCat, а попробуй чисто для проверки - сделать вывод готового запроса на экран (не исполняя его) и затем запустить через простенький скрипт работы с БД, или через phpmyadmin.
Посмотри что будет.. просто это нереально, пусть даже в таблице 2 миллиона записей.
FatCat, а попробуй чисто для проверки - сделать вывод готового запроса на экран (не исполняя его) и затем запустить через простенький скрипт работы с БД, или через phpmyadmin.
Посмотри что будет.. просто это нереально, пусть даже в таблице 2 миллиона записей.
Спустя 1 час, 38 минут, 40 секунд (10.12.2008 - 22:37) FatCat написал(а):
| Цитата (kirik @ 10.12.2008 - 20:58) |
| попробуй чисто для проверки - сделать вывод готового запроса на экран (не исполняя его) и затем запустить через простенький скрипт работы с БД, или через phpmyadmin. |
Первое, что сделал, - попробовал в phpMyAdmin.
Через 30 секунд ожидания белая страница.
| Цитата (Alchemist @ 10.12.2008 - 17:07) |
| Бесконечный цикл, например |
Гы!
| Код |
| $idz = ""; $pack_query = $DB->query("SELECT id, ..... FROM ibf_posts WHERE topic_id =".$this->topic['tid']." order by pid desc"); while ($post = $DB->fetch_row($pack_query)) { $idz .= $post['id']."," } $idz .= "endstring"; $idz = str_replace(",endstring","",$idz); |
Внутри цикла удаляется быстро.
Удаление по условию IN (".$idz.") - тормоз.
Не понимаю...
Спустя 15 минут, 16 секунд (10.12.2008 - 22:52) Alchemist написал(а):
| Цитата (Sylex @ 10.12.2008 - 18:10) |
| уверен? |
сам не проверял, как ты понимаешь, но до сих пор мануал не ошибался...
Спустя 57 минут, 41 секунда (10.12.2008 - 23:50) Alchemist написал(а):
FatCat, да, заставил ты покопаться в мануалах Вот что я нарыл:
Вот что я нарыл: | Цитата (MySQL Manual) |
| The output from EXPLAIN shows ALL in the type column when MySQL uses a table scan to resolve a query. This usually happens under the following conditions: ... You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. |
Я проверил это на таблице с почти 3 млн. записей - действительно, если задаешь пару десятков значений, то EXPLAIN говорит, что используется ключ... если же закинуть сразу 500, то идет сканирование таблицы.
Но тем менее:
| Цитата (phpMyAdmin) |
| Showing rows 0 - 29 (2,329,472 total, Query took 0.3661 sec) |
Спустя 1 час, 28 минут, 59 секунд (11.12.2008 - 01:19) FatCat написал(а):
Тут еще такая странная штука, словно время растет в геометрической програссии.
50 записей - меньше секунды
100 записей - 2 с хвостиком
200 - 7 секунд
400 - больше 20...
Тот же пхпадмин...
50 записей - меньше секунды
100 записей - 2 с хвостиком
200 - 7 секунд
400 - больше 20...
Тот же пхпадмин...
Спустя 1 час, 1 минута, 39 секунд (11.12.2008 - 02:21) lemlem1 написал(а):
Мне кажется, что дело в том, что mysql не использует ключ, если велико количество выбираемых записей.
На тестовой машине отдельные запросы летают, потому что локи ставятся моментально (клиент-то один), и данные быстро удаляются. Однако, на продукшене всё
На тестовой машине отдельные запросы летают, потому что локи ставятся моментально (клиент-то один), и данные быстро удаляются. Однако, на продукшене всё
Спустя 6 минут, 51 секунда (11.12.2008 - 02:27) lemlem1 написал(а):
Ой, случайно отправилось.
На продукшене всё может изменится.
Насколько я помню, FORCE INDEX при делете в mysql указать нельзя, следовательно проблема нерешаема.
Лично я при удалении 30-70% гигантскизх таблиц выбирал во временную (но не memory) всё, что нужно оставить, делал DROP table и переименовывал временную. Как-то так.
На продукшене всё может изменится.
Насколько я помню, FORCE INDEX при делете в mysql указать нельзя, следовательно проблема нерешаема.
Лично я при удалении 30-70% гигантскизх таблиц выбирал во временную (но не memory) всё, что нужно оставить, делал DROP table и переименовывал временную. Как-то так.
Спустя 8 часов, 42 минуты, 34 секунды (11.12.2008 - 11:10) FatCat написал(а):
Еще раз проверил в пхпадмине, похоже, что так и есть.
В большой таблице оптимальный по скорости запрос на удаление 30-50 строк, не больше.
То есть, пока я удаляю порядка 1000 строк, можно не упираться и оставить в цикле; 7 секунд под денвером и порядка 1 секунды на реальной машине - вполне приемлемо.
Если нужно будет удалять многие тысячи, рациональней будет делить запрос на группы по 30-50 удаляемых строк в запросе.
Такой механизм нужно будет встраивать в функцию "очистка форума", в этой функции из idf_post могут удаляться десятки тысяч строк.
В большой таблице оптимальный по скорости запрос на удаление 30-50 строк, не больше.
То есть, пока я удаляю порядка 1000 строк, можно не упираться и оставить в цикле; 7 секунд под денвером и порядка 1 секунды на реальной машине - вполне приемлемо.
Если нужно будет удалять многие тысячи, рациональней будет делить запрос на группы по 30-50 удаляемых строк в запросе.
Такой механизм нужно будет встраивать в функцию "очистка форума", в этой функции из idf_post могут удаляться десятки тысяч строк.
Спустя 28 дней, 11 часов, 25 минут, 56 секунд (9.01.2009 - 22:36) kirik написал(а):
Случайно вот наткнулся на это. Похоже что это баг =)
Спустя 13 часов, 41 минута, 40 секунд (10.01.2009 - 12:18) FatCat написал(а):
Причем старый: в sql4 он тоже присутствует...
Спустя 7 часов, 11 минут, 52 секунды (10.01.2009 - 19:29) Alchemist написал(а):
наткнулся в репортах на такой код:
| SQL |
| DELETE FROM r USING phpbb_search_results AS r LEFT JOIN phpbb_sessions AS s ON s.session_id = r.session_id WHERE s.session_id IS NULL; |
Это ошибка или так можно писать ?
_____________
Бесплатному сыру в дырки не заглядывают...
Здесь расположена полная версия этой страницы.