Indeks Nedir ?
Indeks, veri sorgulamayı hızlandırmak için veritabanının arka planında kullanılan, bir tablodaki verilerin bellekteki yerini işaret eden güçlü bir yapıdır. Talep edilen verileri hızlı bir şekilde arayabilmemize olanak tanır.
Veritabanındaki indeks, bir kitabın giriş kısmındaki ‘İçindekiler’ listesine çok benzer bir yapıdadır.
Binlerce kitabın bulunduğu bir kütüphanede bir kitap aradığımızı ve bunu 1 dk içinde bulmamız gerektiğini düşünelim, bu mümkün olur muydu? Fakat elimizde kitabın adının ve bulunduğu rafın numara bilgisinin olduğu bir belge olsaydı konumunu kolayca bulabilirdik.
İşte büyük verilerle çalışan büyük şirketlerde de bu belgeye ihtiyaç duyulur. Çünkü çağımızda bir uygulamanın performansı veriyi getirme hızına bağlıdır. Ne kadar hatasız, görsel şölen sunan bir uygulama da olsa geç açılan uygulama bizden değildir :)
Başarının anahtarı hızlı erişimdir!
Bu hızı bize sunabilecek yapılardan biri de Index’lerdir.
Indeks Ne İşe Yarar?
Indeksler, veritabanı performansını artırmak ve sorgu hızlarını optimize etmek için kullanılır. Normalde bir sorgu çalıştırıldığında, veritabanı sistemi tüm verileri tarar ve eşleşen sonuçları bulur. Burada en kötü senaryo aranan verinin o tabloda hiç olmamasıdır. Bu süreç büyük veritabanlarında oldukça zaman alabilir.
Indeks kullanıldığında ise veritabanı doğrudan indekse karşılık gelen değere ulaşır, böylece sorgu performansı büyük ölçüde artar.
Indeksler genellikle verileri sıralı bir şekilde depolamak için ikili-dengeli ağaç yapısı(B-Tree) kullanılarak uygulanır. Indeks veri yapısı iki alana sahiptir; dizinin sıraladığı veritabanı sütununun değerlerini saklayan alan, ve sütun değerlerinin disk konumunu belirlemeye yardımcı olan alan.
Indeksleri Ne Zaman Kullanmalıyız?
Indeksler, genellikle büyük veritabanlarında veya sıkça sorgulanan tablolarda kullanılır. Uygun yerde kullanıldığında veritabanı performansını önemli ölçüde artırabilir.
Indeks ek bir veri yapısıdır. Bu, hem saklanmak için “diskte” hem de erişilmek için “bellekte” alana ihtiyaç duyduğu anlamına gelir. Indeksler gereksiz kullanıldığında veya yanlış konfigüre edildiğinde veri ekleme, güncelleme ve silme işlemlerinde yavaşlığa neden olabilir veya çok fazla büyüdüğünde disk alanını doldurabilir.
Indekslerin otomatik olarak bakımının yapılması, tabloda bir değişiklik olduğunda veritabanının ek eylemler gerçekleştirmesi anlamına gelir. Örneğin, users tablosunda bir kullanıcı için last_name kolonunda “Şen”den “Atmaca”ya güncelleme yapıldığında, veritabanı; verilerin depolandığı bloğu yeni değerle günceller, eski “Şen” değerini de tüm dizinlerden kaldırır. Bu, CPU zamanı ve disk erişimi gerektirir. Böylece gereksiz indekslerin tutulması DML işlemlerinin yavaşlamasına neden olur.
Dolayısıyla, her zaman her koşulda indeks kullanmanın çok verimli olduğunu söyleyemeyiz.
Küçük tablolar için tarama performansı indeks tablodan daha üstün olabilir.
DML(Data Manipulation Language) : Veri Manupulasyonu
insert,update, delete, select
Indeks Türleri Nelerdir ?
- Single Column Index: Tek sütunlu dizin, yalnızca bir tablo sütunu temel alınarak oluşturulan indeksdir.
CREATE INDEX index_name ON table_name (column_name);2. Composite — MultiColumn Index: Birden fazla sütunu içerir ve bu sütunların kombinasyonuna göre sıralanır. Birleşik indeksler, birden fazla sütunu içeren sorgular için faydalı olabilir. WHERE yan tümcesinde filtre olarak sıklıkla kullanılan iki veya daha fazla sütun varsa, çok sütunlu indeks en iyi seçim olacaktır.
CREATE INDEX index_name ON table_name (column1_name, column2_name);3. Benzersiz (Unique) Index: Benzersiz indeksler yalnızca performans için değil aynı zamanda veri bütünlüğü için de kullanılır. Benzersiz bir dizin, tabloya herhangi bir yinelenen değerin eklenmesine izin vermez. Genellikle primary key(birincil anahtar) alanlarında kullanılır.
CREATE UNIQUE INDEX index_name on table_name (column_name);4. Partial Indexes :
Belirli bir koşulu sağlayan veri satırlarını içerecek şekilde bir indeksin oluşturulması anlamına gelir. Bu, veri tablosundaki belirli bir alt kümesi için indeks oluşturmanıza izin verir.
CREATE INDEX index_name on table_name (conditional_expression);Örnek senaryo düşünelim: Bir e-ticaret uygulamasında bir ürün tablosu olduğunu ve bu tablonun “stok_durumu” sütunu bulunduğunu varsayalım. Stok durumu “true” olan yani stokta bulunan ürünleri sık sık sorguluyorsak, bu durumu indekslemek performansı artırabilir.
CREATE INDEX idx_product_stock_status ON product(stock_status);Bu indeks de işe yarar fakat biz sadece stokta olan ürünleri yani true olanları arıyoruz. Fakat bu index yine full scan ile hem true hem false olanları arıyor. Aşağıdaki gibi true alanına göre filtre index koyarsak çok daha performanslı bir index yaratmış oluruz. Sadece stokta olan ürünler içinden arama yaparız.
CREATE INDEX idx_product_stock_status ON product (id)
WHERE stock_status = true;5. Tam Metin (Full-Text) Index: Metin tabanlı sütunlar üzerinde tam metin aramalarını hızlandırmak için kullanılır. Özellikle büyük metin verileri içeren tablolarda etkilidir. Or: fotoğrafları base64 formatında tutan bir tablo vb.
6. Index İfadeleri (Function-Based Index): Belirli bir matematiksel ifadeye dayalı olarak oluşturulan indekslerdir. Örneğin, bir sütundaki mutlak değerlere göre indeks oluşturulabilir.
Indeks oluştururken nasıl karar verilir?
Vehicle adında bir tablomuz olsun. Bu tabloda da id, vehicle_type, plate, driver_id .. kolonları olsun. Bunlardan hangi alanlarda, hangi indeks türlerini koymalıyız??
- Önem Derecesi:
Vehicle tablomuzun alanlarını gözönüne alalım, plate alanı benzersiz ve null olmayan bir alandır. Model alanı ise null olabilir ve benzersiz olmayan bir alandır. Primary key olan id alanı en yüksek öneme sahiptir. Bunun aksine, model ve color sütunlarının önem derecesi düşük olacaktır çünkü bunlar birden fazla yinelenen değer içerebilir. Düşük öneme sahip bir sütunda indeks oluşturmak, sorgulandığında birden fazla kayıt döndürmesi nedeniyle tercih edilmez; bu, genel sorgu yürütme süresini artırır ve veritabanı performansını düşürür.
Primary Key Index: Her zaman bir tabloda yalnızca bir adet primary key indeks bulunabilir. Bu genellikle tablonun benzersiz tanımlayıcısı olan bir kolona uygulanır.
CREATE UNIQUE INDEX idx_vehicle_id ON vehicle (id);2. Where clause — single index belirleme :
SELECT * FROM vehicle WHERE driver_id = 1011;Bu şekilde tek bir alan üzerinde çok fazla sorgulama yapıyorsak aşağıdaki indeksi eklemek mantıklı olacaktır.
CREATE INDEX idx_driver_id ON vehicle (driver_id);3. Where clause — multiple index belirleme :
Birden fazla alan içeren Where cümlelerini sıklıkla kullanıyorsak aşağıdaki gibi bir indeks oluşturabiliriz.
SELECT * FROM vehicle WHERE vehicle_type = 'RING' AND PLATE = '34AD34'CREATE INDEX idx_vehicle_type_plate ON vehicle (vehicle_type, plate);Başka bir örnek olarak, eğer biri sıklıkla aşağıdaki gibi sorgular yapıyorsa:
SELECT * FROM users WHERE (first_name || ' ' || last_name) = 'Dilek Şen';o zaman şöyle bir dizin oluşturmaya değer olabilir:
CREATE INDEX users_name ON users ((first_name || ' ' || last_name));4. Where clause — partial index belirleme :
Eğer sadece belirli bir vehicle_type için sık sık sorgulama yapılıyorsak bu durumda yalnızca belirli bir koşulu sağlayan veriler için indeks oluşturma yoluna gidebiliriz. Bu, tablodaki yalnızca belirli bir alt küme üzerinde çalışan sorgular için fayda sağlar.
Or: vehicle_type : RING, SHORT_DIST, LONG_DIST tiplerini alabilsin. RING ve LONG_DIST tipini çok fazla sorguluyorken SHORT_DIST çok nadir/hiç sorgulamıyorsak bu kümeyi dışarda bırakarak sadece en çok sorguladığımız tiplerin indeks listesinde yer almasını sağlayabiliriz. Böylece indeks kümesini küçültmüş oluruz. Bu da istediğimiz verinin daha hızlı gelmesini sağlayacaktır.
SELECT *
FROM vehicle
WHERE vehicle_type <> 'SHORT_DISTANCE' AND id in (1001,..);CREATE INDEX idx_vehicle_type_not_short ON vehicle (id)
WHERE vehicle_type <> 'SHORT_DISTANCE';Önemli Bilgiler;
— Oluşturduğumuz indeksler gerçekten kullanılıyor mu?
Indekslerin kullanılabilmesi için indeks ifadesinin where koşuluyla tam olarak eşleşmesi gerekir. Örneğin, last_name alanı ile bir indeks oluşturmuş olalım;
Aşağıdaki Select ifadesi bu indeksi kullanır:
SELECT * FROM users WHERE last_name = 'Şen'Ancak bu Select ifadesi indeksi kullanmaz:
SELECT * FROM users WHERE UPPER(last_name) = 'Şen'Where clause, indeksteki LAST_NAME ifadesiyle eşleşmeyen UPPER(LAST_NAME) öğesini içerdiğinden ikinci select ifadesi indeksi kullanmaz. Eğer UPPER kullanılan çok fazla SELECT ifadesi varsa UPPER(LAST_NAME) içeren bir indeks oluşturulmalıdır.
— Madem bu kadar hızlı neden her yere indeks koymuyoruz?
Küçük verili tablolar için indeksler oldukça işimize yarayacak, hatta gereksiz attığımız indeksler bize çok da problem yaratmayacaktır. Fakat gelecekte tablo çok fazla büyüdüğünde indeks artık verimli çalışmayacaktır — yavaşlık gün geçtikçe artacaktır, çünkü tabloda gerekli/gereksiz bakımının yapılması gereken bir sürü indeks yaratılmıştır..
Kayıtlar sıklıkla ekleniyor, güncelleniyor ve siliniyorsa, tabloyla ilişkilendirilen indeks sayısı ne kadar az olursa performans o kadar iyi olur. Bunun nedeni, veritabanı güncellendiğinde indeksler de güncelleneceği için dolayısıyla kayıt ekleme, güncelleme ve silme performansı yavaşlayacaktır.
Çok büyük miktarda kayıt değişime uğrayacaksa indeksleri kaldırıp değişimleri yapıp tekrar indeks yaratmak bir çözüm olabilir.
— Indeksi nasıl silebiliriz?
Bir indeksi kaldırmak için bu DROP INDEXkomutunu kullanabiliriz. Indeksler herhangi bir zamanda tablolara eklenebilir ve tablolardan çıkarılabilir.
DROP INDEX index_name;— Indeksi güncellememiz gerekir mi?
Bir indeks oluşturulduktan sonra başka bir müdahale gerekmez. Sistem, tablo değiştirildiğinde indeksi günceller ve bunu yapmanın sıralı tablo taramasından daha verimli olacağını düşündüğünde indeksi sorgularda kullanır.
— Indeks yaratmanın data kaybı açısından bir riski var mı?
Büyük bir tabloda indeks oluşturmak uzun zaman alabilir. PostgreSQL üzerinde konuşursak varsayılan olarak PostgreSQL, indeks oluşturma işlemine paralel olarak tabloda SELECT ifadelerine izin verir, ancak yazmalar ( INSERT, UPDATE, DELETE) dizin oluşturma tamamlanana kadar engellenir. Canlı sistemlerde bu genellikle kabul edilemez bir durumdur. Fakat yazma işlemlerinin indeks oluşturma işlemine paralel olarak gerçekleşmesine izin vermek mümkündür.
CREATE INDEX , CONCURRENTLY yöntem seçeneği belirtilerek çağrılır . Bu seçenek kullanıldığında, PostgreSQL'in tablonun iki taramasını gerçekleştirmesi gerekir ve buna ek olarak, indeksi değiştirebilecek veya indeksi kullanabilecek tüm mevcut işlemlerin sonlandırılmasını beklemesi gerekir. Bunu kullanmadan önce daha detaylı araştırma yapmanızı öneririm.
— Veri tabanında tablodan veri silindiği zaman indeks’e ne olur?
not: postgres veritabanı üzerine yazılmıştır, farklı veritabanları için ele alma yöntemi farklı olabilir.
Veritabanında, üzerinde indeks olan bir tablodan veri silindiğinde;
- Silinen veriye ait indeks silinir veya güncellenir. or: Indeks B-Tree yapısı üzerine kurulduysa silinen veriye ait indeks de kaldırılır.
- Silinen indeksten sonra boşluklar oluşacaktır. Postgres bu indeksleri optimize etmek için çeşitli stratejiler kullanır;
- Silme işlemi sonrasında, PostgreSQL istatistiklerini günceller. Bu istatistikler, sorgu planlaması ve performans optimizasyonu için önemlidir. araştırınız: ANALYZE
- PostgreSQL’de, otomatik temizlik işlemleri (autovacuum) indeks ve tablo düzenini korumak için çalışır. Bu işlemler, silme işlemleri sonucunda ortaya çıkan boşlukları temizler.
— Büyük veritabanlarında büyük bir tabloya indeks atılmaya çalışıldığında vacuum işlemi ile aynı zamana denk gelirse ne olur?
- Indeks ekleme işlemi ve
VACUUMişlemi, her ikisi de kaynak tüketen işlemlerdir. Bu durumda, aynı anda çalıştıkları zaman, sistem kaynakları (CPU, bellek, disk I/O) üzerinde rekabet olabilir, bu da performans düşüklüğüne yol açabilir. VACUUMişlemi sırasında, PostgreSQL otomatik olarak bir dizi kilitleme (lock) kullanabilir. Bu, tabloyu ve indeksleri de içerir. Indeks eklemek istediğinizde, bu işlemACCESS EXCLUSIVEkilidi kullanabilir, bu da tablo üzerinde tüm diğer işlemleri engeller. Eğer aynı anda birVACUUMişlemi de çalışıyorsa, bu durum indeks eklemeye engel olabilir veya tam tersi senaryoya da sebebiyet verebilir.- Bu tür durumlarda, genellikle indeks eklemek veya
VACUUMişlemi gibi daha düşük aciliyetli işlemleri yoğun saatler dışında gerçekleştirmek gerekir. Bu işlemleri planlarken düşük sistem yükü ve trafiği olduğu zamanlarda, birbiriyle çakışmayacak şekilde planlarsak performans sorunlarını minimize etmiş oluruz.
Not : “VACUUM”
Vacuum işlemi, veritabanında yapılan güncellemeler, silmeler ve işlemler sonucu boşa çıkan alanları serbest bırakarak disk alanının daha etkili kullanılmasını sağlar.
İstatistikleri güncelleyerek veritabanı optimizasyonunu destekler.
Dead Rows (Ölü Satırlar — güncellenen veya silinen veriler) , vacuum işlemi, bu ölü satırları temizleyerek veritabanının boyutunu küçültür ve performansı artırır.
MVCC(Multi-Version Concurrency Control), PostgreSQL’de aynı anda birden çok işlem tarafından erişilen verileri yönetmek için kullanılan yöntemdir. Bir transaction’ın başka bir transaction’ın değişikliklerini görmesini engeller. MVCC yönetimi sırasında dolayısıyla eski versiyonlar ve gereksiz verikler birikir. vacuum işlemi, bu eski sürümleri temizleyerek veritabanını optimize eder.
NOT: Veritabanında TRANSACTION
Bir dizi işlem veya sorgunun bir bütün haline getirilmesidir. Bu işlemler birbirleriyle bağlantılıdır ve birlikte çalışarak belirli bir görevi tamamlarlar. Bu işlemlerin birlikte başarılı bir şekilde tamamlanması veya tamamlanmaması gerekir.
— Bir tabloya indeks yarattığımızda foreign_key alanı otomatik olarak indekslenir mi?
MySQL, PostgreSQL gibi bazı veritabanları, FOREIGN KEY kolonları için otomatik olarak indeks oluşturma özelliğine sahiptir. Fakat veritabanı bağımsız bu özellik değişebilir. Postgres veritabanını ele alırsak bu özellik default olarak açık değildir. Çünkü tabloya eklenen her foreign_key için oto indeks yaratılırsa çok az sorgu alınan kolonlar için gereksiz DML maliyeti doğardı.
PostgreSQL, birincil anahtarlar(primary_key) ve benzersiz(unique) kısıtlamalar üzerinde otomatik olarak indeks oluşturur, ancak yabancı anahtar(foreign_key) ilişkilerinin referans tarafında oluşturmaz. Bu, yabancı anahtara dayalı herhangi bir sorgunun tam tablo taraması yapacağı anlamına gelir.
or: Postgres veritabanında vehicle adında bir tablom var. Bu tabloda driver_id alanı var ve foreign_key. Driver_id’nin referans aldığı driver tablosunda id kolonunda indeks var. Vehicle tablosunda ise hiç indeks yok. Select * from vehicle where driver_id=..; şeklinde bir sorgu çalıştırdığımızda indeks kullanılır mı ?
vehicle tablosunda driver_id alanı üzerinde bir indeks bulunmadığı için sorgu sırasında indeks kullanılmayacaktır.
Foreign_key ilişkisi kurulmuş olsa bile, driver_id üzerinde bir indeks olmadığı için veritabanı yönetim sistemi driver_id'ye erişim sağlamak için tam tablo taraması (full table scan) gibi daha maliyetli yöntemlere başvurur.
