Первичный ключ в таблице

#sql #sql_server

Повышает ли наличие первичного ключа производительность работы с таблицей, если он
не участвует в условии отбора?

Если не ошибаюсь, то наличие первичного ключа задает способ хранения строк.

Если первичного ключа нет, то все хранится в куче. Так же есть некоторые особенности
в использовании некластиризированных индексов на куче и без кучи.
    

Ответы

Ответ 1

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

Другое дело, что в SqlServer первичный ключ по умолчанию является кластерным индексом.
То есть в определении таблицы

create table TableName (ID int primary key, ... );


primary key эквивалентно primary key clustered. При желании, однако, вполне можно
сделать первичный ключ и некластерным индексом, указав primary key nonclustered.


  Если не ошибаюсь, то наличие первичного ключа задает способ хранения
  строк.
  Если первичного ключа нет, то все хранится в куче.


Правильнее будет сформулировать - наличие или отсутствие кластерного индекса (не
обязательно, чтобы он был первичным ключом).

Если на таблице нет кластерного индекса, то данные таблицы хранятся в куче без какого
либо определённого порядка.

Если же на таблице есть кластерный индекс, то в нём и содержатся данные таблицы.
Кластерный индекс представляет собой "B+"-дерево, в страницах которого лежат ключи
в логически упорядоченном виде. В дополнение к ключам в страницах верхних уровней лежат
ссылки на страницы следующего уровня, а в страницах нижнего уровня ("листьях") - данные
неключевых столбцов.




  Повышает ли наличие первичного ключа производительность работы с
  таблицей, если он не участвует в условии отбора?


Может повышать.

Сканирование кластерного первичного ключа (вообще кластерного индекса) может быть
более производительным по сравнению со сканированием кучи, т.к. в кластерном индексе,
в отличие от кучи, при изменении данных не создаются переадресованные записи (т.н.
forwarded records).

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

Пример.

Создадим две почти одинаковых таблицы. Одну с некластерным первичным ключом:

create table Heap
(
    ID int,
    Name varchar(50),
    Comments varchar(1000),
    constraint PK_Heap primary key nonclustered (ID)
);


Другую, с такими же столбцами, но с кластерным первичным ключом:

create table Cluster
(
    ID int,
    Name varchar(50),
    Comments varchar(1000),
    constraint PK_Cluster primary key clustered (ID)
);


Добавим в таблицы данных:

;with nums as (
    select N = row_number() over (order by @@spid)
    from sys.all_columns a cross join sys.all_columns b
)
insert into Cluster (ID, Name, Comments)
select top (10000)
    N, 'Name ' + cast((N - 1) % 1000 + 1 as varchar(10)), 'Comments'
from nums
order by N;

insert into Heap (ID, Name, Comments)
select ID, Name, Comments from Cluster;


Включив statistics io, выполним одинаковый запрос к каждой из таблиц - такой, чтобы
он вызывал их полное сканирование:

set statistics io on;
declare @cnt int;
select @cnt = count(1) from Heap where Name = 'Name 15';
select @cnt = count(1) from Cluster where Name = 'Name 15';
set statistics io off;


Статистика показывает, что число чтений пока примерно одинаково:

Table 'Heap'. Scan count 1, logical reads 61 ...
Table 'Cluster'. Scan count 1, logical reads 63 ...


Изменим данные в таблицах (что вызовет появление переадресованных записей в таблице-куче):

update Heap set Comments = replicate('More ', 50) + Comments
update Cluster set Comments = replicate('More ', 50) + Comments


И повторим запросы с count и статистикой к таблицам:

Table 'Heap'. Scan count 1, logical reads 9139 ...
Table 'Cluster'. Scan count 1, logical reads 680 ...


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