Veritabanı performansını optimize etme konusunda endişelenmenize gerek olmadığı günler geride kaldı. Zaman durmuyor. Her yeni teknoloji girişimcisi, bir yandan ellerine geçen tüm verileri toplamaya çalışırken bir yandan da bir sonraki Facebook'u yaratmak ister. İşletmelerin para kazanmalarına yardımcı olacak modelleri daha iyi eğitmek için bu verilere ihtiyacı var. Bu gibi durumlarda programcıların büyük miktarda bilgiyle hızlı ve güvenilir bir şekilde çalışmalarına olanak tanıyan API'ler oluşturmaları gerekir.
Belirli bir süredir uygulama veya veritabanı arka uçları tasarlıyorsanız, muhtemelen sayfalandırılmış sorguları çalıştırmak için kod yazmışsınızdır. Örneğin şöyle:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Bu şekilde mi?
Ancak sayfalandırmanızı bu şekilde yaptıysanız, bunu en verimli şekilde yapmadığınızı söylemekten üzüntü duyuyorum.
Bana itiraz mı etmek istiyorsun? . , и Bugün bahsetmek istediğim teknikleri zaten kullanıyorlar.
Daha önce hiç kullanmamış en az bir arka uç geliştiricisinin adını verin OFFSET и LIMIT sayfalandırılmış sorgular gerçekleştirmek için. MVP (Minimum Viable Product) ve az miktarda verinin kullanıldığı projelerde bu yaklaşım oldukça uygulanabilir. Tabiri caizse "sadece işe yarıyor".
Ancak sıfırdan güvenilir ve verimli sistemler oluşturmanız gerekiyorsa bu tür sistemlerde kullanılan veritabanlarının sorgulanmasının verimliliğine önceden dikkat etmelisiniz.
Bugün, sayfalandırılmış sorgu motorlarının yaygın olarak kullanılan (çok kötü) uygulamalarının sorunlarından ve bu tür sorguları çalıştırırken nasıl yüksek performans elde edilebileceğinden bahsedeceğiz.
OFFSET ve LIMIT'in nesi yanlış?
Daha önce de söylendiği gibi, OFFSET и LIMIT Büyük miktarda veriyle çalışması gerekmeyen projelerde iyi performans gösterirler.
Sorun, veritabanı artık sunucunun belleğine sığmayacak kadar büyüdüğünde ortaya çıkar. Ancak bu veritabanıyla çalışırken sayfalandırılmış sorguları kullanmanız gerekir.
Bu sorunun kendini göstermesi için, DBMS'nin her sayfalandırılmış sorguda verimsiz bir Tam Tablo Tarama işlemine başvurduğu bir durum olmalıdır (bu arada ekleme ve silme işlemleri gerçekleşebilir ve güncel olmayan verilere ihtiyacımız yoktur!).
"Tam tablo taraması" (veya "sıralı tablo taraması", Sıralı Tarama) nedir? Bu, DBMS'nin tablonun her satırını, yani içerdiği verileri sırayla okuduğu ve bunların belirli bir koşula uygunluğunu kontrol ettiği bir işlemdir. Bu tür tablo taramasının en yavaş olduğu bilinmektedir. Gerçek şu ki, çalıştırıldığında sunucunun disk alt sistemini içeren birçok giriş/çıkış işlemi gerçekleştirilir. Disklerde depolanan verilerle çalışmayla ilgili gecikme ve verileri diskten belleğe aktarmanın kaynak yoğun bir işlem olması durumu daha da kötüleştiriyor.
Örneğin, 100000000 kullanıcıya ait kayıtlarınız var ve bu yapıyla bir sorgu çalıştırıyorsunuz. OFFSET 50000000. Bu, DBMS'nin tüm bu kayıtları yüklemesi (ve bunlara ihtiyacımız bile yok!), bunları belleğe koyması ve ardından diyelim ki raporlanan 20 sonucu alması gerektiği anlamına gelir. LIMIT.
Diyelim ki şöyle görünebilir: "50000'den 50020'den 100000'ye kadar satırları seç". Yani sorguyu tamamlamak için sistemin öncelikle 50000 satır yüklemesi gerekecektir. Ne kadar gereksiz iş yapmak zorunda kalacağını görüyor musun?
Bana inanmıyorsanız özellikleri kullanarak oluşturduğum örneğe bakın .
Örnek db-fiddle.com'da
Orada, solda, tarlada Schema SQLveritabanına ve sağdaki alana 100000 satır ekleyen kod var Query SQL, iki sorgu gösterilir. İlki, yavaş olan şuna benzer:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Aynı soruna etkili bir çözüm olan ikincisi ise şöyle:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Bu istekleri yerine getirmek için butona tıklamanız yeterli Run sayfanın üst kısmında. Bunu yaptıktan sonra sorgu yürütme süresine ilişkin bilgileri karşılaştırırız. Etkin olmayan bir sorguyu yürütmenin ikinci sorguyu yürütmeye göre en az 30 kat daha uzun sürdüğü ortaya çıktı (bu süre çalıştırmadan çalıştırmaya değişir; örneğin sistem ilk sorgunun tamamlanmasının 37 ms sürdüğünü bildirebilir, ancak sorgunun yürütülmesi ikinci - 1 ms).
Ve eğer daha fazla veri varsa, o zaman her şey daha da kötü görünecek (buna ikna olmak için, benim 10 milyon satırla).
Az önce tartıştığımız şey, veritabanı sorgularının gerçekte nasıl işlendiğine dair size biraz fikir verecektir.
Lütfen değerin ne kadar yüksek olduğunu unutmayın OFFSET — isteğin tamamlanması ne kadar uzun sürerse.
OFFSET ve LIMIT kombinasyonu yerine ne kullanmalıyım?
Kombinasyon yerine OFFSET и LIMIT Aşağıdaki şemaya göre inşa edilmiş bir yapı kullanmaya değer:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Bu, imleç tabanlı sayfalandırmayla sorgu yürütmedir.
Mevcut olanları yerel olarak depolamak yerine OFFSET и LIMIT ve bunları her istekte iletmek için, alınan son birincil anahtarı saklamanız gerekir (genellikle bu ID) Ve LIMITsonucunda yukarıdakine benzer sorgular elde edilecektir.
Neden? Önemli olan, okunan son satırın tanımlayıcısını açıkça belirterek, DBMS'nize gerekli verileri aramaya nerede başlaması gerektiğini söylemenizdir. Ayrıca, anahtarın kullanımı sayesinde arama verimli bir şekilde gerçekleştirilecek; sistemin belirtilen aralığın dışındaki hatlarla dikkatini dağıtmasına gerek kalmayacak.
Çeşitli sorguların aşağıdaki performans karşılaştırmasına bir göz atalım. İşte etkisiz bir sorgu.
Yavaş istek
Ve işte bu isteğin optimize edilmiş bir versiyonu.
Hızlı istek
Her iki sorgu da tam olarak aynı miktarda veri döndürür. Ancak ilkinin tamamlanması 12,80 saniye sürüyor, ikincisi ise 0,01 saniye sürüyor. Farkı hissediyor musun?
Olası sorunlar
Önerilen sorgu yönteminin etkili bir şekilde çalışması için tablonun, tamsayı tanımlayıcı gibi benzersiz, sıralı dizinler içeren bir sütuna (veya sütunlara) sahip olması gerekir. Bazı özel durumlarda bu, veritabanıyla çalışma hızını artırmak için bu tür sorguların kullanılmasının başarısını belirleyebilir.
Doğal olarak sorguları oluştururken tabloların özel mimarisini dikkate almanız ve mevcut tablolarda en iyi çalışacak mekanizmaları seçmeniz gerekir. Örneğin, büyük miktarda ilgili veri içeren sorgularda çalışmanız gerekiyorsa bunu ilginç bulabilirsiniz. makale.
Birincil anahtarın eksik olması sorunuyla karşı karşıya kalırsak, örneğin çoktan çoğa ilişkisi olan bir tablomuz varsa, o zaman geleneksel anahtar kullanma yaklaşımı OFFSET и LIMIT, bize uyması garanti edilir. Ancak kullanımı potansiyel olarak yavaş sorgulara neden olabilir. Bu gibi durumlarda, yalnızca sayfalandırılmış sorguları işlemek için gerekli olsa bile, otomatik olarak artan bir birincil anahtar kullanmanızı öneririm.
Bu konuyla ilgileniyorsanız - , и - birkaç faydalı malzeme.
sonuçlar
Çıkarabileceğimiz ana sonuç, veritabanlarının boyutu ne olursa olsun, sorgu yürütme hızını analiz etmenin her zaman gerekli olduğudur. Günümüzde çözümlerin ölçeklenebilirliği son derece önemlidir ve belirli bir sistem üzerinde çalışmanın en başından itibaren her şey doğru şekilde tasarlanırsa, bu gelecekte geliştiriciyi birçok sorundan kurtarabilir.
Veritabanı sorgularını nasıl analiz eder ve optimize edersiniz?
Kaynak: habr.com