kostyl.dev
Ef Core

Індекси, Обмеження та Схема (Частина 1)

Глибокий розбір індексів в EF Core — HasIndex, унікальні індекси, складені, фільтровані, covering індекси. Як B-tree індексує дані, коли індекс допомагає, а коли шкодить. Primary Key vs Alternate Key vs Unique Index.

Індекси, Обмеження та Схема

Чому індекси — це не просто «прискорення запиту»

Уявіть телефонну книгу. Якщо сторінки не відсортовані — щоб знайти «Іваненко Олексій» — потрібно перегорнути кожну сторінку від початку до кінця. Це sequential scan (повне сканування). Якщо книга відсортована за прізвищем — можна відкрити приблизно на «І» і знайти за секунди. Це і є принцип роботи B-tree індексу.

Але телефонна книга відсортована лише за одним критерієм. Якщо потрібно знайти всіх людей з номером 044-* — знову повне сканування, бо книга не відсортована за номером. Так само і в базах: один індекс допомагає лише при пошуку за тими стовпцями, що до нього входять.

Перш ніж перейти до EF Core Fluent API — потрібно зрозуміти, як індекси реально працюють. Без цього розуміння HasIndex() — просто «магічна аннотація», що начебто робить все швидшим.

B-tree індекс: анатомія структури даних

B-tree (Balanced tree, збалансоване дерево) — структура даних, що лежить в основі більшості реляційних індексів. Вона містить відсортовані ключові значення у вузлах дерева, де кожен вузол вказує на діапазон значень у нижньому рівні.

Уявімо таблицю Customers з 1 000 000 рядків і B-tree індекс на Email:

                    [M-R]
                   /      \
           [M-N]             [R-S]
          /      \           /    \
    [Ma-Mc]  [Md-Mz]   [Ra-Rm]  [Rn-Rz]
    /  |  \   / | \     / | \    / | \
  [leaf] [leaf] [leaf] [leaf] [leaf] ...
   ↓       ↓       ↓
  Ptrs   Ptrs   Ptrs → рядки у heap (таблиці)

Пошук Email = 'maria@example.com':

  1. Починаємо з кореня → maria → між M-R → йдемо вліво
  2. У M-N вузлі → maria → між Ma-Mc → йдемо до листа
  3. Лист: знаходимо точне значення → отримуємо pointer до рядка у heap
  4. Переходимо до heap і читаємо повний рядок

Замість 1 000 000 порівнянь — лише ~3-4 порівняння (висота дерева). Для мільйонного рядка — висота B-tree log₁₀₀(1 000 000) ≈ 3.

Де B-tree особливо ефективний:

  • Точний пошук: WHERE Email = 'x'
  • Діапазони: WHERE CreatedAt BETWEEN '2024-01-01' AND '2024-12-31'
  • Сортування: ORDER BY LastName — дерево вже відсортоване
  • Prefix пошук: WHERE Name LIKE 'Іван%' (але не LIKE '%ванко'!)

Де B-tree НЕ допомагає:

  • WHERE LastName LIKE '%ченко' — суфікс пошук
  • WHERE LOWER(Email) = 'x@y.com' — функція над стовпцем
  • WHERE Amount * 1.2 > 1000 — вираз над стовпцем

Ці нюанси критичні для розуміння, коли HasIndex реально допомагає, а коли — лише займає місце.

HasIndex: базова конфігурація

Флуент-метод HasIndex визначає індекс у EF Core. В абсолютній більшості випадків це B-tree індекс (хоча провайдери підтримують й інші типи: GIN, GiST, HASH для PostgreSQL).

Простий індекс

public class Customer
{
    public int Id { get; set; }
    public string FullName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string? PhoneNumber { get; set; }
    public string CountryCode { get; set; } = string.Empty;
    public DateTime RegisteredAt { get; set; }
    public bool IsActive { get; set; }
}

public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.HasKey(c => c.Id);

        builder.Property(c => c.FullName).IsRequired().HasMaxLength(200);
        builder.Property(c => c.Email).IsRequired().HasMaxLength(320);
        builder.Property(c => c.CountryCode).HasMaxLength(2).IsUnicode(false);

        // Простий індекс: частий пошук за CountryCode
        builder.HasIndex(c => c.CountryCode);

        // Індекс з власним ім'ям (зручно для DBA і міграцій)
        builder.HasIndex(c => c.RegisteredAt)
               .HasDatabaseName("IX_Customers_RegisteredAt");
    }
}

Генерований DDL:

CREATE INDEX [IX_Customers_CountryCode]
    ON [Customers] ([CountryCode]);

CREATE INDEX [IX_Customers_RegisteredAt]
    ON [Customers] ([RegisteredAt]);

Коли потрібен індекс на CountryCode: Якщо запискладно робити WHERE CountryCode = 'UA' і таблиця містить 500K+ рядків — без індексу це повне сканування. З індексом — миттєво.

Когда індекс на CountryCode не потрібен: Якщо CountryCode має лише 5 унікальних значень (UA, US, DE, GB, FR) — cardinality (різноманітність значень) дуже низька. B-tree Index Scan може бути повільнішим за Sequential Scan, якщо вибирається > 15-20% рядків. СУБД сама вирішує — але знати це корисно.

IsUnique: унікальний індекс

Унікальний індекс гарантує унікальність значень у стовпці і одночасно прискорює пошук по ньому. Це обмеження + індекс в одному.

// Email має бути унікальним по всій таблиці
builder.HasIndex(c => c.Email)
       .IsUnique()
       .HasDatabaseName("UX_Customers_Email");

CREATE UNIQUE INDEX [UX_Customers_Email]
    ON [Customers] ([Email]);
-- або (залежно від провайдера):
ALTER TABLE [Customers] ADD CONSTRAINT [UX_Customers_Email] UNIQUE ([Email]);
Alternate Key vs Unique Index: HasAlternateKey() — це UNIQUE CONSTRAINT, що водночас стає можливою ціллю для FK. HasIndex().IsUnique() — це UNIQUE INDEX, що не є стандартним FK target. For FK references — завжди HasAlternateKey. Для просто унікальності — HasIndex().IsUnique().

Складені (Composite) індекси: порядок стовпців має значення

Складений індекс охоплює декілька стовпців. Він ефективний, якщо запит фільтрує за кількома стовпцями або поєднує фільтрацію з сортуванням.

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Status { get; set; } = string.Empty;
    public DateTime PlacedAt { get; set; }
    public decimal TotalAmount { get; set; }
    public string CountryCode { get; set; } = string.Empty;
}

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.HasKey(o => o.Id);

        // Складений індекс: частий запит "замовлення конкретного клієнта за статусом"
        builder.HasIndex(o => new { o.CustomerId, o.Status })
               .HasDatabaseName("IX_Orders_Customer_Status");

        // Складений індекс для звітів: "замовлення за країною в діапазоні дат"
        builder.HasIndex(o => new { o.CountryCode, o.PlacedAt })
               .HasDatabaseName("IX_Orders_Country_Date");
    }
}

CREATE INDEX [IX_Orders_Customer_Status]
    ON [Orders] ([CustomerId], [Status]);

CREATE INDEX [IX_Orders_Country_Date]
    ON [Orders] ([CountryCode], [PlacedAt]);

Правило «лівого префіксу»

Складений індекс (A, B) ефективний для запитів, що фільтрують за:

  • WHERE A = x — ✅ використовує (лівий стовпець)
  • WHERE A = x AND B = y — ✅ використовує (обидва стовпці)
  • WHERE A = x ORDER BY B — ✅ ефективно (сортування по B у контексті A)
  • WHERE B = y — ❌ не використовує індекс (пропущений лівий стовпець A)
// Запити, що скористаються IX_Orders_Customer_Status:
var byCustomer = context.Orders
    .Where(o => o.CustomerId == 42)           // A = x ✅
    .ToList();

var byCustomerAndStatus = context.Orders
    .Where(o => o.CustomerId == 42 && o.Status == "Delivered") // A=x AND B=y ✅
    .ToList();

// Запит, що НЕ скористається:
var byStatusOnly = context.Orders
    .Where(o => o.Status == "Delivered")      // тільки B ❌
    .ToList();

// Рішення: окремий індекс на Status або переставити порядок у складеному

Тому порядок стовпців у HasIndex(o => new { o.CustomerId, o.Status }) критичний: перший стовпець — той, за яким фільтрують найчастіше.

Складений уніuельний індекс

public class Enrollment
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    public DateTime EnrolledAt { get; set; }
}

// Студент не може бути записаний на курс двічі
builder.HasIndex(e => new { e.StudentId, e.CourseId })
       .IsUnique()
       .HasDatabaseName("UX_Enrollments_Student_Course");

Фільтровані (Partial) індекси

Фільтрований індекс (filtered або partial index) індексує тільки підмножину рядків, що відповідають умові. Це менший індекс → менше пам'яті → швидші операції запису → ефективніший для специфічних запитів.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public bool IsActive { get; set; }
    public bool IsDeleted { get; set; }
    public string? Email { get; set; }  // nullable
    public string Category { get; set; } = string.Empty;
}

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasKey(p => p.Id);

        // Фільтрований індекс: тільки активні продукти
        // Запит "WHERE IsActive = 1 AND Category = ..." — використає цей індекс
        builder.HasIndex(p => p.Category)
               .HasFilter("[IsActive] = 1")  // SQL Server синтаксис
               .HasDatabaseName("IX_Products_Active_Category");

        // Фільтрований унікальний індекс: Email унікальний тільки серед не-видалених
        // NULL Email дозволений для soft-deleted записів
        builder.HasIndex(p => p.Email)
               .IsUnique()
               .HasFilter("[IsDeleted] = 0 AND [Email] IS NOT NULL")
               .HasDatabaseName("UX_Products_Email_NotDeleted");
    }
}

-- SQL Server:
CREATE INDEX [IX_Products_Active_Category]
    ON [Products] ([Category])
    WHERE [IsActive] = 1;

CREATE UNIQUE INDEX [UX_Products_Email_NotDeleted]
    ON [Products] ([Email])
    WHERE [IsDeleted] = 0 AND [Email] IS NOT NULL;

PostgreSQL-варіант:

// PostgreSQL через Npgsql: HasFilter з PostgreSQL-синтаксисом
builder.HasIndex(p => p.Category)
       .HasFilter("""\"IsActive\" = true""")
       .HasDatabaseName("IX_Products_Active_Category");

-- PostgreSQL:
CREATE INDEX "IX_Products_Active_Category"
    ON "Products" ("Category")
    WHERE "IsActive" = true;

Реальний сценарій: Soft Delete + фільтрований індекс

Soft Delete — частий патерн у EF Core (через IsDeleted або DeletedAt). Без фільтрованого індексу звичайний індекс включатиме всі видалені рядки — тобто для таблиці з 1M записів, 800K з яких видалені, індекс на Email для 200K активних є надлишковим і менш ефективним.

// Global Query Filter для soft delete
builder.HasQueryFilter(p => !p.IsDeleted);

// Фільтрований індекс: тільки по активних рядках
builder.HasIndex(p => new { p.Category, p.IsActive })
       .HasFilter("[IsDeleted] = 0")
       .HasDatabaseName("IX_Products_NotDeleted_Category_IsActive");

Тепер індекс займає менше місця і є більш ефективним для запитів по активним записам.

Include columns: Covering Indexes

Covering Index (покриваючий індекс) — індекс, що містить не лише ключові стовпці, але й додаткові стовпці для відповіді на запит без звернення до основної таблиці (heap). Це дозволяє уникнути bookmark lookup (зворотнього звернення до heap).

Проблема bookmark lookup

// Запит: отримати Name і Price всіх Product за Category
var products = context.Products
    .Where(p => p.Category == "Electronics")
    .Select(p => new { p.Name, p.Price })
    .ToListAsync();

Якщо є INDEX (Category), SQL Server:

  1. Сканує індекс → знаходить всі Id рядків з Category = 'Electronics'
  2. Для кожного Id — bookmark lookup до heap → читає рядок → бере Name і Price

При 10K рядків категорії — 10K bookmark lookups. Це дорого.

Рішення: IncludeProperties

// Covering index: Category + включаємо Name і Price
builder.HasIndex(p => p.Category)
       .IncludeProperties(p => new { p.Name, p.Price })
       .HasDatabaseName("IX_Products_Category_Covering");

CREATE INDEX [IX_Products_Category_Covering]
    ON [Products] ([Category])
    INCLUDE ([Name], [Price]);  -- SQL Server
    -- PostgreSQL: INCLUDE (Name, Price) теж підтримується

Тепер для запиту WHERE Category = 'Electronics' SELECT Name, Price — SQL Server читає лише індекс, без звернення до heap. Результат: значно менше IO.

Правило Covering Index: Якщо запит часто читає ті самі кілька стовпців після фільтрації — розгляньте IncludeProperties. Але: включення великих стовпців (nvarchar(max), varbinary(max)) у INCLUDE значно збільшує розмір індексу. Оптимально включати лише невеликі, часто читані стовпці.

Складений covering index

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public string ProductSku { get; set; } = string.Empty;
}

// Частий запит: "рядки замовлення для конкретного замовлення: Id, Quantity, UnitPrice"
builder.HasIndex(oi => oi.OrderId)
       .IncludeProperties(oi => new { oi.ProductId, oi.Quantity, oi.UnitPrice })
       .HasDatabaseName("IX_OrderItems_OrderId_Covering");

CREATE INDEX [IX_OrderItems_OrderId_Covering]
    ON [OrderItems] ([OrderId])
    INCLUDE ([ProductId], [Quantity], [UnitPrice]);

Primary Key vs Alternate Key vs Unique Index

Це три різних механізми, що часто плутають. Розберемо відмінності:

Primary Key

builder.HasKey(e => e.Id);
// або складений:
builder.HasKey(e => new { e.StudentId, e.CourseId });
  • Гарантує: унікальність + NOT NULL
  • DDL: CONSTRAINT PK_... PRIMARY KEY
  • Clustered: у SQL Server PK за замовчуванням clustered — рядки фізично збережені у порядку PK. PostgreSQL PK — завжди нецластерований heap + B-tree.
  • FK target: так, будь-яка FK може вказувати на PK

Alternate Key

// Alternate Key: унікальний ідентифікатор, що є можливим FK target
builder.HasAlternateKey(p => p.Isbn);
// або складений:
builder.HasAlternateKey(a => new { a.FirstName, a.LastName });

ALTER TABLE [Products]
    ADD CONSTRAINT [AK_Products_Isbn] UNIQUE ([Isbn]);
  • Гарантує: унікальність + NOT NULL (у SQL Server через UNIQUE CONSTRAINT)
  • DDL: CONSTRAINT AK_... UNIQUE
  • FK target: так! Це головна відмінність від Unique Index
  • Використання: builder.HasOne().WithMany().HasPrincipalKey(p => p.Isbn) — FK вказує на AK

Unique Index

builder.HasIndex(c => c.Email).IsUnique();

CREATE UNIQUE INDEX [IX_Customers_Email] ON [Customers] ([Email]);
  • Гарантує: унікальність (NULL зазвичай дозволений — залежить від СУБД)
  • DDL: CREATE UNIQUE INDEX
  • FK target: технічно ні у стандарті SQL, але деякі СУБД дозволяють
  • Використання: коли потрібна тільки унікальність без FK

Порівняльна таблиця

Primary KeyAlternate KeyUnique Index
Флуент APIHasKey()HasAlternateKey()HasIndex().IsUnique()
NOT NULLЗавждиТакNULL може дублюватись (1 запис)
FK targetТакТакЗазвичай ні
КількістьОдинДекількаДекілька
DDLPRIMARY KEYUNIQUE CONSTRAINTUNIQUE INDEX

Full-Text Index (провайдерний)

EF Core не підтримує Full-Text Search нативно — це провайдерна функція. Але можна додати через migrationBuilder.Sql().

SQL Server Full-Text Index

public partial class AddFullTextIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Потрібен Full-Text Catalog
        migrationBuilder.Sql(@"
            IF NOT EXISTS (SELECT * FROM sys.fulltext_catalogs WHERE name = 'ProductsCatalog')
                CREATE FULLTEXT CATALOG [ProductsCatalog] AS DEFAULT;

            CREATE FULLTEXT INDEX ON [Products] ([Name], [Description])
                KEY INDEX [PK_Products]
                ON [ProductsCatalog]
                WITH CHANGE_TRACKING AUTO;
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP FULLTEXT INDEX ON [Products];");
    }
}

Запит через EF Core:

// Full-Text Search через FromSqlRaw — EF Core не транслює CONTAINS нативно
var results = await context.Products
    .FromSqlRaw("SELECT * FROM [Products] WHERE CONTAINS([Name], {0})", "laptop")
    .ToListAsync();

PostgreSQL Full-Text Search

// PostgreSQL: через NpgsqlTsVectorExtension або власний migration SQL
migrationBuilder.Sql(@"
    ALTER TABLE ""Products"" ADD COLUMN ""SearchVector"" tsvector
        GENERATED ALWAYS AS (
            to_tsvector('ukrainian', coalesce(""Name"", '') || ' ' || coalesce(""Description"", ''))
        ) STORED;

    CREATE INDEX IX_Products_SearchVector
        ON ""Products"" USING gin(""SearchVector"");
");

Практичні завдання (Частина 1)

Рівень 1 — Базовий

Завдання 1.1: Аудит індексів

Є таблиця BlogPost (Id, Title, Slug, PublishedAt, AuthorId, CategoryId, ViewCount, IsPublished). Визначте які запити будуть типовими для блогу і підберіть індекси:

  • Головна сторінка: WHERE IsPublished = 1 ORDER BY PublishedAt DESC LIMIT 10
  • Сторінка автора: WHERE AuthorId = x AND IsPublished = 1
  • Категорія: WHERE CategoryId = x AND IsPublished = 1 ORDER BY PublishedAt DESC
  • Пошук за slug: WHERE Slug = 'my-post'

Напишіть конфігурацію через HasIndex. Де доцільний фільтрований індекс? Де — covering?

Завдання 1.2: Alternate Key vs Unique Index

Є Book (Id, Isbn, Title) і OrderItem (Id, OrderId, BookIsbn, Quantity). OrderItem.BookIsbn — FK до Book.Isbn. Яку конструкцію використати: HasAlternateKey(b => b.Isbn) чи HasIndex(b => b.Isbn).IsUnique()? Чому? Реалізуйте правильну конфігурацію FK.

Завдання 1.3: Порядок стовпців у складеному індексі

Для запиту WHERE CustomerId = @id AND Status = @status AND PlacedAt > @date на таблиці Order:

  1. Напишіть складений індекс у двох варіантах: (CustomerId, Status, PlacedAt) і (Status, CustomerId, PlacedAt)
  2. Який варіант кращий, якщо 90% запитів фільтрують за CustomerId спочатку?
  3. Перевірте через SQL Server Execution Plan або EXPLAIN у PostgreSQL.

Рівень 2 — Логіка

Завдання 2.1: Covering Index для звіту

Є Order (Id, CustomerId, Status, TotalAmount, TaxAmount, PlacedAt, CountryCode). Частий запит:

context.Orders
    .Where(o => o.CountryCode == "UA" && o.Status == "Delivered")
    .Select(o => new { o.Id, o.TotalAmount, o.TaxAmount, o.PlacedAt })
    .OrderBy(o => o.PlacedAt)
    .ToListAsync();

Розробіть оптимальний covering index, що дозволить уникнути bookmark lookup. Поясніть вибір стовпців і порядку в IncludeProperties.

Завдання 2.2: Фільтровані індекси для Soft Delete

Система реалізує soft delete через IsDeleted (bool). У Customer є поля Email (унікальний) і PhoneNumber (унікальний). Але після soft delete інший клієнт може зареєструватися з тим самим email.

Реалізуйте фільтровані унікальні індекси:

  • Email унікальний серед IsDeleted = false
  • PhoneNumber унікальний серед IsDeleted = false і PhoneNumber IS NOT NULL

Перевірте: чи можна додати двох клієнтів з однаковим email, якщо перший IsDeleted = true?

Рівень 3 — Архітектура

Завдання 3.1: Індексна стратегія для e-commerce

Для таблиць Product (Id, Name, CategoryId, BrandId, Price, Stock, IsActive, CreatedAt) і OrderItem (Id, OrderId, ProductId, Quantity, UnitPrice) розробіть повну індексну стратегію:

  1. Аналіз типових запитів (каталог, пошук, admin, звіти)
  2. Для кожного запиту: який індекс його покриє?
  3. Перевірте: чи немає дублюючих або надлишкових індексів?
  4. Оцініть компроміс: кожен індекс прискорює читання, але сповільнює запис (INSERT/UPDATE/DELETE). При якому рівні навантаження (read:write ratio) індексна стратегія потребує перегляду?

Підсумок частини 1

У першій частині заклали фундамент роботи з індексами в EF Core:

  • B-tree структура: розуміємо, чому індекс прискорює пошук (log замість лінійного часу) і коли він не допомагає (суфіксний пошук, функції над стовпцями, низька cardinality).
  • HasIndex(): базовий синтаксис, іменування через HasDatabaseName.
  • IsUnique(): унікальний індекс як комбінація обмеження і прискорювача.
  • Складені індекси: правило «лівого префіксу» — порядок стовпців критичний.
  • Фільтровані індекси (HasFilter): менший індекс для підмножини рядків. Ідеальний для soft delete.
  • Covering Index (IncludeProperties): уникаємо bookmark lookup, включаючи часто читані стовпці.
  • PK vs AK vs Unique Index: три різних механізми з різними семантиками і DDL.

У другій частині — Check Constraints, Database Sequences, Collation, Database Comments, схема (HasDefaultSchema) та HasDbFunction для маппінгу SQL-функцій.

Успадкування — TPT, TPC та Порівняння Стратегій (Частина 2)

Table-Per-Type (TPT) і Table-Per-Concrete-Class (TPC) в EF Core — конфігурація, продуктивність, міграції, вибір стратегії генерації ID. Фінальна порівняльна таблиця всіх трьох підходів та матриця рішень.

Індекси, Обмеження та Схема (Частина 2)

Check Constraints, Database Sequences і Hi-Lo, Collation, Database Comments, HasDefaultSchema, схема бази даних через Fluent API. Повний розбір обмежень цілісності в EF Core.

Copyright © 2026