Mysql-indexek kezdőknek
Kezdjük azzal, hogy gyakran látni kapcsolatos hibák létrehozása az index MySQL. Sok fejlesztő (és nem csak az új MySQL) létre sok indexek az oszlopokat, amelyeket használni fognak a mintákban, és úgy vélik, hogy ez a legjobb stratégia. Például, ha azt kell, hogy végre egy lekérdezés, mint AGE = 18 ÉS ÁLLAM = „CA”, sokan egyszerűen létrehozhat 2 külön index oszlopok és AGE ÁLLAM.
Sokkal jobb (itt és az alábbiakban figyelmét fordító :. És általában az egyetlen helyes) stratégia az, hogy hozzon létre egy kombinált típusú index (AGE, állam). Nézzük meg, hogy miért van ez így.
Általában (de nem mindig) indexek MySQL B-fán alapuló indexek - az index az ilyen típusú képes gyorsan megtekintheti szereplő információk a prefixumok és kereshetőség tartományok rendezve értékeket. Például, ha az Ön által kért AGE = 18 B-fán alapuló index az oszlop AGE MySQL megtalálja az első sort a táblázatban a kérelemnek megfelelő, és folytassa a keresést, amíg meg nem találja a megfelelő első sorban -, akkor leállítja a keresést, mert Úgy véli, hogy további semmit nem alkalmas. Zenekarok, mint például az érdeklődés az űrlap 18 és 20, működik hasonló módon - MySQL megáll más értékeket.
Valamivel bonyolultabb a helyzet lekérdezések, mint életkor (18,20,30), mivel MySQL valójában át többször index.
Tehát, már megbeszéltük, hogy a MySQL keresi az index, de nem határozzák meg, hogy visszatér a keresést követően - általában (ha nem beszélünk, amely (amelyek) index) kap egy „pointer string”, ami lehet az értéke az elsődleges kulcsot (ha használ InnoDB motort ), a fizikai ellensúlyozza a fájl ( `MyISAM„) vagy valami ilyesmi. Fontos, hogy a belső MySQL motor ezen index találni egy teljes sor az összes szükséges adatot megfelelően az adott index értékét.
És mik a lehetőségek a MySQL, ha létre két különböző index? Ez akár csak az egyiket választhatja ki a megfelelő sort (majd szűrni a kinyert adatokat az irányított WHERE -, de használata nélkül indexek), vagy kap egy pointert a húr minden a megfelelő indexek és kiszámítja a kereszteződés, majd vissza az adatokat .
Merre van megfelelőbb függ szelektivitás és korrelációs indexek. Ha WHERE munka után ki az első oszlopot kijelölt sorok 5% -ot, és a nagyobb WHERE második oszlop Vonalszűrők 1% -át, a használata csomópontok, persze, van értelme. De ha a második WHERE kiszűrni csak 4,5%, ez általában sokkal előnyösebb, hogy csak az első index és szűrésére nem kívánt vonalak után az adatok kinyerése.
Nézzünk néhány példát:
CREATE TABLE 'idxtest' (
'I1' int (10) előjel nélküli NOT NULL,
'I2' int (10) előjel nélküli NOT NULL,
'Val' varchar (40) alapértelmezett nulla,
KEY 'i1' ( 'i1'),
KEY 'i2' ( 'i2'),
KEY 'kombinált' ( 'i1', 'i2')
) MOTOR = MyISAM DEFAULT CHARSET = latin1
Hoztam létre egy oszlopra I1 és I2 egymástól független, és mindegyikük választja ki mintegy 1% -a a sorok a táblázat, amely összesen 10 Mill. Recordings.
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest AHOL I1 = 50 és I2 = 50;
Mint látható, a MySQL használatát választotta kombinált index és a lekérdezés végre kevesebb, mint 10 ms!
Most tegyük fel, hogy van egy kód csak az egyes hangszórók (mondani az optimalizáló figyelmen kívül hagyni a kombinált index):
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest IGNORE INDEX (kombinált), AHOL I1 = 50 és I2 = 50;
Mint látható, ebben az esetben a MySQL végrehajtott keresés kereszteződés indexek, és hajtsa végre a lekérdezést vette 70 ms - 7-szer hosszabb!
Most nézzük meg, mi történik, ha csak egy index és adatok szűréséhez:
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest IGNORE INDEX (kombinált, i2) AHOL I1 = 50 és I2 = 50;
Ebben az időben, a MySQL volt, hogy ennél jóval több sort, és a lekérdezés kivégzés 290 ms. Látjuk tehát, hogy a használata indexek metszési sokkal jobb, mint egy index, de ez sokkal jobb, hogy egy kombinált indexet.
Azonban ez a probléma nem ér véget a kereszteződés index. Jelenleg a lehetőségét, hogy ez az eljárás a MySQL jelentősen korlátozott, így a MySQL használja őket nem mindig:
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest IGNORE INDEX (kombinált), AHOL I1 = 50 és I2 IN (49,50);
Egyszer egy kérelmet az egyik oszlop válik összehasonlítás és átadása, MySQL már nem használja a kereszteződés indexek, annak ellenére, hogy ebben az esetben a kérelem I2 (49,50) nem lenne több, mint indokolt, mivel . kérés továbbra is meglehetősen szelektív.
Most tölteni még egy tesztet. Azt leszedte az asztalt, és újra megtöltötte adatokat oly módon, hogy az értékek az I1 és I2 erősen korrelál. Tény, hogy ma már általában a következők:
mysql> UPDATE idxtest SET I2 = I1;
Query OK, 10900996 érintett sorok (6 perc 47.87 másodperc)
Sorok párosított: 11010048 Megváltozott: 10900996 Figyelmeztetések: 0
Lássuk, mi fog történni ebben az esetben:
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest AHOL I1 = 50 és I2 = 50;
Az optimalizáló úgy döntött, hogy használja a kereszteződés indexek, bár talán ez volt a legrosszabb megoldás! A lekérdezés vett 360 ms. Szintén figyelni nagy hiba az értékelést a hozzávetőleges sorok számát.
Ez történt annak a ténynek köszönhető, hogy a MySQL feltételezi oszlopában lévő értékek az I1 és I2 független, ezért úgy dönt, a kereszteződés indexek. Tény, hogy nem tudja elképzelni, a másik, mert statisztikai adatok a korrelációs értékek nincs oszlopokat.
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest IGNORE mutatószáma (I2) WHERE I1 = 50 és I2 = 50;
És most, amikor tilos használni MySQL index i2 oszlopon (és ezért nem találja a kereszteződés az index), akkor az index egyetlen oszlop, és nem vonhatók össze. Ez történt így, mert a MySQL van statisztikákat hozzávetőleges számát befolyásoló sorok, és mivel ez azonos a két indexek, MySQL választotta kisebb. Lekérdezés futtatásának vett 290 ms újra - pontosan ugyanaz, mint legutóbb.
Force MySQL hogy csak egyesített index:
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest IGNORE INDEX (i1, i2) AHOL I1 = 50 és I2 = 50;
Úgy látszik, hogy a MySQL körülbelül 20% a rossz becslése száma keresett egymás után, ami természetesen nem igaz, hiszen Ez ugyanazt a prefix, mint ha az index csak i1 oszlopon. MySQL nem tudja ezt, mert statisztika megtekintéséhez az egyes indexek, és nem próbálja meg azok összehangolására.
Tekintettel arra, hogy az alkalmazott kombinációs index nagyobb, mint egy oszlop index, lekérdezés kivégzés 300 ms.
Látjuk tehát, hogy a MySQL dönthet a kereszteződés indexek, akkor is, ha ez a legrosszabb lehetőség, bár technikai szempontból, ez minden bizonnyal a legjobb terv, tekintve, hogy más statisztikák nem.
Vannak egyszerű módon, hogy a MySQL nem használja a kereszteződés indexek, de sajnos nem tudom, hogyan lehet azt használni az átkelés, ha úgy véli, ez az opció optimális. Remélem, hogy egy ilyen lehetőség a jövőben lesz hozzá.
Végül nézzük meg a helyzetet, amikor az indexek metszési megtaláló eljárással sokkal jobban működik, mint a kombinált indexek több oszlopot. Beszélünk, amikor használja, vagy amikor kiválasztja az oszlopok között. Ebben az esetben egy kombinált index használhatatlanná válik, és a MySQL van egy választás között egy teljes asztalnál Vizsgálat (Teljes vizsgálat) és a teljesítő egyesület (UNION) helyett keresési metszési adatok értékeket, amelyekre már megkapta a hang asztalra.
Ismét én változott az értéket I1 és I2 oszlopokat úgy, hogy azok tartalmazzák a független adatok (a tipikus helyzet a táblázatokat).
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest AHOL I1 = 50 vagy I2 = 50;
Ilyen végzett lekérdezések 660 ms. Letiltása az index a második oszlopban megkapjuk a FULL SCAN:
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest IGNORE mutatószáma (I2) WHERE I1 = 50 vagy I2 = 50;
Vegye figyelembe, hogy a MySQL van i1 kulcs, kombinálni lehet használni, de valójában ez a lehetőség nem. Végrehajtása az ilyen kéréseket vesz 3370 ms!
Szintén fontos megjegyezni, hogy az a megkeresés volt 5-ször hosszabb, annak ellenére, hogy a FULL SCAN eltelt körülbelül 50-szer több sor. Ez azt mutatja, egy nagyon nagy különbség a teljesítmény közötti teljes megnyitása az asztal és a hozzáférési kulccsal, amely úgy 10-szer hosszabb (abban az értelemben, az „érték” hozzáférési vonal), annak ellenére, hogy végezzük a memóriában.
Abban az esetben, optizator UNION jár fejlettebb, és képes megbirkózni a tartományok:
mysql> EXPLAIN SELECT avg (hossz (Val)) SZÁRMAZÓ idxtest AHOL I1 = 50 vagy I2 (49,50);
A legtöbb esetben a kombinált indexek több oszlopot a legjobb megoldás, ha használja között ilyen oszlopok WHERE. A kereszteződés indexek elvileg növeli a teljesítményt, de ez még mindig lényegesen rosszabb, mint amikor egy billentyűkombinációt. Ha használja vagy az oszlopok között meg kell, hogy az index minden oszlop MySQL tudta találni a kereszteződés, és a kombinált indexek nem lehet használni az ilyen kéréseket.
Minden MySQL indexek (elsődleges, egyedi, és INDEX) tárolt B-fát. Strings automatikusan tömörítésre, hogy távolítsa el a hiányosságokat a elő- és záró szóközöket (lásd 6.5.7, «CREATE INDEX Syntax").
Az indexek használatával:
SELECT MIN (key_part2), MAX (key_part2) SZÁRMAZÓ tábla_neve ahol key_part1 = 10
- Rendezéséhez, vagy csoportosítása a táblázatban, ha ezek a műveletek történik egy bal szélső előtag a használt kulcs (például ORDER BY key_part_1, key_part_2). Ha minden fontos alkatrész is a DESC, a kulcs olvasni fordított sorrendben (lásd 5.2.7 „Hogyan MySQL optimalizálása ORDER BY»).
- Bizonyos esetekben, a lekérdezés optimalizálni lehet letölteni értékek megkérdezése nélkül az adatállományban. Ha az összes oszlopot, amelyet néhány tábla numerikus és forma legbaloldalibb előtagot kulcsfontosságú, hogy egy nagy sebességű, a kívánt érték lehet letölteni közvetlenül az index fa:
SELECT key_part3 FROM tábla_neve WHERE key_part1 = 1
Tegyük fel, hogy kiadja a következő nyilatkozatot SELECT:
mysql> SELECT * FROM WHERE tbl_name col1 = ért1 ÉS col2 = ért2;
Ha van egy multi-oszlop index col1 és col2, a megfelelő sort lehet letöltésre közvetlenül. Abban az esetben, oszlop col1 és col2 létezik külön kódokat, az optimalizáló megpróbálja megtalálni a leginkább korlátozó index meghatározásával, hogy mely indexet találja kevesebb sort, és használja az indexet, hogy letölteni ezeket a sorokat.
Ha a tábla több oszlop index, minden legbaloldalibb előtag az index lehet optimalizáló által használt megtalálni sorokat. Például, ha van egy index a három oszlop (col1, col2, Col3), akkor fennáll a lehetősége egy indexelt kereső (col1), (col1, col2) és (col1, col2, Col3).
MySQL nem lehet használni a részindex, ha az oszlopok nem alkotnak legbaloldalibb előtag az index. Tegyük fel, hogy van egy SELECT utasítást az alábbiak szerint:
mysql> SELECT * FROM WHERE tbl_name col1 = ért1;
mysql> SELECT * FROM WHERE tbl_name col2 = ért2;
mysql> SELECT * FROM WHERE tbl_name col2 = ért2 ÉS Col3 = val3;
mysql> SELECT * FROM WHERE tbl_name col1 = ért1 ÉS col2 = ért2;
Ha egy index létezik (col1, col2, Col3), az első és a negyedik lekérdezést felett használja az indexet. A második és harmadik kérések csinálni közé indexelt oszlopok, de (col2) és (col2, Col3) nem a bal szélső részén előtagokat (col1, col2, Col3).
* Ugyanakkor, az indexek nem fog működni, függetlenül, hogy milyen típusú index, azaz és az index típusa: INDEX és egyedi index típusú működni fog nagyon gyorsan.
MySQL indexek is alkalmazni HASONLÓ összehasonlítást, ha az argumentum LIKE expresszió állandó karaktersorozat nem úgy kezdődik egy sablont jelképe. Például az alábbi SELECT utasítások használni indexek:
mysql> SELECT * FROM WHERE tbl_name key_col mint a "Patrick%";
mysql> SELECT * FROM WHERE tbl_name key_col mint a "Pat% _ck%";
Csak sorok venni az első csapat „Patrick” <= key_col <"Patricl", а во второй - только строки с "Pat" <= key_col <"Pau".
Az alábbi SELECT utasítások nem fogja használni indexek:
mysql> SELECT * FROM WHERE tbl_name key_col LIKE "% Patrick%";
mysql> SELECT * FROM WHERE tbl_name key_col LIKE other_col;
Az első csapat, a LIKE érték kezdődik helyettesítő karaktert. A második parancs LIKE érték nem állandó.
A MySQL verzió 4.0 keletkezik egy másik optimalizálás LIKE kifejezés. Ha egy kifejezés. LIKE „% sztring%”, és a string hossza (string) több, mint 3 karakter, MySQL fogja használni egy algoritmus Turbo Boyer-Moore inicializálni egy string sablont, majd használja ezt a sablont, a kereséshez gyorsabb.
Ha keres a oszlop_neve IS NULL fogja használni indexek ha oszlop_neve az index.
MySQL általában használ az index, amely megkeresi a legkisebb a sorok számát. Az index használják oszlopokat össze az alábbi szereplők: =,>,> =, <, <=, BETWEEN и LIKE с префиксом, не содержащим шаблонного символа, такого как something%.
Ha az index nem fogja minden szinten és a WHERE, nem használják, hogy optimalizálja a lekérdezést. Más szóval: hogy képes legyen használni az indexet, egy előtag az index kell vonni minden ÉS csoportban.
A következő WHERE használni indexek:
AHOL index_part1 = 1 ÉS index_part2 = 2 és other_column = 3
. AHOL index = 1 vagy A = 10 és az index = 2 / * index = index = 1 vagy 2 * /
. WHERE index_part1 = 'hello' AND index_part_3 = 5
/ * Optimalizált, mint egy "index_part1 = 'hello'" * /
. AHOL index1 = 1 és indexe2 = 2 vagy index1 = 3 és index3 = 3;
/ * Használhatja az index index1, de nem indexe2 vagy index * 3 /
A következő WHERE indexeket nem használó:
. AHOL index_part2 = 1 ÉS index_part3 = 2
/ * Index_part_1 nem használja * /
. AHOL index = 1 vagy A = 10
/ * Ne használja indexek mindkét oldalán az AND * /
. AHOL index_part1 = 1 OR index_part2 = 10
/ * Nincs index, amely magában foglalja az összes vonal * /
Bizonyos esetekben, a MySQL nem fogja használni az index, akkor is, ha ez lehetséges. Néhány példát az ilyen helyzetek alábbiakban adjuk meg: