MariaDB'de Index Seçimi: Samanlıkta İğne Aramayın, Mıknatıs Kullanın

N+1 yazısında da, 1 milyon satırlık WHERE yazısında da aynı tavsiyeyle bitirmiştik: "EXPLAIN'e bakın." Doğru tavsiye, ama eksik. EXPLAIN size optimizer'ın ne yapmayı planladığını söyler. Ne yaptığını söylemez.

MariaDB index optimizasyonunu bu sefer tahminle değil sayaçla konuşalım. Bir milyon siparişlik bir tablo kurdum, aynı sorguyu beş farklı index ile çalıştırdım ve her seferinde MariaDB'nin gerçekte kaç satır, kaç sayfa okuduğunu kaydettim.

Metaforumuz eski. Tablo samanlık, aradığınız yedi satır iğne, index de mıknatıs. Bu sefer eklenen parça, yanlış ucundan tutulan mıknatısın iğneyle birlikte samanlığın yarısını da çekmesi. Ölçümün en sert sonucu bu oldu; yanlış sıralı index, hiç index olmamasından üç kat yavaş çıktı.

MariaDB'de EXPLAIN ANALYZE Yok, ANALYZE Var#

MySQL 8.0.18'den beri EXPLAIN ANALYZE var. O alışkanlıkla MariaDB'ye gelirseniz karşılama şöyle oluyor:

EXPLAIN ANALYZE SELECT 1;
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
-- that corresponds to your MariaDB server version for the right syntax
-- to use near 'ANALYZE SELECT 1' at line 1

MariaDB'de aynı işi 10.1'den beri ANALYZE deyimi yapıyor. Özelliği yazan Sergei Petrunia, o günkü blog yazısının yorumlarında önce PostgreSQL'deki gibi EXPLAIN ANALYZE adını düşündüklerini, itirazlar üzerine vazgeçtiklerini anlatıyor.

Resmi dokümana göre ANALYZE sorguyu gerçekten çalıştırır, sonucu atar ve size planı verir. Planın yanına iki gözlem sütunu ekler:

• rows / r_rows: optimizer'ın tahmin ettiği satır sayısı ile gerçekten okunan.
• filtered / r_filtered: WHERE koşulundan sağ çıkan satırların tahmini ve gerçek oranı.

ANALYZE FORMAT=JSON bir adım daha ileri gidiyor: her düğüm için r_total_time_ms ve InnoDB tablolarda r_engine_stats.pages_accessed, yani dokunulan buffer pool sayfası. Bu son sayaç 10.6.15 ve 10.11.5'ten itibaren geliyor.

Acı Gerçek: ANALYZE UPDATE ve ANALYZE DELETE değişikliği gerçekten yapar. Doküman bunu açıkça yazıyor. Bir de karıştırılmasın: ANALYZE TABLE bambaşka bir komut, sorgu çalıştırmaz, istatistik toplar.

Samanlık: Bir Milyon Siparişlik Tablo#

Tabloyu MariaDB'nin Sequence motoruyla, harici betik olmadan doldurdum. seq_1_to_1000000 hazır bir sayı tablosu gibi davranıyor; CRC32 ile de sütunlar birbirinden bağımsız dağılıyor.

CREATE TABLE siparisler (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  musteri_id INT UNSIGNED NOT NULL,
  durum ENUM('beklemede','odendi','iptal','iade') NOT NULL,
  tutar DECIMAL(10,2) NOT NULL,
  adres_notu VARCHAR(255) NOT NULL,
  olusturma DATETIME NOT NULL
) ENGINE=InnoDB;
​
INSERT INTO siparisler (musteri_id, durum, tutar, adres_notu, olusturma)
SELECT
  1 + CRC32(CONCAT('m', seq)) % 20000,
  ELT(1 + CASE WHEN CRC32(CONCAT('d', seq)) % 10 < 7 THEN 1
               WHEN CRC32(CONCAT('d', seq)) % 10 < 8 THEN 0
               WHEN CRC32(CONCAT('d', seq)) % 10 < 9 THEN 2
               ELSE 3 END,
      'beklemede', 'odendi', 'iptal', 'iade'),
  ROUND(50 + (seq * 37) % 4950 + (seq % 100) / 100, 2),
  REPEAT('x', 40 + seq % 120),
  '2024-01-01' + INTERVAL CRC32(CONCAT('t', seq)) % (1000 * 86400) SECOND
FROM seq_1_to_1000000;

Sonuç: 20.000 müşteri, siparişlerin %70'i odendi, tarihler Ocak 2024 ile Eylül 2026 arası, 143,7 MB veri. Aradığımız iğne bir müşteri ekranındaki klasik sorgu:

SELECT id, tutar, olusturma
FROM siparisler
WHERE musteri_id = 4242
  AND durum = 'odendi'
  AND olusturma >= '2026-01-01'
ORDER BY olusturma DESC
LIMIT 20;

Bu sorgunun döndürdüğü satır sayısı 7. Aynı tabloda 2026'dan kalan sipariş sayısı ise 265.171. Bu iki sayıyı aklınızda tutun.

Teknik Detay: Ölçümler MariaDB 10.11.14'te, 512 MB buffer pool ile, tablo bellekteyken alındı. Milisaniyeler bu makinenin rakamı; taşınabilir olan okunan satır ve sayfa sayısı.

Beş Mıknatıs, Beş Ölçüm#

Tek sütunluk index: Optimizer haklı olarak yüz çevirdi#

olusturma üzerindeki index possible_keys listesine girdi ama kullanılmadı. 2026 siparişleri tablonun dörtte birinden fazlası; o kadar satır için index'ten tek tek tabloya gitmek, tabloyu baştan sona okumaktan pahalı. Burada optimizer'ı suçlayacak bir şey yok.

Ters sıralı index: Samanlıktan beter#

Asıl ders burada. Aynı üç sütun, sadece sıra farklı, ve sorgu index'siz hâlinden üç kat yavaş. Üç tekrarda da 535 ile 568 ms arası.

Teşhis: Plan range diyor ama ANALYZE içerideki manzarayı gösteriyor. MariaDB index'i olusturma sırasıyla geriye doğru yürüdü (Handler_read_prev: 265.171), yani 2026'nın bütün siparişlerine tek tek uğradı. Dokunulan sayfa 9.130'dan 795.861'e çıktı. Extra sütununda Using index condition yok; index'teki musteri_id ve durum aramayı hiç daraltmadı.

Neden bu plan seçildi? Sorgudaki ORDER BY olusturma DESC LIMIT 20 ipucu veriyor. Bu index satırları zaten istenen sırayla veriyor; plan, sıralamadan kurtulup 20 satıra ulaşınca durabileceği varsayımıyla kurulmuş görünüyor. Ama eşleşen satır 7 tane. Yirmiyi hiç bulamayan okuma, aralığın sonuna kadar gitti. Sağlaması basit: aynı index dururken ORDER BY ve LIMIT'i kaldırdığımda optimizer tam taramaya döndü (172 ms, 9.130 sayfa).

Gerçek: Bir index'in plana girmesi, işe yaradığı anlamına gelmiyor. rows ile r_rows arasındaki fark (493.596'ya karşı 265.171) zaten bir şeylerin ters gittiğini söylüyordu.

Doğru sıra: Eşitlikler önce, aralık sonda#

(musteri_id, durum, olusturma) ile tahmin 7, okunan 7, sayfa 42. Mıknatıs önce müşteriye, sonra duruma iniyor; tarih aralığı en sonda kaldığı için o daralmış dilimin içinde sıralı okuma yapılıyor ve ORDER BY bedavaya geliyor.

Sütun sırasının neden önemli olduğunu MariaDB dokümanındaki Rick James'in composite index yazısı örneklerle anlatıyor. Eşitlik-önce kuralının sağlamasını iki yan ölçümle yaptım:

• (musteri_id, olusturma, durum) sırasında okunan satır 10'a çıktı. Aralık ortaya gelince sonraki sütun aramayı daraltamıyor; müşterinin 2026'daki bütün siparişleri okunup durum sonradan eleniyor. Burada fark küçük çünkü müşteri başına ortalama 50 sipariş var. Siparişi bol bir B2B müşterisinde bu fark aynı kalmaz.
• Doğru index dururken musteri_id'yi WHERE'den çıkarınca plan index tipine düştü: bir milyon index kaydının hepsi tarandı. Index'in ilk sütunu yoksa mıknatısı tutacak sap da yok.

Kapsayan index: Tabloya hiç gitmemek#

Sorgu id, tutar ve olusturma istiyor. tutar'ı index'in sonuna ekleyince Extra'da Using index belirdi ve sayfa sayısı 42'den 3'e indi. InnoDB'de ikincil index'ler birincil anahtarı zaten taşıdığı için id için ayrıca bir şey yapmak gerekmedi.

Bedel: Index boyutu 21,5 MB'tan 27,6 MB'a çıktı ve her INSERT/UPDATE artık bir sütun daha yazıyor. Aynı sorguyu SELECT * ile yazdığımda Using index anında kayboldu. Kapsayan index, sayfa başına en sık çalışan bir iki sorgunun lüksüdür; her sorguya yapılırsa yazma tarafına fatura keser.

PHP Tarafında: Sorguyu Mıknatısla Test Etmek#

Elle ANALYZE yazıp JSON okumak ilk iki seferde eğlenceli. Sonrası için çerçeve bağımsız küçük bir fonksiyon:

<?php
declare(strict_types=1);
​
/**
 * Sorguyu ANALYZE FORMAT=JSON ile gerçekten çalıştırır ve her tablo için
 * tahmini satır (rows) ile okunan satırı (r_rows) yan yana döndürür.
 * Yalnızca SELECT için kullanın: ANALYZE UPDATE/DELETE değişikliği yapar.
 */
function planRaporu(PDO $pdo, string $select, array $params = []): array
{
    if (!preg_match('/^\s*SELECT\b/i', $select)) {
        throw new InvalidArgumentException('Yalnızca SELECT analiz edilir.');
    }
​
    $stmt = $pdo->prepare('ANALYZE FORMAT=JSON ' . $select);
    $stmt->execute($params);
    $plan = json_decode((string) $stmt->fetchColumn(), true, flags: JSON_THROW_ON_ERROR);
​
    $satirlar = [];
    $yuru = function (mixed $dugum) use (&$yuru, &$satirlar): void {
        if (!is_array($dugum)) {
            return;
        }
        if (isset($dugum['table']['table_name'])) {
            $t = $dugum['table'];
            $satirlar[] = [
                'tablo'    => $t['table_name'],
                'erisim'   => $t['access_type'] ?? '?',
                'index'    => $t['key'] ?? null,
                'tahmin'   => $t['rows'] ?? null,
                'okunan'   => $t['r_rows'] ?? null,
                'sayfa'    => $t['r_engine_stats']['pages_accessed'] ?? null,
                'kapsayan' => $t['using_index'] ?? false,
            ];
        }
        foreach ($dugum as $alt) {
            $yuru($alt);
        }
    };
    $yuru($plan);
​
    return $satirlar;
}

Doğru index dururken yukarıdaki sorguyla çağırdığımda çıktı tek satır: siparisler | range | index=ix | tahmin=7 | okunan=7 | sayfa=42 | kapsayan=hayır. Fonksiyon PDO'nun hem emüle edilen hem gerçek hazırlanmış ifadeleriyle (ATTR_EMULATE_PREPARES açık ve kapalı) aynı sonucu verdi.

Uyarı: ANALYZE sorguyu gerçekten çalıştırır. Canlı sunucuda ağır bir raporu bununla test ederseniz o raporu iki kez çalıştırmış olursunuz. Bu fonksiyonun yeri geliştirme ortamı ve CI.

Mıknatıs Seçerken Kontrol Listesi#

• Eşitlik sütunları önce, aralık sütunu sonda. = ve IN önde; >=, BETWEEN, LIKE 'abc%' en sonda. ORDER BY sütunu aralık sütunuyla aynıysa sıralama bedavaya gelir.
• İlk sütunu sorgunuz vermiyorsa index aramaya yaramaz. (a, b, c) index'i WHERE b = ? için en iyi ihtimalle baştan sona taranır.
• rows ile r_rows'u yan yana koyun. Aradaki büyük fark ya eskimiş istatistiği ya da yanlış planı gösterir.
• pages_accessed'a bakın. Süre makineye göre değişir; aynı veride dokunulan sayfa sayısı değişmez.
• ORDER BY ... LIMIT'li sorguları index ekledikten sonra ayrıca ölçün. Bu yazının en pahalı planı tam olarak oradan çıktı.

Mıknatıs samanlıkta iğneyi bulur. Yanlış ucundan tutarsanız iğneyle birlikte samanı da kucaklarsınız ve fark ettiğinizde elinizde 795.861 sayfa vardır.

Teknolojiyle kalın, sorgularınız sayaçla konuşsun.