Ottimizzazione del database Esplorazione degli indici in SQL

Ottimizzazione database Esplorazione indici SQL

 

Mentre si cerca un determinato argomento in un libro, si visita prima la pagina dell’indice (che si trova all’inizio del libro) e si trova il numero di pagina che contiene il nostro argomento di interesse. Ora, immaginate quanto sia scomodo trovare un determinato argomento in un libro senza la pagina dell’indice. Per questo, dobbiamo cercare ogni pagina nel libro, il che richiede molto tempo e frustra.

Un problema simile si verifica anche in SQL Server quando recupera i dati dal database. Per superare questo problema, SQL Server utilizza anche l’indicizzazione che velocizza il processo di recupero dei dati, e in questo articolo ne parleremo. Affronteremo il motivo per cui è necessaria l’indicizzazione e come creare ed eliminare gli indici in modo efficace. Il prerequisito di questo tutorial è la conoscenza di base dei comandi SQL.

 

Cos’è l’indicizzazione?

 

L’indicizzazione è un oggetto di schema che utilizza un puntatore per recuperare i dati dalle righe, riducendo il tempo di I/O (input/output) per individuare i dati. L’indicizzazione può essere applicata a una o più colonne che vogliamo cercare. Memorizzano la colonna in una struttura dati separata chiamata B-Tree. Uno dei principali vantaggi di B-Tree è che memorizza i dati in ordine ordinato.

Se vi state chiedendo perché i dati possono essere recuperati più velocemente se sono ordinati, allora dovete leggere su Ricerca lineare vs Ricerca binaria.

L’indicizzazione è uno dei metodi più famosi per migliorare le prestazioni delle query SQL. Sono piccoli, veloci e notevolmente ottimizzati per le tabelle relazionali. Quando vogliamo cercare una riga senza indicizzazione, SQL esegue una scansione completa della tabella in modo lineare. In altre parole, SQL deve scansionare ogni riga per trovare le condizioni corrispondenti, il che richiede molto tempo. D’altra parte, l’indicizzazione mantiene i dati ordinati, come discusso in precedenza.

Tuttavia, dovremmo anche fare attenzione, l’indicizzazione crea una struttura dati separata che richiede spazio extra, e ciò può diventare problematico quando il database è grande. Per una buona pratica, l’indicizzazione è efficace solo sulle colonne utilizzate frequentemente e può essere evitata sulle colonne utilizzate raramente. Di seguito sono riportati alcuni scenari in cui l’indicizzazione potrebbe essere utile:

  1. Il numero di righe deve essere (>10000).
  2. La colonna richiesta contiene un gran numero di valori.
  3. La colonna richiesta non deve contenere un gran numero di valori NULL.
  4. È utile se ordiniamo o raggruppiamo frequentemente i dati in base a determinate colonne. L’indicizzazione recupera rapidamente i dati ordinati anziché eseguire una scansione completa.

E l’indicizzazione può essere evitata quando:

  1. La tabella è piccola.
  2. O quando i valori della colonna sono utilizzati raramente.
  3. O quando i valori delle colonne cambiano frequentemente.

Può anche capitare che l’ottimizzatore rilevi che una scansione completa della tabella richiede meno tempo rispetto alla tabella indicizzata, quindi l’indicizzazione potrebbe non essere utilizzata, anche se esiste. Questo può accadere quando la tabella è piccola, o la colonna viene aggiornata frequentemente.

 

Creazione di un Database di Esempio

 

Prima di iniziare, è necessario configurare MySQL Workbench sul proprio PC per seguire facilmente il tutorial. Si può fare riferimento a questo video di YouTube per configurare il proprio workbench.

Dopo aver configurato il proprio workbench, creeremo alcuni dati casuali da cui potremo eseguire le nostre query.

Creazione della Tabella:

-- Creare una tabella per contenere i dati casuali

CREATE TABLE employee_info (id INT PRIMARY KEY AUTO_INCREMENT,
                                               name VARCHAR(100),
                                                    age INT, email VARCHAR(100));

 

Inserimento dei Dati:

-- Inserire dati casuali nella tabella

INSERT INTO employee_info (name, age, email)
SELECT CONCAT('Utente', LPAD(ROW_NUMBER() OVER (), 5, '0')),
       FLOOR(RAND() * 50) + 20,
       CONCAT('utente', LPAD(ROW_NUMBER() OVER (), 5, '0'), '@xyz.com')
FROM information_schema.tables
LIMIT 100;

 

Verrà creata una tabella chiamata employee_info con attributi come nome, età ed email. 

Mostrare i Dati:

SELECT *
FROM employee_info;

 

Output:

 

 

Creazione e eliminazione di un indice

 

Per creare un indice, possiamo utilizzare il comando CREATE come segue:

Sintassi:

CREATE INDEX nome_indice SU NOME_TABELLA (NOME_COLONNA);

 

Nella query sopra, nome_indice è il nome dell’indice, nome_tabella è il nome della tabella e nome_colonna è il nome della colonna su cui vogliamo applicare l’indicizzazione.

Esempio:

CREATE INDEX age_index SU employee_info (age);

 

Possiamo anche creare indici per più colonne nella stessa tabella:

CREATE INDEX nome_indice SU NOME_TABELLA (col1,
                                       col2,
                                       col3, ....);

 

Indice unico: Possiamo anche creare un indice unico per una particolare colonna che non consente di memorizzare valori duplicati in quella colonna. Questo mantiene l’integrità dei dati e migliora ulteriormente le prestazioni.

CREATE UNIQUE INDEX nome_indice SU NOME_TABELLA (NOME_COLONNA);

 

Nota: Gli indici possono essere creati automaticamente per le colonne PRIMARY_KEY e UNIQUE. Non è necessario crearli manualmente.

Eliminazione di un indice:

Possiamo utilizzare il comando DROP per eliminare un particolare indice dalla tabella.

DROP INDEX nome_indice SU NOME_TABELLA;

 

Dobbiamo specificare i nomi dell’indice e della tabella per eliminare l’indice.

Mostra gli indici:

È anche possibile visualizzare tutti gli indici presenti nella tabella.

Sintassi:

SHOW INDEX
FROM NOME_TABELLA;

 

Esempio:

SHOW INDEX
FROM employee_info;

 

Output:

 

 

Aggiornamento di un indice

 

Il comando seguente crea un nuovo indice nella tabella esistente.

Sintassi:

ALTER TABLE NOME_TABELLA AGGIUNGI INDICE nome_indice (col1, col2, col3, ...);

 

Nota: L’ALTER non è un comando standard di ANSI SQL. Quindi può variare tra altri database.

Ad esempio:

ALTER TABLE employee_info AGGIUNGI INDICE name_index (name);

SHOW INDEX
FROM employee_info;

 

Output:

   

Nell’esempio precedente, abbiamo creato un nuovo indice nella tabella esistente. Ma non possiamo modificare un indice esistente. Per fare ciò, dobbiamo prima eliminare il vecchio indice e quindi crearne uno nuovo modificato.

Ad esempio:

DROP INDEX name_index SU employee_info;


CREATE INDEX name_index SU employee_info (name, email);

SHOW INDEX
FROM employee_info ;

 

Output:

 

 

Conclusione

 

In questo articolo, abbiamo affrontato una comprensione di base dell’indicizzazione SQL. È anche consigliabile mantenere l’indicizzazione limitata a poche colonne, in quanto un maggior numero di indici può influire negativamente sulle prestazioni. L’indicizzazione velocizza le query SELECT e la clausola WHERE, ma rallenta le dichiarazioni di inserimento e aggiornamento. Pertanto, applicare l’indicizzazione solo alle colonne utilizzate frequentemente è una buona pratica.

Fino ad allora, continua a leggere e a imparare. Aryan Garg è uno studente di Ingegneria Elettrica, attualmente all’ultimo anno del suo corso di laurea. Il suo interesse si concentra nel campo dello sviluppo web e dell’apprendimento automatico. Ha coltivato questo interesse ed è desideroso di lavorare ancora in queste direzioni.