Рецепты оптимизации SQL-запросов рядом с Битрикс

Зачем это «рядом с Битрикс»

Ядро и модули генерируют свой SQL, но кастомные выборки, отчёты, агенты и прямые запросы к b_iblock_element, таблицам продажи и своим таблицам идут в MySQL теми же правилами. Ниже — сжатые рецепты: что ускоряет план и что чаще всего ломает его на боевых объёмах.

Общие принципы

Выбирайте только нужные колонки и отсекайте лишние строки условиями до JOIN.
Внешние ключи и согласованные типы в связях помогают оптимизатору использовать индексы в JOIN.
Избегайте массового UPDATE без фильтра: всегда ограничивайте затронутый набор (условие, пакет по ключу).
Иногда выгоднее два простых SELECT с IN (...) по предварительно собранным id, чем один тяжёлый JOIN — проверяйте план на копии данных.
В GROUP BY и ORDER BY желательно опираться на колонки одной таблицы под один индекс — смешение таблиц чаще снимает покрытие.
Замена подзапроса на JOIN иногда ускоряет, иногда нет: смотрите EXPLAIN, не догму.
Поля TEXT/BLOB в условиях JOIN и в широком SELECT * раздувают трафик приложение↔СУБД и ухудшают работу буферов.
Для построчной обработки больших выборок в PHP сравните с небуферизованными API драйвера: обычный курсор может тянуть всё в память клиента до цикла.

Подсказки оптимизатору

Явные подсказки (HIGH_PRIORITY, LOW_PRIORITY, STRAIGHT_JOIN, SQL_SMALL_RESULT / SQL_BIG_RESULT, USE INDEX / FORCE INDEX и т. д.) стоит включать, когда профилирование уже показало узкое место и аккуратный индекс или переписанный запрос не помогли. В продакшене это компромисс: порядок JOIN «вручную» или принудительный индекс могут ускорить сегодняшний план и ухудшить завтрашний при смене данных.

COUNT

COUNT(*) считает строки; COUNT(expr) — только ненулевые значения выражения. Для условий вроде «сколько красных» удобна форма с SUM(expr) / булевым выражением, но проще и чаще — COUNT с явным OR NULL внутри, где это читается командой.

На многих движках COUNT(*) с оптимальным индексом не хуже «обходного» подсчёта по id.
Покрывающий индекс под частый подсчёт сужает чтение до индекса без таблицы.
Счётчики в отдельной строке/таблице (денормализация) окупаются при очень частых чтениях и редких пересчётах — типичный приём для витрин и сводок, не для каждой мелкой сущности.

GROUP BY и DISTINCT

Индексы и покрывающие индексы — основа. SQL_BIG_RESULT / SQL_SMALL_RESULT влияют на выбор временной таблицы в памяти или на диске для больших промежуточных множеств. Группировка по целочисленному ключу обычно дешевле, чем по длинным строкам без индекса.

Если MySQL сортирует результат GROUP BY по полям группировки, а вам порядок не важен, иногда помогает явный ORDER BY NULL (проверяйте версию и план). Направление ASC/DESC в группировке возможно, но должно совпадать с тем, что реально нужно отчёту.

LIMIT, OFFSET и UNION

Большое OFFSET дорого: сервер всё равно проходит «отброшенные» строки. Варианты: ключевое продолжение («где id > ? ORDER BY id LIMIT n»), отдельная колонка порядка с индексом, или подзапрос только по ключам с узким индексом, как в учебном примере с фильмами: сначала ограничить первичные ключи через ORDER BY … LIMIT, затем присоединять тяжёлые поля.

UNION ALL предпочтительнее простого UNION: последний подразумевает лишнее слияние без дубликатов. Если дубликаты заведомо невозможны, не платите за DISTINCT дважды.

SELECT f.film_id, f.description
  FROM sakila.film AS f
  INNER JOIN (
    SELECT film_id FROM sakila.film
    ORDER BY title LIMIT 50, 5
  ) AS lim USING (film_id);

Кеширование запросов (классическая модель MySQL)

В старых версиях MySQL кешировался полный результат SELECT при полном совпадении текста SQL, включая комментарии и пробелы. Недетерминизм ломал кеш: NOW(), CURRENT_DATE(), UDF, переменные сессии, временные таблицы. Для тяжёлого редкого запроса с малым выводом кеш мог помогать; для огромных ответов — наоборот, забивать память — тогда имело смысл SQL_NO_CACHE.

В MySQL 8.0 кеш запросов на стороне сервера сняли; логика «стабильный текст + детерминизм» переносится на прикладной уровень (CPHPCache, Redis, собственные слои) и на проектирование SQL без «плавающих» функций в тексте, если вы всё ещё на старом контуре с query cache.

Краткий итог

Сужайте данные на каждом шаге, держите индексы в соответствии с реальными фильтрами, проверяйте UNION и глубокий OFFSET, а экзотические подсказки оптимизатору оставляйте для случаев, когда индекс и переписанный запрос уже исчерпаны.