Vad är ett index?
Index i MySQL sorterar data på ett organiserat sekventiellt sätt. De skapas i kolumnerna som ska användas för att filtrera data. Tänk på ett index som en alfabetiskt sorterad lista. Det är lättare att slå upp namn som har sorterats i alfabetisk ordning än de som inte är sorterade.
Att använda ett index på tabeller som ofta uppdateras kan resultera i dålig prestanda. Detta beror på att MySQL skapar ett nytt indexblock varje gång data läggs till eller uppdateras i tabellen. Generellt bör index användas på tabeller vars data inte ändras ofta men används mycket i utvalda sökfrågor.
Vad använder ett index?
Ingen gillar långsamma system. Hög systemprestanda är av yttersta vikt i nästan alla databassystem. De flesta företag investerar mycket i hårdvara så att datainhämtningar och manipulationer kan bli snabbare. Men det finns gränser för hårdvaruinvesteringar som ett företag kan göra. Optimering av din databas är en billigare och bättre lösning.
Långsamheten i svarstiden beror vanligtvis på att posterna lagras slumpmässigt i databastabeller. Sökfrågor måste gå igenom alla slumpmässigt lagrade poster efter varandra för att hitta önskad data. Detta resulterar i databaser med dålig prestanda när det gäller att hämta data från stora tabeller. Därför används index som sorterar data för att göra det lättare att söka.
Syntax: Skapa index
Index kan definieras på två sätt
- Vid tidpunkten för bordsskapandet
- Efter att tabellen har skapats
För vår myflixdb förväntar vi oss massor av sökningar till databasen med fullständigt namn.
Vi lägger till kolumnen "fullständiga namn" i Index i en ny tabell "members_indexed".
Skriptet som visas nedan hjälper oss att uppnå 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;
Kör ovanstående SQL-skript i MySQL-arbetsbänken mot "myflixdb".
Uppdatering av myflixdb visar den nyskapade tabellen med namnet members_indexed.
"Anmärkning" members_indexed tabell har "full_names" i indexnoden.
När medlemsbasen expanderar och antalet poster ökar kommer sökfrågorna i tabellen members_indexed som använder WHERE- och ORDER BY-klausulerna att bli mycket snabbare jämfört med de som utfördes i tabellen members utan att indexet definierats.
Lägg till index grundläggande syntax
Ovanstående exempel skapade indexet när databastabellen definierades. Anta att vi redan har en tabell definierad och sökfrågorna om den är mycket långsamma. De tar för lång tid att returnera resultaten. Efter att ha undersökt problemet upptäcker vi att vi kan förbättra systemets prestanda avsevärt genom att skapa INDEX i den mest använda kolumnen i WHERE-klausulen.
Vi kan använda följande fråga för att lägga till index
CREATE INDEX id_index ON table_name(column_name);
Låt oss anta att sökfrågor på filmtabellen är mycket långsamma och vi vill använda ett index på "filmtiteln" för att påskynda frågorna, vi kan använda följande skript för att uppnå det.
CREATE INDEX `title_index` ON `movies`(`title`);
Genom att utföra ovanstående fråga skapas ett index i titelfältet i filmtabellen.
Detta innebär att alla sökfrågor på filmtabellen med "titeln" kommer att bli snabbare.
Sökfrågor på andra fält i filmtabellen är dock fortfarande långsammare jämfört med de som baseras på det indexerade fältet.
Obs! Du kan skapa index på flera kolumner om det behövs beroende på de fält du tänker använda för din databassökmotor.
Om du vill visa index definierade i en viss tabell kan du använda följande skript för att göra det.
SHOW INDEXES FROM table_name;
Låt oss nu titta på alla index som definierats på filmbordet i myflixdb.
SHOW INDEXES FROM `movies`;
Att utföra ovanstående skript i MySQL-arbetsbänken mot myflixdb ger oss resultat på skapat index.
Obs! De primära och främmande nycklarna på bordet har redan indexerats av MySQL. Varje index har sitt eget unika namn och den kolumn som det definieras på visas också.
Syntax: Släpp index
Drop-kommandot används för att ta bort redan definierade index på en tabell.
Det kan finnas tillfällen när du redan har definierat ett index i en tabell som ofta uppdateras. Du kanske vill ta bort indexen i en sådan tabell för att förbättra UPDATE- och INSERT-frågan. Den grundläggande syntaxen som används för att släppa ett index på en tabell är som följer.
DROP INDEX `index_id` ON `table_name`;
Låt oss nu titta på ett praktiskt exempel.
DROP INDEX ` full_names` ON `members_indexed`;
Genom att utföra ovanstående kommando tappas indexet med id `fullnamn` från tabellen members_indexed.
Sammanfattning
- Index är mycket kraftfulla när det gäller att förbättra prestanda för MySQL-sökfrågor.
- Index kan definieras när du skapar en tabell eller läggs till senare efter att tabellen redan har skapats.
- Du kan definiera index i mer än en kolumn i en tabell.
- VISA INDEX FRÅN tabellnamn används för att visa de definierade indexen på en tabell.
- DROP-kommandot används för att ta bort ett definierat index i en given tabell.