Зачем нужны индексы в базах данных?

Давай разберёмся с этими вашими индексами, а то народ часто нихуя не понимает, зачем они и почему всё тормозит.

Представь себе, блядь, толстенную книгу, типа «Война и мир», но без оглавления и алфавитного указателя в конце. Тебе надо найти, где там про Наташу Ростову. И ты начинаешь листать, ёпта, с первой страницы, читая каждую строчку. Это и есть FULL TABLE SCAN — полный перебор всей таблицы. Долго, муторно, ресурсов жрёт овердохуища.

А теперь представь, что к этой книге приклеили нормальное оглавление. Хочешь про Наташу — бац, смотришь в оглавление, и тебя сразу кидают на нужную главу. Вот это и есть индекс, ёбана-мать! Он не хранит весь текст книги, а только ключи (например, last_name) и ссылки на страницы (строки в таблице). И ищет он не перебором, а по умному алгоритму, чаще всего по B-дереву — за логарифмическое время. Разница — как ехать на велосипеде или на реактивном самолёте.

Чем они, сука, хороши:

• Скорость, блядь! Запросы с WHERE, JOIN, ORDER BY начинают летать, особенно на больших таблицах. Удивление пиздец наступает, когда запрос из 10 секунд превращается в 10 миллисекунд.
• Могут гарантировать уникальность. Создал уникальный индекс на email — и никаких двух одинаковых почтовых ящиков, всё, вопрос решён.
• Сортировку (ORDER BY) и группировку (GROUP BY) тоже ускоряют, если порядок в индексе совпадает с тем, что ты просишь в запросе.

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

• Замедление записи. Каждый раз, когда ты вставляешь новую строку (INSERT) или обновляешь старую (UPDATE), база должна не только таблицу поправить, но и ВСЕ связанные индексы обновить. Представь, что ты в ту самую книгу вклеиваешь новую главу — теперь надо переписать оглавление, указатель, всё нахуй. Отсюда мораль: на таблицу, куда данные льются потоком (логи, например), навешивать индексы надо с большой, блядь, осторожностью.
• Место на диске жрут. Индекс — это отдельная структура данных. Чем больше полей в индексе и чем больше сама таблица — тем жирнее будет индексный файл.
• За ними надо следить. Со временем индексы фрагментируются, как файлы на жёстком диске в 2002-м году. Их периодически надо перестраивать (типа REINDEX), иначе эффективность падает.

Вот смотри, как это выглядит в коде:

-- Допустим, у нас таблица users, и все ищут по почте. Делаем индекс.
CREATE INDEX idx_users_email ON users(email);

-- Теперь этот запрос не будет сканировать всю таблицу, а прыгнет сразу по индексу.
SELECT * FROM users WHERE email = 'alice@example.com';

-- А можно сделать составной индекс, если часто ищешь по двум полям.
CREATE INDEX idx_users_name_department ON users(last_name, department_id);
-- Этот индекс сработает, если ты ищешь по фамилии ИЛИ по фамилии + отделу.
-- Или если сортируешь именно в таком порядке: ORDER BY last_name, department_id.

А когда индексы — это мудя и пустая трата времени?

• На маленьких таблицах. Если в таблице 100 строк, то база просканирует её быстрее, чем сообразит, куда по индексу прыгать. Доверия ебать ноль к индексу на такой мелочи.
• На столбцах с низкой селективностью. Что такое «низкая селективность»? Ну, например, колонка gender со значениями 'M' и 'F'. Индекс отфильтрует максимум половину таблицы, а толку-то? Проще уж всю таблицу прочитать.
• На таблицах, которые только пишутся. Лог-файлы, очереди событий. Каждая вставка будет тормозить из-за необходимости обновлять индекс. Иногда проще без него, а читать потом партициями.

Итог, ёпта: Индексы — это не серебряная пуля, которую надо навешивать на всё подряд. Это тонкий инструмент. Создавать их нужно с умом, предварительно посмотрев, какие запросы у тебя реально тормозят (смотри EXPLAIN план и логи медленных запросов). Баланс между скоростью чтения и скоростью записи — вот где собака, сука, зарыта. Сделал не те индексы — получишь, что запросы на чтение летают, а система в целом еле дышит, потому что каждое действие по обновлению данных вгоняет её в ступор.