Как организовать индексы для частого возвращения активных записей

Что проверяют

Знаете ли вы про фильтрованные (частичные) индексы и понимаете ли, почему индекс на одну булеву колонку почти бесполезен. Типичная картина: в таблице миллионы строк, активных малая доля, и запросы читают именно их. Следом спрашивают, как описать это в EF Core и как убедиться, что индекс используется.

Как отвечать

Сначала уточните, что значит «активные»: флаг IsActive, статус или пустой DeletedAt при мягком удалении. Дальше план:

• индекс под запрос, а не под флаг: для запроса WHERE IsActive = 1 AND TenantId = @t ORDER BY CreatedAt DESC нужен составной индекс (TenantId, CreatedAt). Колонки равенства идут первыми, сортировка или диапазон после них;
• фильтр: в SQL Server CREATE INDEX IX_Orders_Active ON Orders (TenantId, CreatedAt) WHERE IsActive = 1, в PostgreSQL то же называется частичным индексом. В нём только активные строки, поэтому он меньше и дешевле в обслуживании;
• покрытие: частые поля выборки добавляют через INCLUDE, чтобы не ходить в таблицу за каждой строкой;
• EF Core: HasIndex(x => new { x.TenantId, x.CreatedAt }).HasFilter("[IsActive] = 1"), включаемые колонки задаёт IncludeProperties. Если активность это мягкое удаление, HasQueryFilter(x => x.DeletedAt == null) добавит условие во все запросы;
• проверка: фактический план выполнения в SSMS или EXPLAIN ANALYZE в PostgreSQL, неиспользуемые индексы видны в sys.dm_db_index_usage_stats.

Если архив растёт годами, упомяните секционирование или вынос архива в отдельную таблицу.

Где спотыкаются

Создают индекс по одной колонке IsActive. Если активных хотя бы треть, оптимизатор выберет полное сканирование, а если их мало, фильтр по остальным полям и сортировку всё равно придётся делать отдельно.

Не знают, что фильтрованный индекс в SQL Server может не подхватиться параметризованным запросом. План для WHERE IsActive = @p обязан подходить для любого значения, поэтому условие фильтра лучше передавать литералом. EF Core для Where(x => x.IsActive) так и делает.

Забывают про запись. Если флаг переключается постоянно, каждое изменение это удаление строки из индекса и вставка, и выигрыш на чтении съедается записью.