Почему не срабатывает индекс при sql запросе?

Question

[Ilia Malashko]

Здравствуйте.

Имеется сайт на 1с-Битрикс. С БД MariaDB 10.6.7

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

К примеру выполнили запрос через EXPLAIN на тесте, тут все хорошо:


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


В чем может быть причина такого поведения?

Comments

[Akina]

Вопросы о производительности либо использовании индексов без полных CREATE TABLE всех таблиц, точного текста запроса и сведений о статистике данных - бессмысленны.

Если Вы убеждены, что использование индекса будет эффективно - то всегда есть FORCE INDEX. Хотя начинать надо с ANALYZE TABLE.

[Михаил]

Было такое в постгресе: на тесте индекс ОК, на проде - не используется.
Как оказалось - не была собрана статистика по таблице.

Не знаю как это работает с MySQL/MariaDB, но в целом согласен с Akina

[Akina]

Михаил,

Не знаю как это работает с MySQL/MariaDB

Да прекрасно работает. ANALYZE TABLE обновляет статистику, и индекс снова используется, если это имеет смысл.

Answer 1

[Rsa97]

Слишком низкая селективность индекса. Оптимизатор решил, что полное сканирование таблицы выгоднее, чем использование индекса.

Comments

[Ilia Malashko]

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

Дело в том, что база одна (на тесте только менее актуальная давно не обновляли) а сервера разные.
Есть подозрение, что что-то накрутили в настройках сервера боевого и он теперь не верно принимает решение не использовать индекс.
Так как один и тот же запрос приведенный выше на тесте с использованием индекса выполняется 0,2 сек, а на бою без индекса 14 сек.
А какими настройками это правится?

[Rsa97]

Ilia Malashko, Это настройками не правится. Оптимизатор сам решает, когда имеет смысл использовать индекс. Для MySQL/MariaDB лимит проходит где-то по 1/COUNT(DISTINCT) = 20±5%.
Можно попробовать построить более селективный индекс по набору полей, но надо анализировать запрос.

[Ilia Malashko]

Rsa97, обновил базу на тестовом сервере.
Ситуация не изменилась, на боевом не используется индекс и подтягивается 215029 записей:

type - ALL
possible_keys - PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3,ix_iblock_element_code,ix_perf_b_iblock_element_1,ixbp_iblock_element_id	
key - {пусто}
key_len - {пусто}
ref - {пусто}
rows - 215029

На тесте сейчас та же база и тот же запрос, и он уже отдает 116024 записей и из индекса

type - ref
possible_keys - PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3,ix_iblock_element_code,ix_perf_b_iblock_element_1,ixbp_iblock_element_id		
key - ix_iblock_element_1	
key_len - 4
ref - const
rows - 116024

То есть значит все таки оптимизатор работает по разному? Так как отличие только на уровне сервера.

[Rsa97]

Ilia Malashko, Тоже может быть. Внутренние оптимизаторы СУБД - дело тёмное и их работа может зависеть от погоды на Марсе. Может влиять версия сервера, наличие свободной оперативной памяти, фрагментация таблицы и индексов, возможность параллельной обработки, накопленная за время работы СУБД статистика по таблице и многое другое.