MariaDB'de Index Oluşturma: CREATE INDEX, UNIQUE, Çok Sütunlu ve IGNORED Indexler

Index ne işe yarar?

Resmî "Essentials of an Index" rehberi konuyu bir çalışan tablosu üzerinden anlatır. Index yoksa belirli bir kişinin telefonunu bulmak için sunucunun her satırı tek tek okuması gerekir; tabloda milyonlarca satır olduğunda bu, hızlı bir sunucu için bile verimsizdir. Çözüm verileri sıralamaktır, ancak tüm tabloyu aynı anda hem soyada hem kimliğe hem de pozisyona göre sıralayamazsınız. Index tam olarak bunu çözer: aradığınız sütunun sıralı bir kopyasını ve her değerin ait olduğu satıra giden işaretçiyi ayrı bir yapıda tutar. Soyadı üzerindeki bir index ile "soyadı Marx olan çalışan" sorusu, pozisyon üzerindeki bir index ile de "tüm kasiyerler" sorusu hızla yanıtlanır.

Bedelsiz değildir. Rehber, gereğinden fazla indexin hem disk alanı tükettiğini hem de INSERT, UPDATE ve DELETE işlemlerini yavaşlatabileceğini vurgular, çünkü her veri değişikliğinde ilgili tüm indexlerin de güncellenmesi gerekir. Ayrıca belgeye göre büyük tablolarda (tampon boyutlarını aşanlarda) kazanç çok küçük tablolara göre çok daha belirgindir.

MariaDB'deki index türleri

"Getting Started with Indexes" rehberi dört ana türü ayırır. SQL ifadelerinde KEY ve INDEX kelimeleri genellikle eş anlamlı kullanılır.

Bunlara ek olarak CREATE INDEX sözdizimi coğrafi veriler için SPATIAL ve vektör araması için VECTOR indexlerini de kabul eder. Tam metin arama MariaDB FULLTEXT arama, vektör indexleri de MariaDB vector arama yazısında ayrıca ele alınıyor.

Birincil anahtar ve InnoDB

Birincil anahtar her satırı benzersiz biçimde tanımlar, NULL içeremez ve tablo başına yalnızca bir tane olabilir. Rehber, InnoDB için iki önemli ayrıntıyı not eder: birincil anahtar diğer tüm indexlere sonek olarak eklenir, bu yüzden onu küçük tutmak (uygun bir tamsayı tipi seçmek) hem performans hem depolama açısından önemlidir. İkincisi, tabloda açıkça tanımlanmış bir birincil anahtar ve hiç UNIQUE index yoksa InnoDB kendiliğinden görünmeyen 6 baytlık bir kümelenmiş (clustered) index oluşturur. Var olan bir tabloya birincil anahtar eklemek için şu biçim kullanılır:

ALTER TABLE Employees ADD PRIMARY KEY(ID);

CREATE INDEX sayfası da InnoDB birincil anahtarlarının mümkünse tablo oluşturulurken tanımlanmasını önerir, çünkü sonradan birincil anahtar eklemek tablonun yeniden oluşturulmasını gerektirir. Tablo tasarımının geri kalanı için MariaDB veritabanı ve tablo oluşturma yazısına bakabilirsiniz.

CREATE INDEX sözdizimi

Belgedeki tam sözdiziminin en sık kullanılan kısmı şöyledir:

CREATE [OR REPLACE] [UNIQUE|FULLTEXT|SPATIAL|VECTOR] INDEX
    [IF NOT EXISTS] index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [WAIT n | NOWAIT]
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

Arka planda bu ifade bir ALTER TABLE ifadesine çevrilir. Çalıştırmak için tablo ya da veritabanı üzerinde INDEX yetkisi gerekir; yetkilerin nasıl verildiğini MariaDB GRANT ve REVOKE yazısında bulabilirsiniz. Başka bir bağlantı tabloyu kullanıyorsa bir metadata kilidi etkindir ve ifade bu kilit bırakılana kadar bekler; bu durum işlem desteklemeyen tablolar için de geçerlidir.

Belgedeki örneklerden ilki tek sütunlu benzersiz bir index oluşturur:

CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);

OR REPLACE ve IF NOT EXISTS

Aynı adla ikinci kez index oluşturmak hata verir. OR REPLACE eklerseniz sunucu mevcut indexi silip yeni tanımla değiştirir; IF NOT EXISTS eklerseniz index yalnızca o adla bir index yoksa oluşturulur, varsa hata yerine bir uyarı üretilir. Belgede gösterilen akış şöyledir:

CREATE INDEX xi ON xx5 (x);
Query OK, 0 rows affected (0.03 sec)

CREATE INDEX xi ON xx5 (x);
ERROR 1061 (42000): Duplicate key name 'xi'

CREATE OR REPLACE INDEX xi ON xx5 (x);
Query OK, 0 rows affected (0.03 sec)

CREATE INDEX IF NOT EXISTS xi ON xx5 (x);
Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;
+-------+------+-------------------------+
| Level | Code | Message                 |
+-------+------+-------------------------+
| Note  | 1061 | Duplicate key name 'xi' |
+-------+------+-------------------------+

Kurulum betiklerinde IF NOT EXISTS güvenli bir varsayılandır: betik ikinci kez çalıştığında durmaz. OR REPLACE ise var olan indexi düşürüp yeniden kurduğu için büyük tablolarda maliyetlidir; yalnızca tanımı gerçekten değiştirmek istediğinizde kullanın.

UNIQUE index ve NULL davranışı

Benzersiz index, indexlenen sütundaki ya da sütun birleşimindeki değerlerin tekrar etmemesini sağlar. Birincil anahtardan farkı NULL kabul etmesidir. SQL'de bir NULL başka bir NULL'a hiçbir zaman eşit sayılmadığı için aynı benzersiz indexte birden fazla NULL bulunabilir. Belgedeki örnek bunu iki sütunlu bir benzersiz index ile gösterir:

CREATE TABLE t1 (a INT NOT NULL, b INT, UNIQUE (a,b));
INSERT INTO t1 VALUES (1,1), (2,2);
INSERT INTO t1 VALUES (2,1);
INSERT INTO t1 VALUES (3,NULL), (3, NULL);
SELECT * FROM t1;

+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 2 |    2 |
| 3 | NULL |
| 3 | NULL |
+---+------+

Burada (2,1) satırı kabul edilir, çünkü benzersizlik tek tek sütunlara değil birleşime uygulanır. İki (3, NULL) satırı da kabul edilir. Bunu doğrulamak için rehber SELECT (3, NULL) = (3, NULL); sorgusunu çalıştırır ve sonucun 0, yani yanlış olduğunu gösterir. Sütunun tekrar etmemesini kesin olarak istiyorsanız onu NOT NULL tanımlayın.

Rehberde iki ileri ayrıntı daha vardır. Birincisi, sonundaki boşluk karakterlerinin yok sayıldığı sütunlarda yalnızca sondaki boşluk sayısı farklı olan değerler yinelenen anahtar hatasına yol açabilir. İkincisi, anahtar uzunluğu depolama motorunun sınırını aşarsa ve motor uzun benzersiz indexleri destekliyorsa, benzersizliği sağlamak için bir HASH anahtar oluşturulabilir; SHOW CREATE TABLE çıktısında bu durum USING HASH olarak görünür. Ayrıca sanal (generated) bir sütun üzerine kurulan benzersiz index ile benzersizliği yalnızca belirli satırlara, örneğin yalnızca etkin kullanıcılara uygulamak mümkündür.

Çok sütunlu index ve sütun sırası

Bir index birden fazla sütunu kapsayabilir. MariaDB bütün indexi kullanamadığında indexin en soldaki sütun ya da sütunlarını kullanabilir; bu kural HASH indexler için geçerli değildir. "Compound (Composite) Indexes" sayfası bunu ABD başkanları tablosu üzerinden anlatır: WHERE last_name = 'Johnson' AND first_name = 'Andrew' sorgusu için iki ayrı tek sütunlu index yerine tek bir INDEX(last_name, first_name) çok daha uygundur. Sayfaya göre MariaDB bir sorguda nadiren birden fazla index kullanır; iki ayrı index varsa genellikle birini seçer ve kalan koşulu satırları okuyarak süzer.

Sütun sırasıyla ilgili kurallar şöyle özetlenebilir:

• WHERE içindeki sıra önemsizdir: AND ile bağlanan koşulların hangi sırada yazıldığı fark etmez.
• Index içindeki sıra çok önemlidir: INDEX(last, first) yalnızca last ile süzen sorguya yardım eder, yalnızca first ile süzen sorguda işe yaramaz.
• Gereksiz tekrar: hem INDEX(last) hem INDEX(last, first) tanımlamak eklemelerde maliyet yaratır ve okumalarda nadiren yarar sağlar; ikincisi ilkinin işini zaten görür.
• Sona eklenen sütunlar: indexin sonuna eklenen fazladan sütunlar az zarar verir, hatta indexi "kapsayan" hâle getirerek fayda sağlayabilir.

Kapsayan (covering) index, sorgunun ihtiyaç duyduğu tüm sütunları içeren indextir. Örnekte INDEX(last_name, first_name, term) tanımlandığında sunucu term değerini de indexten okuyabildiği için tablo verisine hiç gitmez; EXPLAIN çıktısının Extra sütununda bu durum "Using index" olarak görünür. Sayfa ayrıca INDEX(last_name(5), first_name(5)) gibi önek indexlerinin nadiren yardımcı olduğunu, çoğu zaman zarar verdiğini belirtir.

Küçük bir uygulama örneği: bir sipariş tablosunda sık sık "belirli bir müşterinin siparişlerini tarihe göre sırala" sorgusu çalışıyorsa önce eşitlikle süzülen sütunu, sonra sıralama sütununu koyarsınız:

CREATE INDEX idx_musteri_tarih ON siparisler (musteri_id, siparis_tarihi);

SELECT id, toplam
FROM siparisler
WHERE musteri_id = 42
ORDER BY siparis_tarihi;

Bu index yalnızca musteri_id ile süzen sorgulara da yardım eder, ama yalnızca siparis_tarihi ile süzen bir sorguda soldan önek kuralı nedeniyle kullanılamaz. Indexin gerçekten seçildiğini EXPLAIN ile doğrulamadan karar vermeyin.

Önek uzunluğu, ASC/DESC ve index tipleri

CREATE TABLE sayfasına göre index tanımında her sütundan sonra bir önek uzunluğu verebilirsiniz; uzunluk verilmezse sütunun tamamı indexlenir. Sütunlar için ASC veya DESC de belirtilebilir. Belgenin güncel sekmesi (MariaDB 11.4'ten itibaren) tek tek sütunların artan ya da azalan sırada saklanabildiğini ve bunun bazı ORDER BY durumlarında yararlı olduğunu, azalan indexlerin de MIN() ve MAX() için kullanılabildiğini söyler. Bir indexte en fazla 32 parça bulunabilir.

COMMENT seçeneğiyle bir indexin ne işe yaradığını 1024 karaktere kadar açıklayabilirsiniz; sunucu bu metni kullanmaz ama ekip arkadaşlarınız için değerlidir. KEY_BLOCK_SIZE değeri CREATE INDEX içinde yok sayılır, buna rağmen SHOW CREATE TABLE çıktısında görünür.

Mevcut indexleri görmek

Bir tablonun indexlerini iki yolla görebilirsiniz. SHOW INDEX her index sütunu için bir satır döndürür; SHOW CREATE TABLE ise tablonun tüm index tanımlarını içeren CREATE TABLE ifadesini gösterir:

SHOW INDEX FROM Employees;
SHOW CREATE TABLE Employees\G

Birincil anahtarı olmayan tabloları bulmak için rehber information_schema.TABLES ile information_schema.KEY_COLUMN_USAGE tablolarını LEFT JOIN ile birleştiren ve sistem şemalarını dışarıda bırakan bir sorgu verir. Birincil anahtarsız tablolar InnoDB'nin gizli indexine kalır; bu sorguyu düzenli çalıştırmak tasarım hatalarını erken yakalamanıza yardım eder.

IGNORED: indexi silmeden devre dışı bırakmak

MariaDB 10.6'dan itibaren bir indexi "yok sayılan" olarak işaretleyebilirsiniz. Yok sayılan index güncellenmeye devam eder ama optimize edici onu hiç yokmuş gibi davranır: sorgu planlarında kullanmaz ve istatistik kaynağı olarak da başvurmaz. MySQL 8'deki "invisible index" özelliğine benzer. Bir tablonun birincil anahtarı, açık ya da örtük olsun, yok sayılamaz.

CREATE OR REPLACE TABLE t1 (id INT PRIMARY KEY, b INT, KEY k1(b));
ALTER TABLE t1 ALTER INDEX k1 IGNORED;

-- veya index oluştururken:
CREATE OR REPLACE TABLE t1 (id INT PRIMARY KEY, b INT);
CREATE INDEX k1 ON t1(b) IGNORED;

Belge önerilen iş akışını da verir: kullanılmıyor gibi görünen bir indexi doğrudan silmek risklidir, çünkü optimize edici onu planda kullanmasa bile tahminleri için ona dayanıyor olabilir ve büyük bir indexi yeniden oluşturmak zaman alır. İşaretlemek ise anlıktır. Önce indexi yok sayılan olarak işaretleyin, uygulamanın normal çalıştığını izleyin, sorun çıkarsa NOT IGNORED ile geri alın, her şey yolundaysa indexi silin. Belgedeki örnekte aynı sorgunun iki durumdaki planı şöyledir:

CREATE OR REPLACE TABLE t1 (id INT PRIMARY KEY, b INT, KEY k1(b) IGNORED);

EXPLAIN SELECT * FROM t1 ORDER BY b;
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

ALTER TABLE t1 ALTER INDEX k1 NOT IGNORED;

EXPLAIN SELECT * FROM t1 ORDER BY b;
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | index | NULL          | k1   | 5       | NULL | 1    | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

Bir indexin yok sayılıp sayılmadığı SHOW INDEX çıktısındaki ve INFORMATION_SCHEMA.STATISTICS tablosundaki Ignored sütununda görünür. Dikkat: yok sayılan bir indexi USE INDEX, FORCE INDEX veya IGNORE INDEX ipucunda kullanmak, var olmayan bir index adı vermişsiniz gibi hata üretir.

Online DDL, kilitler ve ilerleme

CREATE INDEX çevrim içi DDL'i ALGORITHM ve LOCK yan tümceleriyle destekler; bu seçeneklerin anlamları ALTER TABLE sayfasında açıklanır ve MariaDB ALTER TABLE yazısında ayrıntılı olarak ele alınıyor. WAIT n ile kilit için en fazla kaç saniye bekleneceğini belirleyebilir, NOWAIT ile kilit alınamazsa ifadenin hemen başarısız olmasını sağlayabilirsiniz. Uzun süren işlemlerde mariadb istemcisi ilerleme bilgisini gösterir; belgedeki örnek çıktı şöyledir:

CREATE INDEX i ON tab (num);
Stage: 1 of 2 'copy to tmp table'    46% of stage

Aynı ilerleme bilgisi SHOW PROCESSLIST çıktısında da görünür. Bekleyen DDL ifadelerini nasıl izleyeceğinizi MariaDB SHOW PROCESSLIST ve KILL yazısında bulabilirsiniz. Rehber çok büyük tablolar için bir öneri daha yapar: veriyi önce yükleyip indexleri sonra oluşturmak, boş tabloda index kurup ardından veri yüklemekten genellikle daha hızlıdır. Toplu yükleme için MariaDB LOAD DATA ve mariadb-import yazısına bakabilirsiniz.

Index silmek: DROP INDEX

DROP INDEX de arka planda bir ALTER TABLE ifadesine çevrilir, aynı INDEX yetkisini ister ve metadata kilidi varsa bekler. IF EXISTS eklerseniz index yoksa hata yerine uyarı alırsınız. Birincil anahtarı silmek için index adı olarak PRIMARY yazmanız gerekir; PRIMARY bir anahtar kelime olduğu için ters tırnak zorunludur:

DROP INDEX IF EXISTS idx_musteri_tarih ON siparisler;
DROP INDEX `PRIMARY` ON siparisler;

Kullanılmayan indexleri bulmak için rehber iki yol önerir: kullanıcı istatistikleri etkinse information_schema.INDEX_STATISTICS tablosunu sorgulamak, ya da slow query log açıkken log_queries_not_using_indexes değişkenini ON yaparak tam tablo taraması yapan sorguları günlüğe yazdırmak. Günlük ayarları için MariaDB error log ve slow query log yazısına bakın.

Index neden kullanılmaz?

En sık rastlanan durum, başında joker karakter bulunan LIKE '%kelime%' sorgularıdır. Rehbere göre bu tür sorgular standart BTREE indexlerini etkin biçimde kullanamaz ve tam tablo taramasına dönüşebilir; böyle aramalar için tam metin indexi gerekir. İkinci yaygın sebep tip ya da karşılaştırma kuralı (collation) uyuşmazlığıdır: indexli bir metin sütununu sayıyla ya da uyumsuz bir collation ile karşılaştırmak indexin kullanılmasını engeller. MariaDB 10.6.16'dan itibaren sunucu bu durumda "Cannot use key ... for lookup" biçiminde bir not üretir. Varsayılan olarak bu not yalnızca EXPLAIN çalıştırıldığında gösterilir; tüm sorgular için görmek isterseniz note_verbosity değişkenini kullanabilirsiniz. Karakter seti ve collation konusu için MariaDB karakter seti ve collation yazısına bakın.

Fonksiyon içine alınmış sütunlar da klasik bir tuzaktır. SUBSTRING sayfasının güncel sekmesine göre MariaDB 11.8'den itibaren optimize edici SUBSTR(indexli_sutun, 1, n) = sabit_metin biçimindeki koşullar için indexi kullanabilir; daha önceki sürümlerde indexli sütun SUBSTR() argümanı olduğunda index kullanılamıyordu. Bu iyileştirme yalnızca bu kalıp için geçerlidir, diğer fonksiyonlar için genel bir kural gibi düşünmeyin.

Sık yapılan hatalar

• Her sütuna ayrı index eklemek: MariaDB bir sorguda genellikle tek index seçer. Birlikte süzülen sütunlar için tek bir çok sütunlu index çoğu zaman daha iyidir.
• Sütun sırasını rastgele seçmek: eşitlikle süzülen sütunu öne, aralık ya da sıralama sütununu arkaya koyun; soldan önek kuralını unutmayın.
• Birincil anahtarı CREATE INDEX ile eklemeye çalışmak: bu mümkün değildir, ALTER TABLE ... ADD PRIMARY KEY kullanın.
• UNIQUE indexin NULL'ları engellediğini sanmak: birden fazla NULL kabul edilir; gerekirse sütunu NOT NULL yapın.
• Kullanılmadığını düşündüğünüz indexi hemen silmek: önce IGNORED ile işaretleyip izleyin, ardından silin.
• Indexin kullanıldığını varsaymak: tip uyuşmazlığı, başta joker karakter veya fonksiyon içine alınmış sütun indexi devre dışı bırakabilir; EXPLAIN ile kontrol edin.

Sık Sorulan Sorular

KEY ile INDEX arasında fark var mı?

Hayır. MariaDB belgeleri iki kelimenin SQL ifadelerinde genellikle eş anlamlı kullanıldığını belirtir; CREATE TABLE sayfası da INDEX ve KEY için "eş anlamlı" der.

Index adını vermek zorunda mıyım?

CREATE TABLE içinde index adı isteğe bağlıdır; verilmezse otomatik bir ad atanır. Ancak indexi silmek için adına ihtiyaç duyarsınız ve kısıtlama ihlalinde hata mesajlarında bu ad görünür, bu yüzden anlamlı bir ad vermek iyi bir alışkanlıktır. CREATE INDEX ifadesinde ise ad sözdiziminin zorunlu parçasıdır.

Yabancı anahtar için ayrıca index oluşturmalı mıyım?

Yabancı anahtar sütunlarının bir BTREE indexin tamamı ya da en sol kısmı olması gerekir. Böyle bir index yoksa MariaDB onu otomatik oluşturur. Ayrıntılar MariaDB foreign key ve CHECK kısıtlamaları yazısında.

Kaç index fazla sayılır?

Belgeler kesin bir sayı vermez. Ölçüt, indexlerin uygulamanızın WHERE, JOIN ve ORDER BY kalıplarıyla eşleşmesidir. Hiç kullanılmayan her index yazma işlemlerine yük bindirir; kullanımı istatistiklerle ve EXPLAIN ile izleyin.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · EXPLAIN ve ANALYZE · ALTER TABLE · FULLTEXT arama · Foreign key ve CHECK · SQL sorgu optimizasyonu