Mi célt szolgálnak az indexek?
- felgyorsítsák az adatbázisban való keresést
- A lekérdezések indexelt oszlopok nélkül lényegesebben tovább tarthatnak
(tegyük fel, hogy jó a query és az index)
- Segit ervényesiteni az adatbázisos megszorításokat (UNIQUE, FOREIGN KEY)
- Hátránya, hogy több helyet foglal és az adatbázisba való írás, törlés tovább tart
Index típusok
- BTREE: leggyakoribb MySQL index
- RTREE: csak MyISAM-nál
- HASH: MEMORY,NDB
- BITMAP: MySQL nem támogatja
- FULLTEXT: MyISAM, Innodb MySQL 5.6
MyISAM vs Innodb
- MyISAM pointereket használ, ami megmondja, hogy melyik byte-on mi található
- Innodb Primary key: az adatokat fa elrendezésben tárolja
- Innodb Secodary indexek a primary key-t pointerként tárolja
Milyen műveleteknél használhatjuk a BTREE indexeket?
- point lookup: minden sor ahol KEY=5
- open range: minden sor ahol KEY>5
- closed range: minden sor ahol 10>KEY>5
String Index
- Nincs nagy különbség a számokhoz képest
- A LIKE prefix egy különleges tipusa a rangeknek
- LIKE 'ABC%' azt jelenti, hogy 'ABC[lowest]' < KEY < 'ABC[highest]'
- LIKE '%ABC' kifejezés eseten nincs lehetőség az indexek használatára (mivel nem lehet rendezni)
Multiple Column Indexes
- A rendezési sorrend meghatározott. Fontos a definiálási sorrend.
- Egy BTREE indexet hoz létre
- Nem (minden esetben) használja a multipart indexeket ha a lekérdezésben range-t talál (< ; > ; BETWEEN )
- használja az multipart indexeket ha a range IN ()-nel van definiálva
- Nem támogatott: rendezés különböző sorrendben
- Csak az egyenlőség (=) megengedett ha az order by -ban nem szerepel az első kulcs oszlop
- KEY(A,B)
Helyes index használat
- ORDER BY A : rendezés az első oszlop alapján
- A=5 ORDER BY B : szűrés az első oszlop alapján és rendezés a második alapján
- ORDER BY A DESC, B DESC : azonos kritéria alapján való rendezés
- A>5 ORDER BY A : range az első oszlopban, és az szerinti rendezés
Hibás index használat
- ORDER BY B : rendezés a második oszlop szerint
- A>5 ORDER BY B : range az első oszlopban és rendezés a második szerint
- A IN (1,2) ORDER BY B : IN range az első oszlopban
- ORDER BY A ASC, B DESC : különböző sorrendben való rendezés
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));
- A következő lekérdezések használják a name indexet
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test
WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius'
AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test
WHERE last_name='Widenius'
AND first_name >='M' AND first_name < 'N';
Mivel a sorrend fontos ezért az alábbi lekérdezések nem használják a nanem indexet.
SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius' OR first_name='Michael';
Egyéb gondolatok:
INDEX Innodb táblákban
- A PRIMARY KEY valójában minden egyéb indexhez hozzá van adva
KEY(A) valójában KEY (A,primary key)
MIN/MAX optimization
- MIN() / MAX() megfelelő használatával gyorsithatunk a lekérdezés sebességén
INDEX és JOIN
- Nagyon fontos, hogy csak indexelt oszlopokat JOIN-noljunk össze
EXPLAIN
- Fontos ellenőrizni, hogy a lekérdezéseinkben kihasználjuk-e az indexek adta előnyöket
- A lekérdezés elé csak írjuk be, hogy EXPLAIN és nézzük át a kapott eredményt
- key: megadja, hogy milyen kulcsot használ
- rows: vizsgált sorok száma (értlem szerűen minél kisebb ez a szám annál gyorsabb a query)
- extra: hasznos információk
Hozzáteszem erről külön blogot lehetne nyitni.
Tippek:
#1 Range megvariálása
Adott KEY(A,B)
SELECT * FROM TBL WHERE A BETWEEN 2 AND 4 AND B=5;
A fenti query nem fogja használni az multipart-os indexet.
Viszont ha átirjuk a between()-t IN()-re akkor már igen.
SELECT * FROM TBL WHERE A IN(2,3,4) AND B=5;
#2 Range megvariálása
KEY(GENDER,CITY)
SELECT * FROM TBL WHERE CITY="BUDAPEST";
SELECT * FROM TBL WHERE GENDER IN ("ferfi","no") AND CITY="BUDAPEST";
Abban az esetben ha multipart kulcsunk első kulcsa nem tartalmaz sok különböző adatot érdemes elgondolkodni a használatán.
#3 Unio
KEY(A,B)
SELECT * FROM TBL WHERE A IN(2,3) ORDER BY LIMIT 5;
A rendezés miatt nem használ indexet.
Viszont ha külön kérjük le az adatokat és utána az eredmények unióját rendezzük akkor már ki tudjuk használni az indexek által nyújtott előnyöket
(SELECT * FROM TBL WHERE A=2 ORDER BY B LIMIT 5) UNION ALL (SELECT * FROM TBL WHERE A=3 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;
Kicsit el kellett merülnöm a MySQL lelkivilágában és összegyűjtöttem az indexelés téma körben a főbb gondolatokat. Első sorban magamnak, hogy meglegyen.
Ha látsz benne olyan részt ami nem egyértelmű vagy ki kellene egészíteni, jelezd kérlek.
