SQL Dizinleri Açıklaması, Pt. 1

Doğru kullanıldığında, bir SQL veritabanı dizini o kadar etkili olabilir ki sihirli gibi görünebilir. Ancak aşağıdaki alıştırmalar, çoğu SQL indeksinin mantığının – ve onları doğru şekilde kullanmanın – altında oldukça basit olduğunu gösterecektir.

SQL Dizinleri Açıklaması adlı bu dizide, verilere erişmek için dizinleri kullanma ve tüm modern RDBMS'ler tarafından yapıldığı şekilde dizinler tasarlama motivasyonlarını inceleyeceğiz. Ardından, belirli sorgu kalıpları için veri döndürmek için kullanılan algoritmalara bakacağız.

SQL Indexes Açıklamasını takip edebilmek için indeksler hakkında çok fazla bilgi sahibi olmanıza gerek yok. Sadece iki ön koşul var:

• Temel SQL bilgisi
• Herhangi bir programlama dilinin temel bilgisi

SQL Indexes Açıklamasının gireceği ana konular şunlardır:

• SQL veritabanı dizinlerine neden ihtiyacımız var; dizinleri kullanarak yürütme planlarını görselleştirme
• Dizin tasarımı: hangi dizinler bir sorguyu hızlı ve verimli hale getirir
• İndeksleri etkin bir şekilde kullanmak için nasıl sorgu yazabiliriz?
• SQL'de dizin kullanımının okuma/yazma verimliliğine etkisi
• Kapsama indeksleri
• Bölümleme, okuma ve yazma üzerindeki etkisi ve ne zaman kullanılacağı

Bu sadece bir SQL indeks öğreticisi değil, indekslerin altında yatan mekanikleri anlamaya yönelik derin bir dalış.

Alıştırmalar yaparak ve problem çözme yöntemlerimizi analiz ederek bir RDBMS'nin indeksleri nasıl kullandığını anlayacağız. Alıştırma materyalimiz salt okunur Google E-Tablolardan oluşur. Bir alıştırma yapmak için Google E-Tablosunu kopyalayabilir ( Dosya → Kopyasını oluştur ) veya içeriğini kendi Google E-Tablonuza kopyalayabilirsiniz.

Her alıştırmada, Oracle sözdizimini kullanan bir SQL sorgusu göstereceğiz. Tarihler için ISO 8601 biçimini kullanacağız, YYYY-MM-DD .

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

İlk görev - henüz yapmayın - bir otel rezervasyon sisteminin belirli bir müşterisi için Rezervasyon elektronik tablosundaki tüm satırları bulmak ve aşağıdaki sorgunun yürütülmesini simüle ederek bunları kendi elektronik tablonuza kopyalamaktır:

 SELECT * FROM Reservations WHERE ClientID = 12;

Ama biz belirli bir yöntemi takip etmek istiyoruz.

Yaklaşım 1: Sıralama Yok, Filtreleme Yok

İlk deneme için herhangi bir sıralama veya filtreleme özelliği kullanmayın. Lütfen harcanan zamanı kaydedin. Ortaya çıkan sayfa 73 satır içermelidir.

Bu sözde kod, görevi sıralamadan gerçekleştirme algoritmasını gösterir:

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

Bu durumda, koşulu sağlayan 73 satırı döndürmek ve kopyalamak için 841 satırın tümünü kontrol etmemiz gerekiyordu.

Yaklaşım 2: Yalnızca Sıralama

İkinci deneme için, sayfayı ClientID sütununun değerine göre sıralayın. Filtreler kullanmayın. Zamanı kaydedin ve verileri sıralamadan görevi tamamlamak için geçen zamanla karşılaştırın.

Sıralamadan sonra, yaklaşım şöyle görünür:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

Bu sefer “sadece” 780 satırı kontrol etmek zorunda kaldık. Bir şekilde ilk sıraya geçebilseydik, daha da az zaman alırdı.

Ancak görev için bir program geliştirmemiz gerekirse, bu çözüm ilkinden bile daha yavaş olacaktır. Bunun nedeni, önce tüm verileri sıralamamız gerekeceğinden, bu da her satıra en az bir kez erişilmesi gerektiği anlamına gelir. Bu yaklaşım, yalnızca sayfa zaten istenen sırada sıralanmışsa iyidir.

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

Şimdi görev, 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')

Alıştırma 1'deki elektronik tabloyu kullanın. Sıralama yaparak ve ayırmadan görevi tamamlamak için harcanan süreyi ölçün ve karşılaştırın. Doğru sayı 91'dir.

Sıralamasız yaklaşım için, algoritma temelde Alıştırma 1'dekiyle aynıdır.

Sıralama yaklaşımı da önceki alıştırmadakine benzer. Döngüyü iki parçaya ayıracağız:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

Alıştırma 3: Suç Soruşturması

Polis müfettişi, 13 ve 14 Ağustos 2020 tarihlerinde otele gelen konukların listesini görmek istiyor.

 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;

Yaklaşım 1: Yalnızca Tarihe Göre Sıralama

Müfettiş listeyi bir an önce istiyor. Tabloyu/e-tabloyu varış tarihine göre sıralamamızın daha iyi olacağını zaten biliyoruz. Alıştırma 2'yi yeni bitirdiysek, tablo zaten sıralanmış olduğu için şanslıyız. Böylece, Alıştırma 2'dekine benzer bir yaklaşım uyguluyoruz.

Lütfen zamanı, okumanız gereken satır sayısını ve listedeki öğelerin sayısını kaydetmeye çalışın.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

Bu yaklaşımı kullanarak, 46 kişilik bir liste oluşturmak için 511 satır okumamız gerekti. Tam olarak aşağı kayabilseydik, 13 Ağustos'taki ilk varış yerini bulmak için tekrar döngüsünden 324 okuma yapmak zorunda değildik. Ancak, konuğun otele HotelID 3 ile gelip gelmediğini kontrol etmek için yine de 100'den fazla satır okumak zorunda kaldık.

Müfettiş bunca zaman bekledi ama mutlu olmayacaktı: Konukların adları ve diğer ilgili veriler yerine, yalnızca anlamsız kimliklerin bir listesini teslim ettik.

Serinin ilerleyen bölümlerinde bu konuya geri döneceğiz. Önce listeyi daha hızlı hazırlamanın bir yolunu bulalım.

Yaklaşım 2: Otele Göre Sıralama, Ardından Tarih

Satırları HotelID ve ardından DateFrom göre sıralamak için tüm sütunları seçebilir, ardından Google E-Tablolar menü seçeneğini Veri → Sıralama aralığını kullanabiliriz.

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

İlkini otelimize yerleştirmeden önce ilk 338 varışını atlamak zorunda kaldık. Ondan sonra, 13 Ağustos'ta ilk varış yerini bulmak için 103'ün üzerinde erken varış yaptık. Son olarak, 46 ardışık ClientID değerini kopyaladık. Üçüncü adımda, bir ardışık kimlik bloğunu kopyalayabilmemiz bize yardımcı oldu. Ne yazık ki o bloktan bir şekilde ilk sıraya atlayamadık.

Yaklaşım 3: Yalnızca Otele Göre Sıralama

Şimdi aynı alıştırmayı yalnızca HotelID tarafından sipariş edilen elektronik tabloyu kullanarak deneyin.

Yalnızca HotelID tarafından sıralanan tabloya uygulanan algoritma, HotelID ve DateFrom (bu sırayla) göre sıralama yaptığımızdan daha az verimlidir:

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

Bu durumda, HotelID 3 olan otele 166 varışın tümünü okumamız ve her biri için DateFrom istenen aralığa ait olup olmadığını kontrol etmemiz gerekir.

Yaklaşım 4: Tarihe Göre Sıralama, Ardından Otel

Önce HotelID ve ardından DateFrom göre mi yoksa tam tersi şekilde mi sıraladığımız gerçekten önemli mi? Bulalım: Önce DateFrom göre, ardından HotelID göre sıralamayı deneyin.

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

İlgili tarihin bulunduğu ilk satırı bulduk, ardından otele ilk varışı bulana kadar devamını okuduk. Bundan sonra, birkaç satır için her iki koşul da yerine getirildi, doğru tarih ve doğru otel. Ancak, Otel 3'e vardıktan sonra, aynı tarih için 4, 5 ve benzeri otellere varışlarımız oldu. Onlardan sonra, ilgilendiğimiz otelimize art arda gelenleri okuyabilene kadar ertesi gün 1 ve 2 numaralı oteller için satırları tekrar okumak zorunda kaldık.

Gördüğümüz gibi, tüm yaklaşımlar, tam satır kümesinin ortasında, kısmen eşleşen verileri temsil eden tek bir ardışık veri bloğuna sahiptir. Yaklaşımlar 2 ve 4, kısmi eşleşmelerin sonuna ulaşmadan önce mantığın algoritmayı tamamen durdurmamıza izin verdiği tek yaklaşımlardır.

Yaklaşım 4, iki blokta tam olarak eşleşen verilere sahiptir, ancak Yaklaşım 2, hedeflenen verilerin tümünün bir ardışık blokta olduğu tek yaklaşımdır.

Rakamlarla, bu durumda Yaklaşım 2'nin en fazla avantaja sahip olduğu açıktır.

SQL Dizinleri Açıklandı: Sonuçlar ve Sırada Ne Var

Bu alıştırmaları yapmak aşağıdaki noktaları netleştirmelidir:

1. Düzgün sıralanmış bir tablodan okumak daha hızlıdır.
2. Bir tablo zaten sıralanmamışsa, sıralama, sıralanmamış bir tablodan okumaktan daha uzun sürer.
3. Sıralanan tabloda bir arama koşuluyla eşleşen ilk satıra atlamanın bir yolunu bulmak, çok sayıda okuma tasarrufu sağlar.
4. Önceden sıralanmış bir tabloya sahip olmak faydalı olacaktır.
5. En sık sorgular için tablonun sıralanmış kopyalarını korumak yardımcı olacaktır.

Şimdi, bir tablonun sıralanmış bir kopyası neredeyse bir veritabanı indeksi gibi geliyor. SQL Dizinleri Açıklaması'ndaki sonraki makale, temel bir dizin uygulamasını kapsar. Okuduğunuz için teşekkürler!