Jimmys SQL-skola, del 1

Varför har psalmboken innehållsförteckning?

Många av er har säkert varit med om det. En sajt som har fungerat i flera år går plötsligt segt som sirap. Det måste vara något fel på servern. Ingenting har ju ändrats i koden.

Det första man bör tänka i fall som detta, i alla fall med en databasdriven sida (och vilken halvstor sida är inte det på den här sidan av 2000-talet?), är …

Har någon av förutsättningarna, t ex datat, kanske ändrats?

Att leta upp en psalm i ett femsidors häfte utan innehållsförteckning klarar de flesta att göra utan att försämra körens responstid, men när psalmboken växt till 631 sidor så är innehållsförteckningen bra att ha.

Motsvarigheten i databaser är korrekt indexering, vilket är lite av en konstform.

Hur väljer man då sina index för att frågorna ska kunna ställas med rimlig svarstid även med stora mängder data (för vem vill väl inte att order-tabellen ska börja snudda på 10 000-tals rader)?

Det enkla svaret är att det beror på frågorna, men det finns dock ett antal gyllene tumregler som man kan använda sig av för avgöra om en kolumn behöver ett index.

När bör index användas?

Sker det ofta sökningar på kolumnen som returnerar endast ett fåtal av alla rader i tabellen? Isåfall är ett index lämpligt. Detta är psalmboken i ett nötskal. Man frågar …

SELECT * FROM psalms WHERE title =
   'By Babel\'s streams we sat and wept'

… och endast en rad av alla psalmer matchar sökfrågan. Utan index på kolumen title så måste servern söka igenom alla rader tills den hittar psalmen, med index så tar den bara en snabb titt i indexet och hittar rätt psalm direkt.

Är kolumnen en primärnyckel eller en främmande nyckel? Man kan då anta att frågor behöver koppla ihop rader i de bägge tabellerna och därför är det vanligtvis lämpligt att ha index på dessa kolumner. Detta är mest viktigt om man ställer frågor som inte alltid returnerar alla relaterade rader (då servern i sådana fall ändå måste gå igenom alla rader).

Tänk t ex …

SELECT p.title, a.name FROM psalms p INNER JOIN author a ON a.id =
   p.author_id WHERE p.title =
   'By Babel\'s streams we sat and wept'

Utan index på primärnyckeln author.id skulle servern göra:

• Först slår jag upp psalmtiteln i indexet för p.title.
• Jag hittade en rad, snabbt som blixten, nu ska jag leta rätt på författaren med hjälp av mitt JOIN-uttryck.
• Vem är nu author 42? Inte var det första raden i author-tabellen – jag kollar på andra.
• Inte andra raden heller, jag får kolla på tredje …
• … osv, till användaren tröttnar och går till den konkurrerande bibel-sajten.

Med index så blir skeendet ett annat:

• Först slår jag upp psalmtiteln i indexet för p.title.
• Jag hittade en rad, snabbt som blixten, nu ska jag leta rätt på författaren med hjälp av mitt JOIN-uttryck.
• Vem är nu author 42? Jag kollar i innehållsförteckningen för author.id. Aha, så hette han.

Kommer vi efterfråga data sorterat efter en viss kolumn? Isåfall är ett index oftast lämpligt (ett så kallat ”clustered index” är att föredra men i MySQL, vilket vi som bekant erbjuder, så får man sådana bara för primärnycklar, eller det första unika indexet utan NULL-värden i InnoDB, med MyISAM så lagras index och data separat, så man kan inte ha data sorterat efter ett index, vilket är definitionen av ett ”clustered index”).

Kommer vi göra begränsande uttryck som består av flera kolumner, men i gengäld reducerar antalet rader i svaret avsevärt? Då är det lämpligt att ha ett kombinerat index på flera av dessa kolumner. Ta t ex:

SELECT p.title, a.name FROM psalms p INNER JOIN author a ON a.id =
   p.author_id WHERE p.title =
   'By Babel\'s streams we sat and wept' AND p.lang = 'en'

här kan ett kombinerat index på p.title och p.lang vara lämpligt:

CREATE INDEX idx_combined_title_lang ON psalms(title, lang)

Dock så får man avväga hur mycket varje extra kolumn reducerar det genomsnittliga antalet rader i svaren. Om man bara har ett språk i databasen så skulle indexet ovan inte vara bättre än det förra på enbart psalms.title. Tvärtom skulle det vara sämre, då det tar upp mer utrymme på disk och ännu värre, mer minne i index-cachen. Det ökar också arbetsmängden som behöver göras varje gång en rad läggs till eller uppdateras i tabellen. Med 300 språk i databasen så är situationen dock kanske en annan (i slutändan så måste man ändå mäta för att vara säker på vad som är lämpligast).

När ska man inte använda index då?

När man aldrig gör sökningar på en kolumn. Då gör man uppdatering och tillägg av data långsammare utan vinst.

När allt data i kolumnen är nästan identiskt. Om man gör sökningar, men sökningarna alltid returnerar ett stort antal rader från tabellen så är inte index lika lämpligt som när begränsningen blir förhållandevis stor.

Om man har en kolumn där antalet rader är högt och radernas data i kolumnen har en hög genomsnittslängd så är det ofta olämpligt att göra ett index på kolumnen då indexet tar upp för mycket minne i index-cachen. Man kan då istället göra ett index på ett några tecken långt prefix för kolumen.

Ett sätt att ta reda på hur långt prefix som är lämpligt är att helt enkelt kolla:

SELECT COUNT(*), SUBSTR(title, 1, 1) AS 'prefix'
   FROM psalms GROUP BY prefix WITH ROLLUP;
SELECT COUNT(*), SUBSTR(title, 1, 2) AS 'prefix'
   FROM psalms GROUP BY prefix WITH ROLLUP;
SELECT COUNT(*), SUBSTR(title, 1, 3) AS 'prefix'
   FROM psalms GROUP BY prefix WITH ROLLUP;

… och så vidare, sedan väljer man den lägsta längd (tredje parametern till SUBSTR) där antalet unika prefix börjar närma sig antalet rader i tabellen. I det optimala fallet så ska alltså antalet rader som frågan ovan returnerar vara värdet för COUNT(*) för ROLLUP-raden (den där prefix=NULL) + 1. Då är alla rader unika i de första N kolumnerna (där N är prefix-längden som användes i frågan). I detta fall har även alla grupper COUNT(*)=1, eftersom det bara är en rad med det prefixet.

Det här är ju jättebra, men hur analyserar jag den här komplicerade frågan?

I MySQL så är EXPLAIN din vän. I manualen så tipsas det om att man bör kolla på de rader i EXPLAIN-utmatningen där key är NULL, där type är range, index eller ALL eller där Extra innehåller ”Using filesort” eller ”Using temporary”.

Dessa rader ger en indikering på vilka tabeller i frågan som söks igenom utan index, vilket allt som oftast ger ett dåligt resultat med mycket data. Det finns även en kolumn som anger hur många rader som fråge-optimeraren uppskattar att servern ska behöva behandla för att ta fram frågeresultatet för tabellen i fråga. Höga värden där (kolumnen heter rows) är ofta synonymt med lång exekvering.

En annan variant, som vi rekommenderar starkt, är att ni skickar in frågor som ni har problem med till oss på askjimmy@loopia.se, så tar vi upp så många som möjligt av dem i nästa del av denna artikelserie. Ni får även gärna skicka in andra luriga frågor av SQL-karaktär så gör jag mitt bästa för att hitta en bra lösning åt er.

Det var allt för den här gången. Tills nästa gång så får jag önska er trevliga JOINs, trevligt aggregerande och all lycka i ert indexeringsarbete.