Как индекс покрытия может удовлетворить более одного запроса?

Question

Я унаследовал базу данных MS Sql, размещенную в Azure. В поисках улучшения производительности я много читал об индексации и покрытии индекса. (Может быть, это наиболее полное чтение, которое я нашел: https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/)

Но одно сомнение все еще остается...

Так, например, для таблицы расчетов ниже (которая имеет около 8 миллионов строк), я обнаружил, что наиболее часто используемые поля используются в запросе where (внутри объединений или нет):PAYMENT_DATE, DUE_DATE, CUSTOMER_ID, DELAY_DAYS, AMOUNT,

CREATE TABLE [dbo].[BILLING](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CHANGED_DATE] [datetime] NULL,
    [INCLUDED_DATE] [datetime] NULL,
    [CHANGED_USER_ID] [int] NULL,
    [INCLUDED_USER_ID] [int] NULL,
    [BILL_CODE] [varchar](255) NOT NULL,
    [PAYMENT_DATE] [datetime] NULL,
    [DUE_DATE] [datetime] NOT NULL,
    [AMOUNT] [float] NOT NULL,
    [AMOUNT_PAYED] [float] NULL,
    [CUSTOMER_ID] [int] NOT NULL,
    [OUR_NUMBER] [varchar](200) NULL,
    [TYPE] [varchar](250) NULL,
    [BANK_ID] [int] NULL,
    [ISSUE_DATE] [datetime] NULL,
    [STATE] [varchar](20) NULL,
    [DUNNING_STATE_ID] [int] NULL,
    [OPEN_VALUE] [float] NULL,
    [ACCREDIT_VALUE] [float] NULL,
    [LOWER_VALUE] [float] NULL,
    [DISCCOUNT_VALUE] [float] NULL,
    [INTEREST_VALUE] [float] NULL,
    [FINE_VALUE] [float] NULL,
    [RECEIVED_AMOUNT] [float] NULL,
    [DELAY_DAYS] [int] NULL,
    [BRANCH_ID] [int] NULL,
    [FIELD1] [varchar](250) NULL,
    [FIELD2] [varchar](250) NULL,
    [FIELD3] [varchar](250) NULL,
    [FIELD4] [varchar](250) NULL,
    [FIELD5] [varchar](250) NULL,
    [OBS1] [varchar](250) NULL,
    [OBS2] [varchar](250) NULL,
    [OBS3] [varchar](250) NULL,
    [INTEREST_RATE] [float] NULL,
    [INTEREST_CALC] [float] NULL,
    [AGREEMENT_STATE] [varchar](20) NULL,
    [AGREEMENT_ID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)

Кроме того, целевые запросы для оптимизации делают вычисления по предложению select по:AMOUNT, DELAY_DAYS, COUNT(ID), Например:

SELECT
        T.CUSTOMER_ID AS CUSTOMER_ID
        , COUNT(T.ID) AS NUM_BILLS
        , COUNT(
            CASE
                WHEN T.DELAY_DAYS <= 0 THEN 1
                ELSE NULL
            END
        ) AS DEPOSITS
        , COUNT(
            CASE
                WHEN T.DELAY_DAYS > 0 THEN 1
                ELSE NULL
            END
        ) AS DEFAULTED
        , COUNT(
            CASE
                WHEN T.DELAY_DAYS BETWEEN 30 AND 60 THEN 1
                ELSE NULL
            END
        ) AS DEFAULTED_30
        , COUNT(
            CASE
                WHEN T.DELAY_DAYS BETWEEN 60 AND 90 THEN 1
                ELSE NULL
            END
        ) AS DEFAULTED_60
        , COUNT(
            CASE
                WHEN T.DELAY_DAYS > 90 THEN 1
                ELSE NULL
            END
        ) AS DEFAULTED_90
        , MAX(T.DELAY_DAYS) AS MAX_DEFAULTED_TIME
        , SUM(
            CASE
                WHEN T.DELAY_DAYS > 0 THEN T.DELAY_DAYS
                ELSE 0
            END
        ) AS SUM_DEFAULTED_TIME
        , SUM(T.AMOUNT) AS AMOUNT
        , SUM(
            CASE
                WHEN T.DELAY_DAYS > 0 THEN T.AMOUNT
                ELSE 0
            END
        ) AS DEFAULTED_AMOUNT
    FROM BILLING T
    WHERE
        T.DUE_DATE < GETDATE()
        AND T.AMOUNT > 0
    GROUP BY
        T.CUSTOMER_ID

Таким образом, для меня казалось очевидным, что следующий индекс решит все мои проблемы:

CREATE NONCLUSTERED INDEX [ix_Titulo_main_fields] ON [dbo].[BILLING]
(
    [PAYMENT_DATE] ASC,
    [DUE_DATE] DESC,
    [AMOUNT] ASC,
    [CUSTOMER_ID] ASC,
    [STATE] ASC,
    [DELAY_DAYS] ASC,
    [BRANCH_ID] ASC,
    [AGREEMENT_ID] ASC
)
INCLUDE (   [BILLING_CODE],
    [AGREEMENT_STATE],
)
GO;

Напротив, когда я запрашиваю план запроса в Management Studio, SQL Server не использует этот индекс и предлагает мне создать новый:

CREATE NONCLUSTERED INDEX [ix_billing_due_date_amount] ON [dbo].[billing]
(
    [due_date] ASC,
    [amount] ASC
)
INCLUDE (   [customer_id],
    [delay_days])
GO

Итак, сомнения:
должен ли индекс покрытия быть именно тем, что ищет предложение WHERE?
Если это правда, как индекс покрытия может удовлетворить более одного запроса?
Иначе, почему прежний индекс не удовлетворяет запросу?

Я действительно не знаю, где я что-то пропустил...

Заранее спасибо!

Answer 1

Заказ имеет значение. Поскольку предлагаемый вами индекс начинается с [payment_date], но предикат запроса не включает в себя [payment_date], индекс вряд ли будет более выгодным, чем сканирование таблицы.

Можно иметь один индекс, охватывающий несколько запросов. Первое перечисленное поле индекса почти всегда должно быть в предикате всех запросов. Для улучшения результатов также примените эту логику ко 2-му полю, 3-му полю и т. Д.

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

Примечание: в Oracle есть функция "сканирование с пропуском индекса", которая позволяет использовать индекс, даже если в предикате отсутствует начальный столбец. Он эффективен, когда передний столбец имеет несколько различных значений (по материалам learningintheopen.org).

Answer 2

Для любого конкретного запроса вы, безусловно, можете создать специализированный индекс - оптимизатор может сказать вам об этом. И конкретный запрос будет усилен, а другие подобные запросы будут выполняться более или менее быстрее. Однако, как правило, я не использую индексы специально для запроса и не предпочитаю индексы с несколькими столбцами, и я не использую include. Могут быть редкие исключения, но обычно я не делаю. Зачем? Оптимизатор даст вам подсказку о том, какой индекс вам нужен в области запроса - после 8-10 или около того индексов вы добавляете в ту же таблицу, и оптимизатор больше не будет распознавать, какой индекс использовать, не говоря уже о задержках вставки / обновления (хотя правильное индексирование экономит время даже при вставке / обновлении времени блокировки).

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

В целом, наличие 4-8 индексов в одной колонке для таблицы - лучший обходной путь для всех sql, которые в конечном итоге будут выполняться для этой таблицы. Это действительно до тех пор, пока эти столбцы выбраны с исследованием использования, как вы описываете.

Это потому, что на самом деле самое важное - это первая фильтрация. Фильтрация 10000 строк из 8 миллионов за 3 секунды - это успех - теперь неважно, как фильтровать 10000 строк в 10 финалов. Может быть, там тоже есть индекс, а может и нет, но где вы хотите отсканировать таблицу: 8 миллионов или 10000 отфильтрованных?

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

Иногда запросы выбирают неправильный индекс - какой-то универсальный фильтр, который отфильтровывает только 5%, а игнорирует фильтр, отфильтровывающий 95%. Это может быть плохая статистика или оценка количества элементов, что приводит к плохому плану выполнения. Вы можете преодолеть это с помощью подсказки запроса для индекса, который вы обязательно будете использовать всегда или применять оценку 2012 года.