SQL Dizinleri Açıklaması, Pt. 2

SQL Indexes Açıklaması'nın ilk dersinde, veri alımını hızlandırmak için sıralamayı kullanmayı öğrendik. Sorgu yürütmemiz, satırlar sıralandıktan sonra daha hızlı olsa da, sıralama, her satırı en az bir kez okuyup hareket ettirmeyi içerir. Bu, yöntemi tüm tabloyu sırayla okumaktan daha yavaş ve daha az verimli hale getirir.

Mantıklı sonuç, belirli bir tablonun - resmi olarak SQL indeksleri olarak adlandıracağımız, IX_ ile ön eklenmiş - sıralanmış kopyalarını korumamız gerektiği gibi görünüyor. İlk makaledeki alma algoritmaları daha sonra uygulanabilir olacak ve başlamadan önce tabloyu sıralamamız gerekmeyecek.

Tablonun Sıralanmış Kopyası Olarak Dizin

Yine Google E-Tablolar'ı kullanarak bu fikrin gerçek uygulamasına bir göz atalım. Rezervasyon elektronik tablomuz, aynı verileri içeren beş sayfadan oluşan bir koleksiyon haline gelir. Her sayfa farklı sütun kümelerine göre sıralanır.

Buradaki alıştırmaların, önceki SQL dizini öğretici makalesinden daha az titiz olması amaçlanmıştır; bunlar, zamanlayıcı ve satır sayısından ziyade hissederek yapılabilir. Bazı alıştırmalar çok benzer görünecek, ancak bu sefer keşfediyoruz:

1. Sıralanmış birincil tablolar yerine ayrı dizinler kullanılırken veriler nasıl daha verimli bir şekilde alınır?
2. Verileri değiştirirken her dizinde ve tabloda sıra nasıl korunur

Önceki öğretici okumalara odaklanmıştı, ancak birçok ortak gerçek dünya veri dinamiğinde (otel rezervasyonlarımız dahil), hem yeni veri eklemek hem de mevcut verileri güncellemek için dizin oluşturmanın yazma performansı üzerindeki etkilerini hesaba katmamız gerekiyor.

Ön Alıştırma: Bir Rezervasyonu İptal Edin

Sıralanmış tablo stratejisini kullanarak SQL dizin performansı hakkında bir fikir edinmek için göreviniz, 22 Ağustos 2020'den itibaren Otel 4'teki İstemci 12 için bir rezervasyonu silmektir. tablo ve doğru sıralamayı koruyun.

Tamamlandı? Tablonun birden çok sıralanmış kopyasını tutma fikrinin göründüğü kadar iyi olmadığı açık olmalıdır. Hala şüpheniz varsa, sildiğiniz rezervasyonu yeniden eklemeyi veya mevcut bir rezervasyonun tarihini değiştirmeyi de deneyebilirsiniz.

Tablonun sıralanmış kopyaları daha hızlı erişime izin verirken, şimdi öğrendiğimiz gibi, veri değişikliği bir kabus. Mevcut bir satırı eklememiz, silmemiz veya güncellememiz gerektiğinde, tablonun tüm kopyalarını almamız, bir satır ve/veya eklenmesi veya taşınması gereken yeri bulmamız ve son olarak veri bloklarını taşımamız gerekecek.

Satır Adreslerini Kullanan SQL İndeksleri

Bu elektronik tablo, farklı bir yaklaşım kullanan dizinler içerir. Dizin satırları hala belirli kriterlere göre sıralanır, ancak diğer tüm bilgileri dizin satırında tutmuyoruz. Bunun yerine, H sütununda yalnızca "satır adresini", Rezervasyonlar sayfasındaki satırın adresini (tablonun kendisini temsil eder) tutarız.

Tüm RDBMS uygulamaları, fiziksel bir adres kullanarak diskteki bloğu hızla bulmak için işletim sistemi düzeyinde bir yetenek kullanır. Satır adresleri tipik olarak bir blok adresi artı satırın blok içindeki konumundan oluşur.

Bu dizin tasarımının nasıl çalıştığını öğrenmek için birkaç alıştırma yapalım.

Alıştırma 1: Bir Müşterinin Tüm Rezervasyonları

İlk makalede olduğu gibi, aşağıdaki SQL sorgusunun yürütülmesini simüle edeceksiniz:

 SELECT * FROM Reservations WHERE ClientID = 12;

Yine, iki makul yaklaşım var. İlki, Rezervasyonlar tablosundaki tüm satırları okumak ve yalnızca kriterlere uyan satırları getirmektir:

 For each row from Reservations If Reservations.ClientID = 12 then write down Reservations.*

İkinci yaklaşım, IX_ClientID sayfasından veri okumayı ve ölçütlere uyan herhangi bir öğe için, RowAddress değerine dayalı olarak Rezervasyon tablosunda bir satır bulmayı içerir:

 Get first row from IX_ClientID where ClientID = 12 While IX_ClientID.ClientID = 12 Fetch Reservations.* where rowAddress = IX_ClientID.rowAddress Write down Reservations.* Get next row from IX_ClientID

Burada, Get first row from ifadesi, önceki makalede görülenlere benzer bir döngü tarafından uygulanır:

 Repeat Fetch next row from IX_ClientID Until ClientID >= 12

Bir satır bulana kadar aşağı kaydırarak veya satırAdresi sütunundaki bir filtreyi kullanarak belirli bir satırAdresi olan bir satırı bulabilirsiniz.

İade edilecek sadece bir avuç rezervasyon olsaydı, dizini kullanan yaklaşım daha iyi olurdu. Ancak, döndürülecek yüzlerce hatta bazen onlarca satırla, yalnızca Rezervasyonlar tablosunu doğrudan kullanmak daha hızlı olabilir.

Okuma hacmi, ClientID değerine bağlıdır. En büyük değer için dizinin tamamını okumanız gerekirken, en düşük değer için dizinin başında yer alır. Ortalama değer, satır sayısının yarısıdır.

Bu kısma daha sonra döneceğiz ve verimli bir çözüm sunacağız. Şimdilik kriterlerimize uyan ilk satırı bulduktan sonraki kısma odaklanalım.

Alıştırma 2: Belirli Bir Tarihte Başlayan Rezervasyon Sayısı

Görev, yeni dizin tasarımını kullanarak 16 Ağustos 2020'deki check-in sayısını saymaktır.

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16','YYYY-MM-DD');

Sayım için uygun dizini kullanma yaklaşımı, ilgili satır sayısı ne olursa olsun, bir tablo taramasından üstündür. Bunun nedeni, Rezervasyonlar tablosuna hiç erişmemiz gerekmemesidir—ihtiyacımız olan tüm bilgilere dizinin kendisinde sahibiz:

 Count := 0 Get first row from IX_DateFrom where DateFrom >= '2020-08-16' While found and DateFrom < '2020-08-17' Count := Count + 1 Get next row from IX_DateFrom Write down Count

Algoritma temelde sıralanmış tabloları kullananla aynıdır. Ancak, dizin satırı tablo satırından çok daha kısadır, bu nedenle RDBMS'mizin diskten daha az veri bloğu okuması gerekir.

Alıştırma 3: Suç Soruşturması (Verilen Otel ve Tarih Aralığı Konuk Listesi)

13 ve 14 Ağustos 2020 tarihlerinde Otel 3'e gelen konukların bir listesini hazırlayalım.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13','YYYY-MM-DD') AND TO_DATE('2020-08-14','YYYY-MM-DD') ) AND HotelID = 3;

Rezervasyonlar tablosundaki tüm satırları okuyabilir veya mevcut dizinlerden birini kullanabiliriz. Aynı alıştırmayı belirli kriterlere göre sıralanmış bir tablo ile yaptıktan sonra, IX_HotelID_DateFrom indeksinin en verimli olduğunu gördük.

 Get first row from IX_HotelID_DateFrom where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and DateFrom < '2020-08-15' and IX_HotelID_DateFrom.HotelID = 3 Fetch Reservations.* where rowAddress = IX_HotelID_DateFrom.rowAddress Write down Reservations.ClientID Get next row from IX_HotelID_DateFrom

Daha Verimli Bir İndeks Tasarlayabilir miyiz?

Tabloya, bildirdiğimiz konuk listesi için ihtiyacımız olan tek bilgi olan ClientID değeri nedeniyle erişiyoruz. Bu değeri SQL dizinine dahil edersek, tabloya hiç erişmemize gerek kalmaz. Yalnızca böyle bir dizinden okunan bir liste hazırlamaya çalışın, IX_HotelID_DateFrom_ClientID :

 Get first row from IX_HotelID_DateFrom_ClientID where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and HotelID = 3 and DateFrom < '2020-08-15' Write down ClientID Get next row from IX_HotelID_DateFrom_ClientID

İndeks, sorgu yürütme için gerekli tüm bilgileri içerdiğinde, indeksin sorguyu kapsadığını söylüyoruz.

Alıştırma 4: Kimlikler Yerine Konukların İsimlerinin Listesi

Bir suçu araştıran bir polis memuru için misafir kimliklerinin listesi işe yaramaz. İsim vermemiz gerekiyor:

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND r.HotelID = 3;

Bir liste sağlamak için Reservations tablosundaki verilerin yanı sıra, bu Google sayfasında bulunabilecek konuk bilgilerini içeren bir Clients tablosuna da ihtiyacımız var.

Bu alıştırma öncekine benzer ve yaklaşım da öyle.

 Get first row from IX_HotelID_DateFrom_ClientID where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and HotelID = 3 and DateFrom < '2020-08-15' Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Clients.ClientName Get next row from IX_HotelID_DateFrom_ClientID

Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID ifadesi, tablo taraması veya dizinimizi kullanarak uygulanabilir. Bir tablo taraması kullanırsak, While döngüsündeki her ClientID için, Clients tablosundaki satırların ortalama yarısını okumamız gerekir:

 -- Get row from Clients using table scan Repeat Fetch next row from Clients Until ClientID = IX_HotelID_DateFrom_ClientID.ClientID or not found If found Write down ClientName

Şimdiye kadar ele aldığımız dizin uygulaması—haydi buna “düz” dizin uygulaması diyelim—pek de yardımcı olmayacaktır. Dizinden aynı sayıda satırı (daha küçük satırlar olsa da) okumamız ve ardından RowAddress kullanarak Clients içindeki satıra atlamamız gerekir:

 -- Get row from Clients using flat index Repeat Fetch next row from Clients_PK_Flat Until ClientID >= IX_HotelID_DateFrom_ClientID.ClientID If found Fetch Clients.* where rowAddress = Clients_PK_Flat.rowAddress Write down ClientName

Not: Burada PK , serinin ilerleyen bölümlerinde inceleyeceğimiz bir terim olan "birincil anahtar" anlamına gelir.

Bu kadar çok satır okumak zorunda kalmadan bunu başarmanın bir yolu var mı? Evet—B-ağacı indeksleri tam olarak bunun içindir.

Dengeli Ağaç (B-ağacı) İndeksleri

Clients_PK_Flat satırları dört satırlı bloğa bölelim ve blok başlangıcının bloğundan ve adresinden son ClientID değerini içeren bir liste oluşturalım (sütun IndexRowAddress ). Ortaya çıkan veritabanı dizini veri yapısı—Clients_PK_2Levels sayfasında bulabilirsiniz. Yeni yapının ClientID 28 olan bir istemci bulmanıza nasıl yardımcı olduğunu deneyin. Algoritma şöyle görünmelidir:

 Fetch Level2.* Loop Leaf_address := Level3Address Exit when ClientID >= 28 Fetch next row from Level2 Fetch Level3.* where Level3Address = Leaf_address -- 3-21 Loop Client_address := RowAddress Exit when ClientID >= 28 Fetch next row from Level 3 Fetch Clients.* where rowAddress = Client_address -- 42 Write down Clients.*

Muhtemelen başka bir seviye ekleyebileceğimizi anladınız. Seviye 1, IX_Clients_PK sekmesinde görebileceğiniz gibi dört satırdan oluşur. Müşteri Kimliği 28 olan misafirin adını bulmak için, birincil anahtar yapısından her düzey için bir tane olmak üzere üç veri bloğu (düğüm) okumanız ve son olarak 42 adresli Müşteriler satırına atlamanız gerekir.

Bu SQL indeksinin yapısına dengeli ağaç denir. Kök düğümden her yaprak düzeyi düğüme giden yol aynı uzunluğa sahip olduğunda, B-ağacı derinliği olarak adlandırılan ağaç dengelenir. Bizim durumumuzda derinlik üçtür.

Şu andan itibaren, elektronik tablolarımız yalnızca yaprak düzeyinde girişler içermesine rağmen, her bir dizinin B-ağacı yapısına sahip olduğunu kabul edeceğiz. B ağacı hakkında bilinmesi gereken en önemli gerçekler şunlardır:

• B-ağacı dizin yapısı, piyasadaki tüm büyük RDBMS'ler tarafından en yaygın olarak kullanılan dizindir.
• Dengeli bir ağacın tüm seviyeleri, anahtar sütun değerlerine göre sıralanır.
• Veriler diskten bloklar halinde okunur.
• Bir B-ağacı düğümü, bir veya daha fazla blok içerir.
• Sorgu performansını etkileyen en önemli faktör diskten okunan blok sayısıdır.
• Kökten başlayarak yaprak düzeyinde biten her yeni B ağacı düzeyindeki öğelerin sayısı katlanarak artar.

Alıştırma 5: Suç Soruşturması, Kısım II

Şimdi, polis müfettişi A kentindeki tüm otellerden ilgili konukların adlarının, varış tarihlerinin ve otel adlarının bir listesini arıyor.

 SELECT h.HotelName, r.DateFrom as CheckInDate, c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID JOIN Hotels h ON r.HotelID = h.HotelID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND h.City = 'A';

Yaklaşım 1

IX_DateFrom_HotelID_ClientID dizinini kullanırsak, tarih aralığındaki her satır için Hotels tablosuna erişmemiz ve otelin A şehrinden olup olmadığını kontrol etmemiz gerekir. Eğer öyleyse, Müşteriler tablosuna da erişmemiz gerekir. müşterinin adını okuyun.

 For each row from IX_DateFrom_HotelID_ClientID where DateFrom between '2020-08-13' and '2020-08-14' For each row from Hotels where HotelID = IX_DateFrom_HotelID_ClientID.HotelID If Hotels.City = 'A' then Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Hotels.HotelName, IX_HotelID_DateFrom_ClientID.DateFrom, Clients.ClientName

Yaklaşım 2

IX_HotelID_DateFrom_ClientID kullanmak bize daha verimli bir yürütme planı sağlar.

 For each row from Hotels where City = 'A' For each row from IX_HotelID_DateFrom_ClientID where HotelID = Hotels.HotelID and DateFrom between '2020-08-13' and '2020-08-14' Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Hotels.HotelName, IX_HotelID_DateFrom_ClientID.DateFrom, Clients.ClientName

Hotels tablosundan A kentindeki tüm otelleri buluyoruz. Bu otellerin kimliğini bilerek, IX_HotelID_DateFrom_ClientID dizininden sonraki öğeleri okuyabiliyoruz. Böylece her otel ve tarih için B ağacı yaprak seviyesinde ilk sırayı bulduktan sonra A şehri dışındaki otellerden gelen rezervasyonları okumuyoruz.

Burada, hedeflerimize uygun bir veritabanı dizinimiz olduğunda, ek bir birleştirmenin aslında bir sorguyu daha hızlı hale getirebileceğini görebiliriz.

B-ağacı yapısı ve bir satır eklendiğinde, güncellendiğinde veya silindiğinde nasıl güncellendiği, bölümleme motivasyonunu ve etkisini açıkladığımda daha ayrıntılı olarak ele alınacaktır. Mesele şu ki, bir indeks kullandığımızda bu işlemi hızlı bir şekilde düşünebiliriz.

SQL'de Dizin Sorgusu: Ayrıntılar Fark Yaratır

Dizinler ve veritabanları söz konusu olduğunda, SQL dili düzeyinde çalışmak, uygulama ayrıntılarını bir dereceye kadar gizler. Bu alıştırmalar, farklı SQL dizinlerini kullanırken yürütme planlarının nasıl çalıştığına dair bir fikir edinmenize yardımcı olmayı amaçlamaktadır. Makaleyi okuduktan sonra, bir sorguyu olabildiğince hızlı ve verimli hale getirecek mevcut dizinler ve tasarım dizinleri verilen en iyi yürütme planını tahmin edebileceğinizi umuyorum.

Bu dizinin bir sonraki bölümünde, SQL'de dizinlerin kullanımında en yaygın en iyi uygulamaları ve anti-kalıpları araştırmak ve anlamak için yeni edinilen becerileri kullanacak ve genişleteceğiz. Bir sonraki bölümde ele almak istediğim iyi ve en iyi uygulamaların bir listesi var, ancak sonraki makaleyi ihtiyaçlarınız ve deneyiminizle daha alakalı hale getirmek için, lütfen yanıtlanmasını istediğiniz kendi sorularınızı göndermekten çekinmeyin.

SQL Indexes Açıklaması'nın son bölümünde, tablo ve dizin bölümleme, onu kullanmak için doğru ve yanlış motivasyonlar ve bunun sorgu performansı ve veritabanı bakımı üzerindeki etkisi hakkında da bilgi edineceğiz.