Уявіть телефонну книгу. Якщо сторінки не відсортовані — щоб знайти «Іваненко Олексій» — потрібно перегорнути кожну сторінку від початку до кінця. Це sequential scan (повне сканування). Якщо книга відсортована за прізвищем — можна відкрити приблизно на «І» і знайти за секунди. Це і є принцип роботи B-tree індексу.
Але телефонна книга відсортована лише за одним критерієм. Якщо потрібно знайти всіх людей з номером 044-* — знову повне сканування, бо книга не відсортована за номером. Так само і в базах: один індекс допомагає лише при пошуку за тими стовпцями, що до нього входять.
Перш ніж перейти до EF Core Fluent API — потрібно зрозуміти, як індекси реально працюють. Без цього розуміння HasIndex() — просто «магічна аннотація», що начебто робить все швидшим.
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':
maria → між M-R → йдемо влівоmaria → між Ma-Mc → йдемо до листаЗамість 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 — дерево вже відсортованеWHERE Name LIKE 'Іван%' (але не LIKE '%ванко'!)Де B-tree НЕ допомагає:
WHERE LastName LIKE '%ченко' — суфікс пошукWHERE LOWER(Email) = 'x@y.com' — функція над стовпцемWHERE Amount * 1.2 > 1000 — вираз над стовпцемЦі нюанси критичні для розуміння, коли 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% рядків. СУБД сама вирішує — але знати це корисно.
Унікальний індекс гарантує унікальність значень у стовпці і одночасно прискорює пошук по ньому. Це обмеження + індекс в одному.
// 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]);
HasAlternateKey() — це UNIQUE CONSTRAINT, що водночас стає можливою ціллю для FK. HasIndex().IsUnique() — це UNIQUE INDEX, що не є стандартним FK target. For FK references — завжди HasAlternateKey. Для просто унікальності — HasIndex().IsUnique().Складений індекс охоплює декілька стовпців. Він ефективний, якщо запит фільтрує за кількома стовпцями або поєднує фільтрацію з сортуванням.
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 }) критичний: перший стовпець — той, за яким фільтрують найчастіше.
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");
Фільтрований індекс (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 — частий патерн у 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");
Тепер індекс займає менше місця і є більш ефективним для запитів по активним записам.
Covering Index (покриваючий індекс) — індекс, що містить не лише ключові стовпці, але й додаткові стовпці для відповіді на запит без звернення до основної таблиці (heap). Це дозволяє уникнути bookmark lookup (зворотнього звернення до heap).
// Запит: отримати 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:
Id рядків з Category = 'Electronics'Id — bookmark lookup до heap → читає рядок → бере Name і PriceПри 10K рядків категорії — 10K bookmark lookups. Це дорого.
// 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.
IncludeProperties. Але: включення великих стовпців (nvarchar(max), varbinary(max)) у INCLUDE значно збільшує розмір індексу. Оптимально включати лише невеликі, часто читані стовпці.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]);
Це три різних механізми, що часто плутають. Розберемо відмінності:
builder.HasKey(e => e.Id);
// або складений:
builder.HasKey(e => new { e.StudentId, e.CourseId });
CONSTRAINT PK_... PRIMARY 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]);
CONSTRAINT AK_... UNIQUEbuilder.HasOne().WithMany().HasPrincipalKey(p => p.Isbn) — FK вказує на AKbuilder.HasIndex(c => c.Email).IsUnique();
CREATE UNIQUE INDEX [IX_Customers_Email] ON [Customers] ([Email]);
CREATE UNIQUE INDEX| Primary Key | Alternate Key | Unique Index | |
|---|---|---|---|
| Флуент API | HasKey() | HasAlternateKey() | HasIndex().IsUnique() |
| NOT NULL | Завжди | Так | NULL може дублюватись (1 запис) |
| FK target | Так | Так | Зазвичай ні |
| Кількість | Один | Декілька | Декілька |
| DDL | PRIMARY KEY | UNIQUE CONSTRAINT | UNIQUE INDEX |
EF Core не підтримує Full-Text Search нативно — це провайдерна функція. Але можна додати через migrationBuilder.Sql().
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: через 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: Аудит індексів
Є таблиця BlogPost (Id, Title, Slug, PublishedAt, AuthorId, CategoryId, ViewCount, IsPublished). Визначте які запити будуть типовими для блогу і підберіть індекси:
WHERE IsPublished = 1 ORDER BY PublishedAt DESC LIMIT 10WHERE AuthorId = x AND IsPublished = 1WHERE CategoryId = x AND IsPublished = 1 ORDER BY PublishedAt DESCWHERE 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:
(CustomerId, Status, PlacedAt) і (Status, CustomerId, PlacedAt)CustomerId спочатку?EXPLAIN у PostgreSQL.Завдання 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 = falsePhoneNumber унікальний серед IsDeleted = false і PhoneNumber IS NOT NULLПеревірте: чи можна додати двох клієнтів з однаковим email, якщо перший IsDeleted = true?
Завдання 3.1: Індексна стратегія для e-commerce
Для таблиць Product (Id, Name, CategoryId, BrandId, Price, Stock, IsActive, CreatedAt) і OrderItem (Id, OrderId, ProductId, Quantity, UnitPrice) розробіть повну індексну стратегію:
У першій частині заклали фундамент роботи з індексами в EF Core:
HasIndex(): базовий синтаксис, іменування через HasDatabaseName.IsUnique(): унікальний індекс як комбінація обмеження і прискорювача.HasFilter): менший індекс для підмножини рядків. Ідеальний для soft delete.IncludeProperties): уникаємо bookmark lookup, включаючи часто читані стовпці.У другій частині — 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.