Индексы в Битрикс: как найти тяжёлый запрос и не навредить базе

Страница каталога грузится секунды, а не сотни миллисекунд. PHP почти не занят, зато MySQL упирается в процессор, и в списке процессов висит один и тот же SELECT по свойствам инфоблока. Первая реакция: добавить индекс на каждое поле из WHERE. Часто это не помогает, а запись в таблицы замедляется. Ниже о том, как найти виновный запрос, прочитать его план и выбрать между индексом, правкой кода и кешем. Материал написан для «1С-Битрикс: Управление сайтом» (ядро 22.x и новее), MySQL 8.0 и MariaDB 10.x.

Сначала найдите запрос, потом думайте об индексе

Индекс без конкретного запроса это гадание. Источников три, и лучше сверять их между собой:

• Медленный лог MySQL. Показывает реальные запросы с временем выполнения и числом просмотренных строк.
• Монитор производительности Битрикса (модуль perfmon, раздел «Настройки → Производительность»; названия пунктов зависят от версии). Связывает запрос со страницей и компонентом, то есть подсказывает, где в PHP его искать. Мониторинг добавляет нагрузку, поэтому включайте его на время замера, а не навсегда.
• SHOW FULL PROCESSLIST в момент тормозов. Грубо, зато видно, что именно выполняется сейчас.

Медленный лог включается без перезапуска сервера, но сбросится после рестарта. Для постоянной настройки пропишите те же параметры в конфиг MySQL.

-- MySQL 8.0 и MariaDB 10.x
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

-- Путь к файлу лога
SHOW VARIABLES LIKE 'slow_query_log_file';

Через день-два соберите статистику. Утилита mysqldumpslow из поставки MySQL группирует похожие запросы, а pt-query-digest из Percona Toolkit делает то же подробнее. Смотрите не на самый долгий запрос, а на тот, у которого максимальна суммарная доля времени: 20 мс, выполненные 50 тысяч раз, дороже одного запроса на 3 секунды.

Читаем EXPLAIN

EXPLAIN показывает план, не выполняя запрос. Из вывода нужны четыре колонки:

• type: значение ALL означает полный просмотр таблицы;
• key: какой индекс выбран, NULL означает, что никакой;
• rows: оценка числа строк, которые придётся просмотреть;
• Extra: пометки Using filesort и Using temporary говорят, что сортировка или группировка идут мимо индекса.

Типичный запрос, который Битрикс строит для списка элементов:

-- 5 здесь условный ID инфоблока
EXPLAIN
SELECT ID, NAME
FROM b_iblock_element
WHERE IBLOCK_ID = 5 AND ACTIVE = 'Y'
ORDER BY SORT ASC
LIMIT 24;

Если в плане выбран индекс только по IBLOCK_ID, а в Extra стоит Using filesort, база читает все активные элементы инфоблока и сортирует их, чтобы отдать 24. Индекс, где колонки идут в порядке «равенство, равенство, сортировка», убирает и чтение лишнего, и сортировку:

-- Префикс ix_local_ отличает ваши индексы от индексов ядра
ALTER TABLE b_iblock_element
    ADD INDEX ix_local_el_active_sort (IBLOCK_ID, ACTIVE, SORT);

Какие индексы в таблице уже есть, покажет SHOW INDEX FROM b_iblock_element. Проверьте это до того, как добавлять свой: возможно, ядро уже закрыло ваш случай.

В MySQL 8.0.18 и новее есть EXPLAIN ANALYZE: он выполняет запрос и показывает фактическое время. В MariaDB аналог называется ANALYZE SELECT. Учтите, что на проде тяжёлого запроса это такая же нагрузка, как и от самого запроса.

Свойства инфоблока: где на самом деле лежат данные

Фильтр по свойству в CIBlockElement::GetList или в ORM превращается в JOIN. Что именно соединяется, зависит от версии хранения инфоблока. В хранении 1.0 все значения лежат в общей b_iblock_element_property. В хранении 2.0 одиночные свойства лежат колонками в таблице b_iblock_element_prop_s{ID инфоблока}, множественные в b_iblock_element_prop_m{ID инфоблока}. Версию хранения смотрите в настройках инфоблока.

Для 2.0 и частого фильтра по одиночному свойству индекс ставится на его колонку:

-- Узнайте тип колонки и уже существующие индексы
SHOW CREATE TABLE b_iblock_element_prop_s5;

-- Свойство с ID 12 в инфоблоке 5.
-- Для текстовой колонки нужна длина префикса, для числовой её не пишут
ALTER TABLE b_iblock_element_prop_s5
    ADD INDEX ix_local_prop_12 (PROPERTY_12(64));

Фасетный индекс умного фильтра работает иначе и со своими таблицами. Это отдельная тема, здесь её не разбираем.

Четыре подхода и их цена

Когда что выбирать

• Запрос идёт часто, в rows сотни тысяч, а в ответе десятки строк: нужен индекс.
• Запрос быстрый, но в одном хите их сотни из-за вызова в цикле: индекс не поможет, переписывайте код.
• Запрос тяжёлый, а данные меняются раз в сутки: кеш.
• Фильтр по пяти и более свойствам в произвольных сочетаниях: индексами это не закрыть, смотрите в сторону фасетного индекса или отдельной таблицы.

Что ломается

Индекс ускоряет одни запросы и слегка замедляет все записи в таблицу. Чем длиннее список индексов, тем дороже каждая вставка.

• Индекс есть, а план его не использует. Причины: функция над колонкой в условии, LIKE '%текст' с процентом в начале, сравнение строки с числом.
• Неверный порядок колонок. Составной индекс работает по левому префиксу: условия на равенство ставьте первыми, диапазон и сортировку последними.
• Индекс на колонке с двумя значениями. Одиночный индекс по ACTIVE почти бесполезен из-за низкой селективности.
• Лишние индексы на таблицах с частой записью. Корзины, почтовые события, поисковый индекс: вставка обновляет каждый индекс.
• Обновление ядра. Ваши индексы с префиксом ix_local_ обычно переживают его, но после обновления проверяйте их через SHOW INDEX и храните ALTER в репозитории.

Сноска. Селективность это доля уникальных значений в колонке. У колонки с двумя значениями она низкая, у идентификатора максимальная. Чем выше селективность, тем сильнее индекс сужает выборку.

Добавление вторичного индекса в InnoDB обычно идёт без блокировки записи, но заметно нагружает диск. На крупной таблице делайте это в окно низкой нагрузки и после резервной копии. Сначала прогоните ALTER на копии базы и сравните план.

Что делать дальше

Через неделю после изменений соберите медленный лог заново и сравните с прошлым срезом. Главные показатели: суммарное время топ-10 запросов и отношение просмотренных строк к отданным (Rows_examined к Rows_sent в отчёте). Если отношение не упало, индекс не работает, как задумано. Заведите в репозитории файл со списком своих индексов и ALTER для них, чтобы восстановить их после переноса или обновления. Кеширование компонентов и фасетный индекс стоит разбирать отдельно, когда индексы и правка выборок закончатся.