13 вопросов
Индексы ускоряют поиск и сортировку за счет структуры данных (обычно B-tree), позволяющей не сканировать всю таблицу. Ускоряют WHERE, JOIN, ORDER BY, GROUP BY. Минусы: место на диске, замедление INSERT/UPDATE/DELETE (обновление индекса), выбор оптимального набора индексов - компромисс.
B-tree - сбалансированное дерево: данные в листьях, ключи в узлах для навигации. Поиск, вставка, удаление - O(log N). Данные в листьях отсортированы - удобно для диапазонов (BETWEEN, >, ORDER BY). В PostgreSQL и MySQL индексы по умолчанию - B-tree. Подходит для сравнений и сортировки по одному или нескольким столбцам.
Индекс по нескольким столбцам. Порядок столбцов важен: индекс (a, b, c) может использоваться для запросов по (a), (a, b), (a, b, c), но не эффективно для (b) или (c). Правило: сначала столбцы с равенством (=), затем диапазон/сортировка. Пример: WHERE status = 'active' ORDER BY created_at - индекс (status, created_at).
Индекс строится только по подмножеству строк (условие WHERE при создании индекса). Меньше размер, быстрее обновление, подходит для запросов с тем же условием. В PostgreSQL: CREATE INDEX ... WHERE status = 'active'; В MySQL частичных индексов нет, но можно эмулировать отдельной таблицей или индексами по полю с малым числом значений.
Индекс, в котором есть все столбцы, нужные для запроса (Index-Only Scan). Запрос выполняется без обращения к таблице - только к индексу. В PostgreSQL INCLUDE добавляет столбцы в листья индекса без участия в порядке сортировки. Уменьшает I/O, ускоряет запросы с малым набором столбцов.
CREATE INDEX idx ON orders (user_id) INCLUDE (total, status);Индекс строится не по столбцу, а по выражению (функция от столбца). Используется когда запросы фильтруют по выражению. Пример: WHERE lower(email) = 'a@b.com' - индекс CREATE INDEX ON users (lower(email)). В PostgreSQL поддерживаются; в MySQL - только по столбцам (можно виртуальный столбец с выражением и индекс по нему).
Ограничение уникальности: в таблице не может быть двух строк с одинаковым значением (или комбинацией) в индексированных столбцах. NULL обычно не считается дубликатом (в PG несколько NULL допустимы в уникальном индексе). Используется для естественных ключей (email, slug) и для поддержки UNIQUE constraint. Одновременно ускоряет поиск по этому полю.
Индекс по хеш-функции от ключа. Подходит только для точного совпадения (=), не для диапазонов и ORDER BY. O(1) в среднем для поиска. В PostgreSQL hash-индекс редко выигрывает у B-tree; в MySQL MEMORY-таблицы используют hash по умолчанию. Для точечных запросов по ключу иногда выгоден.
GIN (Generalized Inverted Index) - для полнотекстового поиска, массивов, JSONB (содержит, @>, ?). Построен по элементам (токенам); эффективен когда один документ дает много ключей. GiST (Generalized Search Tree) - для геоданных, диапазонов, полнотекста; может давать false positives, нужна проверка по таблице. Выбор зависит от типа данных и операторов.
Каждый INSERT/UPDATE/DELETE должен обновить все затронутые индексы. Больше индексов - больше работы при записи и больше места. Много индексов на часто обновляемой таблице - узкое место. Баланс: индексы только под реальные запросы, избегать дублирования (два индекса с пересекающимся набором столбцов). Мониторинг: slow query log, pg_stat_user_indexes.
Оптимизатор оценивает стоимость планов (стоимость = I/O + CPU), использует статистику по таблицам и индексам (число строк, распределение). Выбирает план с минимальной оценкой. Важно: актуальная статистика (ANALYZE в PG, ANALYZE TABLE в MySQL). Подсказки (hints) в крайних случаях переопределяют выбор. Неправильная оценка - часто из-за устаревшей статистики или сложных предикатов.
Seq Scan (Full Table Scan) - последовательное чтение всей таблицы. Когда данных мало или большая доля строк подходит под условие - может быть выгоднее индекса. Index Scan - обход индекса, по индексу получают ссылки на строки, затем чтение таблицы (heap). Index Only Scan - данные берутся только из индекса (covering). Bitmap Index Scan - по индексу строят битовую карту строк, затем читают таблицу по порядку (для нескольких условий).
EXPLAIN выводит план; EXPLAIN ANALYZE выполняет запрос и дает фактические строки и время. Смотреть: type (ALL - full scan; index, range - использование индекса); rows (оценка/факт); key - какой индекс; Extra - Using temporary, Using filesort, Using index (covering). Высокая разница между estimated и actual rows - повод обновить статистику или переписать запрос.