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

Откуда:
Сообщений: 38
Здравствуйте уважаемые форумчане! Подтолкните пожалуйста к правильному понимаю внутреннего механизма хранения неуникальных некластеризованных индексов. В чем причина хранения на промежуточных уровнях идентификатора записи? В источниках которые попадались мне основная причина указана в требовании уникальности записи, но зачем эта уникальность нужна (при наличии адреса страницы) я никак понять не могу.

Модератор: Тема перенесена из форума "PHP, Perl, Python".


Сообщение было отредактировано: 21 янв 15, 10:08
21 янв 15, 10:04    [17146651]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
Glory
Member

Откуда:
Сообщений: 104751
https://www.sql.ru/articles/mssql/03013101indexes.shtml
21 янв 15, 10:45    [17146990]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
rere1234
Member

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

Спасибо за ссылку но я так и не нашел в статье ответ на интересующий меня вопрос
21 янв 15, 11:31    [17147380]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
Glory
Member

Откуда:
Сообщений: 104751
rere1234
но я так и не нашел в статье ответ на интересующий меня вопрос

А вы приведете доказательства своих предположений про индексы ? Или это аксиомы ?
21 янв 15, 11:32    [17147394]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
rere1234
Glory,

Спасибо за ссылку но я так и не нашел в статье ответ на интересующий меня вопрос


Где Вы увидели в статье упоминание Вашего утверждения?
21 янв 15, 12:55    [17148055]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Ссылка на кластерный ключ хранится для того, чтобы можно было через него достать недостающие в индексе поля. А вот где вы увидели в некластерном индексе "физический адрес" при наличии кластерного индекса - не понятно.
21 янв 15, 13:44    [17148424]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
o-o
Guest
по-моему, он про некластерные, построенные на куче.
только что имеется в виду под "идентификатор записи"?
ТС, вы этим ключ индекса обзываете или RID?
ключ нормально, что на всех уровнях лежит, а RID только на листовом
21 янв 15, 13:51    [17148485]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
В MS SQL любой индекс физически уникален. Поэтому неуникальные индексы сервер уникализирует путем добавления атрибутов в ключ. Ключи индекса присутствуют на всех страницах, не только на листовых - на то они и ключи.
В случае некластерного неуникального на куче - уникализируется добавлением в ключ RID строки данных.
В случае некластерного неуникального на кластерном - уникализируется добавлением в ключ ключа кластерного.
21 янв 15, 14:18    [17148696]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
o-o
Guest
извиняюсь и забираю свои слова обратно.
действительно, в неуникальном некластерном RID присутствует на всех уровнях.
invm, спасибо
21 янв 15, 14:48    [17148999]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
rere1234
Member

Откуда:
Сообщений: 38
Извиняюсь, надо было сразу написать текст запроса чтобы не было лишних вопросов

use tempdb

dbcc traceon(3604)
go

-- подготавливаю набор данных (таблица куча)
if (object_id('heap') is not null)
	drop table heap

create table heap(id int identity, name char(900))

insert into heap(name)
select top(10000) newid() from sys.all_columns s1, sys.all_columns s2

-- создаю уникальный некластеризованный индекс на куче
create unique nonclustered index udx_heap_name on heap(name)

-- узнаю его идентификатор
declare @indid_heap_unique int = (select indid from sysindexes where name = 'udx_heap_name'), 
		@pageid_heap_unique int

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_heap_unique = allocated_page_page_id from 
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.heap'), @indid_heap_unique, null, 
'DETAILED')
where page_level not in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса и идентификатор страницы ChildPageId
DBCC PAGE ('tempdb', 1, @pageid_heap_unique, 3)

-- создаю неуникальный некластеризованный индекс на куче
create nonclustered index idx_heap_name on heap(name)

-- узнаю его идентификатор
declare @indid_heap_nonunique int = (select indid from sysindexes where name = 'idx_heap_name'), @pageid_heap_nonunique int

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_heap_nonunique = allocated_page_page_id from 
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.heap'), @indid_heap_nonunique, null, 
'DETAILED')
where page_level not in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса, идентификатор страницы ChildPageId и добавился HEAP RID
DBCC PAGE ('tempdb', 1, @pageid_heap_nonunique, 3)


-- подготавливаю набор данных (кластеризованный индекс)
if (object_id('clusteredtable') is not null)
	drop table clusteredtable

create table clusteredtable(id int identity, name char(900))

insert into clusteredtable(name)
select top(10000) newid() from sys.all_columns s1, sys.all_columns s2

-- создаю уникальный некластеризованный индекс на кластеризованном индексе
create unique nonclustered index udx_clusteredtable_name on clusteredtable(name)

-- узнаю его идентификатор
declare @indid_clusteredtable_unique int = (select indid from sysindexes where name = 'udx_clusteredtable_name'), 
		@pageid_clusteredtable_unique int

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_clusteredtable_unique = allocated_page_page_id from 
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.clusteredtable'), @indid_clusteredtable_unique, null, 
'DETAILED')
where page_level not in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса и идентификатор страницы ChildPageId
DBCC PAGE ('tempdb', 1, @pageid_clusteredtable_unique, 3)

-- создаю неуникальный некластеризованный индекс на кластеризованном индексе
create nonclustered index idx_clusteredtable_name on clusteredtable(name)

-- узнаю его идентификатор
declare @indid_clusteredtable_nonunique int = (select indid from sysindexes where name = 'idx_clusteredtable_name'), 
		@pageid_clusteredtable_nonunique int

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_clusteredtable_nonunique = allocated_page_page_id from 
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.clusteredtable'), @indid_clusteredtable_nonunique, null, 
'DETAILED')
where page_level not in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса, идентификатор страницы ChildPageId и добавился HEAP RID
DBCC PAGE ('tempdb', 1, @pageid_clusteredtable_nonunique, 3)


Если я правильно понел invm, это требование самого Sql Server. Просто ведь для поиска значения с помощью индекса он фактически не нужен (на промежуточном уровне) так как есть идентификатор страницы и значение ключа. Спасибо всем за ответы.
21 янв 15, 17:51    [17150459]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
o-o
Guest
rere1234
основная причина указана в требовании уникальности записи, но зачем эта уникальность нужна (при наличии адреса страницы) я никак понять не могу.

по-моему, уникальность записей индексных страниц -- это требование для построения B-tree.

In B-trees, internal (non-leaf) nodes can have a variable number of child nodes within some pre-defined range.
...
Each internal node of a B-tree will contain a number of keys. The keys act as separation values which divide its subtrees.


и здесь ключ -- это именно (уникальный) ключ, а не то неуникальное, что задается в скобочках в CREATE NONCLUSTERED INDEX
21 янв 15, 18:15    [17150595]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
rere1234
Member

Откуда:
Сообщений: 38
Не создал для кластеризованный индекс для второго варианта)
+
use tempdb

dbcc traceon(3604)
go

-- подготавливаю набор данных (таблица куча)
if (object_id('heap') is not null)
drop table heap

create table heap(id int identity, name char(900))

insert into heap(name)
select top(10000) newid() from sys.all_columns s1, sys.all_columns s2

-- создаю уникальный некластеризованный индекс на куче
create unique nonclustered index udx_heap_name on heap(name)

-- узнаю его идентификатор
declare @indid_heap_unique int = (select indid from sysindexes where name = 'udx_heap_name'),
@pageid_heap_unique int

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_heap_unique = allocated_page_page_id from
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.heap'), @indid_heap_unique, null,
'DETAILED')
where page_level not in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса и идентификатор страницы ChildPageId
DBCC PAGE ('tempdb', 1, @pageid_heap_unique, 3)

-- создаю неуникальный некластеризованный индекс на куче
create nonclustered index idx_heap_name on heap(name)

-- узнаю его идентификатор
declare @indid_heap_nonunique int = (select indid from sysindexes where name = 'idx_heap_name'), @pageid_heap_nonunique int

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_heap_nonunique = allocated_page_page_id from
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.heap'), @indid_heap_nonunique, null,
'DETAILED')
where page_level not in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса, идентификатор страницы ChildPageId и добавился HEAP RID
DBCC PAGE ('tempdb', 1, @pageid_heap_nonunique, 3)


-- подготавливаю набор данных (кластеризованный индекс)
if (object_id('clusteredtable') is not null)
drop table clusteredtable

create table clusteredtable(id int identity, name char(900))

-- создаем кластерный индекс на таблице
create clustered index cix_clusteredtable_id on clusteredtable(id)


insert into clusteredtable(name)
select top(10000) newid() from sys.all_columns s1, sys.all_columns s2

-- создаю уникальный некластеризованный индекс на кластеризованном индексе
create unique nonclustered index udx_clusteredtable_name on clusteredtable(name)

-- узнаю его идентификатор
declare @indid_clusteredtable_unique int = (select indid from sysindexes where name = 'udx_clusteredtable_name'),
@pageid_clusteredtable_unique int

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_clusteredtable_unique = allocated_page_page_id from
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.clusteredtable'), @indid_clusteredtable_unique, null,
'DETAILED')
where page_level not in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса и идентификатор страницы ChildPageId
DBCC PAGE ('tempdb', 1, @pageid_clusteredtable_unique, 3)

-- создаю неуникальный некластеризованный индекс на кластеризованном индексе
create nonclustered index idx_clusteredtable_name on clusteredtable(name)

-- узнаю его идентификатор
declare @indid_clusteredtable_nonunique int = (select indid from sysindexes where name = 'idx_clusteredtable_name'),
@pageid_clusteredtable_nonunique int

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_clusteredtable_nonunique = allocated_page_page_id from
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.clusteredtable'), @indid_clusteredtable_nonunique, null,
'DETAILED')
where page_level not in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса, идентификатор страницы ChildPageId и добавился HEAP RID
DBCC PAGE ('tempdb', 1, @pageid_clusteredtable_nonunique, 3)

-- выбираю первую попавшую страницу этого индекса не листьевого уровня
select top(1) @pageid_clusteredtable_nonunique = allocated_page_page_id from
sys.dm_db_database_page_allocations(db_id(), object_id('dbo.clusteredtable'), @indid_clusteredtable_nonunique, null,
'DETAILED')
where page_level in (0) and page_type_desc = 'INDEX_PAGE'

-- печаю ее дамп, исходя из него видно что на промежуточном уровне
-- хранится ключ индекса, идентификатор страницы ChildPageId и добавился HEAP RID
DBCC PAGE ('tempdb', 1, 1307, 3)

DBCC PAGE ('tempdb', 1, 1298, 3)

DBCC PAGE ('tempdb', 1, 1296, 3)

DBCC PAGE ('tempdb', 1, 1232, 3)
21 янв 15, 18:57    [17150784]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
rere1234
Member

Откуда:
Сообщений: 38
o-o,

Ну ведь для уникальности на промежуточном уровне вполне хватает связки идентификатор страницы и ключа индекса(указанного в скобочках). А на листовом уровне можно использовать RID. Спасибо за инфу по B-Tree, порою в этом направлении)
21 янв 15, 19:32    [17150914]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
o-o
Guest
rere1234,
нет, не пойдет, ключ же не должен меняться от уровня к уровню. я думаю, они выбрали именно RID, т. к. он заведомо нужен на листовом уровне, и это самый объемный уровень. выбери они другой "унификатор", его бы пришлось тащить и на листовой уровень тоже, наверное решили, лучше большой RID на остальные уровни, чем скромного размера, но зато во все листья
21 янв 15, 20:26    [17151218]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
rere1234
Member

Откуда:
Сообщений: 38
o-o,

Так на листовом уровне унификатор вроде не нужен особо. Значение ключа есть, страница известна. Начинаешь читать записи из страницы по порядку заданому в slot array. И начиная с первого совпадения с ключом сканируешь индекс дальше. Или я чего то не учел.
21 янв 15, 21:38    [17151464]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
rere1234
В чем причина хранения на промежуточных уровнях идентификатора записи? В источниках которые попадались мне основная причина указана в требовании уникальности записи, но зачем эта уникальность нужна (при наличии адреса страницы) я никак понять не могу.
А как вы будете искать в индексе одну(!) конкретную запись если у вас промежуточный уровень неуникальный? И при чем тут адрес страницы? Он вам никак не поможет.

Для того чтобы индексы работали оптимально, всегда должна быть "быстрая" связь между непосредствнно данными (будь то кластер или куча) и некластерными индексами. И обязательно в обе стороны, а не только в одну, как может показаться на первый взгляд.

Скажем нашли вы одну строку в куче, у вас есть её физический адрес или RID записи. И как вы теперь, зная значение некоего ключа некластерного индекса (который нифига не уникален) и RID найдете соответствующую "запись" в некластерном индексе если у вас на промежуточном уровне нет RID? В худшем случае придется сканировать всю таблицу. В лучшем, часть таблицы. Но ни о каком поиске речи уже быть не может. А теперь представьте 10,000 таких поисков? Всё - производительность никакая.
22 янв 15, 01:03    [17151806]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
Mind
Member

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

Так на листовом уровне унификатор вроде не нужен особо. Значение ключа есть, страница известна. Начинаешь читать записи из страницы по порядку заданому в slot array. И начиная с первого совпадения с ключом сканируешь индекс дальше. Или я чего то не учел.
Вам не нужен. Серверу нужен. Актуальность индекса как-то поддерживать нужно, а не только искать по нему.
22 янв 15, 01:06    [17151813]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
rere1234
Member

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

Да вы правы, при изменении значения поля таблицы, на котором существует некластеризованный индекс, нам понадобится найти соответствующую запись в самом некластеризованном индексе для ее удаления. Спасибо за ответ.
22 янв 15, 07:02    [17151946]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
Уникальность требуется для балансировки дерева, т.к. необходимо знать - где точно резать ветку. Кстати, статью было бы неплохо отретушировать, реализация требования уникальности в сиквеле в ней не отражена полностью.
22 янв 15, 11:25    [17152907]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность некластеризованного индекса  [new]
o-o
Guest
Владислав Колосов
Уникальность требуется для балансировки дерева

дерево деревом, любой "уникализатор" бы прокатил,
именно RID нужен на всех уровнях для "обратной связи", для поддержки самого индекса,
разве Mind непонятно объяснил?
мне тоже сперва показалось, что любого уникализаторa хватит, но тогда его придется не только в индекс добавить,
но и в саму кучу, жирно-то не будет?

с неуникальным кластерным ведь так и делают: в него самого добавляют uniquifier
(ок, как минимум, для целей навигации в самом кластерном -- выполняем требование дерева),
и потом этот самый добавленный уникализатор перекочевывает в некластерные индексы:
в уникальные некластерные только на листовой уровень, для прямой связи (дерево довольствуется уникальностью ключа некластерного)
в неуникальные некластерные на все уровни: так и дерево получит свое требование уникальности записей,
и "обратная связь" будет налажена.

еще до 2005-ого при ребилде неуникального кластерного перегенерировались еще и эти уникализаторы,
что вело к необходимости перестраивать еще и все некластерные, потом пофиксили.

вот все подробно описано What happens to non-clustered indexes when the table structure is changed?
22 янв 15, 14:43    [17154508]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить