Как выбрать столбцы при создании индекса?

Это кажется странным вопросом. Я знаю разные типы индексов в sql-сервере (кластерный, некластеризованный, уникальный, отфильтрованный, индекс с включенными столбцами и т.д.), И я знаю, как их создавать. Также я знаю, что индекс зависит от запроса, но я не знаю, кто выбирает столбец при создании индекса. Например, предположим, что простой веб-сайт позволяет пользователям публиковать текст и изображения. На веб-сайте есть две две таблицы, показанные на изображении:

Запрос пользователя на веб-сайте:

Select UserID,UserName from User where Email='something' and Password='something'

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

Запрос, который получает сообщения пользователя:

Select * from Posts where UserID='something'

Этот запрос отличается от первого запроса. Этот запрос может возвращать несколько строк, в то время как первый всегда будет возвращать одну строку. Теперь тот же вопрос, как выбрать столбец?

Я хочу сказать, как выбрать столбцы, когда:

Создание кластерного индекса.
Создание некластеризованного индекса.
Создание некластеризованных с включенными столбцами.

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

Ответ 1

В идеальном мире вы хотите индексировать столбцы, которые появляются в условии WHERE или в условии JOIN. В вашем случае это будут столбцы Email и Password.

Таким образом, вы можете использовать некластеризованный индекс для таблицы пользователей, а также для электронной почты и пароля.

Так что в значительной степени этот индекс:

CREATE NONCLUSTERED INDEX idx_User_Email_Password
    ON dbo.User (Email, Password);

Так что, если вы запустите этот запрос:

SELECT UserID, UserName
FROM User
WHERE Email = 'something'
    AND Password = 'something';

В конечном итоге вы будете использовать только что созданный индекс (скорее всего) или кластеризованный индекс, и он будет искать через него. Однако ваш запрос выбирает UserID и UserName, которые не включены в ваш индекс, в результате ваш запрос выполнит поиск ключей (он найдет записи в созданном индексе и dbo.User вашу таблицу dbo.User чтобы найти совпадение). значения для оператора SELECT (UserID и UserName). Чтобы избежать этого, вы можете создать индекс со столбцами INCLUDED чтобы удалить поиск ключей (и вы захотите это сделать).

CREATE NONCLUSTERED INDEX idx_User_Email_Password
    ON dbo.User (Email, Password)
    INCLUDE (UserID, UserName);

Используя этот индекс, вы получите хороший поиск по НЕКЛАСТЕРНОМУ ИНДЕКСУ в вашем плане выполнения.

Кроме того, выбор порядка индексированных столбцов имеет значение. Допустим, ваша таблица будет содержать UserTypeID (их не так много). Таким образом, вы должны передать некоторые конкретные идентификаторы UserTypeID и список идентификаторов UserID, а затем SQL Server, вероятно, захочет выбрать индекс с UserTypeID в качестве первого индексированного столбца.

Итак, некоторые тесты:

CREATE TABLE #Users
(
    UserId INT
    , UserName VARCHAR(500)
    , Email VARCHAR(500)
    , Password VARCHAR(500)
);

CREATE CLUSTERED INDEX idx_Users_UserID
    ON #Users (UserID);

-- Some test data from my DB
INSERT INTO #Users (UserId, UserName, Email, Password)
SELECT TOP (10000) UserId, UserName, Email, 'password'
FROM Users;

Так что это запрос:

SELECT *
FROM #Users;

Будет выполнено сканирование индекса, поскольку мы не указываем никаких деталей.

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

SELECT *
FROM #Users
WHERE UserID = 602;

Теперь давайте создадим индекс без включенных столбцов и сделаем запрос:

CREATE NONCLUSTERED INDEX idx_Users_Email_Password
    ON #Users (Email, Password);

SELECT *
FROM #Users
WHERE Email = '[email protected]';

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

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

CREATE NONCLUSTERED INDEX idx_Users_Email_Password_iUserName
    ON #Users (Email, Password)
    INCLUDE (UserName);

Это высококачественная статья, и я бы рекомендовал ее прочитать: https://www.simple-talk.com/sql/performance/index-selection-and-the-query-optimizer/

Ответ 2

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