Почему не срабатывает индекс при sql запросе?
Здравствуйте.
Имеется сайт на 1с-Битрикс. С БД MariaDB 10.6.7
Некоторое время назад начали появляться медленные запросы на бою. Установили что проблема в том что не используется индекс при получении значений таблицы.
К примеру выполнили запрос через EXPLAIN на тесте, тут все хорошо:
Но если тот же запрос выполнить на бою, то индекс не используется, хотя ключи имеются:
В чем может быть причина такого поведения?
Дополнительно:
Вопросы о производительности либо использовании индексов без полных CREATE TABLE всех таблиц, точного текста запроса и сведений о статистике данных - бессмысленны.
Если Вы убеждены, что использование индекса будет эффективно - то всегда есть FORCE INDEX. Хотя начинать надо с ANALYZE TABLE.
Как оказалось - не была собрана статистика по таблице.
Не знаю как это работает с MySQL/MariaDB, но в целом согласен с Akina
Не знаю как это работает с MySQL/MariaDB
Да прекрасно работает. ANALYZE TABLE обновляет статистику, и индекс снова используется, если это имеет смысл.
Слишком низкая селективность индекса. Оптимизатор решил, что полное сканирование таблицы выгоднее, чем использование индекса.
- Там действительно низкая селективность, запрос получает все товары каталога.
Но на тесе с индексом быстрее.Дело в том, что база одна (на тесте только менее актуальная давно не обновляли) а сервера разные.
Есть подозрение, что что-то накрутили в настройках сервера боевого и он теперь не верно принимает решение не использовать индекс.
Так как один и тот же запрос приведенный выше на тесте с использованием индекса выполняется 0,2 сек, а на бою без индекса 14 сек.
А какими настройками это правится? - Ilia Malashko, Это настройками не правится. Оптимизатор сам решает, когда имеет смысл использовать индекс. Для MySQL/MariaDB лимит проходит где-то по 1/COUNT(DISTINCT) = 20±5%.
Можно попробовать построить более селективный индекс по набору полей, но надо анализировать запрос. - 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
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
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
То есть значит все таки оптимизатор работает по разному? Так как отличие только на уровне сервера.
- Ilia Malashko, Тоже может быть. Внутренние оптимизаторы СУБД - дело тёмное и их работа может зависеть от погоды на Марсе. Может влиять версия сервера, наличие свободной оперативной памяти, фрагментация таблицы и индексов, возможность параллельной обработки, накопленная за время работы СУБД статистика по таблице и многое другое.
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Возможно, проблема с отсутствием срабатывания индекса при SQL запросе может быть вызвана несколькими причинами. Вот несколько возможных причин и способы их решения:
1. Отсутствие индекса на поле, по которому вы осуществляете поиск в запросе. Убедитесь, что у соответствующего поля в вашей таблице есть индекс. Если индекс отсутствует, создайте его с помощью следующего SQL запроса:
CREATE INDEX index_name ON table_name (column_name);
2. Использование неправильного типа индекса. В зависимости от запроса и типа данных, может потребоваться использовать различные типы индексов, такие как обычные индексы, уникальные индексы или полнотекстовые индексы. Убедитесь, что вы выбрали правильный тип индекса для вашего случая.
3. Устаревшие статистические данные. Иногда база данных может иметь устаревшие статистические данные, что может привести к неправильному выбору плана выполнения запроса и игнорированию индексов. Попробуйте обновить статистику базы данных с помощью команды ANALYZE:
ANALYZE TABLE_NAME;ANALYZE table_name;
4. Слишком малое количество данных. Если таблица содержит слишком мало записей, оптимизатор запросов может принять решение не использовать индекс. В таком случае, попробуйте добавить больше данных для тестирования срабатывания индекса.
5. Неправильно составлен запрос. Проверьте ваш SQL запрос на наличие ошибок и оптимизируйте его, если это необходимо. Иногда неправильно составленные запросы могут привести к игнорированию индексов.
Надеюсь, что эти советы помогут вам решить проблему с отсутствием срабатывания индекса при SQL запросе. Если проблема сохраняется, пожалуйста, предоставьте больше информации о вашей ситуации, чтобы мы могли помочь вам более точно.