Как установить правильный индекс для соединения SQL с предложением where
Я некоторое время искал в Google, но не нашел ответа, который решает мою «проблему».
У меня есть 3 примера со следующей структурой.
Клиент:
- Пользовательский ИД
- Имя
- Фамилия
- Пол
- IsDeleted
Выставленный счет:
- Идентификатор счета
- Пользовательский ИД
- Количество
- Позиции
- Является действительным
InvoicePos:
- InvoicePosId
- Идентификатор счета
- PosName
- Оплачено
Теперь я хотел бы присоединиться к ним со следующим запросом.
SELECT T1.FirstName,
T1.LastName,
T2.Amount,
T3.PosName
FROM Customer AS T1
JOIN Invoice AS T2 ON T1.CustomerId = T2.CustomerId
JOIN InvoicePos AS T3 ON T2.InvoiceId = T3.InvoiceId
WHERE T1.FirstName = 'A'
AND T1.LastName = 'B'
AND T2.Positions = 3
AND T3.IsPaid = 1;
Для таблицы Customer у меня есть индекс для имени, фамилии, идентификатора клиента
Для InvoicePos у меня есть индекс для IsPaid, InvoiceId, InvoicePosId
Но какой индекс я должен использовать для таблицы Invoice?
t1, t2 и t3 — плохие псевдонимы таблиц. Используйте псевдонимы таблиц, которые имеют смысл, например C для клиента.
Чтобы расширить комментарий @jarlh: Плохие привычки, от которых следует избавиться: использование псевдонимов таблиц, таких как (a, b, c) или (t1, t2, t3)
Ответы 5
Вы также можете запустить оптимизатор запросов для своего запроса, и SQL предоставит вам лучшие индексы для применения, а также покажет вам увеличение производительности в %.
Это не поможет, если это некластеризованный индекс; это не будет покрывающим индексом. И даже в этом случае кластерный индекс только по этим двум столбцам не поможет WHERE, который не ссылается на эти столбцы.
Только для этого точного запроса размещение некоторого индекса в Table2/Invoice с CustomerId, Positions было бы идеальным.
Хотя обычно имеют смысл следующие индексы:
Customer:
unique index on CustomerId
index on LastName, FirstName
Invoice:
unique index on InvoiceId
index on CustomerId
InvoicePos:
unique index on InvoicePosId
unique index on InvoiceId, InvoiceId
index on IsPaid, InvoiceId (for your scenario)
используйте эти индексы:
Create Index IX__Customer_001 on Customer (CustomerId,FirstName,LastName)
Create Index IX__Invoice_001 on Invoice (CustomerId,InvoiceId,Positions) include (Amount)
Create Index IX__InvoicePos_001 on InvoicePos (InvoiceId,IsPaid) include (PosName)
вы должны использовать поля, которые использовались только как столбцы выбора в предложении include, и помещать предикаты в ключевые столбцы.
Добро пожаловать в Stack Overflow! это поможет, если вы потратите время, чтобы объяснить свой ответ, чтобы ОП понял, почему вы сделали этот выбор. Ответ только по коду часто не помогает другим пользователям учиться, особенно если они его не понимают.
Моя основная проблема заключается в том, чтобы понять, в каком порядке разрешается запрос.
В столбцах AI есть несколько индексов (CostumerId, InvoiceId, InvoicePosId).
Индекс в таблице 1 (имя, фамилия, костюмер) имеет смысл, поскольку он фильтрует имена, а затем объединяется с таблицей счетов-фактур.
А вот второй индекс мне непонятен.
Для этого запроса:
SELECT T1.FirstName, T1.LastName, T2.Amount, T3.PosName
FROM Customer T1 JOIN
Invoice T2
ON T1.CustomerId = T2.CustomerId JOIN
InvoicePos T3
ON T2.InvoiceId = T3.InvoiceId
WHERE T1.FirstName = 'A' AND
T1.LastName = 'B' AND
T2.Positions = 3 AND
T3.IsPaid = 1;
Я бы рекомендовал следующие индексы:
Customer(FirstName, LastName, CustomerId)Invoice(CustomerId, InvoiceId, Positions, Amount)InvoicePost(InvoiceId, IsPaid, PosName).
Во-первых, это покрывающие индексы для вашего запроса. Итак, нужны только индексы. Если у вас есть дополнительные столбцы в SELECT, вы можете удалить столбцы только для SELECT из индексов.
Идея состоит в том, чтобы начать с самых избирательных WHERE условий. Я предполагаю, что ограничения на имена являются наиболее строгими. Следовательно, индексация начинается с Customer и использует условия WHERE. Затем добавляются дополнительные столбцы из предложений ON и SELECT.
Для остальных таблиц столбцы JOIN являются первыми ключами в индексе, за которыми следуют столбцы WHERE и SELECT.
Спасибо, это помогает мне лучше понять проблему. Остался единственный вопрос для таблицы 2, как установить индекс. «CustomerId + позиции» или «InvoiceId + позиции»
Индексы — это не то, чему можно просто «научить» в простом ответе; требуется гораздо большее понимание. Вам, вероятно, будет лучше читать статьи и документацию или даже пройти курс, посвященный лучшим способам индексации ваших таблиц. Вы не упомянули первичные ключи, и если они сгруппированы, это также имеет большое значение.