Какие знаешь конкретные названия индексов?

Конкретные названия и типы индексов в базах данных

Индексы — один из самых критических компонентов оптимизации БД. За 10+ лет разработки я видел как плохо спроектированные индексы убивают производительность, а хорошо спроектированные дают 100x ускорение. Разберу все основные типы.

1. Primary Key Index

Основной ключ, гарантирует уникальность каждой строки:

-- SQL определение
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- PostgreSQL
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100)
);

На уровне Java/Hibernate:

import javax.persistence.*;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private String email;
}

Характеристики:

• Создаётся автоматически
• Гарантирует уникальность
• Кластеризованный индекс (в большинстве БД)
• Самый быстрый поиск по id

2. UNIQUE Index

Гарантирует уникальность значений в поле:

-- Создание уникального индекса
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- Или через constraint
ALTER TABLE users ADD CONSTRAINT uc_email UNIQUE(email);

На уровне Hibernate:

@Entity
@Table(name = "users", uniqueConstraints = {
    @UniqueConstraint(columnNames = "email", name = "uc_email")
})
public class User {
    @Id
    private Long id;
    
    @Column(unique = true)
    private String email;
    
    private String name;
}

Использование:

• Email должен быть уникальный
• Username должен быть уникальный
• ISBN в книгах
• Гарантирует целостность данных + быстрый поиск

3. Single Column Index

Индекс на одном столбце:

-- Простой индекс на столбце
CREATE INDEX idx_users_name ON users(name);

-- С сортировкой (DESC)
CREATE INDEX idx_users_created_desc ON users(created_at DESC);

SQL запросы которые ускоряются:

-- Индекс использует
SELECT * FROM users WHERE name = 'John';

-- Индекс использует (range query)
SELECT * FROM users WHERE name LIKE 'Jo%';

-- Индекс может использовать (ORDER BY)
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

На уровне JPA:

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_name", columnList = "name"),
    @Index(name = "idx_created", columnList = "created_at")
})
public class User {
    @Id
    private Long id;
    
    @Column(length = 100)
    private String name;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

4. Composite Index (Multi-Column Index)

Индекс на нескольких столбцах одновременно:

-- Создание composite индекса
-- ВАЖНО: порядок имеет значение!
CREATE INDEX idx_user_status_date ON users(status, created_at DESC);

Использование: Для запроса используется индекс ПОЛНОСТЬЮ:

-- Индекс использует ПОЛНОСТЬЮ
SELECT * FROM users WHERE status = 'active' AND created_at > '2023-01-01';

-- Индекс использует ТОЛЬКО ДЛЯ status (status is first column)
SELECT * FROM users WHERE status = 'active';

-- Индекс НЕ использует (created_at не первый столбец)
SELECT * FROM users WHERE created_at > '2023-01-01';

На уровне JPA:

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_status_date", columnList = "status,created_at")
})
public class User {
    @Id
    private Long id;
    
    @Enumerated(EnumType.STRING)
    private UserStatus status;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

Правило для composite индексов:

Порядок столбцов в индексе = Порядок столбцов в WHERE clause

Главное правило: Equality (=) ПЕРЕД Range/Order
Верно:   WHERE status = 'active' AND created_at > date
Индекс:  (status, created_at)  ✓

Верно:   WHERE country = 'US' AND city = 'NY' AND population > 100k
Индекс:  (country, city, population)  ✓

5. Full Text Index

Для полнотекстового поиска:

-- PostgreSQL full text index
CREATE INDEX idx_articles_text ON articles 
  USING GIN (to_tsvector('english', title || ' ' || content));

-- MySQL
CREATE FULLTEXT INDEX idx_articles_text ON articles(title, content);

Запросы:

-- PostgreSQL
SELECT * FROM articles 
WHERE to_tsvector('english', title || ' ' || content) 
      @@ to_tsquery('english', 'database & performance');

-- MySQL
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('database performance' IN BOOLEAN MODE);

На уровне Hibernate Search:

import org.hibernate.search.mapper.pojo.mapping.definition.annotation.FullTextField;
import org.hibernate.search.mapper.pojo.mapping.definition.annotation.Indexed;

@Entity
@Indexed
@Table(name = "articles")
public class Article {
    @Id
    private Long id;
    
    @FullTextField(analyzer = "english")
    private String title;
    
    @FullTextField(analyzer = "english")
    private String content;
}

6. B-Tree Index (по умолчанию)

Структура данных для большинства индексов:

Больше всего используется для:
- Равенства (=)
- Диапазонов (<, >, <=, >=)
- LIKE 'prefix%'
- ORDER BY
- GROUP BY

НЕ подходит для:
- LIKE '%suffix%'
- Полнотекстовый поиск

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

-- Или явно
CREATE INDEX idx_users_email ON users USING BTREE(email);

7. Hash Index

Для только точного совпадения (не поддерживает диапазоны):

-- PostgreSQL
CREATE INDEX idx_users_status ON users USING HASH(status);

-- MySQL
CREATE INDEX idx_users_status ON users(status) USING HASH;

Характеристики:

• Очень быстро для точного поиска (=)
• НЕ поддерживает <, >, LIKE
• НЕ поддерживает ORDER BY
• Меньше памяти чем B-Tree

-- Индекс использует
SELECT * FROM users WHERE status = 'active';

-- Индекс НЕ использует
SELECT * FROM users WHERE status > 'active';
SELECT * FROM users ORDER BY status;

8. GiST Index (Generalized Search Tree)

Для сложных типов данных:

-- PostgreSQL: для географических данных
CREATE INDEX idx_locations_geom ON locations USING GIST(geometry);

-- Для диапазонов
CREATE INDEX idx_events_period ON events USING GIST(period);

-- Поиск близко к точке
SELECT * FROM locations 
WHERE geometry <-> point '(0,0)' < 100;

-- Поиск пересекающихся диапазонов
SELECT * FROM events 
WHERE period && '[2024-01-01, 2024-02-01]'::tsrange;

9. GIN Index (Generalized Inverted Index)

Для массивов, JSONB, полнотекстовых данных:

-- PostgreSQL: для JSONB
CREATE INDEX idx_users_tags ON users USING GIN(tags);

CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

Запросы:

-- Содержит элемент
SELECT * FROM users WHERE tags @> ARRAY['java', 'python'];

-- JSONB ключ существует
SELECT * FROM users WHERE metadata -> 'role' = '"admin"';

10. Partial Index

Индекс только на подмножество данных:

-- Индекс только на активных пользователей
CREATE INDEX idx_active_users ON users(email) 
WHERE status = 'active';

-- Экономит место и ускоряет вставки
CREATE INDEX idx_deleted_orders ON orders(id)
WHERE deleted_at IS NOT NULL;

Использование:

// Быстрый поиск активных пользователей
SELECT * FROM users WHERE status = 'active' AND email LIKE 'john%';

// Поиск удалённых заказов
SELECT * FROM orders WHERE deleted_at IS NOT NULL;

11. Expression Index

Индекс на результат выражения:

-- PostgreSQL: индекс на нижний регистр
CREATE INDEX idx_users_name_lower ON users(LOWER(name));

-- Индекс на вычисленное значение
CREATE INDEX idx_users_age_group ON users((age / 10));

Запросы:

-- Использует индекс
SELECT * FROM users WHERE LOWER(name) = 'john';

-- Использует индекс на возрастную группу
SELECT * FROM users WHERE (age / 10) = 2;

12. Covering Index

Индекс включает все нужные столбцы (Index-Only Scan):

-- PostgreSQL
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE(total_amount, status);

-- MySQL - можно включить в key
CREATE INDEX idx_orders ON orders(user_id, total_amount);

Выигрыш:

-- Без covering индекса:
SELECT total_amount, status FROM orders WHERE user_id = 123;
-- Шаги: 1) index seek по user_id 2) lookup в основной таблице за данными

-- С covering индексом:
SELECT total_amount, status FROM orders WHERE user_id = 123;
-- Шаги: 1) index seek по user_id (ВСЕ ДАННЫЕ УЖЕ В ИНДЕКСЕ)

Практический пример: оптимизация реальной таблицы

@Entity
@Table(name = "orders", indexes = {
    // Primary key (автоматический)
    
    // Основной поиск
    @Index(name = "idx_user_id", columnList = "user_id"),
    
    // Многостолбечный индекс
    @Index(name = "idx_user_status_date", 
            columnList = "user_id,status,created_at"),
    
    // Уникальность
    @Index(name = "idx_order_number_unique", 
            columnList = "order_number", unique = true),
    
    // Partial index эквивалент (через discriminator)
    @Index(name = "idx_active_orders", 
            columnList = "user_id,total_amount")
})
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @ManyToOne
    private User user;  // Индекс на это поле
    
    @Enumerated(EnumType.STRING)
    private OrderStatus status;
    
    @Column(unique = true)
    private String orderNumber;
    
    private BigDecimal totalAmount;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

// Запросы которые ускоряются:
// 1. SELECT * FROM orders WHERE user_id = ?
// 2. SELECT * FROM orders WHERE user_id = ? AND status = 'PENDING'
// 3. SELECT * FROM orders WHERE order_number = ?
// 4. SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC

Анализ индексов в PostgreSQL

-- Какие индексы существуют
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- План запроса
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

-- Неиспользуемые индексы
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Размер индекса
SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Лучшие практики

1. Всегда создавай индекс на Foreign Keys

   ALTER TABLE orders ADD INDEX idx_user_id(user_id);
   

2. Composite индексы: Equality ПЕРЕД Range

   Правильно:   (status, created_at)     для WHERE status = ? AND created_at > ?
   Неправильно: (created_at, status)     это не ускорит запрос на status
   

3. Используй EXPLAIN для проверки индексов

   EXPLAIN ANALYZE SELECT ...; -- Проверить какой индекс используется
   

4. Не создавай лишние индексы

   • Каждый индекс замедляет INSERT/UPDATE/DELETE
   • Занимает место на диске
   • Надо поддерживать

5. Проверяй неиспользуемые индексы

   • Удали которые не используются
   • Экономь место и ускоряй модификации

6. Partial Index для большого процента данных

   • Если индексируешь 90% строк, лучше полный индекс
   • Если 5% — лучше partial

Итог: Правильные индексы могут ускорить запросы в 100+ раз, но неправильные могут замедлить всю БД. Всегда анализируй планы запросов!