MySQL och index

Optimering av databaser är ett område som det går att skriva hur mycket som helst om. I detta inlägg kommer vi fokusera på indexeringen i MySQL.

Skillnaden mellan en förfrågan som är indexerad och en som inte är det kan vara enorm, från en bråkdel av en sekund till flera minuter och i värsta fall ännu längre. MySQL låter oss använda ett index per förfrågan. Om flera tabeller joinas kan ett index användas per tabell och om en tabell joinas flera gånger i samma förfrågan kan olika index användas för varje gång.

MySQL erbjuder tre olika typer av index:
PRIMARY KEY
UNIQUE
INDEX

PRIMARY KEY
Alla fält måste vara ickenull och alla rader i tabellen måste vara unika. En tabell får ha max ett index av typen PRIMARY KEY. För InnoDB lagras en tabells rader sorterade på PRIMARY KEY, för MySQL behöver ej raderna vara lagrade i den ordningen.

UNIQUE
Fält kan vara null men de rader som har ett värde måste vara unika.

INDEX
Fält kan vara null och raderna behöver ej vara unika. Ett INDEX kan bestå av en kombination av flera fält.

Vi ska nu titta lite närmare på index av typen INDEX som består av flera fält.

MySQL använder fält i index från vänster till höger och en förfrågan kan använda delar av ett index. Används en del av ett index, så måste det vara fälten från vänster håll. I detta exempel har vi ett index som består av fälten x, y och z. Vi kan använda indexet för uppslag på fältet x, fälten x och y och fälten x,y och z men ej för fältet y eller för fältet z.

Vid en förfrågan som använder flera fält i ett index, ska fälten till vänster helst ha konstanta värden. Resultaten är sorterade i ett index från vänster till höger för varje fält. Alla vänstra fält med konstanta värden i ett index sorteras rätt vid en förfrågan. Om det förekommer ett fält till höger kommer även det bli rätt sorterat.

Exempel på förfrågningar:

SELECT * FROM foo ORDER BY x; — BRA
SELECT * FROM foo ORDER BY z; — DÅLIG
SELECT * FROM foo WHERE x = 123 ORDER BY x; — BRA
SELECT * FROM foo WHERE x = 123 ORDER BY y; — BRA
SELECT * FROM foo WHERE x = 123 ORDER BY z; — DÅLIG
SELECT * FROM foo WHERE x > 123 ORDER BY x; — BRA
SELECT * FROM foo WHERE x > 123 ORDER BY y; — DÅLIG
SELECT * FROM foo WHERE x = 123 AND y = 123 ORDER BY z; — BRA
SELECT * FROM foo WHERE x = 123 AND y > 123 ORDER BY z; — DÅLIG