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

Собственно сам вопрос:

к примеру существует таблица TABLE с полями SERIALKEY (int), NONE (varchar(20))
SERIALKEY является кластерным индексом, но не является инкрементным т.е. не IDENTITY(1,1) на практике наверное крайне редко такое бывает, но все же.

Вопрос: Кластерный индекс содержит на листьях сами значения столбца ID, по определенной сортеровки (к примеру по возрастанию). К примеру в таблицу вставили 100 строк (т.е. иемеем значения SERIALKEY в диапозоне 1 - 100), затем удалили диапозон от 60 до 80 (SERIALKEY 60 - 80), и к примеру вновь внесли 10 строк (т.е. вновь в лоб внесли значения SERIALKEY в диапозоне 65 - 75), как в данной ситуации поведет себя кластерный индекс т.е. как он создасться физически. Даже незнаю как съимитировать подобную ситуацию в скрипте что бы проверить ту самую физическую сортировку индекса.

Разъяснил как смог, самоучка ) сильно не ругайтесь за криво изложенный вопрос.
Всем за ранее спасибо за информацию!
10 июл 15, 17:59    [17878415]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20607
Ну если очень грубо то...
Обычный индекс - это сортированная таблица (значение индекса-ссылка на запись). Реально данные таблицы лежат в другом месте, но быстро вынимаются по ссылке.
А кластерном индексе ссылки нет, вместо неё лежит сама запись. И дополнительный переход к ней по ссылке не нужен.
10 июл 15, 18:04    [17878438]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
СТУДЕНТ123
Guest
Akina
Ну если очень грубо то...
Обычный индекс - это сортированная таблица (значение индекса-ссылка на запись). Реально данные таблицы лежат в другом месте, но быстро вынимаются по ссылке.
А кластерном индексе ссылки нет, вместо неё лежит сама запись. И дополнительный переход к ней по ссылке не нужен.

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

Ближе к вопросу, того чего не получается понять к примеру таблица содержит 100 записей (SERIALKEY от 1 до 101):

Удалил запись в середине диапозона таблицы SERIALKEY = 80, теперь добавил новую строку с SERIALKEY = 80, данная строка она займет все тоже место согласно сортировки по кластерному индексу? т.е. ... 78,79,80,81,82,83.... ведь физически данные добавятся куда угодно на странице или все же не на столь тесно взаимосвязанно физическое расположение с кластерным индексом (с обычными индексами - не кластерными все понятно)
10 июл 15, 18:15    [17878481]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
СТУДЕНТ123
Удалил запись в середине диапозона таблицы SERIALKEY = 80, теперь добавил новую строку с SERIALKEY = 80, данная строка она займет все тоже место согласно сортировки по кластерному индексу? т.е. ... 78,79,80,81,82,83....


1) Логически можно считать, что данные сортированы по ключу на листовом уровне.
2) Листовой уровень - двусвязный списов страниц, страницы логически (нельзя считать что физически) расположены в порядке сортировки.
3) Строки в пределах страницы (содержимое строк) НЕ УПОРЯДОЧЕНЫ по ключу. В пределах страницы по ключу упорядочены УПОРЯДОЧЕНЫ СЛОТЫ, ссылающиеся на строки этой страницы.
4) В случае индекса, после удаления запись будет помечена как GHOST record, пока ее мусорщик не соберет.
5) Если удалишь строку с ключем SERIALKEY = 80, закоммитишь транзакцию и мусорщик успеет отработать, то
5а) если строка с ключем SERIALKEY = 80 будет вставлена строка ТАКОГО ЖЕ размера как удаленная, то она попадет на ту же страницу откуда была удалена запись, но в силу (3) этого может и не быть. иногда содержимое страницы уплотняется.
5б) если строка с ключем SERIALKEY = 80 будет вставлена строка БОЛЬШЕГО размера, то может быть как случай (5а), так и возникнуть PageSplit, если места на странице нету.

[/quot]
10 июл 15, 18:44    [17878614]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+

6) если в пункте (4) транзакция не закоммичена и ты делаешь в рамках нее delete 80 и insert 80, то последняя будет добавляться на правах новой строки (рядом с той 80, которая помечена как GHOST).
7) если в пункте (4) транзакция закомичена, то по-момему тоже будет добавляться на правах новой строки (рядом с той 80, которая помечена как GHOST, но пока еще не убрана).
10 июл 15, 18:48    [17878626]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
СТУДЕНТ123
Guest
churupaha
СТУДЕНТ123
Удалил запись в середине диапозона таблицы SERIALKEY = 80, теперь добавил новую строку с SERIALKEY = 80, данная строка она займет все тоже место согласно сортировки по кластерному индексу? т.е. ... 78,79,80,81,82,83....


1) Логически можно считать, что данные сортированы по ключу на листовом уровне.
2) Листовой уровень - двусвязный списов страниц, страницы логически (нельзя считать что физически) расположены в порядке сортировки.
3) Строки в пределах страницы (содержимое строк) НЕ УПОРЯДОЧЕНЫ по ключу. В пределах страницы по ключу упорядочены УПОРЯДОЧЕНЫ СЛОТЫ, ссылающиеся на строки этой страницы.
4) В случае индекса, после удаления запись будет помечена как GHOST record, пока ее мусорщик не соберет.
5) Если удалишь строку с ключем SERIALKEY = 80, закоммитишь транзакцию и мусорщик успеет отработать, то
5а) если строка с ключем SERIALKEY = 80 будет вставлена строка ТАКОГО ЖЕ размера как удаленная, то она попадет на ту же страницу откуда была удалена запись, но в силу (3) этого может и не быть. иногда содержимое страницы уплотняется.
5б) если строка с ключем SERIALKEY = 80 будет вставлена строка БОЛЬШЕГО размера, то может быть как случай (5а), так и возникнуть PageSplit, если места на странице нету.

[/quot] спасибо за разжованный ответ! 5ть раз перечитал - вроде все понял!


Можно еще последний вопрос:

К примеру имеем самый относительно часто встречаемый запрос к громосткой таблице (в рамках примера):

SELECT SERIALKEY,LOG_US, FIO, PHONE, NONE FROM TABLE_NAME WHERE LOGUS IN (Перечисление) AND PHONE(Перечисление)
под этот запрос при создании покрытия в составном индексе также необходимо учитывать порядок согласно условию запроса (т.е. сначало LOGUS а затем PHONE) или этим можно принебречь?
10 июл 15, 18:54    [17878647]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
СТУДЕНТ123
Guest
Замечательно! все что нужно в рамках второго вопроса нашел в справке!

Akina, churupaha большое спасибо за помощь!!!
10 июл 15, 19:24    [17878740]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
o-o
Guest
Последний вопрос сам какой-то неправильный, какая разница, в каком порядке записаны условия в where? Условия переставляйте как хотите, план у всех "переставленных" будет один. Т. е. в вашем примере порядок полей в индексе может повлиять на его выбор/отметание, но не в связи с порядком записи условий в where, а в связи с селективностью каждого поля.
+
"громоздкий" не от слова "мост",
а вот "сортировка"... если вам поможет, то пусть будет от слова" сортир"
10 июл 15, 19:25    [17878743]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
o-o
Guest
Akina
Ну если очень грубо то...
Обычный индекс - это сортированная таблица (значение индекса-ссылка на запись). Реально данные таблицы лежат в другом месте, но быстро вынимаются по ссылке.
А кластерном индексе ссылки нет, вместо неё лежит сама запись. И дополнительный переход к ней по ссылке не нужен.

Я понимаю, что это "грубо", но протестую все равно. Не надо так неприлично упрощать. Любой индекс это прежде всего дерево поиска. И кластерная таблица это тоже не просто сами записи, а плюс еще и дерево над ними.
Вот тогда да, это и называется "индекс". А когда всего лишь"сами записи", это, простите, куча.
Если посмотреть на записи, на их структуру, то куча это записи типа data records. Кластерная таблица это data records(leaf level) + index records(non-leaf levels) . Некластерный индекс это index records (all levels)
10 июл 15, 19:43    [17878777]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
СТУДЕНТ123
Guest
o-o


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

Есть две таблицы:
1. SKU (Номенклатура)
Поля (с учетом физического порядка столбцов в таблице)
1.1 SERIALKEY
1.2.STORERKEY
1.3.SKU
1.4.DESCR
2. SKUxLOC (ячейки - номенклатура)
Поля (с учетом физического порядка столбцов в таблице)
2.1. SERIALKEY
2.2. STORERKEY
2.3. LOC
2.4. SKU

Теперь джойним друг с другом

1 . Кривой запрос т.к. порядок в соединении даст лишние действия (некая пересортица алиасов)
SELECT .....
FROM SKUxLOC AS SL INNER JOIN SKU AS S ON S.STORERKEY = SL.STORERKEY AND S.SKU = SL.SKU

2. Кривой запрос т.к. порядок в соединении не соответствует нумерации столбцам в самих таблицах но с алиасами все хорошо
SELECT .....
FROM SKUxLOC AS SL INNER JOIN SKU AS S ON SL.SKU = S.SKU AND SL.STORERKEY = S.STORERKEY

3. Норм запрос т.к. порядок в соединении соответствует нумерации в самих столбцах таблиц и с алиасы в "ON" тоже правильно расставлены
SELECT .....
FROM SKUxLOC AS SL INNER JOIN SKU AS S ON SL.SKU = S.SKU AND SL.STORERKEY = S.STORERKEY


Вот собственно как играет ли роль некого порядка перечисления в составном индексе или там не важно кокое поле идет первым или последним?

Или я совсем загнался и это сильно на производительность не съиграет, просто удобство чтения (логического понимания пользователем) запроса =)
10 июл 15, 19:44    [17878781]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
СТУДЕНТ123
Guest
Тут промахнулся, правильно так

3. Норм запрос т.к. порядок в соединении соответствует нумерации в самих столбцах таблиц и с алиасы в "ON" тоже правильно расставлены
SELECT .....
FROM SKUxLOC AS SL INNER JOIN SKU AS S ON SL.STORERKEY = S.STORERKEY AND SL.SKU = S.SKU
10 июл 15, 19:46    [17878787]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
СТУДЕНТ123
Вот собственно как играет ли роль некого порядка перечисления в составном индексе или там не важно кокое поле идет первым или последним?

Или я совсем загнался и это сильно на производительность не съиграет, просто удобство чтения (логического понимания пользователем) запроса =)
Не то, что "это сильно на производительность не съиграет", а вообще не имеет значения, в каком порядке перечислены условия.

При этом, во первых, порядок столбцов в таблице вообще не имеет никакого значения, совсем. Серверу всё равно, как они перечислены, какая ему разница? Он читает страницу - и вот у него она, с всеми столбцами, в памяти, всё, он с ними работает.

Во вторых, если вы имели в виду, что в определении таблиц определяется кластерный индекс из перечисленных полей, то ни один из ваших запросов такой индекс использовать не сможет.
10 июл 15, 19:51    [17878794]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
СТУДЕНТ123
Guest
alexeyvg
СТУДЕНТ123
Вот собственно как играет ли роль некого порядка перечисления в составном индексе или там не важно кокое поле идет первым или последним?

Или я совсем загнался и это сильно на производительность не съиграет, просто удобство чтения (логического понимания пользователем) запроса =)
Не то, что "это сильно на производительность не съиграет", а вообще не имеет значения, в каком порядке перечислены условия.

При этом, во первых, порядок столбцов в таблице вообще не имеет никакого значения, совсем. Серверу всё равно, как они перечислены, какая ему разница? Он читает страницу - и вот у него она, с всеми столбцами, в памяти, всё, он с ними работает.

Во вторых, если вы имели в виду, что в определении таблиц определяется кластерный индекс из перечисленных полей, то ни один из ваших запросов такой индекс использовать не сможет.
ОК, все понял, спасибо за разъяснение, извеняюсь что теги для запросов не использовал с телефона пишу, пока в пробке стою.
10 июл 15, 19:56    [17878809]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
o-o
Guest
СТУДЕНТ123
Не раз натыкался на подобную рекомендацию, словами наверное не совем правильно изложу, лучше на примере:

Есть две таблицы:
1. SKU (Номенклатура)
Поля (с учетом физического порядка столбцов в таблице)
1.1 SERIALKEY
1.2.STORERKEY
1.3.SKU
1.4.DESCR
2. SKUxLOC (ячейки - номенклатура)
Поля (с учетом физического порядка столбцов в таблице)
2.1. SERIALKEY
2.2. STORERKEY
2.3. LOC
2.4. SKU


нету никакого такого "физического порядка" в таблице,
он есть, но совсем не как у вас, а собираются вместе fixed-length columns, variable length columns, короче,
качайте себе книгу
Microsoft SQL Server 2012 Internals (Developer Reference)
и разбирайтесь.
"самоучка" это отмазка, вряд ли хоть кому-то из здесь присутствующих
нанимали репетитора.
+ когда у меня в тексте заглавные буквы, я тоже с телефона из пробки пишу.
а кроме книг, есть видео и аудио.
наушники воткнул и 4 часа пути превращаются в 4 часа обучалова.
а форум -- в перерывах, чтоб не опухнуть
10 июл 15, 20:15    [17878860]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
СТУДЕНТ123
Не раз натыкался на подобную рекомендацию, словами наверное не совем правильно изложу, лучше на примере
Я бы отрывал пальцы тем кто пишет такие рекомендации.
10 июл 15, 22:31    [17879221]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
o-o
Guest
СТУДЕНТ123
при создании покрытия в составном индексе также необходимо учитывать порядок согласно условию запроса (т.е. сначало LOGUS а затем PHONE) или этим можно принебречь?

у меня после проветривания возникло предположение, о чем (наверное) вопрос.
не о порядке условий в *where*, а о порядке полей в составе ключа.
у вас одно с другим смешалось.
допустим, у вас таблица person (id, name, surname, ...)
и я ищу по полю name:
select *
from person
where name = N'Василий'

тогда мне никак не подойдет индекс вида (surname, name),
хотя в *ключе* этого индекса и есть name, но вторым полем.
потому что представьте себе, у вас есть оглавление в телефонной книге
фамилии в алфавитном порядке, и имена тоже, только имена-то упорядочены вторым полем:
Иванов Василий
Иванов Иван
Иванов Петр
...
Петров Василий
Петров Иван

если я ищу всех Вась,
то чем мне поможет упорядочение по именам, когда оно вторым столбцом?
такое оглавление мне с поиском по фамилии поможет и только.

вот тут Microsoft SQL Server 2012 Internals
можно не скачивая исчерпывающе прочесть,
как раз эти страницы не вырезаны из бесплатного просмотра.
там и рисунки, и структура записи.

а еще судя вот по этому
СТУДЕНТ123
к примеру существует таблица TABLE с полями SERIALKEY (int), NONE (varchar(20))
SERIALKEY является кластерным индексом, но не является инкрементным т.е. не IDENTITY(1,1)

вы ключ индекса и сам индекс не различаете.
отсюда и фантазии на тему "физического порядка столбцов в таблице".
поэтому начните с определения ключа, а потом уже про "внутренности" читайте
10 июл 15, 23:10    [17879323]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите про кластерный индекс  [new]
СТУДЕНТ123
Guest
o-o
СТУДЕНТ123
при создании покрытия в составном индексе также необходимо учитывать порядок согласно условию запроса (т.е. сначало LOGUS а затем PHONE) или этим можно принебречь?

у меня после проветривания возникло предположение, о чем (наверное) вопрос.
не о порядке условий в *where*, а о порядке полей в составе ключа.
у вас одно с другим смешалось.
допустим, у вас таблица person (id, name, surname, ...)
и я ищу по полю name:
select *
from person
where name = N'Василий'

тогда мне никак не подойдет индекс вида (surname, name),
хотя в *ключе* этого индекса и есть name, но вторым полем.
потому что представьте себе, у вас есть оглавление в телефонной книге
фамилии в алфавитном порядке, и имена тоже, только имена-то упорядочены вторым полем:
Иванов Василий
Иванов Иван
Иванов Петр
...
Петров Василий
Петров Иван

если я ищу всех Вась,
то чем мне поможет упорядочение по именам, когда оно вторым столбцом?
такое оглавление мне с поиском по фамилии поможет и только.

вот тут Microsoft SQL Server 2012 Internals
можно не скачивая исчерпывающе прочесть,
как раз эти страницы не вырезаны из бесплатного просмотра.
там и рисунки, и структура записи.

а еще судя вот по этому
СТУДЕНТ123
к примеру существует таблица TABLE с полями SERIALKEY (int), NONE (varchar(20))
SERIALKEY является кластерным индексом, но не является инкрементным т.е. не IDENTITY(1,1)

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


Вот тут то все и уяснилось в голове. Спасибо огромное О-О, действительно все перемешалось!
13 июл 15, 12:07    [17884881]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить