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

MySQL optimizasyonunun prensibini herkese anlatmak zorundayım, umarım bir daha hata yapmazsınız!

Yazar: Chen Chuan, herhangi bir ihlal varsa, lütfen silmek için iletişime geçin, teşekkür ederim. www.jianshu.com/p/d7665192aaaf

MySQL sorgu optimizasyonundan bahsetmişken, birçok beceri biriktirdiğinize inanıyorum: SELECT * kullanamazsınız, NULL alanları kullanmayın, makul şekilde dizinler oluşturun, alanlar için uygun veri türlerini seçin... Bu optimizasyon becerilerini gerçekten anlıyor musunuz? ? Arkasında nasıl çalıştığını anlıyor musun? Gerçek senaryolarda performans gerçekten iyileşiyor mu? Öyle düşünmüyorum. Bu nedenle, bu optimizasyon önerilerinin arkasındaki ilkeleri anlamak özellikle önemlidir.Umarım bu makale, bu optimizasyon önerilerini yeniden incelemenize ve bunları gerçek iş senaryolarında makul bir şekilde uygulamanıza olanak tanır.

MySQL mantıksal mimarisi

MySQL'in çeşitli bileşenlerinin birlikte nasıl çalıştığına dair mimari bir diyagram oluşturabilirseniz, bu MySQL sunucusunu derinlemesine anlamanıza yardımcı olacaktır. Aşağıdaki şekil MySQL'in mantıksal mimarisini göstermektedir.

MySQL mantıksal mimarisi, şu kaynaktan: Yüksek Performanslı MySQL

Genel MySQL mantıksal mimarisi üç katmana bölünmüştür.Üst katman, MySQL'e özgü olmayan istemci katmanıdır.Bağlantı işleme, yetkilendirme ve kimlik doğrulama ve güvenlik gibi işlevlerin tümü bu katmanda gerçekleştirilir.

MySQL'in temel hizmetlerinin çoğu, sorgu ayrıştırma, analiz, optimizasyon, önbelleğe alma ve yerleşik işlevler (zaman, matematik, şifreleme vb.) dahil olmak üzere orta katmandadır. Tüm çapraz depolama motoru işlevleri de bu katmanda uygulanır: saklı yordamlar, tetikleyiciler, görünümler vb.

En alttaki katman, MySQL'de veri depolama ve almadan sorumlu olan depolama motorudur. Linux altındaki dosya sistemlerine benzer şekilde, her depolama motorunun avantajları ve dezavantajları vardır. Ortadaki hizmet katmanı, API'ler aracılığıyla depolama motoruyla iletişim kurar ve bu API arabirimleri, farklı depolama motorları arasındaki farkları korur.

MySQL sorgulama işlemi

MySQL'in her zaman daha yüksek sorgu performansı elde etmesini isteriz ve en iyi yol MySQL'in sorguları nasıl optimize ettiğini ve yürüttüğünü bulmaktır. Bunu anladığınızda, şunları bulacaksınız: Pek çok sorgu optimizasyonu çalışması, MySQL optimizer'ın beklendiği gibi makul bir şekilde çalışabilmesi için aslında sadece bazı ilkeleri takip etmektir.

MySQL'e bir istek gönderirken MySQL tam olarak ne yapar?

MySQL sorgulama işlemi

İstemci/Sunucu İletişim Protokolü

MySQL istemci/sunucu iletişim protokolü "yarı çift yönlüdür": herhangi bir zamanda, sunucu istemciye veri gönderir veya istemci sunucuya veri gönderir ve bu iki eylem aynı anda gerçekleşemez. Bir uç bir mesaj göndermeye başladığında, diğer ucun yanıt vermesi için mesajın tamamını alması gerekir, bu nedenle bir mesajı küçük parçalara ayırıp bağımsız olarak göndermek zorunda değiliz ve zorunda değiliz ve bunu yapmanın bir yolu yok. akış kontrolü.

İstemci, sorgu isteğini sunucuya tek bir paket halinde gönderir, bu nedenle sorgu ifadesi çok uzun olduğunda, max_allowed_packet parametresinin ayarlanması gerekir. Ancak, sorgu çok büyükse, sunucunun daha fazla veri almayı reddedip bir istisna atacağına dikkat edilmelidir.

Aksine, sunucunun kullanıcıya yanıt verdiği veriler genellikle büyüktür ve birden çok veri paketinden oluşur. Ancak sunucu, istemcinin isteğine yanıt verdiğinde, istemci, yalnızca ilk birkaç sonucu alıp sunucunun göndermeyi durdurmasına izin vermek yerine, döndürülen sonucun tamamını almalıdır. Bu nedenle, gerçek geliştirmede, sorguyu olabildiğince basit tutmak ve yalnızca gerekli verileri döndürmek çok iyi bir alışkanlıktır.İletişimler arasındaki veri paketlerinin boyutunu ve sayısını azaltmak çok iyi bir alışkanlıktır.

sorgu önbelleği

Bir sorguyu ayrıştırmadan önce, sorgu önbelleği açıksa MySQL, sorgunun sorgu önbelleğindeki verilere ulaşıp ulaşmadığını kontrol eder. Geçerli sorgu sorgu önbelleğine ulaşırsa, kullanıcı izinleri bir kez kontrol edildikten sonra önbellekteki sonuç doğrudan döndürülür. Bu durumda sorgu ayrıştırılmaz, yürütme planı oluşturulmaz ve yürütülmez.

MySQL önbelleği bir referans tablosunda saklar (tablo olarak anlaşılmamalıdır, HashMap'e benzer bir veri yapısı olarak düşünülebilir), bir hash değeri indeksi aracılığıyla, bu hash değeri sorgunun kendisinden, mevcut veritabanına iletilir. sorgulanabilir ve istemci protokolü sürümü numarası ve sonucu etkileyebilecek diğer bilgiler. Bu nedenle, herhangi bir karakterdeki iki sorgu arasındaki fark (örneğin: boşluk, yorum) bir önbellek kaçırma ile sonuçlanacaktır.

Sorgu, mysql kitaplığında kullanıcı tanımlı işlevler, depolanmış işlevler, kullanıcı değişkenleri, geçici tablolar ve sistem tabloları içeriyorsa, sorgu sonuçları önbelleğe alınmaz. Örneğin, NOW() veya CURRENT_DATE() işlevleri, farklı sorgu süreleri nedeniyle farklı sorgu sonuçları döndürür.Örneğin, CURRENT_USER veya CONNECION_ID() içeren bir sorgu ifadesi, farklı kullanıcılar nedeniyle farklı sonuçlar döndürür. Bu tür sorgu sonuçlarını önbelleğe alın. hiçbir anlam ifade etmiyor.

Önbellek olduğu için başarısız olur.Sorgu önbelleği ne zaman başarısız olur? MySQL'in sorgu önbellek sistemi, bir sorguya dahil olan her tablonun kaydını tutar ve bu tablolar (veri veya yapı) değişirse, bu tabloyla ilişkili tüm önbelleğe alınmış veriler geçersiz olur. Bu nedenle, herhangi bir yazma işleminde MySQL, ilgili tablo için tüm önbellekleri geçersiz kılmalıdır. Sorgu önbelleği çok büyük veya parçalı ise bu işlem çok fazla sistem tüketimine hatta sistemin bir süreliğine donmasına neden olabilir. Ve sisteme sorgu önbelleğinin ek tüketimi sadece yazma işlemlerinde değil, aynı zamanda okuma işlemlerinde de:

SQL ifadesi önbelleğe asla ulaşmasa bile, herhangi bir sorgu ifadesi başlamadan önce kontrol edilmelidir.
Sorgu sonuçları önbelleğe alınabilirse, yürütme tamamlandıktan sonra sonuçlar önbellekte saklanacak ve bu da ek sistem tüketimi getirecektir.

Buna dayanarak şunu bilmeliyiz ki sorgu önbelleğe alma her koşulda sistem performansını iyileştirmeyecektir.Önbelleğe alma ve geçersiz kılma ek tüketim getirecektir.Sadece önbelleğin getirdiği kaynak tasarrufu kendi tükettiği kaynaklardan daha fazla olduğunda performans getirecektir. sisteme. terfi etmek. Ancak önbelleği açmanın bir performans iyileştirmesi getirip getirmeyeceğinin nasıl değerlendirileceği çok zor bir iştir ve bu makalenin kapsamı dışındadır. Sistemin bazı performans sorunları varsa, sorgu önbelleğini açmayı deneyebilir ve veritabanı tasarımında aşağıdaki gibi bazı optimizasyonlar yapabilirsiniz:

Bir büyük tabloyu birden çok küçük tabloyla değiştirin, fazla tasarlamamaya dikkat edin
Dairesel tekli kesici uç yerine toplu kesici uç
Önbellek alanının boyutunu makul bir şekilde kontrol edin.Genel olarak, boyutu birkaç on megabayta ayarlamak daha uygundur.
Bir sorgu ifadesinin SQL_CACHE ve SQL_NO_CACHE aracılığıyla önbelleğe alınması gerekip gerekmediğini kontrol edebilirsiniz.

Son bir tavsiye, özellikle yoğun yazma gerektiren uygulamalar için sorgu önbelleğini hafifçe açmamaktır. Gerçekten yardımcı olamıyorsanız, query_cache_type öğesini DEMAND olarak ayarlayabilirsiniz, o zaman yalnızca SQL_CACHE'ye katılan sorgular önbelleğe alınır ve diğer sorgular olmaz, böylece hangi sorguların önbelleğe alınması gerektiğini özgürce kontrol edebilirsiniz.

Elbette, sorgu önbelleği sisteminin kendisi çok karmaşıktır ve buradaki tartışma, yalnızca küçük bir kısımdır, diğer daha derinlemesine konular, örneğin: önbellek belleği nasıl kullanır? Hafızanın parçalanması nasıl kontrol edilir? İşlem sorgu önbelleğini nasıl etkiler vb., okuyucular ilgili bilgileri kendileri okuyabilir.

Ayrıştırma ve Ön İşleme

MySQL, SQL ifadelerini anahtar kelimelere göre ayrıştırır ve karşılık gelen bir ayrıştırma ağacı oluşturur. Bu süreçte, ayrıştırıcı temel olarak dilbilgisi kurallarını doğrular ve ayrıştırır. Örneğin, SQL'de yanlış anahtar kelimelerin kullanılıp kullanılmadığı veya anahtar kelimelerin sırasının doğru olup olmadığı vb. Önişleme ayrıca ayrıştırma ağacının MySQL kurallarına göre geçerli olup olmadığını kontrol edecektir. Örneğin, sorgulanacak veri tablosunun ve veri sütununun var olup olmadığını kontrol edin vb.

sorgu optimizasyonu

Önceki adımlar tarafından oluşturulan sözdizimi ağacı geçerli kabul edilir ve optimize edici tarafından bir sorgu planına dönüştürülür. Çoğu durumda, bir sorgu birçok şekilde yürütülebilir ve son olarak ilgili sonuçları döndürebilir. Optimize edicinin rolü, aralarında en iyi yürütme planını bulmaktır.

MySQL, belirli bir yürütme planı kullanarak bir sorgunun maliyetini tahmin etmeye çalışan ve en düşük maliyetli olanı seçen maliyet tabanlı bir optimize edici kullanır. MySQL'de, mevcut sorgunun hesaplanmasının maliyeti, mevcut oturumun last_query_cost değeri sorgulanarak elde edilebilir.

MySQL kodu

mysql > t_message limit 10'dan * seçin; ... sonuç kümesini atlayarak mysql > 'last_query_cost' gibi durumu göster; +-------+-------------+ | Değişken_adı | Değer | +-------+-------------+ | Last_query_cost | 6391.799000 | +-------+-------------+

Örnekteki sonuçlar, optimize edicinin yukarıdaki sorguyu tamamlamak için yaklaşık 6391 rastgele veri sayfası araması alacağını düşündüğünü göstermektedir. Bu sonuç, tablo veya dizin başına sayfa sayısı, dizinin önemliliği, dizin ve veri satırlarının uzunluğu, dizinin dağılımı vb. dahil olmak üzere bazı sütun istatistiklerine dayalı olarak hesaplanır.

MySQL'in yanlış yürütme planını seçmesinin birçok nedeni vardır, örneğin hatalı istatistikler, kontrolü altında olmayan işlemlerin (kullanıcı tanımlı işlevler, saklı prosedürler) maliyetini dikkate almamak ve MySQL'in optimal olduğunu düşündüğü şeyin bizimkinden farklı olması gibi. Aynı (yürütme süresinin mümkün olduğunca kısa olmasını istiyoruz, ancak MySQL değeri maliyetin düşük olduğunu düşündüğünü seçiyor, ancak düşük maliyet, kısa yürütme süresi anlamına gelmiyor) vb.

MySQL'in sorgu iyileştiricisi, en uygun yürütme planı oluşturmak için birçok optimizasyon stratejisi kullanan çok karmaşık bir bileşendir:

Tablonun ilişkilendirme sırasını yeniden tanımlayın (sorgu ile birden fazla tablo ilişkilendirildiğinde, SQL'de belirtilen sırayı mutlaka takip etmez, ancak ilişkilendirme sırasını belirtmek için bazı püf noktaları vardır)
MIN() ve MAX() işlevlerini optimize edin (bir sütunun minimum değerini bulmak için, sütunun bir dizini varsa, yalnızca B+Tree dizininin en sol ucunu bulmanız gerekir, aksi takdirde maksimum değeri bulabilirsiniz, özel ilke için aşağıya bakın)
Sorguyu erken sonlandırın (örneğin: Limit kullanılırken, yeterli sayıda sonuç kümesi bulunduğunda sorgu hemen sonlandırılır)
Optimize edilmiş sıralama (MySQL'in eski versiyonunda iki transferli sıralama kullanılır, yani önce satır işaretçisi ve sıralanacak alan okunur, hafızada sıralanır ve daha sonra sıralama sonucuna göre veri satırı okunurken, yeni sürüm, tek bir Alt aktarım sıralaması kullanır, yani tüm veri satırlarını bir kerede okur ve ardından belirli bir sütuna göre sıralama yapar.G/Ç yoğun uygulamalar için verimlilik çok daha yüksek olacaktır)

MySQL'in sürekli gelişimi ile, optimize edici tarafından kullanılan optimizasyon stratejileri de sürekli gelişiyor.İşte sadece birkaç yaygın ve anlaşılması kolay optimizasyon stratejisi.Diğer optimizasyon stratejileri için bunları kendiniz kontrol edebilirsiniz.

sorgu yürütme motoru

Ayrıştırma ve optimizasyon aşamaları tamamlandıktan sonra, MySQL ilgili bir yürütme planı oluşturacak ve sorgu yürütme motoru, sonucu elde etmek için yürütme planında verilen talimatları kademeli olarak yürütecektir. Yürütme sürecinin tamamındaki işlemlerin çoğu, depolama motoru tarafından uygulanan ve işleyici API'leri olarak adlandırılan arabirimler çağrılarak yapılır. Sorgu işlemindeki her tablo, bir işleyici örneği ile temsil edilir. Aslında MySQL, sorgu optimizasyonu aşamasında her tablo için bir işleyici örneği oluşturur.Optimizer, tablonun tüm sütun adları, dizin istatistikleri vb. dahil olmak üzere bu örneklerin arayüzüne göre tablo hakkında ilgili bilgileri elde edebilir. Depolama motoru arabirimi çok zengin işlevler sağlar, ancak alt katmanda yalnızca düzinelerce arabirim vardır ve bu arabirimler yapı taşları gibi bir sorgunun işlemlerinin çoğunu tamamlar.

Sonucu istemciye döndür

Sorgu yürütmenin son aşaması, sonuçları istemciye döndürmektir. Hiçbir veri sorgulanmasa bile MySQL, sorgudan etkilenen satır sayısı ve yürütme süresi gibi sorguyla ilgili bilgileri yine de döndürür.

Sorgu önbelleğe alma açıksa ve sorgu önbelleğe alınabilirse, MySQL ayrıca sonucu önbellekte saklar.

Sonuç kümesini istemciye döndürmek artımlı ve adım adım bir işlemdir. İlk sonuç oluşturulduğunda MySQL'in sonuç kümesini yavaş yavaş istemciye döndürmeye başlaması mümkündür. Bu sayede sunucunun çok fazla sonuç depolamasına gerek kalmaz ve çok fazla bellek tüketir ve ayrıca istemcinin döndürülen sonuçları ilk seferde almasına izin verir. Unutulmamalıdır ki, sonuç kümesindeki her satır, 'de açıklanan iletişim protokolünü karşılayan bir veri paketi olarak gönderilecek ve ardından TCP protokolü aracılığıyla iletilecektir.İletimi sırasında MySQL veri paketleri önbelleğe alınabilir ve daha sonra gönderilebilir. partiler halinde.

Geri dönün ve genellikle 5 adıma bölünmüş olan MySQL'in tüm sorgu yürütme sürecini özetleyin:

İstemci MySQL sunucusuna bir sorgu isteği gönderir
Sunucu önce sorgu önbelleğini kontrol eder ve önbelleğe isabet ederse hemen önbellekte depolanan sonucu döndürür. Aksi takdirde bir sonraki aşamaya geçin
Sunucu, SQL ayrıştırma, ön işleme gerçekleştirir ve optimize edici ilgili yürütme planını oluşturur.
Yürütme planına göre MySQL, sorguyu yürütmek için depolama motorunun API'sini çağırır.
Sorgu sonucunu önbelleğe alırken sonucu istemciye döndür

Performans Optimizasyon Önerileri

Bu kadar okuduktan sonra bazı optimizasyon yöntemleri vermeyi bekleyebilirsiniz, evet, aşağıda 3 farklı açıdan bazı optimizasyon önerileri verilecektir. Ama bekleyin, önce size bir tavsiye daha var: Bu makalede tartışılanlar da dahil olmak üzere optimizasyon hakkında gördüğünüz "mutlak gerçeği" dinlemeyin, gerçek iş senaryolarında test ederek sizi doğrulayın. tepki süreleri.

Şema Tasarımı ve Veri Tipi Optimizasyonu

Sadece küçük ve seçmesi kolay veri türleri ilkesini izleyin. Daha küçük veri türleri genellikle daha hızlıdır, daha az disk ve bellek kullanır ve işlenmesi için daha az CPU döngüsü gerektirir. Daha basit veri türleri, hesaplamak için daha az CPU döngüsü gerektirir.Örneğin, tamsayılar karakter işlemlerinden daha ucuzdur, bu nedenle ip adreslerini depolamak için tamsayılar ve dizeler yerine zamanları depolamak için DATETIME'lar kullanılır.

Hataları anlaması kolay olabilecek birkaç ipucu:

Genel olarak konuşursak, NULL olabilecek bir sütunu NULL DEĞİL olarak değiştirmek çok fazla performansa yardımcı olmaz, ancak sütunda bir dizin oluşturmayı planlıyorsanız, sütunu NULL DEĞİL olarak ayarlamanız gerekir.
INT(11) gibi tamsayı türleri için bir genişlik belirtmek hiçbir şey yapmaz. INT, depolama alanı olarak 16'yı kullanır, ardından temsil aralığı belirlenir, dolayısıyla INT(1) ve INT(20) depolama ve hesaplama için aynıdır.
İMZASIZ, pozitif sayıların üst sınırını kabaca iki katına çıkaran negatif değerlere izin verilmediği anlamına gelir. Genel olarak TINYINT depolama aralığı gibi, DECIMAL veri tipini kullanmaya pek gerek yoktur. Mali verileri saklamanız gerektiğinde bile BIGINT'i kullanmaya devam edebilirsiniz. Örneğin, on binde birine kadar doğru olmanız gerekiyorsa, verileri bir milyonla çarpabilir ve ardından 4 bayt depolama alanı kullanmak için TIMESTAMP ve 8 bayt depolama alanı kullanmak için DATETIME kullanabilirsiniz. Bu nedenle TIMESTAMP yalnızca DATETIME ile gösterilen aralıktan çok daha küçük olan 1970 - 2038 yıllarını temsil edebilir ve TIMESTAMP değeri farklı zaman dilimlerine göre değişir.
Çoğu durumda bir numaralandırma türü kullanmaya gerek yoktur, dezavantajlardan biri, numaralandırmanın dizelerinin listesinin sabit olmasıdır, dizeleri ekleme ve çıkarma (numaralandırma seçenekleri) ALTER TABLE (yalnızca sonuna öğeler ekliyorsa) kullanmalıdır. liste, tabloyu yeniden oluşturmaya gerek yok).
Şemada çok fazla sütun olmamalıdır. Bunun nedeni, depolama motorunun API'sinin, sunucu katmanı ile depolama motoru katmanı arasında satır arabellek formatı aracılığıyla veri kopyalaması ve ardından arabellek içeriğinin kodunu sunucu katmanındaki her sütuna çözmesi gerekmesidir.Bu dönüştürme işlemi çok pahalıdır. Çok fazla sütun varsa ve gerçekte az sayıda sütun kullanılıyorsa, yüksek CPU kullanımına neden olabilir.
ALTER TABLE büyük tablolar için çok zaman alıcıdır.MySQL'in tablo sonuçlarını değiştiren işlemlerin çoğunu gerçekleştirme şekli, yeni bir yapıya sahip boş bir tablo oluşturmak, eski tablodaki tüm verileri bulmak, onu tabloya eklemektir. yeni tabloyu ve ardından eski tabloyu silin. Özellikle hafıza yetersiz olduğunda ve tablo çok büyük olduğunda ve hala büyük indeksler olduğunda, daha uzun sürecektir. Tabii ki, bu sorunu çözmek için bazı garip hileler var, ilgileniyorsanız kendiniz kontrol edebilirsiniz.

Yüksek performanslı dizinler oluşturun

Dizin oluşturma, MySQL sorgu performansını iyileştirmenin önemli bir yoludur, ancak çok fazla dizin, yüksek disk kullanımına ve yüksek bellek kullanımına yol açarak uygulamanın genel performansını etkileyebilir. Olaydan sonra dizin eklemekten kaçınmalısınız, çünkü sorunu bulmak için çok fazla SQL izlemeniz gerekebilir ve bir dizin ekleme süresi, başlangıçta bir dizin eklemek için gereken süreden çok daha uzun olmalıdır. indeks eklemenin de çok teknik olduğu görülüyor. .

Aşağıdakiler, size yüksek performanslı dizinler oluşturmaya yönelik bir dizi stratejiyi ve her bir stratejinin bunun arkasında nasıl çalıştığını gösterecektir. Ancak bundan önce, indeksleme ile ilgili bazı algoritmaları ve veri yapılarını anlamak, aşağıdakileri daha iyi anlamanıza yardımcı olacaktır.

Endeksle ilgili veri yapıları ve algoritmalar

Genellikle bahsettiğimiz dizin, ilişkisel veritabanlarında veri bulmak için en yaygın kullanılan ve etkili dizin olan B-Tree dizinine atıfta bulunur ve çoğu depolama motoru bu dizini destekler. B-Tree terimi, MySQL'in CREATE TABLE veya diğer ifadelerde bu anahtar sözcüğü kullanması nedeniyle kullanılır, ancak aslında farklı depolama motorları farklı veri yapıları kullanabilir.Örneğin, InnoDB B+Tree kullanır.

B+Tree'deki B, denge anlamına gelen denge anlamına gelir. Unutulmamalıdır ki B+ ağaç indeksi belirli bir anahtar değeri ile belirli bir satırı bulamaz.Sadece aranan veri satırının bulunduğu sayfayı bulur.Ardından veritabanı sayfayı hafızaya okuyacak, ardından hafızada arama yapacak, ve son olarak aradığınız verileri alın.

B+Tree'yi tanıtmadan önce ikili arama ağacına bir göz atalım.Klasik bir veri yapısıdır.Sol alt ağacın değeri her zaman kökün değerinden küçüktür ve sağ alt ağacın değeri her zaman daha büyüktür. kökün değeri aşağıdaki gibidir Şekil . Bu dersin ağacında 5 değerine sahip bir kayıt bulmak istiyorsanız, genel işlem şudur: önce değeri 6 olan, yani 5'ten büyük olan kökü bulun, böylece sol alt ağacı bulun, 3'ü bulun ve 5, 3'ten büyükse, 3 Ağacın doğru çocuğunu bulun, toplam 3 kez bulun. Aynı şekilde 8 değerine sahip bir kayıt ararsanız 3 kez aramanız gerekir. Bu nedenle, bir ikili arama ağacındaki ortalama arama sayısı (3 + 3 + 3 + 2 + 2 + 1) / 6 = 2,3 kezdir ve sıralı bir aramada değeri olan bir kaydı bulmak için yalnızca 1 arama yeterlidir. / 2, ancak arama değeri 8 kaydın 6 kez olması gerekir, bu nedenle sıralı aramanın ortalama arama süreleri: (1 + 2 + 3 + 4 + 5 + 6) / 6 = 3,3 kez, çünkü çoğu durumda ortalama arama ikili arama ağacının hızı, Sıralı aramalardan daha hızlıdır.

İkili Arama Ağacı ve Dengeli İkili Ağaç

İkili arama ağacı isteğe bağlı olarak oluşturulabildiğinden, aynı değer, Şekil 2'de gösterildiği gibi ikili arama ağacını oluşturabilir. Açıktır ki, bu ikili ağacın sorgu verimliliği, sıralı aramaya benzer. İkili arama numarasının en yüksek sorgu performansını istiyorsanız, dengelenecek ikili arama ağacına, yani dengeli bir ikili ağaç (AVL ağacı) gerekir.

Dengeli bir ikili ağaç, önce ikili arama ağacının tanımını karşılamalıdır ve ikinci olarak, herhangi bir düğümün iki alt ağacı arasındaki yükseklik farkı 1'den büyük olmamalıdır. Açıkçası, grafik dengeli bir ikili ağaç tanımını karşılamazken, grafik 1 dengeli bir ikili ağaçtır. Dengeli bir ikili ağacın arama performansı nispeten yüksektir (en iyi performans en uygun ikili ağaçtır) Sorgu performansı ne kadar iyi olursa, bakım maliyeti de o kadar yüksek olur. Örneğin, Şekil 1'deki dengeli ikili ağaçta, kullanıcının 9 değerinde yeni bir düğüm eklemesi gerektiğinde, aşağıdaki değişikliklerin yapılması gerekir.

Dengeli İkili Ağaç Döndürme

Eklenen ağacı bir sola döndürme işlemiyle dengeli bir ikili ağaca değiştirmek en basit durumdur ve pratik uygulama senaryolarında birden çok kez döndürmek gerekebilir. Buraya kadar bir soru düşünebiliriz.Dengeli ikili ağacın arama verimliliği fena değil, uygulama da çok basit ve ilgili bakım maliyeti kabul edilebilir.MySQL indeksi neden doğrudan dengeli ikili ağacı kullanmıyor?

Veritabanındaki veriler arttıkça, dizinin boyutu da artar ve hepsini bellekte depolamak imkansızdır, bu nedenle dizin genellikle diskte bir dizin dosyası biçiminde saklanır. Bu durumda, dizin arama işlemi sırasında disk G/Ç tüketimi gerçekleşir ve G/Ç erişimi tüketimi, bellek erişiminden birkaç kat daha yüksektir. Milyonlarca düğümü olan bir ikili ağacın derinliğini hayal edebiliyor musunuz? Diske bu kadar büyük bir derinliğe sahip bir ikili ağaç yerleştirilirse, bir düğüm her okunduğunda, bir disk G/Ç okuması gerekir ve tüm arama süresi açıkça kabul edilemez. Peki, arama sürecinde G/Ç erişimlerinin sayısı nasıl azaltılır?

Etkili bir çözüm, ağacın derinliğini azaltmak ve ikili ağacı bir m-ary ağacına (çok yönlü arama ağacı) dönüştürmektir ve B+Ağacı çok yönlü bir arama ağacıdır. B+Tree'yi anlarken, yalnızca en önemli iki özelliğini anlamanız gerekir: İlk olarak, tüm anahtar kelimeler (veri olarak anlaşılabilir) yaprak düğümlerinde (Yaprak Sayfası), yaprak olmayan düğümlerde (İndeks Sayfası) ve Gerçek olmayan düğümlerde saklanır. veri depolanır, tüm kayıt düğümleri, anahtar değer boyutu sırasına göre aynı yaprak düğüm katmanında depolanır. İkincisi, tüm yaprak düğümleri işaretçiler tarafından bağlanır. Aşağıdaki şekil, yüksekliği 2 olan basitleştirilmiş bir B+Ağacı göstermektedir.

B+Tree'yi Basitleştirin

Bu iki özellik nasıl anlaşılır? MySQL, her düğümün boyutunu bir sayfanın tamsayısına ayarlar (nedeni aşağıda açıklanacaktır), yani, düğüm alanının boyutu kesin olduğunda, her düğüm daha fazla dahili düğüm depolayabilir, böylece her düğüm depolayabilir. daha fazla dahili düğüm İndeks aralığı daha büyük ve daha kesindir. Tüm yaprak düğümler için işaretçi bağlantılarını kullanmanın avantajı, menzil erişimi yapabilmeleridir.Örneğin yukarıdaki şekilde, 20'den büyük ancak 30'dan küçük kayıtları arıyorsanız, yalnızca düğüm 20'yi bulmanız gerekir ve sırayla 25 ve 30'u bulmak için işaretçiyi geçebilir. Bağlantı işaretçisi yoksa, aralık araması yapılamaz. Bu aynı zamanda MySQL'in dizin depolama yapısı olarak B+Tree'yi kullanmasının önemli bir nedenidir.

MySQL neden diskin depolama ilkesinin anlaşılmasını gerektiren düğüm boyutunu sayfanın tamsayı katına ayarlar. Diskin erişim hızı, ana belleğinkinden çok daha yavaştır.Mekanik hareket kaybına ek olarak (özellikle sıradan mekanik sabit diskler), diskin erişim hızı genellikle ana belleğin milyonda biridir. Disk G/Ç'sini en aza indirmek için, disk genellikle isteğe bağlı olarak kesinlikle okunmaz, ancak her seferinde ileri okuyacaktır.Sadece bir bayt gerekli olsa bile, disk bu konumdan başlayacak ve sırayla belirli bir uzunlukta okuyacaktır. veri belleğe geri döner ve ileri okumanın uzunluğu Genel olarak, sayfaların bir tamsayıdır.

Sayfa, bilgisayar tarafından yönetilen belleğin mantıksal bir bloğudur.Donanım ve işletim sistemi genellikle ana belleği ve disk deposunu eşit boyuttaki bitişik bloklara böler. Her depolama bloğuna sayfa denir (birçok işletim sisteminde bir sayfanın boyutu genellikle 4K'dır). ). Sayfa birimlerinde ana bellek ve disk değişimi verileri. Program tarafından okunacak veri ana bellekte olmadığında bir sayfa hatası istisnası tetiklenecektir.Bu sırada sistem diske bir disk okuma sinyali gönderecek ve disk, diskin başlangıç konumunu bulacaktır. veri ve bir veya birkaç sayfayı sürekli olarak okuyun Belleğe yükleyin, ardından anormal bir şekilde geri dönün ve program çalışmaya devam eder.

MySQL, bir düğümün boyutunu bir sayfaya eşit olarak ayarlamak için disk önceden okuma ilkesini akıllıca kullanır, böylece her düğüm yalnızca bir G/Ç ile tam olarak yüklenebilir. Bu amaca ulaşmak için, her yeni düğüm oluşturulduğunda, doğrudan bir sayfa alanı için geçerlidir, bu da bir düğümün bir sayfada fiziksel olarak depolanmasını ve bilgisayar depolama tahsisinin sayfaya göre hizalanmasını sağlar, bu da okuma gerçekleştirir. bir düğüm Sadece bir G/Ç. B+Tree'nin yüksekliğinin h olduğunu varsayarsak, bir alma işlemi en fazla h-1I/O (kök düğüm yerleşik belleği) gerektirir ve karmaşıklık O(h)=O(logMN) olur. Pratik uygulama senaryolarında, M genellikle büyüktür, genellikle 100'ü aşar, bu nedenle ağacın yüksekliği genellikle küçüktür, genellikle 3'ten fazla değildir.

Son olarak, kısaca B+Tree düğümünün çalışmasını anlayın ve bir bütün olarak dizinin bakımı hakkında genel bir anlayışa sahip olun.Dizin, sorgu verimliliğini büyük ölçüde iyileştirebilse de, dizini korumak hala çok maliyetlidir, bu nedenle endeksin makul bir şekilde oluşturulması da özellikle önemlidir.

Yine de yukarıdaki ağacı örnek alarak, her düğümün yalnızca 4 iç düğümü depolayabileceğini varsayıyoruz. Yapılacak ilk şey, aşağıdaki şekilde gösterildiği gibi ilk düğümü 28 yerleştirmektir.

Ne yaprak sayfası ne de dizin sayfası dolu

Daha sonra bir sonraki düğümü 70 ekleyin. Index Sayfasında sorgulama yaptıktan sonra, yaprak düğümün 50 ile 70 arasında eklenmesi gerektiğini ancak yaprak düğümün dolu olduğunu biliyoruz.Şu anda, bir bölme işlemi yapmak gerekiyor. yaprak düğüm başlangıç noktası 50'dir, bu nedenle yaprak düğümler aşağıdaki şekilde gösterildiği gibi ara değere göre bölünür.

Yaprak Sayfa bölünmüş

Son olarak, bir düğüm 95 eklenir.Şu anda, hem Dizin Sayfası hem de Yaprak Sayfası doludur ve aşağıdaki şekilde gösterildiği gibi iki bölme gereklidir.

Yaprak Sayfası ve Dizin Sayfası ayrımı

Bölündükten sonra, sonunda böyle bir ağaç oluşur.

son ağaç

Dengeyi sağlamak için B+Tree'nin yeni eklenen değerler için çok sayıda sayfa bölme işlemi yapması gerekiyor ve sayfa bölme işlemi için G/Ç işlemleri gerekiyor.Sayfa bölme işlemlerini olabildiğince azaltmak için B+Tree de benzer çözümler sunuyor Dengeli ikili ağaç için döndürme işlevi. LeafPage dolduğunda ancak sol ve sağ kardeş düğümleri dolu olmadığında, B+Tree bölme işlemini yapmak için acele etmez, kaydı geçerli sayfanın kardeş düğümüne taşır. Normalde, döndürme işlemleri için önce sol kardeş kontrol edilir. Örneğin, yukarıdaki ikinci örnekte, 70 eklerken sayfa bölme değil, sola döndürme yapacak.

Sola dönüş işlemi

Sayfa bölme işlemleri döndürme işlemleriyle en aza indirilebilir, böylece dizin bakımı sırasında disk G/Ç işlemlerini azaltır ve dizin bakım verimliliğini artırır. Düğümlerin silinmesi ve düğüm türlerinin eklenmesi, burada açıklanmayacak olan döndürme ve bölme işlemlerini hala gerektirdiğine dikkat edilmelidir.

yüksek performans stratejisi

Yukarıdakiler aracılığıyla, B+Tree'nin veri yapısı hakkında genel bir anlayışa sahip olduğunuza inanıyorum, ancak MySQL'deki dizin, verilerin depolanmasını nasıl organize ediyor? Basit bir örnekle açıklamak gerekirse, aşağıdaki veri tablosu varsa:

MySQL kodu

TABLO OLUŞTUR İnsanlar( last_name varchar(50) boş değil, first_name varchar(50) boş değil, dob tarihi boş değil, cinsiyet enum(`m`,`f`) boş değil, anahtar(soy_ad, ad_ad, dob) );

Tablodaki her veri satırı için dizin, son_ad, ad ve dob sütunlarının değerlerini içerir.Aşağıdaki şekil, dizinin veri depolamayı nasıl düzenlediğini gösterir.

İndeksler veri depolamayı nasıl organize eder: Yüksek Performanslı MySQL

Dizinin ilk olarak birinci alana göre sıralandığı, isimler aynı olduğunda üçüncü alana yani doğum tarihine göre sıralandığı görülmektedir. endeksin "en sol ilkesi".

1. MySQL dizinleri kullanmaz: bağımsız olmayan sütunlar

"Ayrı sütun", dizine alınmış bir sütunun bir ifadenin parçası olamayacağı veya bir işlevin argümanı olamayacağı anlamına gelir. örneğin:

id + 1 = 5 olduğu yerden * seçin

id = 4'e eşdeğer olduğunu görmek kolaydır, ancak MySQL bu ifadeyi otomatik olarak ayrıştıramaz ve aynısı işlevleri kullanmak için de geçerlidir.

2. Önek dizini

Sütun çok uzunsa, genellikle başlangıçta bazı karakterleri dizine ekleyebilirsiniz, bu da dizin alanından etkili bir şekilde tasarruf edebilir ve dizin verimliliğini artırabilir.

3. Çok sütunlu dizinler ve dizin sırası

Çoğu durumda, birden çok sütunda bağımsız dizinler oluşturmak, sorgu performansını iyileştirmez. Nedeni çok basit.MySQL, hangi indeksin seçileceğinin sorgulama için daha verimli olduğunu bilmiyor, bu yüzden MySQL 5.0 gibi eski versiyonda, bir sütun için rastgele bir indeks seçecek ve yeni versiyon şu stratejiyi benimseyecek: indekslerin birleştirilmesi Basit bir örnek için, bir film döküm tablosunda, hem actor_id hem de film_id sütunlarında bağımsız dizinler kurulur ve ardından aşağıdaki sorgu vardır:

aktör_id = 1 veya film_id = 1 olan film_actor içinden film_id,actor_id seçin

MySQL'in eski sürümü rastgele bir dizin seçecek, ancak yeni sürüm aşağıdaki optimizasyonları yapıyor:

aktör_id = 1 olan film_actor içinden film_id,actor_id öğesini seçin hepsini birleştirmek film_id = 1 ve act_id olan film_actor'dan film_id,actor_id'yi seçin < > 1

Birden çok dizin kesiştiğinde (birden çok AND koşulu), ilgili tüm sütunları içeren bir dizin, genellikle birden çok bağımsız dizine tercih edilir.
Ortak işlem için birden fazla dizin olduğunda (birden çok VEYA koşulu), sonuç kümesini birleştirme ve sıralama gibi işlemler, özellikle bazı dizinler çok seçici olmadığında ve iade edilmesi gerektiğinde, çok fazla CPU ve bellek kaynağı tüketir. büyük miktarda veri birleştirildiğinde daha yüksek. Bu durumda, tam bir tablo taraması yapmak daha iyidir.

Bu nedenle açıklama yaparken index merge (Ekstra alanında union kullanarak) olduğunu fark ederseniz sorgu ve tablo yapısının zaten optimal olup olmadığını kontrol etmelisiniz.Sorgu ve tablo ile ilgili bir sorun yoksa sadece dizin yapısının çok kötü olduğu anlamına gelir.Bir dizinin uygunluğu dikkatlice düşünülmeli, muhtemelen tüm ilgili sütunları içeren çok sütunlu bir dizin daha uygun olacaktır.

İndeksin veri depolamayı nasıl organize ettiğinden daha önce bahsetmiştik.Şekilden de anlaşılacağı gibi, çok sütunlu bir indeks kullanıldığında, indeksin sırası sorgu için çok önemlidir.Açıkçası, daha seçici alanlar, daha seçici alanların önüne yerleştirilmelidir. Bu şekilde, koşulları karşılamayan verilerin çoğu ilk alandan filtrelenebilir.

Dizin seçiciliği, benzersiz dizin değerlerinin veri tablosundaki toplam kayıt sayısına oranını ifade eder.Seçicilik ne kadar yüksek olursa, sorgu verimliliği o kadar yüksek olur, çünkü daha yüksek seçiciliğe sahip dizin, MySQL'in sorgu sırasında daha fazla satırı filtrelemesine izin verir. Benzersiz bir dizinin seçiciliği, en iyi dizin seçiciliği ve en iyi performans olan 1'dir.

İndeks seçiciliği kavramını anladıktan sonra, hangi alanın daha yüksek seçiciliğe sahip olduğunu belirlemek zor değildir.Sadece kontrol edin, örneğin:

personel_kimliği = 2 ve müşteri_kimliği = 584 olduğunda ödemeden * SEÇİN

Bir (personel_kimliği, müşteri_kimliği) dizini mi oluşturulmalı yoksa sipariş tersine mi çevrilmeli? Hangi alanın seçiciliği 1'e yakınsa ve hangi alan önce dizine eklenirse, aşağıdaki sorguyu yürütün.

staff_id_selectivity olarak count(farklı staff_id)/count(*) öğesini seçin, say(farklı müşteri_kimliği)/say(*), müşteri_kimliği_seçiciliği olarak, ödemeden say(*)

Çoğu durumda bu prensibi kullanmanın yanlış bir tarafı yoktur, ancak yine de verilerinizde bazı özel durumlar olup olmadığının farkında olun. Basit bir örnek için belirli bir kullanıcı grubu altında işlem yapan kullanıcıların bilgilerini sorgulamak için:

user_group_id = 1 ve trade_amount olan ticaretten user_id seçin > 0

MySQL, bu sorgu için indeksi (user_group_id, trade_amount) seçti.Özel durumu dikkate almazsanız, bu sorun değil gibi görünüyor, ancak gerçek durum, bu tablodaki verilerin çoğunun eski sistemden taşındığıdır. Sistemin verileri uyumsuzdur, bu nedenle eski sistemden taşınan verilere varsayılan bir kullanıcı grubu atanır. Bu durumda, dizin tarafından taranan satır sayısı temel olarak tam tablo taramasıyla aynıdır ve dizin herhangi bir rol oynamaz.

Genel olarak konuşursak, pratik kurallar ve çıkarımlar çoğu durumda faydalıdır ve geliştirme ve tasarımımıza rehberlik edebilir, ancak fiili durum genellikle daha karmaşıktır ve fiili iş senaryolarındaki bazı özel durumlar tüm tasarımınızı mahvedebilir.

4. Çoklu Menzil Koşullarından Kaçının

Gerçek geliştirmede, belirli bir süre içinde oturum açan kullanıcıları sorgulamak gibi, genellikle birden çok aralık koşulu kullanırız:

kullanıcıyı seçin.* kullanıcıdan login_time > '2017-04-01' ve 18-30 yaş arası;

Bu sorguda bir sorun var: login_time sütunu ve age sütunu olmak üzere iki aralık koşulu var, MySQL login_time sütun dizinini veya yaş sütun dizinini kullanabilir, ancak ikisini birden kullanamaz.

5. Kapsama Endeksi

Bir indeks sorgulanması gereken tüm alanların değerlerini içeriyor veya kapsıyorsa, sorgulamak için tabloya geri dönmeye gerek yoktur, buna örtü indeksi denir. Kapsama dizinleri çok kullanışlı araçlardır ve sorguların yalnızca dizini taraması gerektiğinden performansı büyük ölçüde artırabilir, bu da birçok fayda sağlar:

İndeks girişi, veri satır boyutundan çok daha küçüktür.Sadece indeks okunursa, veri erişim miktarı büyük ölçüde azalır.
Dizin, sütun değerleri sırasına göre depolanır; bu, G/Ç yoğun aralık sorguları için her bir veri satırını diskten rastgele okumaktan çok daha az GÇ'dir.

6. Sıralamak için dizin taramasını kullanın

MySQL'in sıralı bir sonuç kümesi üretmenin iki yolu vardır, biri sonuç kümesini sıralamak, diğeri ise indeks sırasına göre taranarak elde edilen sonuçların doğal olarak sıralanmasıdır. Açıklama sonucundaki type sütununun değeri index ise, sıralama için index taraması kullanıldığı anlamına gelir.

Dizinin kendisini taramak hızlıdır çünkü yalnızca bir dizin kaydının sonraki bitişik kayda taşınması gerekir. Ancak, indeksin kendisi sorgulanması gereken tüm sütunları kapsayamıyorsa, o zaman bir indeks kaydı her tarandığında, ilgili satır tekrar tabloya sorgulanmalıdır. Bu okuma işlemi temelde rasgele G/Ç'dir, bu nedenle verileri indeks sırasına göre okumak genellikle sıralı tam tablo taramasından daha yavaştır.

Bir indeks tasarlarken, bir indeksin hem sıralamayı hem de sorguyu karşılayabilmesi en iyisidir.

Bir dizin, yalnızca dizinin sütun sırası ORDER BY yan tümcesinin sırası ile tamamen aynıysa ve tüm sütunlar aynı yönde sıralanmışsa sonuçları sıralamak için kullanılabilir. Sorgunun birden çok tabloyu ilişkilendirmesi gerekiyorsa, dizin yalnızca ORDER BY yan tümcesi tarafından başvurulan tüm alanlar ilk tablo olduğunda sıralama için kullanılabilir. ORDER BY yan tümcesi, sorguyla aynı kısıtlamalara sahiptir ve en soldaki önekin gereksinimlerini karşılamalıdır (bir istisna vardır, yani en soldaki sütun bir sabit olarak belirtilir, aşağıdaki basit bir örnektir), aksi takdirde gerekir sıralama işlemi yürütülecek ve dizin sıralamadan yararlanamaz.

// En soldaki sütun sabittir, index: (date,personel_id,customer_id) date = '2015-06-01' olan demodan staff_id,customer_id öğesini seçin staff_id,customer_id ile sipariş

7. Gereksiz ve yinelenen dizinler

Redundant indexler aynı sütunlar üzerinde aynı sırada oluşturulmuş aynı tipteki indexleri ifade eder.Bu tür indexlerden mümkün olduğunca kaçınılmalı ve keşfedildikten hemen sonra silinmelidir. Örneğin, bir dizin (A, B) varsa ve bir dizin (A) oluşturmak artık bir dizindir. Yedekli dizinler genellikle bir tabloya yeni bir dizin eklendiğinde ortaya çıkar.Örneğin, biri yeni bir dizin (A, B) oluşturur, ancak bu dizin mevcut bir dizinin (A) uzantısı değildir.

Çoğu durumda, yenilerini oluşturmak yerine mevcut dizinleri genişletmeye çalışmalısınız. Ancak, mevcut bir dizini çok büyük olacak şekilde genişletmek ve dizini kullanan diğer sorguları etkilemek gibi, performansla ilgili hususların yedek dizinler gerektirdiği nadir durumlar vardır.

8. Uzun süredir kullanılmayan dizinleri silin

Uzun süredir kullanılmayan bazı indekslerin periyodik olarak silinmesi çok iyi bir uygulamadır.

Burada indeksleme konusu üzerinde duracağım ve son olarak, bir indeks her zaman en iyi araç değildir ve bir indeks sadece, indeksin sorguyu hızlandırmaya yardımcı olan faydası, getirdiği ekstra işten daha ağır bastığında etkilidir. Çok küçük tablolar için basit bir tam tablo taraması daha verimlidir. Orta ila büyük tablolar için dizinler çok etkilidir. Çok büyük tablolar için, dizin oluşturma ve bakım maliyeti artar ve bölümlenmiş tablolar gibi diğer teknikler şu anda daha etkili olabilir. Sonunda, açıklamak ve sonra test etmek bir erdemdir.

Türe özel sorgu optimizasyonu

COUNT() sorguyu optimize etme

COUNT() en yanlış anlaşılan fonksiyon olabilir.İki farklı işlevi vardır.Biri sütun değerlerini saymak, diğeri ise satır sayısını saymaktır. Sütun değerlerini sayarken, sütun değerinin boş olmaması gerekir ve NULL sayılmaz. Parantez içindeki ifadenin boş olamayacağını onaylarsanız, aslında satır sayısını sayıyorsunuz demektir. En basiti, COUNT(*) kullanırken, hayal ettiğimiz gibi tüm sütunlara genişlemez, aslında tüm sütunları yok sayar ve satır sayısını doğrudan sayar.

En yaygın yanlış anlamamız burada, parantez içinde bir sütun belirtmek ve sayının satır sayısı olmasını beklemek ve çoğu zaman yanlışlıkla birincinin daha iyi performans göstereceğini düşünmektir. Ancak durum böyle değil.Satır sayısını saymak istiyorsanız, doğrudan COUNT(*) kullanın, bu net bir anlama ve daha iyi performansa sahiptir.

Bazen bazı iş senaryoları tamamen doğru bir COUNT değeri gerektirmez ve bunun yerine yaklaşık bir değer kullanılabilir.EXPLAIN'den gelen satır sayısı iyi bir tahmindir ve EXPLAIN'in yürütülmesinin sorguyu gerçekten yürütmesi gerekmez, bu nedenle maliyet çok yüksektir. düşük. Genel olarak konuşursak, COUNT() yürütmek, doğru veri elde etmek için çok sayıda satırın taranmasını gerektirir, bu nedenle optimize etmek zordur ve MySQL düzeyinde yapılabilecek tek şey kaplama indeksidir. Sorun çözülemezse, yalnızca mimari düzeyde, örneğin bir özet tablosu eklemek veya redis gibi harici bir önbellekleme sistemi kullanmak gibi çözülebilir.

İlişkisel sorguları optimize etme

Büyük veri senaryosunda, tablo, doğrudan JOIN kullanmaktan daha iyi performansa sahip yedekli bir alanla ilişkilendirilir. Gerçekten ilişkili bir sorgu kullanmanız gerekiyorsa, şunlara özellikle dikkat etmeniz gerekir:

ON ve USING yan tümcelerindeki sütunlarda dizinler olduğundan emin olun. İndeks oluşturulurken çağrışımların sırası dikkate alınmalıdır. Tablo A ve tablo B, sütun c ile ilişkilendirildiğinde, optimize edicinin ilişkilendirme sırası A, B ise, o zaman tablo A'nın karşılık gelen sütununda bir dizin oluşturmaya gerek yoktur. Kullanılmayan indeksler ek yük getirecektir.Genel olarak, başka sebepler olmadıkça, sadece ilişki sırasına göre ikinci tablonun ilgili sütunlarında indeksler oluşturmak gerekir (belirli sebepler aşağıda analiz edilecektir).
MySQL'in potansiyel olarak optimizasyon için dizinleri kullanabilmesi için GROUP BY ve ORDER BY içindeki tüm ifadelerin yalnızca bir tablodaki sütunları içerdiğinden emin olun.

İlişkisel sorguları optimize etmeye yönelik ilk ipuçlarını anlamak için MySQL'in ilişkisel sorguları nasıl gerçekleştirdiğini anlamanız gerekir. Mevcut MySQL ilişkilendirme yürütme stratejisi çok basittir.Herhangi bir ilişkilendirme için iç içe döngü ilişkilendirme işlemi gerçekleştirir, yani önce bir tablodaki tek bir veri parçasını döngüye sokar ve ardından iç içe geçmiş döngüde sonraki tabloda eşleşen satırları arar. ve ardından tüm tablolarda eşleşen bir davranış bulunana kadar devam eder. Daha sonra her tablonun eşleşen satırlarına göre sorguda gerekli olan her sütun döndürülür.

Çok mu soyut? Örnek olarak yukarıdaki örneği ele alalım, örneğin şöyle bir sorgu var:

A.xx,B.yy SEÇ (c) KULLANARAK BİR INNER JOIN B'DEN A.xx IN (5,6) NEREDE

MySQL'in ilişkilendirme işlemini sorgudaki A ve B ilişkilendirme sırasına göre gerçekleştirdiğini varsayarsak, MySQL'in sorguyu nasıl tamamladığını göstermek için aşağıdaki sözde kod kullanılabilir:

external_iterator = A.xx,A.c'Yİ A NERDEN A.xx IN (5,6) SEÇİN; external_row = external_iterator.next; while(outer_row) { inner_iterator = B.yy'yi B NERDEN SEÇİN B.c = external_row.c; inner_row = inner_iterator.next; while(inner_row) { çıktı; inner_row = inner_iterator.next; } external_row = external_iterator.next; }

En dıştaki sorgunun A.xx sütununu temel aldığı görülebilir.A.c üzerinde bir dizin varsa, ilişkili sorgunun tamamı kullanılmayacaktır. İç sorguya bakıldığında, B.c üzerinde bir indeks varsa, sorguyu hızlandırabileceği açıktır, bu yüzden sadece ilişkilendirme sırasına göre ikinci tablonun karşılık gelen sütununda bir indeks oluşturmak gerekir.

LIMIT sayfalandırmayı optimize edin

Bir sayfalama işlemi gerektiğinde, genellikle LIMIT artı bir ofset kullanılarak ve uygun bir ORDER BY yan tümcesi eklenerek uygulanır. Karşılık gelen bir dizin varsa, verimlilik genellikle iyi olacaktır, aksi takdirde MySQL'in çok sayıda dosya sıralama işlemi yapması gerekir.

Yaygın bir sorun, ofset çok büyük olduğunda, örneğin LIMIT 1000020 gibi bir sorguda, MySQL'in 10020 kaydı sorgulaması ve ardından yalnızca 20 kayıt döndürmesi gerekir.İlk 10000 kayıt atılır, bu çok pahalıdır. .

Bu tür bir sorguyu optimize etmenin en kolay yollarından biri, tüm sütunları sorgulamak yerine, mümkün olduğunda bir kapsayan dizin taraması kullanmaktır. Ardından, tüm sütunları döndürmek için gerektiği şekilde ilişkili bir sorgu yapın. Ofset büyük olduğunda, bunu yapmanın verimliliği büyük ölçüde artacaktır. Aşağıdaki sorguyu göz önünde bulundurun:

SEÇ film_kimliği,filmden açıklama SİPARİŞ LİMİTİ 50,5;

Tablo çok büyükse, sorgu en iyi şekilde aşağıdaki şekilde değiştirilir:

film.film_id,film.description SEÇİN INNER JOIN filminden ( Filmden film_kimliğini SEÇ SİPARİŞ SINIR 50,5 ) AS tmp KULLANMA(film_id);

Buradaki gecikmeli ilişkilendirme, sorgu verimliliğini büyük ölçüde artıracak, MySQL'in mümkün olduğunca az sayfa taramasına, erişilmesi gereken kayıtları almasına ve ardından gerekli sütunları sorgulamak için ilişkili sütunlara göre orijinal tabloya dönmesine izin verecektir.

Bazen, verilerin en son alındığı konumu kaydetmek için bir yer imi kullanabilirseniz, bir sonraki sefer doğrudan yer imi tarafından kaydedilen konumdan taramaya başlayabilirsiniz, böylece aşağıdaki sorgu gibi OFFSET kullanmaktan kaçınabilirsiniz:

t LIMIT 10000, 10'DAN id SEÇİN;

Bunu şu şekilde değiştirin:

Kimliği NEREDEN SEÇİN > 10000 SINIR 10;

Diğer optimizasyonlar, önceden hesaplanmış bir özet tablo kullanmayı veya yalnızca birincil anahtar sütunları ve sıralanması gereken sütunları içeren gereksiz bir tabloyla ilişkilendirmeyi içerir.

UNION'ı optimize et

MySQL'in UNION ile başa çıkma stratejisi, önce geçici bir tablo oluşturmak, ardından her sorgu sonucunu geçici tabloya eklemek ve son olarak sorguyu yapmaktır. Bu nedenle, birçok optimizasyon stratejisi UNION sorgularında iyi çalışmaz. WHERE, LIMIT, ORDER BY gibi yan tümceleri her bir alt sorguya manuel olarak "bastırmak" genellikle gereklidir, böylece optimize edici bu koşullardan tam olarak yararlanabilir ve ilk önce optimize edebilir.

Sunucuyu gerçekten tekilleştirmeniz gerekmiyorsa, UNION ALL kullanmanız gerekir.Eğer ALL anahtar sözcüğü yoksa MySQL, geçici tabloya DISTINCT seçeneğini ekler, bu da tüm geçici tablonun verilerinin benzersiz bir şekilde kontrol edilmesine neden olur, bu da çok pahalı.. Elbette, ALL anahtar sözcüğüyle bile, MySQL her zaman sonucu geçici bir tabloya koyar, okur ve istemciye geri gönderir. Bu, birçok durumda gerekli olmasa da, örneğin, her bir alt sorgunun sonuçları doğrudan istemciye döndürülebilir.

sonsöz

Sorguların nasıl yürütüldüğünü ve zamanın nerede harcandığını ve ayrıca optimizasyon süreci hakkında biraz bilgi sahibi olmak, MySQL'i daha iyi anlamanıza ve yaygın optimizasyon tekniklerinin ardındaki ilkeleri anlamanıza yardımcı olabilir. Bu makaledeki ilke ve örneklerin teori ile pratiği daha iyi birleştirmenize ve daha fazla teorik bilgiyi pratiğe uygulamanıza yardımcı olabileceğini umuyorum.

Diyecek başka bir şey yok.Size iki soru bırakayım.Cevabı kafanızda düşünebilirsiniz.Herkesin sık sık konuştuğu şey bu ama nedenini çok az insan düşünüyor?

Paylaşım yaparken böyle bir bakış açısı fırlatacak çok programcı var: Saklı yordamları mümkün olduğunca kullanmamaya çalışın.Saklı yordamların bakımı çok zordur ve kullanım maliyetini arttırır. müşteri tarafı. Müşteriler bunları yapabildiğine göre, prosedürleri neden saklasınlar?
JOIN'in kendisi çok uygundur, doğrudan sorgulayın, neden bir görünüme ihtiyacınız var?

2018'in son ayında Huawei, Samsung, Lenovo ve vivo geride kalamıyor

Gionee'nin Liu Lirong ile yakın ilişkisi olan düzinelerce ünlü sözcüsü var!