MySQL Composite Index Oluşturma ve Kullanımı

Veritabanı performans sorunlarıyla uğraşan her sysadmin er ya da geç “neden bu sorgu bu kadar yavaş?” sorusuyla karşılaşır. Çoğu zaman cevap basittir: eksik veya yanlış yapılandırılmış index. Ama tek kolon indexlerinin yetmediği, birden fazla kolonun bir arada sorgulandığı senaryolarda composite index devreye girer. Bu yazıda MariaDB ve MySQL üzerinde composite index’in nasıl çalıştığını, nasıl oluşturulduğunu ve gerçek dünya senaryolarında nasıl kullanıldığını ele alacağız.

Composite Index Nedir?

Composite index, birden fazla kolonu kapsayan bir indeks yapısıdır. Tek kolonlu indexlerden farklı olarak, WHERE koşulunda veya JOIN işlemlerinde birden fazla kolon bir arada kullanıldığında çok daha etkili sonuçlar verir.

Basit bir örnekle açıklamak gerekirse: Bir e-ticaret uygulamanız var ve siparişler tablosunda hem user_id hem de status kolonuna göre sık sık sorgulama yapıyorsunuz. Bu iki kolon üzerinde ayrı ayrı index oluşturmak yerine, bu iki kolonu kapsayan tek bir composite index oluşturmak hem daha az bellek kullanır hem de sorgularınızı dramatik biçimde hızlandırır.

MariaDB ve MySQL, composite index’i B-tree yapısıyla depolar. Bu yapı, indexin soldan sağa doğru çalıştığı anlamına gelir. Yani (user_id, status, created_at) şeklinde bir composite index tanımladıysanız, bu index user_id bazlı sorgularda, user_id + status bazlı sorgularda ve user_id + status + created_at bazlı sorgularda kullanılabilir. Ama sadece status veya sadece created_at bazlı bir sorgu bu indexten faydalanamaz.

Leftmost Prefix Kuralı

Composite index’in en kritik kavramı leftmost prefix (en soldaki önek) kuralıdır. Bu kuralı anlamadan composite index kullanmak, çoğu zaman beklenen performansı getirmez.

Diyelim ki şöyle bir index tanımladınız:

CREATE INDEX idx_orders_composite ON orders (user_id, status, created_at);

Bu index şu sorgu kombinasyonlarında kullanılabilir:

Sadece user_id
user_id + status
user_id + status + created_at

Ama şu kombinasyonlarda kullanılamaz:

Sadece status
Sadece created_at
status + created_at

Bu kuralı göz ardı eden indexler “dead index” olarak tabloda yer kaplar, disk alanı ve yazma performansı açısından yük oluşturur ama sorgulara hiçbir katkı sağlamaz.

Örnek Tablo Yapısı

Pratik örneklere geçmeden önce üzerinde çalışacağımız tablo yapılarını oluşturalım:

CREATE TABLE orders (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    region      VARCHAR(50) NOT NULL,
    payment_method VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id   BIGINT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   SMALLINT UNSIGNED NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Şimdi test verileri ekleyelim ve composite index öncesi ile sonrası performansı karşılaştıralım.

Composite Index Oluşturma Yöntemleri

Tablo Oluştururken Index Eklemek

CREATE TABLE user_sessions (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    session_token VARCHAR(128) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    is_active  TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    INDEX idx_user_active (user_id, is_active),
    INDEX idx_user_expires (user_id, expires_at),
    UNIQUE INDEX idx_token (session_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Mevcut Tabloya Index Eklemek

-- Tek seferlik index ekleme
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- Birden fazla index aynı anda ekleme (daha verimli)
ALTER TABLE orders
    ADD INDEX idx_user_status_created (user_id, status, created_at),
    ADD INDEX idx_region_status (region, status),
    ADD INDEX idx_payment_status (payment_method, status, created_at);

-- CREATE INDEX sözdizimi ile ekleme
CREATE INDEX idx_order_items_order_product
    ON order_items (order_id, product_id);

ALTER TABLE ile birden fazla index eklerken hepsini aynı komuta dahil etmek önemlidir. Her ALTER TABLE komutu tabloya tam bir yeniden yazma işlemi uygular. Ayrı ayrı çalıştırırsanız tablo N kez yeniden yazılır.

EXPLAIN ile Sorgu Analizi

Index’lerin gerçekten kullanılıp kullanılmadığını anlamak için EXPLAIN veya EXPLAIN ANALYZE kullanmak zorunludur.

-- Index yokken bir sorgu deneyelim
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001
  AND status = 'pending'
ORDER BY created_at DESC;

Bu sorguyu indexsiz çalıştırdığınızda çıktıda type: ALL ve rows kolonunda tablonun tüm satır sayısını yakın bir değer görürsünüz. Bu, full table scan demektir.

Index ekledikten sonra:

-- Index ekle
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

-- Aynı sorguyu tekrar analiz et
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001
  AND status = 'pending'
ORDER BY created_at DESC;

Bu sefer çıktıda şunları görmelisiniz:

type: ref veya range (ALL’dan çok daha iyi)
key: idx_user_status_created
rows: Gerçekten taranan satır sayısı (çok daha az)
Extra: Using index condition veya Using where; Using index

Covering Index Kavramı

Composite index’in en güçlü özelliklerinden biri covering index yapısıdır. SELECT sorgusunda istediğiniz tüm kolonlar index içindeyse, MySQL disk’e gitmeden sadece index üzerinden sonucu döndürebilir. Bu durumda EXPLAIN çıktısında Extra kolonunda Using index ifadesini görürsünüz.

-- Bu sorgu için covering index oluşturalım
-- Sorguda sadece user_id, status, created_at ve total_amount var
ALTER TABLE orders ADD INDEX idx_covering_orders
    (user_id, status, created_at, total_amount);

-- Covering index'ten faydalanacak sorgu
EXPLAIN SELECT user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 1001
  AND status IN ('pending', 'processing')
ORDER BY created_at DESC
LIMIT 20;

Using index çıktısı, sorgunun tamamen index üzerinden çözüldüğünü gösterir. Veri sayfalarına erişilmez, bu da özellikle büyük tablolarda muazzam bir performans farkı yaratır.

Gerçek Dünya Senaryoları

Senaryo 1: E-Ticaret Sipariş Sorgulama

Bir e-ticaret platformunda en sık karşılaşılan sorgular kullanıcıya özel sipariş listelemeleridir:

-- Kullanıcının son siparişlerini getir
SELECT id, status, total_amount, created_at
FROM orders
WHERE user_id = 1001
  AND status != 'cancelled'
ORDER BY created_at DESC
LIMIT 10;

-- Bölgeye göre bekleyen siparişler
SELECT id, user_id, total_amount, created_at
FROM orders
WHERE region = 'istanbul'
  AND status = 'pending'
  AND created_at >= '2024-01-01'
ORDER BY created_at ASC;

Bu iki sorgu için doğru composite indexler:

-- Birinci sorgu için
ALTER TABLE orders ADD INDEX idx_user_status_created
    (user_id, status, created_at);

-- İkinci sorgu için
ALTER TABLE orders ADD INDEX idx_region_status_created
    (region, status, created_at);

Senaryo 2: Log Tablosu Performansı

Log tabloları genellikle devasa boyutlara ulaşır ve yanlış indexleme felakete yol açar:

CREATE TABLE application_logs (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_name   VARCHAR(100) NOT NULL,
    log_level  ENUM('DEBUG','INFO','WARNING','ERROR','CRITICAL') NOT NULL,
    message    TEXT NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    server_id  VARCHAR(50) NOT NULL,
    trace_id   VARCHAR(64)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Composite indexler
ALTER TABLE application_logs
    ADD INDEX idx_app_level_created (app_name, log_level, created_at),
    ADD INDEX idx_server_level_created (server_id, log_level, created_at);

-- Bu index ile hızlanan sorgular
SELECT id, message, created_at
FROM application_logs
WHERE app_name = 'payment-service'
  AND log_level IN ('ERROR', 'CRITICAL')
  AND created_at BETWEEN '2024-06-01 00:00:00' AND '2024-06-30 23:59:59'
ORDER BY created_at DESC
LIMIT 100;

Senaryo 3: Çoklu JOIN Sorgularında Index Kullanımı

-- Order items ile join yapılan sorgu
EXPLAIN SELECT
    o.id,
    o.user_id,
    o.status,
    o.total_amount,
    oi.product_id,
    oi.quantity,
    oi.unit_price
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 1001
  AND o.status = 'delivered'
  AND oi.product_id = 500
ORDER BY o.created_at DESC;

-- Bu sorgu için gerekli indexler
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);
ALTER TABLE order_items ADD INDEX idx_order_product (order_id, product_id);

JOIN sorgularında her tablonun JOIN koşulunda kullanılan kolonları için composite index bulunması kritiktir. order_items tablosunda (order_id, product_id) composite index’i, hem JOIN hem de filtreleme için tek indexte çözüm sunar.

Index Seçiciliği ve Kolon Sıralaması

Composite index tasarlarken kolon sıralaması performansı doğrudan etkiler. Genel kural: seçiciliği yüksek kolonları sola koy.

Seçicilik, bir kolonun benzersiz değer oranıdır. Bunu ölçmek için:

-- Her kolonun seçiciliğini ölç
SELECT
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT region) / COUNT(*) AS region_selectivity,
    COUNT(DISTINCT payment_method) / COUNT(*) AS payment_selectivity
FROM orders;

Örneğin user_id seçiciliği 0.8 çıkarken status 0.005 çıkıyorsa, (user_id, status) sıralaması (status, user_id) sıralamasından çok daha etkili olacaktır.

Ancak bu kural mutlak değildir. Sorgularınızın nasıl çalıştığı da belirleyicidir. Eğer sorgularınızın büyük çoğunluğunda status = 'pending' şartı varsa ve user_id bazen olmuyorsa, status‘u sola almak mantıklı olabilir.

Index Bakımı ve Sorun Giderme

Kullanılmayan ve Gereksiz Indexleri Tespit Etmek

-- Performance Schema ile kullanılmayan indexleri bul (MySQL 5.6+)
SELECT
    object_schema,
    object_name,
    index_name,
    count_star,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'myapp_db'
  AND index_name IS NOT NULL
  AND count_star = 0
ORDER BY object_name, index_name;

-- Mevcut indexleri listele
SHOW INDEX FROM orders;

-- Tüm indexleri information_schema üzerinden görüntüle
SELECT
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns,
    INDEX_TYPE,
    NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'myapp_db'
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE
ORDER BY TABLE_NAME, INDEX_NAME;

Duplicate Index Tespiti

-- Çakışan veya gereksiz indexleri bul
-- Örneğin (user_id) ve (user_id, status) varsa,
-- (user_id) tek başına gereksiz olabilir
SELECT
    a.TABLE_NAME,
    a.INDEX_NAME AS index1,
    b.INDEX_NAME AS index2,
    GROUP_CONCAT(DISTINCT a.COLUMN_NAME ORDER BY a.SEQ_IN_INDEX) AS cols1,
    GROUP_CONCAT(DISTINCT b.COLUMN_NAME ORDER BY b.SEQ_IN_INDEX) AS cols2
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b
    ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
    AND a.TABLE_NAME = b.TABLE_NAME
    AND a.INDEX_NAME != b.INDEX_NAME
    AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
    AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.TABLE_SCHEMA = 'myapp_db'
  AND a.SEQ_IN_INDEX = 1
GROUP BY a.TABLE_NAME, a.INDEX_NAME, b.INDEX_NAME
ORDER BY a.TABLE_NAME;

Index Silmek

-- Gereksiz index'i sil
ALTER TABLE orders DROP INDEX idx_user_id_only;

-- Veya
DROP INDEX idx_user_id_only ON orders;

Yazma Performansına Etkisi

Composite index’lerin en önemli dezavantajı yazma performansına olan etkisidir. Her INSERT, UPDATE ve DELETE işleminde tüm indexler güncellenir. Bu nedenle index sayısını ve kapsamını dikkatli yönetmek gerekir.

Yüksek yazma yoğunluğu olan tablolarda şu prensipleri uygulayın:

Bir tabloda 5-6 indexi geçmeye çalışmayın
Nadiren kullanılan sorgular için index oluşturmayın
innodb_buffer_pool_size değerini artırarak index sayfalarının bellekte tutulmasını sağlayın
Büyük toplu insert işlemleri öncesi ALTER TABLE orders DISABLE KEYS komutunu, sonrasında ALTER TABLE orders ENABLE KEYS komutunu kullanmayı değerlendirin (MyISAM için geçerli, InnoDB için alternatif yöntemler tercih edilir)

MariaDB’de Invisible Index Özelliği

MariaDB 10.6+ ve MySQL 8.0+ sürümlerinde invisible index özelliği geldi. Bu özellik sayesinde bir index’i silmeden önce query planner’dan gizleyerek etkisini test edebilirsiniz:

-- Index'i görünmez yap (sorgu planlayıcı kullanmaz ama varlığını korur)
ALTER TABLE orders ALTER INDEX idx_user_status_created INVISIBLE;

-- Etkiyi test et
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 'pending'
ORDER BY created_at DESC;

-- Güvenli olduğuna karar verdiyseniz sil
DROP INDEX idx_user_status_created ON orders;

-- Vazgeçtiyseniz geri al
ALTER TABLE orders ALTER INDEX idx_user_status_created VISIBLE;

Bu özellik özellikle production ortamında index değişikliği yapmadan önce etkiyi test etmek için çok değerlidir.

Index Hint Kullanımı

Bazen MySQL/MariaDB query optimizer beklediğiniz index’i seçmeyebilir. Bu durumda index hint kullanabilirsiniz:

-- Belirli bir index kullanmaya zorla
SELECT * FROM orders USE INDEX (idx_user_status_created)
WHERE user_id = 1001
  AND status = 'pending'
ORDER BY created_at DESC;

-- Belirli bir index'i kullanımdan dışla
SELECT * FROM orders IGNORE INDEX (idx_user_id_only)
WHERE user_id = 1001
  AND status = 'pending';

-- Sadece JOIN için index kullan
SELECT * FROM orders FORCE INDEX FOR JOIN (idx_user_status_created)
WHERE user_id = 1001;

Index hint kullanmak genellikle son çare olmalıdır. Eğer optimizer yanlış index seçiyorsa, istatistiklerin güncel olup olmadığını kontrol edin:

ANALYZE TABLE orders;
ANALYZE TABLE order_items;

Sık Yapılan Hatalar

Fonksiyon içinde kolon kullanmak: WHERE YEAR(created_at) = 2024 gibi ifadeler index’i devre dışı bırakır. Bunun yerine WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31' kullanın.
Implicit type conversion: WHERE user_id = '1001' (string vs integer) index kullanımını engeller. Veri tiplerini eşleştirin.
OR koşullarını yanlış kullanmak: WHERE user_id = 1 OR status = 'pending' gibi OR koşulları composite index’ten tam faydalanamaz. UNION kullanımını değerlendirin.
Kardinalitesi düşük kolon prefix’te: status gibi az değer alan kolonları composite index’in başına koymak seçiciliği düşürür.
Her kolona ayrı index açmak: 5 kolonlu bir tabloya 5 ayrı tek kolonlu index açmak yerine, sorgu paternlerinize göre 1-2 composite index çok daha etkilidir.

Sonuç

Composite index, doğru kullanıldığında veritabanı performansını katbekat artırabilen güçlü bir araçtır. Ama kör bir şekilde “her şeye index açalım” yaklaşımı hem disk alanını hem de yazma performansını olumsuz etkiler.

Başarılı bir composite index stratejisi için şu adımları izleyin: önce slow_query_log‘u aktif edip yavaş sorguları tespit edin, EXPLAIN ile bu sorguları analiz edin, sorgu paternlerinize göre leftmost prefix kuralına uygun composite indexler tasarlayın, covering index fırsatlarını değerlendirin ve son olarak Performance Schema ile index kullanımını düzenli olarak izleyin.

Production ortamında index değişikliği yaparken pt-online-schema-change veya gh-ost gibi araçları kullanmayı ihmal etmeyin. Büyük tablolarda direkt ALTER TABLE komutu yazma işlemlerini bloke edebilir ve bu durum production sistemlerde kabul edilemez bir downtime’a yol açar.

MariaDB ve MySQL’de Composite Index Oluşturma ve Kullanımı