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

Оптимізація 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