MySQL Indeks: Opret, tilføj og slip vejledning

Hvad er en MySQL Indeks?

An indeks in MySQL er en datastruktur, der lagrer kolonneværdier på en ordnet måde, så motoren hurtigt kan slå rækker op. Indekser oprettes på den eller de kolonner, der oftest bruges til at filtrere data. Tænk på et indeks som en alfabetisk sorteret liste: det er langt hurtigere at finde et navn i en sorteret liste end i en usorteret bunke.

Indekser indebærer et kompromis — hver INSERT eller UPDATE skal vedligeholde indekset, så det kan skade den samlede ydeevne at tilføje for mange indekser i en skrivetung tabel. Som en tommelfingerregel indekserer man kolonner, der vises i WHERE-, JOIN- og ORDER BY-klausuler, i tabeller, der læses oftere, end de skrives til.

Hvorfor bruge et indeks?

Ingen kan lide langsomme systemer. Høj ydeevne er en topprioritet for næsten alle databasebaserede applikationer. Virksomheder bruger mange penge på hardware for at holde forespørgsler hurtige, men der er et loft over, hvad hardware alene kan levere. Optimering af indekser er en billigere og mere effektiv løftestang.

Langsomme svartider skyldes normalt, at rækker gemmes i fysisk rækkefølge på disken. Uden et indeks, MySQL skal scanne hver række for at finde dem, der matcher et prædikat — en "fuld tabelscanning". Indekser lader MySQL hopper direkte til de matchende rækker, hvilket transformerer forespørgselsplanen fra O(n) til omtrent O(log n) for B-træ-opslag.

Syntaks: Opret indeks

Et indeks kan defineres to steder:

1. Ved oprettelsen af ​​tabellen.
2. Efter at tabellen allerede findes.

Eksempel: Opret et indeks integreret med CREATE TABLE

For myflixdb databasen, forventer vi mange søgninger i kolonnen med fuldt navn. Scriptet nedenfor opretter en ny members_indexed tabel med et indeks på full_names kolonne.

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 scriptet i MySQL Arbejdsbænk mod myflixdb databasen.

Opfrisk myflixdb at se det nye members_indexed bord. Det full_names kolonnen vises nu under Indexes node.

Efterhånden som medlemskabet vokser, søgeforespørgsler på members_indexed der bruger WHERE og ORDER BY imod full_names er meget hurtigere end de samme forespørgsler på originalen members tabellen uden indekset.

Tilføj et indeks efter at tabellen allerede findes

Du vil ofte opdage, at en eksisterende tabel har brug for et indeks – søgeforespørgsler er langsomme, og en EXPLAIN-plan viser en fuld tabelscanning på en kolonne, der vises i WHERE. CREATE INDEX Sætningen tilføjer et indeks uden at genskabe tabellen.

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

Konkret eksempel — fremskynd søgninger på title kolonne i movies bord:

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

Enhver forespørgsel, der filtrerer på movies.title understøttes nu af det nye indeks. Forespørgsler, der filtrerer på andre kolonner, scanner stadig tabellen, medmindre de har deres eget indeks.

Bemærk: Du kan oprette et sammensat indeks på tværs af flere kolonner, når dine forespørgsler altid filtrerer eller sorterer på den samme kombination. Rækkefølgen er vigtig – den første kolonne bestemmer, om indekset kan bruges.

Liste indekser i en tabel

Brug SHOW INDEXES for at se hvert indeks, der er defineret i en tabel.

SHOW INDEXES FROM `table_name`;

Eksempel — liste indekser på movies bord:

SHOW INDEXES FROM `movies`;

Kør sætningen i MySQL Workbench mod myflixdb for at se de eksisterende indekser og de kolonner, de dækker.

Bemærk: Primære og fremmednøgler indekseres automatisk af MySQLHvert indeks har et unikt navn og viser den/de kolonne(r), det dækker.

Syntaks: Drop indeks

Brug DROP INDEX at fjerne et eksisterende indeks fra en tabel. Dette er nyttigt, når en skrivetung tabel bliver bremset af et indeks, der ikke længere tjener sin plads på læsesiden.

DROP INDEX `index_id` ON `table_name`;

Konkret eksempel — drop full_names indeks fra members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Typer af MySQL Indexes

MySQL understøtter flere indekstyper, der hver især er egnet til en forskellig arbejdsbyrde.

Type	Formål
PRIMÆRNØGLE	Unik rækkeidentifikator; grupperet med tabeldataene i InnoDB.
ENESTÅENDE	Håndhæver unikhed, mens den fungerer som et indeks.
INDEKS (B-træ)	Standard sekundært indeks, der bruges til områdeforespørgsler og lighedsopslag.
FULDTEKSTEN	Optimeret til tekstsøgning i naturligt sprog med MATCH … AGAINST.
RUMLIG	R-træindeks for GIS-datatyper som POINT og POLYGON.
HASH	Opslag af lighed i konstant tid; brugt af MEMORY-lagringsmotoren.
Sammensat (flerkolonner)	Kombinerer flere kolonner i ét indeks; respekterer reglen for præfikset yderst til venstre.

Bedste praksis for MySQL Indexes

Vanerne nedenfor holder indeksene nyttige og forhindrer dem i at blive dødvægt.

• Indeks for forespørgselsmønsteret, ikke kolonnenavnet: Tilføj indekser, der matcher rigtige WHERE-, JOIN- og ORDER BY-klausuler, ikke "alle kolonner, der lyder vigtige".
• Se rækkefølgen af ​​sammensat indeks: Den indledende kolonne skal vises i forespørgslen for at indekset kan bruges.
• Undgå duplikerede indekser: Et indledende præfiks i et sammensat indeks dækker allerede opslag i én kolonne på det pågældende præfiks.
• Inspicer med FORKLAR: Bekræft, at planlæggeren rent faktisk vælger det nye indeks.
• Fjern ubrugte indekser: brug sys.schema_unused_indexes in MySQL 5.7+ for at finde indekser, som intet læser.
• Matchdatatyper: Hvis en WHERE-klausul sammenligner en VARCHAR-kolonne med et tal, kan indekset ikke bruges på grund af en implicit konvertering.

Ofte Stillede Spørgsmål