Не очень известный факт о кластерном индексе

добавлено: 03 авг 11
понравилось:0
просмотров: 3240
комментов: 15

теги:

Автор: Ray D

Казалось бы все уже давно разжевано, однако об одном интересном факте мало кто знает --
при наличии кластерного индекса (уникального или неуникального), при создании неуникальных некластерных индексов,
ключ кластерного индекса будет не только на листовом уровне дерева, но и в промежуточных узлах.

Об этом подробно написала Kalen Delaney в своем блоге:
http://sqlblog.com/blogs/kalen_delaney/archive/2010/03/07/more-about-nonclustered-index-keys.aspx

Не пересказывая г-жу Дилани, отмечу только, что это приводит к интересному эффекту --
при наличии уникального кластерного индекса по полю X и неуникального некластерного
индекса по полю Y, для запроса вида
select ... where Y = @param1 and X = @param2
при использовании некластерного индекса, index seek будет по обоим полям -- Y, X, а не только по Y.
Если же этот индекс сделать уникальным, то, как и ожидается, ключ кластерного индекса будет только
на листовом уровне, и index seek будет только по Y.

Замечание msLex -- у Дилани написано, что это будет происходить только в том случае, если кластерный индекс уникальный. Тесты же показывают, что это происходит в любом случае.

Комментарии


  • 03 августа 2011, 13:17 Александр Гладченко

    ...index seek будет по обоим полям -- Y, X, а не только по Y...
    Т.е. можно расчитывать, что поиск будет по колонке Y и/или по колонке X?

  • для запроса select .. where Y = @param1 -- seek по Y, для select .. where Y = @param1 and X = @param2 -- seek по Y, X. По X и так seek будет для select .. where X = @param, но уже по кластерному индексу.

  • непонятно одно: зачем это?
    если Х уникально и по нему есть кластерный индекс - почему бы не делать seek по кластерному?

    видимо имелась в виду ситуация с "select x,y from ...", а не просто "select ... where..." ...

  • что-то зацепило меня :) еще в догонку:

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

  • 1. Ну я же говорю, "при использовании", т.е. если сервер решит его использовать. Может быть и select x,y,z from ... where x = .. and y = .. с lookup-ом.
    2. Там же в блоге Дилани в каментах:
    .. Actually regarding my comment above I assume that this is just for delete/update performance. If an Update/Delete is carried out on a table row it would need some way of efficiently locating the corresponding non clustered index row?
    3. Совершенно верно, будет увеличиваться и дерево.

  • Влад, небольшое уточнение. Является кластерный индекс уникальным или нет не важно.

  • Ага, правильное уточнение, исправил.

  • Честно говоря не наблюдаю "интересному эффекту" ДАЖЕ при форсировании использования неуникального некластерного индекса Y. Никаких двойных сиков, один сик+лук-ап, вполне ожидаемо. Если нечего не форсировать будет ровно один сик, по кластерному. Это все на SQL Server 2008R2SP1. Можно запускабельный пример для иллюстрации той мысли коей посвящена заметка?

  • [sql]

    if object_id(N'dbo.test', N'U') is not null begin
    drop table dbo.test
    end
    go

    create table dbo.test (
    a int not null
    , b int not null
    , c int not null
    )
    go

    insert into dbo.test (
    a
    , b
    , c
    )
    select top (100000)
    abs(checksum(newid())) % 100
    , abs(checksum(newid())) % 100
    , abs(checksum(newid())) % 100
    from sys.objects s1
    cross join sys.objects s2
    cross join sys.objects s3
    go

    create clustered index ix_a on dbo.test(a)
    go

    create nonclustered index ix_b on dbo.test(b)
    go

    select a
    , b
    , c
    from dbo.test with(index(ix_b))
    where a = 5
    and b = 10
    go

    drop table dbo.test
    go
    [/sql]
    Смотрите внимательно план (seek predicates).

  • Именно практически такой опыт и делал перед тем как написать первый комментарий. Моя интерпретация плана исполнения с форсированным неуник., некласт. индексом (ННИ):
    -все начинается с Index Seek в рамках которого:
    **сканируем ННИ в поисках такого сочетания значений b+a как 10+5 (в данном случае "+" это НЕ сложение, а именно сочетание).
    **находим все нужные строки (в моем примере - 14 штук) ЗА ОДИН СИК (!). Один сик, только по ННИ.
    **нашли. Но на выходе обсуждаемого оператора плана (Index Seek) будет не 2 колонки, а 3: b+a+uniquifier кластерного ключа, который включается в ключ индекса ix_a нравится нам это или нет; в моем случае данная колонка зовется Uniq1002.
    -все найденные строки (числом 14) становятся внешней таблицей для оператора Nested Loops.
    -для каждой строки этой внешней таблицы выполняется Лук-Ап, и вот ОН-то проворачивается 14 раз, никто и не спорит - а как иначе?
    -лук-ап идет по кластерному индексу ix_a по составному ключу a+Uniq1002 и его единственная цель - достать колонку "c". О том что ключ будет именно составной ведется речь в "главной" статье Kalen Delaney.
    -наконец, "c" "подстыковывается" к a+b+Uniq1002, а последняя отбрасывается - готово!

    Итого: ОДИН (!) сик + 14 лук-апов. НЕТУ двух сиков. Однако с удовольствием прочитаю вашу интерпретацию того же самого - может я конкретно "не врубаюсь" в вопрос?

  • Да не два сика, а сик по двум полям :) Посмотрите еще раз в seek predicates.

  • >>Да не два сика, а сик по двум полям

    Разумеется, вполне согласен. Ведь нас интересует СОЧЕТАНИЕ a и b и поскольку оно, сочетание это, "под рукой" у сика - то и соответственно.

    >>Посмотрите еще раз в seek predicates.

    Я, по-моему, все что увидел в предикате в частности и в плане выполнения вообще расписал едва ли не на уровне байтов. Если чего продолжаю не видеть - подскажите, будьте добры.
    А пока я по прежнему не врубаюсь - чему посвящена заметка? Если просто прорекламировать статью Kalen Delaney - нет вопросов, последняя заслуживает самой широкой рекламы. Но мне казалось, автор заметки хотел своих мыслей добавить, нет?

  • Вы статью Дилани читали? А то, что я написал?
    >Не пересказывая г-жу Дилани, отмечу только, что это ?>приводит к интересному эффекту
    Еще раз, коротко, в случае уникального некластерного индекса, ключ кластерного будет только на листовом уровне, и в случае Y =.. and X =.. сик будет только по Y! По X будет фильтрация.

    В случае неуникального некластерного индекса -- ключ кластерного будет еще и в дереве, и сик будет и по Y, и по X.

  • >Я, по-моему, все что увидел в предикате в частности и в >плане выполнения вообще расписал едва ли не на уровне >байтов. Если чего продолжаю не видеть - подскажите, ?>будьте добры.
    Вы подробно расписали план, все так и есть. Но в seek predicates, похоже, так и не заглянули. Сделайте еще один тест с уникальным некластерным индексом.

  • >>Сделайте еще один тест с уникальным некластерным индексом.

    О! Вот этого мне и не хватало - спасибо! Уловил разницу... Вы все правильно подметили - в этом случае движок ищет только по b, потому как нет у него сочетаний b+a в случае _уникального_ некластерного. Точнее есть, но лишь на листовом уровне b-индекса, а не так как в первом опыте. Хотя сам план, замечу, не изменился совершенно - тот же СИК+тот же ЛукАп, разница только в Seek Predicate и все. Спасибо что помогли разобраться!



Необходимо войти на сайт, чтобы оставлять комментарии