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 er faktisk meget langsomt. Lad os se, hvor meget tid der skal bruges til at hente dataene for den seneste side.

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)

Det er vanvittigt. Kan dog være OK for løsninger, der kører i baggrunden. Endnu et skjult problem med fremgangsmåden kan afsløres, hvis man forsøger at slette en post fra tabellen midt i scanningen af den. Lad os sige, at du er færdig med den 10. side (100 000 poster er allerede besøgt) og skal til at scanne posterne mellem 100 001 og 110 000. Men posterne 99 998 og 99 999 bliver slettet før den næste SELECT udførelse. I så fald returnerer følgende forespørgsel det uventede resultat:

 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, sprang forespørgslen over posterne med id’erne 100 001 og 100 002. De vil ikke blive behandlet af applikationens kode med fremgangsmåden, fordi de efter de to sletninger optræder i de første 100 000 poster efter de to sletninger. Derfor er metoden upålidelig, hvis datasættet er foranderligt.

Løsning nr. 3 – den sidste for i dag

Løsningen ligner meget den foregående, fordi den stadig bruger paging, men nu bruger vi i stedet for at stole på antallet af scannede poster user_id for den senest besøgte post som offset.

Simplificeret algoritme:

1. Vi får PAGE_SIZE antal poster fra tabellen. Startoffsetværdien er 0.
2. Brug den maksimale returnerede værdi for user_id i batchen som offset for den næste side.
3. Hent den næste batch fra de poster, som har user_id værdi højere end den aktuelle offset.

Søgningen i aktion for 5 000. side, hver side indeholder data om 10 000 brugere:

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)

Bemærk, at værdierne for user_id ikke er sekventielle og kan have huller som 25 348 er lige efter 25 345. Løsningen virker også, hvis der slettes poster fra fremtidige sider – selv i det tilfælde springer forespørgslen ikke over poster. Sødt, ikke?

Forklaring af ydeevne

For yderligere læring anbefaler jeg at undersøge resultaterne af EXPLAIN EXTENDED for hver version af forespørgslen for at få de næste 10 000 poster efter 50 000 000 000.

Lad os fokusere på den vigtigste forskel mellem udførelsesplanerne for 2. og 3. løsning, da den 1. ikke er praktisk anvendelig for store tabeller.

• Join type: index vs range. Den første betyder, at hele indekstræet skannes for at finde posterne. range typen fortæller os, at indekset kun bruges til at finde matchende rækker inden for et bestemt område. Så range type er hurtigere end index.
• Mulige nøgler: NULL vs PRIMARY. Kolonnen viser de nøgler, der kan bruges af MySQL. BTW, hvis vi kigger i kolonnen keys, kan vi se, at i sidste ende PRIMARY nøgle bruges til begge forespørgsler.
• Rows: 50 010 000 vs 50 000 000. Værdien viser et antal poster, der er analyseret, før resultatet returneres. For den 2. forespørgsel afhænger værdien af, hvor dyb vores rulning er. Hvis vi f.eks. forsøger at få de næste 10 000 poster efter 9999. side, undersøges der 99 990 000 poster. I modsætning hertil har den tredje forespørgsel en konstant værdi; det er ligegyldigt, om vi indlæser data for den første side eller den allersidste side. Den er altid halv størrelse af tabellen.
• Filtreret: 200.00 vs 100.00. Kolonnen angiver skønsmæssigt den procentdel af tabellen, der skal filtreres før behandling. Det er bedre at have den højere værdi. Værdien 100.00 betyder, at forespørgslen gennemgår hele tabellen. For den anden forespørgsel er værdien ikke konstant og afhænger af sidetal: hvis vi spørger på den første side, vil værdien af den filtrerede kolonne være 1000000.00. For den allersidste side ville den være 100.00.
• Ekstra: NULL vs Using where. Giver yderligere oplysninger om, hvordan MySQL løser forespørgslen. Brug af WHERE på PRIMARY-nøglen gør udførelsen af forespørgslen hurtigere.

Jeg formoder, at join-type er den parameter i forespørgslen, der har ydet det største bidrag til ydelsen for at gøre den 3. forespørgsel hurtigere. En anden vigtig ting er, at den 2. forespørgsel er ekstremt afhængig af antallet af de sider, der skal scrolles. Mere dyb pagination er langsommere i det tilfælde.

Mere vejledning om forståelse af output for EXPLAIN-kommandoen kan findes i den officielle dokumentation for dit RDBMS.

Summary

Det vigtigste emne for blogindlægget var relateret til scanning af en stor tabel med 100 000 000 poster ved hjælp af offset med en primærnøgle (keyset pagination). Samlet set blev 3 forskellige tilgange gennemgået og testet på det tilsvarende datasæt. Jeg anbefaler kun en af dem, hvis du har brug for at scanne en mutabel stor tabel.

Derudover reviderede vi brugen af EXPLAIN EXTENDED-kommandoen til at analysere udførelsesplanen for MySQL-forespørgsler. Jeg er sikker på, at andre RDBMS har analoger til funktionaliteten.

I det næste kapitel vil vi være opmærksomme på dataaggregation og optimering af lagring. Stay tuned!

Hvad er din metode til at scanne store tabeller?

Har du husket et andet formål med at bruge keyset pagination som i løsning #3?