Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вопрос по использованию индексов в запросе  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 25
Добрый день.

Помогите, пожалуйста, разобраться в логике использования индексов

1. Есть таблица

Id int
CustomerId int
Name nvarchar(100)
..... (несколько других полей)

2. Есть кластерный PK по Id

3. Есть индекс по CustomerId.

Пробуем два варианта запроса:

1. SELECT * FROM [Table] where CustomerId = 1

Execution plan показывает, что идёт Clastered index scan.

2. SELECT * FROM [Table] where id in (SELECT[Id] FROM [Table] where CustomerId = 233365)

Execution plan показывает, что идут два Index seek (по CustomerId и по PK), потом Nested loops.

Собственно, вопросы

- почему в первом случае не используется индекс по полю?
- станет ли MS Sql его использовать при большом количестве записей?
- нужно ли как-то форсировать использование индекса или всё равно не сделю лучше, чем разработчики Ms SQL и в нужный момент будет автоматически лучший путь выборки?

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

Сообщение было отредактировано: 10 июн 21, 15:42
10 июн 21, 15:50    [22333904]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1775
Sergey Gusev
Добрый день.
- почему в первом случае не используется индекс по полю?


потому что вы выбираете все поля (select * ) а таких поле в некластерном индексе нет, поэтому оптимизатор считает что скан кластерного индекса будет дешевле

если перепишите запрос на
SELECT [id] FROM [Table] where CustomerId = 1


получите поиск.


не используйте никогда select * при тестах, выбирайте только те поля которые в действительности вам нужны

Дополню: если строк в таблице мало или по гистограмме видно что предикат не будет особенно селективен оптимизатор все равно может посчитать что скан будет дешевле.

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

Сообщение было отредактировано: 10 июн 21, 16:04
10 июн 21, 16:06    [22333922]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
msLex
Member

Откуда:
Сообщений: 9022
felix_ff
Sergey Gusev
Добрый день.
- почему в первом случае не используется индекс по полю?


потому что вы выбираете все поля (select * ) а таких поле в некластерном индексе нет, поэтому оптимизатор считает что скан кластерного индекса будет дешевле

если перепишите запрос на
SELECT [id] FROM [Table] where CustomerId = 1



получите поиск.


не используйте никогда select * при тестах, выбирайте только те поля которые в действительности вам нужны


второй запрос автора вручную реализует механизм key lookup.



Sergey Gusev
- почему в первом случае не используется индекс по полю?

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

Sergey Gusev
- станет ли MS Sql его использовать при большом количестве записей?

Да

Sergey Gusev
- нужно ли как-то форсировать использование индекса или всё равно не сделю лучше, чем разработчики Ms SQL и в нужный момент будет автоматически лучший путь выборки?

"Зависит от"
Для вашего первого запрос при таких индексах хинт (forceseek) вряд ли навредит.

SELECT [id] FROM  [Table] with(forceseek)  where CustomerId = 1


Сообщение было отредактировано: 10 июн 21, 16:06
10 июн 21, 16:14    [22333929]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 25
felix_ff

не используйте никогда select * при тестах, выбирайте только те поля которые в действительности вам нужны


Мне как раз нужны все поля в конечном результате. И я сейчас в размышлениях - делать ли самостоятельную выборку сначала id по простому индексу, а потом все поля по кластерному. Или это будет напрасной тратой усилий и оптимизатор запросов MS SQL решит эту проблему за меня.
10 июн 21, 16:15    [22333930]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1775
Sergey Gusev
felix_ff

не используйте никогда select * при тестах, выбирайте только те поля которые в действительности вам нужны


Мне как раз нужны все поля в конечном результате. И я сейчас в размышлениях - делать ли самостоятельную выборку сначала id по простому индексу, а потом все поля по кластерному. Или это будет напрасной тратой усилий и оптимизатор запросов MS SQL решит эту проблему за меня.


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

если нужны все поля делайте или кластерный индекс по [CustomerId]

или модифицируйте свой некластерный индекс что бы он покрывал запрос, (используя INCLUDE список полей которые будут участвовать в конечном селекте)
10 июн 21, 16:19    [22333933]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 25
msLex
Для вашего первого запрос при таких индексах хинт (forceseek) вряд ли навредит.


Вот спасибо, добрый человек! Поставил - и прямо всё как хотел - Index seek и Key lookup
10 июн 21, 16:19    [22333934]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1775
Sergey Gusev
msLex
Для вашего первого запрос при таких индексах хинт (forceseek) вряд ли навредит.


Вот спасибо, добрый человек! Поставил - и прямо всё как хотел - Index seek и Key lookup


Любой Key Lookup / RID Lookup относительно дорогая операция, старайтесь как раз их избегать.

На малом объеме возвращаемых данных вы особо не увидите накладных расходов, а когда объемы данных подрастут то вернуть к примеру набор в 100000 строк с использованием Key/RID Lookup уже будет достаточно наглядно видно что показывает оверхед по статистики IO

Сообщение было отредактировано: 10 июн 21, 16:15
10 июн 21, 16:21    [22333938]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 25
felix_ff
Любой Key Lookup / RID Lookup относительно дорогая операция


А я думал, это даже луче, чем Index Seek.

Получается, мой второй вариант (из стартового сообщения) - оптимальный? Там два Index seek.
10 июн 21, 16:27    [22333942]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
msLex
Member

Откуда:
Сообщений: 9022
Sergey Gusev
felix_ff
Любой Key Lookup / RID Lookup относительно дорогая операция


А я думал, это даже луче, чем Index Seek.

Получается, мой второй вариант (из стартового сообщения) - оптимальный? Там два Index seek.

Физически это одно и тоже.

Range Scan (все записи из индекса по заданному CustomerId) - 1 "спуск по дереву"
По каждой из полученных записей поиск в кластерном индексе - N "спусков по дереву"


Надо понимать, что каждый единичный index seek, это random IO, а перебор записей одной за другой - это последовательное чтенье (в том числе в рамках одной страницы) с read ahead вычиткой страниц.

Что будет быстрее в конкретном случае - вопрос.

В SQL Server заложены некие эмпирические оценки этих операций.

Сообщение было отредактировано: 10 июн 21, 16:28
10 июн 21, 16:36    [22333945]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1775
Sergey Gusev
felix_ff
Любой Key Lookup / RID Lookup относительно дорогая операция


А я думал, это даже луче, чем Index Seek.

Получается, мой второй вариант (из стартового сообщения) - оптимальный? Там два Index seek.


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

+

use tempdb;

drop table if exists #tmp1, #tmp2;

create table #tmp1 (
      [id] int index IX clustered,
      [customerID] int index NIX,
      [name] sysname,
      [surname] sysname,
      [patronymic] sysname,
      [comment] nvarchar(4000)
);

create table #tmp2 (
      [id] int,
      [customerID] int index IX clustered,
      [name] sysname,
      [surname] sysname,
      [patronymic] sysname,
      [comment] nvarchar(4000)
);

create table #tmp3 (
      [id] int index IX clustered,
      [customerID] int,
      [name] sysname,
      [surname] sysname,
      [patronymic] sysname,
      [comment] nvarchar(4000)
);
create nonclustered index NIX on #tmp3 ([customerID]) include ([name], [surname], [patronymic], [comment]);



insert into #tmp1
select top (50000)
      row_number() over (order by 1/0),
      1+ cast(CRYPT_GEN_RANDOM(4, 0x25F18060) as bigint) % 10,
      cast(newid() as nvarchar(128)),
      cast(newid() as nvarchar(128)),
      cast(newid() as nvarchar(128)),
      replicate('A', cast(CRYPT_GEN_RANDOM(4) as bigint) % 4000)
from master.dbo.spt_values c1
    cross join master.dbo.spt_values c2;

insert into #tmp2 select * from #tmp1;
insert into #tmp3 select * from #tmp1;


set statistics io, time on;

print '------------------------TEST-----------------------'
print '#tmp1 nonclustered index seek'
select [id] from #tmp1 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp1 nonclustered index seek + keylookup' 
select * from #tmp1 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp1 clustered index seek'
select * from #tmp1 where [id]  < 40;
print '---------------------------------------------------'
go

print '#tmp2 clustered index seek specific column'
select [id] from #tmp2 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp2 clustered index seek all columns' 
select * from #tmp2 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp2 clustered index scan'
select * from #tmp2 where [id]  < 40;
print '---------------------------------------------------'
go

print '#tmp3 nonclustered index seek specific column'
select [id] from #tmp3 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp3 nonclustered index seek all columns' 
select * from #tmp3 where [customerID] = 4;
print '---------------------------------------------------'
go
print '#tmp3 clustered index scan'
select * from #tmp3 where [id]  < 40;
print '---------------------------------------------------'
go



а еще можете поиграться с самой выборкой и к примеру из select *, сделать выборку где не будет выбираться колонка [comment] и циферки начнут изменяться

Сообщение было отредактировано: 10 июн 21, 16:50
10 июн 21, 16:53    [22333951]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по использованию индексов в запросе  [new]
Sergey Gusev
Member

Откуда:
Сообщений: 25
felix_ff
Ну держите Вам тест для игры, можете сами поиграться с вариантами выборок.


Спасибо, как раз сейчас сам это сделал. Добавил 4.7 миллионов записей и попробовал запросы из своего первого сообщения.

Первый запрос перестал быть IndexScan, стало IndexSeek + KeyLookup. Второй остался прежним. По скорости практически идентичны.

Спасибо всем, кто отвлёкся на мой вопрос.
10 июн 21, 17:12    [22333964]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить