MySQL Indexhandledning – Skapa, lägg till och släpp

By: Marcus Allen Marcus Allen
Hours Uppdaterad

Vad är ett index?

Indexerar i MySQL sortera data på ett organiserat sekventiellt sätt. De skapas i kolumnen/kolumnerna som kommer att 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 uppdateras ofta kan resultera i dålig prestanda. Det här är för att MySQL skapar ett nytt indexblock varje gång data läggs till eller uppdateras i tabellen. I allmänhet bör index användas på tabeller vars data inte ändras ofta men som används mycket i utvalda sökfrågor.

Vad använder ett index?

Ingen gillar långsamma system. Hög systemprestanda är av största vikt i nästan alla databassystem. De flesta företag investerar mycket i hårdvara så att datahämtning och manipulationer kan gå snabbare. Men det finns gränser för hårdvaruinvesteringar som ett företag kan göra. Att optimera din databas är en billigare och bättre lösning.

MySQL index

Långsamheten i svarstiden beror vanligtvis på att posterna lagras slumpmässigt i databastabeller. Sökfrågor måste gå igenom hela de slumpmässigt lagrade posterna efter varandra för att hitta önskad data. Detta resulterar i dåliga prestandadatabaser 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å 2 sätt

  1. Vid tidpunkten för tabellskapandet
  2. Efter att tabellen har skapats

Exempelvis:

För vår myflixdb förväntar vi oss massor av sökningar till databasen på fullständigt namn.

Vi kommer att lägga till kolumnen "full_names" till 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änk mot "myflixdb".

MySQL Skapa index

Uppdatering av myflixdb visar den nyskapade tabellen med namnet members_indexed.

"Notera" members_indexed-tabellen har "full_names" i indexnoden.

När medlemsbasen expanderar och antalet poster ökar, kommer sökfrågor i tabellen members_indexed som använder satserna WHERE och ORDER BY att vara mycket snabbare jämfört med de som utförs i tabellen med medlemmar utan indexet definierat.

Lägg till grundläggande syntax för index

Ovanstående exempel skapade indexet när databastabellen definierades. Anta att vi redan har en tabell definierad och sökfrågor på 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 på den mest använda kolumnen i WHERE-satsen.

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 snabba upp frågorna, vi kan använda följande skript för att uppnå det.

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

Genom att köra ovanstående fråga skapas ett index på titelfältet i filmtabellen.

Detta innebär att alla sökfrågor i filmtabellen med "titeln" kommer att gå snabbare.

Sökfrågor på andra fält i filmtabellen kommer dock fortfarande att vara långsammare jämfört med de som baseras på det indexerade fältet.

Notera: Du kan skapa index på flera kolumner om det behövs beroende på de fält som du tänker använda för din databassökmotor.

Om du vill se indexen som definierats för 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 ta en titt på alla index som definieras i filmtabellen i myflixdb.

SHOW INDEXES FROM `movies`;

Exekvera skriptet ovan i MySQL arbetsbänk mot myflixdb ger oss resultat på skapat index.

Notera: De primära och främmande nycklarna på bordet har redan indexerats av MySQL. Varje index har sitt eget unika namn och kolumnen där det är definierat 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 på en tabell som uppdateras ofta. Du kanske vill ta bort indexen på en sådan tabell för att förbättra UPDATE- och INSERT-frågornas prestanda. Den grundläggande syntaxen som används för att släppa ett index på en tabell är följande.

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 köra kommandot ovan släpps indexet med id ` full_names ` från tabellen members_indexed.

Sammanfattning

  • Index är mycket kraftfulla när det gäller att avsevärt förbättra prestandan för MySQL sökfrågor.
  • Index kan definieras när du skapar en tabell eller läggas till senare efter att tabellen redan har skapats.
  • Du kan definiera index för mer än en kolumn i en tabell.
  • SHOW INDEX FROM table_name används för att visa de definierade indexen på en tabell.
  • Kommandot DROP används för att ta bort ett definierat index på en given tabell.

Du kanske gillar: