MySQL Index oktatóanyag – Létrehozás, hozzáadása és eldobás

Mi az index?

Indexek be MySQL rendezett sorrendben rendezi az adatokat. Az adatok szűrésére használt oszlop(ok)ban jönnek létre. Tekintse meg az indexet ábécé sorrendben rendezett listának. Könnyebb megkeresni az ábécé sorrendbe rendezett neveket, mint a nem rendezetteket.

Ha indexet használ a gyakran frissített táblákon, az gyenge teljesítményt eredményezhet. Ez azért van, mert MySQL minden alkalommal új indexblokkot hoz létre, amikor az adatokat hozzáadja vagy frissíti a táblázatban. Általában az indexeket olyan táblákon kell használni, amelyek adatai nem változnak gyakran, de gyakran használják a kiválasztott keresési lekérdezésekben.

Mire jó az index?

Senki sem szereti a lassú rendszereket. A magas rendszerteljesítmény szinte minden adatbázisrendszerben elsődleges fontosságú. A legtöbb vállalkozás sokat fektet a hardverbe, hogy az adatok visszakeresése és manipulálása gyorsabb legyen. De a hardverberuházásoknak van határa egy vállalkozás. Az adatbázis optimalizálása olcsóbb és jobb megoldás.

A válaszidő lassúsága általában annak tudható be, hogy a rekordokat véletlenszerűen tárolják adatbázistáblákban. A keresési lekérdezéseknek egymás után végig kell futniuk a véletlenszerűen tárolt rekordokon, hogy megtalálják a kívánt adatokat. Ez gyenge teljesítményt eredményez az adatbázisokban, amikor nagy táblákból kell lekérni az adatokat. Ezért indexeket használnak az adatok rendezésére, hogy megkönnyítsék a keresést.

Szintaxis: Index létrehozása

Az indexeket kétféleképpen lehet meghatározni

1. A táblázat elkészítésekor
2. A táblázat létrehozása után

Példa:

A myflixdb-nk esetében sok keresést várunk az adatbázisban teljes néven.

A „full_names” oszlopot hozzáadjuk az Indexhez egy új „members_indexed” táblázatban.

Az alábbi forgatókönyv segít nekünk ennek elérésében.

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;

Futtassa a fenti SQL-szkriptet MySQL munkapad a „myflixdb” ellen.

A myflixdb frissítése megjeleníti az újonnan létrehozott Members_indexed táblát.

"Jegyzet" A tagok_indexelt táblájában a „teljes_nevek” szerepel az indexek csomópontjában.

Ahogy a tagok bázisa bővül és a rekordok száma növekszik, a WHERE és ORDER BY záradékot használó Member_indexed táblában végzett keresési lekérdezések sokkal gyorsabbak lesznek, mint a tagtábla által végrehajtott lekérdezések az index meghatározása nélkül.

Index alapszintaxis hozzáadása

A fenti példa létrehozta az indexet az adatbázistábla definiálásakor. Tegyük fel, hogy már definiáltunk egy táblát, és a keresési lekérdezések rajta nagyon lassúak. Túl sokáig tart az eredmények visszaküldése. A probléma kivizsgálása után rájöttünk, hogy nagymértékben javíthatjuk a rendszer teljesítményét, ha 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 filmek táblájában a keresési lekérdezések nagyon lassúak, és a „film címén” indexet szeretnénk használni a lekérdezések felgyorsítására, ezt a következő szkript segítségével érhetjük el.

CREATE INDEX `title_index` ON `movies`(`title`);

A fenti lekérdezés végrehajtása egy indexet hoz létre a filmek tábla címmezőjében.

Ez azt jelenti, hogy a filmek táblázatában a „címet” használó összes keresési lekérdezés gyorsabb lesz.

A filmek táblázatának más mezőiben végzett keresési lekérdezések azonban továbbra is lassabbak, mint az indexelt mezőn alapulók.

Jegyzet: Ha szükséges, több oszlopon is létrehozhat indexeket, attól függően, hogy mely mezőket kívánja használni adatbázis-keresőmotorjához.

Ha meg szeretné tekinteni egy adott táblán meghatározott indexeket, használhatja a következő szkriptet.

SHOW INDEXES FROM table_name;

Vessünk most egy pillantást a myflixdb filmek táblázatában meghatározott összes indexre.

SHOW INDEXES FROM `movies`;

A fenti szkript végrehajtása MySQL munkapad a myflixdb ellenében eredményeket ad a létrehozott indexen.

Jegyzet: A táblán lévő elsődleges és idegen kulcsokat már indexelte MySQL. Minden indexnek megvan a maga egyedi neve, és megjelenik az oszlop, amelyen meghatározásra került.

Szintaxis: Drop index

A drop paranccsal eltávolíthatók a már meghatározott indexek a táblából.

Előfordulhat, hogy már definiált egy indexet egy gyakran frissített táblán. Érdemes lehet eltávolítani az indexeket egy ilyen táblából az UPDATE és INSERT lekérdezések teljesítményének javítása érdekében. Az index táblázatban való eldobásához használt alapvető szintaxis a következő.

DROP INDEX `index_id` ON `table_name`;

Nézzünk most egy gyakorlati példát.

DROP INDEX ` full_names` ON `members_indexed`;

A fenti parancs végrehajtása kidobja a ` full_names ` azonosítójú indexet a Members_indexed táblából.

Összegzésként

• Az indexek nagyon erősek, ha nagymértékben javítják a teljesítményt MySQL keresési lekérdezések.
• Az indexek a táblázat létrehozásakor definiálhatók, vagy később adhatók hozzá, miután a tábla már létrejött.
• Egy táblázat több oszlopában is megadhat indexeket.
• A SHOW INDEX FROM táblanév a meghatározott indexek táblán való megjelenítésére szolgál.
• A DROP parancs egy meghatározott index eltávolítására szolgál egy adott tábláról.