Hoppa till innehållet
POLARISAI ACADEMY
R03-201 · Navigatör

Databaser, cache och datatålighet

Efteråt kan du genomföra en överföring på 30 som committas hel, namnge vilken isoleringsnivå som fortfarande kan lämna en dirty read, räkna sidorna ett sammansatt index rör vid 250 träffar, och byta en N+1-slinga på 101 frågor mot 2.

Nivå
Navigatör
Tid
24 h (uppskattning)
Förkunskap
R03-101, R02-201
Kurs 2 av 4
Hoppa till

Efter kursen kan du

Eleven kan indexera och cacha en långsam databas med 100 000 rader så att p95-frågetiden sjunker under målvärdena och köra ett automatiserat skript för point-in-time-återställning av säkerhetskopia som verifierar 100 % radintegritet.

En överföring och de fyra ACID-kontrollerna

Förfrågan som proxyn skickade vidare är nu två skrivningar som hör till samma händelse. En transaktion är klammern runt de skrivningarna. Den här lektionen har två konton. Konto 7 börjar på 80 och konto 8 på 20, så summan är 100. En överföring på 30 drar 30 från konto 7 och lägger 30 på konto 8. Efter båda skrivningarna står saldona på 50 och 50, och 50 + 50 är fortfarande 100. Atomicitet betyder att debet och kredit committas tillsammans eller tas tillbaka tillsammans med rollback. Stannar processen efter att konto 7 skrivits till 50 i den ännu öppna transaktionen, och före commit, lägger rollback tillbaka 80 och 20. Det halvt skrivna paret 50 och 20 summerar till 70, och det paret är tillståndet som atomiciteten städar bort.

Konsistens i den här lektionen är en regel du kan kontrollera med vanlig räkning. En överföring är tillåten bara när källans saldo räcker till beloppet. 80 räcker till 30, så överföringen på 30 är tillåten. 80 räcker inte till 90, så en överföring på 90 avvisas innan något saldo ändras, och båda står kvar på 80 och 20 med summan 100. Isolering betyder att en andra transaktion läser saldon enligt den nivå du satt, och nästa avsnitt räknar vad varje nivå fortfarande släpper igenom. Hållbarhet betyder att en commit som redan har återvänt överlever en krasch. Efter att överföringen på 30 har committats visar en krasch fortfarande konto 7 på 50 och konto 8 på 50. En krasch som kommer medan transaktionen är öppen lämnar 80 och 20.

Fyra isoleringsnivåer på en lagerrad

SQL-standarden namnger fyra isoleringsnivåer: read uncommitted, read committed, repeatable read och serializable. Du skiljer dem åt med en lagerrad och en räkning av öppna order. Lagret börjar på 5. En andra transaktion sätter lagret till 0 och har inte committat. Vid read uncommitted kan den första transaktionen läsa den 0:an. Den andra transaktionen gör sedan rollback, och det lagrade lagret är 5 igen. 0:an var en dirty read: en läsning av en skrivning som aldrig committades. Vid read committed ger samma läsning 5, eftersom skrivningen till 0 fortfarande är öppen. Read committed stoppar den dirty read. Read uncommitted är den enda av de fyra nivåerna som fortfarande lämnar den ocommittade 0:an.

En non-repeatable read använder data som faktiskt committats. Den första transaktionen, på read committed, läser lager 5. Den andra sätter lagret till 2 och committar. Den första läser samma rad igen och får nu 2. Två läsningar av en rad i en transaktion gav 5 och sedan 2. Vid repeatable read stannar den andra läsningen av samma rad på 5 tills den första transaktionen tar slut. En phantom är en ny rad i ett intervall du redan räknat. Öppna order är från början två rader, id 7 och 8. Den första transaktionen på repeatable read räknar öppna order och får 2. Den andra transaktionen lägger in order 9 med status open och committar. I SQL-standarden kan repeatable read fortfarande låta nästa räkning i den första transaktionen bli 3. Serializable håller den andra räkningen på 2. Du skriver ner de fyra utfallen och väljer sedan den nivå som håller den räkning du behöver stilla.

Sidor i ett sammansatt B-tree

Tabellen orders i den här lektionen har 100 000 rader. Det sammansatta indexet idx_cust_created är ett B-tree, ett balanserat sökträd, på (customer_id, created_at), i den kolumnordningen. Varje lövsida rymmer 100 indexposter, så lövnivån är 100 000 / 100 = 1 000 sidor. Fanout är 100, alltså pekar en inre sida på 100 barn. De 1 000 löven hänger under 1 000 / 100 = 10 inre sidor, och en rotsida pekar på de 10. En punktuppslagning går via roten, sedan en inre sida, sedan ett löv. Det är 3 sidor. En genomgång utan användbart index läser alla 100 000 rader. Sidstorlekarna här är lektionens modell, valda så att divisionen går jämnt upp. En riktig fil använder den sidstorlek motorn är byggd med.

Trädet kan söka bara från den vänstra kolumnen. WHERE customer_id = 42 kan söka. WHERE customer_id = 42 och created_at ligger i ett intervall kan söka på customer_id och sedan gå framåt längs created_at. WHERE bara created_at ligger i ett intervall, och customer_id saknas i villkoret, kan inte söka i det här indexet. Planraden för den datumfrågan är en genomläsning av alla 100 000 rader. Ta sedan intervallet som faktiskt söker. Kund 42 har 250 träffande poster, packade 100, sedan 100, sedan 50, och den första av nycklarna är första posten på sitt löv. Nycklarna fyller 3 lövsidor. Nedstigningen kostar fortfarande roten och en inre sida, eftersom en inre sida kan vara förälder till alla 3 löven, och lövvandringen kostar 3. Intervallet läser 2 + 3 = 5 sidor. Att hitta samma 250 rader genom att läsa hela tabellen skulle läsa alla 100 000 rader.

Planraden och N+1-räkningen

EXPLAIN QUERY PLAN skriver ut strategin för en sats. Skalet i den här lektionen visar en detaljrad per tabell det läser, i den form som dokumenteras för nuvarande SQLite. En missad sökning på orders visar SCAN orders, genomgången av alla 100 000 rader. Det indexerade intervallet, när du också hämtar kolumnen amount så att indexet inte är täckande, visar SEARCH orders USING INDEX idx_cust_created (customer_id=? AND created_at>?). Den raden är läsningen på 5 sidor från handmodellen ovan. Planen namnger indexet och de WHERE-villkor det använder. Den skriver inte ut sidantalet. Du läser SCAN eller SEARCH ur planen, och multiplicerar sidor bara från den angivna lövstorleken 100 och den angivna fanouten 100.

N+1 är en slinga som syns i antalet frågor. En fråga hämtar order för kund 42. En andra fråga, skickad en gång per order, hämtar raderna för den ordern. Tre order, id 101, 102 och 103, kostar 1 + 3 = 4 frågor. Hundra order kostar 1 + 100 = 101 frågor. Den samlade formen hämtar orderna och hämtar sedan alla rader med en fråga vars IN-lista innehåller precis de order-id:na, så summan blir 2 frågor. Den här lektionen sätter priset för en fråga till 2 millisekunder i round trip, väntan tills satsen gått iväg och raderna kommit tillbaka. Slingan på 101 frågor kostar 101 × 2 = 202 millisekunder. Paret med IN-lista kostar 2 × 2 = 4 millisekunder. Skillnaden är 202 − 4 = 198 millisekunder. Båda versionerna lämnar samma rader när IN-listan är precis de id som den första frågan gav.

Ett exempel

Trettio enheter, fem sidor, 101 frågor

Börja med konto 7 på 80 och konto 8 på 20. Summan är 100. Öppna en transaktion och gör en överföring på 30. Skriv konto 7 som 80 − 30 = 50. Skriv konto 8 som 20 + 30 = 50. Kontrollera 50 + 50 = 100, och committa sedan. En krasch efter den committen visar fortfarande 50 och 50. Spela samma start igen och stoppa processen efter att debet skrivits och före commit. Rollback körs eftersom transaktionen var öppen, och saldona läses som 80 och 20 igen. En överföring på 90 från samma start klarar inte regeln 80 >= 90, så inget saldo ändras. Det avvisade paret är fortfarande 80 och 20.

Lagret börjar på 5, och öppna order är de två raderna 7 och 8. Transaktion T2 sätter lagret till 0 och lämnar transaktionen öppen. Transaktion T1 på read uncommitted läser 0. T2 gör rollback, och lagret är 5. Samma läsning på read committed ger 5 medan T2 fortfarande är öppen. Sedan läser T1 på read committed lager 5, T2 sätter lagret till 2 och committar, och T1 läser raden igen och får 2. Samma andra läsning på repeatable read stannar på 5. T1 på repeatable read räknar öppna order och får 2. T2 lägger in order 9 med status open och committar. I SQL-standarden kan nästa räkning på repeatable read bli 3, och nästa räkning på serializable stannar på 2.

Tabellen orders har 100 000 rader och indexet idx_cust_created på (customer_id, created_at). Löven rymmer 100 poster, så det finns 1 000 lövsidor. Fanout är 100, så det finns 10 inre sidor och 1 rot. En punktuppslagning på customer_id = 42 rör 3 sidor. Januarintervallet för den kunden håller 250 poster, packade 100, 100 och 50 från början av ett löv, så det rör 3 lövsidor plus roten och en inre sida, alltså 5 sidor. Villkoret som bara nämner created_at kan inte söka i det här indexet. Dess planrad är SCAN orders, och den genomläsningen läser 100 000 rader. Intervallet som nämner customer_id = 42 och en undre gräns på created_at, och som också hämtar amount, har planraden SEARCH orders USING INDEX idx_cust_created (customer_id=? AND created_at>?).

Kund 42 har 100 order i det intervallet. Att hämta orderna är 1 fråga. Att hämta rader en gång per order är 100 frågor till. Summan är 101 frågor. Med 2 millisekunder per fråga kostar slingan 202 millisekunder. Att hämta orderna och sedan hämta raderna med en IN-lista kostar 2 frågor och 2 × 2 = 4 millisekunder. För en mindre hög med tre order, id 101, 102 och 103, kostar slingan per order 1 + 3 = 4 frågor, och formen med IN-lista kostar fortfarande 2. Raderna som kommer tillbaka stämmer när varje id i IN-listan är ett id som den första frågan gav, och inget extra id lagts till.

Övning

Räkna en andra överföring på papper

Använd samma två konton, 7 på 80 och 8 på 20. För över 25 och committa. Skriv båda saldona, och skriv båda saldona igen för en krasch som kommer efter att debet skrivits och före commit. Skriv summan av det halvt skrivna paret. Lagret börjar på 9. En andra transaktion har satt lagret till 1 och inte committat. Skriv vad den första transaktionen läser vid read uncommitted, vad den läser vid read committed, och vad lagret är efter att den andra transaktionen gjort rollback. Sedan har den första transaktionen på read committed redan läst 9, och den andra committar lager = 4. Skriv den andra läsningen vid read committed, och skriv den andra läsningen vid repeatable read. Öppna order börjar på 4 rader. När en annan transaktion committat en ny öppen order, skriv vad SQL-standarden tillåter att nästa räkning blir vid repeatable read, och vad serializable håller kvar. På samma B-tree, 100 poster per löv och fanout 100, skriv antalet lövsidor och hur många sidor en punktuppslagning rör. Ett intervall på 160 nycklar är packat 100 och sedan 60, med första nyckeln först på ett löv. Skriv sidantalet för det intervallet. Till sist: 40 order hämtar sina rader med var sin fråga, plus den ena orderfrågan, till 2 millisekunder per fråga. Skriv antalet frågor och millisekunderna för den slingan, sedan antalet frågor och millisekunderna för formen med IN-lista.

Den committade överföringen på 25 lämnar 55 och 45. Kraschen före commit lämnar 80 och 20. Det halvt skrivna paret är 55 och 20, och 55 + 20 = 75. Read uncommitted ger 1, read committed ger 9, och rollback lämnar lagret på 9. Den andra läsningen vid read committed är 4, och den andra läsningen vid repeatable read stannar på 9. SQL-standarden tillåter att nästa räkning av öppna order vid repeatable read blir 5, och serializable håller 4. Trädet har fortfarande 1 000 lövsidor, och en punktuppslagning rör 3 sidor. Intervallet på 160 nycklar rör 2 lövsidor plus roten och en inre sida, alltså 4 sidor. Fyrtio order ger 1 + 40 = 41 frågor och 41 × 2 = 82 millisekunder. Formen med IN-lista ger 2 frågor och 4 millisekunder.

Kontrollfrågor

Svara själv först. Öppna sedan frågan för att se svaret.

Konto 7 börjar på 80 och konto 8 på 20. Efter en committad överföring på 30, vilka är de två saldona, och vilka är de om kraschen kommer före commit?

Efter commit är saldona 50 och 50. Om kraschen kommer före commit lämnar rollback 80 och 20.

Lagret börjar på 5. En andra transaktion har skrivit 0 och inte committat. Vad ger en läsning på read uncommitted, och vad ger en läsning på read committed?

Read uncommitted ger 0. Read committed ger 5. 0:an är en dirty read av en skrivning som fortfarande är öppen.

Indexet ligger på (customer_id, created_at), med 100 poster per löv, fanout 100 och 100 000 rader. Hur många sidor rör en punktuppslagning, och hur många sidor rör intervallet på 250 nycklar när nycklarna fyller 3 löv från början av ett löv?

En punktuppslagning rör 3 sidor. Intervallet på 250 nycklar rör 5 sidor: roten, en inre sida och 3 lövsidor.

Hundra order kör var sin radfråga, plus den ena orderfrågan, till 2 millisekunder per fråga. Hur många frågor och millisekunder tar slingan, och hur många frågor och millisekunder tar formen med IN-lista?

Slingan tar 101 frågor och 202 millisekunder. Formen med IN-lista tar 2 frågor och 4 millisekunder.

Ta med dig

Du committar debet och kredit tillsammans, du väljer isoleringsnivå efter den räkning den håller stilla, och du byter en genomläsning av 100 000 rader mot ett indexintervall på 5 sidor och en slinga på 101 frågor mot 2.

Slutuppgift

Lämna in SQL-index, cache-aside-logik och ett backup/restore-skript som klarar automatiska prestandatester (p95 < 15 ms på fixture-hårdvara), tester av stampede-skydd och checksummetester för point-in-time recovery.

Slutuppgiften rättas av fasta facit och automatiska tester när proven öppnar.

Begrepp i kursen

19 begrepp i ordlistan hör till den här kursen.

Fler lektioner kommer

Första lektionen är öppen att läsa. Resten av kursens lektioner skrivs, och prov och diplom kommer senare. Till väntelistan