Последнее время сервачёк начал буквально трещать от нагрузки – от чего появились тормоза, что конечно же негативно сказывается на всём – юзерам не приятно, да и роботы не похвалят.
Первое, что пришло в голову – конечно же апгрейд (уже пора т.к. года 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пасибо за поддержку и грамотный комментарий
Оптимизация самого запроса очень важна для меня.