Какие плюсы и минусы индекса в БД?

Какие плюсы и минусы индекса в БД?

Индекс — это структура данных, которая ускоряет поиск в таблице за счёт дополнительного хранения и обслуживания. Как и всё в разработке, индексы имеют компромиссы.

Плюсы индексов

1. Ускорение поиска (основное преимущество)

-- БЕЗ индекса - полный скан таблицы O(n)
SELECT * FROM Users WHERE Email = 'john@example.com';  -- 10 сек (1M строк)

-- С индексом на Email - бинарный поиск O(log n)
CREATE INDEX idx_users_email ON Users(Email);
SELECT * FROM Users WHERE Email = 'john@example.com';  -- 1 мс

2. Ускорение сортировки и фильтрации

-- ORDER BY работает быстрее с индексом
SELECT * FROM Users ORDER BY CreatedDate DESC;  -- Использует индекс

-- JOIN работает быстрее
SELECT u.* FROM Users u
JOIN Orders o ON u.Id = o.UserId  -- Индекс на UserId
WHERE o.Status = 'active';

3. Поддержка уникальности

CREATE UNIQUE INDEX idx_users_email_unique ON Users(Email);
-- Гарантирует, что Email уникален в таблице

4. Ускорение агрегаций

CREATE INDEX idx_users_status_createdate ON Users(Status, CreatedDate);

-- Это быстрее
SELECT Status, COUNT(*) FROM Users
WHERE CreatedDate > '2025-01-01'
GROUP BY Status;

Минусы индексов

1. Дополнительный расход памяти

-- Для таблицы из 1M строк
CREATE INDEX idx_users_email ON Users(Email);
-- Индекс занимает ~100 MB дополнительной памяти

-- Много индексов на одной таблице - растёт потребление памяти

2. Замедление INSERT, UPDATE, DELETE

-- БЕЗ индексов - быстро
INSERT INTO Users VALUES (...)  -- 1 мс

-- С 5 индексами - медленнее
-- БД должна обновить все индексы
INSERT INTO Users VALUES (...)  -- 5 мс

--批量 вставки страдают больше
INSERT INTO Users SELECT ... FROM TempUsers  -- 50% медленнее с индексами

3. Усложнение плана выполнения

-- С множеством индексов оптимизатор может выбрать неправильный индекс
SELECT * FROM Orders
WHERE UserId = 1  -- Есть индекс
  AND Status = 'active'  -- Есть индекс
  AND CreatedDate > '2025-01-01'  -- Есть индекс
-- Оптимизатор должен выбрать ОДИН индекс (или комбинацию)
-- Неправильный выбор = медленный запрос

4. Синхронизация индекса при изменениях

-- Если данные в таблице изменились
UPDATE Users SET Email = 'newemail@example.com' WHERE Id = 1;
-- БД должна обновить ВСЕ индексы, содержащие Email
-- Это IO операция, замедляет обновление

Типы индексов

Primary Key (кластерный индекс)

CREATE TABLE Users
(
    Id INT PRIMARY KEY,  -- Кластерный индекс
    Email NVARCHAR(255),
    Name NVARCHAR(255)
);
-- Таблица отсортирована по Id, очень быстрый поиск

Non-clustered (некластерный индекс)

CREATE INDEX idx_email ON Users(Email);  -- Отдельная структура
-- Указывает на первичный ключ, затем ищет в таблице

Composite Index (индекс по нескольким столбцам)

CREATE INDEX idx_user_date_status ON Users(UserId, CreatedDate, Status);
-- Быстро ищет по всем трём столбцам
-- Но НЕ поможет для поиска только по Status (порядок важен!)

Full-text Index

CREATE FULLTEXT INDEX ON Articles(Content);
SELECT * FROM Articles WHERE CONTAINS(Content, 'database');

Практический пример

// EF Core
public class User
{
    public int Id { get; set; }
    
    [Index]  // Простой индекс
    public string Email { get; set; }
    
    [Index(nameof(Status), nameof(CreatedDate))]  // Composite индекс
    public string Status { get; set; }
    public DateTime CreatedDate { get; set; }
}

// Migration
public partial class AddUserIndexes : Migration
{
    protected override void Up(MigrationBuilder mb)
    {
        mb.CreateIndex(
            name: "idx_users_email",
            table: "Users",
            column: "Email",
            unique: true);

        mb.CreateIndex(
            name: "idx_users_status_date",
            table: "Users",
            columns: new[] { "Status", "CreatedDate" });
    }
}

Когда добавлять индекс?

✅ ДОБАВЛЯЙ индекс:

• На столбцы, по которым часто ищешь (WHERE)
• На столбцы, по которым часто сортируешь (ORDER BY)
• На внешние ключи (JOIN)
• На столбцы, которые часто группируешь (GROUP BY)

SELECT * FROM Orders WHERE UserId = 1;  -- Индекс на UserId
SELECT * FROM Orders ORDER BY CreatedDate;  -- Индекс на CreatedDate
SELECT * FROM Orders WHERE Status IN ('active', 'pending');  -- Индекс на Status

❌ НЕ ДОБАВЛЯЙ индекс:

• На столбцы с низкой селективностью (много дублей)
• На булевы столбцы (IsActive, IsDeleted)
• На столбцы, которые редко используются
• На маленькие таблицы (< 10K строк)

-- Индекс на IsActive - почти бесполезен (только 2 значения)
CREATE INDEX idx_is_active ON Users(IsActive);

-- Индекс на маленькой таблице
CREATE TABLE SmallLookup(Id INT, Name NVARCHAR(100));  -- 50 строк
CREATE INDEX idx_name ON SmallLookup(Name);  -- Избыточно

Мониторинг индексов

-- SQL Server: найти неиспользуемые индексы
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.user_updates,
    s.user_seeks
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id
WHERE s.user_seeks = 0 AND s.user_updates > 100;
-- Индекс обновляется, но никогда не используется для поиска - удали!

-- Найти фрагментированные индексы
SELECT 
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id
WHERE ps.avg_fragmentation_in_percent > 10;
-- Переиндексируй или пересоберите индекс

Оптимизация индексов

-- Включаемые столбцы (covering index)
CREATE INDEX idx_orders_userid_amount 
ON Orders(UserId) INCLUDE (Amount, CreatedDate);
-- Запрос может быть полностью обслужен индексом

SELECT Amount, CreatedDate FROM Orders
WHERE UserId = 1;  -- Не нужен доступ к таблице!

-- Фильтрованный индекс
CREATE INDEX idx_active_orders 
ON Orders(UserId) WHERE Status = 'active';
-- Меньше размер, быстрее обновление

Таблица: Плюсы vs Минусы

Золотое правило

Индекс окупается, если:

• Количество SELECT запросов >> UPDATE/DELETE
• Таблица большая (> 100K строк)
• Селективность столбца > 80% (разные значения)

Не добавляй индекс "для подстраховки" — измеряй и анализируй!

// В ASP.NET, если запрос медленный:
// 1. Запроси EXPLAIN PLAN у БД
// 2. Узнай, сколько строк сканируется
// 3. Добавь индекс только если нужен
// 4. Проверь, что индекс использует оптимизатор