MySQL tips: Bästa sätt att hämta data ur tung dbtabell

Risken att jag nån gång kommer stöta på er i arbetslivet framkallar vågor av panik i mig och lämnar mig med en känsla av att det digitala samhället kommer göra en core dump när nästa generation ska ta över.

Lyssna noga nu. 50K rader i en tabell är absolut ingenting. Jag kan inte understryka detta nog. Om du så har gjort alla fel i boken två gånger och dessutom portat BeOS till ABC80 och kör din mysql-server på den hojen borde en SELECT * på 50K poster inte märkas.
Om utvecklare inte bemödar sig att ta reda på vad ett databasindex är föreslår jag att utvecklaren i fråga väljer ett annat sätt att lagra sitt data. T.ex en textfil och gangnar sig av regular expressions för att hämta fram det aktuella datat.

Databasindex är till för flera saker. Det ni pratar om här är att snabba upp en selectering av en datamängd. Det är en anledning. Men ett databasindex är först och främst ett sätt att RELATERA information till ANNAN information.

Till exempel kan man ta alla elever som går på daglig verksamhet i regi av samhall, tilsammans med alla skolor som samhall har i Sverige, och genom att indexera detta med ett enkelt nummer, påvisa vilken elev som går på vilken skola. Den enklaste formen av relation som finns, då en elev tillhör en skola. 1 till 1. Ett annat exempel är relationen Personnummer till namn.

Men ibland blir det klurigt. Ibland kan en sak vara relaterad till många andra. Vissa elever har speciella behov. Då kan man få hjälp av hjälpmedelscentralen. Det vore ganska taskigt av hjälpmedelscentralen om de vägrade låna ut mer än ett hjälpmedel till varje behövande. Vad ska Börje göra nu?
Vi behöver relatera den behövandes personnummer med varje hjälpmedel. Men personnumer är ju unikt. Det här tål att tänkas på.
Vi gör helt enkelt så att vi skapar en databstabell med kolumnerna personnumer och hjälpmedelsnr, och här kommer det eleganta, vi skapar ett index som innehåller BÅDA kolumnerna och talar om att detta sammansatta index ska vara unikt.
Vi har inte bara löst problemet med att kunna spara vilka hjälpmedel Börje behöver, vi har dessutom MODELLERAT vår databsmodell så att Börja inte kan låna samma sak två gånger! Wow!

Relationsdatabaser skall per definition använda relationer. Relationer skapas med hjälp av index.

Om du inte fattar ett jota av vad jag snackar om, ha för vara att ALLTID göra på följanade sätt. Varje gång du skapar en tabell i en databas så se till att den första kolumnen är ett löpnummer med AUTO-INCREMENT som du döper till samma sak som tabellen + 'id'. Till exempel ska tabellen tblUser innehålla UserId som första kolumn, och den kolumnen skall vara indexerad som PRIMARY.

UserId INT NOT NULL AUTO_INCREMEN;
PRIMARY KEY (UserId);

Efter detta lägger du in resten av dina kolumner i tabellen. Att du oundvikligen kommer att använda fel abstraktionsnivå när du modellerar ditt data kan du nu strunta i, för med denna enkla åtgärd av dig möjliggör du för en stor pojke att rätta till dina misstag och konvertera ditt data.

Björn Gustavsson hade fel, det är inte Priests i WOW som sprider hat på Internet..

För att förtydliga denna punk är det naturligvis så att relationer definieras av keys. Som i sin tur är unika begrepp i en klump med data. T.ex MySQL's keyword PRIMARY lägger till en unik constraint, alltså en begränsning att detta värde ej får förekomma två gånger eller vara null, på den kolumn som avses. Av nån anledning brukar jag benämna dessa unika begrepp, som vanligt folk kallar unika nycklar, för unika index eller primär index.

Fast du måste ju inte ha ett index för att kunna relatera, även fast man kanske alltid ska ha det. Så det är väl ändå först och främst för att snabba upp sökningar, precis som namnet index antyder.

Fast Primary Key är per default indexerat i de flesta eller alla DBMS.

Primary key är väl mer ett slags index. Du kan ju inte ha primary key och index t ex. (eller?)

Hmm...


Det bör ju tilläggas att en 1-1 relation oftast inte skall vara en "relation" överhuvudtaget utan bör ligga tillsammans i samma tabell, speciellt namn och personnummer. Och relationen skola-elev är knappast 1-1.

Sen det här med relationer och index som du skriver om... Att sätta index på något gör det inte till en relation i databasen. En foregin key gör det till en relation. Index sätter du på fält som du vill kunna söka snabbt på, eller i fallet med unique att du vill se till att bara en rad får ha ett specifikt värde över en eller flera fält, vilket i princip inte behöver vara ett index men i praktiken oftast är det.

Primary index är inte samma sak som ett unikt index, primary index använder man för att definiera id't för en rad/post, för att tillse att man snabbt kan hämta ut de rader man vill ha, vilket också råkar vara unikt. Unikt index i sig kan du sätta på vilka fält du vill, där du vill att ett värde (eller flera tillsammans) bara får förekomma i en rad/post. Exempelvis inloggningsnamn för en användare, det behöver inte vara kopplat till en relation. Det kan du också använda tillsammans med foregin key om du vill att en rad/post ska ha en 1-1 relation med ett annat objekt.

Primary Key är även Unique och Not Null. Min poäng var mest att har du satt PK har du en okej uppbyggnad av index redan där, särskilt som alla attribut du joinar på (FK) skall vara indexerade då. (Sen att man vill indexera lite mer är en annan femma, men...)

Man skrev mycket 'halvsanningar' här.

Man skappar inte relationner med en index ! Relation finns eller inte. En index är bara ett verktyg för att bland annat snabba upp databasen: jag skappar en databas med ett antal tabell UTAN index. Det går koppla olycka tabell med SQL frågor. Det blir mer eller mindre långsamt om man har (eller inte) index , eller om det är många (eller inte) rader i tabeller.

PRIMARY KEY eller UNIQUE är bara en 'constraint'. Den säger att bara en (eller flera) kolumn i en rad i tabell ska ha en viss värde. De flesta DBMS väljer att indexera per automatik primär nyckel för att snabba upp systemet: annars måste den gå igenom hela tabell för att kontrollera varje insert/update. Det finns DBMS som inte gör det: Oracle RdB (Obs inte det vanliga Oracle). man skappar constraint och index separat. det ger en viss flexibilitet: man kan skappa en index som hjälper att komtrollera primär nyckeln och snabba upp en del andra SQL frågor: en index för flera mål istället för flera index.
Det går att göra smma sak i Oracle: man skappar en index med de kolumn som är PK innan man definierar PK. Index behöver inte vara begränsat till PK bara den hjälper till så skappar inte Oracle en till index.
För små tabeller där en eller 2 disk io hämtar alt data behöver man inte en index på en PK eller det blir bara en disk IO till.
Man hittar idag i många schema tabeller med flera "PK" eller unique data speciel i MS SQL db där utvecklare använder mycket av incremental id men där det finns andra sett att identifiera en rad:
Exempel: en user tabell där man har en incrimental id, och en personnummer där båda är unique och kan vara PK
Man ska tänka att en index brukar hjälpa för hämta data med att det kan blir lånsammare att gör insert/update.

Sen ska man säga att det finns väl ett sätt att normalisera en databas schema. Men det betyder inte att det är det bästa: man ska tänka på hur databasen används:
Vad för SQL insert/update/select görs och hur ofta. Hur mycket data det är. Och då kan man se vad man ska göra: indexera eller inte skappa constraint eller inte (foreign key, not null, primary key, unique, ...).

Mycket prat här.

Så komplicerat är det inte, skapa bara ett index på ditt where-uttryck, weekid.

Det går med stor säkerhet inte alls snabbare att göra select på vissa kolumner, istället för *. Detta beroende på hur MySQL lagrar rader internt. Hela raden måste ändå läsas, det skulle bara gå extra CPU att filtrera bort de kolumner som inte önskas.

50 000 rader är litet. 100 miljoner rader och däröver klassar jag som stort.