Hej alla! Jag är en backend-utvecklare som skriver mikrotjänster i Java + Spring. Jag arbetar i ett av de interna produktutvecklingsteamen på Tinkoff.
I vårt team uppstår ofta frågan om att optimera frågor i ett DBMS. Du vill alltid vara lite snabbare, men du kan inte alltid klara dig med genomtänkt konstruerade index – du måste leta efter några lösningar. Under en av dessa irrande runt på nätet i jakt på rimliga optimeringar när man arbetar med databaser hittade jag , författare till SQL Performance Explained. Det här är den där sällsynta typen av blogg där du kan läsa alla artiklar i rad.
Jag skulle vilja översätta en kort artikel av Marcus åt dig. Det kan i viss mån kallas ett manifest som syftar till att uppmärksamma det gamla, men fortfarande relevanta problemet med utförandet av offsetoperationen enligt SQL-standarden.
På vissa ställen kommer jag att komplettera författaren med förklaringar och kommentarer. Jag kommer att referera till alla sådana platser som "ca." för mer klarhet
En liten introduktion
Jag tror att många vet hur problematiskt och långsamt att arbeta med sidval via offset är. Visste du att den ganska enkelt kan ersättas med en mer effektiv design?
Så, nyckelordet offset säger åt databasen att hoppa över de första n posterna i begäran. Databasen behöver dock fortfarande läsa dessa första n poster från disk, i den givna ordningen (obs: tillämpa sortering om det är angivet), och först då kommer det att vara möjligt att returnera poster från n+1 och framåt. Det mest intressanta är att problemet inte ligger i den specifika implementeringen i DBMS, utan i den ursprungliga definitionen enligt standarden:
… raderna sorteras först efter och begränsas sedan genom att ta bort antalet rader som anges i från början…
-SQL:2016, del 2, 4.15.3 Härledda tabeller (obs: för närvarande den mest använda standarden)
Nyckelpunkten här är att offset tar en enda parameter - antalet poster att hoppa över, och det är allt. Enligt denna definition kan DBMS endast hämta alla poster och sedan kassera de onödiga. Uppenbarligen tvingar denna definition av offset oss att göra extra arbete. Och det spelar ingen roll om det är SQL eller NoSQL.
Bara lite mer smärta
Problemen med offset slutar inte där, och här är varför. Om, mellan att läsa två sidor med data från disken, en annan operation infogar en ny post, vad händer i det här fallet?
När offset används för att hoppa över poster från tidigare sidor, i situationen att lägga till en ny post mellan läsningar av olika sidor, kommer du med största sannolikhet att få dubbletter (observera: detta är möjligt när vi läser sida för sida med hjälp av ordning efter konstruktion, då i mitten av vår produktion kan den få en ny post).
Figuren visar tydligt denna situation. Basen läser de första 10 posterna, varefter en ny post infogas, som förskjuter alla lästa poster med 1. Sedan tar basen en ny sida från de nästa 10 posterna och börjar inte från den 11:e, som den ska, utan från 10:e, duplicerar denna post. Det finns andra anomalier förknippade med användningen av detta uttryck, men detta är det vanligaste.
Som vi redan har fått reda på är detta inte problem med ett specifikt DBMS eller deras implementeringar. Problemet är att definiera paginering enligt SQL-standarden. Vi talar om för DBMS vilken sida som ska hämtas eller hur många poster som ska hoppa över. Databasen kan helt enkelt inte optimera en sådan begäran, eftersom det finns för lite information för detta.
Det är också värt att förtydliga att detta inte är ett problem med ett specifikt sökord, utan snarare med semantiken i frågan. Det finns flera syntaxer som är identiska i sin problematiska natur:
- Nyckelordet offset är som nämnts tidigare.
- En konstruktion av två nyckelord limit [offset] (även om limit i sig inte är så illa).
- Filtrering efter nedre gränser, baserat på radnumrering (till exempel radnummer(), radnummer osv.).
Alla dessa uttryck berättar helt enkelt hur många rader du ska hoppa över, ingen ytterligare information eller sammanhang.
Längre fram i den här artikeln används nyckelordet offset som en sammanfattning av alla dessa alternativ.
Livet utan OFFSET
Låt oss nu föreställa oss hur vår värld skulle se ut utan alla dessa problem. Det visar sig att livet utan förskjutning inte är så svårt: med ett urval kan du bara välja de rader som vi ännu inte har sett (notera: det vill säga de som inte fanns på föregående sida), med hjälp av ett villkor där.
I det här fallet utgår vi från det faktum att urval exekveras på en beställd uppsättning (gammal god ordning av). Eftersom vi har en beställd uppsättning kan vi använda ett ganska enkelt filter för att bara få den data som ligger bakom den sista posten på föregående sida:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYDet är hela principen för detta tillvägagångssätt. Visst blir det roligare när man sorterar efter många kolumner, men tanken är fortfarande densamma. Det är viktigt att notera att denna design är tillämplig på många -beslut.
Detta tillvägagångssätt kallas sökmetod eller nyckeluppsättningspaginering. Det löser problemet med flytande resultat (obs: situationen med att skriva mellan sidläsningar, beskrivits tidigare) och, naturligtvis, vad vi alla älskar, det fungerar snabbare och mer stabilt än den klassiska offseten. Stabiliteten ligger i det faktum att bearbetningstiden för begäran inte ökar i proportion till antalet efterfrågade tabeller (obs: om du vill lära dig mer om arbetet med olika tillvägagångssätt för paginering kan du . Du kan också hitta jämförande riktmärken för olika metoder där).
En av rutschkanorna att sidnumrering med nycklar naturligtvis inte är allsmäktig – den har sina begränsningar. Det viktigaste är att hon inte har förmågan att läsa slumpmässiga sidor (obs: inkonsekvent). Men i en tidevarv av ändlös rullning (obs: på fronten) är detta inte ett sådant problem. Att ange ett sidnummer för att klicka är ett dåligt beslut i UI-design i alla fall (obs: åsikt från artikelförfattaren).
Hur är det med verktygen?
Paginering på tangenter är ofta inte lämplig på grund av bristen på instrumentellt stöd för denna metod. De flesta utvecklingsverktyg, inklusive olika ramverk, låter dig inte välja exakt hur paginering ska utföras.
Situationen förvärras av det faktum att den beskrivna metoden kräver end-to-end-stöd i de teknologier som används - från DBMS till exekvering av en AJAX-förfrågan i webbläsaren med oändlig rullning. Istället för att bara ange sidnumret måste du nu ange en uppsättning nycklar för alla sidor samtidigt.
Antalet ramverk som stöder paginering på nycklar växer dock gradvis. Här är vad vi har för tillfället:
- för Java;
- för Ruby;
- и för Django;
- för Python;
- — API för kriterier för JPA-implementering.
- för Perl;
- , kartläggare för Node.js .
(Obs: några länkar togs bort på grund av att vissa bibliotek vid tidpunkten för översättningen inte hade uppdaterats sedan 2017-2018. Om du är intresserad kan du titta på originalkällan.)
Det är i detta ögonblick som din hjälp behövs. Om du utvecklar eller stödjer ett ramverk som gör någon användning av paginering, så ber jag, jag uppmanar dig att ge inbyggt stöd för paginering på nycklar. Om du har frågor eller behöver hjälp så hjälper jag gärna till (, , ) (obs: av min erfarenhet av Marcus kan jag säga att han är riktigt entusiastisk över att sprida detta ämne).
Om du använder färdiga lösningar som du tycker är värda att ha stöd för sidnumrering med nycklar, skapa en förfrågan eller till och med erbjuda en färdig lösning, om möjligt. Du kan också länka till den här artikeln.
Slutsats
Anledningen till att ett så enkelt och användbart tillvägagångssätt som sidnumrering med nycklar inte är utbrett är inte att det är svårt att implementera tekniskt eller kräver någon stor ansträngning. Den främsta anledningen är att många är vana vid att se och arbeta med offset – detta tillvägagångssätt dikteras av själva standarden.
Som ett resultat är det få som funderar på att ändra inställningen till paginering, och på grund av detta utvecklas instrumentellt stöd från ramverk och bibliotek dåligt. Därför, om idén och målet med offsetfri paginering ligger nära dig, hjälp till att sprida det!
Källa:
Författare: Markus Winand
Källa: will.com