MySQL Index: Skapa, Lägg till och Släpp Handledning

Vad är en MySQL Index?

An index in MySQL är en datastruktur som lagrar kolumnvärden på ett ordnat sätt så att motorn snabbt kan söka upp rader. Index skapas på den eller de kolumner som oftast används för att filtrera data. Tänk på ett index som en alfabetiskt sorterad lista: det är mycket snabbare att hitta ett namn i en sorterad lista än i en osorterad hög.

Index har en avvägning – varje INSERT eller UPDATE måste underhålla indexet, så att lägga till för många index i en skrivtung tabell kan skada den totala prestandan. Som en tumregel bör kolumner som visas i WHERE-, JOIN- och ORDER BY-klausuler i tabeller som läses oftare än de skrivs till indexeras.

Varför använda ett index?

Ingen gillar långsamma system. Hög prestanda är en högsta prioritet för nästan alla databasbaserade applikationer. Företag spenderar stora summor pengar på hårdvara för att hålla frågorna snabba, men det finns ett tak för vad enbart hårdvara kan leverera. Att optimera index är en billigare och effektivare åtgärd.

Långsamma svarstider beror vanligtvis på att rader lagras i fysisk ordning på disken. Utan ett index, MySQL måste skanna varje rad för att hitta de som matchar ett predikat – en "fullständig tabellskanning". Index låter MySQL hoppar direkt till matchande rader, vilket omvandlar frågeplanen från O(n) till ungefär O(log n) för B-trädsökningar.

Syntax: Skapa index

Ett index kan definieras på två ställen:

1. Vid tidpunkten för tabellskapandet.
2. Efter att tabellen redan finns.

Exempel: Skapa ett index inline med CREATE TABLE

För myflixdb databasen, förväntar vi oss många sökningar i kolumnen för fullständigt namn. Skriptet nedan skapar en ny members_indexed tabell med ett index på full_names kolonn.

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;

Kör skriptet i MySQL Arbetsbänk mot myflixdb databas.

refresh myflixdb att se det nya members_indexed tabell. De full_names kolumnen visas nu under Index nod.

Allt eftersom medlemsantalet växer, sökfrågor på members_indexed som använder WHERE och ORDER BY mot full_names är mycket snabbare än samma frågor på originalet members tabell utan index.

Lägg till ett index efter att tabellen redan finns

Du kommer ofta att upptäcka att en befintlig tabell behöver ett index – sökfrågor är långsamma och en EXPLAIN-plan visar en fullständig tabellsökning på en kolumn som visas i WHERE. CREATE INDEX kommandot lägger till ett index utan att återskapa tabellen.

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

Konkret exempel — snabba upp sökningar på title kolumnen i movies tabell:

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

Varje fråga som filtrerar på movies.title stöds nu av det nya indexet. Frågor som filtrerar på andra kolumner skannar fortfarande tabellen om de inte har ett eget index.

Obs: Du kan skapa ett sammansatt index över flera kolumner när dina frågor alltid filtrerar eller sorterar på samma kombination. Ordningen spelar roll – den inledande kolumnen avgör om indexet kan användas.

Lista index i en tabell

Använda SHOW INDEXES för att se varje index som definierats i en tabell.

SHOW INDEXES FROM `table_name`;

Exempel — lista index på movies tabell:

SHOW INDEXES FROM `movies`;

Kör satsen i MySQL Arbetsbänk mot myflixdb för att se befintliga index och de kolumner de täcker.

Obs: Primär- och främmande nycklar indexeras automatiskt av MySQLVarje index har ett unikt namn och listar den/de kolumn(er) det täcker.

Syntax: Släpp index

Använda DROP INDEX för att ta bort ett befintligt index från en tabell. Detta är användbart när en skrivtung tabell saktas ner av ett index som inte längre förtjänar sin plats på lässidan.

DROP INDEX `index_id` ON `table_name`;

Konkret exempel — släpp full_names index från members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Typer av MySQL Index

MySQL stöder flera indextyper, som var och en är lämpad för en annan arbetsbelastning.

Typ	Syfte
PRIMÄRNYCKEL	Unik radidentifierare; klustrad med tabelldata i InnoDB.
UNIK	Framtvingar unikhet samtidigt som den fungerar som ett index.
INDEX (B-träd)	Standard sekundärt index som används för intervallfrågor och likhetssökningar.
FULLTEXT	Optimerad för textsökning på naturligt språk med MATCH … AGAINST.
RUMSLIG	R-trädindex för GIS-datatyper som POINT och POLYGON.
HASCH	Uppslagningar av likhet i konstant tid; används av MEMORY-lagringsmotorn.
Sammansatt (flerkolumn)	Kombinerar flera kolumner till ett index; följer prefixregeln längst till vänster.

Bästa metoder för MySQL Index

Vanorna nedan håller index användbara och hindrar dem från att bli dödviktiga.

• Index för frågemönstret, inte kolumnnamnet: lägg till index som matchar riktiga WHERE-, JOIN- och ORDER BY-klausuler, inte "varje kolumn som låter viktig".
• Titta på kompositindexordning: Den inledande kolumnen måste finnas i frågan för att indexet ska kunna användas.
• Undvik dubbletter av index: Ett inledande prefix i ett sammansatt index täcker redan sökningar med en kolumn på det prefixet.
• Inspektera med FÖRKLARING: bekräfta att planeraren faktiskt väljer det nya indexet.
• Ta bort oanvända index: användning sys.schema_unused_indexes in MySQL 5.7+ för att hitta index som ingenting läser.
• Matchningsdatatyper: Om en WHERE-klausul jämför en VARCHAR-kolumn med ett tal kan indexet inte användas på grund av en implicit konverterad klausul.

Vanliga frågor