Почему операция в Битрикс24 занимала 39 секунд: диагностика таблицы на 9,5 млн строк

Проблема

На одном из коробочных порталов заметно замедлилась операция объединения сущностей CRM. Пользователь запускал объединение и почти минуту ждал завершения.

Первой гипотезой мог быть кастомный PHP-код, большое количество связанных объектов или сама логика объединения. Но вместо оптимизации наугад я сначала разделил время выполнения PHP и базы данных.

Сначала отделил PHP от базы

Замеры показали, что основная задержка возникает не в PHP, а внутри SQL.

Почти всё время SQL приходилось на один SELECT. Это сразу сузило область поиска: оптимизировать весь процесс объединения не требовалось — сначала нужно было разобраться именно с этим запросом.

Проблемный штатный запрос

Узким местом оказался штатный запрос Битрикс к таблице:

b_utm_calendar_event

Упрощённо условие выглядело так:

WHERE FIELD_ID = 17
  AND VALUE = 'D_378252'

Запрос искал связанные записи календарных событий. Результат был совсем небольшим — всего 113 строк, но получение этих строк занимало около 35,55 секунды.

На момент диагностики в таблице было:

9 479 572 строк

Сам по себе большой объём ещё не является проблемой. MySQL нормально работает и с более крупными таблицами, если запрос может быстро найти нужные строки по подходящему индексу.

Что показал EXPLAIN

MySQL использовал существующий индекс, но он не соответствовал условию запроса полностью.

В таблице были индексы, в том числе по:

VALUE_ID

и составной индекс:

FIELD_ID, VALUE_ID, VALUE_INT

При этом запрос фильтровал по:

FIELD_ID
VALUE

Поле VALUE имело тип TEXT и подходящего индекса для этой комбинации не было.

В результате MySQL мог использовать FIELD_ID, но после этого всё равно должен был проверить очень большой набор строк по VALUE.

EXPLAIN оценивал объём просмотра примерно в:

4 739 786 строк

То есть для получения 113 результатов база фактически работала примерно с половиной таблицы.

9 479 572 строк в таблице
          ↓
индекс по FIELD_ID
          ↓
~4 739 786 потенциальных строк
          ↓
проверка VALUE = 'D_378252'
          ↓
113 нужных строк

Почему проблема проявилась со временем

На небольшом объёме такой запрос может работать незаметно быстро. Если после фильтрации по FIELD_ID остаётся несколько тысяч строк, дополнительная проверка по VALUE почти не ощущается.

Но на старой коробке таблица постепенно растёт:

десятки тысяч
→ сотни тысяч
→ миллионы строк

Код запроса при этом может вообще не меняться. Поэтому проблема выглядит как внезапное "Битрикс стал тормозить", хотя фактически узкое место росло вместе с объёмом данных.

Это важный момент при диагностике старых коробок: один и тот же код на данных трёхлетней давности и на текущем объёме может иметь совершенно разные характеристики.

Какое исправление предложил

Для конкретного условия нужен индекс, позволяющий отбирать записи не только по FIELD_ID, но и по VALUE.

В ходе диагностики я предложил отдельный индекс:

CREATE INDEX ix_utm_calendar_event_crm_value
ON b_utm_calendar_event (
    VALUE(32),
    FIELD_ID,
    VALUE_ID
);

Поскольку VALUE имеет тип TEXT, используется префиксный индекс VALUE(32). Для значения вида D_378252 такого префикса достаточно, чтобы в индекс попало всё фактическое значение.

Цель такого индекса — перейти от:

FIELD_ID
↓
миллионы кандидатов
↓
фильтрация VALUE

к гораздо более узкому поиску:

VALUE + FIELD_ID
↓
небольшой диапазон подходящих записей

Результат диагностики

В результате удалось точно локализовать узкое место: штатный запрос Битрикс к b_utm_calendar_event фильтровал данные по FIELD_ID и неиндексированному VALUE. На таблице примерно с 9,48 млн строк существующий индекс сокращал выборку недостаточно, и MySQL оценивал просмотр примерно 4,74 млн строк для получения 113 результатов.

Причина была не просто в "большой таблице" и не в общем количестве данных, а в сочетании трёх факторов:

большой объём данных
+
конкретный WHERE
+
неподходящая структура существующих индексов

В этом случае одного профилирования вместе с EXPLAIN оказалось достаточно, чтобы объяснить почти 36 секунд из 39 секунд всей операции.