Tutorial su CREATE INDEX in MySQL: ottimizzare le query MySQL

Introduzione

Nell'ottimizzazione dei database, creare indici è una tecnica fondamentale per migliorare le prestazioni delle query. Questa guida ti spiega perché e come creare gli indici, così le tue query MySQL saranno il più efficienti possibile. Capendo come usare bene gli indici, puoi ridurre notevolmente il tempo di esecuzione delle query, soprattutto nei database con grandi volumi di dati.

Prima di continuare a leggere, volevo parlarti del nostro gestore di database MySQL moderno e veloce, Beekeeper Studio. Il suo editor della struttura delle tabelle ti permette di creare un indice, anche composito, scegliendo le colonne da un elenco, ed è gratuito per MySQL: scaricalo qui.

Capire gli indici in MySQL

Un indice in MySQL è una struttura dati che velocizza le operazioni di recupero dei dati da una tabella, al costo di scritture aggiuntive e di spazio di archiviazione per mantenere la struttura dell'indice. Gli indici servono a trovare rapidamente i dati senza dover cercare in ogni riga della tabella ogni volta che vi si accede.

Come creare un indice in MySQL

Creazione di un indice di base

Puoi creare un indice in MySQL con l'istruzione CREATE INDEX:

Query:

CREATE INDEX idx_lastname ON employees(lastname);

Questo comando crea un indice chiamato idx_lastname sulla colonna lastname della tabella employees, rendendo più veloci le ricerche basate sulla colonna lastname.

Output di esempio:

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Creare un indice univoco

Un indice univoco garantisce che tutti i valori della colonna indicizzata siano distinti.

Query:

CREATE UNIQUE INDEX idx_employee_id ON employees(employee_id);

Questo comando crea un indice univoco sulla colonna employee_id, garantendo che non ci siano due righe con lo stesso employee_id.

Output di esempio:

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Usare gli indici con chiavi composte

Puoi creare un indice su più colonne per ottimizzare le query che filtrano o ordinano in base a quelle colonne.

Query:

CREATE INDEX idx_name_department ON employees(lastname, department_id);

Questo comando crea un indice che include sia la colonna lastname sia department_id, utile per le query che coinvolgono entrambi i campi.

Output di esempio:

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Casi d'uso pratici

Migliorare le prestazioni delle query

Gli indici migliorano notevolmente le prestazioni delle query, soprattutto per le istruzioni SELECT su grandi quantità di dati.

Prima dell'indicizzazione:

Supponiamo una query SELECT sulla tabella employees senza un indice sulla colonna lastname.

Dopo l'indicizzazione:

La stessa query SELECT dopo la creazione di idx_lastname.

Query:

SELECT * FROM employees WHERE lastname = 'Doe';

Output di esempio senza indice:

/* Takes significantly longer, e.g., 1.5 seconds */

Output di esempio con indice:

/* Takes significantly less time, e.g., 0.02 seconds */

Garantire l'integrità dei dati

Gli indici univoci impediscono valori duplicati in una colonna, garantendo l'integrità dei dati.

Scenario:

Tentativo di inserire un employee_id duplicato dopo aver creato un indice univoco.

Output di esempio:

ERROR 1062 (23000): Duplicate entry '12345' for key 'idx_employee_id'

Conclusione

Creare indici in MySQL è una tecnica importante per ottimizzare le query sul tuo database. Scegliendo le colonne giuste da indicizzare, soprattutto quelle usate spesso nelle condizioni di ricerca o nelle clausole di join, puoi ottenere notevoli miglioramenti delle prestazioni.

Ricorda: gli indici velocizzano il recupero dei dati, ma rallentano anche le operazioni di inserimento e aggiornamento, a causa del lavoro aggiuntivo per mantenere la struttura dell'indice. Usali con giudizio per trovare il giusto equilibrio tra prestazioni in lettura e in scrittura.

Sperimenta partendo da questi esempi sui tuoi database MySQL per vedere i vantaggi dell'indicizzazione. Con un'attenta pianificazione e implementazione, gli indici diventeranno una parte preziosa dei tuoi strumenti di ottimizzazione del database.

Buona ottimizzazione!