MySQL Index-tutorial – Maken, toevoegen en verwijderen

Door: Marcus Allen Marcus Allen
Hours Bijgewerkt

Wat is een index?

Indexen binnen MySQL gegevens op een georganiseerde, opeenvolgende manier sorteren. Ze worden gemaakt in de kolom(men) die 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 is zo omdat MySQL creëert een nieuw indexblok telkens wanneer gegevens worden toegevoegd of bijgewerkt in de tabel. Over het algemeen moeten indexen worden gebruikt voor tabellen waarvan de gegevens niet vaak veranderen, maar die veel worden gebruikt in geselecteerde zoekopdrachten.

Wat gebruikt een index?

Niemand houdt van trage systemen. Hoge systeemprestaties zijn van het grootste belang in bijna alle databasesystemen. De meeste bedrijven investeren zwaar in hardware, zodat het ophalen en manipuleren van gegevens sneller kan gebeuren. Maar er zijn grenzen aan de hardware-investeringen die een bedrijf kan doen. Het optimaliseren van uw database is een goedkopere en betere oplossing.

MySQL Index

De traagheid van de responstijd is meestal te wijten aan het feit dat de records willekeurig in databasetabellen worden opgeslagen. Zoekopdrachten moeten de hele willekeurig opgeslagen records één voor één doorlopen om de gewenste gegevens te vinden. Dit resulteert in slecht presterende databases 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

  1. Op het moment dat de tabel wordt gemaakt
  2. Nadat de tabel is gemaakt

Voorbeeld:

Voor onze myflixdb verwachten we veel zoekopdrachten naar de database op volledige naam.

We zullen de kolom “full_names” 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 MySQL werkbank tegen de “myflixdb”.

MySQL Index maken

Als u myflixdb vernieuwt, wordt de nieuw gemaakte tabel met de naam Members_indexed weergegeven.

"Opmerking" De tabel Members_indexed heeft “full_names” in het indexknooppunt.

Naarmate de ledenbasis groter wordt en het aantal records toeneemt, zullen zoekopdrachten in de tabel member_indexed die de clausules WHERE en ORDER BY gebruiken, veel sneller zijn vergeleken met de zoekopdrachten die in de ledentabel worden uitgevoerd zonder dat de index is gedefinieerd.

Voeg de basissyntaxis van de index toe

In het bovenstaande voorbeeld werd de index gemaakt bij het definiëren van de databasetabel. Stel dat we al een tabel hebben gedefinieerd en dat zoekopdrachten daarin erg traag zijn. Het duurt te lang voordat de resultaten worden geretourneerd. Nadat we het probleem hebben onderzocht, ontdekken we dat we de systeemprestaties aanzienlijk kunnen verbeteren door INDEX te maken in de meest gebruikte kolom in de WHERE-clausule.

We kunnen de volgende query gebruiken om een ​​index toe te voegen

CREATE INDEX id_index ON table_name(column_name);

Stel dat zoekopdrachten op de tabel 'films' erg traag zijn en we willen een index op de 'filmtitel' gebruiken om de zoekopdrachten te versnellen. We kunnen het volgende script gebruiken om dat te bereiken.

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

Als u de bovenstaande query uitvoert, wordt er een index gemaakt in het titelveld in de filmtabel.

Dit betekent dat alle zoekopdrachten in de filmtabel met behulp van de “titel” sneller zullen zijn.

Zoekopdrachten op andere velden in de filmtabel zullen echter nog steeds langzamer zijn vergeleken met zoekopdrachten op basis van het geïndexeerde veld.

Opmerking: U kunt indien nodig indexen op meerdere kolommen maken, afhankelijk van de velden die u voor uw databasezoekmachine wilt gebruiken.

Als u de indexen wilt bekijken die voor een bepaalde tabel zijn gedefinieerd, kunt u het volgende script gebruiken.

SHOW INDEXES FROM table_name;

Laten we nu eens kijken naar alle indexen die zijn gedefinieerd in de filmtabel in myflixdb.

SHOW INDEXES FROM `movies`;

Voer het bovenstaande script uit in MySQL werkbank tegen de myflixdb geeft ons resultaten op 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: Index verwijderen

De opdracht drop wordt gebruikt om reeds gedefinieerde indexen in een tabel te verwijderen.

Het kan voorkomen dat u al een index hebt gedefinieerd voor een tabel die regelmatig wordt bijgewerkt. Mogelijk wilt u de indexen uit een dergelijke tabel verwijderen om de prestaties van UPDATE- en INSERT-query's te verbeteren. De basissyntaxis die wordt gebruikt om een ​​index in 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 tabel Members_indexed verwijderd.

Samenvatting

  • Indexen zijn zeer krachtig als het gaat om het aanzienlijk verbeteren van de prestaties van MySQL zoekopdrachten.
  • Indexen kunnen worden gedefinieerd bij het aanmaken van een tabel, maar kunnen ook later worden toegevoegd nadat de tabel al is aangemaakt.
  • U kunt indexen definiëren voor meer dan één kolom in een tabel.
  • De SHOW INDEX FROM tabelnaam wordt gebruikt om de gedefinieerde indexen in een tabel weer te geven.
  • Het DROP-commando wordt gebruikt om een ​​gedefinieerde index uit een bepaalde tabel te verwijderen.