Voorbij zijn de dagen dat u zich geen zorgen hoefde te maken over het optimaliseren van de databaseprestaties. De tijd staat niet stil. Elke nieuwe tech-ondernemer wil het volgende Facebook creëren, terwijl hij probeert alle gegevens te verzamelen die hij te pakken kan krijgen. Bedrijven hebben deze gegevens nodig om modellen waarmee ze geld kunnen verdienen, beter te kunnen trainen. In dergelijke omstandigheden moeten programmeurs API’s creëren waarmee ze snel en betrouwbaar met grote hoeveelheden informatie kunnen werken.
Als u al enige tijd applicatie- of database-backends ontwerpt, heeft u waarschijnlijk code geschreven om gepagineerde query's uit te voeren. Bijvoorbeeld zoals dit:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Zoals het is?
Maar als dit de manier is waarop u uw paginering heeft uitgevoerd, dan spijt het mij te moeten zeggen dat u dit niet op de meest efficiënte manier heeft gedaan.
Wilt u bezwaar tegen mij maken? . , и Ze gebruiken de technieken waar ik het vandaag over wil hebben al.
Noem ten minste één backend-ontwikkelaar die nog nooit heeft gebruikt OFFSET и LIMIT gepagineerde query's uitvoeren. In MVP (Minimum Viable Product) en in projecten waarbij kleine hoeveelheden data worden gebruikt, is deze aanpak prima toepasbaar. Het “werkt gewoon”, om het zo maar te zeggen.
Maar als u vanaf het begin betrouwbare en efficiënte systemen wilt creëren, moet u van tevoren letten op de efficiëntie van het bevragen van de databases die in dergelijke systemen worden gebruikt.
Vandaag zullen we het hebben over de problemen met veelgebruikte (slechte) implementaties van gepagineerde query-engines, en hoe je hoge prestaties kunt bereiken bij het uitvoeren van dergelijke query's.
Wat is er mis met OFFSET en LIMIT?
Zoals al gezegd, OFFSET и LIMIT Ze presteren goed in projecten waarbij niet met grote hoeveelheden data hoeft te worden gewerkt.
Het probleem ontstaat wanneer de database zo groot wordt dat deze niet meer in het geheugen van de server past. Wanneer u met deze database werkt, moet u echter gepagineerde query's gebruiken.
Om dit probleem te laten manifesteren, moet er een situatie zijn waarin het DBMS zijn toevlucht neemt tot een inefficiënte Volledige Tabelscanbewerking op elke gepagineerde query (terwijl er invoeg- en verwijderingsbewerkingen kunnen plaatsvinden en we geen verouderde gegevens nodig hebben!).
Wat is een “volledige tabelscan” (of “sequentiële tabelscan”, sequentiële scan)? Dit is een bewerking waarbij het DBMS achtereenvolgens elke rij van de tabel leest, dat wil zeggen de gegevens die deze bevatten, en controleert of deze aan een bepaalde voorwaarde voldoen. Het is bekend dat dit type tabelscan het langzaamst is. Feit is dat wanneer het wordt uitgevoerd, er veel invoer-/uitvoerbewerkingen worden uitgevoerd waarbij het schijfsubsysteem van de server betrokken is. De situatie wordt nog verergerd door de vertragingen die gepaard gaan met het werken met gegevens die op schijven zijn opgeslagen, en het feit dat het overbrengen van gegevens van schijf naar geheugen een resource-intensieve operatie is.
U hebt bijvoorbeeld records van 100000000 gebruikers en u voert een query uit met de constructie OFFSET 50000000. Dit betekent dat het DBMS al deze records moet laden (en we hebben ze niet eens nodig!), ze in het geheugen moet plaatsen en daarna bijvoorbeeld twintig resultaten moet verzamelen die in LIMIT.
Laten we zeggen dat het er zo uit zou kunnen zien: "selecteer rijen van 50000 tot 50020 van 100000". Dat wil zeggen dat het systeem eerst 50000 rijen moet laden om de zoekopdracht te voltooien. Zie je hoeveel onnodig werk ze zal moeten doen?
Als je me niet gelooft, kijk dan eens naar het voorbeeld dat ik heb gemaakt met behulp van de functies .
Voorbeeld op db-fiddle.com
Daar, links, in het veld Schema SQL, er is code die 100000 rijen in de database invoegt, en aan de rechterkant, in het veld Query SQL, worden twee query's weergegeven. De eerste, langzame, ziet er als volgt uit:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
En de tweede, die een effectieve oplossing is voor hetzelfde probleem, is als volgt:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Om aan deze verzoeken te voldoen, klikt u gewoon op de knop Run bovenaan de pagina. Nadat we dit hebben gedaan, vergelijken we informatie over de uitvoeringstijd van de query. Het blijkt dat het uitvoeren van een ineffectieve query minstens 30 keer langer duurt dan het uitvoeren van de tweede (deze tijd varieert van run tot run; het systeem kan bijvoorbeeld melden dat de eerste query 37 ms duurde om te voltooien, maar de uitvoering van de seconde - 1 ms).
En als er meer gegevens zijn, zal alles er nog slechter uitzien (om hiervan overtuigd te zijn, kijk eens naar mijn met 10 miljoen rijen).
Wat we zojuist hebben besproken, zou u enig inzicht moeten geven in hoe databasequery's daadwerkelijk worden verwerkt.
Houd er rekening mee dat hoe hoger de waarde OFFSET — hoe langer het duurt voordat het verzoek is voltooid.
Wat moet ik gebruiken in plaats van de combinatie van OFFSET en LIMIT?
In plaats van een combinatie OFFSET и LIMIT Het is de moeite waard om een structuur te gebruiken die is gebouwd volgens het volgende schema:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Dit is het uitvoeren van query's met cursorgebaseerde paginering.
In plaats van de huidige lokaal op te slaan OFFSET и LIMIT en deze bij elk verzoek verzendt, moet u de laatst ontvangen primaire sleutel opslaan (meestal is dit ID) En LIMITAls gevolg hiervan zullen soortgelijke zoekopdrachten als de bovenstaande worden verkregen.
Waarom? Het punt is dat u, door expliciet de identificatie van de laatst gelezen rij op te geven, uw DBMS vertelt waar het moet beginnen met zoeken naar de benodigde gegevens. Bovendien wordt het zoeken, dankzij het gebruik van de sleutel, efficiënt uitgevoerd; het systeem hoeft niet te worden afgeleid door lijnen buiten het opgegeven bereik.
Laten we eens kijken naar de volgende prestatievergelijking van verschillende query's. Hier is een ineffectieve vraag.
Langzaam verzoek
En hier is een geoptimaliseerde versie van dit verzoek.
Snel verzoek
Beide query's retourneren exact dezelfde hoeveelheid gegevens. Maar de eerste duurt 12,80 seconden en de tweede 0,01 seconde. Voel je het verschil?
Mogelijke problemen
Om de voorgestelde querymethode effectief te laten werken, moet de tabel een kolom (of kolommen) hebben met unieke, opeenvolgende indexen, zoals een geheel getal-ID. In sommige specifieke gevallen kan dit bepalend zijn voor het succes van het gebruik van dergelijke zoekopdrachten om de snelheid van het werken met de database te verhogen.
Bij het samenstellen van query's moet u uiteraard rekening houden met de specifieke architectuur van de tabellen en de mechanismen kiezen die het beste werken op de bestaande tabellen. Als u bijvoorbeeld moet werken in query's met grote hoeveelheden gerelateerde gegevens, vindt u dit wellicht interessant artikel.
Als we worden geconfronteerd met het probleem van het missen van een primaire sleutel, bijvoorbeeld als we een tabel hebben met een veel-op-veel-relatie, dan is de traditionele benadering van het gebruik van OFFSET и LIMIT, past gegarandeerd bij ons. Maar het gebruik ervan kan resulteren in potentieel langzame zoekopdrachten. In dergelijke gevallen raad ik aan een automatisch oplopende primaire sleutel te gebruiken, zelfs als deze alleen nodig is om gepagineerde zoekopdrachten af te handelen.
Als u geïnteresseerd bent in dit onderwerp - , и - verschillende nuttige materialen.
Resultaten van
De belangrijkste conclusie die we kunnen trekken is dat, ongeacht de grootte van de databases waar we het over hebben, het altijd nodig is om de snelheid van de uitvoering van zoekopdrachten te analyseren. Tegenwoordig is de schaalbaarheid van oplossingen uiterst belangrijk, en als alles vanaf het allereerste begin van het werken aan een bepaald systeem correct is ontworpen, kan dit de ontwikkelaar in de toekomst van veel problemen redden.
Hoe analyseer en optimaliseer je databasequery's?
Bron: www.habr.com