Какой тип индекса лучше использовать для поиска по primary key при джойне (B-tree vs hash)?

Основная концепция

При выполнении операции JOIN база данных часто использует индексы для ускорения поиска совпадающих строк. Primary key обычно автоматически индексируется, и выбор типа индекса влияет на производительность. B-tree (сбалансированное дерево) — наиболее распространённый тип индекса, который поддерживает как точный поиск по равенству, так и диапазонные запросы (>, <, BETWEEN). Hash индекс хранит хеш-коды ключей и работает только для операций сравнения на равенство (=).

Почему B-tree предпочтительнее

• B-tree поддерживает упорядоченное сканирование, что полезно при сортировке результатов JOIN или при использовании GROUP BY.
• Многие СУБД (PostgreSQL, MySQL с InnoDB) используют B-tree по умолчанию для primary key, так как он эффективен для большинства сценариев.
• Hash индекс не может быть использован для операций сравнения, кроме равенства, и не поддерживает индексацию для составных ключей в полной мере.

Пример кода

-- Создание таблиц с primary key (автоматически B-tree в PostgreSQL)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- JOIN с использованием B-tree индекса по primary key
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 100;

-- Для hash индекса (PostgreSQL):
CREATE INDEX hash_idx ON users USING hash (id);
-- Но такой индекс не ускорит JOIN с диапазоном

Вывод

B-tree индекс является универсальным и предпочтительным выбором для primary key при выполнении JOIN, так как он обеспечивает высокую производительность для точного поиска и поддерживает дополнительные операции, необходимые в реальных запросах. Hash индекс стоит использовать только в специфических случаях, когда гарантированно выполняются только точные сравнения на равенство и нет необходимости в сортировке или диапазонных условиях.