Mi az index?
A MySQL indexei szervezetten, szekvenciálisan rendezik az adatokat. Az oszlop (ok) on jönnek létre, amelyek az adatok szűrésére szolgálnak. Gondoljon egy indexre, mint ábécé szerint rendezett listára. Könnyebb a betűrendbe rendezett neveket megkeresni, mint a nem rendezett neveket.
Az index használata a gyakran frissített táblákon gyenge teljesítményt eredményezhet. A MySQL ugyanis minden alkalommal létrehoz egy új indexblokkot, amikor az adatok hozzáadódnak vagy frissülnek a táblázatban. Az indexeket általában olyan táblákon kell használni, amelyek adatai nem változnak gyakran, de sokat használnak a kiválasztott keresési lekérdezésekben.
Mire használható az Index?
Senki sem szereti a lassú rendszereket. A magas rendszer teljesítmény szinte minden adatbázis-rendszerben kiemelt fontosságú. A legtöbb vállalkozás jelentős összegeket fektet a hardverbe, hogy az adatok visszakeresése és manipulálása gyorsabb lehessen. De a vállalkozás által megvalósítható hardverbefektetéseknek korlátai vannak. Az adatbázis optimalizálása olcsóbb és jobb megoldás.
A válaszidő lassúsága általában annak köszönhető, hogy a rekordokat véletlenszerűen tárolják az adatbázis-táblákban. A keresési lekérdezéseknek egymás után kell körbevezetniük a véletlenszerűen tárolt rekordokat, hogy megtalálják a kívánt adatokat. Ez gyenge teljesítményű adatbázisokat eredményez, amikor nagy táblákból kell adatokat visszakeresni. Ezért az indexeket olyan adatokkal rendezik, amelyek megkönnyítik a keresést.
Szintaxis: Index létrehozása
Az indexeket kétféleképpen lehet meghatározni
- Asztalkészítés idején
- Miután létrehozta az asztalt
A myflixdb-nél sok keresést várunk az adatbázisból a teljes néven.
A "full_names" oszlopot hozzáadjuk az Indexhez egy új "members_indexed" táblázatban.
Az alább látható szkript segít abban, hogy ezt elérjük.
CREATE TABLE `members_indexed` (`membership_number` int(11) NOT NULL AUTO_INCREMENT,`full_names` varchar(150) DEFAULT NULL,`gender` varchar(6) DEFAULT NULL,`date_of_birth` date DEFAULT NULL,`physical_address` varchar(255) DEFAULT NULL,`postal_address` varchar(255) DEFAULT NULL,`contact_number` varchar(75) DEFAULT NULL,`email` varchar(255) DEFAULT NULL,PRIMARY KEY (`membership_number`),INDEX(full_names)) ENGINE=InnoDB;
Hajtsa végre a fenti SQL parancsfájlt a MySQL munkaasztalon a "myflixdb" ellen.
A myflixdb frissítése az újonnan létrehozott, member_indexed nevű táblázatot mutatja.
A "Megjegyzés" members_indexed táblában az "indexek" csomópontban "teljes_nevek" szerepelnek.
Ahogy bővül a tagok száma és növekszik a rekordok száma, a WHERE és ORDER BY záradékot használó members_indexed tábla keresési lekérdezései sokkal gyorsabbak lesznek, mint azok, amelyek a tagok tábláját az index nélkül definiálták.
Index alapszintaktika hozzáadása
A fenti példa létrehozta az indexet az adatbázis-tábla meghatározásakor. Tegyük fel, hogy már van megadva egy táblánk, és a keresési lekérdezések nagyon lassúak. Túl sokáig tartanak az eredmények visszaadása. A probléma kivizsgálása után rájövünk, hogy jelentősen javíthatjuk a rendszer teljesítményét azáltal, hogy létrehozzuk az INDEX-et a WHERE záradék leggyakrabban használt oszlopában.
A következő lekérdezést használhatjuk index hozzáadásához
CREATE INDEX id_index ON table_name(column_name);
Tegyük fel, hogy a keresési lekérdezések a mozitáblán nagyon lassúak, és egy indexet szeretnénk használni a "film címén" a lekérdezések felgyorsításához, ennek eléréséhez a következő szkriptet használhatjuk.
CREATE INDEX `title_index` ON `movies`(`title`);
A fenti lekérdezés végrehajtása indexet hoz létre a filmek táblázat cím mezőjében.
Ez azt jelenti, hogy a filmasztalon a "cím" használatával minden keresési lekérdezés gyorsabb lesz.
A filmtábla többi mezőjére vonatkozó keresési lekérdezések azonban továbbra is lassabbak lesznek, mint az indexelt mező alapján.
Megjegyzés: Ha szükséges, több oszlopban is létrehozhat indexeket, attól függően, hogy mely mezőket kívánja használni az adatbázis keresőjében.
Ha meg akarja tekinteni az adott táblán definiált indexeket, akkor a következő szkriptet használhatja erre.
SHOW INDEXES FROM table_name;
Vessünk egy pillantást a myflixdb filmtáblázatában meghatározott összes indexre.
SHOW INDEXES FROM `movies`;
A fenti szkript futtatása a MySQL munkapadban a myflixdb-vel szemben eredményeket ad a létrehozott indexről.
Megjegyzés: Az asztalon lévő elsődleges és idegen kulcsokat a MySQL már indexelte. Minden indexnek megvan a maga egyedi neve, és megjelenik az az oszlop is, amelyen meghatároztuk.
Szintaxis: Cseppindex
A drop parancs a táblázatban már definiált indexek eltávolítására szolgál.
Előfordulhat, hogy már definiált egy indexet egy gyakran frissülő táblán. Érdemes eltávolítania az indexeket egy ilyen tábláról az UPDATE és az INSERT lekérdezések teljesítményének javítása érdekében. Az index szétesése, amelyet egy index indexeléséhez használunk, a következő.
DROP INDEX `index_id` ON `table_name`;
Most nézzünk meg egy gyakorlati példát.
DROP INDEX ` full_names` ON `members_indexed`;
A fenti parancs végrehajtásával az index a "teljes_nevek" azonosítóval leesik a members_indexed táblából.
Összegzés
- Az indexek nagyon hatékonyak a MySQL keresési lekérdezések teljesítményének nagymértékű javítása terén.
- Az indexek meghatározhatók egy táblázat létrehozásakor, vagy később hozzáadhatók, miután a tábla már elkészült.
- Táblázatokat egynél több oszlopban definiálhat.
- A SHOW INDEX FROM tábla_neve a megadott indexek megjelenítésére szolgál egy táblán.
- A DROP paranccsal definiálható egy megadott index egy adott tábláról.