Dizinlerle iyileştirme
Dizinler, tablo satırları için iyileştirilmiş arama yolları oluşturarak veri alımını hızlandıran veri yapılarıdır. Dizinler olmadan, veritabanı motorunun eşleşen kayıtları bulmak için tablodaki her satırı taraması gerekir, bu da tablolar büyüdükçe aşırı derecede yavaşlayan tam tablo taraması anlamına gelir.
Dizin bir kitabın dizini gibi çalışır: Makale bulmak için her sayfayı okumak yerine doğrudan ilgili sayfalara atlamak için dizine danışabilirsiniz. Veritabanı benzer şekilde dizinleri kullanır ve milyonlarca satır karşılaştırmasını birkaç verimli aramaya dönüştürür.
Ancak, veritabanının verinin yanında dizin yapısını koruması gerektiğinden dizinler depolama alanı kullanır ve , INSERTve UPDATE işlemlerini yavaşlatırDELETE. Bu denge, dizin seçimini hem sorgu performansını hem de yazma aktarım hızını doğrudan etkileyen kritik bir tasarım kararı haline getirir.
Farklı dizin türleri farklı amaçlara hizmet eder.
Satır depolama dizinlerini kullanın
Verimli dizinler tasarlamak, iyi veritabanı ve uygulama performansı elde etmek için önemlidir. Dizin eksikliği, fazla dizin oluşturma veya düşük tasarlanmış dizinler veritabanı performans sorunlarının en önemli kaynaklarıdır.
Rowstore dizinleri verileri satır biçiminde düzenler ve bir satırın tüm sütunlarını aynı sayfada depolar ve bu da tam kayıtları alan veya sık güncelleştirmeler yapan işlem iş yükleri için en uygun hale getirir.
Kümelenmiş dizin , tablodaki veri satırlarını anahtar değerlerine göre sıralar ve depolar. Bu anahtar değerler, dizin tanımına dahil edilen sütunlardır. Veri satırları tek bir sırada depolanabildiği için tablo başına yalnızca bir kümelenmiş dizin olabilir.
Kümelenmemiş dizin , veri satırlarından ayrı bir yapıya sahiptir. Kümelenmemiş dizin, kümelenmemiş dizin anahtarı değerlerini içerir ve her anahtar değeri girdisinin anahtar değerini içeren veri satırına yönelik bir işaretçisi vardır. Bir tabloda veya dizinli görünümde birden çok kümelenmemiş dizin oluşturabilirsiniz.
-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID
ON Product(ProductID);
-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category
ON Product(Category)
INCLUDE (ProductName, Price);
Kümelenmiş dizinler, fiziksel satır sırasını tanımladıkları ve sıralı veriler üzerinde taramaları iyileştirdikleri için verimli aralık sorgularına, kararlı ve dar anahtarlara ya da kimlik sütunları veya tarih alanları gibi doğal bir sıralama düzenine ihtiyacınız olduğunda en iyisidir.
Kümelenmemiş dizinler, kümelenmiş anahtarla uyumlu olmayan belirli koşul, birleşim veya sıralama desenleri için hızlı aramalara ihtiyacınız olduğunda veya anahtar aramalarından kaçınmak için ek sütunlar ekleyerek bir sorguyu kapsamak istediğinizde idealdir.
Bunlar arasında seçim yapmak, verilere nasıl eriştiğinize bağlıdır: Birincil erişim yolu için kümelenmiş dizini ve yazma işlemlerinde neden oldukları maliyeti dengeleyerek, alternatif, yüksek oranda seçici veya sık sorgulanan desenleri desteklemek için kümelenmiş olmayan dizinleri kullanın.
Columnstore dizinlerini anlamak
Geleneksel rowstore dizinleri, tek tek kayıtları alan işlem sistemleri için mükemmel olan verileri satır satır depolar. Ancak milyonlarca satırı tarayarak toplamaları (SUM, AVG, COUNT) hesaplayan analitik sorgular gerekmeyen sütunları okumak için zaman harcar. Columnstore dizinleri, verileri sütun sütun depolayarak ve yalnızca sorgunuz için gerekli sütunları okuyarak bu sorunu çözmeyi hedefler.
Columnstore mimarisini anlayın
Columnstore dizini, verileri her birinde en fazla 1.048.576 satır içeren satır grupları halinde düzenler. Her satır grubu içinde motor her sütunu ayrı ayrı bir sütun kesimi olarak depolar ve bağımsız olarak sıkıştırır. Bu mimari, sorgu iyileştiricisinin yalnızca sorgu için gereken sütunları okumasına olanak tanır ve ilgisiz verileri tamamen atlar.
Veri eklediğinizde, küçük toplu işlemler ilk olarak B+ ağaç dizini kullanan geçici bir satır deposu yapısı olan deltastore'ya gider. Bir delta satır grubu yeterli satır biriktirdikten (en az 102.400) sonra, tuple-mover adlı bir arka plan işlemi bunu columnstore'da sıkıştırır. 102.400 veya daha fazla satırlık toplu yüklerle gelen satırlar deltastore'yi atlar ve doğrudan columnstore'ya sıkıştırır.
Aşağıdaki tabloda columnstore dizinleri için öneri açıklanmaktadır:
| Scenario | Tavsiye | Nedeni |
|---|---|---|
| Veri ambarı olgu tabloları | Columnstore kullanın | Analiz için kullanılan milyondan fazla satıra sahip tablolar sütunlu depolama ve sıkıştırmadan yararlanır |
| Raporlama veritabanları | Columnstore kullanın | Toplu sorgular içeren okuma ağırlıklı iş yükleri sütun odaklı erişimle daha hızlı performans gösterir |
| Geçmiş veriler | Columnstore'u kullanın | Nadiren güncelleştirdiğiniz ancak sıklıkla analiz ettiğiniz arşivlenmiş veriler yüksek sıkıştırma oranlarına ulaşır |
| Küçük tablolar (<1 milyon satır) | columnstore'lardan kaçının | Ek yük avantajlardan daha ağır basıyor; satır gruplarının etkili sıkıştırma için yeterli satıra ihtiyacı var |
| Yüksek frekanslı güncelleştirmeler/silmeler | columnstore'lardan kaçının | Değişiklikler satırları yerinde güncelleştirmek yerine silinmiş olarak işaretleyerek parçalanmalara neden oluyor |
| Tek satırlı aramalar | sütun depolarından kaçının | Tek tek kayıtları almak için rowstore dizinleri daha hızlıdır |
Kümelenmiş Columnstore Dizini 'ni (CCI) kullanma
Kümelenmiş Columnstore Dizini (CCI), tüm tablonun birincil depolama yapısı haline gelen ve mevcut kümelenmiş rowstore dizinini değiştiren bir sütun deposu dizini türüdür. Rowstore tablosunun yanında ikincil bir sütunlu kopya oluşturan kümelenmemiş columnstore dizininden (NCCI) farklı olarak, CCI tüm tablo verilerini yalnızca sütun biçiminde depolar.
Bu, tablonun geleneksel satır tabanlı depolama alanı kullanmadığı anlamına gelir; motor her sütunu ayrı ayrı sıkıştırır ve depolar. Hem CCI hem de NCCI aynı sütunlu sıkıştırma ve toplu işleme iyileştirmelerini kullanır, ancak analiz birincil iş yükü olduğunda ve satır düzeyi işlemsel erişim desenlerine ihtiyacınız olmadığında CCI kullanır. Buna karşılık, NCCI aynı tablodaki analitik sorgular için sütunlu bir yapı sağlarken işlem sorguları için satır deposu dizinlerini korumanıza olanak tanır.
deyimini CREATE CLUSTERED COLUMNSTORE INDEX kullanarak kümelenmiş columnstore dizini oluşturabilirsiniz. Bir örnek aşağıda verilmiştir:
-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;
-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;
Kümelenmemiş Columnstore Dizini (NCCI) Kullanma
Kümelenmemiş Columnstore Dizini (NCCI), mevcut rowstore tablosunun yanı sıra seçili sütunların ayrı bir sütunlu kopyasını oluşturur ve aynı tablonun hem işlem hem de analitik iş yüklerine verimli bir şekilde hizmet vermesine olanak sağlar. Tablo, hızlı tek satırlı aramalar ve güncelleştirmeler için özgün kümelenmiş satır deposu dizinini korurken, NCCI analiz sorguları için en iyi duruma getirilmiş sütun tabanlı erişim sağlar. Sorgu iyileştiricisi, sorgu desenini temel alan satır deposu ve columnstore yapıları arasında otomatik olarak seçim uygular.
deyimini kullanarak CREATE NONCLUSTERED COLUMNSTORE INDEX kümelenmemiş bir columnstore dizini oluşturabilirsiniz. Bir örnek aşağıda verilmiştir:
-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);
Columnstore dizinlerini izleme
Dinamik yönetim görünümünü sorgulayarak sys.dm_db_column_store_row_group_physical_stats columnstore dizinlerinizin sistem durumunu ve performansını izleyebilirsiniz.
Aşağıdaki sorguda durum, satır sayısı, silinen satırlar ve depolama boyutu gibi satır grubu istatistikleri gösterilir. Açık satır grupları hala deltastore'a eklemeleri kabul ediyor, kapalı satır grupları tuple taşıyıcının bunları sıkıştırması için bekliyor ve sıkıştırılmış satır grupları verileri sütun biçiminde depolar. Yüksek silinmiş satır sayısı veya birçok küçük satır grubu, ALTER INDEX REORGANIZE ile çözebileceğiniz parçalanmayı gösterir.
-- Check columnstore health
SELECT
object_name(object_id) AS TableName,
state_desc,
total_rows,
deleted_rows,
size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');
Dizin seçimi hem sorgu performansını hem de yazma aktarım hızını doğrudan etkiler. Üretimde yüksek maliyetli yeniden derlemeleri ve performans sorunlarını önlemek için dizinleri ilk geliştirme sırasında dikkatli bir şekilde tasarlar.