MariaDB ve MySQL’de INDEX Oluşturma ve Sorgu Hızını Artırma

Tarafındankomut
0 Yorum

Veritabanı performans sorunları, sistem yöneticilerinin ve geliştiricilerin en sık karşılaştığı problemlerin başında gelir. Uygulama yavaş çalışıyor, kullanıcılar şikayet ediyor, CPU yüzde yüzde takılıp kalıyor… Ve sen log’lara bakıyorsun, uzun süre çalışan sorgular görüyorsun. İşte tam bu noktada index optimizasyonu devreye giriyor. MariaDB ve MySQL’de doğru index stratejisi, bir sorgunun milisaniyeler yerine saniyeler sürmesi ile anında yanıt vermesi arasındaki farkı yaratabilir. Bu yazıda, index kavramını derinlemesine ele alacağız ve gerçek dünya senaryolarıyla nasıl kullanacağını adım adım göstereceğiz.

Index Nedir ve Neden Bu Kadar Önemli?

Bir kitabın sonundaki dizin (index) bölümünü düşün. “Veritabanı” kelimesini aramak istiyorsun; dizine bak, 47. sayfada yazıyor, direkt oraya git. Dizin olmadan ne yapardın? Kitabı baştan sona okuyarak o kelimeyi bulmaya çalışırdın. Veritabanı indexleri de tam olarak böyle çalışır.

MySQL ve MariaDB’de bir tabloda index yoksa, veritabanı motoru full table scan yapar. Yani tablodaki her satırı tek tek okur. Küçük tablolar için bu sorun değil, ama milyonlarca satır içeren bir tabloda bu işlem ciddi performans sorunlarına yol açar.

Index’lerin sağladığı avantajlar:

  • Okuma hızı: SELECT sorguları çok daha hızlı çalışır
  • JOIN performansı: Tablolar arası birleştirme işlemleri hızlanır
  • ORDER BY optimizasyonu: Sıralama işlemleri index üzerinden yapılır
  • Benzersizlik garantisi: UNIQUE index ile veri bütünlüğü sağlanır

Ama unutma, index’lerin bir bedeli var. Her INSERT, UPDATE ve DELETE işleminde index’ler de güncellenir. Bu yüzden gereksiz index oluşturmak, yazma performansını düşürür ve disk alanı tüketir.

Index Türleri

PRIMARY KEY

Her tablonun temel kimlik sütunudur. Otomatik olarak cluster index oluşturur. InnoDB’de tüm veriler bu index etrafında fiziksel olarak düzenlenir.

UNIQUE Index

Bir sütun veya sütun kombinasyonunun benzersiz olmasını garanti eder. Örneğin e-posta adresleri için ideal.

Ordinary (Regular) Index

En yaygın kullanılan index türü. Sütun değerlerini hızlı aramak için kullanılır, benzersizlik şartı yoktur.

FULLTEXT Index

Metin içinde arama yapmak için kullanılır. LIKE ‘%kelime%’ yerine çok daha hızlı çalışır.

Composite (Birleşik) Index

Birden fazla sütunu kapsayan index. Sütun sırasının önemi büyüktür.

Mevcut Durumu Analiz Etme

Önce mevcut index’leri ve tablo yapısını inceleyelim. Gerçek bir senaryo üzerinden gidelim: E-ticaret sitesi veritabanımızda orders tablosu var ve sorgular yavaş çalışıyor.

-- Tablonun mevcut yapısını ve index'leri görüntüle
SHOW CREATE TABLE ordersG

-- Ya da daha temiz görünüm için
SHOW INDEX FROM orders;

-- Tablo istatistiklerini gör
SHOW TABLE STATUS LIKE 'orders'G
-- Hangi sorgular yavaş çalışıyor? Slow query log'u etkinleştir
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-queries.log';

-- Mevcut ayarları kontrol et
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Slow query log’u etkinleştirdikten sonra birkaç saat bekle ve logları incele. mysqldumpslow aracı bu konuda çok yardımcı olur:

# En yavaş 10 sorguyu listele
mysqldumpslow -s t -t 10 /var/log/mysql/slow-queries.log

# Belirli bir pattern içeren yavaş sorgular
mysqldumpslow -s t -t 5 -g "SELECT.*orders" /var/log/mysql/slow-queries.log

EXPLAIN ile Sorgu Analizi

Index oluşturmadan önce ve sonra sorgu planını analiz etmek şart. EXPLAIN komutu, MySQL’in bir sorguyu nasıl çalıştıracağını gösterir.

-- Index olmadan sorgu planı
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 1234 
AND status = 'pending' 
ORDER BY created_at DESCG

-- EXPLAIN ANALYZE ile gerçek zamanlı analiz (MariaDB 10.9+, MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE customer_id = 1234 
AND status = 'pending'G

EXPLAIN çıktısında dikkat etmen gereken alanlar:

  • type: ALL görüyorsan full table scan yapılıyor, kötü işaret. ref, range, index görüyorsan index kullanılıyor
  • key: Kullanılan index adı. NULL görüyorsan index yok
  • rows: Taranacak tahmini satır sayısı. Bu değer ne kadar düşükse o kadar iyi
  • Extra: “Using filesort” veya “Using temporary” görüyorsan optimizasyon gerekiyor

Pratik Index Oluşturma Senaryoları

Senaryo 1: E-Ticaret Sipariş Tablosu

Diyelim ki aşağıdaki gibi bir tablomuz var:

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
    shipping_city VARCHAR(100),
    product_count INT DEFAULT 0
);

-- Önce EXPLAIN ile mevcut durumu gör
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234G
-- type: ALL, rows: 2500000 -> Felaket!

-- customer_id üzerine index oluştur
CREATE INDEX idx_customer_id ON orders (customer_id);

-- Şimdi tekrar kontrol et
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234G
-- type: ref, rows: 47 -> Mükemmel!

Senaryo 2: Composite Index ile Çoklu Koşul

Tek sütunlu index bazen yetmez. Özellikle birden fazla WHERE koşulu kullanan sorgular için composite index çok daha etkili olur.

-- Sık çalışan bu sorguyu düşün
SELECT id, total_amount, created_at 
FROM orders 
WHERE status = 'pending' 
AND created_at >= '2024-01-01'
ORDER BY created_at DESC;

-- status ve created_at üzerine composite index
CREATE INDEX idx_status_created ON orders (status, created_at);

-- Index kullanımını doğrula
EXPLAIN SELECT id, total_amount, created_at 
FROM orders 
WHERE status = 'pending' 
AND created_at >= '2024-01-01'
ORDER BY created_at DESCG

-- "Using filesort" kayboldu mu? Kontrol et
-- Extra: Using index condition ->

Önemli not: Composite index’te sütun sırası kritik öneme sahiptir. “Leftmost prefix” kuralı gereği, index’in soldan başlayan sütunları WHERE koşulunda kullanılmazsa index devreye girmez. Yani idx_status_created index’i, sadece created_at üzerinde arama yaparken kullanılmaz.

Senaryo 3: Covering Index ile Maksimum Performans

Bir sorgu, sadece index sütunlarından veri çekiyorsa, MySQL disk’e hiç gitmeden index üzerinden sonucu döner. Buna covering index denir.

-- Bu sorgu için
SELECT customer_id, status, created_at 
FROM orders 
WHERE customer_id = 1234 
AND status = 'pending';

-- Üç sütunu da kapsayan covering index
CREATE INDEX idx_covering ON orders (customer_id, status, created_at);

-- EXPLAIN çıktısında "Using index" görmelisin
EXPLAIN SELECT customer_id, status, created_at 
FROM orders 
WHERE customer_id = 1234 
AND status = 'pending'G
-- Extra: Using index -> Disk okuma yok, ultra hızlı!

Senaryo 4: FULLTEXT Index ile Metin Arama

Ürün açıklamalarında arama yapıyorsan, LIKE ‘%kelime%’ performans katilir. FULLTEXT index bu sorunu çözer.

-- Ürün tablosunda FULLTEXT index
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2),
    FULLTEXT INDEX ft_search (name, description)
);

-- Mevcut tabloya FULLTEXT index ekle
ALTER TABLE products ADD FULLTEXT INDEX ft_search (name, description);

-- LIKE yerine MATCH...AGAINST kullan
-- Yavaş yöntem
SELECT * FROM products WHERE description LIKE '%kablosuz kulaklık%';

-- Hızlı yöntem
SELECT *, MATCH(name, description) AGAINST('kablosuz kulaklık' IN BOOLEAN MODE) AS score
FROM products 
WHERE MATCH(name, description) AGAINST('kablosuz kulaklık' IN BOOLEAN MODE)
ORDER BY score DESC;

Var Olan Index’leri Yönetme

Index İsimlendirme Standardı

Proje büyüdükçe index’leri takip etmek zorlaşır. Standart bir isimlendirme politikası belirle:

  • idx_: Ordinary index için önek
  • uniq_: UNIQUE index için önek
  • ft_: FULLTEXT index için önek
  • Sütun isimlerini alt çizgi ile birleştir

Index Silme ve Güncelleme

-- Var olan index'i sil
DROP INDEX idx_customer_id ON orders;
ALTER TABLE orders DROP INDEX idx_customer_id;

-- Index'i yeniden adlandır (MariaDB 10.5.2+ ve MySQL 5.7+)
ALTER TABLE orders RENAME INDEX idx_old_name TO idx_new_name;

-- ONLINE DDL ile production'da kesintisiz index ekle
ALTER TABLE orders 
ADD INDEX idx_shipping_city (shipping_city),
ALGORITHM=INPLACE, 
LOCK=NONE;

Kullanılmayan Index’leri Tespit Etme

-- Performance Schema ile kullanılmayan indexleri bul
SELECT 
    t.object_schema,
    t.object_name AS table_name,
    t.index_name,
    t.count_star AS usage_count
FROM performance_schema.table_io_waits_summary_by_index_usage t
WHERE t.object_schema = 'ecommerce_db'
AND t.index_name IS NOT NULL
AND t.count_star = 0
ORDER BY t.object_name;

-- sys şeması kullanılarak (daha kolay okunur)
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = 'ecommerce_db';

Kullanılmayan index’leri direkt silme; önce bir süre daha gözlemle. Bazı index’ler belirli dönemlerde (ay sonu raporları gibi) devreye girer.

Gerçek Dünya Senaryosu: Yavaş Çalışan Rapor Sorgusu

Bir müşteri yönetim sisteminde ay sonu raporu dakikalar sürüyor. Aşağıdaki sorgu sorun çıkarıyor:

-- Problematik sorgu (45 saniye çalışıyor)
SELECT 
    c.name,
    c.email,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    MAX(o.created_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-01-31'
AND o.status IN ('delivered', 'shipped')
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 100;

-- Önce EXPLAIN ile analiz et
EXPLAIN SELECT c.name, c.email, COUNT(o.id)...

-- Tespit edilen sorunlar:
-- orders.customer_id üzerinde index yok
-- orders.created_at üzerinde index yok
-- orders.status üzerinde index yok

-- Çözüm: Composite index
CREATE INDEX idx_orders_report ON orders (created_at, status, customer_id, total_amount);

-- customers tablosunda da kontrol et
CREATE INDEX idx_customers_lookup ON customers (id, name, email);

-- Sorgu şimdi 0.3 saniyede çalışıyor!

Bu senaryoda idx_orders_report index’i neden bu sırayla oluşturuldu?

  • created_at öne aldık çünkü BETWEEN ile range sorgusu yapılıyor, en seçici filtre bu
  • status ikinci sıraya geldi, IN() ile filtreleme yapılıyor
  • customer_id JOIN için gerekli
  • total_amount covering index için eklendi, SUM hesaplamasında disk okumayı azaltır

Index Bakımı ve İstatistik Güncelleme

Index’ler zamanla bozulabilir (fragmentation) ve istatistikleri güncelliğini yitirebilir. Bu durum, optimizer’ın yanlış kararlar almasına yol açar.

-- Tablo ve index istatistiklerini güncelle
ANALYZE TABLE orders;
ANALYZE TABLE customers;

-- InnoDB tablolarında index optimizasyonu
OPTIMIZE TABLE orders;
-- Bu komut tabloyu kilitler! Production'da dikkatli kullan
-- Daha iyi alternatif: pt-online-schema-change (Percona Toolkit)

-- Index fragmentation durumunu kontrol et
SELECT 
    table_name,
    index_name,
    stat_name,
    stat_value
FROM mysql.innodb_index_stats
WHERE database_name = 'ecommerce_db'
AND table_name = 'orders'
ORDER BY index_name;

-- Otomatik istatistik güncelleme ayarı
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';
SET GLOBAL innodb_stats_auto_recalc = ON;

-- Daha hassas istatistik için sample size artır
SET GLOBAL innodb_stats_persistent_sample_pages = 25;

Sık Yapılan Hatalar

Hata 1: Her Sütuna Index Koymak

Bazı yöneticiler “daha fazla index = daha hızlı” sanır. Bu yanlış! 20 sütunlu bir tabloda her sütuna index koymak:

  • INSERT/UPDATE/DELETE işlemlerini yavaşlatır
  • Disk kullanımını gereksiz yere artırır
  • Optimizer’ı şaşırtabilir

Hata 2: Fonksiyon Kullanan WHERE Koşulları

-- Bu index KULLANMAZ
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM orders WHERE UPPER(status) = 'PENDING';

-- Bunun yerine şunu yaz, index çalışır
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

SELECT * FROM orders WHERE status = 'pending';
-- (status sütununu lowercase tutmak için uygulama katmanında kontrol et)

Hata 3: LIKE ile Wildcard Başlangıcı

-- Index çalışmaz (başta wildcard var)
SELECT * FROM products WHERE name LIKE '%laptop%';

-- Index çalışır (sondaki wildcard sorun değil)
SELECT * FROM products WHERE name LIKE 'laptop%';

-- Ortada ve başta arama için FULLTEXT kullan
SELECT * FROM products 
WHERE MATCH(name) AGAINST('laptop' IN BOOLEAN MODE);

Hata 4: NULL Değerler ve Index

InnoDB, NULL değerleri index’e dahil eder ama bazı durumlarda beklenmedik davranışlar olabilir:

-- NULL kontrol ederken dikkatli ol
SELECT * FROM orders WHERE shipping_city IS NULL;
-- Bu sorgu index kullanabilir ama test et

-- IS NOT NULL ile seçici index
CREATE INDEX idx_city_not_null ON orders (shipping_city) 
WHERE shipping_city IS NOT NULL;
-- MySQL'de partial index desteklenmez, MariaDB'de de sınırlı destek var
-- Alternatif: Default değer ata, NULL kullanma

Production Ortamında Index Ekleme

Büyük tablolarda (ALTER TABLE) index eklemek tabloyu kilitleyebilir. Production ortamı için Percona Toolkit kullan:

# Percona Toolkit kurulumu (Ubuntu/Debian)
apt-get install percona-toolkit

# pt-online-schema-change ile kesintisiz index ekle
pt-online-schema-change 
    --alter "ADD INDEX idx_customer_status (customer_id, status)" 
    --host=localhost 
    --user=root 
    --password=your_password 
    --database=ecommerce_db 
    --table=orders 
    --execute 
    --progress=time,30

# Önce dry-run ile test et
pt-online-schema-change 
    --alter "ADD INDEX idx_customer_status (customer_id, status)" 
    --host=localhost 
    --user=root 
    --password=your_password 
    --database=ecommerce_db 
    --table=orders 
    --dry-run

MariaDB kullanıyorsan, MariaDB 10.0+ sürümünden itibaren ALGORITHM=INPLACE, LOCK=NONE ile online DDL desteği geldi. Ama yine de Percona Toolkit daha güvenli.

Index Monitoring: Günlük Rutin

Sağlıklı bir veritabanı yönetimi için index izleme rutini oluştur:

-- Haftalık çalıştır: Büyük tablolarda index kullanım oranı
SELECT 
    table_schema,
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS index_size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY index_size_mb DESC
LIMIT 20;

-- Duplicate index kontrolü (sys şeması gerekli)
SELECT * FROM sys.schema_redundant_indexes 
WHERE table_schema = 'ecommerce_db';

Sonuç

Index optimizasyonu, veritabanı yönetiminin en kritik ve en çok ihmal edilen konularından biri. Doğru uygulandığında sorgu süreleri yüzde doksan veya daha fazla düşebilir, yanlış uygulandığında ise sistemi daha da yavaşlatabilir.

Özetlemek gerekirse:

  • Önce EXPLAIN ile analiz et, sonra index ekle. Körü körüne index oluşturma
  • Slow query log’u mutlaka etkinleştir ve düzenli olarak incele
  • Composite index sütun sırasına dikkat et, leftmost prefix kuralını unutma
  • Covering index ile disk I/O’yu sıfıra indirebilirsin
  • Metin aramaları için LIKE ‘%…%’ yerine FULLTEXT index kullan
  • WHERE koşullarında fonksiyon kullanmaktan kaçın
  • Production ortamında büyük tablolara Percona Toolkit ile online index ekle
  • Kullanılmayan index’leri temizle, gereksiz index yazma performansını öldürür
  • ANALYZE TABLE ile istatistikleri düzenli güncelle

Index optimizasyonu tek seferlik bir iş değil, sürekli devam eden bir süreç. Veritabanın büyüdükçe ve sorgular değiştikçe index stratejini de güncellemeyi unutma. Monitoring rutinini otomatize et ve aylık bazda index health check yap. Bu disiplini korursan, veritabanı kaynaklı “neden yavaş çalışıyor?” sorusunu çok daha az duyarsın.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir