Оптимізація SQL-запитів через EXPLAIN-аналіз 1С-Бітрікс

Медленная работа каталога или административного интерфейса — частая причина жалоб. Маємо понад 10 років досвіду оптимізації Бітрікс та 50+ успішних проектів. Ми переконалися: корень почти всегда в неэффективных SQL-запросах. Например, страница со списком товаров грузится 8–10 секунд, потому что MySQL сканирует миллионы строк в b_iblock_element, а разработчики не заглядывают в slow query log. Клиенты теряют конверсию, а ресурсы сервера расходуются впустую. Наша задача — за 2–5 дней найти узкие места через EXPLAIN-аналіз и исправить их.

EXPLAIN — команда MySQL, которая показывает план выполнения запроса: какие таблицы сканируются, используются ли индексы, сколько строк обрабатывается. Это основной инструмент после обнаружения медленного запроса в slow query log. Анализ выполняется на копии базы или в часы низкой нагрузки, чтобы не влиять на работу сайта.

Пропонуємо оптимізацію SQL-запитів під ключ: від аналізу slow log до впровадження індексів. Оцінимо ваш проект безкоштовно — надішліть slow query log.

После десяти лет работы с Битрикс мы видим одну и ту же картину: на большинстве проектов добавление индексов вручную без анализа приводит к временным улучшениям, а через неделю проблемы возвращаются. Только системный EXPLAIN-аналіз с последующей корректировкой кода и кэширования даёт долгосрочный результат. Клиенты экономят от 500 000 грн в год после такой оптимизации.

Читання EXPLAIN

EXPLAIN SELECT * FROM b_iblock_element WHERE IBLOCK_ID = 5 AND ACTIVE = 'Y' ORDER BY SORT; 

Ключові стовпці виводу:

Для більш точної діагностики використовуємо EXPLAIN ANALYZE (MySQL 8.0+, MariaDB 10.9+), який виконує запит і показує реальний час:

EXPLAIN ANALYZE SELECT ...; 

Виявлення повільних запитів

У slow query log ми шукаємо запити з часом виконання > 1 секунди. Для кожного запускаємо EXPLAIN. Якщо бачимо type=ALL або rows > 10 000 при точковій вибірці — запит кандидат на оптимізацію. Наприклад, на одному проекті виявили запит до b_iblock_element_property з rows=1 200 000. Після додавання індексу rows впало до 1200, а час виконання знизився з 3 секунд до 0.01 секунди — це в 250 разів швидше.

Характерні проблеми Бітрікс

Using filesort на b_iblock_element — часта знахідка. Запит сортує за SORT, але індекс не покриває комбінацію (IBLOCK_ID, ACTIVE, SORT). Рішення: складений індекс:

ALTER TABLE b_iblock_element ADD INDEX ix_iblock_active_sort (IBLOCK_ID, ACTIVE, SORT); 

Після додавання індексу EXPLAIN показує type=ref і Extra без Using filesort. Час виконання падає з секунд до мілісекунд.

rows = 500 000 на запит до b_iblock_element_property — виникає при фільтрації за значенням властивості без індексу за (IBLOCK_PROPERTY_ID, VALUE). Для VARCHAR-поля VALUE — індекс за префіксом VALUE(64). Це скорочує переглядувані рядки до сотень.

Using temporary при GROUP BY — зустрічається в запитах фасетного фільтра. Фасет Бітрікса будує оптимізовані таблиці b_iblock_find_* — якщо вони не перестворені після додавання властивостей, запити йдуть в обхід.

Діагностика ALL-сканування

type=ALL — найгірший варіант: MySQL сканує всю таблицю. В Бітрікс це часто відбувається при вибірці без умов або з умовами за неіндексованими полями. Негайно додаємо недостатні індекси. Наприклад, для b_iblock_element має бути індекс за (IBLOCK_ID, ACTIVE), для b_iblock_element_property — за (IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID). Якщо проблема в коді компонента, переписуємо запит із використанням CIBlockElement::GetList з правильними параметрами.

Чому EXPLAIN-аналіз ефективніший за інтуїтивний підхід?

Досвід показує: розробники часто додають індекси «на око», не перевіряючи план запиту. EXPLAIN дає об'єктивну картину. Порівняйте самі: без індексу запит сканує 500 000 рядків за 2 секунди, з індексом — 500 рядків за 0.002 секунди. Різниця в 250 разів. Тільки так ми гарантуємо результат.

Що входить у нашу роботу?

1. Аудит slow query log — збираємо всі повільні запити за тиждень. Використовуємо pt-query-digest або вбудовані засоби MySQL.
2. EXPLAIN-аналіз кожного підозрілого запиту — виявляємо type=ALL, Using filesort, Using temporary.
3. Проектування індексів — з урахуванням навантаження та структури даних. Іноді індекс може погіршити продуктивність вставки, тому оцінюємо компроміси.
4. Додавання індексів і переписування запитів — якщо індекс не допомагає, правимо код компонента або запиту (наприклад, зміна ORDER BY або додавання FORCE INDEX).
5. Повторний EXPLAIN — переконуємося, що type змінився, rows впало.
6. Тестування на бойових даних — заміряємо час виконання до та після. Використовуємо профілювання через EXPLAIN ANALYZE.
7. Документація — фіксуємо всі зміни та рекомендації щодо подальшого моніторингу.

Порівняння до та після оптимізації

Терміни та вартість

Оптимізація одного типового запиту (аналіз + правка) займає від 2 до 5 годин. Повний аудит проекту — від 2 до 5 днів. Вартість оптимізації одного запиту — від 5 000 до 15 000 грн. Пропонуємо безкоштовний аналіз одного запиту — надішліть slow query log. Замовте оптимізацію SQL через EXPLAIN-аналіз.

Додаткова інформація: Wikipedia: EXPLAIN