Cos'è un indice?
Gli indici in MySQL ordinano i dati in modo sequenziale organizzato. Vengono creati sulle colonne che verranno utilizzate per filtrare i dati. Pensa a un indice come a un elenco in ordine alfabetico. È più facile cercare i nomi che sono stati ordinati in ordine alfabetico rispetto a quelli che non lo sono.
L'utilizzo di un indice su tabelle che vengono aggiornate di frequente può comportare prestazioni scadenti. Questo perché MySQL crea un nuovo blocco di indice ogni volta che i dati vengono aggiunti o aggiornati nella tabella. In generale, gli indici dovrebbero essere utilizzati su tabelle i cui dati non cambiano frequentemente ma vengono utilizzati molto nelle query di ricerca selezionate.
A cosa serve un indice?
A nessuno piacciono i sistemi lenti. Le elevate prestazioni del sistema sono di primaria importanza in quasi tutti i sistemi di database. La maggior parte delle aziende investe molto nell'hardware in modo che il recupero e la manipolazione dei dati possano essere più rapidi. Ma c'è un limite agli investimenti hardware che un'azienda può fare. L'ottimizzazione del database è una soluzione migliore e più economica.
La lentezza nel tempo di risposta è solitamente dovuta al fatto che i record vengono memorizzati in modo casuale nelle tabelle del database. Le query di ricerca devono scorrere tutti i record memorizzati in modo casuale uno dopo l'altro per individuare i dati desiderati. Ciò si traduce in database con prestazioni scadenti quando si tratta di recuperare dati da tabelle di grandi dimensioni. Pertanto, vengono utilizzati gli indici che ordinano i dati per semplificare la ricerca.
Sintassi: crea indice
Gli indici possono essere definiti in 2 modi
- Al momento della creazione del tavolo
- Dopo che la tabella è stata creata
Per il nostro myflixdb ci aspettiamo molte ricerche nel database su nome completo.
Aggiungeremo la colonna "full_names" all'indice in una nuova tabella "members_indexed".
Lo script mostrato di seguito ci aiuta a raggiungere questo obiettivo.
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;
Esegui lo script SQL precedente nel workbench MySQL su "myflixdb".
L'aggiornamento di myflixdb mostra la tabella appena creata denominata members_indexed.
"Nota" la tabella members_indexed ha "full_names" nel nodo degli indici.
Man mano che la base dei membri si espande e il numero di record aumenta, le query di ricerca sulla tabella members_indexed che utilizzano le clausole WHERE e ORDER BY saranno molto più veloci rispetto a quelle eseguite nella tabella dei membri senza l'indice definito.
Aggiungi la sintassi di base dell'indice
L'esempio precedente ha creato l'indice durante la definizione della tabella del database. Supponiamo di avere già una tabella definita e le query di ricerca su di essa sono molto lente. Impiegano troppo tempo per restituire i risultati. Dopo aver esaminato il problema, scopriamo che possiamo migliorare notevolmente le prestazioni del sistema creando INDICE sulla colonna più comunemente utilizzata nella clausola WHERE.
Possiamo usare la seguente query per aggiungere index
CREATE INDEX id_index ON table_name(column_name);
Supponiamo che le query di ricerca sulla tabella dei film siano molto lente e vogliamo utilizzare un indice sul "titolo del film" per velocizzare le query, possiamo utilizzare il seguente script per ottenere ciò.
CREATE INDEX `title_index` ON `movies`(`title`);
L'esecuzione della query precedente crea un indice nel campo del titolo nella tabella dei film.
Ciò significa che tutte le query di ricerca nella tabella dei film che utilizzano il "titolo" saranno più veloci.
Le query di ricerca su altri campi nella tabella dei film saranno tuttavia ancora più lente rispetto a quelle basate sul campo indicizzato.
Nota: è possibile creare indici su più colonne, se necessario, a seconda dei campi che si intende utilizzare per il motore di ricerca del database.
Se desideri visualizzare gli indici definiti su una particolare tabella, puoi utilizzare il seguente script per farlo.
SHOW INDEXES FROM table_name;
Diamo ora uno sguardo a tutti gli indici definiti nella tabella dei film nel myflixdb.
SHOW INDEXES FROM `movies`;
L'esecuzione dello script precedente nel workbench MySQL su myflixdb ci dà i risultati sull'indice creato.
Nota: le chiavi primarie ed esterne sulla tabella sono già state indicizzate da MySQL. Ogni indice ha il proprio nome univoco e viene mostrata anche la colonna in cui è definito.
Sintassi: indice di rilascio
Il comando drop viene utilizzato per rimuovere gli indici già definiti su una tabella.
Ci possono essere momenti in cui hai già definito un indice su una tabella che viene aggiornato frequentemente. È possibile rimuovere gli indici su una tabella di questo tipo per migliorare le prestazioni delle query UPDATE e INSERT. La sintassi di base utilizzata per rilasciare un indice su una tabella è la seguente.
DROP INDEX `index_id` ON `table_name`;
Vediamo ora un esempio pratico.
DROP INDEX ` full_names` ON `members_indexed`;
L'esecuzione del comando precedente elimina l'indice con id "full_names" dalla tabella members_indexed.
Sommario
- Gli indici sono molto potenti quando si tratta di migliorare notevolmente le prestazioni delle query di ricerca MySQL.
- Gli indici possono essere definiti durante la creazione di una tabella o aggiunti successivamente dopo che la tabella è già stata creata.
- È possibile definire indici su più di una colonna in una tabella.
- SHOW INDEX FROM table_name viene utilizzato per visualizzare gli indici definiti su una tabella.
- Il comando DROP viene utilizzato per rimuovere un indice definito su una determinata tabella.