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

Откуда: Реутов
Сообщений: 219
Помогите разобраться.
Тюниг адвайзер часто советует включить поле ID, по которому построен кластерный индекс, в состав ключа некластерного индекса или в список включаемых полей некластерного индекса. Это ошибка адвайзера или в каких-то случаях это может быть полезным?
14 ноя 12, 16:19    [13473431]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
50/50 что фигню спорю, но вроде как если его явно включать - оно будет там сортированное. а если не включать - оно там все равно будет, но уже несортированное. а это может быть важно для некоторых запросов

а речь про адвайзер или про sys.dm_db_missing_index*? или адвайзер советы дает как раз на основани анализа sys.dm_db_missing_index*? потому как в "эта ваша" sys.dm_db_missing_index* ничего не знает про то, что поля кластерного все равно входят в некластерный, если еще не исправили. а адвайзер, вроде как, "гипотетическими" индексами развлекался.. или это "когда было" и все давно поменялось?
14 ноя 12, 16:37    [13473572]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 219
Речь именно про адвайзер.

автор
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86) Jun 28 2012 08:42:37 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

Database Engine Tuning Advisor 10.50.1600.1
14 ноя 12, 17:12    [13473809]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
ROLpogo,

Структура некластерного индекса может быть разная.
1. Если есть на таблице кластерный индекс, тогда на конечных листах (leaf nodes) некластерного индекса есть ссылка на кластерный индекс.
(поэтому добавлять в этом случае кластерный индекс в некластерный не нужно)

2. Если на таблице нет кластерного индекса, тогда на конечных листах (leaf nodes) некластерного индекса - данные.

Индексы
14 ноя 12, 17:14    [13473824]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
denis2710
Member

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

2. Если на таблице нет кластерного индекса, тогда на конечных листах (leaf nodes) некластерного индекса - данные.


А не RID ли там храниться,помимо значений ключей индекса?
На сколько я помню,добавления ключа кластерного индекса в не кластерный SQL Server проигнорирует.
14 ноя 12, 17:32    [13473911]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
sphinx_mv
Member [заблокирован]

Откуда:
Сообщений: 1672
trew
ROLpogo,
2. Если на таблице нет кластерного индекса, тогда на конечных листах (leaf nodes) некластерного индекса - данные.

Индексы
"Звезды говорят" (с), что при отсутствии кластерного индекса данные таблицы лежат в "куче", а на "leaf nodes" индекса находятся "row id" соотвествующей записи таблицы...
14 ноя 12, 17:43    [13473951]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
если предлагается включить в состав ключа некластерного индекса, нужно ещё обратить внимание на предлагаемый порядок полей.
14 ноя 12, 17:55    [13474021]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 219
trew
ROLpogo,

Структура некластерного индекса может быть разная.
1. Если есть на таблице кластерный индекс, тогда на конечных листах (leaf nodes) некластерного индекса есть ссылка на кластерный индекс.
(поэтому добавлять в этом случае кластерный индекс в некластерный не нужно)

2. Если на таблице нет кластерного индекса, тогда на конечных листах (leaf nodes) некластерного индекса - данные.

Индексы


Разве из вопроса не понятно, что разбираем ситуацию с наличием кластерного индекса?
Только что проверил, и Тюнинг Адвизор и План выполнения запроса предлагают включить поле ID в ключ некластерного индекса, при существующем кластерном на поле ID. Если его ослушаться и не включать поле ID в некластерный индекс, то скан заменяется сиком и все счастливы. Так в чем же подвох?
14 ноя 12, 18:04    [13474075]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Crimean
Member

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

поскольку так же себя ведут и вышеупомянутые DMV, то можно считать это "особенностью"
но разница все же есть:

create table a1 ( id int primary key , flag int )
go
create index ia1_1 on a1 ( flag )
go
create table a2 ( id int primary key , flag int )
go
create index ia2_1 on a1 ( flag, id )
go
insert into a1( id, flag ) select id , id from sysobjects
go
insert into a2( id, flag ) select id , id from sysobjects
go
drop table a1
go
drop table a2
go


К сообщению приложен файл. Размер - 22Kb
14 ноя 12, 19:20    [13474522]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
ой! индексы, конечно же, на первую таблицу сделал оба - предыдущее сообщение не читать
14 ноя 12, 19:28    [13474580]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
msLex
Member

Откуда:
Сообщений: 9291
Crimean
50/50 что фигню спорю, но вроде как если его явно включать - оно будет там сортированное. а если не включать - оно там все равно будет, но уже несортированное. а это может быть важно для некоторых запросов



https://www.sql.ru/blogs/shcherbinin/1011
14 ноя 12, 19:46    [13474687]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Shakill
если предлагается включить в состав ключа некластерного индекса, нужно ещё обратить внимание на предлагаемый порядок полей.
Вы думаете тот порядок полей который вам советует в плане запроса имеет хоть какой то смысл? Сколько я видел примеров, поля всегда просто тупо отсортированы по алфавиту.
14 ноя 12, 21:08    [13474983]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Crimean
но разница все же есть:
Конечно она будет. На первой таблице 3 индекса, на второй 1. Очепятка :)
Вот данные нормального теста, разницы никакой. Да её и не должно быть:

Table 'a1'. Scan count 0, logical reads 1502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'a2'. Scan count 0, logical reads 1502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Вот внутренняя структура индексов. Тоже, попробуйте найти 5 отличий? Их нет.

К сообщению приложен файл. Размер - 19Kb
14 ноя 12, 21:19    [13475013]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ROLpogo
Так в чем же подвох?
В том что адвайзер, скорее всего, не принимает во внимание наличие кластерных индексов. И генерит более безопасный скрипт. Который будет верным даже если кто-то снесет кластерный индекс.
14 ноя 12, 21:26    [13475026]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
ROLpogo
Помогите разобраться.
Тюниг адвайзер часто советует включить поле ID, по которому построен кластерный индекс, в состав ключа некластерного индекса или в список включаемых полей некластерного индекса. Это ошибка адвайзера или в каких-то случаях это может быть полезным?


Необходимо помнить, что ссылка на кластерный индекс есть только в листьях индекса, а в бинарном дереве нет этих ссылок. Соответственно, если критерии поиска включают поля кластерного индекса, то будет эффективнее, если эти поля будут присутствовать также в исходном индексе, чем если их там не будет. Это если оптимизатор выберет INDEX SEEK. Если ситуация INDEX SCAN, то практически без разницы, есть такие поля в индексе или их там нет.
14 ноя 12, 23:19    [13475424]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
ChA
Member

Откуда: Москва
Сообщений: 11317
Crimean
если его явно включать - оно будет там сортированное. а если не включать - оно там все равно будет, но уже несортированное. а это может быть важно для некоторых запросов
+1
Более того, если в обычный индекс добавляется некий уникальный ID, то индекс фактически тоже станет уникальным, если до этого им не был. Правда если не указать этого явно, то не уверен на 100%, что оптимизатор это сам поймет. Возможно ему придется подсказать, специально указав уникальность некластерного индекса после добавления в него ID. Тестировать лень, чисто умозрительные построения.
15 ноя 12, 00:21    [13475689]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Crimean
ой! индексы, конечно же, на первую таблицу сделал оба - предыдущее сообщение не читать
Упс :)
15 ноя 12, 01:29    [13475832]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Глеб
Необходимо помнить, что ссылка на кластерный индекс есть только в листьях индекса, а в бинарном дереве нет этих ссылок.
А это вообще откуда такое? Простой тест доказывает что это не так.

Да и нет такого термина в сиквеле как "бинарное дерево"
15 ноя 12, 01:35    [13475840]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ChA
Crimean
если его явно включать - оно будет там сортированное. а если не включать - оно там все равно будет, но уже несортированное. а это может быть важно для некоторых запросов
+1
Минус сто. Если поле находится в ключе индекса, то с какого по нему не будет сортировки? Оно же не в INCLUDE
ChA
Более того, если в обычный индекс добавляется некий уникальный ID, то индекс фактически тоже станет уникальным, если до этого им не был. Правда если не указать этого явно, то не уверен на 100%, что оптимизатор это сам поймет. Возможно ему придется подсказать, специально указав уникальность некластерного индекса после добавления в него ID. Тестировать лень, чисто умозрительные построения.
Не совсем понял к чему все эти рассуждения. Internally, индекс всегда уникальный.
15 ноя 12, 01:55    [13475859]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 219
Всем спасибо!
Тесты показали, что, действительно, включение поля кластерного индекса в ключ некластерного влияет на сортировку. Теперь будем думать как с этим дальше жить.
15 ноя 12, 02:01    [13475869]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ROLpogo
Всем спасибо!
Тесты показали, что, действительно, включение поля кластерного индекса в ключ некластерного влияет на сортировку. Теперь будем думать как с этим дальше жить.
А можно ваш тест увидеть?
15 ноя 12, 02:06    [13475871]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ROLpogo
Всем спасибо!
Тесты показали, что, действительно, включение поля кластерного индекса в ключ некластерного влияет на сортировку. Теперь будем думать как с этим дальше жить.
Сортировка будет в обоих случаях, просто если поле включено явно, то оно может быть указано каким угодно по счету в ключе, если же оно неявно добавлено как кластерный индекс, то оно всегда будет последним. Вот и вся разница.
Пихать поле кластерного индекса куда бы то ни было кроме как в конец не имеет особого практического смысла, если только у вас нет в запросе ORDER BY которое в точности копирует поля в вашем индексе. И то это какой-то сомнительны вариант, ибо все поля следующие за этим кластерным использоваться не будут и их смело можно переносить в INCLUDE. В итоге вернемся к тому с чего начинали.
15 ноя 12, 02:11    [13475875]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 219
Mind
Сортировка будет в обоих случаях, просто если поле включено явно, то оно может быть указано каким угодно по счету в ключе, если же оно неявно добавлено как кластерный индекс, то оно всегда будет последним. Вот и вся разница.
Пихать поле кластерного индекса куда бы то ни было кроме как в конец не имеет особого практического смысла, если только у вас нет в запросе ORDER BY которое в точности копирует поля в вашем индексе. И то это какой-то сомнительны вариант, ибо все поля следующие за этим кластерным использоваться не будут и их смело можно переносить в INCLUDE. В итоге вернемся к тому с чего начинали.


Спасибо за помощь в понимании вопроса! Но сейчас я опровергну вашу теорию. Итак, вот тестовый пример:

set statistics io on
declare @i int = 1000

create table RPTest
(
  ID   int identity (1, 1) not NULL,
  Num1 int  NULL,
  Num2 int  NULL
) on [PRIMARY]

while @i > 0
begin
  insert into RPTest(Num1, Num2) values(@i - @i % 10, @i - @i % 5)
  set @i -= 1
end
create unique clustered index i_RPTest_PK_ID on RPTest (ID)

create nonclustered index i_RPTest_Num on RPTest (Num1)
/*
drop index i_RPTest_Num on RPTest
create nonclustered index i_RPTest_Num on RPTest (Num1, Num2)
create nonclustered index i_RPTest_Num on RPTest (Num1, ID, Num2)
*/
select ID, Num1, Num2 from RPTest where Num1 > 500 and Num2 < 540 order by Num1, ID

--drop table RPTest


Допустим, нам нужно получить на выходе данные с конкретного диапазона по Num1 > 500 и Num2 < 540 отсортированные по Num1 и ID. Вот что мы поимеем при 3-х разных индексах:
create nonclustered index i_RPTest_Num on RPTest (Num1): scan по кластерному + сортировка. (logical reads 5).
create nonclustered index i_RPTest_Num on RPTest (Num1, Num2): seek по некластерному + сортировка. (logical reads 4).
create nonclustered index i_RPTest_Num on RPTest (Num1, ID, Num2): seek по некластерному. (logical reads 4).

Выходит, что вставка кластерного ключа в середину некластерного индекса избавила нас от лишней сортировки. Значит иногда это полезно.
Осталось теперь разобраться, зачем адвайзер иногда предлагает вставить кластерный ключ в качестве включаемого (include) поля в некластерный? Тесты показали, что кластерный ключ всегда отсортирован, будь он явно в составе ключа некластерного или неявно или в секции include. Т.е. если бы в данном примере у нас был бы индекс:
create nonclustered index i_RPTest_Num on RPTest (Num1, Num2) include(ID) и мы бы хотели получить такую сортировку:
select ID, Num1, Num2 from RPTest where Num1 > 500 and Num2 < 540 order by Num1, Num2, ID
то план покажет seek по некластерному без сортировки! Т.е. движок уже знает, что кластерный отсортирован.
15 ноя 12, 10:06    [13476370]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
msLex
Member

Откуда:
Сообщений: 9291
Mind
Сортировка будет в обоих случаях

да что ж такое, я ж уже давал ссылку где описано КАК и КОГДА ключ кластерного индекса присутствует в некластерном

тынц
15 ноя 12, 13:54    [13478068]     Ответить | Цитировать Сообщить модератору
 Re: Нужен ли кластерный ключ в некластерном индексе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ROLpogo
Спасибо за помощь в понимании вопроса! Но сейчас я опровергну вашу теорию
Сорри, но не получилось у вас опровержения. Но спасибо за скрипт.

Попробуйте вот этот индекс, в нем нет никакого кластерного а результат тот же.
create nonclustered index i_RPTest_Num on RPTest (Num1) INCLUDE (Num2)

А вот что происходит при вашем индексе:
create nonclustered index i_RPTest_Num on RPTest (Num1, ID, Num2)

Num2 в ключе тут как козе баян :) см. картинку

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

К сообщению приложен файл. Размер - 11Kb
15 ноя 12, 23:47    [13481107]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить