Aşina olduğumuz tüm uygulamalardaki tipik senaryolardan biri, verileri belirli kriterlere göre arayıp, okunması kolay bir biçimde görüntülemektir. Sıralama, gruplandırma ve sayfalama için ek seçenekler de olabilir. Görev teorik olarak önemsizdir, ancak çoğu geliştirici bunu çözerken bir takım hatalar yapar ve bu hatalar daha sonra verimliliğin düşmesine neden olur. Bu sorunu çözmek için çeşitli seçenekleri göz önünde bulundurmaya çalışalım ve en etkili uygulamayı seçmek için öneriler oluşturalım.
Çağrı seçeneği #1
Akla gelen en basit seçenek, arama sonuçlarının en klasik haliyle sayfa sayfa görüntülenmesidir.
Uygulamanızın ilişkisel bir veritabanı kullandığını varsayalım. Bu durumda bilgileri bu formda görüntülemek için iki SQL sorgusu çalıştırmanız gerekecektir:
- Geçerli sayfanın satırlarını alın.
- Arama kriterlerine karşılık gelen toplam satır sayısını hesaplayın - bu, sayfaları görüntülemek için gereklidir.
Örnek olarak bir test MS SQL veritabanını kullanan ilk sorguya bakalım 2016 sunucusu için. Bu amaçla Sales.SalesOrderHeader tablosunu kullanacağız:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Yukarıdaki sorgu, listedeki ilk 50 siparişi, eklenme tarihine göre azalan şekilde, yani en son 50 siparişi döndürecektir.
Test tabanında hızlı bir şekilde çalışır, ancak yürütme planına ve G/Ç istatistiklerine bakalım:
Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Sorgu çalışma zamanında SET STATISTICS IO ON komutunu çalıştırarak her sorgu için G/Ç istatistiklerini elde edebilirsiniz.
Yürütme planından görebileceğiniz gibi, en yoğun kaynak kullanan seçenek, kaynak tablonun tüm satırlarını eklenme tarihine göre sıralamaktır. Sorun şu ki, tabloda ne kadar çok satır görünürse sıralama o kadar "zor" olacaktır. Pratikte bu gibi durumların önüne geçilmesi gerekiyor o yüzden ekleme tarihine bir indeks ekleyelim ve kaynak tüketiminin değişip değişmediğine bakalım:
Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Açıkçası çok daha iyi hale geldi. Peki tüm sorunlar çözüldü mü? Toplam mal maliyetinin 100 ABD dolarını aştığı siparişleri aramak için sorguyu değiştirelim:
SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Komik bir durumla karşı karşıyayız: Sorgu planı öncekinden çok da kötü değil, ancak mantıksal okumaların gerçek sayısı tam tablo taramasının neredeyse iki katı kadar. Bir çıkış yolu var - eğer mevcut bir endeksten bileşik bir endeks oluşturursak ve malların toplam fiyatını ikinci alan olarak eklersek, yine 165 mantıksal okuma elde ederiz:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Bu örnekler dizisine uzun süre devam edilebilir ancak burada dile getirmek istediğim iki ana düşünce şunlardır:
- Bir arama sorgusuna yeni bir ölçüt veya sıralama düzeni eklemek, arama sorgusunun hızı üzerinde önemli bir etkiye sahip olabilir.
- Ancak, arama terimleriyle eşleşen tüm sonuçları değil de, verilerin yalnızca bir kısmını çıkarmamız gerekiyorsa, bu tür bir sorguyu optimize etmenin birçok yolu vardır.
Şimdi en başta bahsettiğimiz, arama kriterini karşılayan kayıtların sayısını sayan ikinci sorguya geçelim. Aynı örneği ele alalım: 100$'ın üzerindeki siparişleri aramak için:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Yukarıda belirtilen bileşik indeks göz önüne alındığında şunu elde ederiz:
Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Sorgunun tüm indeksten geçmesi şaşırtıcı değildir, çünkü SubTotal alanı ilk konumda değildir ve bu nedenle sorgu onu kullanamaz. SubTotal alanına bir indeks daha eklenerek sorun çözülüyor ve sonuç olarak sadece 48 mantıksal okuma veriyor.
Miktarların sayılması için birkaç istek örneği daha verebilirsiniz, ancak özü aynı kalır: bir veri parçası almak ve toplam miktarı saymak temelde iki farklı istektirve her biri optimizasyon için kendi önlemlerini gerektirir. Genel olarak, her iki sorgu için de eşit derecede iyi çalışan bir dizin birleşimi bulamazsınız.
Buna göre böyle bir arama çözümü geliştirirken açıklığa kavuşturulması gereken önemli gereksinimlerden biri, bulunan toplam nesne sayısını görmenin bir işletme için gerçekten önemli olup olmadığıdır. Çoğu zaman hayır olur. Bana göre belirli sayfa numaralarına göre gezinme çok dar kapsamlı bir çözümdür, çünkü çoğu sayfalama senaryosu "sonraki sayfaya git" gibi görünür.
Çağrı seçeneği #2
Kullanıcıların bulunan toplam nesne sayısını bilmenin umursamadığını varsayalım. Arama sayfasını basitleştirmeye çalışalım:
Aslında değişen tek şey, belirli sayfa numaralarına gitmenin bir yolunun olmaması ve artık bu tablonun görüntülenmesi için kaç tane olabileceğini bilmesine gerek yok. Ancak şu soru ortaya çıkıyor: Tablo bir sonraki sayfa için veri olup olmadığını nasıl biliyor (“Sonraki” bağlantısını doğru bir şekilde görüntülemek için)?
Cevap çok basit: Veritabanından görüntülemek için gerekenden bir kayıt daha okuyabilirsiniz ve bu "ek" kaydın varlığı bir sonraki bölümün olup olmadığını gösterecektir. Bu şekilde, bir sayfalık veri almak için yalnızca bir istek çalıştırmanız gerekir; bu da performansı önemli ölçüde artırır ve bu tür işlevlerin desteklenmesini kolaylaştırır. Uygulamamda, toplam kayıt sayısını saymayı reddetmenin sonuçların teslimini 4-5 kat hızlandırdığı bir durum vardı.
Bu yaklaşım için çeşitli kullanıcı arayüzü seçenekleri vardır: yukarıdaki örnekte olduğu gibi "geri" ve "ileri" komutları, görüntülenen sonuçlara basitçe yeni bir bölüm ekleyen "daha fazla yükle" düğmesi, "sonsuz kaydırma", bu da işe yarar "Daha fazla yükle" ilkesine dayanmaktadır, ancak bir sonraki bölümü alma sinyali, kullanıcının görüntülenen tüm sonuçları sonuna kadar kaydırmasıdır. Görsel çözüm ne olursa olsun veri örnekleme ilkesi aynı kalır.
Sayfalama uygulamasının nüansları
Yukarıda verilen tüm sorgu örnekleri, sorgunun kendisi sonuç satırlarının hangi sırayla ve kaç satır döndürülmesi gerektiğini belirttiğinde "offset + count" yaklaşımını kullanır. Öncelikle bu durumda parametre aktarımını en iyi nasıl organize edebileceğimize bakalım. Pratikte birkaç yöntemle karşılaştım:
- İstenilen sayfanın seri numarası (pageIndex), sayfa boyutu (pageSize).
- Döndürülecek ilk kaydın seri numarası (startIndex), sonuçtaki maksimum kayıt sayısı (count).
- Döndürülecek ilk kaydın sıra numarası (startIndex), döndürülecek son kaydın sıra numarası (endIndex).
İlk bakışta bu o kadar basit ki hiçbir fark yokmuş gibi görünebilir. Ancak durum böyle değil - en uygun ve evrensel seçenek ikincisidir (startIndex, count). Bunun birkaç nedeni var:
- Yukarıda verilen +1 girişi düzeltme yaklaşımı için pageIndex ve pageSize içeren ilk seçenek son derece elverişsizdir. Örneğin sayfa başına 50 gönderi görüntülemek istiyoruz. Yukarıdaki algoritmaya göre gereğinden fazla bir kayıt daha okumanız gerekiyor. Sunucuda bu "+1" uygulanmazsa, ilk sayfa için 1'den 51'e, ikinci sayfa için 51'den 101'e vb. kayıt talep etmemiz gerektiği ortaya çıkıyor. Sayfa boyutunu 51 olarak belirlerseniz ve pageIndex'i artırırsanız, ikinci sayfa 52'den 102'ye vb. dönecektir. Buna göre, ilk seçenekte, bir sonraki sayfaya gitmek için bir düğmeyi doğru şekilde uygulamanın tek yolu, sunucunun "ekstra" satırı yeniden okumasını sağlamaktır ki bu çok örtülü bir nüans olacaktır.
- Üçüncü seçenek hiç mantıklı değil çünkü çoğu veritabanında sorgu çalıştırmak için son kaydın indeksi yerine sayıyı iletmeniz gerekecek. startIndex'i endIndex'ten çıkarmak basit bir aritmetik işlem olabilir, ancak burada gereksizdir.
Şimdi sayfalamanın “offset + miktar” yoluyla uygulanmasının dezavantajlarını açıklamalıyız:
- Sonraki her sayfayı almak bir öncekinden daha pahalı ve daha yavaş olacaktır, çünkü veritabanının yine de arama ve sıralama kriterlerine göre tüm kayıtları "baştan itibaren" gözden geçirmesi ve ardından istenen parçada durması gerekecektir.
- Tüm DBMS'ler bu yaklaşımı destekleyemez.
Alternatifler var ama onlar da kusurlu. Bu yaklaşımlardan ilki “keyset paging” veya “arama yöntemi” olarak adlandırılır ve şu şekildedir: Bir kısmı aldıktan sonra sayfadaki son kayıttaki alan değerlerini hatırlayabilir ve daha sonra bunları elde etmek için kullanabilirsiniz. sonraki kısım. Örneğin aşağıdaki sorguyu çalıştırdık:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Ve son kayıtta '2014-06-29' sipariş tarihi değerini aldık. Daha sonra bir sonraki sayfayı almak için şunu yapmayı deneyebilirsiniz:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Sorun, OrderDate'in benzersiz olmayan bir alan olması ve yukarıda belirtilen koşulun muhtemelen birçok gerekli satırı kaçırmasıdır. Bu sorguya netlik kazandırmak için koşula benzersiz bir alan eklemeniz gerekir (75074'ün, birincil anahtarın ilk bölümdeki son değeri olduğunu varsayalım):
SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Bu seçenek doğru şekilde çalışacaktır ancak genel olarak koşulun bir OR operatörü içermesi nedeniyle optimizasyonu zor olacaktır. OrderDate arttıkça birincil anahtarın değeri de artıyorsa, bu durumda yalnızca SalesOrderID'ye göre bir filtre bırakılarak durum basitleştirilebilir. Ancak birincil anahtarın değerleri ile sonucun sıralandığı alan arasında kesin bir korelasyon yoksa çoğu DBMS'de bu OR'den kaçınılamaz. Bildiğim bir istisna, demet karşılaştırmalarını tam olarak destekleyen PostgreSQL'dir ve yukarıdaki koşul "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)" olarak yazılabilir. Bu iki alandan oluşan bileşik bir anahtar verildiğinde bunun gibi bir sorgu oldukça kolay olmalıdır.
İkinci bir alternatif yaklaşım örneğin şu şekilde bulunabilir: veya — bir istek, verilere ek olarak, verilerin bir sonraki bölümünü alabileceğiniz özel bir tanımlayıcı döndürdüğünde. Bu tanımlayıcının sınırsız bir kullanım ömrü varsa (Comsos DB'de olduğu gibi), bu, sayfalar arasında sıralı geçişle sayfalamayı uygulamanın harika bir yoludur (yukarıda belirtilen seçenek #2). Olası dezavantajları: tüm DBMS'lerde desteklenmez; sonuçta ortaya çıkan sonraki parça tanımlayıcısının sınırlı bir ömrü olabilir ve bu genellikle kullanıcı etkileşimini (ElasticSearch kaydırma API'si gibi) uygulamak için uygun değildir.
Karmaşık filtreleme
Görevi daha da karmaşıklaştıralım. Çevrimiçi mağazalardan herkesin çok aşina olduğu sözde yönlü aramanın uygulanmasına yönelik bir gereklilik olduğunu varsayalım. Yukarıdaki siparişler tablosunu temel alan örnekler bu durumda pek açıklayıcı değildir, bu nedenle AdventureWorks veritabanından Ürün tablosuna geçelim:
Yönlü aramanın ardındaki fikir nedir? Gerçek şu ki, her filtre elemanı için bu kriteri karşılayan kayıt sayısı gösterilmektedir. diğer tüm kategorilerde seçilen filtreler dikkate alınarak.
Örneğin, bu örnekte Bisikletler kategorisini ve Siyah rengini seçersek, tablo yalnızca siyah bisikletleri gösterecektir ancak:
- Kategoriler grubundaki her kriter için o kategorideki ürün sayısı siyah renkte gösterilecektir.
- “Renkler” grubundaki her bir kriter için bu renkteki bisiklet sayısı gösterilecektir.
Bu tür koşullar için sonuç çıktısının bir örneği:
Ayrıca “Giyim” kategorisini de işaretlerseniz tabloda stokta bulunan siyah kıyafetler de gösterilecektir. “Renk” bölümündeki siyah ürün sayısı da yeni koşullara göre yeniden hesaplanacak, yalnızca “Kategoriler” bölümünde hiçbir şey değişmeyecek... Umarım bu örnekler alışılagelmiş yönlü arama algoritmasını anlamak için yeterlidir.
Şimdi bunun ilişkisel bazda nasıl uygulanabileceğini hayal edelim. Kategori ve Renk gibi her kriter grubu ayrı bir sorgu gerektirecektir:
SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC
SELECT Color, COUNT(1) FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC
Bu çözümün nesi yanlış? Çok basit; iyi ölçeklenmiyor. Her filtre bölümü, miktarları hesaplamak için ayrı bir sorgu gerektirir ve bu sorgular en kolay sorgular değildir. Çevrimiçi mağazalarda bazı kategorilerde birkaç düzine filtre bölümü bulunabilir ve bu ciddi bir performans sorununa neden olabilir.
Genellikle bu açıklamalardan sonra bana bazı çözümler sunulur:
- Tüm miktar sayımlarını tek bir sorguda birleştirin. Teknik olarak UNION anahtar sözcüğü kullanılarak bu mümkündür, ancak performansa pek bir faydası olmayacaktır; veritabanı yine de her bir parçayı sıfırdan yürütmek zorunda kalacaktır.
- Önbellek miktarları. Neredeyse her sorunu anlattığımda bu bana öneriliyor. Uyarı, bunun genellikle imkansız olduğudur. Diyelim ki her biri 10 değere sahip 5 "fasetimiz" var. Bu, aynı çevrimiçi mağazalarda görülebileceklerle karşılaştırıldığında çok "mütevazı" bir durumdur. Bir yön elemanının seçimi diğer 9 öğenin miktarlarını etkiler, başka bir deyişle, her kriter kombinasyonu için miktarlar farklı olabilir. Örneğimizde kullanıcının seçebileceği toplam 50 kriter vardır, dolayısıyla 250 olası kombinasyon olacaktır, böyle bir veri dizisini dolduracak yeterli bellek veya zaman yoktur. Burada tüm kombinasyonların gerçek olmadığını ve kullanıcının nadiren 5-10'dan fazla kriter seçtiğini söyleyerek itiraz edebilirsiniz. Evet, tembel yükleme yapmak ve yalnızca seçilen miktardaki miktarı önbelleğe almak mümkündür, ancak ne kadar çok seçim olursa, böyle bir önbellek o kadar az verimli olur ve yanıt süresi sorunları o kadar belirgin olur (özellikle veri seti düzenli olarak değişir).
Neyse ki, böyle bir sorun uzun süredir büyük hacimli veriler üzerinde tahmin edilebileceği gibi çalışan oldukça etkili çözümlere sahip. Bu seçeneklerden herhangi biri için, yönlerin yeniden hesaplanmasını ve sonuç sayfasının alınmasını sunucuya yapılan iki paralel çağrıya bölmek ve kullanıcı arayüzünü, verileri yönlere göre yüklemek, ekranın görüntülenmesine "engellenmeyecek" şekilde düzenlemek mantıklıdır. Arama Sonuçları.
- Mümkün olduğu kadar nadiren “özelliklerin” tam olarak yeniden hesaplanmasını çağırın. Örneğin, arama kriterleri her değiştiğinde her şeyi yeniden hesaplamayın; bunun yerine mevcut koşullarla eşleşen toplam sonuç sayısını bulun ve kullanıcıdan bunları göstermesini isteyin - "1425 kayıt bulundu, gösterilsin mi?" Kullanıcı arama terimlerini değiştirmeye devam edebilir veya "göster" düğmesini tıklayabilir. Yalnızca ikinci durumda, sonuçların elde edilmesine ve tüm “fasetlere” ilişkin miktarların yeniden hesaplanmasına yönelik tüm talepler yerine getirilecektir. Bu durumda, kolayca görebileceğiniz gibi, toplam sonuç sayısını ve optimizasyonunu elde etmek için bir taleple uğraşmanız gerekecektir. Bu yöntem birçok küçük çevrimiçi mağazada bulunabilir. Açıkçası, bu sorun için her derde deva değil, ancak basit durumlarda iyi bir uzlaşma olabilir.
- Sonuçları bulmak ve özellikleri saymak için Solr, ElasticSearch, Sphinx ve diğerleri gibi arama motorlarını kullanın. Hepsi "fasetler" oluşturmak ve ters çevrilmiş indeks nedeniyle bunu oldukça verimli bir şekilde yapmak için tasarlandı. Arama motorları nasıl çalışır, bu gibi durumlarda neden genel amaçlı veritabanlarından daha etkilidirler, hangi uygulamalar ve tuzaklar vardır - bu ayrı bir makalenin konusudur. Burada, arama motorunun ana veri deposunun yerini alamayacağı, bir ek olarak kullanıldığı gerçeğine dikkatinizi çekmek isterim: ana veritabanında aramayla ilgili herhangi bir değişiklik, arama dizinine senkronize edilir; Arama motoru genellikle yalnızca arama motoruyla etkileşime girer ve ana veritabanına erişmez. Burada en önemli noktalardan biri bu senkronizasyonun güvenilir bir şekilde nasıl organize edileceğidir. Her şey “reaksiyon süresi” gereksinimlerine bağlıdır. Ana veritabanındaki bir değişiklik ile bunun aramada "belirtilmesi" arasındaki süre kritik değilse, birkaç dakikada bir yakın zamanda değiştirilen kayıtları arayan ve bunları dizine ekleyen bir hizmet oluşturabilirsiniz. Mümkün olan en kısa yanıt süresini istiyorsanız, şöyle bir şey uygulayabilirsiniz: Güncellemeleri arama servisine göndermek için.
Bulgular
- Sunucu tarafı sayfalamanın uygulanması önemli bir komplikasyondur ve yalnızca hızlı büyüyen veya yalnızca büyük veri kümeleri için anlamlıdır. "Büyük" veya "hızlı büyüyen"in nasıl değerlendirileceğine dair kesin olarak kesin bir tarif yok, ancak ben şu yaklaşımı izleyeceğim:
- Sunucu zamanı ve ağ aktarımı dikkate alınarak eksiksiz bir veri koleksiyonunun alınması performans gereksinimlerine normal şekilde uyuyorsa, sunucu tarafında sayfalama uygulamasının bir anlamı yoktur.
- Verilerin az olması ancak veri toplamanın sürekli artması nedeniyle yakın gelecekte herhangi bir performans sorununun beklenmediği bir durum ortaya çıkabilir. Gelecekte bazı veri kümeleri önceki noktayı artık karşılamıyorsa, sayfalamaya hemen başlamak daha iyidir.
- İşletme açısından toplam sonuç sayısını veya sayfa numaralarını görüntüleme konusunda katı bir gereklilik yoksa ve sisteminizde bir arama motoru yoksa, bu noktaları uygulamamak ve 2 numaralı seçeneği düşünmek daha iyidir.
- Yönlü arama için açık bir gereklilik varsa performanstan ödün vermeden iki seçeneğiniz vardır:
- Arama kriterleri her değiştiğinde tüm miktarları yeniden hesaplamayın.
- Solr, ElasticSearch, Sphinx ve diğerleri gibi arama motorlarını kullanın. Ancak ana veritabanının yerini alamayacağı, arama problemlerinin çözümü için ana depolamaya ek olarak kullanılması gerektiği anlaşılmalıdır.
- Ayrıca, yönlü arama durumunda, arama sonuçları sayfasının alınmasını ve sayımı iki paralel isteğe bölmek mantıklıdır. Miktarları saymak, sonuçları elde etmekten daha uzun sürebilir, ancak sonuçlar kullanıcı için daha önemlidir.
- Arama için bir SQL veritabanı kullanıyorsanız, bu bölümle ilgili herhangi bir kod değişikliğinin, uygun miktarda veri (canlı veritabanındaki hacmi aşan) üzerinde performans açısından iyi bir şekilde test edilmesi gerekir. Ayrıca sorgu yürütme süresinin izlenmesinin veritabanının tüm örneklerinde ve özellikle "canlı" olanında kullanılması da tavsiye edilir. Geliştirme aşamasında sorgu planlarında her şey yolunda olsa bile veri hacmi arttıkça durum gözle görülür şekilde değişebilir.
Kaynak: habr.com