Тяжёлые запросы

[SpyCam]

Хотелось бы узнать, какие действия смарта (или в админке смарта) генерируют запросы, как в примере ниже?
Такие запросы порой висят по 600-800 секунд. Их быывает одновременно много, более десятка,

SELECT SQL_CALC_FOUND_ROWS gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats1 as gs JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id JOIN rot_gallery_data19 as gd on gd.gallery_id = gs.gallery_id JOIN rot_gal2tag on rot_gal2tag.gallery_id = gs.gallery_id JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND gs.gallery_id IN (SELECT gallery_id FROM rot_gal2tag WHERE tag_id IN ('20537') ) group by gs.gallery_id, gs.thumb_id ORDER BY tested_thumb DESC, gs.total_ctr DESC LIMIT 1500, 60

[admin]

это скорее всего25я страница листинга тагов tag_id IN ('20537')

какой конкретно план запроса этого получается?

[SpyCam]

https://pastebin.com/WbdwJ5L2 вот

[admin]

в целом все верно, считает общее кол-во вхождений для пагинации

надо либо увеличивать время кеша, либо убирать пагинацию (менять на prev next) - тогда не будет считать общее кол-во страниц

вариант 3 - если у админа есть идеи как такой запрос можно сделать быстрее, я сделаю

[magicum]

Вот такая штука тут у нас, судя по explain:

Code: Select all

*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: gd
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 392724
     filtered: 0.00
        Extra: Using where; Using join buffer (Block Nested Loop)

Ругается на block nested loop, и возможно тут собака зарыта.

Беглый гуглёж показал советы а-ля "запилите еще индексов".

Смотрим, а где у нас таблица gd описывается.

Видимо, тут:

Code: Select all

JOIN rot_gallery_data19 as gd on gd.gallery_id = gs.gallery_id 

Я увы, не очень силён в MySQL, но как раз эти поля для джойна - и есть индексы.

У rot_gallery_data19 - это первичный индекс (gallery_id), а у gs (она же rot_gallery_stats1 это поле хоть и не первичный ключ, но таки ключ - KEY `gallery_id` (`gallery_id`) )

Нет ли мыслей по этому поводу?

[admin]

gs.gallery_id не может быть уникальным тк у галеры больше чем 1 тумба может быть

но у меня другой план запроса строитт, начиная с gd, а не gs
это может зависеть и от наполнения базы и от конкретной версии мускла

есть PMA?

[magicum]

Увы, PMA нет. Но я могу сделать что надо для теста.

Я еще вот сделал профайлинг такого запроса. Вот результат: https://pastebin.com/Bx3yZiG3
Т.е. всё время ушло на Sending.
Моя компетенция тут нулевая, но может как-то прольёт свет.

MySQL 5.7 (Percona)

[admin]

sending это и есть выборка

чисто теоретически могу посоветовать только как-то поменять запрос и смотреть как меняется план запроса, без базы это примерно как чинить двигатель по телефону)

[SpyCam]

Напишу в приват с доступом на сервер

[magicum]

В дополнение.

Вот оригинальный запрос, отловленный в slowlog, выполнялся 1700 секунд примерно: https://pastebin.com/7unQyqv3
При повторном его ручном запуске он висел 800 секунд и был прибит, т.к. надоело ждать =)

Далее, путём изнасилования одного онлайн сервиса (т.к. я ни уха ни рыла в DBA) был получен анализ и рекомендации по этому запросу.

Предлагается добавить следующие индексы:

Code: Select all

ALTER TABLE `rot_gal2tag` ADD INDEX `rot_gal2tag_idx_gallery_id_tag_id` (`gallery_id`,`tag_id`);
ALTER TABLE `rot_gallery_info` ADD INDEX `rot_gallery_info_idx_gallery_st_gallery_ty_sponsor_id` (`gallery_status`,`gallery_type`,`sponsor_id`);
ALTER TABLE `rot_gallery_stats1` ADD INDEX `rot_gallery_stats1_idx_best_th_group_gallery_thumb` (`best_thumb`,`group_id`,`gallery_id`,`thumb_id`);

Это и было сделано (после бекапа базы).

Далее предлагается изменить запрос и сделать его таким: https://pastebin.com/Kwy76GkS
Объяснения:
- новые индексы помогают ускорить этот запрос (при проверке explain показывает, что таки да, они задействуются)
- предлагается заменить IN на EXISTS, т.к. по их словам это быстрее
- предлагается использовать значения числовых полей не как строковые, не в кавычках, ибо в этом случае не используются индексы
- ** еще они предлагают избавиться от SQL_CALC_FOUND_ROWS и использовать count(), как в этом примере: https://i.imgur.com/5wROHv6.png (этого нет в новом запросе, там всё еще SQL_CALC_FOUND_ROWS)

Еще предлагается избегать OFFSET, но это не наш случай, судя по всему, и предлагается не делать выборку по всем полям (как это делается в gd.* ), но я не знаю насколько это нужно\ненужно.

Новый запрос выполняется 11 секунд, т.е. прогресс налицо, хотя хотелось бы ещё быстрее, конечно.

Для наглядности вот дифф нового и старого запросов: https://pasteboard.co/Kgtc1SM.png

Для допонительного теста БД возвращена в исходное состояние и снова был запущен новый запрос (т.е. запрос новый но без новых индексов).
Время запроса составило 27 секунд.
Т.е. тут получается важны и индексы и сам модифицированный запрос (но он, видимо, более важен).