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

Откуда: Москва
Сообщений: 2646
Таблица
id int
a1 int
a2 int
и т.д. остальные поля

Запрос
SELECT ID FROM Таблица WHERE Data1 ='20141010' AND num =10 


Вопрос: нужно ли включать поле ID в некластерный индекс? (построен кластерный индекс по ID)
Если на таблице есть кластерный индекс, в некластерный индекс всегда входит кластерный индекс. А как это на примере понять?

Создание некластерного индекса:
 -- 1
CREATE NONCLUSTERED INDEX [inx_test] ON [dbo].[Table1] 
([a1] ,[a2] )


 -- 2
CREATE NONCLUSTERED INDEX [inx_test] ON [dbo].[Table1] 
([a1] ,[a2], ID )


Какой из двух вариантов построения некластерного индекса правильный, для запроса?
31 окт 14, 20:33    [16786103]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
NickAlex66
Member

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

1й, при условии правильного учета селективности.
31 окт 14, 20:40    [16786134]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
trew
Member

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

А можно привести аргументы, почему не 2й вариант?
можно ссылку, где об этом сказано.
31 окт 14, 20:48    [16786155]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NickAlex66,

Какой еще селективности?


trew,

Индексы будут идентичными по своей внутренней структуре. Разница появится только если кто-то грохнет или поменяет кластерный индекс.

trew
А как это на примере понять?

DBCC PAGE
31 окт 14, 21:57    [16786358]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
NickAlex66
Member

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

Mind,

1. По поводу правильно-неправильно: 1-й вариант потому, что ID и так будет включен в некластерный индекс (на нем построен кластерный) и указывать его явно, да еще и в полях на которых строится индекс будет не правильно. И идет от непонимания ТС сути производимых действий.
2. По поводу селективности (немного в сторону от вопроса ТС): прикалываетесь или считаете, что она никак не учитывается при проектировании индекса?
31 окт 14, 22:50    [16786501]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NickAlex66
Mind,

1. По поводу правильно-неправильно: 1-й вариант потому, что ID и так будет включен в некластерный индекс (на нем построен кластерный) и указывать его явно, да еще и в полях на которых строится индекс будет не правильно. И идет от непонимания ТС сути производимых действий.
ID так или иначе будет включен в поля на которых строится индекс. Так что разницы по сути нет.
NickAlex66
2. По поводу селективности (немного в сторону от вопроса ТС): прикалываетесь или считаете, что она никак не учитывается при проектировании индекса?
Если вообще при проектировании индекса, то учитывается. А если конкретно, выбирать между вариантом 1 и 2, то нет.
31 окт 14, 23:24    [16786578]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
trew, некластерный не входит в кластерный, он ссылается на него. Т.е. кластерный просто заменяет кучу.
Поиск по кластерному будет использован в плане, если некластерный не является покрывающим. Тогда запрос полезет в кластерный и выберет требуемые поля уточняющим чтением.

В Вашем примере Data1 = a1 или как?
1 ноя 14, 14:51    [16787807]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Владислав Колосов
В Вашем примере Data1 = a1 или как?


Обсужение запроса который ниже, а не какого-то другого (в первом сообщении забыл заменить).
SELECT ID FROM Таблица WHERE a1=1 AND a2=10 

В таблице есть и другие поля: a3, a4, a5.
Теория: у некластерного индекса, на листовом уровне содержат ссылки на кластерный индекс.
Т.е. поля ID в некластерном индексе нет, только ссылка на него.

1) Нужно ли включать поле кластерного индекса (ID) в некластерный индекс, для запроса который вы видите? Почему Да или почему Нет?

2) Если кластерный индекс составной (ID, b1), и одно из этих полей (b1) в запросе в выборке SELECT.
При создании некластерного индекса, включать в него b1 или нет?
т.е. включать поля кластерного индекса в некластерный (если они нужны в запросе в select) или это бред?

Mind предоложил DBCC PAGE, но я не знаю как расшифровывать результат этой команды.
1 ноя 14, 20:51    [16788556]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
trew
Теория: у некластерного индекса, на листовом уровне содержат ссылки на кластерный индекс.
Т.е. поля ID в некластерном индексе нет, только ссылка на него.
Это кто вас научил такой теории?
1 ноя 14, 20:58    [16788574]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Гавриленко Сергей Алексеевич
trew
Теория: у некластерного индекса, на листовом уровне содержат ссылки на кластерный индекс.
Т.е. поля ID в некластерном индексе нет, только ссылка на него.
Это кто вас научил такой теории?

индексы
Некластерный индекс имеет leaf level, который содержит все ключевые значения, отсортированные в том виде как был определен индекс, вместе с row ID или кластерным ключом. Сами данные не хранятся в индексе и вынимаются из таблицы, используя row ID или ключ кластерного индекса.
1 ноя 14, 23:31    [16788981]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
trew
Гавриленко Сергей Алексеевич
пропущено...
Это кто вас научил такой теории?

индексы
Некластерный индекс имеет leaf level, который содержит все ключевые значения, отсортированные в том виде как был определен индекс, вместе с row ID или кластерным ключом. Сами данные не хранятся в индексе и вынимаются из таблицы, используя row ID или ключ кластерного индекса.
В этой теории все правильно написано, а вашей - нет. Выделил.
1 ноя 14, 23:39    [16789007]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
invm
Member

Откуда: Москва
Сообщений: 9407
trew
Если на таблице есть кластерный индекс, в некластерный индекс всегда входит кластерный индекс
trew
Теория: у некластерного индекса, на листовом уровне содержат ссылки на кластерный индекс.
Некластерный индекс содержит ключи кластерного индекса как минимум на листовом уровне. И на этом уровне они выполняют роль row locator.

Если у вас пока что проблемы с теорией, то можете проинспектировать планы запроса для индексов с ID и без, на предмет наличия Key Lookup.
2 ноя 14, 00:29    [16789090]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
o-o
Guest
trew
Mind предоложил DBCC PAGE, но я не знаю как расшифровывать результат этой команды.

можно и расшифровать, но это не спасет от необходимости почитать заново про организацию индексов.
ну, разве что нагляднее станет.
скорее, вы просто не то смотрите, там же вывод с "говорящими" столбцами, в любом случае вот:
How to use DBCC PAGE

для повышения этажности некластерных тип a2 заменен на "побольше"

create table dbo.t (id int primary key, a1 int, a2 char(800), c char(3000));
go

insert into dbo.t (id, a1, a2, c)
select n, n * 10, cast(n * 10 as char(800)), 'c'
from dbo.Nums
where n <= 2000;
go

create nonclustered index inx_test on dbo.t(a1 ,a2);
create nonclustered index inx_test_id on dbo.t(a1 ,a2, id);

SELECT index_id, index_level, record_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('db0'), OBJECT_ID('t'), null, null, 'Detailed')
---
index_id	index_level	record_count	page_count
1	0	2000	1000
1	1	1000	3
1	2	3	1
2	0	2000	223
2	1	223	25 
2	2	25	3  
2	3	3	1  
3	0	2000	223
3	1	223	25
3	2	25	3
3	3	3	1

уже тут видно, что индексы подозрительно одинаковое число страниц заняли на каждом из уровней,
но ок, добьем по страницам.

получаем все страницы через DBCC IND
в районе 1000-ой строки результата ищите, где пошли страницы первого некластерного (у меня в 1007-ой), страница 2848 с нулевого т.е. листового уровня,
ее смотрим через DBCC PAGE.
в 1260 строке у меня пошли страницы второго некластерного, 3248 -- тоже первая страница листового уровня.
видно, что все то же самое + явно подписано, что id вообще стал частью ключа (потому что некластерные не объявили уникальными).

DBCC IND (db0, t, -1);

DBCC TRACEON(3604);

DBCC PAGE(db0, 1, 2848, 3); -- 1007
---
FileId	PageId	Row	Level	a1 (key)	a2 (key)	id (key)	KeyHashValue
1	2848	0	0	10	10   1	(2e18c90581ed)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
1	2848	1	0	20	20   2	(6a5d5fee037a)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	2848	2	0	30	30   3	(a9612db77df7)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	2848	3	0	40	40   4	(e3d773390655)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	2848	4	0	50	50   5	(fd7c18eee121)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	2848	5	0	60	60   6	(64ae978bfa4f)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	2848	6	0	70	70   7	(8a571f2376d4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	2848	7	0	80	80   8	(f1c22b970d0b)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	2848	8	0	90	90   9	(32fe59ce7386)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	

DBCC PAGE(db0, 1, 3248, 3); -- 1260
---
FileId	PageId	Row	Level	a1 (key)	a2 (key)	id (key)	KeyHashValue
1	3248	0	0	10	10   1	(2e18c90581ed)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            	
1	3248	1	0	20	20   2	(6a5d5fee037a)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	3248	2	0	30	30   3	(a9612db77df7)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	3248	3	0	40	40   4	(e3d773390655)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          	
1	3248	4	0	50	50   5	(fd7c18eee121)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	3248	5	0	60	60   6	(64ae978bfa4f)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	3248	6	0	70	70   7	(8a571f2376d4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	3248	7	0	80	80   8	(f1c22b970d0b)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	
1	3248	8	0	90	90   9	(32fe59ce7386)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	


по остальным уровням сами смотрите.
еще попробуйте создать уникальные некластерные и "почувствуйте разницу"
2 ноя 14, 03:14    [16789300]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
trew
Теория: у некластерного индекса, на листовом уровне содержат ссылки на кластерный индекс.
Т.е. поля ID в некластерном индексе нет, только ссылка на него.
Ну про это уже написали, что это ваша неправильная интерпретация.


Что-то вы полезли в какие то дебри. По хорошему вас вообще не должно волновать что там в кластерном индексе. СОВСЕМ.
Если вам нужно делать поиск по полю, то включайте его в список индексируемых. Если вам нужно это поле только для вывода (в SELECT), то добавляйте его в INCLUDE. Все! Забудьте о наличии кластерного индекса. Какая разница есть он или нет, на каких полях построен и что там внутренне сделает сервер?

1. На скорость запросов это не повлияет.
2. На резмеры индекса это не полияет.
3. При изменении кластерного индекса вам не придется пересматривать все некласерные индексы.
4. Проще читать и понимать определение индекса и зачем он был создан, когда все поля описаны явно, а не зависят от кластерного индекса.


Соответственно ответы на ваши вопросы.

trew
1) Нужно ли включать поле кластерного индекса (ID) в некластерный индекс, для запроса который вы видите? Почему Да или почему Нет?
Да. Поле нужно включить в INCLUDE индекса. Почему? Потому что оно нужно для запроса.

trew
2) Если кластерный индекс составной (ID, b1), и одно из этих полей (b1) в запросе в выборке SELECT.
При создании некластерного индекса, включать в него b1 или нет?
Да. Поле нужно включить в INCLUDE индекса. Почему? Потому что оно нужно для запроса. А если завтра кто-то уберет b1 из кластерного индекса?
3 ноя 14, 22:57    [16794368]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8734
Mind,
+1
4 ноя 14, 03:31    [16795073]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Спасибо всем за ответы, уже понятней становится.

А по поводу INCLUDE, я правильно понимаю?

В индексе, сумма размеров корневого и промежуточных уровней индекса - занимают очень малый размер,
по сравнению с уровнем листьев.
Т.е. размер уровня листьев - это где-то 90% от общего размера индекса.
SELECT index_id, index_level, record_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t'), null, null, 'Detailed')

И используя INCLUDE мы чуть-чуть(на 10%) уменьшаем размер индекса,
по сравнению с вариантом, когда все поля включены в индекс (без include).
4 ноя 14, 20:10    [16797364]     Ответить | Цитировать Сообщить модератору
 Re: Создание некластерного индекса  [new]
o-o
Guest
trew,
не поверите, но по поводу INCLUDED COLUMNS все отлично расписано в хэлпе Index with Included Columns
4 ноя 14, 22:05    [16797778]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить