Блог

Главная - Вопросы по программированию - Кластеризованный индекс против составного индекса. Какой из них лучше?

Кластеризованный индекс против составного индекса. Какой из них лучше?

#sql #sql-server #sqlperformance #clustered-index #non-clustered-index

#sql #sql-сервер #производительность sqlperformance #кластеризованный индекс #некластеризованный индекс

Вопрос:

Я использую Microsoft SQL Server 2017. У меня есть таблица под названием ProductMapping. Ниже приведена структура таблицы:

 CREATE TABLE [dbo].[Accommodation_ProductMapping](
    [ProductMappingId] [uniqueidentifier] NOT NULL,
    [AccommodationId] [uniqueidentifier] NULL,
    [SupplierId] [uniqueidentifier] NULL,
    [SupplierId] [varchar](50) NULL,
    [SupplierName] [varchar](50) NULL,
    [SupplierProductReference] [nvarchar](255) NULL,
    [ProductName] [nvarchar](500) NULL,
    [CountryName] [nvarchar](255) NULL,
    [CountryCode] [nvarchar](50) NULL,
    [CityName] [nvarchar](255) NULL,
    [CityCode] [nvarchar](100) NULL
)

В этой таблице содержится 15 миллиардов данных. Я создал некластерные и составные индексы в этой таблице. Ниже приведены подробности:-

 CREATE NONCLUSTERED INDEX [IDX_CityCode] ON [dbo].[ProductMapping]
(
    [CityCode] ASC
)

CREATE NONCLUSTERED INDEX [IDX_CountryCode] ON [dbo].[ProductMapping]
(
    [CountryCode] ASC,
)

CREATE NONCLUSTERED INDEX [IDX_CountryCityCode] ON [dbo].[ProductMapping]
(
    [CountryCode] ASC,
    [CityCode] ASC
)

CREATE NONCLUSTERED INDEX [IDX_ProductCountryCityCode] ON [dbo].[ProductMapping]
(
    [ProductName] ASC,
    [CountryCode] ASC,
    [CityCode] ASC
)

CREATE NONCLUSTERED INDEX [IDX_AccommodationCountryCityCode] ON [dbo].[ProductMapping]
(
    [AccommodationId] ASC,
    [CountryCode] ASC,
    [CityCode] ASC
)

Я могу получить данные без каких-либо проблем.

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

Кроме того, я создал составной индекс «IDX_CountryCityCode» для кода страны и города, поэтому мне нужен отдельный некластерный индекс для «CityCode» и «CountryCode» (например, IDX_CityCode и IDX_CountryCode).

Заранее благодарю вас.

ОТРЕДАКТИРОВАНО

Я просто хочу знать, удаляю ли я все вышеупомянутые индексы (т. Е. [IDX_CityCode], [IDX_CountryCode], [IDX_CountryCityCode], [IDX_ProductCountryCityCode] amp; [IDX_AccommodationCountryCityCode] ) и помещаю их все в один составной индекс, как показано ниже. Это сработает или это лучший подход?

 CREATE NONCLUSTERED INDEX [IDX_CityCountryAccommodationProduct] ON [dbo].[ProductMapping]
(
    [CityCode] ASC,
    [CountryCode] ASC,
    [AccommodationId] ASC,
    [ProductName] ASC
)

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

1. Вам не нужен IDX_CountryCode индекс, потому что IDX_CountryCityCode также имеет CountryCode в качестве первого столбца. Таблица, вероятно, должна иметь кластеризованный индекс, но лучший кандидат зависит от ваших запросов.

2. Спасибо, Дэн. Не могли бы вы, пожалуйста, проверить мой ОТРЕДАКТИРОВАННЫЙ пост.

3. Составной индекс также может быть кластеризованным индексом. Единственной проблемой здесь является использование uniqueidentifier для идентификаторов. Если значения GUID не созданы с использованием последовательного алгоритма (например, NEWSEQUENTIALID() вместо NEWID() ), они не могут использоваться для кластеризованного индекса. Однако то, какие индексы вы используете, зависит от реальных запросов.

4. I just want to know if I remove all the above indexes зависит от запросов . Порядок столбцов в индексе имеет большое значение. Индекс не поможет так сильно, если оптимизатор запросов не сможет использовать первый столбец для ограничения строк. Более выборочные столбцы должны быть первыми, что означает, что IDX_CountryCityCode и IDX_ProductCountryCityCode имеют неправильный порядок. Если вы замените все индексы на IDX_CityCountryAccommodationProduct и попытаетесь выполнить поиск по ProductName , оптимизатор может вообще игнорировать индекс

5. @user2043071, самые левые ключевые столбцы индекса должны использоваться в предложениях WHERE и JOIN для эффективного использования, поэтому, опять же, это будет зависеть от ваших запросов.

Ответ №1:

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

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

Вы не показали никаких запросов, поэтому невозможно сказать, какой набор индексов является оптимальным. Однако они не эквивалентны.

Ваш составной индекс можно использовать, например, в следующих where предложениях:

 where CityCode = @CityCode
where CityCode = @CityCode and CountryCode = @CountryCode
where CityCode = @CityCode and CountryCode = @CountryCode and AccommodationId = @AccommodationId
where CityCode = @CityCode and CountryCode = @CountryCode and AccommodationId = @AccommodationId and ProductName = @ProductName

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

Этот индекс не может быть использован, если @CityId его нет. Итак, этот индекс не подходит для:

 where CountryCode = @CountryCode
where CountryCode = @CountryCode and AccommodationId = @AccommodationId
whereCountryCode = @CountryCode and AccommodationId = @AccommodationId and ProductName = @ProductName

С четырьмя индексами можно использовать один из них. Оптимизатор пытается использовать «лучший» индекс, когда можно использовать более одного. Иногда оптимизатор выбирает не лучший.

Заголовок вашего вопроса касается кластеризованных и некластеризованных индексов. Это вызывает другие проблемы — в частности, как данные вставляются и обновляются. Кластеризованные индексы накладывают ограничения на способ хранения данных, поэтому они могут оказывать значительное влияние на производительность модификации данных.

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