MySQL Index: Handleiding voor aanmaken, toevoegen en verwijderen

Wat is een MySQL Inhoudsopgave?

An index in MySQL Een index is een datastructuur die kolomwaarden op een geordende manier opslaat, zodat de zoekmachine snel rijen kan opzoeken. Indexen worden aangemaakt op de kolom(men) die het meest worden gebruikt om gegevens te filteren. Zie een index als een alfabetisch gesorteerde lijst: het is veel sneller om een ​​naam te vinden in een gesorteerde lijst dan in een ongesorteerde lijst.

Indexen brengen een afweging met zich mee: elke INSERT- of UPDATE-bewerking moet de index bijwerken, dus het toevoegen van te veel indexen aan een tabel die veel wordt beschreven, kan de algehele prestaties negatief beïnvloeden. Als vuistregel geldt: indexeer kolommen die voorkomen in WHERE-, JOIN- en ORDER BY-clausules op tabellen die vaker worden gelezen dan beschreven.

Waarom een ​​index gebruiken?

Niemand houdt van trage systemen. Hoge prestaties zijn een topprioriteit voor vrijwel elke applicatie die gebruikmaakt van een database. Bedrijven investeren fors in hardware om query's snel te houden, maar hardware alleen heeft een limiet. Het optimaliseren van indexen is een goedkopere en effectievere oplossing.

Trage reactietijden worden meestal veroorzaakt doordat rijen in fysieke volgorde op de schijf zijn opgeslagen. Zonder index, MySQL Elke rij moet worden gescand om de rijen te vinden die aan een predicaat voldoen — een "volledige tabelscan". Indexen maken het mogelijk om elke rij te scannen. MySQL Spring direct naar de overeenkomende rijen, waardoor het queryplan verandert van O(n) naar ongeveer O(log n) voor B-tree-zoekopdrachten.

Syntaxis: Index maken

Een index kan op twee plaatsen worden gedefinieerd:

1. Op het moment van het aanmaken van de tabel.
2. Nadat de tabel al bestaat.

Voorbeeld: Een index aanmaken direct met CREATE TABLE

Voor de myflixdb In deze database verwachten we veel zoekopdrachten op de kolom met volledige namen. Het onderstaande script maakt een nieuwe database aan. members_indexed tabel met een index op de full_names kolom.

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 script uit in MySQL Werkbank tegen de myflixdb database.

verversen myflixdb om de nieuwe te zien members_indexed tafel. De full_names kolom verschijnt nu onder de Indexen knooppunt.

Naarmate het ledenaantal groeit, nemen zoekopdrachten op members_indexed die WHERE en ORDER BY gebruiken tegen full_names zijn veel sneller dan dezelfde zoekopdrachten op het origineel. members tabel zonder index.

Voeg een index toe nadat de tabel al bestaat.

Je zult vaak merken dat een bestaande tabel een index nodig heeft — zoekopdrachten zijn traag en een EXPLAIN-plan laat een volledige tabelscan zien op een kolom die in de WHERE-clausule voorkomt. CREATE INDEX Deze instructie voegt een index toe zonder de tabel opnieuw aan te maken.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Concreet voorbeeld: het versnellen van zoekopdrachten op de title kolom van de movies tafel:

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

Elke query die filtert op movies.title wordt nu ondersteund door de nieuwe index. Query's die filteren op andere kolommen scannen nog steeds de tabel, tenzij ze hun eigen index hebben.

Let op: Je kunt een samengestelde index maken over meerdere kolommen als je query's altijd filteren of sorteren op dezelfde combinatie. De volgorde is belangrijk: de eerste kolom bepaalt of de index kan worden gebruikt.

Lijst de indexen van een tabel

Gebruik SHOW INDEXES Om alle gedefinieerde indexen van een tabel te bekijken.

SHOW INDEXES FROM `table_name`;

Voorbeeld — lijstindexen op de movies tafel:

SHOW INDEXES FROM `movies`;

Voer de instructie uit in MySQL Werkbank tegen myflixdb om de bestaande indexen en de kolommen die ze omvatten te bekijken.

Let op: Primaire en externe sleutels worden automatisch geïndexeerd door MySQLElke index heeft een unieke naam en geeft aan welke kolom(men) deze omvat.

Syntaxis: Drop Index

Gebruik DROP INDEX Het verwijderen van een bestaande index uit een tabel. Dit is handig wanneer een tabel met veel schrijfbewerkingen wordt vertraagd door een index die niet langer rendabel is bij het lezen.

DROP INDEX `index_id` ON `table_name`;

Concreet voorbeeld — laat de full_names index van members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Types van MySQL Indexen

MySQL Ondersteunt verschillende indexsoorten, elk geschikt voor een andere werklast.

Type	Doel
HOOFDSLEUTEL	Unieke rij-identificatie; gegroepeerd met de tabelgegevens in InnoDB.
.	Garandeert uniciteit en fungeert tegelijkertijd als index.
INDEX (B-boom)	Standaard secundaire index die wordt gebruikt voor bereikquery's en gelijkheidszoekopdrachten.
HELE TEKST	Geoptimaliseerd voor zoekopdrachten in natuurlijke taal met MATCH … AGAINST.
RUIMTELIJK	R-tree-index voor GIS-datatypen zoals POINT en POLYGON.
HASH	Gelijkheidszoekacties met constante tijdsduur; gebruikt door de MEMORY-opslagengine.
Samengesteld (meerdere kolommen)	Combineert meerdere kolommen tot één index; houdt rekening met de regel van het meest linkse voorvoegsel.

Beste praktijken voor MySQL Indexen

De onderstaande gewoontes zorgen ervoor dat indexen nuttig blijven en voorkomen dat ze een ballast worden.

• Index voor het zoekpatroon, niet voor de kolomnaam: Voeg indexen toe die overeenkomen met echte WHERE-, JOIN- en ORDER BY-clausules, en niet met "elke kolom die belangrijk lijkt".
• Volgorde van de samengestelde index: De eerste kolom moet in de query voorkomen om de index te kunnen gebruiken.
• Vermijd dubbele indexen: Een voorvoegsel van een samengestelde index dekt al zoekopdrachten in één kolom op dat voorvoegsel.
• Inspecteer met EXPLAIN: Controleer of de planner daadwerkelijk de nieuwe index selecteert.
• Verwijder ongebruikte indexen: . sys.schema_unused_indexes in MySQL 5.7+ om indexen te vinden die door niets worden gelezen.
• Gegevenstypen matchen: Als een WHERE-clausule een VARCHAR-kolom vergelijkt met een getal, kan de index niet worden gebruikt vanwege een impliciete typeconversie.

Veelgestelde vragen