Header RSS-подписка на обновления сайта eMail-подписка на обновления сайта

Механика индексных представлений. Часть 1/3.









После публикации статьи Теоретические основы фильтрованных индексов в SQL Server 2008. Достоинства и недостатки пришло довольно много писем по вопросам конкуренции и взаимозаменяемости описанных в статье фильтрованных индексов и лишь кратко в ней упомянутых индексированных представлений. Статья последующая, К вопросу уникальности индексов, лишь «подогрела» этот интерес к тонким нюансам работы индексов на представлении. Проанализировав отдельные вопросы-ответы на тему таких индексов автор пришел к заключению, что, во-первых, действительно тонкие моменты таких индексов известны крайне незначительной части аудитории, во-вторых, еще меньшее ее число осознает, что главная «засада» указанных индексов даже не в «физике» их работы, а в несовпадении той самой «физики» для разных редакций серверов одной и той же версии, ну и в-третьих, что набранного в ходе таких вопросов-ответов материала вполне достаточно для самостоятельной статьи. Именно она и предлагается вашему вниманию.

Прежде чем переходить непосредственно к тексту и сути вопросов — пара предварительных рекомендаций для читателей. Во-первых, данный материал в большой степени основывается на материале упомянутой статьи К вопросу уникальности индексов, и, в определенном смысле, является ее продолжением. Так, к примеру, все инструменты по исследованию внутреннего состояния индексов взяты из указанной статьи, где эти инструменты описаны достаточно подробно. Кроме того, хотя внутренняя структура индекса сама по себе не является «точкой фокуса» текущего материала, понимание такой структуры будет несомненным плюсом при его чтении. Описание такой структуры снова можно найти по приведенной выше ссылке. Одним словом — предварительное знакомство с публикацией К вопросу уникальности индексов (в текущем тексте автор будет ссылаться на нее как на «базовую статью») крайне рекомендуется, особенно если вы планируете воспроизвести все практические примеры текущего материала на своей тестовой машине.

Во-вторых, статья нынешняя будет разделена на две части. В первой (основной) будут разобраны те самые «тонкие моменты» индексов на представлении но только для редакции Enterprise (в контексте данной заметки она подразумевает так же и редакции Datacenter/Developer) любых современных SQL серверов, то бишь версий 2005-2012. Во второй части (дополнительной) мы посмотрим на центральный вопрос материала — «а как оно все в Standard и ниже?». Не теряя больше времени — к части первой.





Индексы на представлениях в редакции Enterprise.

Структура и внутренне содержимое индексированных представлений.

Как хорошо известно даже начинающим «SQL-щикам», по причинам вполне очевидным на таблице T1 может быть максимум один кластерный индекс по имени, допустим, iT1. Большее число индексов указанного типа исключено по определению. Теперь, допустим, мы создаем для T1 представление V1 не делающее ничего иного как возвращающее все строки данной таблицы, то есть код представления SELECT * FROM T1. Можем ли мы на V1 создать кластерный индекс iV1? Скажем так: если мы в принципе решаем, что V1 заслуживает создания хоть какого-нибудь индекса, то первый индекс iV1 не только может, а обязан быть кластерным. Вот последующие индексы на V1 будут некластеризованными, а первый — только кластерный. И что мы имеем? V1, по сути, есть «копия» T1 и если iT1 и iV1 оба создаются по одной и той же колонке (набору колонок) то здравый смысл подсказывает, что в результате у нас будет два идентичных и кластерных индекса. Созданных на одном и том же наборе данных (читай — на одной таблице). Разве такое допустимо?

Этот нехитрый вопрос ставил в тупик (и, по всей видимости, продолжит так поступать) даже весьма «продвинутых» DBA. Нечеткое понимание механизмов работы и взаимоотношений различных индексов приводит к возникновению немалого числа «мифов», один из которых звучит так: поскольку кластерный индекс может быть только и ровно один, индекс создаваемый на представлении является «псевдо-кластерным», то есть содержит на своем листовом уровне не все колонки таблицы (как это положено уважающему себя «реально-кластерному» индексу), а лишь ключевые колонки и плюс значение ключа «действительного» кластерного индекса базовой таблицы. То есть имеется такое мнение, что даже если оптимизатор будет использовать индекс представления (iV1 в нашем примере), то на его листовом уровне он обнаружит лишь ключ индекса iT1, после чего по этому ключу будет выполнен проход по индексу iT1, и вот уже с листового уровня этого последнего будут взяты значения обычных (не ключевых) колонок. Ну то есть поиск по кластерному индексу представления ничем не отличается от поиска по обычному некластерному индексу таблицы и имеет точно такой же ярко выраженный «двухфазный» принцип действия — о как! Имеет ли такое мнение какую либо связь с реальным положением вещей? А давайте проверим на тестовой базе! После усвоения «базовой статьи» для нас это проще паренной репы...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
USE master
GO
CREATE DATABASE [~DB~]
ON PRIMARY (
    NAME = '~DB~_Data',
    FILENAME = 'c:\sqlCMD.ru\~DB~.mdf',
    SIZE = 20 MB )
LOG ON (
    NAME = '~DB~_Log',
    FILENAME = 'c:\sqlCMD.ru\~DB~.ldf',
    SIZE = 20 MB )
GO
USE [~DB~]
GO
CREATE TABLE dbo.T1 (col1 int, col2 int)
GO
CREATE TABLE Tsup (PageFID  tinyint, PagePID int, IAMFID   tinyint, IAMPID  int, ObjectID  int, IndexID  tinyint, PartitionNumber tinyint,
 PartitionID bigint, iam_chain_type  varchar(30), PageType  tinyint, IndexLevel  tinyint, NextPageFID  tinyint, NextPagePID  int, PrevPageFID  tinyint, PrevPagePID int)
CREATE TABLE Vsup (PageFID  tinyint, PagePID int, IAMFID   tinyint, IAMPID  int, ObjectID  int, IndexID  tinyint, PartitionNumber tinyint,
 PartitionID bigint, iam_chain_type  varchar(30), PageType  tinyint, IndexLevel  tinyint, NextPageFID  tinyint, NextPagePID  int, PrevPageFID  tinyint, PrevPagePID int)
declare @i int
SET @i = 0
BEGIN TRANSACTION
    WHILE @i < 25000 --loop counter
    BEGIN
        insert into dbo.T1 VALUES (@i, 100000+@i)
        set @i += 1
     END
COMMIT TRANSACTION
CREATE UNIQUE CLUSTERED INDEX [IX_tab] ON T1 (col1)
GO
CREATE VIEW dbo.V1 WITH SCHEMABINDING
AS
SELECT col1, col2 FROM dbo.T1
GO
CREATE UNIQUE CLUSTERED INDEX [IX_viw] ON V1 (col1)
GO

INSERT INTO Tsup EXEC ('DBCC IND ([~DB~], T1, -1)');
INSERT INTO Vsup EXEC ('DBCC IND ([~DB~], V1, -1)');
SELECT PagePID,IndexID,PageType,IndexLevel FROM Tsup WHERE IndexLevel>0
UNION ALL
SELECT PagePID,IndexID,PageType,IndexLevel FROM Vsup WHERE IndexLevel>0

Итак, тестовый скрипт несложен. Тест-таблица T1 содержит 25 тыс. записей и кластерный индекс IX_tab по первой колонке. На этой таблице создается элементарное представление V1, а на этом последнем — еще один кластерный индекс IX_viw. После этого, по «технологии» чье описание и интерпретацию результатов можно взять из «базовой статьи», мы переходим к изучению структуры обоих индексов. Прежде всего нас интересует — сколько страниц (то есть data pages) находится в каждом из индексов на не листовом уровне? На машине автора результат от завершающей пары SELECT-ов был таким:

PagePID IndexID PageType    IndexLevel
280     1       2           1
400     1       2           1

Что позволяет нам сделать вывод: не листовой уровень каждого из кластерных индексов состоит ровно из одной страницы, и номера этих страниц равны 280 и 400 для индекса «на таблице» и «на представлении» соответственно. Хорошо, исследуем содержимое каждой из двух страниц:

1
2
3
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 280, 3) --table idx, root level(1)
DBCC PAGE([~DB~], 1, 400, 3) --view idx, root level(1)

Для табличного индекса усеченный резалт-сет имеет вид:

PageId  Row Level   ChildPageId col1 (key)
280     0   1       240         NULL
280     1   1       241         476
280     2   1       242         952
280     3   1       243         1428
...     ... ...     ...         ...
280     51  1       331         24276
280     52  1       332         24752

А для представления на индексе:

PageId  Row Level   ChildPageId col1 (key)
400     0   1       360         NULL
400     1   1       361         476
400     2   1       362         952
400     3   1       363         1428
...     ... ...     ...         ...
400     51  1       451         24276
400     52  1       452         24752

Фактически, отличие только одно: каждая страница ссылается на свои дочерние страницы, то есть те, что располагаются на листовом уровне. Во всем прочем — полное совпадение. Но это не листовой уровень, а отличия, если озвученная ранее версия верна, нужно искать как раз на листовом уровне. В качестве «свидетелей» каждой из сторон выберем первые страницы этого уровня. То есть нас интересует содержимое страницы 240 (она скажет что содержится на листовом уровне в табличном индексе) и содержимое страницы 360 (то же самое для индекса представления). Смотрим сначала 240-ю:

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 240, 3) --table idx, leaf level(0)

Получаем довольно внушительный отчет, т.к. на указанной странице содержится масса записей или, говоря более техническим языком — слотов (slots). Но вот, для примера, слот под номером 0:

Slot 0 Offset 0x60 Length 15
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 15

Memory Dump @0x00000000160CA060

0000000000000000:   10000c00 00000000 a0860100 020000††††........ †.....  

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 0                            
Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4
col2 = 100000                        
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (78d82fa561ac)

А теперь анализируем страницу 360-ю:

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 360, 3) --view idx, leaf level(0)

И вот что творится в ее нулевом слоте:

Slot 0 Offset 0x60 Length 15
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 15

Memory Dump @0x00000000160CA060

0000000000000000:   10000c00 00000000 a0860100 020000††††........ †.....  

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 0                            
Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4
col2 = 100000                        
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (78d82fa561ac)

Иными словами совпадает абсолютно все, байт-в-байт, включая и рассчитанный для данного слота хэш. Анализ прочих слотов обоих индексов, а так же слотов на других страницах, приводит нас к неопровержимому выводу:

Кластерный индекс представления является абсолютно полноценным кластерным индексом. На своем листовом уровне он содержит полную информацию по абсолютно всем колонкам базовых таблиц, разумеется если эти колонки включены в код (оператор SELECT) представления. Для извлечений значений указанных колонок никакой «второй фазы поиска» не требуется, все извлекается со страниц листового уровня обсуждаемого индекса.

Хорошо, «миф» развенчан, но как быть с исходным вопросом? Так может ли быть на таблице кластерных индексов больше чем один? То, что мы легко опровергли миф о «неполноценности» индекса представления ровно никоим образом не может повлиять на ответ: каждый отдельный объект способен иметь максимум один кластерный индекс. Точка. То что в нашем примере индекса кажется два (впрочем — почему кажется? Их два и есть! Это два физически разных индекса, пусть и с идентичным содержимым) — так и базовых объектов два! Только тут надо четко различать термин «базовый объект». Для представления таковым является таблица, а вот для индекса — любой объект указанный в клаузуле ON команды CREATE INDEX. Поэтому с точки зрения индекса базовых объектов два — таблица и представление. И ничто не может помешать каждому из них иметь свой собственный кластерный индекс. То, что в нашем частном случае содержимое оказывается полностью эквивалентным есть ничто иное как именно он самый — «частный случай». Да, два разных индекса, но содержимое совпадает — никаких технических проблем, все допустимо. А вот логические проблемы такого подхода видны «невооруженным глазом». Пометим себе на память:

Если таблица уже имеет кластерный индекс — нет смысла создавать еще один по тому же ключу в представлении извлекающему все (либо часть) колонки из такой таблицы и не обращающемуся к колонкам никаких иных таблиц. Поступив так, мы просто полностью (либо частично) продублируем индекс имеющийся, повысим для сервера «цену» поддержки наших данных в актуальном состоянии не получив взамен никакого «профита».

Переходим к «мифу номер два» — затраты ресурсов на поддержку индексных представлений. То, что каждый очередной индекс (и даже не важно где он создан — на таблице ли, на представлении ли) оказывает негативное влияние на доступные ресурсы известно всем и сомнений не вызывает — за все в этом мире вообще и в SQL Server в частности надо платить. Но вот в оценке масштаба такого влияния для именно индексов на представлении наблюдаются явные расхождения у различных DBA. Есть очередное мнение, что кластерный и любой иной индекс представления полностью перестраивается при любой модификации данных в базовой таблице. Это мнение настолько распространено, что зачастую DBA спросивший на форуме «создали несколько индексов на представлениях — решение стало заметно тормозить, почему?» получает озвученную версию в качестве объяснения. Ну-с — давайте подтвердим или опровергнем. Для начала пересоздадим нашу тестовую базу:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
USE master
GO
CREATE DATABASE [~DB~]
ON PRIMARY (
    NAME = '~DB~_Data',
    FILENAME = 'c:\sqlCMD.ru\~DB~.mdf',
    SIZE = 20 MB )
LOG ON (
    NAME = '~DB~_Log',
    FILENAME = 'c:\sqlCMD.ru\~DB~.ldf',
    SIZE = 20 MB )
GO
ALTER DATABASE [~DB~] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [~DB~]
GO
CREATE TABLE dbo.T1 (col1 int, col2 int)
GO
declare @i int
SET @i = 0
BEGIN TRANSACTION
    WHILE @i < 250 --loop counter
    BEGIN
        insert into dbo.T1 VALUES (@i, 100000+@i)
        set @i += 1
     END
COMMIT TRANSACTION
CREATE UNIQUE CLUSTERED INDEX [IX_tab] ON T1 (col1)
GO
CREATE VIEW dbo.V1 WITH SCHEMABINDING
AS
SELECT col1, col2 FROM dbo.T1
GO
CREATE UNIQUE CLUSTERED INDEX [IX_viw] ON V1 (col1)
GO

Теперь «обрежем» записи в журнале транзакций и посмотрим на его текущее содержимое:

1
2
3
CHECKPOINT
go
DBCC LOG([~DB~], 2)

Видим, что в журнале на текущий момент всего две операции: LOP_BEGIN_CKPT и LOP_END_CKPT — начало и конец того самого чек-поинта.

Если вам нужна помощь по функционалу недокументированной команды DBCC LOG и ее параметрам, то первоначальные сведения можно почерпнуть из части третьей статьи «Как перестать называть журнал транзакций SQL Server лог-файлом и прекратить борьбу за его размер».

Попробуем вставлять:

1
INSERT dbo.T1 (col1, col2) VALUES  (-2,-2)

Что там в журнале?

1
DBCC LOG([~DB~], 2)

А вот что там (усеченно, только релевантная информация):

Current LSN             Operation       Page ID
00000057:0000177a:000c  LOP_INSERT_ROWS 0001:00000118
00000057:0000177a:000e  LOP_INSERT_ROWS 0001:000000f0
00000057:0000177a:0018  LOP_INSERT_ROWS 0001:00000190
00000057:0000177a:001a  LOP_INSERT_ROWS 0001:00000168

Переводя идентификатор страниц из шестнадцатиричной системы в десятичную «дешифруем» записи:

  • 0x118=280 — вставка в страницу не листового уровня для индекса таблицы
  • 0xf0=240 — вставка в страницу листового уровня того же индекса
  • 0x190=400 — вставка в страницу не листового уровня для индекса представления
  • 0x168=360 — вставка в страницу листового уровня того же индекса

Комментарий короткий: «телодвижений» настолько мало, насколько это вообще может быть возможно. Проведя тот же эксперимент для изменения данных:

1
UPDATE dbo.T1 SET col2=999 WHERE col1=0

получаем:

Current LSN             Operation       Page ID
00000057:0000177a:000e  LOP_MODIFY_ROW  0001:000000f0
00000057:0000177a:001a  LOP_MODIFY_ROW  0001:00000168

Иными словами — еще оптимальнее, затрагиваются лишь страницы листового уровня. Кстати — вопрос для самоконтроля: а почему в предыдущем примере корневые страницы (0x118 и 0x190 соответственно) были задействованы, а в данном случае нет? Правильно — потому что в том случае менялось «граничное» значение (-2 стала новой левой границей для всей таблицы и, разумеется, индекса), а в этом — обычное, промежуточное. Если данное объяснение не показалось вам исчерпывающим, вам определенно стоит обратить свое внимание на «базовую статью». Там вопросы граничных и обычных значений разбираются куда как подробнее. Мы же тем временем завершим текущий комплекс экспериментов удалением строки:

1
DELETE dbo.T1 WHERE col1=1

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

Current LSN             Operation       Page ID
00000057:0000177a:000e  LOP_DELETE_ROWS 0001:000000f0
00000057:0000177a:001a  LOP_DELETE_ROWS 0001:00000168

Очередной «миф» повержен: SQL Server тратит на поддержание любого индекса (и индекса на представлении в том числе) настолько мало ресурсов, и пытается выполнить указанную задачу за такое минимальное число шагов, что говорить о какой-либо избыточности (а уж тем более о такой грандиозной операции как тотальное перестроение индекса) просто смешно. Автор затрудняется сказать откуда у данного мифа «ноги растут». Не исключено, что в SQL Server ну очень ранних версий (что-то типа «до 7.0») индексированные представления были реализованы столь примитивным способом, что реально перестраивали себя при каждом изменении данных? 8O Ну и с тех пор, «по инерции»... В любом случае на текущем этапе развития продукта претензий в данном разрезе не может быть никаких: да, ресурсы на поддержу индексов несомненно тратятся, но происходит это в стиле «жесткой экономии» — изменениям подвергаются лишь те страницы данных не менять которые просто невозможно. На все прочее сервер не отвлекается.