MySQL Indeks: loomise, lisamise ja eemaldamise õpetus

Mis on a MySQL Indeks?

An indeks in MySQL on andmestruktuur, mis salvestab veergude väärtusi järjestatud viisil, et mootor saaks ridu kiiresti otsida. Indeksid luuakse veeru või veergude põhjal, mida kõige sagedamini andmete filtreerimiseks kasutatakse. Mõelge indeksist nagu tähestikulises järjekorras sorteeritud loendist: sorteeritud loendist on nime leidmine palju kiirem kui sorteerimata hunnikust.

Indeksitega kaasneb kompromiss – iga INSERT või UPDATE käsk peab indeksit haldama, seega liiga paljude indeksite lisamine kirjutamismahukale tabelile võib üldist jõudlust kahjustada. Rusikareeglina tuleks indeksveerud, mis ilmuvad WHERE-, JOIN- ja ORDER BY-klauslites tabelites, mida loetakse sagedamini kui kirjutatakse, kustutada.

Miks kasutada indeksit?

Kellelegi ei meeldi aeglased süsteemid. Suur jõudlus on peaaegu iga andmebaasipõhise rakenduse peamine mure. Ettevõtted kulutavad päringute kiiruse tagamiseks palju riistvarale, kuid riistvara üksi pakutavatel võimalustel on piir. Indeksite optimeerimine on odavam ja tõhusam vahend.

Aeglased reageerimisajad tulenevad tavaliselt sellest, et read on kettale füüsilises järjekorras salvestatud. Ilma indeksita MySQL peab iga rea ​​skannima, et leida need, mis vastavad predikaadile – „täieliku tabeli skannimine“. Indeksid lasevad MySQL hüppa otse vastavate ridade juurde, mis teisendab päringuplaani B-puu otsingute puhul väärtuselt O(n) ligikaudu väärtuseks O(log n).

Süntaks: Loo indeks

Indeksit saab defineerida kahes kohas:

1. Tabeli loomise ajal.
2. Pärast seda, kui tabel on juba olemas.

Näide: Loo indeks rea sees käsuga CREATE TABLE

Jaoks myflixdb andmebaasis eeldame täisnime veerus palju otsinguid. Allolev skript loob uue members_indexed tabel, mille indeks on full_names kolonni.

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äivita skript MySQL Töölaud vastu myflixdb andmebaas.

värskendama myflixdb et näha uut members_indexed laud. The full_names veerg kuvatakse nüüd all Indexes sõlm.

Liikmelisuse kasvades otsingupäringud saidil members_indexed mis kasutavad WHERE ja ORDER BY funktsioone full_names on palju kiiremad kui samad päringud originaalis members tabel ilma indeksita.

Lisa indeks pärast seda, kui tabel on juba olemas

Tihti avastate, et olemasolev tabel vajab indeksit – otsingupäringud on aeglased ja EXPLAIN-plaan näitab WHERE-s kuvatava veeru täielikku tabeli skannimist. CREATE INDEX lause lisab indeksi ilma tabelit uuesti loomata.

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

Konkreetne näide – otsingute kiirendamine title veerg movies tabelis:

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

Iga päring, mis filtreerib movies.title on nüüd uue indeksi toel. Päringud, mis filtreerivad teiste veergude järgi, skannivad tabelit endiselt, kui neil pole oma indeksit.

Märge: Saate luua liitindeksi mitme veeru ulatuses, kui teie päringud filtreerivad või sorteerivad alati sama kombinatsiooni alusel. Järjekord on oluline – esimene veerg määrab, kas indeksit saab kasutada.

Loetlege tabeli indeksid

Kasutama SHOW INDEXES et näha kõiki tabelis määratletud indekseid.

SHOW INDEXES FROM `table_name`;

Näide – indeksite loend movies tabelis:

SHOW INDEXES FROM `movies`;

Käivita avaldus MySQL Workbench vastu myflixdb et näha olemasolevaid indekseid ja veerge, mida need hõlmavad.

Märge: Primaar- ja võõrvõtmed indekseeritakse automaatselt MySQLIgal indeksil on unikaalne nimi ja see loetleb veeru(d), mida see hõlmab.

Süntaks: Indeksi langetamine

Kasutama DROP INDEX olemasoleva indeksi eemaldamiseks tabelist. See on kasulik siis, kui kirjutamismahukat tabelit aeglustab indeks, mis enam lugemispoolel oma töömahtu ei teeni.

DROP INDEX `index_id` ON `table_name`;

Konkreetne näide — visake ära full_names indeks alates members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Tüübid MySQL Indexes

MySQL toetab mitut indeksitüüpi, millest igaüks sobib erineva töökoormuse jaoks.

KASUTUSALA	Eesmärk
PÕHIVÕTI	Unikaalne rea identifikaator; rühmitatud InnoDB tabeli andmetega.
UNIQUE	Jõustab unikaalsuse, toimides samal ajal indeksina.
INDEKS (B-puu)	Vaikimisi teisejärguline indeks, mida kasutatakse vahemikupäringute ja võrdusotsingute jaoks.
TÄISTEKST	Optimeeritud loomuliku keele tekstiotsingu jaoks funktsiooniga MATCH … AGAINST.
RUUMILINE	R-puu indeks GIS-andmetüüpidele, näiteks POINT ja POLYGON.
HASH	Konstantse aja võrdusotsingud; kasutab MEMORY salvestusmootor.
Liit (mitme veeruga)	Ühendab mitu veergu üheks indeksiks; austab vasakpoolseima eesliite reeglit.

Parimad tavad MySQL Indexes

Allpool toodud harjumused hoiavad indeksid kasulikuna ja takistavad neil raskuseks muutumast.

• Päringumustri indeks, mitte veeru nimi: lisa indeksid, mis vastavad tegelikele WHERE-, JOIN- ja ORDER BY-klauslitele, mitte "igale olulisele veerule".
• Jälgige liitindeksi järjekorda: Indeksi kasutamiseks peab päringus esinema esimene veerg.
• Vältige topeltindekseid: Liitindeksi juhtiv eesliide katab juba selle eesliite üheveerulised otsingud.
• Kontrolli koos SELGITAMISEGA: kinnitage, et planeerija valib tegelikult uue indeksi.
• Eemalda kasutamata indeksid: kasutama sys.schema_unused_indexes in MySQL 5.7+, et leida indekseid, mida miski ei loe.
• Andmetüüpide vastendamine: Kui WHERE-klausel võrdleb VARCHAR-veergu numbriga, ei saa indeksit kasutada kaudse teisenduse tõttu.

KKK