Зачем нужны индексы?
Таблицы в БД часто содержат большое количество строк, а нам часто нужно искать строки по определенному критерию, например, по id пользователя или по ИНН компании. Если индекса нет, то база будет последовательно просматривать все записи строчка за строчкой в поисках соответствующих запросу данных. Это может занять много времени.
Индекс — это отдельная служебная структура в базе данных, которая строится из значений одного или нескольких столбцов таблицы и указывает на местоположение соответствующих строк.
Индексы:
➕ ускоряют поиск
➕ ускоряют сортировку (если порядок индекса подходит под ORDER BY)
➕ могут обеспечить отсутствие дубликатов (если это уникальные индексы)
Индекс позволяет быстро найти нужные данные, не просматривая все строки таблицы, потому что представляет собой структуру данных, оптимизированную под поиск (кратко про структуры данных есть в конспекте кабанчика: хэш-индексы, B-деревья, LSM). Есть и другие индексы, например, инвертированный для полнотекстового поиска, R-Tree для геоданных и др.
Индексы обычно создают на тех столбцах таблицы, по которым часто осуществляют поиск или сортировку. Для одной таблицы можно создать несколько индексов, но создавать их "на каждый чих" тоже не стоит, поскольку индексы замедляют добавления и обновления строк. Замедление происходит, потому что кроме добавления/изменения данных в самой таблице, БД приходится еще обновлять информацию в индексе. Индексы также занимают место на диске и в памяти.
Если какой-то запрос тормозит, то первым делом стоит посмотреть с помощью EXPLAIN план запроса: используется ли индекс или последовательное сканирование всех строк.
Кратко о видах индексов:
1️⃣ По уникальности
Уникальные — гарантируют уникальность значений в столбце, часто используются для первичных ключей.
Неуникальные — допускают повторяющиеся значения.
2️⃣ По количеству полей
Простые — построены по одному столбцу (например, id).
Составные — построены по нескольким столбцам таблицы (например, user_login, date).
3️⃣ Покрывающий индекс
Допустим, есть индекс по полям a и b. И мы делаем запрос как раз по этим полям, то есть все нужные нам данные находятся в самом индексе, не нужно обращаться к таблице. Это называется покрывающим индексом.
Но это НЕ свойство самого индекса, а отношение между индексом и конкретным запросом. Один и тот же индекс может быть покрывающим для одного запроса, и не покрывающим для другого.
История из жизни
Одной из фичей в продукте был большой расчет: из БД доставалось большое количество данных, над ними производились операции, результаты снова сохранялись в БД (использовалась PostgreSQL). Однажды мы заметили, что расчет выполняется очень долго, несколько часов. Раньше такого не было. В последнем релизе изменений в этой части не было, но еще раньше — да, были, переделывали логику расчета. Проблему обнаружили не сразу, потому что запускали этот расчет только раз в месяц. Но ведь мы все протестировали! Всё работало корректно.
Разбираемся глубже, и вот один из ребят обнаруживает, что один из запросов в БД очень долго отрабатывает. Проверили с EXPLAIN — делает seq scan вместо использования индекса. Но почему? Ведь мы точно продумывали и заводили все нужные индексы, когда писали эту функциональность.
Продолжение ⬇️