Sıradan dizin ve benzersiz dizin nasıl seçilir? CSDN blog seçimi
Yazar | .NYXX
Baş Editör | Guo Rui
Üretildi | CSDN Blogu
İnternette benzersiz indeksler ve sıradan indeksler arasında zaten çok fazla fark var, bu yüzden burada ayrıntılı olarak anlatmayacağım.Ardından, farklı iş senaryolarına göre sıradan indeksi veya benzersiz indeksi nasıl seçeceğimizi derinlemesine tartışacağız. Örneğin, bir sosyal güvenlik yönetim sistemini sürdürmek için, her sosyal güvenlik personelinin benzersiz bir kimlik numarası vardır ve iş kodunun iki çift kimlik numarası yazmaması garanti edilmiştir. Sistemin ID numarasına göre adı sorgulaması gerekiyorsa, şu SQL ifadesini çalıştıracaktır:
id_card = "xxxxxxxxxxx" olan suser'dan bir isim seçin;Bu nedenle, genellikle id_card alanında bir dizin oluşturmayı düşünün. Kimlik alanı görece büyük olduğundan, birincil anahtar olarak kullanılmaya uygun değildir Artık dizin için iki seçenek vardır, ya id_card alanı için benzersiz bir dizin oluşturun ya da sıradan bir dizin oluşturun. İşletme kodu yinelenen kimlik numaralarının yazılmayacağını garanti ettiyse, bu iki seçenek mantıksal olarak doğrudur. Ancak performans açısından, seçimin temeli ne olmalıdır? Aşağıda, iki dizinin sorgu süreci ve güncelleme süreci üzerindeki performans etkisini analiz ediyoruz.
Sorgu işlemi
Uygulamayı varsayarak InnoDB endeks organizasyonuna bir göz atalım:
t'den id'yi seçin, burada a = 3Bu sorgu ifadesinin dizin ağacındaki arama süreci aşağıdaki gibi olacaktır:
-
İlk olarak, ağacın kökünden B + ağacına doğru başlayın ve veri sayfasını bulmak için hiyerarşik bir geçiş yolu ile yaprak düğümlerini arayın.
-
Kayıtları ikili aramayla bulun.
Benzersiz dizin söz konusu olduğunda, koşulları karşılayan ilk girişi bulduktan sonra arama duracaktır (örneğin (3,300)). Sıradan dizin koşulu karşılayan bir girdi bulduktan sonra, a = 3 koşulunu karşılamayan ilk girdiyle karşılaşana kadar aramaya devam edecektir.
Farklılıklarından kaynaklanan performans farkı minimum düzeydedir. InnoDB, veri sayfalarının birimlerini okuduğu ve yazdığı için, yani bir girişi okurken, diskten okunmaz, ancak sayfa biriminde, tümü belleğe okunur. Depolama motoru sayfa sayfa okuduğundan ve yazdığından, a = 3 olan bir giriş bulunduğunda, bulunduğu veri sayfası zaten bellekte bulunur. Daha sonra, sıradan indeks için daha fazla yapılması gereken "girdinin koşulları karşılayıp karşılamadığının araştırılması ve yargılanması" işlemleri sadece bir işaretçi işlemine ve hesaplamaya ihtiyaç duyar.
Güncelleme işlemi
Bir veri sayfasının güncellenmesi gerektiğinde, veri sayfası ara bellek havuzundaysa (ara bellek havuzu), doğrudan güncellenir ve aynı anda yineleme günlüğü kaydedilir, ancak veri sayfası bellekte değilse. Tutarlılığı etkilememe gerekçesiyle InnoDB, güncelleme işlemini değişiklik arabelleğinde önbelleğe alacak ve yineleme günlüğünü kaydedecektir.
Arabelleği değiştir
Peki değişim tamponu nedir?
Ana amacı, ikincil dizinin rasgele GÇ'sini azaltmak ve işlem birleştirme etkisini elde etmek için ikincil dizindeki veri işlemlerini önbelleğe almaktır.
MySQL 5.5'ten önceki sürümlerde, yalnızca önbellek ekleme işlemi desteklendiğinden, başlangıçta ara bellek olarak adlandırılırdı, ancak sonraki sürümler daha fazla işlem türünü (ekleme, güncelleme, silme dahil işlem türleri) destekledi ve ardından değiştirmek için adı değiştirdi. tampon.
Değişim tamponunun veri yapısı, ibdata sistem tablosu alanında depolanan bir b + ağacıdır ve kök sayfası ibdata'nın dördüncü sayfasıdır (FSP_IBUF_TREE_ROOT_PAGE_NO).
En son sonucu elde etmek için değişiklik tamponundaki işlemleri orijinal veri sayfasına uygulama sürecine birleştirme adı verilir. Birleştirme zamanı, verilerin gerçekte güncellendiği zamandır ve değişiklik arabelleği, girişlerin değişiklik eylemlerini önbelleğe alır. Bir veri sayfası birleştirilmeden önce, değişiklik arabelleğine ne kadar çok değişiklik kaydedilirse (yani sayfanın ne kadar çok güncellenmesi gerekiyorsa), fayda o kadar büyük olur.
Genel olarak konuşursak, birleştirmeyi tetiklemek için esas olarak aşağıdaki işlemler vardır:
-
Bu veri sayfasını ziyaret edin;
-
Ana iş parçacığı, her saniyede veya her 10 saniyede bir birleştirme ekleme tampon işlemi gerçekleştirir;
-
Veritabanı normal olarak kapatıldığında.
Ayrıca isim değişim tamponu olmasına rağmen aslında kalıcı olabilen verilerdir yani hafızaya kopyalanır ve diske de yazılır.
Arabellek durumu görünümünü değiştir
-
segment boyutu eklemek Arabelleğin toplam boyutu (sayfa sayısı X16KB);
-
birleşmeler, birleştirilen birleştirme sayısını temsil eder;
-
birleştirilmiş işlemler: ekleme eklemek Birleştirme sayısını kaydedin;
-
silme işareti silme işlemi birleştirilir;
-
Silme güncelleme işlemi kaç kez birleştirildi?
Arabelleği değiştir arabellek havuzunu kaplar
Hafızaya okunan verilerin tampon havuzunu işgal etmesi gerekir Bu şekilde hafıza kullanımı önlenebilir ve hafıza kullanımı iyileştirilebilir.
Değişiklik tamponu, arabellek havuzunun belleğini kullanır, bu nedenle kablosuz olarak artırılamaz. İnnodb_change_buffer_max_size parametresi tarafından ayarlanır. Bu parametre, kullanılan bellek oranını gösterir. Varsayılan% 25 ve maksimum% 50'dir. Genellikle, daha fazla yazma ve daha az okuma içeren senaryolarda kullanılır Sadece ayarlanması gerekiyor.
Değişim tamponunun faydaları nelerdir?
MySQL çok sayıda DML işlemi üstlenirse, değişiklik tamponu vazgeçilmezdir.Varlığı, G / Ç tüketimini en aza indirgemek, verileri bellek yoluyla birleştirmek ve çok sayıda işlemi küçük bir G / Ç miktarına indirgemektir. O işlem, dolayısıyla güncelleme işleminin hızını artırır.
Değişiklik tamponunu açmak için hangi senaryolar uygundur?
Değişiklik tamponu, ortak bir dizin senaryosuyla sınırlıdır ve benzersiz bir dizine uygulanamaz. neden?
Çünkü varsayalım ki eklemek (3, 300) Bu giriş için, önce bu girişin tabloda görünüp görünmediğini belirlemeliyiz. Ve bu veri sayfası hafızaya okunarak belirlenmelidir. Hepsi belleğe okunduysa, belleği doğrudan güncellemek daha hızlı olacaktır ve değiştirme arabelleğini kullanmaya gerek kalmayacaktır.
Sonra InnoDB eklemek Girişin akışı nedir (3,300)?
Bu giriş için güncellenecek veri sayfası hafızadaysa:
-
Benzersiz indeks için, herhangi bir çelişki olmadığına karar verdikten sonra 2 ve 4'ün pozisyonlarını bulun, eklemek Bu değer, uygulamanın sonu
-
Sıradan dizin için, 2 ve 4'ün pozisyonlarını bulun, eklemek Bu değer, uygulamanın sonu
Bu giriş için güncellenecek veri sayfası hafızada değilse:
-
Benzersiz bir dizin için, veri sayfasını belleğe okumanız ve ardından bir çakışma olup olmadığını belirlemeniz ve ardından devam etmeniz gerekir. eklemek .
-
Sıradan dizinler için, yalnızca öğe güncelleme işlemini değişiklik tamponuna kaydetmeniz gerekir ve yürütme sona erer.
Tüm senaryolar değişiklik arabelleğini kullanamaz
Sıradan dizinler, tüm senaryolarda değişiklik tamponu kullanımından fayda sağlamazlar.Artık daha az okuyan işletmeler için, sayfaların yazıldıktan hemen sonra erişilme olasılığı nispeten küçüktür ve değişim tamponunun kullanılması en iyisidir.
Ancak, bir işletmenin güncelleme modunun yazdıktan hemen sonra sorgulama olduğu varsayılırsa, koşullar karşılansa bile, güncelleme önce değişiklik tamponuna kaydedilir, ancak veri sayfasına yakında erişileceği için birleştirme işlemi hemen tetiklenir. Bu şekilde, IO'ya rastgele erişim sayısı azalmayacak, ancak değişim tamponunun bakım maliyetini artıracaktır. Bu nedenle, benzer bir iş modeli için, değişim tamponunun bir yan etkisi vardır.
Örneğin:
T değerlerine (id1, a1), (id2, a2) ekleme yapmak istediğinizi varsayalım;
A1'in bulunduğu veri sayfasının bellekte (InnoDB arabellek havuzu) olduğu varsayılarak, eğer a2'nin olmadığı veri sayfası, şekilde gösterildiği gibi:
Sayfa1, burada a1 bellekteyse, belleği doğrudan güncelleyin;
A2'nin bulunduğu Sayfa2 hafızada değilse, değişim tamponuna "Sayfa2'ye gitmek için" kaydedin eklemek Bu bilgiler "bir satır";
Yineleme günlüğüne Sayfa1 güncelleme eylemini kaydedin;
Değişiklik tamponunu kaydedin eklemek Bu bilgi eylemi, yineleme günlüğüne kaydedilir.
Yineleme günlüğü yazmanın üçüncü ve dördüncü işlemleri diske yazmak için birleştirilir. Dolayısıyla, yürütme sürecinden, bu güncelleme bildirimini yürütmenin maliyetinin çok düşük olduğu, yalnızca iki bellek konumu yazıldığı ve sıralı olarak yazıldıkları görülebilir. Şekildeki iki kırmızı ok, arka plan işlemleridir (boşta veya gerekli olduğunda diske yazma) ve güncellemenin yanıt süresini etkilemez.
Öyleyse, sonraki okuma istekleriyle nasıl başa çıkılacağı, örneğin, t'den * seçimini yürütmek istiyoruz, burada a in (a1, a2);
a1 başlangıçta bellekteydi ve bellek daha önce güncellendi, bu nedenle doğrudan bellekten geri döner.
Sayfa2'yi okurken, Sayfa2'yi diskten belleğe okumanız ve ardından yeni bir sürüm oluşturmak ve sonucu döndürmek için işlem günlüğünü değişiklik tamponunda birleştirmeniz gerekir.
sonuç olarak
Sıradan dizinler ve benzersiz dizinler arasında sorgu yeteneklerinde hiçbir fark yoktur ve ana husus, güncellemelerin etkisidir. Genelde sıradan dizinlerin kullanılması tavsiye edilir. Özellikle mekanik bir disk kullanma senaryosunda, veri yazma hızını sağlamak için değişiklik tamponunu büyütmeye çalışın.
Sorumluluk Reddi: Bu makale, CSDN blogger ".NYXX" 'in orijinal makalesidir, orijinal bağlantı: https://blog.csdn.net/songguangfan/article/details/103059623.
