Wat is een index?
Indexen in MySQL sorteren gegevens op een georganiseerde sequentiële manier. Ze worden gemaakt op de kolom (men) die zullen worden gebruikt om de gegevens te filteren. Beschouw een index als een alfabetisch gesorteerde lijst. Het is gemakkelijker om namen op te zoeken die in alfabetische volgorde zijn gesorteerd dan namen die niet zijn gesorteerd.
Het gebruik van een index voor tabellen die regelmatig worden bijgewerkt, kan tot slechte prestaties leiden. Dit komt doordat MySQL een nieuw indexblok aanmaakt telkens wanneer er gegevens in de tabel worden toegevoegd of bijgewerkt. Over het algemeen moeten indexen worden gebruikt voor tabellen waarvan de gegevens niet vaak veranderen, maar veel worden gebruikt in geselecteerde zoekopdrachten.
Wat gebruikt u een index?
Niemand houdt van trage systemen. Hoge systeemprestaties zijn van het grootste belang in bijna alle databasesystemen. De meeste bedrijven investeren fors in hardware, zodat het opvragen en manipuleren van gegevens sneller kan gaan. Maar er is een limiet aan hardware-investeringen die een bedrijf kan doen. Het optimaliseren van uw database is een goedkopere en betere oplossing.
De traagheid van de responstijd wordt meestal veroorzaakt doordat de records willekeurig in databasetabellen worden opgeslagen. Zoekopdrachten moeten de volledige willekeurig opgeslagen records een voor een doorlopen om de gewenste gegevens te vinden. Dit resulteert in databases met slechte prestaties als het gaat om het ophalen van gegevens uit grote tabellen. Daarom worden indexen gebruikt die gegevens sorteren om het zoeken gemakkelijker te maken.
Syntaxis: Index maken
Indexen kunnen op 2 manieren worden gedefinieerd
- Op het moment dat de tafel werd gemaakt
- Nadat de tafel is gemaakt
Voor onze myflixdb verwachten we veel zoekopdrachten in de database op volledige naam.
We zullen de kolom "volledige namen" toevoegen aan Index in een nieuwe tabel "members_indexed".
Het onderstaande script helpt ons daarbij.
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;
Voer het bovenstaande SQL-script uit in de MySQL-werkbank tegen de "myflixdb".
Het vernieuwen van de myflixdb toont de nieuw gemaakte tabel met de naam members_indexed.
"Opmerking" members_indexed table heeft "full_names" in het indexen knooppunt.
Naarmate het ledenbestand groter wordt en het aantal records toeneemt, zullen zoekopdrachten in de tabel met leden_index die de WHERE- en ORDER BY-clausules gebruiken, veel sneller zijn in vergelijking met de zoekopdrachten die in de ledentabel worden uitgevoerd zonder dat de index is gedefinieerd.
Voeg de basissyntaxis van de index toe
Het bovenstaande voorbeeld heeft de index gemaakt bij het definiëren van de databasetabel. Stel dat we al een tabel hebben gedefinieerd en de zoekopdrachten daarop erg traag zijn. Het duurt te lang voordat de resultaten worden geretourneerd. Na het probleem te hebben onderzocht, ontdekken we dat we de systeemprestaties aanzienlijk kunnen verbeteren door INDEX te maken op de meest gebruikte kolom in de WHERE-clausule.
We kunnen de volgende zoekopdracht gebruiken om een index toe te voegen
CREATE INDEX id_index ON table_name(column_name);
Laten we aannemen dat zoekopdrachten op de filmtafel erg traag zijn en we willen een index op de "filmtitel" gebruiken om de zoekopdrachten te versnellen, dan kunnen we het volgende script gebruiken om dat te bereiken.
CREATE INDEX `title_index` ON `movies`(`title`);
Door de bovenstaande zoekopdracht uit te voeren, wordt een index gemaakt op het titelveld in de filmtabel.
Dit betekent dat alle zoekopdrachten op de filmtafel met de "titel" sneller zullen zijn.
Zoekopdrachten op andere velden in de filmtabel zullen echter nog steeds langzamer zijn in vergelijking met de zoekopdrachten op basis van het geïndexeerde veld.
Opmerking: u kunt indien nodig indexen voor meerdere kolommen maken, afhankelijk van de velden die u wilt gebruiken voor uw databasezoekmachine.
Als u de indexen wilt bekijken die voor een bepaalde tabel zijn gedefinieerd, kunt u het volgende script gebruiken om dat te doen.
SHOW INDEXES FROM table_name;
Laten we nu eens kijken naar alle indexen die zijn gedefinieerd op de filmtafel in de myflixdb.
SHOW INDEXES FROM `movies`;
Het uitvoeren van het bovenstaande script in MySQL-werkbank tegen de myflixdb geeft ons resultaten over de gemaakte index.
Opmerking: de primaire en externe sleutels op de tafel zijn al geïndexeerd door MySQL. Elke index heeft zijn eigen unieke naam en de kolom waarin deze is gedefinieerd, wordt ook weergegeven.
Syntaxis: verwijder index
Het drop-commando wordt gebruikt om reeds gedefinieerde indexen op een tabel te verwijderen.
Het kan voorkomen dat u al een index hebt gedefinieerd voor een tabel die regelmatig wordt bijgewerkt. Misschien wilt u de indexen op zo'n tabel verwijderen om de prestaties van de UPDATE- en INSERT-query's te verbeteren. De basissyntaxis die wordt gebruikt om een index op een tabel neer te zetten, is als volgt.
DROP INDEX `index_id` ON `table_name`;
Laten we nu naar een praktisch voorbeeld kijken.
DROP INDEX ` full_names` ON `members_indexed`;
Door het bovenstaande commando uit te voeren, wordt de index met id `full_names 'uit de members_indexed tabel verwijderd.
Overzicht
- Indexen zijn erg krachtig als het gaat om het aanzienlijk verbeteren van de prestaties van MySQL-zoekopdrachten.
- Indexen kunnen worden gedefinieerd bij het maken van een tabel of later worden toegevoegd nadat de tabel al is gemaakt.
- U kunt indexen definiëren voor meer dan één kolom in een tabel.
- De SHOW INDEX FROM tabelnaam wordt gebruikt om de gedefinieerde indexen op een tabel weer te geven.
- Het DROP-commando wordt gebruikt om een gedefinieerde index uit een bepaalde tabel te verwijderen.