.
Место для Вашей рекламы
23 Ноябрь 2012

MySQL — настройка и отладка скриптов

posted in Полезности, Программирование |

Последнее время сервачёк начал буквально трещать от нагрузки — от чего появились тормоза, что конечно же негативно сказывается на всём — юзерам не приятно, да и роботы не похвалят.
Первое, что пришло в голову — конечно же апгрейд (уже пора т.к. года 2 уже конфигурация без изменений, а нагрузка всё растёт).
Но нормальный апгрейд (полностью замена сервера на более мощный с полной настройкой системы с нуля) — дело не быстрое, а проблему хотелось бы решить побыстрее, хотя бы временно.
Анализ процессов показал, что MySQL явно не справляется и прилично грузит систему, особенно одного из хостеров интересовал конкретный запрос, который в админ панели отрабатывал довольно медленно (был осуществлен ручной допил VirturMart-a).
Первое, что я сделал — попытался привести в порядок серверные настройки MySQL — помогла статейка MySQL-тюнинг. Настраиваем по-взрослому.
Далее, оказалось искомый запрос вообще отказался работать — опытным путём выявил, что причиной тому стало ограничение max_join_size = 1000000
Вывод — оптимизация самого запроса.

Итак, исходные данные:

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
Получился такой запросик (для селекта строки переносить не стал т.к. на скорость выполнения не влияет)

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;

Ну и самое главное — по полю участвующему в условии и по полям участвующим в сцепке я проверил наличие индексов и добавил недостающие.
На всякий случай оптимизировал таблицы участвующие в запросе и сбросил кэш.

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 — настройка и отладка скриптов”

Почему бы Вам не высказать своем мнение! Позвольте нам узнать, что Вы думаете...

  1. 1 On 23.11.2012, mihos said:

    Э, брат, так не честно. Сперва оптимизируй и добавь индексы, а потом уже сверяй время выполнения. А то так как то «неинтересно» )

  2. 2 On 23.11.2012, GTAlex said:

    Дак тогда с чём сверять то ?

  3. 3 On 23.11.2012, seoplayer said:

    я верю, что настанет день, когда на костре сожгут программистов, которые пишут select из нескольких таблиц без применения join.

  4. 4 On 23.11.2012, GTAlex said:

    Seoplayer — что интересно, когда я к ГУРУ SQL обратился за помощью сюды http://www.sql.ru/forum/actualthread.aspx?tid=985493
    Там такая фраза мелькнула
    «Ну маны-то надо читать! При выполнении запроса ПЕРВОЕ, что сделает сервер — это преобразует джойны в картезианку с фильтрацией, т.е. приведёт запрос к исходному виду. Мартышкин труд.»
    Тут же читаю в других местах, где сравнивают быстродействие Left Join и Inner Join и пр. — получается, что разница всё таки есть.
    Так что даже не знаю — как прокомментировать …

  5. 5 On 23.11.2012, seoplayer said:

    GTAlex, разница есть и, как видишь, огромная.
    если селектить из нескольких таблиц — sql соберет в одну таблицу все записи выбранных таблиц, затем уберет лишние записи.
    с использованием join — сначала уберутся лишние записи, а потом соберется результат.

    в общем, при 100к записях результат будет небо и земля. в общем не уподобляйся говнокодерам 🙂

  6. 6 On 23.11.2012, GTAlex said:

    Да я, собственно, в курсе, просто поразил ответ мембера на sql.ru 🙂
    cпасибо за поддержку и грамотный комментарий

  7. 7 On 24.11.2012, ALexEY said:

    Оптимизация самого запроса очень важна для меня.

Оставить комментарий

Яндекс.Метрика