Составной индекс на нескольких полях: нужны ли индексы по отдельным полям?

#c_sharp #sql_server #entity_framework_core

Есть некоторая таблица в базе SQL Server 2008 R2:



Три столбца, три индекса.

Создана она при помощи Entity Framework, всё просто:

public class Order
{
    public int Id { get; set; }

    public int GroupBuyingId { get; set; }

    public GroupBuying GroupBuying { get; set; }

    public int ParticipantId { get; set; }

    public Contact Participant { get; set; }
}


Через какое-то время прилетело требование наложить ограничение уникальности на поля
GroupBuyingId  и ParticipantId, ну я и сделал:

public class OrderConfiguration : IEntityTypeConfiguration
{
    public void Configure(EntityTypeBuilder builder)
    {
        builder.HasIndex(x => new { x.GroupBuyingId, x.ParticipantId }).IsUnique();
    }
}


Но перед тем как применять миграцию - автоматически глянул, что там создано:

public partial class UniqueConstraintForOrder : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_Orders_GroupBuyingId",
            table: "Orders");

        migrationBuilder.CreateIndex(
            name: "IX_Orders_GroupBuyingId_ParticipantId",
            table: "Orders",
            columns: new[] { "GroupBuyingId", "ParticipantId" },
            unique: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_Orders_GroupBuyingId_ParticipantId",
            table: "Orders");

        migrationBuilder.CreateIndex(
            name: "IX_Orders_GroupBuyingId",
            table: "Orders",
            column: "GroupBuyingId");
    }
}


И вот что мне непонятно. Создаётся новый индекс по двум полям - это правильно. Но
зачем-то сносится один из существующих индексов (кстати, почему не оба?), видимо из
расчёта, что можно будет использовать составной?

Что вообще в подобных случаях рекомендуется делать - оставлять один составной индекс
или составной + два отдельных? 

Понятно, что во втором случае объём занимаемый индексом будет больше, плюс соответственно
время на его обработку при вставке новых значений, а что насчёт скорости выборок? 

Будет ли составной индекс работать так же эффективно, как и одиночные?
    

Ответы

Ответ 1

Разница для составного индекса и индекса с одним полем заключается в длине ключа,
чем длиннее ключ, тем больше операций чтения при поиске для большой таблицы придется
выполнить движку.

В приведенном примере нет смысла держать дубль индекса, так как разница в длине ключа
несущественна, практического выигрыша по чтениям не будет.

Поиск всегда использует первое поле ключа индекса, поэтому второй индекс не удаляется,
так как он построен по другому (не по первому в составном) полю.


Ответ 2

Составной индекс по полям (A, B) всегда может использоваться вместо индекса (A),
но не может использоваться вместо индекса (B). Ну, если это стандартный B-Tree индекс.

Тем не менее, поиск по простому индексу (A) будет чуть быстрее, чем по составному
(A, B), из-за бо́льшего числа записей в одном ключевом узле дерева. Но на интах вы
этого не заметите.