Az általunk ismert alkalmazások egyik tipikus forgatókönyve az adatok keresése bizonyos kritériumok szerint, és azok könnyen olvasható formában történő megjelenítése. További lehetőségek is lehetnek a rendezésre, csoportosításra és lapozásra. A feladat elvileg triviális, de megoldása során sok fejlesztő hibázik, ami később a termelékenységet is csorbítja. Próbáljuk meg megfontolni a probléma megoldásának különféle lehetőségeit, és ajánlásokat fogalmazzunk meg a leghatékonyabb megvalósítás kiválasztásához.
Lapozási lehetőség #1
A legegyszerűbb lehetőség, ami eszünkbe jut, a keresési eredmények oldalankénti megjelenítése a legklasszikusabb formában.
Tegyük fel, hogy az alkalmazás relációs adatbázist használ. Ebben az esetben az információk ebben az űrlapon való megjelenítéséhez két SQL-lekérdezést kell futtatnia:
- Sorok lekérése az aktuális oldalhoz.
- Számítsa ki a keresési feltételeknek megfelelő sorok teljes számát - ez szükséges az oldalak megjelenítéséhez.
Nézzük meg az első lekérdezést, példaként egy teszt MS SQL adatbázist használva 2016-os szerverhez. Erre a célra a Sales.SalesOrderHeader táblát fogjuk használni:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
A fenti lekérdezés a listából az első 50 rendelést adja vissza, a hozzáadás dátuma szerint rendezve, vagyis az 50 legutóbbi rendelést.
Gyorsan fut a tesztbázison, de nézzük a végrehajtási tervet és az I/O statisztikákat:
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.Az egyes lekérdezések I/O statisztikáihoz a SET STATISTICS IO ON parancs futtatásával érhető el a lekérdezés futásidejében.
Amint a végrehajtási tervből látható, a leginkább erőforrásigényes lehetőség a forrástábla összes sorának rendezése a hozzáadás dátuma szerint. A probléma pedig az, hogy minél több sor jelenik meg a táblázatban, annál „nehezebb” lesz a rendezés. A gyakorlatban az ilyen helyzeteket el kell kerülni, ezért adjunk indexet a hozzáadás dátumához, és nézzük meg, változott-e az erőforrás-felhasználás:
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.
Nyilvánvalóan sokkal jobb lett. De vajon minden probléma megoldódott? Változtassuk meg a lekérdezést, hogy olyan rendeléseket keressünk, amelyeknél az áruk összköltsége meghaladja a 100 USD-t:
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.Van egy vicces helyzetünk: a lekérdezési terv nem sokkal rosszabb, mint az előző, de a logikai olvasások tényleges száma majdnem kétszer akkora, mint egy teljes táblázatvizsgálatnál. Van egy kiút - ha egy már létező indexből összetett indexet készítünk, és második mezőként hozzáadjuk az áruk teljes árát, ismét 165 logikai leolvasást kapunk:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Ezt a példasort még sokáig lehetne folytatni, de a két fő gondolatot szeretném itt kifejezni:
- Bármilyen új feltétel vagy rendezési sorrend hozzáadása a keresési lekérdezéshez jelentős hatással lehet a keresési lekérdezés sebességére.
- De ha csak az adatok egy részét kell kivonnunk, és nem az összes találatot, amely megfelel a keresési kifejezéseknek, akkor sokféleképpen optimalizálhatunk egy ilyen lekérdezést.
Most térjünk át a legelején említett második lekérdezésre – arra, amelyik megszámolja a keresési feltételnek megfelelő rekordok számát. Vegyük ugyanezt a példát – 100 USD feletti rendelések keresése:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
A fent jelzett összetett index alapján a következőket kapjuk:
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.Az, hogy a lekérdezés a teljes indexen keresztül megy, nem meglepő, hiszen a SubTotal mező nem az első helyen van, így a lekérdezés nem tudja használni. A problémát úgy oldja meg, hogy a SubTotal mezőbe egy másik indexet ad, és ennek eredményeként csak 48 logikai olvasást ad.
Adhat még néhány példát a mennyiségek számlálására vonatkozó kérésekre, de a lényeg ugyanaz marad: egy adat fogadása és a teljes összeg megszámlálása két alapvetően eltérő kérés, és mindegyikhez saját intézkedések szükségesek az optimalizáláshoz. Általánosságban elmondható, hogy nem talál olyan index-kombinációt, amely mindkét lekérdezésnél egyformán jól működik.
Ennek megfelelően az egyik fontos követelmény, amelyet tisztázni kell egy ilyen keresési megoldás kidolgozásakor, hogy valóban fontos-e egy vállalkozás számára, hogy lássa a talált objektumok számát. Gyakran előfordul, hogy nem. A konkrét oldalszámok alapján történő navigáció pedig véleményem szerint egy nagyon szűk hatókörű megoldás, mivel a legtöbb lapozási forgatókönyv úgy néz ki, hogy „ugrás a következő oldalra”.
Lapozási lehetőség #2
Tegyük fel, hogy a felhasználókat nem érdekli a talált objektumok teljes számának ismerete. Próbáljuk meg egyszerűsíteni a keresőoldalt:
Valójában csak az változott, hogy nem lehet konkrét oldalszámokhoz navigálni, és most ennek a táblázatnak nem kell tudnia, hány lehet, hogy megjelenítse. De felmerül a kérdés - honnan tudja a táblázat, hogy vannak-e adatok a következő oldalhoz (a „Következő” hivatkozás helyes megjelenítéséhez)?
A válasz nagyon egyszerű: eggyel több rekordot olvashat ki az adatbázisból, mint amennyi a megjelenítéshez szükséges, és ennek a „további” rekordnak a jelenléte megmutatja, hogy van-e következő rész. Így csak egy kérést kell futtatnia egy oldal adat megszerzéséhez, ami jelentősen javítja a teljesítményt és megkönnyíti az ilyen funkciók támogatását. Gyakorlatomban előfordult, hogy a rekordok számának megtagadása 4-5-szörösére gyorsította az eredmények kézbesítését.
Ehhez a megközelítéshez számos felhasználói felületi lehetőség létezik: "vissza" és "előre" parancsok, mint a fenti példában, egy "több betöltés" gomb, amely egyszerűen hozzáad egy új részt a megjelenített eredményekhez, "végtelen görgetés", ami működik. a "tölts be többet" elve alapján, de a következő rész megszerzéséhez a felhasználónak kell görgetnie az összes megjelenített eredményt a végéig. Bármi is legyen a vizuális megoldás, az adatmintavétel elve ugyanaz marad.
A lapozás megvalósításának árnyalatai
A fenti lekérdezési példák mindegyike az „eltolás + számlálás” megközelítést használja, amikor a lekérdezés maga határozza meg, hogy az eredménysorokat milyen sorrendben és hány sort kell visszaadni. Először is nézzük meg, hogyan lehet a legjobban megszervezni a paraméterátadást ebben az esetben. A gyakorlatban több módszerrel is találkoztam:
- A kért oldal sorszáma (pageIndex), oldalméret (pageSize).
- Az első visszaadandó rekord sorszáma (startIndex), az eredmény rekordjainak maximális száma (count).
- Az első visszaadandó rekord sorszáma (startIndex), az utolsó visszaadandó rekord sorszáma (endIndex).
Első pillantásra úgy tűnhet, hogy ez annyira elemi, hogy nincs különbség. De ez nem így van - a legkényelmesebb és leguniverzálisabb lehetőség a második (startIndex, számolás). Ennek több oka is van:
- A fent ismertetett +1 bejegyzés lektorálási megközelítésénél az első lehetőség a pageIndex és a pageSize paraméterekkel rendkívül kényelmetlen. Például oldalanként 50 bejegyzést szeretnénk megjeleníteni. A fenti algoritmus szerint a szükségesnél eggyel több rekordot kell beolvasnia. Ha ez a „+1” nincs megvalósítva a szerveren, akkor kiderül, hogy az első oldalon rekordokat kell kérnünk 1-től 51-ig, a másodikhoz - 51-től 101-ig stb. Ha 51-es oldalméretet ad meg, és növeli a pageIndex értéket, akkor a második oldal 52-ről 102-re tér vissza stb. Ennek megfelelően az első lehetőségnél az egyetlen módja annak, hogy a következő oldalra ugráshoz megfelelő gombot alkalmazzunk, az az, hogy a szerver lektorálja az „extra” sort, ami nagyon implicit árnyalat lesz.
- A harmadik lehetőségnek egyáltalán nincs értelme, mivel a legtöbb adatbázisban a lekérdezések futtatásához továbbra is a számlálást kell átadni, nem pedig az utolsó rekord indexét. A startIndex kivonása az endIndexből egyszerű aritmetikai művelet lehet, de itt felesleges.
Most le kell írnunk a lapozás megvalósításának hátrányait az „eltolás + mennyiség” segítségével:
- Minden következő oldal lekérése drágább és lassabb lesz, mint az előző, mert az adatbázisnak továbbra is végig kell mennie az összes rekordon „elejétől” a keresési és rendezési feltételek szerint, majd meg kell állnia a kívánt töredéknél.
- Nem minden DBMS támogatja ezt a megközelítést.
Vannak alternatívák, de azok is tökéletlenek. Ezen megközelítések közül az elsőt „kulcskészlet lapozásnak” vagy „keresési metódusnak” nevezik, és a következő: egy rész fogadása után megjegyezheti az oldal utolsó rekordjában lévő mezőértékeket, majd felhasználhatja azokat a megszerzésére. a következő részt. Például a következő lekérdezést futtattuk:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Az utolsó rekordban pedig a '2014-06-29' rendelési dátum értéket kaptuk. Ezután a következő oldal eléréséhez megpróbálhatja ezt:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
A probléma az, hogy a OrderDate egy nem egyedi mező, és a fent megadott feltétel valószínűleg sok kötelező sort kihagy. A lekérdezés egyértelművé tételéhez egyedi mezőt kell hozzáadnia a feltételhez (feltételezzük, hogy a 75074 az elsődleges kulcs utolsó értéke az első részből):
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
Ez az opció megfelelően működik, de általában nehéz lesz optimalizálni, mivel a feltétel tartalmaz egy VAGY operátort. Ha az elsődleges kulcs értéke a OrderDate növekedésével nő, akkor a feltétel egyszerűsíthető úgy, hogy csak a SalesOrderID szűrőt hagyja meg. De ha nincs szigorú korreláció az elsődleges kulcs értékei és az eredmény rendezési mezője között, akkor ez a VAGY a legtöbb DBMS-ben nem kerülhető el. Kivétel, amelyről tudok, a PostgreSQL, amely teljes mértékben támogatja a sor összehasonlítást, és a fenti feltétel a következőképpen írható fel: "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Ha egy összetett kulcsot tartalmaz ezzel a két mezővel, egy ilyen lekérdezésnek meglehetősen egyszerűnek kell lennie.
Egy másik alternatív megközelítés megtalálható például a vagy - amikor egy kérés az adatokon kívül egy speciális azonosítót ad vissza, amellyel a következő adatrészt kaphatja meg. Ha ennek az azonosítónak korlátlan élettartama van (mint a Comsos DB-ben), akkor ez egy nagyszerű módja a lapozás megvalósításának az oldalak közötti szekvenciális átmenettel (fent említett 2. lehetőség). Lehetséges hátrányai: nem minden DBMS támogatja; a kapott next-chunk azonosító korlátozott élettartamú lehet, ami általában nem alkalmas a felhasználói interakció megvalósítására (például az ElasticSearch scroll API).
Komplex szűrés
Bonyolítsuk tovább a feladatot. Tegyük fel, hogy követelmény az úgynevezett fazettált keresés megvalósítása, amely mindenki számára nagyon ismerős az online áruházakból. A fenti példák a rendelési táblázat alapján ebben az esetben nem túl szemléletesek, ezért térjünk át a Termék táblára az AdventureWorks adatbázisból:
Mi az ötlet a fazettált keresés mögött? A helyzet az, hogy minden szűrőelemnél megjelenik azon rekordok száma, amelyek megfelelnek ennek a kritériumnak figyelembe véve az összes többi kategóriában kiválasztott szűrőket.
Például, ha ebben a példában a Kerékpárok kategóriát és a Fekete színt választjuk, akkor a táblázatban csak a fekete kerékpárok jelennek meg, de:
- A Kategóriák csoport minden kritériumánál az adott kategóriába tartozó termékek száma feketével jelenik meg.
- A „Színek” csoport minden kritériumánál megjelenik az ilyen színű kerékpárok száma.
Íme egy példa az eredmény kimenetére ilyen feltételek esetén:
Ha bejelöli a „Ruházat” kategóriát is, a táblázatban megjelennek a készleten lévő fekete ruhák is. A „Szín” rovatban a fekete termékek száma is újraszámításra kerül az új feltételeknek megfelelően, csak a „Kategóriák” részben nem változik semmi... Remélem, ezek a példák elegendőek a szokásos fazettált keresési algoritmus megértéséhez.
Most képzeljük el, hogyan valósítható meg ez relációs alapon. Minden kritériumcsoport, például a kategória és a szín, külön lekérdezést igényel:
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
Mi a baj ezzel a megoldással? Nagyon egyszerű – nem skálázható jól. Minden szűrőszakaszhoz külön lekérdezés szükséges a mennyiségek kiszámításához, és ezek a lekérdezések nem a legegyszerűbbek. Az online áruházakban egyes kategóriákban több tucat szűrőrész is lehet, ami komoly teljesítményproblémát jelenthet.
Általában ezek után a kijelentések után néhány megoldást kínálnak, nevezetesen:
- Az összes mennyiségi számlálás összevonása egy lekérdezésben. Technikailag ez lehetséges az UNION kulcsszó használatával, de ez nem sokat segít a teljesítményen – az adatbázisnak továbbra is minden töredéket a semmiből kell végrehajtania.
- Gyorsítótár mennyiségei. Szinte minden alkalommal ezt javasolják, amikor leírok egy problémát. A figyelmeztetés az, hogy ez általában lehetetlen. Tegyük fel, hogy van 10 „arányunk”, amelyek mindegyikének 5 értéke van. Ez egy nagyon „szerény” helyzet ahhoz képest, amit ugyanezen webáruházakban láthatunk. Az egyik aspektuselem kiválasztása 9 másik elem mennyiségeit érinti, vagyis minden kritériumkombinációnál a mennyiségek eltérőek lehetnek. Példánkban összesen 50 feltételt választhat ki a felhasználó, ezért a lehetséges kombinációk száma 250. Nincs elég memória vagy idő egy ilyen adattömb kitöltésére. Itt kifogásolhatja azt, hogy nem minden kombináció valódi, és a felhasználó ritkán választ 5-10 kritériumnál többet. Igen, lehetséges a lusta betöltés és a gyorsítótárba helyezni csak a valaha kiválasztott mennyiséget, de minél több kijelölés van, annál kevésbé lesz hatékony egy ilyen gyorsítótár, és annál észrevehetőbbek lesznek a válaszidővel kapcsolatos problémák (különösen, ha a az adatkészlet rendszeresen változik).
Szerencsére egy ilyen problémára már régóta vannak elég hatékony megoldások, amelyek nagy adatmennyiség esetén kiszámíthatóan működnek. Ezen opciók bármelyikénél célszerű a szempontok újraszámítását és az eredményoldal fogadását két párhuzamos szerverhívásra felosztani, és a felhasználói felületet úgy megszervezni, hogy az adatok aspektusonkénti betöltése „nem zavarja” a Keresési eredmények.
- A lehető legritkábban hívja fel a „szempontok” teljes újraszámítását. Például ne számoljon újra mindent, amikor a keresési feltételek megváltoznak, hanem keresse meg az aktuális feltételeknek megfelelő találatok teljes számát, és kérje meg a felhasználót, hogy mutassa meg őket – „1425 rekord található, mutat?” A felhasználó folytathatja a keresési feltételek módosítását, vagy kattintson a „megjelenítés” gombra. Csak a második esetben hajtják végre az eredmények megszerzésére és a mennyiségek újraszámítására vonatkozó kéréseket minden „oldalon”. Ebben az esetben, amint azt könnyen láthatja, egy kéréssel kell foglalkoznia, hogy megkapja az eredmények teljes számát és annak optimalizálását. Ez a módszer sok kis online boltban megtalálható. Nyilvánvalóan ez nem csodaszer erre a problémára, de egyszerű esetekben jó kompromisszum lehet.
- Használjon keresőmotorokat az eredmények megkeresésére és a szempontok megszámlálására, mint például a Solr, az ElasticSearch, a Sphinx és mások. Mindegyiket úgy tervezték, hogy „széleket” építsenek, és ezt meglehetősen hatékonyan teszik a fordított index miatt. Hogyan működnek a keresők, ilyen esetekben miért hatékonyabbak az általános célú adatbázisoknál, milyen gyakorlatok és buktatók vannak - ez egy külön cikk témája. Itt szeretném felhívni a figyelmet arra, hogy a keresőmotor nem helyettesítheti a fő adattárat, kiegészítésként szolgál: a fő adatbázisban a keresés szempontjából releváns változások szinkronizálódnak a keresőindexbe; A kereső általában csak a keresőmotorral működik együtt, és nem fér hozzá a fő adatbázishoz. Az egyik legfontosabb pont itt az, hogy hogyan lehet ezt a szinkronizálást megbízhatóan megszervezni. Minden a „reakcióidő” követelményeitől függ. Ha a fő adatbázis változása és a keresésben való „megnyilvánulása” közötti idő nem kritikus, létrehozhat egy szolgáltatást, amely néhány percenként megkeresi a nemrégiben módosított rekordokat, és indexeli azokat. Ha a lehető legrövidebb válaszidőt szeretné elérni, megvalósíthat valami hasonlót frissítések küldéséhez a keresőszolgáltatásnak.
Álláspontja
- A szerveroldali lapozás megvalósítása jelentős bonyodalom, és csak gyorsan növekvő vagy egyszerűen nagy adathalmazok esetén van értelme. Nincs teljesen pontos recept a „nagy” vagy a „gyorsan növekvő” értékelésére, de én ezt a megközelítést követném:
- Ha a teljes adatgyűjtemény fogadása, figyelembe véve a szerveridőt és a hálózati átvitelt, normálisan megfelel a teljesítménykövetelményeknek, akkor nincs értelme a lapozást a szerver oldalon megvalósítani.
- Előfordulhat olyan helyzet, hogy a közeljövőben nem várható teljesítményprobléma, mivel kevés az adat, de az adatgyűjtés folyamatosan bővül. Ha a jövőben bizonyos adathalmazok már nem felelnek meg az előző pontnak, jobb, ha azonnal elkezdi a lapozást.
- Ha a vállalkozás részéről nincs szigorú követelmény az összes találat vagy az oldalszámok megjelenítésére, és a rendszerében nincs kereső, akkor jobb, ha ezeket a pontokat nem alkalmazza, és fontolja meg a 2. lehetőséget.
- Ha egyértelmű követelmény a fazettált keresés, akkor két lehetőség közül választhat a teljesítmény feláldozása nélkül:
- Ne számolja újra az összes mennyiséget minden alkalommal, amikor a keresési feltételek megváltoznak.
- Használjon olyan keresőmotorokat, mint a Solr, ElasticSearch, Sphinx és mások. De meg kell érteni, hogy nem helyettesítheti a fő adatbázist, és a fő tároló kiegészítéseként kell használni keresési problémák megoldására.
- Szintén fazettált keresés esetén érdemes a keresési eredményoldal lekérését és a számlálást két párhuzamos kérésre felosztani. A mennyiségek számlálása tovább tarthat, mint az eredmények elérése, miközben az eredmények fontosabbak a felhasználó számára.
- Ha SQL-adatbázist használ a kereséshez, minden ehhez a részhez kapcsolódó kódmódosítást alaposan le kell tesztelni a megfelelő adatmennyiségen (az élő adatbázisban lévő mennyiséget meghaladóan). Célszerű a lekérdezés végrehajtási idejének figyelése az adatbázis minden példányán, és különösen az „élő” példányon. Még ha a fejlesztési szakaszban minden rendben is volt a lekérdezési tervekkel, az adatok mennyiségének növekedésével a helyzet érezhetően változhat.
Forrás: will.com