MySQL Indice: Tutorial su come creare, aggiungere e rilasciare

Che cos'è un MySQL Indice?

An Index in MySQL Un indice è una struttura dati che memorizza i valori delle colonne in modo ordinato, consentendo al motore di ricerca di risalire rapidamente nelle righe. Gli indici vengono creati sulla colonna o sulle colonne utilizzate più frequentemente per filtrare i dati. Si può pensare a un indice come a un elenco ordinato alfabeticamente: trovare un nome in un elenco ordinato è molto più veloce che trovarlo in un elenco non ordinato.

Gli indici comportano un compromesso: ogni operazione di INSERT o UPDATE deve mantenere l'indice, quindi aggiungere troppi indici su una tabella con molte operazioni di scrittura può compromettere le prestazioni complessive. Come regola generale, è preferibile indicizzare le colonne che compaiono nelle clausole WHERE, JOIN e ORDER BY su tabelle che vengono lette più spesso di quanto vengano scritte.

Perché utilizzare un indice?

A nessuno piacciono i sistemi lenti. Le prestazioni elevate sono una priorità assoluta per quasi tutte le applicazioni basate su database. Le aziende investono ingenti somme in hardware per garantire la velocità delle query, ma esiste un limite a ciò che l'hardware da solo può offrire. L'ottimizzazione degli indici rappresenta una soluzione più economica ed efficace.

I tempi di risposta lenti derivano solitamente dal fatto che le righe vengono memorizzate in ordine fisico sul disco. Senza un indice, MySQL deve scansionare ogni riga per trovare quelle che corrispondono a un predicato — una “scansione completa della tabella”. Gli indici consentono MySQL salta direttamente alle righe corrispondenti, il che trasforma il piano di query da O(n) a circa O(log n) per le ricerche in alberi B.

Sintassi: Crea indice

Un indice può essere definito in due punti:

1. Al momento della creazione della tabella.
2. Dopo che la tabella esiste già.

Esempio: creare un indice in linea con CREATE TABLE

Per la myflixdb database, ci aspettiamo molte ricerche sulla colonna fullname. Lo script seguente crea un nuovo members_indexed tabella con indice su full_names colonna.

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;

Eseguire lo script in MySQL Banco da lavoro contro il myflixdb Banca dati.

ricaricare myflixdb per vedere il nuovo members_indexed tavolo. Il full_names la colonna ora appare sotto la Indici nodo.

Man mano che il numero di membri aumenta, le query di ricerca su members_indexed che utilizzano WHERE e ORDER BY contro full_names sono molto più veloci delle stesse query sull'originale members tabella senza indice.

Aggiungi un indice dopo che la tabella esiste già

Spesso scoprirai che una tabella esistente necessita di un indice: le query di ricerca sono lente e un piano EXPLAIN mostra una scansione completa della tabella su una colonna che appare in WHERE. CREATE INDEX Questa istruzione aggiunge un indice senza ricreare la tabella.

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

Esempio concreto: velocizzare le ricerche su title colonna del movies tabella:

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

Ogni query che filtra su movies.title Ora è supportato dal nuovo indice. Le query che filtrano in base ad altre colonne continuano a scansionare la tabella a meno che non dispongano di un proprio indice.

Nota: È possibile creare un indice composito su più colonne quando le query filtrano o ordinano sempre in base alla stessa combinazione. L'ordine è importante: la colonna principale determina se l'indice può essere utilizzato.

Elenca gli indici di una tabella

Usa il SHOW INDEXES per visualizzare tutti gli indici definiti su una tabella.

SHOW INDEXES FROM `table_name`;

Esempio: elenca gli indici su movies tabella:

SHOW INDEXES FROM `movies`;

Eseguire l'istruzione in MySQL banco di lavoro contro di myflixdb per visualizzare gli indici esistenti e le colonne che coprono.

Nota: Le chiavi primarie e le chiavi esterne vengono indicizzate automaticamente da MySQLOgni indice ha un nome univoco e elenca le colonne che comprende.

Sintassi: Drop Index

Usa il DROP INDEX Rimuovere un indice esistente da una tabella. Questa operazione è utile quando una tabella con un elevato volume di scritture viene rallentata da un indice che non apporta più alcun vantaggio in termini di operazioni di lettura.

DROP INDEX `index_id` ON `table_name`;

Esempio concreto: lascia cadere il full_names indice da members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Tipi di MySQL Indici

MySQL supporta diversi tipi di indice, ognuno adatto a un carico di lavoro differente.

Tipo	Missione
CHIAVE PRIMARIA	Identificatore univoco di riga; raggruppato con i dati della tabella in InnoDB.
UNICO	Garantisce l'unicità e al contempo funge da indice.
INDICE (struttura ad albero B)	Indice secondario predefinito utilizzato per le query di intervallo e le ricerche di uguaglianza.
TESTO INTERO	Ottimizzato per la ricerca di testo in linguaggio naturale con CONFRONTA … CONTRO.
SPAZIALE	Indice R-tree per tipi di dati GIS come PUNTO e POLIGONO.
HASH	Ricerche di uguaglianza a tempo costante; utilizzate dal motore di archiviazione MEMORY.
Composito (a più colonne)	Unisce diverse colonne in un unico indice; rispetta la regola del prefisso più a sinistra.

migliori pratiche per MySQL Indici

Le abitudini descritte di seguito mantengono gli indici utili ed evitano che diventino un peso morto.

• Indice per il modello di query, non per il nome della colonna: Aggiungi indici che corrispondano a clausole WHERE, JOIN e ORDER BY reali, non a "ogni colonna che sembra importante".
• Guarda l'ordine degli indici compositi: La colonna principale deve comparire nella query affinché l'indice venga utilizzato.
• Evitare indici duplicati: Un prefisso iniziale di un indice composito copre già le ricerche a colonna singola su tale prefisso.
• Ispeziona con SPIEGAZIONE: Verificare che il pianificatore stia effettivamente selezionando il nuovo indice.
• Elimina gli indici non utilizzati: uso sys.schema_unused_indexes in MySQL 5.7+ per trovare indici che non vengono letti da nessuno.
• Corrispondenza tra i tipi di dati: Se una clausola WHERE confronta una colonna VARCHAR con un numero, l'indice non può essere utilizzato a causa di un cast implicito.

DOMANDE FREQUENTI