Каковы преимущества и недостатки B-Tree индекса?

B-Tree (и его вариант B+Tree, используемый в большинстве СУБД) — это сбалансированное дерево поиска, наиболее распространенный тип индекса в реляционных базах данных.

Преимущества B-Tree индексов:

• Эффективность для диапазонных запросов: Благодаря упорядоченному хранению значений B-Tree отлично подходит для операторов >, <, BETWEEN, а также для ORDER BY и GROUP BY.
• Поддержка префиксного поиска: Индекс может быть использован для запросов с LIKE 'pattern%' (поиск по префиксу).
• Высокая производительность для высокоселективных столбцов: Когда индекс фильтрует большую часть строк (например, поиск по уникальному email).
• Универсальность: Поддерживается всеми основными СУБД (PostgreSQL, MySQL, SQL Server) и часто является индексом по умолчанию.

Недостатки B-Tree индексов:

• Объем: Занимают значительное дисковое пространство, так как хранят не только ключи, но и служебные данные структуры дерева.
• Стоимость операций записи: Вставка, обновление или удаление строк требуют перебалансировки дерева, что может быть затратной операцией для таблиц с высокой частотой записей (write-heavy).
• Неэффективность для некоторых паттернов: Не подходит для полнотекстового поиска или запросов с LIKE '%pattern' (поиск по суффиксу), так как упорядоченность по префиксу не помогает.

Пример создания и использования в PostgreSQL:

-- Создание B-Tree индекса (по умолчанию)
CREATE INDEX idx_users_created_at ON users(created_at);

-- Запрос, который эффективно использует этот индекс
SELECT * FROM users WHERE created_at >= '2024-01-01' ORDER BY created_at;
-- Индекс поможет и в фильтрации (WHERE), и в сортировке (ORDER BY).

-- Запрос, где индекс НЕ поможет (не префикс)
SELECT * FROM users WHERE email LIKE '%@example.com';

Выбор B-Tree индекса — это разумный компромисс по умолчанию для большинства сценариев, где важны как точечные, так и диапазонные запросы.