Налаштування індексів бази даних 1С-Бітрікс

Налаштування індексів бази даних 1С-Бітрікс

Запит до каталогу на 50 000 товарів займає 4 секунди замість 40 мілісекунд. EXPLAIN показує ALL замість ref — таблиця читається цілком. Це типовий сценарій Бітрикс-сайту під навантаженням, де індекси не додавалися після зростання даних або були видалені при оновленні схеми.

Ключові таблиці Бітрикса та їх індекси

Структура зберігання даних інфоблоків розбита на кілька таблиць:

• b_iblock_element — основні записи елементів (поля ACTIVE, IBLOCK_ID, CODE, XML_ID, DATE_ACTIVE_FROM)
• b_iblock_element_property — значення властивостей типу рядок/число
• b_iblock_property_enum — значення властивостей-списків
• b_iblock_section — розділи (категорії каталогу)
• b_iblock_section_element — зв'язок елемент—розділ

На бойовому проекті b_iblock_element_property легко досягає 10–30 мільйонів рядків. Запит фільтрації за двома властивостями без індекса — full scan обох таблиць.

Діагностика повільних запитів

Включити slow query log у MySQL/MariaDB:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Бітрикс підтримує власне профілювання через константу у dbconn.php:

define("DBDebug", true);
define("DBDebugToFile", true);

Лог пишеться у bitrix/modules/main/tools/bx_sql.log. Але на продакшні це включати ненадовго — файл розрастається миттєво.

Обов'язкові індекси для інфоблоків

Перевірити наявність індексів:

SHOW INDEX FROM b_iblock_element;
SHOW INDEX FROM b_iblock_element_property;

Якщо відсутній складний індекс за (IBLOCK_ID, ACTIVE, DATE_ACTIVE_FROM) у b_iblock_element — додати:

ALTER TABLE b_iblock_element
ADD INDEX ix_ie_iblock_active_date (IBLOCK_ID, ACTIVE, DATE_ACTIVE_FROM);

Для фільтрації за властивостями через CIBlockElement::GetList з параметром PROPERTY_* критичен індекс у b_iblock_element_property:

ALTER TABLE b_iblock_element_property
ADD INDEX ix_iep_iblock_prop_val (IBLOCK_ID, IBLOCK_PROPERTY_ID, VALUE);

ALTER TABLE b_iblock_element_property
ADD INDEX ix_iep_element_prop (IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID);

Індекси для модуля пошуку та хітів

Таблиця b_search_content при активному пошуку зростає швидко. Базовий індекс:

ALTER TABLE b_search_content
ADD INDEX ix_sc_module_item (MODULE_ID, ITEM_ID);

Таблиця b_stat_phrase_date (модуль statistic) — часта причина повільних звітів:

ALTER TABLE b_stat_phrase_date
ADD INDEX ix_spd_date_phrase (DATE1, PHRASE_ID);

Індекси для торгового каталогу

Модуль catalog працює з таблицями b_catalog_price, b_catalog_product, b_catalog_store_product:

-- Пошук за ціною в діапазоні
ALTER TABLE b_catalog_price
ADD INDEX ix_cp_catalog_price (CATALOG_GROUP_ID, PRICE, CURRENCY);

-- Остатки на складах
ALTER TABLE b_catalog_store_product
ADD INDEX ix_csp_product_store (PRODUCT_ID, STORE_ID);

Без другого індекса запит остатків по всіх складах при листингу каталогу — O(n) за таблицею складських остатків.

Статистика та очистка застарілого

Бітрикс-сайти з включеним модулем statistic накопичують мільйони рядків у таблицях b_stat_*. Окрема проблема — фрагментація індексів. Після масових видалень через «Очистку статистики» у адміністративній панелі індекси не перебудовуються автоматично.

Аналіз фрагментації:

SELECT TABLE_NAME,
       ROUND(DATA_FREE/1024/1024, 2) AS free_mb,
       ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'bitrix_db'
  AND DATA_FREE > 10485760
ORDER BY DATA_FREE DESC;

Перестройка індексів таблиці без блокування (MySQL 5.6+):

ALTER TABLE b_iblock_element_property ENGINE=InnoDB;
-- або
OPTIMIZE TABLE b_iblock_element_property;

OPTIMIZE TABLE на InnoDB фактично робить ALTER TABLE ... ENGINE=InnoDB — повна перестройка. На таблиці 5 ГБ займе 10–20 хвилин з блокуванням. Для онлайн-перестройки — pt-online-schema-change з Percona Toolkit.

Автоматичне обслуговування індексів

У файл /bitrix/php_interface/init.php можна додати агент, який раз на тиждень запускає ANALYZE TABLE на критичних таблицях:

CAgent::AddAgent(
    'MyAnalyzeTables();',
    'main',
    'N',
    604800, // раз на тиждень
    '',
    'Y'
);

function MyAnalyzeTables() {
    global $DB;
    $tables = ['b_iblock_element', 'b_iblock_element_property', 'b_catalog_price'];
    foreach ($tables as $t) {
        $DB->Query("ANALYZE TABLE {$t}");
    }
    return 'MyAnalyzeTables();';
}

ANALYZE TABLE обновляет статистику без блокування на InnoDB — оптимізатор запитів отримує актуальні дані про розподіл значень.