Сколько может быть некластерных индексов?

Сколько может быть некластерных индексов?

Краткий ответ

В SQL Server и большинстве СУБД нет жёсткого ограничения на количество некластерных индексов (non-clustered indexes). Однако на практике рекомендуется иметь:

• PostgreSQL: неограниченное количество
• SQL Server: до 999 некластерных индексов на таблицу (теоретическое ограничение)
• MySQL: неограниченное, но на практике 5-10

Кластерные vs Некластерные индексы

Кластерные индексы (Clustered Index)

-- В таблице может быть ВСЕ ОДНОГО кластерного индекса!
-- Это физический порядок строк в таблице

CREATE CLUSTERED INDEX idx_pk_user ON users(id);
-- Иначе: PRIMARY KEY автоматически создаёт clustered index

-- Обычно это первичный ключ
@Entity
@Table(name = "users")
public class User {
    @Id  // Это создаст clustered index
    private Long id;
}

Некластерные индексы (Non-Clustered Index)

-- Может быть МНОГО некластерных индексов
-- Это дополнительные структуры данных для быстрого поиска

CREATE NONCLUSTERED INDEX idx_email ON users(email);
CREATE NONCLUSTERED INDEX idx_created_at ON users(created_at);
CREATE NONCLUSTERED INDEX idx_name ON users(name);
CREATE NONCLUSTERED INDEX idx_status ON users(status);

-- Комбинированные индексы (composite)
CREATE NONCLUSTERED INDEX idx_status_created 
    ON users(status, created_at) INCLUDE (name);

Визуальное объяснение

Таблица users:
  id  | name    | email           | status | created_at
------|---------|-----------------|--------|----------
  1   | Alice   | alice@ex.com    | active | 2025-01-01
  2   | Bob     | bob@ex.com      | active | 2025-01-02
  3   | Charlie | charlie@ex.com  | inactive | 2025-01-03

Кластерный индекс (PRIMARY KEY на id):
  id → [1] → [2] → [3]  (физический порядок в памяти)

Некластерные индексы:
  email:
    alice@ex.com → id:1
    bob@ex.com → id:2
    charlie@ex.com → id:3

  created_at:
    2025-01-01 → id:1
    2025-01-02 → id:2
    2025-01-03 → id:3

  status:
    active → [id:1, id:2]
    inactive → [id:3]

PostgreSQL пример

-- В PostgreSQL нет строгого разделения clustered/non-clustered
-- Но применяются похожие концепции

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,  -- Clustered index (B-tree)
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    status VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE
);

-- Создаём несколько индексов
CREATE INDEX idx_email ON users(email);  -- Для поиска по email
CREATE INDEX idx_status ON users(status);  -- Для фильтрации по статусу
CREATE INDEX idx_created_at ON users(created_at);  -- Для сортировки
CREATE INDEX idx_status_created ON users(status, created_at);  -- Composite

-- Специальные индексы
CREATE INDEX idx_email_lower ON users(LOWER(email));  -- Case-insensitive
CREATE INDEX idx_search ON users USING gin(to_tsvector('russian', name));  -- Full-text

-- Проверяем индексы
\d users  -- Покажет все индексы

Java + Hibernate пример

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;  // Clustered index (PRIMARY KEY)
    
    @Column(unique = true)
    @Index(name = "idx_email")  // Non-clustered index
    private String email;
    
    @Column
    @Index(name = "idx_status")  // Non-clustered index
    private String status;
    
    @Column
    @Index(name = "idx_created_at")  // Non-clustered index
    private LocalDateTime createdAt;
    
    // Composite index
    @Table(name = "users", indexes = {
        @Index(name = "idx_status_created", columnList = "status,created_at"),
        @Index(name = "idx_email_status", columnList = "email,status")
    })
}

Оптимальное количество индексов

Плохо: Слишком мало индексов

-- Только PRIMARY KEY
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    status VARCHAR(20),
    created_at TIMESTAMP,
    amount DECIMAL(10,2)
);

-- Проблема: медленные запросы
SELECT * FROM orders WHERE user_id = 1;  -- FULL TABLE SCAN!
SELECT * FROM orders WHERE status = 'pending';  -- FULL TABLE SCAN!
SELECT * FROM orders WHERE created_at > '2025-01-01';  -- FULL TABLE SCAN!

Хорошо: Правильное количество

-- Добавляем индексы на часто ищущиеся поля
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- Теперь запросы быстрые
SELECT * FROM orders WHERE user_id = 1;  -- INDEX SEEK
SELECT * FROM orders WHERE status = 'pending';  -- INDEX SEEK

Плохо: Слишком много индексов

-- Индекс на ВСЕХ колонках
CREATE INDEX idx_id ON orders(id);       -- Лишний! Есть PK
CREATE INDEX idx_amount ON orders(amount);  -- Редко ищут по amount
CREATE INDEX idx_description ON orders(description);  -- На text поле!
CREATE INDEX idx_notes ON orders(notes);  -- Ещё один text индекс

-- Проблемы:
-- 1. INSERT/UPDATE становятся медленнее (нужно обновить все индексы)
-- 2. Больше памяти на индексы
-- 3. Плановщик запросов может выбрать неправильный индекс
-- 4. Сложнее maintain базу

Практические рекомендации

Шаг 1: Анализ query patterns

// Какие запросы выполняются часто?
public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByUserId(Long userId);  // Частый запрос → нужен индекс
    
    List<Order> findByStatus(String status);  // Частый запрос → нужен индекс
    
    List<Order> findByCreatedAtBetween(LocalDateTime from, LocalDateTime to);
    // Частый запрос → нужен индекс
    
    List<Order> findByDescription(String desc);  // Редкий запрос → может быть без индекса
}

Шаг 2: Создание индексов

-- На основе step 1 создаём индексы
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- Composite индекс для часто вместе используемых полей
CREATE INDEX idx_status_created ON orders(status, created_at);

Шаг 3: Мониторинг

-- PostgreSQL: посмотреть статистику индексов
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- MySQL: посмотреть неиспользованные индексы
SELECT object_schema, object_name, count_star
FROM performance_schema.table_statistics
WHERE count_star = 0 AND object_schema != 'mysql';

На собеседовании

Хороший ответ:

"Количество некластерных индексов технически неограниченно (в PostgreSQL), или доходит до 999 в SQL Server. Однако на практике рекомендуется иметь 5-10 индексов на среднюю таблицу.

Главные рекомендации:

1. Индекс нужен на колонки, которые часто используются в WHERE, JOIN, ORDER BY
2. Composite индексы полезны для запросов типа (status = ? AND created_at > ?)
3. Слишком много индексов замедляют INSERT/UPDATE операции
4. Нужно мониторить — использует ли плановщик созданные индексы

Лучший подход: сначала пишем приложение без лишних индексов, потом на основе EXPLAIN ANALYZE добавляем индексы там, где они действительно нужны."