Kesinlikle kuru ürünler! MySQL ortak mülakat soruları + indeks ilkesi analizi!
Bugün sizinle röportaj için gerekli olan Mysql indeksinin altında yatan ilkenin bir analizini paylaşacağım.
- Mysql indeksinin özü
- Mysql indeksinin temel ilkesi
- Mysql indeksinin pratik deneyimi
Kuru malları paylaşmadan önce bir fayda dalgası yaşayalım! Arka plandaki özel mesaja doğrudan dikkat çekmek istiyorum: öğrenme materyalleri, rutin yok! ! !
Aşağıdaki metin:
Röportaj
S: Veritabanındaki en yaygın yavaş sorgu optimizasyon yöntemi nedir?
Sınıf Arkadaşı A: Dizin ekleyin.
S: Dizin eklemek neden yavaş sorguları optimize edebilir? Sınıf Arkadaşı A: ... Bilmiyorum Sınıf Arkadaşı B: Çünkü dizin aslında sorguyu optimize eden bir veri yapısıdır.Örneğin, Mysql'deki dizin B + ağacı ile uygulanır ve B + ağacı sorgu hızını optimize edebilen bir veri yapısıdır. Verileri hızlı bir şekilde bulmak için dizinleri kullanın, böylece sorgular optimize edilebilir.
S: Hangi veri yapısının sorgu hızını iyileştirebileceğini biliyor musunuz? (Bu soruyu duyunca bir çukur varmış gibi hissediyorum ...) Sınıf Arkadaşı B: Karma tablo, tam dengeli ikili ağaç, B ağacı, B + ağaç vb.
Soru: Bu veri yapıları sorgu hızını optimize edebildiğinden, Mysql neden B + ağaçlarını kullanmayı seçiyor? Sınıf arkadaşı B: ... bilmiyorum
Sorular sor
ÇALIŞANLARDAN DİZİNİ GÖSTERİN.başlıklar;
Bir başlıklar tablosu vardır, birincil anahtar üç alandan oluşur: empno, title ve fromdate.
Öyleyse aşağıdaki ifadeler dizinleri kullanacak mı?
Neden karma tablolar, tam dengeli ikili ağaçlar, B ağaçları ve B + ağaçlarının tümü sorguları optimize edebilir ve Mysql neden B + ağaçlarını tercih eder?
Karma tabloların özellikleri nelerdir?
Böyle bir tablo varsa (tablo adı: sanguo):
Şimdi isim alanında bir karma indeksi oluşturun:
Alan değerine karşılık gelen dizi alt simgesinin karma algoritma tarafından rastgele hesaplandığını, bu nedenle görünebileceğini unutmayın. Hash çarpışması . Yani böyle bir indeks yapısı için, şimdi aşağıdaki SQL ifadesini çalıştıralım:
seçin * fromsanguowherename = 'Zhou Yu'
Karma algoritmaya göre "Zhou Yu" için doğrudan bir dizi alt simge hesaplayabilir ve ardından verileri doğrudan verilerden çıkarabilir ve kilide karşılık gelen veri satırının adresini alabilir ve ardından bu veri satırını sorgulayabilirsiniz.
Sonra şimdi aşağıdaki SQL ifadesini çalıştırırsanız:
dilden * seçin > 'Zhou Yu'
Yapabileceğiniz hiçbir şey yok, çünkü hash tablosunun özelliği Hızlı ve doğru bir şekilde sorgulayabilir, ancak aralık sorgusunu desteklemez .
Ya tamamen dengeli bir ikili ağaç kullanırsanız?
Veya yukarıdaki tablo verileri, aşağıdaki şekilde gösterildiği gibi tamamen dengelenmiş bir ikili ağaç ile temsil edilir (basitleştirmek için, verilere karşılık gelen adres şekilde çizilmemiştir.):
Grafikteki her düğüm aslında dört bölümden oluşmalıdır:
Hatırlatılması gereken diğer bir şey de ikili ağaçların sıralı olduğudur. Basitçe "soldaki sağdakinden daha küçüktür." Şimdi "Zhou Yu" yu ararsak, iki kez (ilk Cao Cao, ikinci Zhou Yu), Biha bulmamız gerekir. Umarım bir kez daha ihtiyacım olur. Ve tam dengeli ikili ağaç sıralı olduğu için, aralık aramasını da destekler.
B-ağacını kullanmaya ne dersiniz?
Yine de yukarıdaki tablo verileri, aşağıda gösterildiği gibi B-ağacı ile temsil edilmektedir (basitleştirmek için, verilere karşılık gelen adres şekilde çizilmemiştir.):
Aynı eleman bulunabilir ve B-ağacının temsili, tamamen dengelenmiş ikili ağaçtan "daha kısadır", çünkü B-ağacındaki bir düğüm birden fazla elemanı depolayabilir.
Ya B + ağacı kullanılırsa?
Veya yukarıdaki tablo verileri, aşağıdaki şekilde gösterildiği gibi B + ağacı ile temsil edilir (basitleştirmek için, verilere karşılık gelen adres şekilde çizilmemiştir.):
Aynı eleman için B + ağacının temsilinin B ağacından daha "şişman" olduğunu bulabiliriz.Bunun nedeni, B + ağacındaki yaprak olmayan düğümlerin yaprak düğümlerinde fazlalık olması ve yaprak düğümlerinin işaretçilerle birbirine bağlanmasıdır.
Peki B + ağaçlarının avantajları nelerdir?
Burada "disprove yöntemini" kullanıyoruz.Eğer indeks veri yapısı olarak şimdi tamamen dengeli bir ikili ağaç kullanıyorsak, neyin yanlış olduğuna bir bakalım. Aslında, dizin de çok "büyüktür", çünkü dizin aynı zamanda öğeleri de saklar, tablolarımızdan birinde ne kadar çok veri satırı varsa, karşılık gelen dizin dosyası aslında çok büyük olacaktır. Ayrıca diskte depolanması gerekir, ancak tümü bellekte değil Yani hangi veri yapısını seçeceğimizi düşündüğümüzde, başka bir açıdan düşünebiliriz, Diskten veri okumak için hangi veri yapısı daha uygundur ,veya Hangi veri yapısı disk IO verimliliğini artırabilir . Tamamen dengeli ikili ağaca dönüp baktığımızda, "Zhang Fei" yi sorgulamamız gerektiğinde aşağıdaki adımlara ihtiyacımız var
Aynı şekilde, B-ağacına baktığımızda, "Zhang Fei" nin yalnızca disk IO'sunu üç kez göndererek bulunabileceğini görüyoruz. Bu, B-ağacının avantajı: Bir düğüm birden fazla öğeyi depolayabilir.Tam dengeli bir ikili ağaçla karşılaştırıldığında, tüm ağacın yüksekliği azaltılır ve disk IO verimliliği artırılır. .
B + ağacı, B ağacının yükseltilmiş bir versiyonudur, sadece yaprak olmayan fazlalık düğümler. Bunu yapmanın avantajı Menzil aramanın verimliliğini artırmak için .
Burada Mysql'in indeks olarak B + ağacının veri yapısını kullandığı ve bu da indeksi sorgularken disk IO verimliliğini artırabildiği ve aralık sorgusunun verimliliğini artırabildiği ve B + ağacındaki öğelerin de sıralandığı sonucuna varılabilir.
Öyleyse, bir B + ağacının düğümünde saklamak için kaç öğe uygundur?
Aslında başka bir açıdan da düşünebilirsiniz B + ağacındaki bir düğüm ne kadar büyük?
cevap: B + ağacındaki bir düğüm, bir sayfa veya bir sayfanın birden çok katı için en uygun olanıdır . Çünkü bir düğümün boyutu 1 sayfadan küçükse, bu düğümü okurken 1 sayfa gerçekte okunacak ve kaynak israfına neden olacaktır; bir düğümün boyutu 1,2 sayfa gibi 1 sayfadan büyükse, o zaman bu düğümü okurken İki sayfa okunacak ve bu da kaynak israfına neden olacaktır; bu nedenle israfa neden olmamak için, bir düğümün boyutunu 1, 2, 3, 4, vb. Katların katlarında kontrol etmek en uygunudur.
Öyleyse, Mysql'deki B + ağacının düğümünün boyutu nedir?
Bu sorunun cevabı "1 sayfa". Burada bahsedilen "sayfa", özel bir Mysql birimidir (aslında işletim sistemine benzer). Mysql'in Innodb motorundaki bir sayfanın varsayılan boyutu 16k'dır (işletim sistemindeki bir sayfanın boyutu 4k, ardından Mysql'de 1 sayfa = işletim sisteminde 4 sayfa), komutunu kullanabilirsiniz 'Innodbpagesize' gibi KÜRESEL DURUMU GÖSTER; Görünüm.
Ve size şunu da söyleyebiliriz ki, bir sayfa için bir düğüm yeterlidir.
Bir düğüm için neden 1 sayfa (16k) yeterlidir?
MyISAM ve innodb, Mysql'de B + ağacını kullanıyor
Genellikle B + ağacının yaprak olmayan düğümlerinin veri depolamadığını, yalnızca yaprak düğümlerinin verileri depoladığını düşünürüz; B-ağacının yaprak olmayan ve yaprak düğümlerinin her ikisi de verileri depolarken, yaprak olmayan düğümlerin daha az indeks değeri depolamasına ve ağacın yüksekliğinin nispeten B + ağacından daha yüksek olacak ve ortalama G / Ç verimliliği daha düşük olacaktır.Bu nedenle, B + ağacı indeks veri yapısı olarak kullanılır ve B + ağacının yaprak düğümleri, menzil arama için de uygun olan işaretçilerle bağlanır. Yukarıdaki veri alanındaki iki depolama motoru farklıdır.
MyISAM'da B + ağacı
MYISAM'daki yaprak düğümün veri alanı, veri kaydının adresini depolar
Birincil anahtar dizini
Yardımcı indeks
MyISAM depolama motoru verileri sorgulamak için dizini kullandığında, önce dizine göre veri adresini bulur ve sonra adrese göre belirli verileri sorgular. Ve birincil anahtar dizini ile ikincil dizin arasında çok fazla fark yoktur.
InnoDB'de B + ağacı
InnoDB'deki birincil anahtar dizininin yaprak düğümünün veri alanı veri kayıtlarını depolar ve yardımcı dizin birincil anahtar değerini depolar
Birincil anahtar dizini
Yardımcı indeks
Innodb'daki birincil anahtar dizini gerçek verilere bağlıdır; bu, Innodb tablosunun birincil anahtar dizinine sahip olması gerektiği anlamına gelir. Bir tablo manuel olarak birincil anahtar dizini oluşturmazsa Innodb, benzersiz bir dizin olup olmadığını kontrol eder ve öyleyse benzersiz Dizin birincil anahtar dizini görevi görür. Benzersiz dizin yoksa, varsayılan olarak gizli bir birincil anahtar dizini (kullanıcı tarafından görülmez) oluşturulur. Ek olarak, Innodb'un birincil anahtar dizini MyISAM'ın birincil anahtar dizininden (bir eksi disk GÇ'si) daha verimlidir ve ikincil dizinlerden çok daha yüksektir. Bu nedenle, Innodb'u bir depolama motoru olarak kullandığımızda, en iyisi:
Sorumuza geri dönelim: Bir düğüm için neden 1 sayfa (16k) yeterlidir?
Yukarıdaki Mysql'deki Innodb'deki B + ağacının gerçek uygulamasına bakıldığında (esas olarak birincil anahtar indeksine bakarak), B + ağacındaki bir düğüm tarafından depolanan içeriğin şu şekilde olduğunu görebilirsiniz:
- Yaprak olmayan düğümler: birincil anahtar + işaretçi
- Yaprak düğümü: veri
Öyleyse, 1K veri boyutunda bir satırımız olduğunu varsayalım, o zaman bir sayfa 16 veri depolayabilir, yani bir yaprak düğüm 16 veri depolayabilir; birincil anahtar kimliğinin bigint türü olduğunu varsayarak, uzunluğun 8B, işaretçi olduğunu varsayarak yaprak olmayan düğümlere bakın. Innodb kaynak kodundaki boyut 6B'dir, toplamda 14B, ardından 16K / 14 = 1170 (birincil anahtar + işaretçi) bir sayfada saklanabilir, sonra 2 yüksekliğinde bir B + ağacı depolayabilir: 117016 = 18720 veri , Yüksekliği 3 olan bir B + ağacının depolayabileceği veriler: 11701170 * 16 = 21902400 (on milyon düzeyli çubuk). Bu nedenle InnoDB'deki B + ağacının yüksekliği genellikle 1-3 katmandır ve bu on milyonlarca veri depolamasını karşılayabilir. Verilere bakarken, bir sayfa araması bir GÇ'yi temsil eder, bu nedenle birincil anahtar dizini üzerinden sorgulama genellikle verileri bulmak için yalnızca 1-3 GÇ işlemi gerektirir. Bu yüzden soruma cevap verdim. 1 sayfa = 16k ayarı daha uygun ve çoğu işletme için daha uygun.Elbette bu değer değiştirilebilir, yani iş zamanına göre de ayarlanabilir.
En soldaki önek ilkesi
Çalışanlar.titles limit10'dan ortak bir indeks seçimi *, concat (sağ (emp_no, 1), "-", sağ (başlık, 1), "-", sağ (from_date, 2)) oluşturmak için verileri simüle ediyoruz;
O zaman ilgili B + ağacı
Çalışanlar.titleswhereemp_no = 1'den * seçimi için, dizin kullanılabilir, çünkü yukarıdaki dizinin tüm sorgu aralıklarını kullanabilir. İlk olarak, ilk düğüm "4-r-01", 1 ile karşılaştırın < 4. Böylece, sonucun sol alt ağaçta olduğunu doğrudan belirleyebilir ve benzer şekilde, sırasıyla karşılaştırabilir ve sorgunun kapsamını kademeli olarak daraltabilirsiniz. Çalışanlar.titleswheretitle = '1' seçiminde * indeks kullanılamaz, çünkü yukarıdakileri kullanamaz.Bu nedenle, ilk düğümle karşılaştırılırken empno alanının değeri yoktur ve sol alt ağaca mı yoksa sol alt ağaca mı gidileceği belirsizdir. Sağ alt ağaç sorgulamaya devam eder. Çalışanlar.titleswheretitle = '1' veemp_no = 1 seçiminde * indeks kullanılabilir.Yukarıdaki analizimize göre, ilk düğümle karşılaştırmak için ilk olarak title = '1' koşulunu kullanın, sonuç yok ama mysql Bu sql optimize edilecektir Optimizasyondan sonra indeksin kullanılabilmesi için empno = 1 koşulu ilk sıraya konulacaktır.
