MySQL Hakemiston opetusohjelma – Luo, lisää ja pudota

Mikä on hakemisto?

Indeksit sisään MySQL lajitella tiedot järjestetyllä peräkkäisellä tavalla. Ne luodaan sarakkeisiin, joita käytetään tietojen suodattamiseen. Ajattele hakemistoa aakkosjärjestyksessä lajiteltuna luettelona. On helpompi etsiä nimiä, jotka on lajiteltu aakkosjärjestykseen, kuin ne, joita ei ole lajiteltu.

Indeksin käyttäminen usein päivitettävissä taulukoissa voi heikentää suorituskykyä. Tämä johtuu siitä, että MySQL luo uuden indeksilohkon aina, kun tiedot lisätään tai päivitetään taulukkoon. Yleensä indeksejä tulee käyttää taulukoissa, joiden tiedot eivät muutu usein, mutta joita käytetään paljon valituissa hakukyselyissä.

Mihin indeksiä käytetään?

Kukaan ei pidä hitaista järjestelmistä. Järjestelmän korkea suorituskyky on ensiarvoisen tärkeää lähes kaikissa tietokantajärjestelmissä. Useimmat yritykset investoivat voimakkaasti laitteistoon, jotta tiedonhaku ja käsittely voi olla nopeampaa. Mutta yrityksen laitteistoinvestoinneilla on rajansa. Tietokannan optimointi on halvempi ja parempi ratkaisu.

Vastausajan hitaus johtuu yleensä siitä, että tietueet tallennetaan satunnaisesti tietokantataulukoihin. Hakukyselyiden tulee käydä läpi kaikki satunnaisesti tallennetut tietueet peräkkäin halutun tiedon löytämiseksi. Tämä johtaa huonoihin tietokantoihin, kun tietoja haetaan suurista taulukoista. Tästä syystä indeksejä käytetään tietojen lajitteluun helpottamaan hakua.

Syntaksi: Luo hakemisto

Indeksit voidaan määrittää kahdella tavalla

Taulukon luomisen yhteydessä
Taulukon luomisen jälkeen

Esimerkiksi:

Odotamme myflixdb:ssämme paljon hakuja koko nimellä.

Lisäämme sarakkeen "full_names" Indexiin uuteen taulukkoon "members_indexed".

Alla oleva käsikirjoitus auttaa meitä saavuttamaan sen.

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;

Suorita yllä oleva SQL-skripti MySQL työpenkki "myflixdb":tä vastaan.

Myflixdb:n päivittäminen näyttää juuri luodun taulukon, jonka nimi on Members_indexed.

"Merkintä" Members_indexed-taulukossa on "full_names" indeksisolmussa.

Kun jäsenkanta laajenee ja tietueiden määrä kasvaa, WHERE- ja ORDER BY -lauseita käyttävien jäsenten_indexed-taulukon hakukyselyt ovat paljon nopeampia verrattuna niihin, jotka suoritetaan jäsentaulukossa ilman indeksiä.

Lisää hakemiston perussyntaksi

Yllä oleva esimerkki loi indeksin tietokantataulukkoa määritettäessä. Oletetaan, että meillä on jo määritetty taulukko ja sen hakukyselyt ovat hyvin hitaita. Niiden palauttaminen kestää liian kauan. Ongelman tutkimisen jälkeen huomaamme, että voimme parantaa järjestelmän suorituskykyä huomattavasti luomalla INDEX:n WHERE-lauseen yleisimmin käytettyyn sarakkeeseen.

Voimme käyttää seuraavaa kyselyä indeksin lisäämiseen

CREATE INDEX id_index ON table_name(column_name);

Oletetaan, että elokuvataulukon hakukyselyt ovat erittäin hitaita ja haluamme käyttää "elokuvan nimessä" olevaa indeksiä kyselyjen nopeuttamiseksi, voimme käyttää seuraavaa komentosarjaa saavuttaaksemme sen.

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

Yllä olevan kyselyn suorittaminen luo hakemiston elokuvataulukon otsikkokenttään.

Tämä tarkoittaa, että kaikki elokuvataulukon hakukyselyt, joissa käytetään "otsikkoa", ovat nopeampia.

Elokuvataulukon muiden kenttien hakukyselyt ovat kuitenkin edelleen hitaampia kuin indeksoituun kenttään perustuvat kyselyt.

Huomautus: Voit tarvittaessa luoda indeksejä useisiin sarakkeisiin riippuen kentistä, joita aiot käyttää tietokannan hakukoneessa.

Jos haluat tarkastella tietyssä taulukossa määritettyjä indeksejä, voit käyttää seuraavaa komentosarjaa tehdäksesi sen.

SHOW INDEXES FROM table_name;

Katsotaan nyt kaikkia myflixdb:n elokuvataulukossa määritettyjä indeksejä.

SHOW INDEXES FROM `movies`;

Suoritetaan yllä oleva komentosarja MySQL työpöytä vastaan myflixdb antaa meille tuloksia luodusta indeksistä.

Huomautus: Taulukon ensisijaiset ja viiteavaimet on jo indeksoinut MySQL. Jokaisella indeksillä on oma yksilöllinen nimensä ja myös sarake, jossa se on määritetty, näytetään.

Syntaksi: Pudotusindeksi

Pudotuskomentoa käytetään poistamaan jo määritettyjä indeksejä taulukosta.

Joskus voi olla, että olet jo määrittänyt indeksin taulukkoon, jota päivitetään usein. Haluat ehkä poistaa indeksit tällaisesta taulukosta parantaaksesi UPDATE- ja INSERT-kyselyiden suorituskykyä. Perussyntaksi, jota käytetään indeksin pudottamiseksi taulukkoon, on seuraava.

DROP INDEX `index_id` ON `table_name`;

Katsotaanpa nyt käytännön esimerkkiä.

DROP INDEX ` full_names` ON `members_indexed`;

Yllä olevan komennon suorittaminen pudottaa hakemiston tunnuksella "full_names" taulukosta Members_indexed.

Yhteenveto

Indeksit ovat erittäin tehokkaita, kun se parantaa huomattavasti suorituskykyä MySQL hakukyselyt.
Indeksit voidaan määrittää taulukkoa luotaessa tai lisätä myöhemmin, kun taulukko on jo luotu.
Voit määrittää indeksit useammalle kuin yhdelle taulukon sarakkeelle.
NÄYTÄ INDEX FROM taulukon_nimi käytetään määritetyn indeksin näyttämiseen taulukossa.
DROP-komentoa käytetään määritetyn indeksin poistamiseen tietystä taulukosta.