В посте про индексы MySQL под каталог 100k+ товаров — четыре места, где нужны ручные индексы: фильтр по свойствам, цены и остатки, сортировка с пагинацией, HL-блоки вместо EAV. Там всё подтверждено через EXPLAIN, но EXPLAIN показывает план, не реальное время. Здесь — те же рекомендации на живом тесте.
Поднял тестовый инфоблок версии 2.0 со 102 тысячами элементов (реальные простые, множественные и списочные свойства, зарегистрированный торговый каталог с ценами и остатками — чистый 1С-Битрикс, MySQL 8.0.40, локально), прогнал через него описанные запросы и сверил с тем, что написано в основном посте. Часть выводов подтвердилась один в один, часть пришлось скорректировать по ходу. Время — медиана из 7-9 прогонов через hrtime() из PHP, без принудительного сброса кеша MySQL между прогонами: данные уже лежат в оперативной памяти MySQL (буферном пуле), как и было бы на сервере под постоянным трафиком, а не при первом холодном запросе.
| Что измерял | До | После | Во сколько раз |
|---|---|---|---|
Пагинация: (SORT, ID) > (:last, :last) → тот же переход через OR | 65 мс | 0.17 мс | ×382 |
Пагинация: глубокий OFFSET → переход через OR | 65.8 мс | 0.17 мс | ×387 |
Цена: штатный индекс → добавлен PRICE в индекс | 38.6 мс | 3.3 мс | ×11.7 |
Остаток: штатный индекс → добавлен AMOUNT в индекс | 85.8 мс | 62.1 мс | ×1.4 |
Фильтр по свойствам через CIBlockElement::GetList(), индекс есть или нет | 108 мс | 110 мс | без разницы |
Тот же фильтр вручную, план задан через STRAIGHT_JOIN + FORCE INDEX | 108 мс | 18.6 мс | ×5.8 |
Фильтр по списочному свойству (VALUE_ENUM) — естественный план → план задан явно | 193 мс | 77.4 мс | ×2.5 |
Сильнее всего разница вышла на пагинации. Разница почти в 400 раз на одном и том же индексе и тех же данных. Без переписывания через OR совет из основного поста не дал бы ускорения — вышло бы не быстрее OFFSET.
Индекс на таблице свойств не подхватывается стандартным вызовом API. Составной индекс (IBLOCK_PROPERTY_ID, VALUE, IBLOCK_ELEMENT_ID) в лучшем случае ускорял ручной SQL с условием на значение прямо в JOIN ... ON — на 40 тысячах элементов MySQL выбрал его как ведущий, и запрос ускорился в 6 раз. Тот же самый запрос, слово в слово, на 100 тысячах элементов оптимизатор выполнил иначе, и выигрыш упал до 14% — решение зависит от статистики таблицы, не только от того, как написан SQL. Запрос, который реально генерирует CIBlockElement::GetList() (и сгенерированный по API_CODE D7-класс — собранный им SQL устроен так же), индекс не использует ни на одном из объёмов: EXPLAIN показывает его в possible_keys, но не в key.
Пробовал лечить нестабильность плана через ANALYZE TABLE ... UPDATE HISTOGRAM — в MySQL 8.0 это статистика распределения значений в колонке, по которой оптимизатор точнее оценивает, сколько строк подойдёт под условие, и заводится обычно как раз для такой нестабильности плана. На моих данных разницы не дала: план остался тем же, что до, что после. Единственное, что сработало гарантированно, — задать план явно через STRAIGHT_JOIN и FORCE INDEX. Индекс полезен только под собственный SQL с явным управлением планом, а не как автоматическое ускорение стандартного фильтра каталога — там его не заменить, нужен фасетный индекс.
У списочных свойств (VALUE_ENUM) та же картина на цифрах: штатный индекс (VALUE_ENUM, IBLOCK_PROPERTY_ID) уже стоит сам по себе, а нюанс с планом запроса тот же — естественно MySQL идёт от элемента (193 мс), с явным STRAIGHT_JOIN и FORCE INDEX на этот же штатный индекс — 77 мс.
У цен и остатков штатные индексы уже частично закрывают задачу. У b_catalog_price есть индекс на тип цены — он используется, но без PRICE даёт Using filesort. У b_catalog_store_product есть индекс на PRODUCT_ID — это уже точечный поиск, а не полное сканирование, поэтому выигрыш от добавленного AMOUNT заметно скромнее, чем в случае с ценами. Проверил по исходнику catalog.smart.filter: штатное «скрывать отсутствующие» фильтрует по AVAILABLE, а не через остатки по складам — раздел про EXISTS/b_catalog_store_product про более частный случай, складской учёт.
Индекс из раздела про сортировку держится и на полном запросе GetList(). Реальный CIBlockElement::GetList() с ACTIVE_DATE => 'Y' (так каталожные компоненты обычно и делают) добавляет к фильтру ещё диапазоны по ACTIVE_FROM/ACTIVE_TO и условие на WF_STATUS_ID — колонки, которых в индексе (IBLOCK_ID, ACTIVE, SORT, ID) нет. Проверил на этом полном запросе: filesort не возвращается, Extra — Using index condition; Using where, а не Using filesort. С индексом — 1.25 мс, без — 54.3 мс.
Пересборка фасетного индекса. Через журнал запросов MySQL прямо во время пересборки видно: трогается только своя служебная таблица (b_iblock_{ID}_index), таблицы свойств не задеваются, ручной индекс на них после пересборки остаётся на месте.
Полный разбор механики каждого случая, SQL-примеры и код — в посте про индексы MySQL под каталог 100k+ товаров.