Bugzilla - Оптимизация поиска по изменениям

В поиске Bugzilla есть фильтр по дате изменения бага. То есть «баги измененные в период с ___ по ___ юзером ___, где было изменено одно из полей ___».

В оригинале этот фильтр проверяет только таблицу bugs_activity (изменения значений полей). И это неправильно! Есть ещё таблица longdescs, содержащая комментарии и рабочее время. И проверять её нужно обязательно — например, при создании бага в bugs_activity вообще ничего не попадает и время тоже списывается в комментарии.

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

Какие варианты?

1. LEFT JOIN на каждую таблицу или на их UNION и две проверки IS NOT NULL через «ИЛИ»: выбирает туеву хучу строк и mysql загибается (что в целом логично). То есть, данный вариант уязвим к большому числу изменений/комментов в багах (а оно всегда большое).

2. Независимый подзапрос в каждую таблицу или в их UNION — неправильно оптимизируется по индексам. Нет бы выбрать его результаты во временную таблицу и искать строки уже в ней — в MySQL почти все подзапросы, в том числе и независимые от внешнего, выполняются «снаружи вовнутрь».

   То есть, данный вариант уязвим к отсутствию или малому числу дополнительных фильтров в запросе, с которых его вообще можно начать выполнять и не делать фулскан.

3. Зависимый подзапрос в каждую таблицу (то есть включающий в себя WHERE bugs_activity.bug_id=bugs.bug_id). Тоже выполняется снаружи вовнутрь и тоже приводит к полному сканированию таблицы багов.

4. Изображать из себя оптимизатор запросов, создавать временную таблицу с подходящими под фильтр ID багов и делать на неё INNER JOIN.

   Проблемы:

   • Во-первых, иногда начинают возникать Deadlock’и, детектятся MySQL’ем и происходит «внутренняя ошибка». Не разбирался, но скорее всего поправимо.
   • Во-вторых, конечно, всё равно — одно лечим, другое калечим. Проблемы будут, если багов под фильтр попадёт очень много — все их ID должны будут быть сохранены во временную таблицу, то есть будут гоняться большие объёмы данных.
     То есть данный вариант уязвим к низкой специфичности самого фильтра по дате изменения.
   • В-третьих, накрывается медным тазом предоставление view’шек пользователям по таким запросам, потому что одна временная таблица живёт только в рамках одной сессии.

5. Не изображать из себя оптимизатор запросов и делать INNER JOIN на подзапрос, идентичный описанному в предыдущем пункте. То есть всё то же самое, но без временной таблицы. На данный момент работает оптимально, ибо не множит JOIN’ом строчки (используется либо UNION, либо DISTINCT) и нормально понимается MySQL’евским оптимизатором запросов.

Дополнение: поиск Bugzilla изобилует подзапросами в таблицу profiles. Учитывая дебильный MySQL’евский способ выполнения подзапросов (который даже в 5.5 так и не поменялся), зачем так сделано — непонятно, но всё это достаточно легко переписывается на JOIN’ы, после чего My Bugs начинает просто-таки летать. В нашей сборке сие сделано (2011-06-10 будет пронесено).

---

Статья отреплицирована из внутренней базы знаний компании.