MySQL Indeks: Vodič za stvaranje, dodavanje i ispuštanje

Što je a MySQL Indeks?

An indeks in MySQL je struktura podataka koja pohranjuje vrijednosti stupaca na uređen način kako bi tražilica mogla brzo pretraživati ​​retke. Indeksi se stvaraju na stupcu ili stupcima koji se najčešće koriste za filtriranje podataka. Zamislite indeks kao abecedno sortirani popis: puno je brže pronaći ime na sortiranom popisu nego u nesortiranoj hrpi.

Indeksi nose kompromis - svaki INSERT ili UPDATE mora održavati indeks, pa dodavanje previše indeksa na tablicu s puno zapisa može naštetiti ukupnim performansama. Kao opće pravilo, indeksirajte stupce koji se pojavljuju u klauzulama WHERE, JOIN i ORDER BY na tablicama koje se češće čitaju nego što se u njih piše.

Zašto koristiti indeks?

Nitko ne voli spore sustave. Visoke performanse su glavna briga za gotovo svaku aplikaciju koja se temelji na bazama podataka. Tvrtke troše mnogo na hardver kako bi upiti bili brzi, ali postoji granica onoga što sam hardver može pružiti. Optimizacija indeksa je jeftinija i učinkovitija poluga.

Sporo vrijeme odziva obično proizlazi iz pohranjivanja redaka na disku u fizičkom redoslijedu. Bez indeksa, MySQL mora skenirati svaki redak kako bi pronašao one koji odgovaraju predikatu - "pregled cijele tablice". Indeksi dopuštaju MySQL skoči izravno na odgovarajuće retke, što transformira plan upita iz O(n) u otprilike O(log n) za pretraživanja B-stabla.

Sintaksa: Stvori indeks

Indeks se može definirati na dva mjesta:

1. U trenutku izrade tablice.
2. Nakon što tablica već postoji.

Primjer: Izrada indeksa u liniji s naredbom CREATE TABLE

Za myflixdb U bazi podataka očekujemo puno pretraživanja u stupcu punog imena. Skript u nastavku stvara novi members_indexed tablica s indeksom na full_names stupac.

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;

Izvršite skriptu u MySQL Radni stol protiv myflixdb baza podataka.

Osvježiti myflixdb vidjeti novo members_indexed stol. The full_names stupac se sada pojavljuje ispod Indeksi čvor.

Kako članstvo raste, upiti za pretraživanje na members_indexed koji koriste WHERE i ORDER BY nasuprot full_names puno su brži od istih upita na originalu members tablica bez indeksa.

Dodajte indeks nakon što tablica već postoji

Često ćete otkriti da postojeća tablica treba indeks - upiti za pretraživanje su spori, a EXPLAIN plan prikazuje potpuno skeniranje tablice u stupcu koji se pojavljuje u WHERE. CREATE INDEX Naredba dodaje indeks bez ponovnog stvaranja tablice.

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

Konkretan primjer - ubrzajte pretrage na title stupac movies stol:

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

Svaki upit koji se filtrira na movies.title sada je podržan novim indeksom. Upiti koji filtriraju prema drugim stupcima i dalje skeniraju tablicu osim ako nemaju vlastiti indeks.

Bilješka: Možete stvoriti složeni indeks na više stupaca kada vaši upiti uvijek filtriraju ili sortiraju prema istoj kombinaciji. Redoslijed je važan - vodeći stupac određuje može li se indeks koristiti.

Popis indeksa na tablici

Koristiti SHOW INDEXES vidjeti svaki indeks definiran na tablici.

SHOW INDEXES FROM `table_name`;

Primjer - popis indeksa na movies stol:

SHOW INDEXES FROM `movies`;

Izvršite izjavu u MySQL Radna tezga protiv myflixdb kako biste vidjeli postojeće indekse i stupce koje pokrivaju.

Bilješka: Primarni i strani ključevi se automatski indeksiraju pomoću MySQLSvaki indeks ima jedinstveno ime i navodi stupac(e) koje pokriva.

Sintaksa: Ispuštanje indeksa

Koristiti DROP INDEX za uklanjanje postojećeg indeksa iz tablice. Ovo je korisno kada tablicu s puno zapisa usporava indeks koji više ne zarađuje svoje mjesto na strani čitanja.

DROP INDEX `index_id` ON `table_name`;

Konkretan primjer - ispustite full_names indeks iz members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Vrste MySQL Indeksi

MySQL podržava nekoliko vrsta indeksa, svaki prilagođen različitom radnom opterećenju.

Najbolji primjeri iz prakse za MySQL Indeksi

Sljedeće navike održavaju indekse korisnima i sprječavaju da postanu nepotrebni.

• Indeks za uzorak upita, a ne naziv stupca: dodajte indekse koji odgovaraju stvarnim klauzulama WHERE, JOIN i ORDER BY, a ne „svakom stupcu koji zvuči važno“.
• Pratite redoslijed kompozitnog indeksa: Vodeći stupac mora se pojaviti u upitu da bi se indeks koristio.
• Izbjegavajte duplicirane indekse: Vodeći prefiks složenog indeksa već pokriva pretraživanja u jednom stupcu na tom prefiksu.
• Pregledajte s OBJAŠNJENJEM: potvrditi da planer zapravo bira novi indeks.
• Izbriši nekorištene indekse: koristiti sys.schema_unused_indexes in MySQL 5.7+ za pronalaženje indeksa koje ništa ne čita.
• Tipovi podataka podudaranja: Ako WHERE klauzula uspoređuje VARCHAR stupac s brojem, indeks se ne može koristiti zbog implicitnog pretvaranja.

Pitanja i odgovori