Перейти к содержимому
BXWiki

Медленный запрос к базе - поиск, план, индекс

Находим тот единственный запрос, из-за которого страница отвечает секунду, и разбираемся, чем ему помочь.

Решение

Собираем запросы страницы:

$connection = \Bitrix\Main\Application::getInstance()->getConnection();
$connection->startTracker();
// ... код страницы или скрипта
foreach ($connection->getTracker()->getQueries() as $query) {
    if ($query->getTime() > 0.05) {                    // интересны только долгие
        printf("%.3f c %s\n", $query->getTime(), $query->getSql());
    }
}

Трассировщик показывает не только время работы, но и сам текст запроса. Порог отсечения важнее списка целиком: тысяча запросов по миллисекунде мешает увидеть один запрос на полсекунды.

Читаем план выполнения:

EXPLAIN SELECT ID, NAME FROM b_iblock_element
WHERE IBLOCK_ID = 12 AND ACTIVE = 'Y' ORDER BY SORT LIMIT 20;


-- type: ALL       - таблица читается целиком, указатель не найден
-- rows: 480000    - столько строк база рассчитывает просмотреть
-- key: NULL       - ни один индекс не подошёл

План отвечает на главный вопрос: читается таблица целиком или по указателю к нужным строкам. Полный проход по большой таблице и есть та секунда, которую видит посетитель.

Подбираем индекс под реальный фильтр:

ALTER TABLE b_iblock_element ADD INDEX ix_active_sort (IBLOCK_ID, ACTIVE, SORT);
-- порядок полей повторяет порядок в фильтре: сначала равенство, потом сортировка
-- на большой таблице команда идёт минуты и блокирует запись

Порядок полей в индексе повторяет порядок условий в самом запросе. Индекс на одно поле из трёх помогает частично, а составной закрывает и отбор, и сортировку за один проход.

Считаем цену индекса:

SHOW INDEX FROM b_iblock_element;   -- что уже есть, чтобы не завести дубль
-- каждый индекс обновляется при каждой вставке и правке строки
-- на таблице обмена это превращается в заметное замедление выгрузки

Индекс ускоряет чтение строк и ровно так же замедляет их запись. На таблице, куда обмен пишет десятки тысяч строк, лишний указатель стоит дороже, чем экономит, и это проверяют замером до и после.

Замер повторяют после каждой отдельной правки, а не в самом конце работы. Иначе три изменения дают один результат, и никто не знает, какое из них сработало.

Правки индексов стоит хранить вместе с кодом решения. Указатель, добавленный руками на боевом сайте, не переживёт переноса и вернёт ту же секунду через месяц.

Штатным таблицам самой платформы индексы добавляют с осторожностью. Обновление продукта меняет их структуру, и о своём указателе оно ничего не знает.

Один медленный запрос стоит починить до того, как браться за настройки базы. Тюнинг сервера редко даёт тот же выигрыш, что убранный полный проход по таблице на четыреста тысяч строк.

Типичные проблемы

Индекс добавлен, а запрос быстрее не стал.

Фильтр идёт по выражению над полем, и указатель к нему не применяется. Условие переписывают на сравнение самого поля.

После добавления индексов замедлился обмен.

Каждая вставка строки обновляет все указатели таблицы. На таблицах активной записи их держат по минимуму.

На копии план хороший, на боевом плохой.

План зависит от объёма и распределения данных. Проверяют на копии с реальным объёмом, а не на пустой.

Запрос быстрый в консоли и медленный на сайте.

На сайте у него другие параметры и конкуренция за базу. Сравнивать нужно тот же запрос под нагрузкой.

Долгая команда изменения таблицы подвесила сайт.

Она блокирует запись на всё время работы. Такие правки делают в окно обслуживания или отдельным механизмом.

Частые вопросы

Как найти медленные запросы без правки кода?

Журналом медленных запросов самой базы. Он пишет всё, что дольше заданного порога, без участия сайта.

Почему выборка с фильтром по свойству такая тяжёлая?

Значения свойств лежат в отдельных таблицах, и фильтр требует соединения. Для каталога это решает фасетный индекс.

Стоит ли выбирать все поля сразу?

Нет: лишние поля тянут данные и мешают базе обойтись одним указателем. В выборке перечисляют только нужное.

Когда пора менять структуру, а не индексы?

Когда запрос уже идёт по указателю, а строк всё равно миллионы. Тогда помогает отдельная таблица с готовыми данными.

Смежное

Первоисточник