Dizinleri tasarlama

Tamamlandı

SQL Server, farklı iş yüklerini desteklemek için çeşitli dizin türleri sunar. Yüksek düzeyde bir dizin, tablo veya görünümle ilişkilendirilmiş bir disk içi yapı olarak düşünülebilir ve SQL Server'ın tüm tabloyu taramaya kıyasla dizin anahtarıyla (tablo veya görünümdeki bir veya daha fazla sütundan oluşan) ilişkili satırı veya satırları daha kolay bulmasını sağlar.

Kümelenmiş dizinler

Yaygın bir DBA iş görüşmesi sorusu, dizinler SQL Server'daki temel veri depolama teknolojileri olduğundan, adaya kümelenmiş ve kümelenmemiş dizin arasındaki farkı sormaktır. Kümelenmiş dizin, anahtar değerine göre sıralanmış düzende depolanan temel tablodur. Satırlar yalnızca bir sırada depolanabildiği için belirli bir tabloda yalnızca bir kümelenmiş dizin olabilir. Kümelenmiş dizini olmayan bir tablo yığın olarak adlandırılır ve yığınlar genellikle yalnızca hazırlama tabloları olarak kullanılır. Önemli bir performans tasarım ilkesi, kümelenmiş dizin anahtarınızı mümkün olduğunca dar tutmaktır. Kümelenmiş dizininiz için bir veya daha fazla anahtar sütun göz önünde bulundurulduğunda, benzersiz olan veya birçok farklı değer içeren sütunları seçmeniz gerekir. İyi bir kümelenmiş dizin anahtarının bir diğer özelliği de sırayla erişilen ve tablodan alınan verileri sıralamak için sık kullanılan kayıtlardır. Sıralama için kullanılan sütunda kümelenmiş dizinin bulunması, veriler zaten istenen düzende depolanacağı için sorgu her yürütülürken sıralama maliyetini engelleyebilir.

Uyarı

Tablonun belirli bir sırada 'depolandığını' söylediğimizde, fiziksel, disk içi sırasına değil mantıksal düzene başvururuz. Dizinler sayfalar arasında işaretçilere sahiptir ve işaretçiler mantıksal düzenin oluşturulmasına yardımcı olur. Bir dizini sırayla tararken, SQL Server işaretçileri sayfadan sayfaya izler. Dizin oluşturulduktan hemen sonra, büyük olasılıkla diskte fiziksel sırada depolanır, ancak verilerde değişiklik yapmaya başladıktan ve dizine yeni sayfaların eklenmesi gerektiğinde işaretçiler bize doğru mantıksal sırayı verir, ancak yeni sayfalar büyük olasılıkla fiziksel disk sırasına sahip olmayacaktır.

Kümelenmemiş dizinler

Kümelenmemiş dizinler, veri satırlarından ayrı yapılardır. Kümelenmemiş dizin, dizin için tanımlanan anahtar değerlerini ve anahtar değerini içeren veri satırının işaretçisini içerir. SQL Server'daki dahil edilen sütunlar özelliğini kullanarak kümelenmemiş dizinin yaprak düzeyine fazladan anahtar olmayan sütunlar ekleyebilirsiniz ve böylece daha fazla sütunu kaplayabilirsiniz. Tabloda birden çok kümelenmemiş dizin oluşturabilirsiniz.

Aşağıdaki örnekte, bir dizin eklemeniz veya mevcut bir kümelenmemiş dizine sütun eklemeniz gerektiğinde gösterilmektedir.

Anahtar Arama işlemcisi ile Sorgu ve Sorgu Yürütme Planı

Sorgu planı, dizin arama kullanılarak alınan her satır için kümelenmiş dizinden (tablonun kendisinden) daha fazla veri alınması gerektiğini gösterir. Kümelenmemiş bir dizin vardır, ancak yalnızca ürün sütununu içerir. Sorgudaki diğer sütunları bir kümelenmemiş dizine eklerseniz, anahtar aramasını ortadan kaldırmak için yürütme planı değişikliğini görebilirsiniz.

Anahtar Arama Yokken Dizini ve Sorgu Planını Değiştirme

Yukarıda oluşturulan dizin, kapsayan bir dizin örneğidir. Anahtar sütununa ek olarak, sorguyu kapsayan ve tablonun kendisine erişme gereksinimini ortadan kaldıran ek sütunlar da eklersiniz.

Hem kümelenmemiş hem de kümelenmiş dizinler benzersiz olarak tanımlanabilir; bu da anahtar değerlerinin yinelenmemesi anlamına gelir. Bir tabloda birincil anahtar veya benzersiz kısıtlaması oluşturduğunuzda, benzersiz dizinler otomatik olarak oluşturulur.

Bu bölüm, SQL Server'da satır deposu dizinleri olarak da bilinen b ağacı dizinlerine odaklanır. Aşağıdaki görüntü bir b ağacının genel yapısını temsil eder:

SQL Server ve Azure SQL'de bir dizinin B-ağacı mimarisi

Bir dizin b ağacındaki her sayfa dizin düğümü olarak adlandırılır ve b ağacının üst düğümü kök düğüm olarak adlandırılır. Bir dizindeki alt düğümler yaprak düğümler olarak adlandırılır ve yaprak düğümleri koleksiyonu yaprak düzeyidir.

Dizin tasarımı, sanat ve bilimin bir karışımıdır. Anahtarında birkaç sütun bulunan dar bir dizin, güncelleştirilmesi için daha az zaman gerektirir ve daha az bakım yüküne sahiptir; ancak, daha fazla sütun içeren daha geniş bir dizin kadar çok sorgu için yararlı olmayabilir. Uygulamanızın sorguları tarafından seçilen sütunlara göre birkaç dizin oluşturma yaklaşımı denemeniz gerekebilir. Sorgu iyileştiricisi genellikle sorgu için mevcut en iyi dizin olarak kabul edeceklerini seçer; ancak bu, oluşturulabilecek daha iyi bir dizin olmadığı anlamına gelmez.

Veritabanının düzgün dizine alınması karmaşık bir görev olabilir. Bir tablo için dizinlerinizi planlarken birkaç temel ilkeyi göz önünde bulundurmanız gerekir:

• Sistemin iş yüklerini anlama. Öncelikli olarak ekleme işlemleri için kullanılan tablolar, yüksek okuma etkinliğine sahip veri ambarı işlemleri için kullanılan tablolara kıyasla ek dizinlerden daha az fayda sağlar.
• Dizinleri en sık çalıştırılacak sorgular etrafında iyileştirin.
• Sorgularınızdaki sütunlar için uygun veri türlerini seçin. Dizinler en iyi tamsayı veri türleri, benzersiz veya null olmayan sütunlarla çalışır.
• Kısıt ve birleştirme ifadelerinde sıkça kullanılan sütunlarda küme dışı dizinler oluşturarak genel yükü en aza indirmek için bunları mümkün olduğunca dar tutun.
• Veri boyutunu/hacmini göz önünde bulundurun. Küçük tablolardaki tablo taramaları nispeten ucuzdur, büyük tablolardaki taramalar ise maliyetlidir.

SQL Server tarafından sağlanan bir diğer seçenek de filtrelenmiş dizinlerin oluşturulmasıdır. Filtrelenmiş dizinler, büyük tablolarda satırların önemli bir yüzdesinin bu sütunda aynı değeri paylaştığı sütunlar için idealdir. Aşağıdaki örnek, ayrılan veya kullanımdan kaldırılanlar da dahil olmak üzere tüm çalışanların kayıtlarını depolayan bir çalışan tablosudur.

CREATE TABLE [HumanResources].[Employee](
     [BusinessEntityID] [int] NOT NULL,
     [NationalIDNumber] [nvarchar](15) NOT NULL,
     [LoginID] [nvarchar](256) NOT NULL,
     [OrganizationNode] [hierarchyid] NULL,
     [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
     [JobTitle] [nvarchar](50) NOT NULL,
     [BirthDate] [date] NOT NULL,
     [MaritalStatus] [nchar](1) NOT NULL,
     [Gender] [nchar](1) NOT NULL,
     [HireDate] [date] NOT NULL,
     [SalariedFlag] [bit] NOT NULL,
     [VacationHours] [smallint] NOT NULL,
     [SickLeaveHours] [smallint] NOT NULL,
     [CurrentFlag] [bit] NOT NULL,
     [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
     [ModifiedDate] [datetime] NOT NULL)

Bu tabloda, bir çalışanın şu anda işe alınmış olup olmadığını gösteren adlı CurrentFlagbir sütun vardır. Bu örnekte iki değeri temsil eden veri türü kullanılır bit : biri şu anda çalışıyor, sıfırı ise şu anda çalışmıyor. WHERE CurrentFlag = 1 sütunundaki CurrentFlag ile filtrelenmiş bir dizin oluşturmak, mevcut çalışanların verimli bir şekilde sorgulanmasını sağlar.

Ayrıca, görünümler toplamalar ve/veya tablo birleştirmeleri gibi sorgu öğeleri içerdiğinde önemli performans kazanımları sağlayabilecek görünümlerde dizinler oluşturabilirsiniz.

Columnstore dizinleri

Columnstore dizinleri, büyük toplama iş yükleri içeren sorgular için gelişmiş performans sunar. Başlangıçta veri ambarlarına yönelik olan columnstore dizinleri, büyük tablolardaki sorgu performansı sorunlarını gidermek amacıyla diğer çeşitli iş yükleri için benimsenmiştir. B ağacı dizinleri gibi kümelenmiş columnstore dizini de tablonun kendisini özel bir şekilde depolarken, kümelenmemiş columnstore dizinleri tablodan bağımsız olarak depolanır. Kümelenmiş columnstore dizinleri doğal olarak tablodaki tüm sütunları içerir ancak sıralanmamıştır.

Kümelenmemiş columnstore dizinleri genellikle iki senaryoda kullanılır. İlki, bir sütunun veri türü sütun deposu dizininde desteklenmediğinde oluşur (örneğin XML, CLR, sql_variant, ntext, text ve image). Kümelenmiş columnstore dizini her zaman tablonun tüm sütunlarını içerdiğinden, tek seçenek kümelenmemiş dizindir. İkinci senaryo, karma işlem analizi işleme (HTAP) mimarilerinde kullanılan ve raporlar aynı anda çalıştırılırken verilerin tabloya yüklendiği filtrelenmiş dizinleri içerir. Dizini filtreleme (genellikle bir tarih alanında), verimli ekleme ve raporlama performansı sağlar.

Columnstore dizinleri her sütunu birbirinden bağımsız olarak depolar ve iki avantaj sunar: yalnızca gerekli sütunları tarayarak azaltılmış GÇ ve sütunlar içindeki benzer veriler nedeniyle daha fazla sıkıştırma. Veri ambarlarındaki olgu tabloları gibi büyük veri kümelerini taraan analiz sorgularında en iyi performansı gösterir. Tek değer aramaları için bir columnstore dizinini b ağacının kümelenmemiş diziniyle genişletebilirsiniz.

Bu dizinler ayrıca toplu yürütme modundan, satır kümelerini (genellikle 900 civarında) birer birer değil, toplu halde işleyerek yararlanmaktadır. Bu yaklaşım, CPU yönergelerini önemli ölçüde azaltır.

SELECT SUM(Sales) FROM SalesAmount;

Toplu iş modu, geleneksel satır işlemeye göre performans artışı sağlayabilir. Rowstore için toplu iş modu, columnstore diziniyle aynı okuma performansı düzeyine sahip olmasa da analiz sorguları en fazla 5 kat performans geliştirmesi görebilir.

Veri ambarı iş yükleri için columnstore dizinlerinin bir diğer avantajı da 102.400 veya daha fazla satırlık toplu ekleme işlemleri için iyileştirilmiş yük yoludur. 102.400 doğrudan columnstore'a yüklenecek en düşük değer olsa da, satır grubu olarak adlandırılan her satır koleksiyonu yaklaşık 1.024.000 satıra kadar olabilir. Daha az ama daha dolu olan satır grupları sorgularınızı SELECT daha verimli hale getirir çünkü istenen kayıtları almak için daha az satır grubunun taranmış olması gerekir. Bu yükler bellekte gerçekleşir ve doğrudan dizine yüklenir. Daha küçük birimler için veriler delta deposu olarak adlandırılan bir b ağacı yapısına yazılır ve zaman uyumsuz olarak dizine yüklenir.

Columnstore Dizin Yükleme Örneği

Bu örnekte, aynı veriler FactResellerSales_CCI_Demo ve FactResellerSales_Page_Demo olmak üzere iki tabloya yüklenmektedir. FactResellerSales_CCI_Demo kümelenmiş bir columnstore dizinine sahiptir ve FactResellerSales_Page_Demo iki sütunlu, sayfa sıkıştırılmış bir kümelenmiş b ağacı dizini içerir. Gördüğünüz gibi her tablo, FactResellerSalesXL_CCI tablosundan 1.024.000 satır yüklüyor. SET STATISTICS TIME ON olduğunda, SQL Server sorgu yürütme süresini izler. Verilerin columnstore tablosuna yüklenmesi yaklaşık 8 saniye sürdü ve sayfa sıkıştırılmış tabloya yükleme yaklaşık 20 saniye sürdü. Bu örnekte columnstore dizinine giden tüm satırlar tek bir satır grubuna yüklenir.

Tek bir işlemde columnstore dizinine 102.400'den az veri satırı yüklerseniz, delta deposu olarak bilinen bir b ağacı yapısına yüklenir. Veritabanı motoru, küme taşıyıcısı olarak adlandırılan zaman uyumsuz bir işlem kullanarak bu verileri kolon deposu dizinine taşır. Açık delta depolarının olması sorgularınızın performansını etkileyebilir çünkü bu kayıtları okumak columnstore'dan okumaktan daha az verimlidir. Ayrıca, delta depolarının columnstore dizinlerine eklenmesi ve sıkıştırılması için dizini COMPRESS_ALL_ROW_GROUPS seçeneğiyle yeniden düzenleyebilirsiniz.