Разделение страницы? Как это работает?

jssteele89 спросил: 28 марта 2018 в 01:51 в: sql
  1. Разделение страницы влияет на физическую сортировку данных в индексе или таблице в целом? ПРИМЕР: Если у вас индексированный столбец электронной почты и расщепление страницы происходило много, будет ли адрес электронной почты недействительным в списке, а теперь более длинным?

  2. Как разделение / фрагментация страницы работает с записью первичного ключа? ПРИМЕР: Если у вас есть следующая таблица с первичными ключами, являющимися номерами.

    1, bob, chair
    2, joe, table
    3, brandon, lamp
    4, jared, tv
    

Допустим, вы удалили запись 3. Теперь таблица читает 1 , 2, 4 (не по порядку), а затем запись 3 снова вставлена ​​позднее? Затем он будет читать 1,2,4,3. Записывает ли запись 4 место записи 3 или зарезервировано ли она в случае повторной установки записи 3 позднее? Или вам нужно выполнить перестройку индекса, чтобы физически пересортировать его?

Спасибо

3 ответа

The Impaler ответил: 28 марта 2018 в 02:13

SQL Server использует таблицы кластеризованного индекса (по умолчанию). Эти таблицы сортируются по их первичному ключу.

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

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

Andrew ответил: 28 марта 2018 в 02:26

1.) Кластерный индекс - это логический, а не физический порядок, не пытайтесь думать о нем как о реальном физическом порядке, слишком много уровней абстракции между индексом и хранилищем, чтобы рассмотреть физический порядок. (Распределение экстентов, Файловые группы, Фрагментация на диске / Lun, сами Lun - все это делает понятие физического порядка неверным.)

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

2.) Физический порядок по сравнению с логическим - опять же, остановите Если подумать о физическом порядке, даже если строки находятся на одной странице, массив слотов страницы - это тот, который поддерживает фактический порядок данных на самой странице. Таким образом, в то время как на странице это может закончиться 1,2,4,3, массив слотов будет 4,3,2,1 (массив слотов начинается в конце страницы и читается в обратном направлении) - независимо от места -использовано или нет, на самом деле не имеет значения, поскольку оно будет обрабатывать позиции смещения на странице в зависимости от порядка, который будет поддерживаться этим массивом.

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

Как SQL Server работает на уровне хранилища, может стать довольно сложной темой, лично я бы порекомендовал книгу Калена Делани, хотя другие доступны.

dfundako ответил: 28 марта 2018 в 02:45

Давайте посмотрим на ваш пример и посмотрим, что произойдет.

Во-первых, фиктивная таблица и фиктивные данные.

CREATE TABLE testing (ID INT PRIMARY KEY, junk VARCHAR(100))
GO INSERT INTO testing(ID, junk)
VALUES 
(1, 'blah'),
(2, 'blahhhh'),
(3, 'Blabbb')
GO 

Теперь давайте посмотрим на фактической странице данных она сохраняется.

SELECT
   *, 
   sys.fn_PhysLocFormatter(%%physloc%%) AS Page_Location
   from testing

Возьмите выходные данные этой функции и поместите их в DBCC PAGE

DBCC TRACEON(3604) --Send output of DBCC PAGE to SSMS Client
GO
DBCC PAGE('your_db_name',the_first_num_from_page_location,the_second_num_from_page_location,3)

Это покажет вам, что на самом деле есть на странице данных. "Слоты" - это строки в вашей таблице, и страница начинается с слота 0.

Slot 0 Offset 0x60 Length 19Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19                    
Memory Dump @0x0000004493BFA0600000000000000000:   30000800 01000000 02000001 00130062 6c6168    0..............blahSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4ID = 1                              Slot 0 Column 2 Offset 0xf Length 4 Length (physical) 4junk = blah                         Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (8194443284a0)   

Продолжайте читать страницу, и вы увидите ваши 3 строки данных. Теперь удалите запись 2, повторно запустите запрос page_location и посмотрите на страницу. Слот 1 теперь должен быть вашей записью ID = 3.

DELETE FROM testing
WHERE ID = 2

Теперь перестройте индекс PK и заново вставьте запись 2.

ALTER INDEX your_pk here ON testing
REBUILDINSERT INTO testing(ID, Junk)
VALUES 
(2,'blerg')
GO 

Повторите запрос page_location, так как страницы, вероятно, изменились после перестройки и посмотрите на ваши слоты. Даже если вы вставили 2 после 3, слот 1 (строка 2) является вашей записью ID = 2.

Однако на странице данных есть нечто, называемое массивом слотов, в котором хранится логический порядок данных. Таким образом, даже если данные на странице могут физически храниться совершенно не в порядке, массив слотов сообщает серверу sql, как их следует логически отсортировать на основе индекса.

Вы можете увидеть это в действии здесь:

DBCC TRACEON(3604) --Send output of DBCC PAGE to SSMS Client
GO
DBCC PAGE('your_db_name',the_first_num_from_page_location,the_second_num_from_page_location,2)

В самом низу вы увидите таблицу смещений: OFFSET TABLE:

Row - Offset                        
2 (0x2) - 115 (0x73)                
1 (0x1) - 136 (0x88)                
0 (0x0) - 96 (0x60)   

Снова перестройте ваш PK и посмотрите, как он изменения:

OFFSET TABLE:Row - Offset                        
2 (0x2) - 135 (0x87)                
1 (0x1) - 115 (0x73)                
0 (0x0) - 96 (0x60)   

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