churakovmike
2024-06-16
Решаем задачи с собеседований #1
На интервью довольно часто встречается вопросы связанные с индексами, но иногда могут спросить что-то более хитрое, например - "почему при использовании оператора BETWEEN могут не использоваться индексы?". Давайте разберемся, почему же?

Для начала, вспомним, что оператор between необходим для поиска записей в диапазоне. Например, чтобы найти пользователей, ID которых больше 200 и меньше 500:

select * from users where id between 200 and 500;

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

select * from users where created_at between '2024-02-11' and '2024-03-10';

Теперь самое время вернуться к поднятой раннее теме индексов, что особенного может скрываться за between? Для начала, для обоих приведенных запросов сделаем анализ запроса:

explain select * from users
 where id between 200 and 500;
 
explain select * from users
 where created_at between '2024-02-11' and '2024-03-10';

Для первого запроса, в результате анализа, увидим примененный индекс - PRIMARY, так и должно быть если мы делаем выборку по условиям связанным с первичным ключом. Однако, если посмотреть на результат анализа второго запроса, то в нем будет отсутствовать примененный индекс. В первую очередь, это потому, что индекс действительно нужно добавить. Если его нет на таблице - то и в запросе он участвовать не будет:

CREATE INDEX users_created_at ON users (created_at);

После добавления индекса снова проводим анализ запроса с between по датам, и снова видим, что индекс не применяется. На это может повлиять несколько факторов:

- в таблице users очень мало записей, и внутренний оптимизатор запросов решил не применять индекс
- неправильно добавлен индекс
- у нас версия mysql, в которой between не поддерживает индексы

Первый и второй пункт сразу отметаем, табличка не пустая, в ней хранится около миллиона записей, значит дело в операторе between. При работе с версией mysql < 5.7, факт того, что индекс не применяется  еще нужно было как то выяснить и осознать. Это вносило определенные неудобства, и, не зная об этой особенности, можно провести ни один час гугления. Именно этот нюанс и является ответом интервьюеру на собеседовании. Но зная проблематику - необходимо привести примеры ее решения, а их несколько:

Способ №1: перейти на новую версию mysql - самый простой ответ, но не самый простой в реализации. Чтобы перевести СУБД на новую версию необходимо ее детально разобрать, и найти все свои функции, триггеры, запросы внутри приложений, убедиться, что все совместимо, и привести к правильному виду. Это большое количество человеко-часов.

Способ №2: Заставить mysql насильно применять индексы - в любом запросе можно описать какой индекс должен использоваться, однако все равно остается вероятность его игнорирования оптимизатором:

select * from users
 FORCE INDEX (users_created_at)
  where created_at >= '2024-02-10' and created_at <= '2024-03-10'

Способ №3: Отказаться от between и переписать запрос на простые условия сравнения:

select * from users where created_at between '2024-02-11' and '2024-03-10';

 

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

👁 50