Оптимізація SQL-запитів через EXPLAIN-аналіз 1С-Бітрікс
EXPLAIN — команда MySQL, яка показує план виконання запиту: які таблиці сканюються, використовуються ліндекси, скільки рядків обробляється. Це основний інструмент після виявлення повільного запиту в slow query log.
Як читати EXPLAIN
EXPLAIN SELECT * FROM b_iblock_element
WHERE IBLOCK_ID = 5 AND ACTIVE = 'Y'
ORDER BY SORT;
Ключові стовпці виводу:
| Стовпець | На що дивитися |
|---|---|
type |
ALL = повне сканування (погано), ref/range/const = використовує індекс |
key |
Який індекс вибрав оптимізатор, NULL = індексу немає |
rows |
Оцінка числа рядків для перегляду. 100 000+ при простому виборі — проблема |
Extra |
Using filesort = сортування в пам'яті/на диску. Using temporary = тимчасова таблиця |
EXPLAIN ANALYZE (MySQL 8.0+, MariaDB 10.9+) виконує запит і показує реальний час:
EXPLAIN ANALYZE SELECT ...;
Характерні проблеми Бітрікс
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);
rows = 500 000 на запит до b_iblock_element_property. Фільтрація за значенням властивості без індексу за (IBLOCK_PROPERTY_ID, VALUE). Для VARCHAR-поля VALUE — індекс за префіксом VALUE(64).
Using temporary при GROUP BY. Трапляється у запитах фасетного фільтра. Фасет Бітрікс будує оптимізовані таблиці b_iblock_find_* — якщо вони не перестворені після додавання властивостей, запити йдуть в обхід.
Порядок роботи
- Отримати повільний запит з slow query log або SQL-трекера Бітрікс
- Запустити
EXPLAIN— знайтиtype=ALLабоrows> 10 000 при точковому виборі - Визначити відсутній індекс або неефективний JOIN
- Додати індекс, повторити
EXPLAIN— переконатися, щоtypeзмінився - Перевірити реальний час на боєвих даних
Після додавання індексу MySQL не завжди його вибере — якщо статистика таблиці застаріла, запустіть ANALYZE TABLE b_iblock_element для перерахунку.