Профілювання MySQL-запитів 1С-Бітрікс

Профілювання MySQL-запитів 1С-Бітрікс

Бітрікс активно працює з базою даних: модуль каталогу (b_catalog_price, b_iblock_element, b_iblock_element_prop_s*), пошук (b_search_content), сесії (b_user_session). При 20–50 тисячах товарів і ввімкнених властивостях інфоблоку типова сторінка каталогу генерує 50–200 SQL-запитів. Частина з них виконується за 1–2 мс, але кілька можуть займати по 500 мс — і саме вони визначають час відповіді сторінки.

Увімкнення slow query log

Перший інструмент — журнал повільних запитів MySQL. Вмикається без перезапуску сервера:

-- Динамічно, без перезавантаження MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 0.5;       -- запити довші за 500 мс
SET GLOBAL log_queries_not_using_indexes = 1;  -- запити без індексів

У /etc/mysql/conf.d/slow.cnf для постійного ефекту:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

min_examined_row_limit = 100 прибирає шум від швидких запитів за первинним ключем, які «не використовують індекс» формально.

Аналіз через pt-query-digest

Percona Toolkit — стандарт аналізу slow log:

pt-query-digest /var/log/mysql/slow.log \
    --limit 20 \
    --report-format query_report \
    > /tmp/slow_report.txt

Звіт групує запити за нормалізованим шаблоном (замінює значення на ?), показує:

• Calls — скільки разів виконувався
• R/Call — середній час на виклик
• R/t — сумарний час за період

На Бітрікс-проєктах топ-5 проблемних запитів зазвичай виглядає так:

1. SELECT ... FROM b_iblock_element_prop_s5 WHERE IBLOCK_ELEMENT_ID IN (...) — N+1 при виборці властивостей
2. SELECT ... FROM b_catalog_price WHERE PRODUCT_ID = ? — поштучна виборка цін
3. SELECT COUNT(*) FROM b_iblock_element WHERE ... — COUNT без індексу по складеному фільтру
4. SELECT ... FROM b_search_content WHERE ... — повнотекстовий пошук по великій таблиці
5. UPDATE b_user_session SET ... — конкуренція за оновлення сесій

EXPLAIN та аналіз плану запиту

Для кожного повільного запиту зі звіту запускаємо EXPLAIN:

EXPLAIN SELECT be.ID, be.NAME, bp.VALUE
FROM b_iblock_element be
LEFT JOIN b_iblock_element_prop_s5 bp ON bp.IBLOCK_ELEMENT_ID = be.ID
WHERE be.IBLOCK_ID = 12
  AND be.ACTIVE = 'Y'
  AND be.WF_STATUS_ID = 1
ORDER BY be.SORT ASC
LIMIT 48 OFFSET 0;

На що дивитися у виводі EXPLAIN:

• type = ALL (full table scan) — відсутній індекс, критично
• type = ref або range — використовується індекс, нормально
• rows — оцінка кількості рядків, що перевіряються; якщо на таблиці з 100k записів rows = 95000 — індекс не працює
• Extra: Using filesort — сортування в пам'яті/на диску, може бути дорогим
• Extra: Using temporary — створюється тимчасова таблиця

-- EXPLAIN ANALYZE (MySQL 8.0+) показує реальний час
EXPLAIN ANALYZE SELECT ...;

Створення індексів для типових запитів Бітрікс

Кілька індексів, які регулярно відсутні в стандартному встановленні або зникають після оновлень:

-- Складений індекс для фільтрації активних елементів за інфоблоком з сортуванням
CREATE INDEX idx_iblock_element_active_sort
ON b_iblock_element (IBLOCK_ID, ACTIVE, WF_STATUS_ID, SORT);

-- Індекс для виборки цін за продуктами та групами
CREATE INDEX idx_catalog_price_product_group
ON b_catalog_price (PRODUCT_ID, CATALOG_GROUP_ID);

-- Індекс для пошуку по сесіях (якщо b_user_session без кешу)
CREATE INDEX idx_user_session_timestamp
ON b_user_session (TIMESTAMP_X);

Після створення індексу перезапускаємо EXPLAIN — тип повинен змінитися з ALL на ref або range.

Бітрікс ORM та N+1

D7 ORM (\Bitrix\Main\ORM) часто генерує N+1 при роботі зі зв'язаними сутностями. Діагностика — через вбудований дебагер або перехоплення SQL:

// Логуємо всі запити в dev-оточенні
\Bitrix\Main\Application::getConnection()->setTracker(
    new \Bitrix\Main\DB\SqlTracker(50)
);

// Наприкінці запиту:
$tracker = \Bitrix\Main\Application::getConnection()->getTracker();
foreach ($tracker->getQueries() as $query) {
    if ($query->getTime() > 0.1) {
        error_log($query->getSql() . ' [' . $query->getTime() . 's]');
    }
}

Типовий N+1 в ORM-коді:

// ПОГАНО: запит до b_catalog_price на кожній ітерації
$elements = ElementTable::getList(['filter' => ['IBLOCK_ID' => 12]]);
while ($el = $elements->fetch()) {
    $price = PriceTable::getRow(['filter' => ['PRODUCT_ID' => $el['ID']]]); // N запитів
}

// ДОБРЕ: пакетна виборка
$ids = array_column($elements->fetchAll(), 'ID');
$prices = PriceTable::getList(['filter' => ['PRODUCT_ID' => $ids]])->fetchAll();
$priceMap = array_column($prices, null, 'PRODUCT_ID');

Кейс: оптовий дистриб'ютор

Сайт на Бітрікс «Малий бізнес», каталог 28 000 позицій, 3 000 унікальних відвідувачів на день. Сервер: 4 CPU, 8 GB RAM. Навантаження на MySQL — 85–90% CPU в пікові години, періодичні таймаути.

pt-query-digest за добу показав: 92% сумарного часу MySQL — запити до b_iblock_element_prop_s8 (таблиця рядкових властивостей). EXPLAIN показав type = ALL на 280 000 рядках — індекс по IBLOCK_ELEMENT_ID був відсутній.

-- Один індекс закрив проблему
CREATE INDEX idx_prop_s8_element_id ON b_iblock_element_prop_s8 (IBLOCK_ELEMENT_ID);

Навантаження на MySQL впало з 85–90% до 15–20% CPU без жодних змін у коді застосунку.

Інструменти моніторингу

• Percona Monitoring and Management (PMM) — повнофункціональний стек, графіки QPS, latency, топ запитів у реальному часі
• MySQL Workbench → Performance Schema — вбудований GUI, зручний для разової діагностики
• Grafana + mysql_exporter — для інтеграції в наявний моніторинг

Терміни