Блог

Главная - Вопросы по программированию - В Microsoft SQL Server улучшат ли производительность 2 некластеризованных индекса, когда уже есть некластеризованный составной индекс?

В Microsoft SQL Server улучшат ли производительность 2 некластеризованных индекса, когда уже есть некластеризованный составной индекс?

#sql-server #indexing

#sql-server #индексирование

Вопрос:

У меня есть таблица под названием Files, которая содержит эти столбцы

 [Id] [int] IDENTITY(1,1) NOT NULL,
[RowVersion] [timestamp] NULL,
[CreatedAt] [datetime2](7) NOT NULL,
[UpdatedAt] [datetime2](7) NOT NULL,
[FileName] [nvarchar](450) NOT NULL,
[FileContent] [nvarchar](max) NULL,
[MessengerId] [int] NOT NULL,
[FileTypeId] [int] NOT NULL,
[Ref] [int] NOT NULL,

Таблица имеет кластеризованный индекс по идентификатору первичного ключа.

Также существует некластеризованный составной индекс MessengerId и FileName

 CREATE UNIQUE NONCLUSTERED INDEX [IX_Files_CourierId_FileName] ON [dbo].[Files]
(
    [MessengerId] ASC,
    [FileName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Этот запрос выполняется медленно

 SELECT COUNT(*) FROM Files WHERE MessengerId = 1 AND Filename = 'myfilename.xml'

Я пытаюсь протестировать, потому что тайм-ауты происходят на рабочем сервере. На моем ноутбуке разработчика у меня нет никаких проблем.

Улучшит ли производительность добавление 2 новых индексов для MessengerId и Filename?

2 новых индекса выглядят следующим образом

 CREATE NONCLUSTERED INDEX [Index_on_FileName] ON [dbo].[Files]
(
    [FileName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [Index_on_MessengerId] ON [dbo].[Files]
(
    [MessengerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Комментарии:

1. Текущий индекс является покрывающим индексом и может использоваться для удовлетворения вашего запроса без необходимости поиска строк в таблице. Ни один из предложенных индексов не охватывает, поэтому запросу придется прибегнуть к поиску / сканированию таблицы, чтобы найти оставшиеся данные. Ваша проблема может заключаться в устаревшей статистике или перехвате параметров. Также есть медленное чтение в приложении, быстрое в SSMS?

2. Помимо проблем с планом запроса, проблема также может быть связана с общей загрузкой сервера или приложениями, удерживающими блокировки строк (для последнего вы можете изучить такие вещи, как изоляция моментальных снимков). Единственное, в чем вы можете быть уверены, это не проблема, а полезность индекса. Если вы хотите узнать, помогает ли (существующий или новый) индекс, всегда просматривайте план запроса до и после; в этом случае это должно было сказать (даже в тестовой среде), что новые индексы не будут использоваться вашим запросом вместо существующего.

Ответ №1:

Этот запрос

 SELECT COUNT(*) FROM Files WHERE MessengerId = 1 AND Filename = 'myfilename.xml'

требуется поиск равенства индексов в MessengerId и Filename и никаких других столбцах, поэтому вашего текущего индекса будет достаточно.

Вы также можете изменить порядок столбцов для получения того же результата (вы можете найти его немного быстрее в случае отсутствия совпадения)

 CREATE UNIQUE NONCLUSTERED INDEX [IX_Files_CourierId_FileName] ON [dbo].[Files]
(
    [FileName] ASC,
    [MessengerId] ASC
)

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

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

Метки: ,