Kurs
SQL ile Veri İşleme
4 sa
337.1K
Bir veri bilimci olarak sıklıkla yığınla veriyi yönetmek zorunda kalacaksınız. Büyük miktardaki veriyle uğraşmak hiç de kolay değildir. Bu nedenle, veriyi mümkün olduğunca verimli şekilde işleyebilmek için, verinin fiziksel olarak nasıl düzenlendiğini net biçimde anlamanız ve pratik tekniklerle işleyebilmeniz gerekir.
Çoğu yazılım bir tür veriye dayanır ve bir İTVYS (İlişkisel Veritabanı Yönetim Sistemi) ile iyi entegre olduğundan, SQL modern bir yazılım mühendisi için olmazsa olmaz bir beceridir. Bir web uygulaması, bir API ya da kurum içi bir uygulama olsun, İTVYS her zaman oradadır. SQL ise bir İTVYS üzerinde sorgulama yapmak için kullanılan dildir.
Bir veri bilimci olarak SQL ve ilgili teknikleri bilmek hayati önem taşır. Ele aldığınız veriye ilişkin belirli sorularınıza yanıt alabilmek için bir İTVYS’de sorgulama yapabilmeniz gerekir; SQL bu asgari ihtiyacı karşılar.
DataCamp ile çektiği son videoda, David Robinson (Chief Data Scientist @ DataCamp) bir Veri Bilimi problemi üzerinde SQL’i nasıl kullandığını gösterdi. Göz atmanızı öneririz. İş akışı oldukça ilgi çekici.
Bugün, öncelikle veritabanındaki verinin düzenlenmesiyle ilgili olan ve SQL kullanarak bazılarını uygulayacağınız İndeksleme adlı bir tekniği öğreneceksiniz. Bu, indekslemenin veritabanı içinde bilgiyi depolamak için nasıl kullanılabileceğine ve daha hızlı yürütme süreleri sağlayabileceğine dair bir genel bakış sunacaktır.
Not: Bu eğitime başlamadan önce, SQL’in temellerine aşina değilseniz bunları öğrenmeniz şiddetle tavsiye edilir. SQL temellerinizi tazelemek isterseniz DataCamp’in Veri Bilimi için SQL’e Giriş kursu mükemmel bir kaynaktır.
Bir Dosyada Kayıtların Düzenlenmesine Kısa Bir Not
İndeksleme hakkında bir şeyler çalışmaya başlamadan önce, verinin dosyaların içinde fiziksel olarak nasıl düzenlendiğini anlamanız şarttır. Elbette tüm ayrıntılara inmeyeceksiniz; ancak düzenlemeye dair iyi bir genel bakışa sahip olmak, indekslemeyi çok daha net anlamanıza yardımcı olur. Hadi başlayalım.
Genel olarak kayıtların/verinin düzenlenmesi, kayıtların nasıl saklandığıyla ilgilidir ve kabaca iki türe ayrılabilir:
-
Sıralı düzen: Bir dosyadaki tüm kayıtlar belirli bir arama anahtarı değerine göre sıralanır. Sıralı bir dosyanın içinde bir değeri aramak için genellikle İkili arama kullanılır. Bu, aramayı oldukça verimli hale getirir; çünkü arama zaman karmaşıklığı logaritmik zaman içinde kalır. Ancak dosyaya ekleme pahalı bir işlem haline gelir; zira yeni öğeye yer açmak için tüm dosyayı yeniden düzenlemeniz gerekebilir.
-
Sırasız düzen: Bu türde, kayıtlar dosyada yer olan herhangi bir konuma, genellikle dosyanın en sonuna eklenir. Doğrusal arama kullanıldığı için arama, önceki türe göre verimli değildir; ancak ekleme pahalı bir işlem değildir.
Peki dosyalar sıralı olsa bile, dosyaların blok boyutlarıyla içlerindeki her bir kaydın boyutu çok büyükse ne olur? Bunu bir sonraki bölümde inceleyelim.
İndekslemeye Duyulan İhtiyaç
Bu bölümde, dosyaları/bilgiyi verimli şekilde saklamak amacıyla indeksleme kullanmanın ardındaki motivasyonu ve bunun bu dosya/bilgiyle ilgili diğer işlemleri nasıl geliştirdiğini öğreneceksiniz.
Veritabanında birçok kayıttan oluşan bir tablonuz (ilişki) olduğunu düşünün. Kayıtlar ayrıca 1000 bloğa bölünmüştür. Görsel olarak düzen şu şekildedir:
Yukarıdaki düzenden şu noktalar açıktır:
- Düzen, kayıtların ilk sütununa uygulanmıştır (verinin tablolar halinde düzenlendiği bir ilişkisel veritabanı sistemi olduğunu varsayın)
- Toplam verinin bölündüğü blok sayısı: 1000 blok.
Unutmayın, her kayıt başka sütunlar da içerir; ancak bu düzende sıralama ilk sütuna uygulanmış ve bloklar buna göre bölünmüştür. Şimdi, bu düzen içinde ya da kayıtlarda bir şey aramak için ikili arama yaparsanız, toplam süre $\log_{2}1000$ olur ki bu da 10 zaman birimine eşittir. Bu arama süresi iyileştirilebilir mi?
Elbette, evet.
Bunu bir kitap okuma deneyimi ve dizin sayfasının olmaması şeklinde düşünün. Rastgele bir sayfa açarsınız (ya da ikili aramada kitabın ortasındaki sayfayı açarsınız) ve istediğiniz sayfaya ulaşmak için sayfaları sola ve sağa çevirirsiniz. Bu kesinlikle zaman alır. Öyle değil mi?
Oysa kitapta bir dizin sayfası olsaydı arama çok daha verimli olurdu. Dizin sayfasına bakarak doğrudan ilgili sayfaya gidebilirdiniz. Yukarıdaki duruma da aynı şekilde indeksleme uygulayabilirsiniz.
Burada indekslemeyi nasıl uygulayabileceğinize dair bir özet:
- Önceki düzende kullanılan sıralı değerlere ek olarak, her blok için bir blok işaretçisi (pointer) tutun.
Bu, veri dosyalarını saklamak için kullanılan blok sayısının azalmasıyla sonuçlanacaktır. Artık belirli bir kaydı aramak için, daha az sayıda bloktan oluşan bu yeni düzen içinde arama yapmanız yeterli olacak ve sonunda aradığınız kayda (varsa) çok daha kısa sürede ulaşacaksınız. Bunu görselleştirelim. Yeni indeksli kayıtların sayısının 8 bloğa düştüğünü varsayarsak, aşağıdaki gibi bir düzen elde edersiniz:
Blok sayısı azaldığı için arama süresi ciddi ölçüde düşecektir. Diyelim ki 90 kaydını aramak istiyorsunuz. Bu yeni indeksli şemada, önce ilgili blok işaretçisini bulmanız gerekir; bu örnekte 3’tür. Ardından bu bilgiyle asıl veri kayıtlarını tek seferde bulursunuz.
Dolayısıyla bu durumda arama süresi $\log_{2}8 + 1$ = 4 zaman birimidir ve önceki duruma kıyasla kayda değer biçimde daha kısadır.
Yukarıdaki örnek, indeksleme yapma ihtiyacını tanıtmaktadır. İndekslemeyle ilgili dikkate değer bazı noktalar şunlardır:
- Orijinal veri kayıtlarında sıralama yalnızca tek bir alan kullanılarak yapılabilir. Ardından alan girdileri buna göre indekslenebilir. Yalnızca o alanı kullanarak arama yapıyorsanız, arama süresi iyileşir. (Son derece önemli)
- İndeksler de sıralıdır.
- İndeks kaydı iki alandan oluşur (indeks dosyasının yapısı):
- Orijinal dosyanın anahtarı
- Anahtarın orijinal veri kayıtlarında bulunduğu bloğa işaretçi
- İndekslerde arama yapmak için ikili arama kullanılır.
- İndeksli girdiler kullanılarak bir kayda erişmek için gereken ortalama blok erişimi sayısı:
$\log_{2}B_i + 1$ , burada $B_i$ indeksli kayıtlardaki blok sayısıdır - Bir ilişkideki herhangi bir alan üzerinde indeks oluşturulabilir (birincil anahtar, aday anahtarlar, anahtar olmayan alanlar).
Şimdi, orijinal veri kayıtlarının sıralamasına ve indeks dosyasında kaç kayıt tutacağınıza bağlı olarak birçok indeksleme şeması olabilir. Bir sonraki bölümde en popüler olanları inceleyeceksiniz.
Farklı İndeksleme Stratejileri:
- Sık (Dense) İndeksleme: Her arama anahtarı değeri için bir indeks girdisi oluşturuluyorsa buna sık indeksleme denir. Bunu görsel olarak anlamak için aşağıdaki diyagramı inceleyin.
- Seyrek (Sparse) İndeksleme: Yalnızca bazı kayıtlar için bir indeks girdisi oluşturuluyorsa buna seyrek indeksleme denir. Bunu görsel olarak açıklayan bir diyagram aşağıdadır.
Yukarıdaki diyagramlar her iki indeksleme şemasını da oldukça anlaşılır kılar. Burada dikkat edilmesi gereken kritik bir nokta, seyrek indeksleme örneğinin hem sık hem de seyrek indekslemenin bir kombinasyonu olduğudur. Çünkü her benzersiz arama anahtarı değeri (1, 2 ve 3) için bir indeks vardır; ancak her veri kaydı için bir indeks yoktur.
Şimdi, kayıtların seviyesine göre diğer indeksleme türlerini inceleyeceksiniz. Tek seviyeli indekslemede, indeks dosyasının sayısı yalnızca birdir. Ancak bazen indeks dosyası o kadar büyür ki indeks dosyasının kendisi de indekslenir. Bu durumda buna çok seviyeli indeksleme denir. Daha derine inelim.
Seviyelendirmeye göre yapılan diğer indeksleme stratejilerine genel bir bakış:
Tek seviyeli indeksleme:
- Birincil (Primary) indeksleme
- Kümelenmiş (Clustered) indeksleme
- İkincil (Secondary) indeksleme
Çok seviyeli indeksleme:
- B Ağacı
- B+ Ağacı
Tek seviyeli indekslemeye ait tüm indeksleme stratejilerini inceleyeceksiniz. Eğitimin sonunda, ilgilenmeniz halinde çok seviyeli indeksleme şemalarını keşfetmeniz için bir bağlantı bulacaksınız. Şimdi Birincil İndekslemeyi ele alalım.
Birincil indeksleme:
Birincil indeks, kayıtları sabit uzunlukta olan ve iki alandan oluşan sıralı bir dosyadır:
- İlk alan, veri dosyasının birincil anahtarıyla aynıdır.
- İkinci alan, birincil anahtarın bulunduğu veri bloğuna bir işaretçidir. - Kaynak
Her bloğun ilk kaydı için oluşturulan indekslere blok çıpaları denir. Birincil indekslemede indeks girdilerinin sayısı = orijinal veri bloklarının sayısıdır. Birincil indeks kullanılarak ortalama blok erişimi sayısı:
Daha net anlamak için aşağıdaki diyagrama bakın: 
Sağdaki şekil, orijinal veri kayıtlarının birkaç bloğa bölündüğünü gösterir. 1,2,3,...,9 sayılarını içeren sütunun birincil anahtarlar olduğuna dikkat edin. Soldaki şekil ise her bir girdinin şunlardan oluştuğu indeksli girdileri gösterir:
- Her veri bloğunun ilk girdisi (blok çıpası)
- İkinci girdi ise blok işaretçisini gösterir.
Bir an durup bunun hangi tür indeksleme olduğunu düşünün (Seyrek mi Sık mı). Yanıtlarınızı göndermek için yorumlar bölümünü kullanın.
Şimdi Kümelenmiş İndekslemeyi inceleyeceksiniz.
Kümelenmiş indeksleme:
Kümelenmiş indeks, dosya kayıtları her kayıt için farklı bir değere sahip olmayan anahtar olmayan bir alana göre fiziksel olarak sıralanmış olan veri dosyası üzerinde oluşturulur. İndekslemenin yapıldığı bu alana kümelenme alanı denir. Bu nedenle adı kümelenmiş indekstir.
Diyagramlar her zaman anlamayı kolaylaştırır. Gördüğünüz gibi, orijinal veri kayıtları anahtar olmayan bir özniteliğe göre sıralanmıştır ve bu anahtar olmayan özniteliğin her bir farklı değeri için bir indeks girdisi oluşturulmuştur. Bu şemayı kullanarak belirli bir kaydı bulmak için gereken ortalama blok erişimi sayısı $\geq$ $\log_{2}B_i + 1$’dir; burada $B_i$ indeksli kayıtlardaki blok sayısıdır. Buradaki $\geq$ işaretine dikkat edin.
Kümelenmiş indekste, belirli bir anahtarın bulunduğu bloğu bulduktan sonra, diğer blokları da dolaşmanız gerekebilir (yukarıdaki şekilden de açıkça görüldüğü gibi).
Bunun hangi tür indeksleme olduğunu düşünebilirsiniz (Seyrek mi Sık mı). Yanıtlarınızı göndermek için yorumlar bölümünü kullanın. Şimdi İkincil İndekslemeyi inceleyelim.
İkincil indeksleme:
Veritabanınızda Employee adında bir tablonuz olduğunu varsayın. Tabloda şu öznitelikler vardır:
- employee_id
- employee_name
- employee_department
- employee_salary
employee_id birincil anahtardır. Bu tablo üzerinde employee_id’ye dayalı birincil indekslemeyi zaten yaptınız. Ancak bir uygulama geliştirirken veritabanı sorgularının çoğunun employee_name özniteliğini kullandığını fark ettiniz. Bu durumda birincil indeksleme pek yardımcı olmayacak ve employee_name değerlerinin tümü için ayrı bir indeksleme tutmak iyi bir uygulama olacaktır. Zaten çalışan adları veritabanında sıralı şekilde bulunmayacaktır. Bu nedenle onları indekslemek, çalışan adlarını içeren sorguları kesinlikle hızlandıracaktır.
Bu, ikincil indekslemeye klasik bir örnektir. İkincil indeksleme için uygun bir görsel de oluşturmaya çalışalım:
Daha iyi bir gösteriminiz varsa lütfen yorumlar bölümünde paylaşmaktan çekinmeyin.
Şimdi PostgreSQL’de nasıl indeks oluşturabileceğinizi göreceksiniz. Temelleri öğrenmek isterseniz DataCamp’in PostgreSQL’de Verileri Birleştirme kursunu alın.
PostgreSQL’de İndeks Oluşturma
PostgreSQL veritabanında indeks oluşturmaya başlamadan önce, bir tabloda bazı verilerin bulunması gerekir. Aşağıdaki kayıtlardan oluşan Student adlı basit bir tablo oluşturalım:
- student_id
- student_name
- student_year
student_id alanını birincil anahtar yapacak ve öğrenci adlarının ve öğrenci yıllarının boş olmasına izin vermeyeceksiniz.
Sorgu şu şekilde olacaktır:
CREATE TABLE STUDENT(
student_id TEXT PRIMARY KEY,
student_name TEXT NOT NULL,
student_year TEXT NOT NULL
);
Tablo başarıyla oluşturulduktan sonra, tabloya uygun bazı veriler eklemeniz gerekir. Kolaylık olması için bu .csv dosyasını kullanıp Student tablosuna içe aktarabilirsiniz. Uyumluluk gösteren bir .csv dosyasını PostgreSQL tablosuna aşağıdaki sorguyla içe aktarabilirsiniz:
COPY STUDENT FROM '/path/to/csv/Student.csv' WITH (FORMAT csv);
Yukarıdaki sorgu, verilerin kopyalandığı .csv dosyasının adının Student olduğunu varsayar.
Şimdi verilere bakalım. select * from STUDENT; komutunu çalıştırmak aşağıdaki kayıtları döndürür: 
select sorgusu toplam 86 kayıt döndürmelidir. Artık indeks oluşturmaya hazırsınız. Tek sütunlu indeksleri aşağıdaki sözdizimiyle oluşturabilirsiniz:
CREATE INDEX index_name
ON table_name (column_name);
student_id alanı üzerinde bir indeks oluşturalım (birincil indeksleme).
CREATE INDEX id_index
ON STUDENT (student_id);
Çok sütunlu indeksler de oluşturulabilir:
CREATE INDEX id_index
ON STUDENT (student_id,student_name);
Bir indeksi aşağıdaki sözdizimiyle kaldırabilirsiniz:
DROP INDEX index_name;
STUDENT gibi küçük bir tabloda indekslemenin ne yaptığını hissetmek zordur. Ancak tablo büyük olsaydı (büyük bir üniversitenin benzer kayıtları içeren bir veritabanı tablosunu düşünün), indeksleme şüphesiz hayati bir rol oynardı.
Başardınız!
Sonuna kadar geldiğiniz için tebrikler. Bu eğitimde indekslemeyi, nedenlerini ve farklı indeksleme şemalarını öğrendiniz. Ayrıca PostgreSQL’de basit indekslemeyi nasıl yapabileceğinizi gördünüz.
Ancak bu eğitimde çok seviyeli indekslemeyi incelemediniz. Bunları keşfetmekle ilgileniyorsanız aşağıdakiler harika kaynaklardır:
Peki indeksleme kullanmak her zaman fayda sağlar mı? Bazı durumlarda indeksleme kullanmak önerilmez.
Umarım bu eğitim, indekslemenin temellerini netleştirmenize yardımcı olmuştur. İlginç bulgularınızı yorumlar bölümünde paylaşın.
DataCamp’in SQL Öğrenme Merkezine göz atın.
Konular
