Почему SQL-запрос может быть медленным, даже если используется индекс?

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

1. Низкая селективность индекса. Индекс неэффективен, если он построен по полю с малым количеством уникальных значений (например, пол, статус_заказа с 2-3 вариантами). В этом случае СУБД может решить, что полное сканирование таблицы будет быстрее, чем работа с индексом.

2. Использование функций над индексированным столбцом. Применение функций к столбцу в условии WHERE делает использование индекса невозможным.

   -- ПЛОХО: Индекс по `created_at` не будет использован
   SELECT * FROM orders WHERE YEAR(created_at) = 2023;
   
   -- ХОРОШО: Индекс будет использован
   SELECT * FROM orders WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

3. Индекс не покрывает запрос (Not a Covering Index). Если индекс включает только поля из WHERE, но не все поля из SELECT, СУБД сначала находит нужные записи по индексу, а затем для каждой из них обращается к таблице за остальными данными. Это может быть медленно, если таких записей много.

4. Неявное преобразование типов. Если тип данных в условии запроса не совпадает с типом столбца в таблице, СУБД может быть вынуждена преобразовывать типы, что мешает использованию индекса.

   -- user_id имеет тип INT, а в запросе передается строка. Индекс может не использоваться.
   WHERE user_id = '123'

5. Фрагментация индекса. В таблицах с частыми операциями INSERT, UPDATE, DELETE индекс может стать фрагментированным, что снижает скорость поиска по нему. Требуется его реорганизация (REORGANIZE) или перестроение (REBUILD).

6. Поиск по шаблону с LIKE в начале строки. Индекс не будет использоваться, если шаблон поиска начинается с %.

   -- ПЛОХО: Индекс по `name` не используется (Full Scan)
   WHERE name LIKE '%John%'
   
   -- ХОРОШО: Индекс используется (Index Range Scan)
   WHERE name LIKE 'John%'