Что такое индексы в базе данных и какой индекс используется по умолчанию?

Question

Определение: индекс в базе данных — это дополнительная структура данных, которая ускоряет поиск строк по одному или нескольким столбцам.Без индексов база данных вынуждена просматривать всю таблицу целиком (full scan), что медленно на больших объемах данных. Индексы устроены так, чтобы быстро находить нужные значения, обычно через структуры вроде B-деревьев. В большинстве реляционных СУБД по умолчанию используется индекс типа B-tree (например, при создании PRIMARY KEY или UNIQUE ограничения). Такой индекс хорошо подходит для точного поиска, диапазонных запросов и сортировки. Однако индексы занимают место и замедляют операции записи, поэтому их нужно использовать разумно.

YeaHub · Accepted Answer

1. Что такое индекс в базе данныхОпределение:Индекс в базе данных — это вспомогательная структура данных (отдельная от основной таблицы), которая позволяет быстрее находить строки по значениям одного или нескольких столбцов.Основная идея индекса:Таблица хранит сами данные (строки).Индекс хранит:упорядоченные значения индексируемого столбца(ов),ссылки (указатели) на реальные строки таблицы.При запросе по проиндексированному столбцу СУБД сначала ищет в индексе, а уже потом обращается к таблице.Что происходит без индекса:База данных выполняет полное сканирование таблицы: проверяет каждую строку.Время выполнения растет почти линейно с количеством строк.Что дает индекс:Сильно ускоряет:поиск по WHERE column = value,диапазонные запросы BETWEEN, >, <,сортировку ORDER BY.Но:занимает дополнительное место на диске,замедляет INSERT, UPDATE, DELETE, потому что индекс тоже нужно обновлять.2. Типы индексов и индекс по умолчаниюВо многих реляционных базах данных (PostgreSQL, MySQL, MariaDB и других) индекс по умолчанию — это B-tree (B-дерево).Определение:B-tree индекс — это индекс, основанный на сбалансированном дереве, где ключи хранятся в отсортированном виде, что позволяет выполнять быстрый поиск, вставку и удаление за логарифмическое время.Почему B-tree используется по умолчанию:Поддерживает:точный поиск =,диапазоны >=, <=, BETWEEN,сортировку ORDER BY column.Хорошо сбалансирован для большинства типичных запросов.Универсальный компромисс между скоростью и универсальностью.Когда создается индекс автоматически:При объявлении:PRIMARY KEY — создается уникальный индекс.UNIQUE — также создается уникальный индекс.Типом по умолчанию почти всегда будет B-tree, если явно не указан другой тип.3. Простой пример с индексомSQL-- Создадим таблицу пользователей CREATE TABLE users ( id SERIAL PRIMARY KEY, -- автоматически создается B-tree индекс по id email TEXT UNIQUE, -- автоматически создается уникальный B-tree индекс created_at TIMESTAMP ); -- Явное создание индекса на поле created_at CREATE INDEX idx_users_created_at ON users (created_at); -- по умолчанию B-tree Что происходит при запросе:SQLSELECT * FROM users WHERE email = 'test@example.com'; СУБД:Находит значение 'test@example.com' в уникальном B-tree индексе по email.Получает ссылку на конкретную строку.Считывает только нужную строку из таблицы.4. Когда индексы полезны и когда вредятИндексы особенно полезны:Для столбцов, которые часто используются в:WHERE,JOIN,ORDER BY,GROUP BY.Для больших таблиц (десятки тысяч строк и больше).Индексы могут мешать, если:Таблица маленькая — полное сканирование и так быстро.Столбец почти одинаковый у всех строк (низкая селективность, например is_active с 99% значений true).Таблица очень интенсивно изменяется:много INSERT/UPDATE/DELETE,каждый индекс нужно обновлять.5. ВыводИндекс — это способ ускорить выборки за счет дополнительной структуры данных.В большинстве реляционных СУБД индекс по умолчанию — B-tree.Индексы особенно важны для больших таблиц и частых фильтраций/сортировок.Но они не бесплатны: расходуют место и замедляют записи, поэтому их нужно добавлять осознанно.