Последнее время сервачёк начал буквально трещать от нагрузки — от чего появились тормоза, что конечно же негативно сказывается на всём — юзерам не приятно, да и роботы не похвалят.
Первое, что пришло в голову — конечно же апгрейд (уже пора т.к. года 2 уже конфигурация без изменений, а нагрузка всё растёт).
Но нормальный апгрейд (полностью замена сервера на более мощный с полной настройкой системы с нуля) — дело не быстрое, а проблему хотелось бы решить побыстрее, хотя бы временно.
Анализ процессов показал, что MySQL явно не справляется и прилично грузит систему, особенно одного из хостеров интересовал конкретный запрос, который в админ панели отрабатывал довольно медленно (был осуществлен ручной допил VirturMart-a).
Первое, что я сделал — попытался привести в порядок серверные настройки MySQL — помогла статейка MySQL-тюнинг. Настраиваем по-взрослому.
Далее, оказалось искомый запрос вообще отказался работать — опытным путём выявил, что причиной тому стало ограничение max_join_size = 1000000
Вывод — оптимизация самого запроса.
Итак, исходные данные:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT js_vm_orders.order_id, order_status, js_vm_orders.customer_note, js_vm_orders.cdate, order_total, js_vm_orders.user_id, payment_method_name, first_name, last_name, phone_1, phone_2, user_email, city, ship_method_id, hourly FROM js_vm_order_payment, js_vm_payment_method, js_vm_orders, js_vm_order_user_info, js_vm_rp_state WHERE js_vm_orders.order_status = ‘P’ AND js_vm_order_payment.order_id=js_vm_orders.order_id AND js_vm_payment_method.payment_method_id=js_vm_order_payment.payment_method_id AND js_vm_orders.order_id=js_vm_order_user_info.order_id AND js_vm_order_user_info.state=js_vm_rp_state.state_code |
Время выполнения запроса 8-9 секунд!
EXPLAIN
Для начала я использовал альясы для таблиц — с ними самому-же запрос читать стало попроще.
Далее, разобрав логику я переписал условия сцепки с использованием INNER JOIN
Получился такой запросик (для селекта строки переносить не стал т.к. на скорость выполнения не влияет)
|
1 2 3 4 5 6 7 8 9 10 |
SELECT o.order_id, order_status, o.customer_note, o.cdate, order_total, o.user_id, payment_method_name, first_name, last_name, phone_1, phone_2, user_email, city, ship_method_id, hourly FROM jos_vm_orders o INNER JOIN jos_vm_order_user_info u ON o.order_id = u.order_id INNER JOIN jos_vm_rp_state s ON u.state = s.state_code INNER JOIN jos_vm_order_payment p ON o.order_id = p.order_id INNER JOIN jos_vm_payment_method m ON p.payment_method = m.payment_method_id WHERE o.order_status = ‘P’ LIMIT 0 , 100; |
Ну и самое главное — по полю участвующему в условии и по полям участвующим в сцепке я проверил наличие индексов и добавил недостающие.
На всякий случай оптимизировал таблицы участвующие в запросе и сбросил кэш.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
ALTER TABLE `js_vm_orders` ADD INDEX (`order_status`); ALTER TABLE `js_vm_order_user_info` ADD INDEX (`state`); ALTER TABLE `js_vm_rp_state` ADD INDEX (`state_code`); OPTIMIZE TABLE `js_vm_order_user_info`; FLUSH TABLE `js_vm_order_user_info`; OPTIMIZE TABLE `js_vm_rp_state`; FLUSH TABLE `js_vm_rp_state`; OPTIMIZE TABLE `js_vm_orders`; FLUSH TABLE `js_vm_orders`; OPTIMIZE TABLE `js_vm_order_payment`; FLUSH TABLE `js_vm_order_payment`; OPTIMIZE TABLE `js_vm_payment_method`; FLUSH TABLE `js_vm_payment_method`; |
ИТОГО
запрос занял 0.0002 сек.
EXPLAIN
7 комментариев на «“MySQL — настройка и отладка скриптов”»
Э, брат, так не честно. Сперва оптимизируй и добавь индексы, а потом уже сверяй время выполнения. А то так как то «неинтересно» )
Дак тогда с чём сверять то ?
я верю, что настанет день, когда на костре сожгут программистов, которые пишут select из нескольких таблиц без применения join.
Seoplayer — что интересно, когда я к ГУРУ SQL обратился за помощью сюды http://www.sql.ru/forum/actualthread.aspx?tid=985493
Там такая фраза мелькнула
«Ну маны-то надо читать! При выполнении запроса ПЕРВОЕ, что сделает сервер — это преобразует джойны в картезианку с фильтрацией, т.е. приведёт запрос к исходному виду. Мартышкин труд.»
Тут же читаю в других местах, где сравнивают быстродействие Left Join и Inner Join и пр. — получается, что разница всё таки есть.
Так что даже не знаю — как прокомментировать …
GTAlex, разница есть и, как видишь, огромная.
если селектить из нескольких таблиц — sql соберет в одну таблицу все записи выбранных таблиц, затем уберет лишние записи.
с использованием join — сначала уберутся лишние записи, а потом соберется результат.
в общем, при 100к записях результат будет небо и земля. в общем не уподобляйся говнокодерам 🙂
Да я, собственно, в курсе, просто поразил ответ мембера на sql.ru 🙂
cпасибо за поддержку и грамотный комментарий
Оптимизация самого запроса очень важна для меня.