AllYouNeedIsBackend

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size10 000 rows in set (40.81 sec)

Det är verkligen väldigt långsamt. Låt oss se hur mycket tid det tar att hämta data för den senaste sidan.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 99 990 000, 10 000; --- 9999th page * 10 000 page size10 000 rows in set (1 min 20.61 sec)

Detta är vansinnigt. Kan dock vara OK för lösningar som körs i bakgrunden. Ytterligare ett dolt problem med tillvägagångssättet kan avslöjas om du försöker ta bort en post från tabellen mitt i skanningen av den. Säg att du är klar med den tionde sidan (100 000 poster är redan besökta) och ska skanna posterna mellan 100 001 och 110 000. Men posterna 99 998 och 99 999 raderas före nästa SELECT exekvering. I så fall returnerar följande fråga det oväntade resultatet:

 SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 100 000, 10 000; N, id, ... 1, 100 003, ... 2, 100 004, ...

Som du kan se, hoppade frågan över posterna med id 100 001 och 100 002. De kommer inte att behandlas av programkoden med detta tillvägagångssätt eftersom de efter de två raderingsoperationerna finns bland de första 100 000 posterna. Därför är metoden opålitlig om datamängden är föränderlig.

Lösning #3 – den sista för idag

Ansatsen är mycket lik den föregående eftersom den fortfarande använder sig av paging, men nu istället för att förlita oss på antalet skannade poster använder vi user_id för den senast besökta posten som offset.

Simplifierad algoritm:

1. Vi får PAGE_SIZE antal poster från tabellen. Startvärde för förskjutning är 0.
2. Använd det högsta returnerade värdet för user_id i partiet som förskjutning för nästa sida.
3. Hämta nästa parti från de poster som har user_id värde som är högre än aktuellt offset.

Frågan i praktiken för den 5 000:e sidan, varje sida innehåller uppgifter om 10 000 användare:

SELECT user_id, external_id, name, metadata, date_createdFROM usersWHERE user_id > 51 234 123 --- value of user_id for 50 000 000th recordORDER BY user_id ASCLIMIT 10 000;10 000 rows in set (0.03 sec)

Notera att värdena för user_id inte är sekventiella och att de kan ha luckor, t.ex. att 25 348 är precis efter 25 345. Lösningen fungerar även om poster från framtida sidor tas bort – även i det fallet hoppar inte frågan över poster. Snyggt, eller hur?

Förklaring av prestanda

För ytterligare inlärning rekommenderar jag att man undersöker resultaten av EXPLAIN EXTENDED för varje version av sökfrågan för att få fram de nästa 10 000 posterna efter 50 000 000 000.

Låt oss fokusera på den viktigaste skillnaden mellan exekveringsplanerna för den andra och tredje lösningen, eftersom den första inte är praktiskt användbar för stora tabeller.

• Typ av sammanfogning: index vs range. Den första innebär att hela indexträdet skannas för att hitta posterna. range typ berättar att indexet endast används för att hitta matchande rader inom ett visst intervall. Så range typ är snabbare än index.
• Möjliga nycklar: NULL vs PRIMARY. Kolumnen visar de nycklar som kan användas av MySQL. Om vi tittar på kolumnen för nycklar kan vi se att nyckeln PRIMARY används för båda sökningarna.
• Rader: 50 010 000 vs 50 000 000. Värdet visar ett antal poster som analyserats innan resultatet returneras. För den andra frågan beror värdet på hur djup vår rullning är. Om vi till exempel försöker få fram nästa 10 000 poster efter den 9999:e sidan undersöks 99 990 000 poster. Den tredje frågan har däremot ett konstant värde; det spelar ingen roll om vi laddar in data för den första sidan eller den allra sista sidan. Det är alltid halva tabellens storlek.
• Filtrerat: 200.00 vs 100.00. Kolumnen anger uppskattningsvis den procentandel av tabellen som ska filtreras före bearbetning. Att ha ett högre värde är bättre. Värdet 100.00 innebär att frågan tittar igenom hela tabellen. För den andra frågan är värdet inte konstant utan beror på sidnumret: om vi frågar den första sidan skulle värdet för den filtrerade kolumnen vara 1000000.00. För den allra sista sidan skulle värdet vara 100.00.
• Extra: NULL vs Using where. Ger ytterligare information om hur MySQL löser frågan. Användning av WHERE på PRIMARY-nyckeln gör att frågeutförandet går snabbare.

Jag misstänker att join-typ är den parameter i frågeutförandet som gav det största bidraget till prestandan för att göra den tredje frågeutförandet snabbare. En annan viktig sak är att den 2:a frågan är extremt beroende av antalet sidor som ska rullas. Mer djup paginering är långsammare i det fallet.

Mer vägledning för att förstå utdata för EXPLAIN-kommandot finns i den officiella dokumentationen för ditt RDBMS.

Sammanfattning

Huvudämnet för blogginlägget var relaterat till att skanna en stor tabell med 100 000 000 poster med hjälp av offset med en primärnyckel (keyset pagination). Totalt sett granskades 3 olika tillvägagångssätt och testades på motsvarande dataset. Jag rekommenderar endast en av dem om du behöver skanna en stor tabell som är föränderlig.

Och vi reviderade användningen av kommandot EXPLAIN EXTENDED för att analysera exekveringsplanen för MySQL-förfrågningar. Jag är säker på att andra RDBMS har analoger för funktionaliteten.

I nästa kapitel kommer vi att uppmärksamma dataaggregation och lagringsoptimering. Stay tuned!

Vad är din metod för att skanna stora tabeller?

Kommer du ihåg något annat syfte med att använda keyset pagination som i lösning #3?