MySQL Indeksvejledning – Opret, Tilføj og slip

Af: Marcus Allen Marcus Allen
Hours Opdateret

Hvad er et indeks?

Indekser i MySQL sortere data på en organiseret sekventiel måde. De oprettes på den eller de kolonner, der vil blive brugt til at filtrere dataene. Tænk på et indeks som en alfabetisk sorteret liste. Det er lettere at slå navne op, der er sorteret i alfabetisk rækkefølge, end navne, der ikke er sorteret.

Brug af et indeks på tabeller, der ofte opdateres, kan resultere i dårlig ydeevne. Dette er fordi MySQL opretter en ny indeksblok, hver gang data tilføjes eller opdateres i tabellen. Generelt bør indekser bruges på tabeller, hvis data ikke ændres ofte, men bruges meget i udvalgte søgeforespørgsler.

Hvad bruger et indeks?

Ingen kan lide langsomme systemer. Høj systemydelse er af største betydning i næsten alle databasesystemer. De fleste virksomheder investerer kraftigt i hardware, så datahentninger og manipulationer kan være hurtigere. Men der er grænser for hardwareinvesteringer, som en virksomhed kan foretage. At optimere din database er en billigere og bedre løsning.

MySQL Indeks

Langsomheden i responstiden skyldes normalt, at posterne er gemt tilfældigt i databasetabeller. Søgeforespørgsler skal gå gennem hele de tilfældigt lagrede poster efter hinanden for at finde de ønskede data. Dette resulterer i databaser med dårlig ydeevne, når det kommer til at hente data fra store tabeller. Derfor bruges indekser, der sorterer data for at gøre det nemmere at søge.

Syntaks: Opret indeks

Indekser kan defineres på 2 måder

  1. På tidspunktet for oprettelse af bordet
  2. Efter at tabellen er oprettet

Eksempel:

For vores myflixdb forventer vi masser af søgninger til databasen på fulde navn.

Vi tilføjer kolonnen "full_names" til Index i en ny tabel "members_indexed".

Scriptet vist nedenfor hjælper os med at opnå det.

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;

Udfør ovenstående SQL-script i MySQL workbench mod "myflixdb".

MySQL Opret indeks

Opdatering af myflixdb viser den nyoprettede tabel med navnet members_indexed.

"Bemærk" medlemmer_indekseret tabel har "fulde_navne" i indeksknuden.

Efterhånden som medlemsbasen udvides, og antallet af poster stiger, vil søgeforespørgsler på tabellen members_indexed, der bruger WHERE- og ORDER BY-sætningerne, være meget hurtigere sammenlignet med dem, der udføres i medlemstabellen uden det definerede indeks.

Tilføj indeks grundlæggende syntaks

Ovenstående eksempel oprettede indekset, da databasetabellen blev defineret. Antag, at vi allerede har en tabel defineret, og søgeforespørgsler på den er meget langsomme. De tager for lang tid at returnere resultaterne. Efter at have undersøgt problemet opdager vi, at vi i høj grad kan forbedre systemets ydeevne ved at oprette INDEX på den mest brugte kolonne i WHERE-sætningen.

Vi kan bruge følgende forespørgsel til at tilføje indeks

CREATE INDEX id_index ON table_name(column_name);

Lad os antage, at søgeforespørgsler på filmtabellen er meget langsomme, og vi ønsker at bruge et indeks på "filmtitlen" for at fremskynde forespørgslerne, vi kan bruge følgende script til at opnå det.

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

Udførelse af ovenstående forespørgsel opretter et indeks på titelfeltet i filmtabellen.

Dette betyder, at alle søgeforespørgsler på filmtabellen ved hjælp af "titlen" vil være hurtigere.

Søgeforespørgsler på andre felter i filmtabellen vil dog stadig være langsommere sammenlignet med dem, der er baseret på det indekserede felt.

Bemærk: Du kan oprette indekser på flere kolonner, hvis det er nødvendigt, afhængigt af de felter, du har til hensigt at bruge til din databasesøgemaskine.

Hvis du vil se de indekser, der er defineret på en bestemt tabel, kan du bruge følgende script til at gøre det.

SHOW INDEXES FROM table_name;

Lad os nu tage et kig på alle de indekser, der er defineret på filmtabellen i myflixdb.

SHOW INDEXES FROM `movies`;

Udførelse af ovenstående script i MySQL arbejdsbord mod myflixdb giver os resultater på oprettet indeks.

Bemærk: De primære og fremmede nøgler på bordet er allerede blevet indekseret af MySQL. Hvert indeks har sit eget unikke navn, og kolonnen, hvorpå det er defineret, vises også.

Syntaks: Drop indeks

Drop-kommandoen bruges til at fjerne allerede definerede indekser på en tabel.

Der kan være tidspunkter, hvor du allerede har defineret et indeks på en tabel, der ofte opdateres. Du ønsker måske at fjerne indekserne på sådan en tabel for at forbedre UPDATE- og INSERT-forespørgslernes ydeevne. Den grundlæggende syntaks, der bruges til at slippe et indeks på en tabel, er som følger.

DROP INDEX `index_id` ON `table_name`;

Lad os nu se på et praktisk eksempel.

DROP INDEX ` full_names` ON `members_indexed`;

Udførelse af ovenstående kommando taber indekset med id ` fuld_navne ` fra den medlemmer_indekserede tabel.

Resumé

  • Indekser er meget kraftfulde, når det kommer til at forbedre ydeevnen i høj grad MySQL søgeforespørgsler.
  • Indekser kan defineres ved oprettelse af en tabel eller tilføjes senere, efter at tabellen allerede er oprettet.
  • Du kan definere indekser på mere end én kolonne i en tabel.
  • SHOW INDEX FROM table_name bruges til at vise de definerede indekser på en tabel.
  • DROP-kommandoen bruges til at fjerne et defineret indeks på en given tabel.

Du kan lide: