Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Еще вопрос про индексы  [new]
фыфывфывфы
Guest
Всем привет.
Есть таблица 1С - _AccumReg21882
У нее есть два индекса

1. Кластерный, PK, содержащий следующие столбцы
_Active
_LineNo
_Period
_RecorderRRef
_RecorderTRef


2. Некластерный
_LineNo
_RecorderRRef
_RecorderTRef


Есть запрос

select 
top 10000 
[_Active], [_RecorderTRef]
from [_AccumReg21882]


При выполнении этого запроса, как мне казалось, должно было произойти Cluster Index Scan
Но, почему-то, SQL Server делает Index nonclustered scan
Скрин плана запроса - в аттаче.

Или текстовый вариант

1. В случае использования некластерного индекса
  |--Top(TOP EXPRESSION:((10000)))
       |--Index Scan(OBJECT:([NewUpp3].[dbo].[_AccumReg21882].[_Accum21882_ByRecorder_RN]))

(10000 row(s) affected)
Table '_AccumReg21882'. Scan count 1, logical reads 83, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 12 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.


2. При использовании кластерного
  |--Top(TOP EXPRESSION:((10000)))
       |--Clustered Index Scan(OBJECT:([NewUpp3].[dbo].[_AccumReg21882].[_Accum21882_ByPeriod_TRN]))

(10000 row(s) affected)
Table '_AccumReg21882'. Scan count 1, logical reads 432, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 13 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.


У меня есть два вопроса:
1. Почему выбирается этот индекс? Судя по статистике - да он более "дешевле", чем сканирование кластерного индекса.
2. Если он выбирает второй индекс, откуда и как он извлекает данные по столбцу, которого нет в индексе?
27 мар 12, 09:11    [12318199]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> 1. Почему выбирается этот индекс? Судя по статистике - да он более "дешевле", чем сканирование кластерного индекса.

гм. тогда в чем вопрос? все логично, вроде.

> 2. Если он выбирает второй индекс, откуда и как он извлекает данные по столбцу, которого нет в индексе?

столбцы кластерного индекса содержатся во всех некластерных.

Posted via ActualForum NNTP Server 1.5

27 мар 12, 09:17    [12318211]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
фыфывфывфы,
фыфывфывфы
2. Если он выбирает второй индекс, откуда и как он извлекает данные по столбцу, которого нет в индексе?

можно иногда все же почитать BOL
27 мар 12, 09:23    [12318224]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
фыфывфывфы
Guest
denis2710,

Иногда все же читаю.

Тогда вопрос - почему в плане не видно "извлечения" столбца _Active ?
27 мар 12, 09:30    [12318245]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
фыфывфывфы
Guest
Все, разобрался.
Спасибо за участие :)
27 мар 12, 09:31    [12318248]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
фыфывфывфы
denis2710,

Иногда все же читаю.

Тогда вопрос - почему в плане не видно "извлечения" столбца _Active ?
Почему - как раз по ссылке написано.

http://msdn.microsoft.com/ru-ru/library/ms177484%28v=sql.100%29.aspx
Каждая строка некластеризованного индекса содержит некластеризованное ключевое значение и указатель на строку.

http://msdn.microsoft.com/ru-ru/library/ms177484%28v=sql.100%29.aspx
Если для таблицы имеется кластеризованный индекс или индекс построен на индексированном представлении, то указатель строки — это ключ кластеризованного индекса для строки.
27 мар 12, 09:36    [12318268]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
фыфывфывфы
Guest
Гавриленко Сергей Алексеевич,

Да, спасибо :)
Прочитав именно эту строку для меня все стало понятно. До этого, видимо, не очень внимательно читал. Спасибо denis2710

Тогда еще вопрос.
Я правильно тогда понимаю, что размер некластерного индекса = размер столбцов некласт. индекса + размер столбцов кластерного?
27 мар 12, 09:57    [12318373]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
фыфывфывфы
Guest
И еще.
Если в кластерном индексе уже присутствует поле, предположим _Active, то нет смысла включать его в некластерный ?
27 мар 12, 09:59    [12318386]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
фыфывфывфы,

фыфывфывфы
Если в кластерном индексе уже присутствует поле, предположим _Active, то нет смысла включать его в некластерный ?
Да.

пора уже начинать читать BOLТам вообще много ответов,на разные вопросы :)
27 мар 12, 10:07    [12318420]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
фыфывфывфы
И еще.
Если в кластерном индексе уже присутствует поле, предположим _Active, то нет смысла включать его в некластерный ?
Для выборки этого поля не имеет смысла, а для поиска по этому полю имеет.
27 мар 12, 10:13    [12318447]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
плюс еще один момент:
https://www.sql.ru/blogs/shcherbinin/1011
27 мар 12, 10:31    [12318587]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
фыфывфывфы
Guest
alexeyvg,

Хмм... если следовать ссылке RayD, получается, что создав уникальный кластер. индекс, включать поле, участвующее в кластерном, в некластерный вообще не имеет смысла - ни для выборки, ни для поиска.
27 мар 12, 11:36    [12319094]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
netivan
Member

Откуда:
Сообщений: 8768
фыфывфывфы
alexeyvg,

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

тоже самое хотел спросить :).
27 мар 12, 12:17    [12319426]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
фыфывфывфы
alexeyvg,

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

1. Правильно, однако некластерный индекс должен быть неуникальным.
2. Там есть небольшая поправка:
Замечание msLex -- у Дилани написано, что это будет происходить только в том случае, если кластерный индекс уникальный. Тесты же показывают, что это происходит в любом случае.
27 мар 12, 12:26    [12319499]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
netivan
фыфывфывфы
alexeyvg,

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

тоже самое хотел спросить :).
Для поиска очень важен порядок полей в индексе.
27 мар 12, 12:29    [12319528]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
AndyD
Member

Откуда:
Сообщений: 30
фыфывфывфы,

Для некластерного индекса из вашего вопроса поля в интексе будут перечисляться так

_LineNo
_RecorderRRef
_RecorderTRef
_Active
_Period


если поля _Recorder* не включать в индекс, то поля будут идти так

_LineNo
_Active
_Period
_RecorderRRef
_RecorderTRef


т.е. если искать по полям _LineNo и _RecorderRRef, то индекс может и не быть задействован вообще (зависит от распределения данных в полях _Active и _Period, конечно)


Что касается неуникального кластерного индекса, то к полям, входящим в него, добавляется еще один столбец - UNIQUIFIER, которое делает полный кластерный ключ уникальным.
Это поле так же добавляется во все уровни некластерных индексов, наряду с явно заданными полями кластерного ключа
27 мар 12, 12:38    [12319604]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
netivan
фыфывфывфы
alexeyvg,

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

тоже самое хотел спросить :).
Насколько я понимаю, поля из кластерного индекса включаются так же, как поля INCLUDE, то есть они могут потом использоваться просто как данные (в том числе для наложения фильтра), но в дерево они не включаются, то есть B-дерево индекса не "продолжается" этими полями.

Соответственно и эффективного поиска не будет.
27 мар 12, 13:19    [12319979]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
alexeyvg
Соответственно и эффективного поиска не будет.
Дык написано же, что это не всегда так: https://www.sql.ru/blogs/shcherbinin/1011
27 мар 12, 13:21    [12319995]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Ray D
плюс еще один момент:
https://www.sql.ru/blogs/shcherbinin/1011
Получается, что индекс расширяется кластерным, дерево продолжается? Да, интересно, нужно попробовать, и почитать пост Kalen Delaney ...
27 мар 12, 13:23    [12320021]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Гавриленко Сергей Алексеевич
alexeyvg
Соответственно и эффективного поиска не будет.
Дык написано же, что это не всегда так: https://www.sql.ru/blogs/shcherbinin/1011
Да, уже прочёл :-)
27 мар 12, 13:23    [12320028]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
netivan
Member

Откуда:
Сообщений: 8768
alexeyvg,

не понял. ведь данные хранятся на листовом уровне, я чего-то не понял =\
27 мар 12, 13:42    [12320223]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
alexeyvg
Ray D
плюс еще один момент:
https://www.sql.ru/blogs/shcherbinin/1011
Получается, что индекс расширяется кластерным, дерево продолжается? Да, интересно, нужно попробовать, и почитать пост Kalen Delaney ...
Отлично ищет, был неправ...

При наличии "правильного" индекса предпочитает его, но ищет и в маленьком индексе по скрытым полям из кластерного индекса.

Вот скрипт для таких же неверющих, как я :-)

+

create table test_index (f int not null, pk1 int not null, pk2 int not null identity, primary key clustered (pk1, pk2))
go

;with cte
as (
	select top 100 ROW_NUMBER() over (order by object_id) as n
	from sys.columns
)
insert test_index(f, pk1)
select c1.n, c2.n
from cte c1
	cross join cte c2	
	cross join cte c3
	
go
create index ix_test_index_f on test_index (f)
go
select *
from test_index
where f=50 and pk1 = 50
go

create index ix_test_index_f_pk1 on test_index (f, pk1)
go
select *
from test_index
where f=49 and pk1 = 49

go
drop table test_index 

27 мар 12, 13:44    [12320249]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
netivan
не понял. ведь данные хранятся на листовом уровне, я чего-то не понял =\
Получается, не только на листовом - B-дерево продолжается.
27 мар 12, 13:46    [12320266]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
netivan
Member

Откуда:
Сообщений: 8768
alexeyvg
netivan
не понял. ведь данные хранятся на листовом уровне, я чего-то не понял =\
Получается, не только на листовом - B-дерево продолжается.
я вас не понял. лист = конец дерева.
27 мар 12, 18:01    [12322651]     Ответить | Цитировать Сообщить модератору
 Re: Еще вопрос про индексы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
netivan
alexeyvg
пропущено...
Получается, не только на листовом - B-дерево продолжается.
я вас не понял. лист = конец дерева.
Да, я понял.

Но тут эти данные будут не только на листовом уровне (то есть не только в конце дерева)

Т.е. допустим у вас для одного значения _LineNo,_RecorderRRef,_RecorderTRef будет ещё стотыщ записей

Тогда найденная запись в индексе будет указывать не на список всех этих записей, а на промежуточный уровень с диапазонами кластерного индекса.
И включение в условия поиска кроме этих трёх полей ещё и поля _Active будет ограничивать поиск без скана всех статыщ записей на листовом уровне.
27 мар 12, 18:35    [12322927]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить