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

Dizini kullandığımda sorgu neden hala yavaş?

Sık sık sınıf arkadaşlarım tarafından soruluyor, neden SQL ifadelerimden biri bir dizin kullanıyor, ancak yine de yavaş sorguya giriyor? Bugün dizinler ve yavaş sorgular hakkında konuşmak için bu soruyla başlayacağız.

Ek olarak, bir özet ekleyin, kişisel olarak ekibin ORM'yi makul bir şekilde kullanması gerektiğini düşünün, ORM değiş tokuşları ve seçenekleri ( . ORM'nin nesne yönelimli ve yazma işlemlerinde avantajlarını, ortak sorgularda olası tuzaklardan kaçınmak için makul şekilde kullanın (tabii ki, Linq sorgulama yeteneğiniz çok güçlüyse, bu başka bir konudur), çünkü ORM, DB'nin altında çok fazla bilgi içeriğini engeller. , Programcılar için iyi bir şey değildir ve nihai performans arayışına sahiptir, ancak ORM'yi tam olarak anlamayan ekipler daha dikkatli olmalıdır.

Vaka Analizi

Eve daha yakın, deney yapmak için aşağıdaki tabloyu oluşturdum:

OLUŞTURULABİLİR`T` ( `id`int (11) NOTNULL, `a`int (11) DEFAUTNULL, PRIMARYKEY ("kimlik"), ANAHTAR`a` (` a`) ) MOTOR = InnoDB;

Tabloda üç alan vardır, burada id birincil anahtar dizini ve a sıradan dizindir.

İlk olarak SQL, ifadenin yürütme zamanını kullanarak bir ifadenin yavaş bir sorgu ifadesi olup olmadığına karar verir. İfadenin yürütme zamanını long_query_time sistem parametresi ile karşılaştırır İfadenin yürütme süresi bundan daha uzunsa, ifade yavaş sorgu günlüğüne kaydedilir.Bu parametrenin varsayılan değeri 10 saniyedir. Tabii üretimde bu kadar büyük bir değer belirlemeyeceğiz, genelde 1 saniyeye ayarlayacağız. Daha hassas bazı işletmeler için 1 saniyeden daha az bir değer belirleyebiliriz.

İfadenin yürütülmesi sırasında tablonun indisi kullanılsa da, bir ifadenin çıktı sonucunu açıklayarak KEY değerinin NULL olmadığını görebilirsiniz.

T'den * seçimini açıklamaya bir göz atalım; ANAHTAR sonuç NULL

açıkla t'den t'yi seçin, burada id = 2; ANAHTAR sonuç PRIMARY'dir, bu da genellikle birincil anahtar indeksini kullandığını söyleriz

Açıklamanın ANAHTAR sonucu, a'dan t'yi seçmektir; a dizininin kullanıldığını gösterir.

Son iki sorgunun ANAHTARI NULL olmasa da, sonuncusu aslında tüm dizin ağacını tarar a.

Bu tablonun veri hacminin 1 milyon satır olduğu varsayılırsa, Şekil 2'deki ifade hala çok hızlı yürütülebilir, ancak Şekil 3'teki ifade çok yavaş olmalıdır. Daha aşırı bir durumsa, örneğin, bu veritabanındaki CPU baskısı çok yüksekse, ikinci ifadenin yürütme süresi de long_query_time'ı aşabilir ve yavaş sorgu günlüğüne girecektir.

Böylece bir sonuca varabiliriz: Bir dizinin kullanılıp kullanılmayacağı ile yavaş bir sorgu girilip girilmeyeceği arasında gerekli bir bağlantı yoktur. Bir dizinin kullanılması yalnızca bir SQL ifadesinin yürütme sürecini temsil eder ve yavaş bir sorgu girip girmediği, yürütme süresine göre belirlenir ve bu yürütme süresi çeşitli dış faktörlerden etkilenebilir. Diğer bir deyişle, ifadeleriniz dizinleri kullanırken hala çok yavaş olabilir.

Tam dizin taramasının eksiklikleri

O halde bu konuya daha derinlemesine bakarsak, aslında açıklığa kavuşturulması gereken gizli bir sorun var ki bu da bir indeks kullanarak kastedilen budur.

Hepimiz InnoDB'nin dizinle organize edilmiş bir tablo olduğunu ve tüm verilerin dizin ağacında saklandığını biliyoruz. Örneğin, yukarıdaki tablo t, bu tablo iki dizin içerir, bir birincil anahtar dizini ve bir normal dizin. InnoDB'de, veriler birincil anahtar dizinine yerleştirilir. resim gösterdiği gibi:

Verilerin birincil anahtar dizinine yerleştirildiğini görebilirsiniz. Mantıksal olarak konuşursak, InnoDB tablosundaki tüm sorgular en az bir dizin kullanır, bu yüzden şimdi size bir soru soruyorum, eğer t nerede id > 0. Bu ifadenin indeksleme için yararlı olduğunu düşünüyor musunuz?

Yukarıdaki ifadenin açıklamasının çıktısının BİRİNCİL olduğunu görelim. Aslında, verilerden biliyorsunuz, bu cümle kapsamlı bir tarama olmalı. Ancak optimizer, bu ifadenin yürütülmesi sırasında, birincil anahtar indeksine göre kimliği karşılayan ilkini bulması gerektiğine inanır. > 0 değerinin de endeksi kullandığı kabul edilir.

Dolayısıyla, açıklama sonucunda yazılan ANAHTAR NULL olmasa bile, aslında tam bir tablo taraması olabilir.Bu nedenle, InnoDB'de indeks kullanılmayan tek bir durum vardır, yani birincil anahtar indeksinin en sol yaprak düğümünden başlayıp sağa doğru tarama Tüm dizin ağacı.

Diğer bir deyişle, indeks kullanmamak doğru bir açıklama değildir.

Bir sorgunun tüm birincil anahtar dizin ağacını geçtiğini belirtmek için tam bir tablo taraması kullanabilirsiniz;
Ayrıca, a'dan t'ye kadar seçme gibi bir sorguyu göstermek için tam bir dizin taraması da kullanabilirsiniz; tüm sıradan dizin ağacını taradı;
Ve t'den * seç gibi bir cümle, burada id = 2'nin genellikle dizini kullandığını söylüyoruz. Demek istediği, dizinin hızlı arama işlevini kullandığımız ve tarama satırlarının sayısını etkili bir şekilde azalttığımızdı.

Dizinin filtrelenebilirliği yeterince iyi olmalıdır

Yukarıdaki anatomiye dayanarak, tam indeks taramasının sorguyu yavaşlatacağını biliyoruz ve ardından indeksin filtrelenebilirliğinden bahsedeceğiz.

Şimdi bir tablo tuttuğunuzu varsayalım, bu tablo Çin'deki 1,4 milyar insanın temel bilgilerini kaydediyor ve şimdi 10-15 yaşları arasındaki tüm isimleri ve temel bilgileri öğrenmek istiyorsunuz, o zaman ifadeniz böyle yazılacak, t_10 ile 15 yaş arasındaki insanlar.

Bu ifadeye baktığınızda, yaş alanında indekslemeye başlamalısınız, aksi takdirde tam bir tarama olacaktır, ancak indeksi oluşturduktan sonra bu ifadenin yürütülmesinin hala yavaş olduğunu göreceksiniz, çünkü bu koşulu karşılayan veriler 100 milyonu aşabilir. Kürek çekmek.

İndeksledikten sonra bu tablonun organizasyon şemasına bir göz atalım:

Bu ifadenin uygulama akışı şu şekildedir:

Dizinden bir ağaç araması kullanın, yaşı 10'a eşit olan ilk kaydı alın, birincil anahtar kimliğinin değerini alın, tüm bilgi satırını almak için kimlik değerine göre birincil anahtar dizinine gidin ve sonuç kümesinin bir parçası olarak geri döndürün;
Dizin yaşını sağa doğru tarayın, bir kimliğin değerini alın, birincil anahtar dizindeki tüm bilgi satırını alın ve bunu sonuç kümesinin bir parçası olarak geri getirin;
15 yaşından büyük ilk kayıtla karşılaşılıncaya kadar yukarıdaki adımları tekrarlayın;

Şu ifadeye bakın, bir dizin kullanmasına rağmen, 100 milyondan fazla satırı taradı. Artık bir indeks kullanıp kullanmayacağımızı tartışırken, aslında tarama satırlarının sayısını önemsediğimizi biliyorsunuz.

Büyük bir tablo için sadece bir indeks gerekli değildir, aynı zamanda indeksin filtrelenebilirliği de yeterince iyidir.

Bu örnekteki yaş gibi, filtrelenebilirliği yeterince iyi değil Tablo yapısını tasarlarken, tüm filtrelenebilirliği yeterince iyi hale getirmeliyiz, yani ayrımcılık derecesi yeterince yüksek.

Masaya dönmenin maliyeti

Yani filtreleme performansı iyi, sorgunun taranan satır sayısının daha az olması gerektiği anlamına mı geliyor?

Başka bir örneğe bakalım:

Yürütme ifadeniz t_people'dan * seçin; burada name = 'Zhang San' ve age = 8

T_people tablosunda ortak bir isim ve yaş indeksi olan bir indeks var. Bu ortak indeksin filtrelemesi iyi olmalı. Adı Zhang San olan ve yaşı 8 olan ilk çocuğu eklem indeksinde hızlıca bulabilirsiniz. Tabii ki böyle bir çocuk Fazla olmamalıdır, bu nedenle sağa taranan satır sayısı azdır ve sorgu verimliliği çok yüksektir.

Ancak sorgunun filtrelenebilirliği ile dizinin filtrelenebilirliği aynı olmak zorunda değildir.Tüm isimlerin ilk karakterinin Zhang ve 8 yaşındaki tüm çocukların olduğunu bulmaksa, cümleniz nasıl yazılır? ?

Cümlenizi nasıl yazarsınız? Açıkçası yazarsınız: t_people'dan * seçin; burada isim '%' ve yaş = 8;

MySQL5.5 ve önceki sürümlerde, bu ifadenin yürütme akışı aşağıdaki gibidir:

İlk yaş alanı ortak dizinden Zhang ile başlayan kaydı bulun, birincil anahtar kimliğini çıkarın, ardından birincil anahtar dizin ağacına gidin ve kimliğe göre tüm satırın değerini çıkarın;
Yaş alanının 8'e eşit olup olmadığını belirleyin, öyleyse, sonuç kümesinin bir satırı olarak döndürün, değilse atın.
Eklem indeksinde sağa dönün ve masaya dönme ve ortak indeks ağacındaki ismin ilk kelimesinin Zhang olmadığı bir kayıtla karşılaşana kadar değerlendirme mantığını tekrarlayın.

Birincil anahtar dizinindeki tüm veri satırını, kimliğe dayalı olarak, tabloya geri çağırma eylemi olarak adlandırıyoruz. Gördüğünüz gibi bu yürütme sürecinde en çok zaman alan adım masaya geri dönmek ... Ülkede ilk karakteri Zhang olan 80 milyon insan olduğunu varsayarsak, bu işlemin 80 milyon kez tabloya dönmesi ve ilk kayıt sırasını bulması gerekecek. O anda, dizinin yalnızca en soldaki öneki ve birleşik dizini kullanılabilir ve en çok, en soldaki önek ilkesi olarak adlandırılır.

Bu yürütme sürecini görebiliyorsunuz, çok sayıda dönüş tablosu var ve performansı yeterince iyi değil.Optimize etmenin bir yolu var mı?

MySQL sürüm 5.6'da, dizin koşulu aşağı itme optimizasyonu tanıtıldı. Bu optimize edilmiş yürütme sürecine bir göz atalım:

İlk olarak, ortak indeks ağacından, yaş alanı Zhang'ın başlangıcı olan ilk kaydı bulun ve bu indeks kaydındaki yaş değerinin 8 olup olmadığına karar verin. Eğer öyleyse, tabloya dönün, tüm veri satırını getirin ve sonuç kümesinin bir parçası olarak geri döndürün. Değilse atın;
Ortak indeks ağacında, sağa çaprazlayın ve yaş alanını değerlendirdikten sonra, ortak indeks ağacındaki adın ilk karakteri Zhang'ın kaydı olmayana kadar gerektiği gibi tabloya dönün;

Bu süreç ile yukarıdakiler arasındaki fark, ortak indeksi geçme sürecinde, 8'e eşit yaş koşulunun, ulusal ismin ilk karakterinin Zhang'ın kişisi olduğu varsayılarak, tabloya dönme sayısını azaltarak tüm traverslerin sürecine itilmesidir. , 8 yaşında 1 milyon çocuk var, o zaman ortak dizinin sorgu sürecinde 80 milyon kez geçilmesi gerekiyor ve tabloya dönmek için yalnızca 1 milyon kez gerekiyor.

Sanal sütun

Bu optimizasyonun etkisinin hala çok iyi olduğu görülebiliyor, ancak bu optimizasyon hala en soldaki önek ilkesinin sınırlamasını atlamıyor, bu nedenle yine de ortak indekste 80 milyon satırı taramanız gerekiyor. Başka bir optimizasyon yöntemi var mı?

Ortak bir indeks oluşturmak için ismin ve yaşın ilk kelimesini düşünebiliriz. Bu, MySQL 5.7 tarafından sunulan sanal sütun kullanılarak gerçekleştirilebilir. Tablo yapısını değiştirmek için ilgili SQL deyimi:

altertablet_peopleaddname_firstvarchar (2) oluşturuldu (sol (isim, 1)), addindex (isim_ilinc, yaş);

Bu SQL ifadesinin yürütme etkisine bakalım:

CREATETABLE`t_people` ( `id`int (11) VARSAYILAN, varchar (20) DEFAUTNULL, `name_first`varchar (2) GENERATEDALWAYSAS (left (` name`, 1)) VIRTUAL, KEY`name_first` (`name_first`, 'age') ) MOTOR = InnoDBDEFAULTCHARSET = utf8;

İlk olarak, insanlar üzerinde name_first adında sanal bir sütun oluşturur ve ardından name_first ve age üzerinde ortak bir indeks oluşturur ve bu sanal sütunun değerini her zaman ad alanının ilk iki baytına eşit yapar.Sanal sütun veri giriyor Güncelleme yaparken bir değer belirleyemezsiniz ve güncelleme sırasında aktif olarak değiştiremezsiniz.Değeri, tanıma göre otomatik olarak üretilecek ve isim alanı değiştirildiğinde de otomatik olarak değiştirilecektir.

Bu yeni ortak indeksle, adı Zhang olan ve yaşı 8 olan bir çocuğu aradığımızda, bu SQL ifadesi şu şekilde yazılabilir: t_people'dan * seçin, burada name_first = 'Zhang' ve yaş = 8.

Bu şekilde, bu ifadenin yürütme sürecinin sadece 1 milyon ortak indeksin satırını taraması ve 1 milyon kez tabloya dönmesi gerekiyor.Bu optimizasyonun özü, sorgu sürecini hızlandırmak için daha kompakt bir indeks oluşturmamızdır.

sonuç olarak

Bu makale size dizinin temel yapısını ve sorgu optimizasyonu için bazı temel fikirleri tanıtır.Şimdi bildiğiniz gibi, dizini kullanan ifadeler de yavaş sorgular olabilir. Sorgu optimizasyon sürecimiz genellikle taranan satırların sayısını azaltma işlemidir.

Yavaş sorgu şu şekilde özetlenebilir: