MySQL Tutorial sull'indice: crea, aggiungi e rilascia

Cos'è un indice?

Indici dentro MySQL ordinare i dati in modo sequenziale organizzato. Vengono creati nelle colonne che verranno utilizzate per filtrare i dati. Pensa a un indice come a un elenco ordinato alfabeticamente. È più semplice cercare i nomi ordinati in ordine alfabetico rispetto a quelli che non lo sono.

L'utilizzo di un indice su tabelle aggiornate frequentemente può comportare prestazioni scadenti. Questo è perché MySQL crea un nuovo blocco indice ogni volta che i dati vengono aggiunti o aggiornati nella tabella. In genere, gli indici dovrebbero essere utilizzati su tabelle i cui dati non cambiano frequentemente ma vengono utilizzati molto in query di ricerca selezionate.

A cosa serve un indice?

A nessuno piacciono i sistemi lenti. Le prestazioni elevate 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 esiste un limite agli investimenti hardware che un’azienda può effettuare. Ottimizzare il database è una soluzione migliore e più economica.

La lentezza nel tempo di risposta è solitamente dovuta al fatto che i record vengono archiviati 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 indici che ordinano i dati per facilitare la ricerca.

Sintassi: Crea indice

Gli indici possono essere definiti in 2 modi

1. Al momento della creazione della tabella
2. Dopo che la tabella è stata creata

Esempio:

Per il nostro myflixdb ci aspettiamo molte ricerche nel database sul 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 in MySQL banco di lavoro contro "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 sulla 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 che le query di ricerca su di essa siano molto lente. Richiedono troppo tempo per restituire i risultati. Dopo aver analizzato il problema, scopriamo che possiamo migliorare notevolmente le prestazioni del sistema creando INDEX sulla colonna più comunemente utilizzata nella clausola WHERE.

Possiamo usare la seguente query per aggiungere l'indice

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. Per raggiungere questo obiettivo, possiamo utilizzare lo script seguente.

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 utilizzando il "titolo" saranno più veloci.

Le query di ricerca su altri campi della tabella film saranno comunque più lente rispetto a quelle basate sul campo indicizzato.

Nota: Puoi creare indici su più colonne se necessario a seconda dei campi che intendi utilizzare per il tuo motore di ricerca del database.

Se si desidera visualizzare gli indici definiti su una tabella specifica, è possibile utilizzare lo script seguente.

SHOW INDEXES FROM table_name;

Diamo ora un'occhiata a tutti gli indici definiti nella tabella dei film in myflixdb.

SHOW INDEXES FROM `movies`;

Eseguendo lo script precedente in MySQL banco di lavoro contro myflixdb ci dà 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: rilascia indice

Il comando drop viene utilizzato per rimuovere gli indici già definiti su una tabella.

Potrebbero verificarsi casi in cui è già stato definito un indice su una tabella che viene aggiornata frequentemente. Potresti voler rimuovere gli indici su tale tabella 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 di MySQL query di ricerca.
• Gli indici possono essere definiti durante la creazione di una tabella oppure aggiunti in un secondo momento, 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.