paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL: Consigli sull'utilizzo degli indici

In questo articolo descrivero', in linee generali, come MySql gestisce gli indici, indicando come e quando crearli per sfruttarli al meglio.

Tipi di indici

Mysql prevede le seguenti tipologie di indici, di cui daro' brevemente un cenno, senza entrare nei dettagli.
Primary Key: Si applica per le colonne che prevedono tutti i valori univoci ed escludono il valore NULL. Tipico il caso dei campi ID, definiti come AUTOINCREMENT.
Unique: Si applica per le colonne che prevedono tutti i valori univoci, ma, a differenza della precedente tipologia, e' permesso il valore NULL.
No-Unique: Si applica per le colonne che prevedono valori ripetuti. La parola chiave per la creazione e' INDEX o KEY
Full Text:Si applica su colonne di tipo TEXT.
Spatial:Si applica su colonne che prevedono dati di tipo SPATIAL.

Considerazioni sull'utilizzo

(I) Generali

- Non riferirsi ad un indice, usando una condizione di where che implichi la lettura della maggior parte, o peggio, di tutti, i dati della tabella. Esempio tipico e' il caso del valore di una data. In questo caso la ricerca verra' effettuata su tutti i record

 select * from t where YEAR='1994'

mentre in quest'altro invece su un range ristretto di dati.

 select * from t where birthday='1994-01-01'

- Ricordare che gli indici vengono utilizzati in particolare nelle Join, dove la condizione principale nella where e' rappresentata dall'ID delle due tabelle in FK, in quanto il tale campo sara' gia' definito come Primary Key

- Nella condizione di where, utilizzare correttamente il tipo di dato che si va a verificare. Ad esempio, se il campo field01 e' di tipo numerico, la select scritta in questo modo

 .... where field01=18 

sara' quella corretta, mentre questa

 ... where field0='18' 
obblighera' ad eseguire una conversione inutile, da stringa a numerico.

- Usando LIKE si ottimizzera' l'uso dell'indice se la condizione non inizia per una wild card (es. %). Ovvero, questa condizione sara' ottimizzata

 .... where field01 like 'de%' 

mentre questa no

 .... where field01 like '%de%'

(II) Indexing Column Prefix

Creare un indice per una parte della colonna risulta piu' performante che crearlo per tutta la colonna. Evitare pero', come indicato nel punto (I) di avere quantita' simili di dati da estrarre.

(III) Limitare l'output dei dati

Questo si applica sia usando la clausola LIMIT, sia estraendo solo le colonne strettamente necessarie, evitando, quindi, dove possibile, istruzioni del tipo

select * from...

(IV) Summary Table

Puo' essere utile, nel caso di join particolarmente complesse, appoggiarsi a delle tabelle temporanee o a delle tabelle definite come Memory. In quest'ultimo caso, verificare pero' che la quantita' di dati non sia superiore alla quantita' di memoria disponibile.
Quindi gli indici crearli poi sulla tabella temporanea.

Strumenti utili per la gestione degli indici

Explain Plan

Questa clausola permette di verificare se un indice viene utilizzato o meno, per una tabella o per una select.
La sintassi e' la seguente:

 EXPLAIN SELECT ...

oppure

EXPLAIN tablename 

Ed il risultato saranno vari tipi di output, come indicato in questo link alla documentazione ufficiale.

Select per calcolare il numero di duplicati

Per sapere la quantita' di record duplicati che andremo ad estrarre e quindi l'utilita' o meno dell'indice applicato alla relativa colonna, possiamo utilizzare la seguente semplice istruzione sql

SELECT
   COUNT(*) AS 'Total Rows',
   COUNT(DISTINCT name) AS 'Distinct Values',
   COUNT(*) - COUNT(DISTINCT name) AS 'Duplicate Values'
FROM t;