g u t x .com.tr İpek yolu - Çin'i anlamaya götürürüm

Mysql veritabanı indeksinin uygulama prensibini ayrıntılı olarak açıklayın - BTree indeksi, hash, tam metin indeksi vb.

Genel Bakış

MySQL birçok depolama motorunu destekler ve çeşitli depolama motorları farklı dizinleri destekler. Bu nedenle, MySQL veritabanı birden çok dizin türünü destekler. BTree indeksi, B + Ağaç indeksi, hash indeksi, tam metin indeksi ve daha fazlası. Aşağıda, bu dizinlerin uygulama ilkelerine kısa bir giriş yer almaktadır.

01 Hash indeksi

Yalnızca bellek (bellek) depolama motoru, karma dizinini destekler. Karma dizini, değerin hashCode'unu hesaplamak için dizin sütununun değerini kullanır ve ardından değerin hashCode'un karşılık gelen konumunda bulunduğu satır verilerinin fiziksel konumunu depolar. Karma algoritma kullanıldığından, Erişim hızı çok hızlıdır, ancak bir değer yalnızca bir hashCode'a karşılık gelebilir ve bu, hash'in dağıtım yöntemidir, dolayısıyla karma indeksi, aralık arama ve sıralama işlevini desteklemez.

genel konuşma Karma indeksi, belirli bir karma algoritma kullanmaktır , Anahtar değerini yeni bir hash değerine dönüştürmek için, kök düğümden yaprak düğümüne bir B + ağacı gibi adım adım arama yapmaya gerek yoktur ve karşılık gelen konum, çok hızlı olan tek bir hash algoritması ile anında bulunabilir.

02 Tam metin dizini

FULLTEXT (tam metin) dizini, Yalnızca MyISAM ve InnoDB için mevcuttur Daha büyük veriler için, tam metin dizini oluşturmak çok zaman alır ve yer kaplar. Büyük metin nesneleri veya daha büyük CHAR türü veriler için, normal bir dizin kullanıyorsanız, metnin ilk birkaç karakterini eşleştirmek mümkündür, ancak metnin ortasında birkaç kelimeyle eşleştirmek istiyorsanız, LIKE% word% kullanmanız gerekir Eşleştirmek için, işlenmesi uzun zaman alır ve yanıt süresi büyük ölçüde artacaktır.Bu durumda, FULLTEXT dizini kullanılabilir. Bir FULLTEXT indeksi oluştururken, metin için bir kelime listesi oluşturulacak ve indeksleme sırasında kelime listesine göre indekslenecektir. .

sfenks - tam metin dizini

FULLTEXT, tablo oluşturulduğunda oluşturulabilir veya gerektiğinde ALTER veya CREATE INDEX ile eklenebilir:

// Bir tablo oluştururken FULLTEXT dizini ekle CTREATE TABLE my_table ( id INT (10) BİRİNCİL ANAHTAR, isim VARCHAR (10) BOŞ DEĞİL, metnim METİN, FULLTEXT (metnim) ) MOTOR = MyISAM VARSAYILAN KARAKTER = utf8; // Tabloyu oluşturduktan sonra, gerektiğinde bir FULLTEXT dizini ekleyin ALTER TABLE my_table ADD FULLTEXT INDEX ft_index (sütun_adı);

Tam metin dizini sorgusunun ayrıca kendi özel sözdizimi vardır ve% LIKE sorgu dizesi% 'nin bulanık sorgu sözdizimi kullanılamaz

SELECT * FROM table_name MATCH (ft_index) AGAINST ('sorgu dizesi');

Not:

* Daha büyük veri kümeleri için, FULLTEXT indeksi olmayan bir tabloya veri eklemek ve ardından bir FULLTEXT indeksi eklemek, zaten FULLTEXT indeksi olan bir tabloya veri eklemekten daha hızlıdır.
* Sürüm 5.6'dan önce MySQL ile birlikte gelen tam metin dizini yalnızca MyISAM depolama motoru için kullanılabilir. Başka bir veri motoruysa, tam metin dizini etkili olmayacaktır. 5.6 sürümünden sonra InnoDB depolama motoru tam metin indekslemeyi desteklemeye başladı
* MySQL'de İngilizce tam metin indeksleme için kullanışlıdır, ancak Çince şu anda desteklenmemektedir. 5.7 sürümünden sonra, ngram eklentisini kullanarak Çinceyi destekler.
* MySQL'de, alınan dizge çok kısaysa, beklenen sonuç alınamaz.Eğer alınan dizge en az 4 bayt uzunluğundadır.Ayrıca, alınan karakter durdurma sözcükleri içeriyorsa, durdurma sözcükleri göz ardı edilecektir.

03 BTree indeksi ve B + Ağaç indeksi

1. BTree endeksi

BTree, ağacın derecesinin 2d (d > 1), yükseklik h ise, BTree aşağıdaki koşulları karşılamalıdır:

Her bir yaprak düğümünün yüksekliği aynıdır, h'ye eşittir;
Yaprak olmayan her düğüm, n-1 anahtarlardan ve n işaretçilerden oluşur; burada d < = n < = 2d, anahtar ve nokta birbirinden ayrılır ve düğümün her iki ucu da anahtar olmalıdır;
Yaprak düğüm işaretçilerinin tümü boştur;
Yaprak olmayan düğümlerin anahtarlarının tümü, anahtarın anahtarı bir dizin olarak temsil ettiği ve veriler, anahtar değerinin bulunduğu satırın verileridir;

BTree'nin yapısı aşağıdaki gibidir:

BTree organizasyonu altında, ikili arama yöntemini kullanabilirsiniz. Arama karmaşıklığı h * log (n) şeklindedir. Genel olarak konuşursak, ağacın yüksekliği çok küçüktür, genellikle yaklaşık 3'tür, bu nedenle BTree çok verimli bir aramadır. yapı.

2. B + Ağaç dizini

B + Tree, BTree'nin bir çeşididir. Diyelim ki ağacın derecesi ve h ağacın yüksekliği olsun.B + Tree ve BTree arasındaki temel farklar şunlardır:

B + Ağacı'ndaki yaprak olmayan düğümler verileri saklamaz, yalnızca anahtar değerleri depolar;

B + Ağacının yaprak düğümlerinde işaretçi yoktur, tüm anahtar değerleri yaprak düğümlerde görünecektir ve anahtar tarafından saklanan anahtar değeri, veri verilerinin fiziksel adresine karşılık gelir;

B + Ağacının yaprak olmayan her düğümü n anahtar değerden ve n işaret noktasından oluşur;

B + Ağacının yapısı aşağıdaki gibidir:

3. B + Tree'nin BTree'ye kıyasla avantajları

1) Disk okuma ve yazma maliyeti daha düşüktür

Genel olarak konuşursak, B + Tree, harici belleğin indeks yapısını gerçekleştirmek için BTree'den daha uygundur, çünkü depolama motorunun tasarım uzmanları, harici belleğin (disk) depolama yapısını akıllıca kullanırlar, yani diskin en küçük depolama birimi bir sektördür ve İşletim sisteminin bloğu genellikle sektörün tam sayı katıdır. İşletim sistemi belleği sayfa birimleri halinde yönetir. Bir sayfa genellikle varsayılan olarak 4K'dır. Veritabanının sayfası genellikle işletim sistemi sayfasının tam sayı katına ayarlanır. Dizin yapısının düğümü, bir sayfanın boyutu olacak şekilde tasarlanmıştır ve daha sonra, tüm düğümün verileri belleğe her okunduğunda ve ardından bellekte arandığında, harici belleğin "ön okuma" ilkesini kullanarak bilinir. Belleğin okuma hızı, harici bellek okuma G / Ç hızının yüzlerce katıdır, bu nedenle arama hızını iyileştirmenin anahtarı, disk G / Ç'sini en aza indirmektir, bu durumda her düğümde daha fazla tuş olduğunu anlayabilirsiniz. , Daha sonra ağacın yüksekliği ne kadar küçükse, o kadar az G / Ç gerekir.Bu nedenle, genel olarak, B + Tree, BTree'den daha hızlıdır, çünkü B + Tree, verileri yaprak olmayan düğümlerde depolamaz ve daha fazla anahtar depolayabilir.

2) Sorgu hızı daha kararlı

B + Ağaç yaprak olmayan düğümler veri depolamadığından, tüm veriler yaprak düğümlere sorgulanmalıdır ve yaprak düğümlerin yüksekliği aynıdır, bu nedenle tüm verilerin sorgu hızı aynıdır.

4. Sıralı indeksli B + TREE

Birçok depolama motoru, bitişik yaprak düğümlerine işaretçiler ekleyerek, sıralı erişim işaretçileriyle bir B + Ağacı oluşturan B + Ağacı temelinde optimize edilmiştir.Bu, ilkini bulduğunuz sürece aralıklı aramanın verimliliğini artırmak içindir. Daha sonra sırayla aşağıdaki değerleri bulabilirsiniz.

B + Ağacının yapısı aşağıdaki gibidir:

04 Kümelenmiş dizin ve kümelenmemiş dizin

Yukarıdakiler esas olarak MySQL dizin yapısının gerçekleştirme ilkesiyle ilgilidir, şimdi belirli depolama motorunun dizin yapısını nasıl gerçekleştirdiğine bir göz atalım. MySQL'deki en yaygın iki depolama motoru, sırasıyla kümelenmemiş dizinler ve kümelenmiş dizinler uygulayan MyISAM ve InnoDB'dir.

Kümelenmiş dizinin açıklaması şu şekildedir: kümelenmiş dizinin sırası, verilerin fiziksel depolama sırasıdır.

Kümelenmemiş dizinin açıklaması şöyledir: dizin sırasının verilerin fiziksel düzeniyle ilgisi yoktur.

Her şeyden önce, birkaç kavram eklemeliyiz: İndekslerin sınıflandırılmasında, indeks anahtarının birincil anahtar olup olmadığına göre dizin anahtarını "birincil dizin" ve "ikincil dizin" olarak bölebiliriz. Birincil anahtar değeri kullanılarak oluşturulan dizine "birincil dizin" denir. "İkincil dizin" olarak adlandırılır. Bu nedenle, yalnızca bir birincil dizin olabilir ve birçok yardımcı dizin olabilir.

1. MyISAM kümelenmemiş dizin

MyISAM depolama motoru, kümelenmemiş bir dizin kullanır. Kümelenmemiş dizinin birincil ve ikincil dizinleri hemen hemen aynıdır, ancak birincil dizinin yinelemelere izin vermemesi ve boş değerlere izin vermemesidir. Yaprak düğümlerinin anahtarları, anahtar değerini gösterecek şekilde depolanır. Verilerin fiziksel adresi.

myisam (kümelenmemiş) tablo dağılımı

Kümelenmemiş bir dizinin veri tablosu ve dizin tablosu ayrı olarak saklanır.

Kümelenmemiş dizindeki veriler, verilerin eklendiği sıraya göre depolanır. Bu nedenle, kümelenmemiş dizin, tek veri sorgusu için daha uygundur. Kampanya siparişi, anahtar değerinden etkilenmez.

FULLTEXT indeksi yalnızca MyISAM'da kullanılabilir. (Mysql5.6'dan sonra, innoDB ayrıca tam metin indekslemeyi de destekler)

* İlk başta, kümelenmemiş dizinin birincil indeksi ve ikincil indeksi aynı içeriği gösterdiği için neden ikincil indekse ihtiyaç duyulduğunu anlamadım, daha sonra indeksin sorgu için kullanılmadığını fark ettim ve o yerlerde, WHERE ve ORDER BY ifadelerinden hemen sonra gelmiyor mu, o zaman ya sorgu koşulu birincil anahtar değilse? Şu anda, bir yardımcı dizine ihtiyaç vardır.

2. InnoDB kümelenmiş dizin

Kümelenmiş dizinin birincil dizininin yaprak düğümü, anahtar değerine karşılık gelen verilerin kendisini depolar ve yardımcı dizinin yaprak düğümü, anahtar değerine karşılık gelen verilerin birincil anahtar değerini depolar. Bu nedenle, birincil anahtarın değeri ne kadar küçükse, tür o kadar iyi ve basitse o kadar iyidir.

InnoDB (kümelenmiş) tablo dağıtımı

Kümelenmiş dizinin verileri, birincil anahtar dizini ile birlikte depolanır.

Kümelenmiş dizinin verileri, birincil anahtarın sırasına göre depolanır. Bu nedenle, daha az disk G / Ç'sine sahip olabilen ve sorguyu hızlandıran birincil anahtar dizini aralığına göre arama yapmak için uygundur. Ancak bu nedenle, kümelenmiş dizinin ekleme sırası, birincil anahtarın monoton sırasına yerleştirilmesi en iyisidir, aksi takdirde sık sık sayfa bölünmelerine neden olur ve performansı ciddi şekilde etkiler.

InnoDB'de, yalnızca dizinlenmiş sütunları bulmanız gerekiyorsa, sorgu verimliliğini artıracak başka sütunlar eklememeye çalışın.

* Ana dizini kullanırken, kümelenmiş dizini kullanmak daha uygundur, çünkü kümelenmiş dizinin yalnızca bir kez aranması gerekir ve kümelenmemiş dizinin, verilerin adresi bulunduktan sonra veriler için bir G / Ç araması yapması gerekir.

* Kümelenmiş yardımcı dizin, birincil anahtarın anahtar değerini depoladığından, yardımcı dizin şu anda korunmadığından, veri satırı taşındığında veya sayfa bölündüğünde maliyeti düşürebilir. Ancak birincil dizin verinin kendisini sakladığından, kümelenmiş dizin daha fazla yer kaplayacaktır.

* Kümelenmiş dizinler, yeni veri eklenirken kümelenmemiş dizinlerden çok daha yavaştır, çünkü yeni veriler eklenirken birincil anahtarın yinelenip yinelenmediğini tespit etmek gerekir. Bu, kümelenmiş dizinin yaprak düğümleri yerine birincil dizinin tüm yaprak düğümlerini geçmeyi gerektirir. Veri adresi daha az yer kaplar, bu nedenle dağıtım yoğunlaşır ve sorgulama sırasında daha az G / Ç vardır, ancak kümelenmiş dizinin ana dizini verinin kendisini depolar.Veri geniş bir alan kaplar ve çok sayıda sektörü işgal edebilecek daha geniş bir dağıtım aralığına sahiptir. , Bu nedenle geçişi tamamlamak için daha fazla G / Ç gerekir.

3. Kümelenmiş dizin ile kümelenmemiş dizin arasındaki fark

Aşağıdaki şekil, kümelenmiş dizin ve kümelenmemiş dizin arasındaki farkı canlı bir şekilde gösterebilir:

Yukarıdaki şekilden, kümelenmiş dizinin ikincil dizininin yaprak düğümünün verilerinin birincil anahtarın değerini sakladığını ve birincil dizinin yaprak düğümünün verilerinin verilerin kendisini sakladığını, yani veri ve dizinin birlikte depolandığını ve dizinin Sorgulanacak yer verinin kendisidir, bu durumda indeksin sırası ve verinin kendisinin sırası aynıdır;

Birincil indeksin verileri ve kümelenmemiş indeksin ikincil indeksinin yaprak düğümünün verileri, depolanan verilerin fiziksel adresleridir; bu, indeks ve verilerin birlikte depolanmadığı ve verilerin sırasının indeksin sırasıyla hiçbir ilgisi olmadığı anlamına gelir. Yani, indeks sırasının verinin fiziksel sırası ile ilgisi yoktur.

ps: MyISAM ve innoDB arasındaki farklar şu şekilde özetlenmiştir:

sonuç olarak

InnoDB işlemleri, satır düzeyinde kilitlemeyi destekler, B ağacını, Tam metni ve diğer dizinleri destekler, ancak Hash dizinlerini desteklemez;

MyISAM işlemleri desteklemez, tablo düzeyinde kilitlemeyi destekler, B ağacı ve Tam metin gibi dizinleri destekler ve Karma dizinleri desteklemez;

Ayrıca, Bellek işlemleri desteklemez, tablo düzeyinde kilitlemeyi destekler, B-ağacı ve Hash gibi dizinleri destekler ve tam metin dizinlerini desteklemez;

Daha sonra daha fazla devop ve DBA içeriği paylaşacağım ve ilgilenen arkadaşlar buna dikkat edebilir ~