Prowadzę ciągłą debatę z różnymi programistami w moim biurze na temat kosztu indeksu i tego, czy unikalność jest korzystna czy kosztowna (prawdopodobnie obie). Sednem problemu są nasze konkurujące zasoby.
tło
Wcześniej czytałem dyskusję, w której stwierdzono, że Uniqueindeks nie jest dodatkowym kosztem do utrzymania, ponieważ Insertoperacja domyślnie sprawdza, gdzie mieści się w drzewie B, a jeśli duplikat zostanie znaleziony w nieunikalnym indeksie, dołącza unikalizator do koniec klucza, ale w przeciwnym razie wstawia bezpośrednio. W tej sekwencji zdarzeń Uniqueindeks nie ma dodatkowych kosztów.
Mój współpracownik zwalcza to stwierdzenie, mówiąc, że Uniquejest egzekwowane jako druga operacja po poszukiwaniu nowej pozycji w drzewie B, a zatem jego utrzymanie jest bardziej kosztowne niż indeks nieunikalny.
W najgorszym przypadku widziałem tabele z kolumną tożsamości (z natury unikatową), która jest kluczem do klastrowania tabeli, ale jest wyraźnie określona jako nie-unikalna. Z drugiej strony najgorsza jest moja obsesja na punkcie wyjątkowości, a wszystkie indeksy są tworzone jako unikalne, a gdy nie jest możliwe zdefiniowanie wyraźnie unikalnej relacji do indeksu, dołączam PK tabeli na końcu indeksu, aby zapewnić wyjątkowość jest gwarantowana.
Często biorę udział w recenzowaniu kodu dla zespołu programistów i muszę być w stanie podać ogólne wytyczne dla nich. Tak, każdy indeks powinien zostać oceniony, ale jeśli masz pięć serwerów z tysiącami tabel i aż dwadzieścia indeksów w tabeli, musisz być w stanie zastosować kilka prostych reguł, aby zapewnić określony poziom jakości.
Pytanie
Czy wyjątkowość ma dodatkowy koszt zaplecza w Insertporównaniu z kosztem utrzymania nieunikalnego indeksu? Po drugie, co jest złego w dodawaniu klucza podstawowego tabeli na końcu indeksu, aby zapewnić unikalność?
Przykładowa definicja tabeli
create table #test_index
(
id int not null identity(1, 1),
dt datetime not null default(current_timestamp),
val varchar(100) not null,
is_deleted bit not null default(0),
primary key nonclustered(id desc),
unique clustered(dt desc, id desc)
);
create index
[nonunique_nonclustered_example]
on #test_index
(is_deleted)
include
(val);
create unique index
[unique_nonclustered_example]
on #test_index
(is_deleted, dt desc, id desc)
include
(val);Przykład
Przykład dodania Uniqueklucza na końcu indeksu znajduje się w jednej z naszych tabel faktów. Jest Primary Keyto jest Identitykolumna. Jednakże, Clustered Indexto zamiast kolumny schemat partycjonowania, a następnie trzech zagranicznych kluczowych wymiarów bez wyjątkowości. Wybrana wydajność na tym stole jest fatalna i często uzyskuję lepsze czasy wyszukiwania, korzystając Primary Keyz wyszukiwania kluczowego, zamiast korzystać z niego Clustered Index. Inne tabele, które mają podobny projekt, ale mają Primary Keydołączone na końcu, mają znacznie lepszą wydajność.
-- date_int is equivalent to convert(int, convert(varchar, current_timestamp, 112))
if not exists(select * from sys.partition_functions where [name] = N'pf_date_int')
create partition function
pf_date_int (int)
as range right for values
(19000101, 20180101, 20180401, 20180701, 20181001, 20190101, 20190401, 20190701);
go
if not exists(select * from sys.partition_schemes where [name] = N'ps_date_int')
create partition scheme
ps_date_int
as partition
pf_date_int all
to
([PRIMARY]);
go
if not exists(select * from sys.objects where [object_id] = OBJECT_ID(N'dbo.bad_fact_table'))
create table dbo.bad_fact_table
(
id int not null, -- Identity implemented elsewhere, and CDC populates
date_int int not null,
dt date not null,
group_id int not null,
group_entity_id int not null, -- member of group
fk_id int not null,
-- tons of other columns
primary key nonclustered(id, date_int),
index [ci_bad_fact_table] clustered (date_int, group_id, group_entity_id, fk_id)
)
on ps_date_int(date_int);
go
if not exists(select * from sys.objects where [object_id] = OBJECT_ID(N'dbo.better_fact_table'))
create table dbo.better_fact_table
(
id int not null, -- Identity implemented elsewhere, and CDC populates
date_int int not null,
dt date not null,
group_id int not null,
group_entity_id int not null, -- member of group
-- tons of other columns
primary key nonclustered(id, date_int),
index [ci_better_fact_table] clustered(date_int, group_id, group_entity_id, id)
)
on ps_date_int(date_int);
go
CaseiIfstruktury są ograniczone do 10 poziomów, sensowne jest, że istnieje również limit rozwiązywania nieunikalnych bytów. W twoim oświadczeniu brzmi to tak, jakby dotyczyło tylko przypadków, gdy klucz klastrowania nie jest unikalny. Czy jest to problem dlaNonclustered Indexczy klucz klastrowania jest,Uniqueczy nie ma problemu zNonclusteredindeksami?