MySQL tips and tricks: Velocizzare le Insert

In MySQL si possono utilizzare almeno tre modi per inserire i dati in tabella.


In questo articolo faremo un'analisi delle caratteristiche di ogni caso:

L'inserimento singolo

L'inserimento a gruppi

L'inserimento con LOAD DATA

Analizziamo i 3 modi per capire quale di questi è il più veloce per le nostre esigenze considerando che comunque ogni caso è sempre influenzato da:

• dimensione record
• quantità e dimensione degli indici
• quantità dei record da inserire insieme
• presenza di BIN, LOG-BIN

Inserimento singolo:

INSERT INTO tab (a,b,c) VALUES (1,2,3)

Caratteristiche:

• Un comando per ogni riga da inserire
• Il metodo più lento in assoluto!
• Sintassi semplice
• Si può ritardare usando la clausola delayed (insert delayed into)

Inserimento a gruppi:

INSERT INTO tab (a, b,c) VALUES (1,2,3),(4,5,6),(7,8,9)

Caratteristiche:

• Un solo comando per ogni gruppo di record
• Il metodo pià veloce (senza passare dalla riga di comando, quindi in genere dal DBA)
• Sintassi più difficile
• Dmensione massima della query limitata (in base alle impostazione di MySQL)

Inserimento con LOAD DATA:

LOAD DATA INFILE "pippo.txt" INTO TABLE tab

• Una sola istruzione per tutta la tabella
• Il metodo più veloce in assoluto!!
• Il file contenente i dati deve essere sul server
• Si può specificare il formato del record da usare (LINE TERMINATED/ENCLOSED BY)

Tutti e tre i metodi di inserimento possono essere più veloci ricorrendo all'indicizzazione ritardata, metodo con il quale, prima di iniziare a fare l'inserimento, si disabilitano gli indici e si riabilitano con l'inserimento avvenuto.

In questa maniera il DB ricrea tutte le chiavi in una sola operazione.

Es:

ALTER TABLE tab DISABLE KEYS;

Eseguo l'inserimento singolo, a gruppi o con LOAD DATA

ALTER TABLE tab ENABLE KEYS;

Concludendo ecco i risultati con l'inserimento di 100.000 record in una tabella con 3 indici:

• Singolo: 22,5 sec, con indicizzazione ritardata: 17,6 sec.
• A gruppi di 10.000: 6,9 sec, con ind. rit.: 2,7 sec
• Con Load Data: 5,8 sec, con ind. rit.: 3,1 sec

MySQL Tips and Tricks: eliminare i duplicati

Assumiano di avere una tabella con alcune righe duplicate che vogliamo eliminare:

mysql> select * from duplicati;

4 rows in set (0.00 sec)

Un modo veloce per rimuovere i duplicati (vedi riga con key 1 e 2) è aggiungere una chiave unica alle colonne a,b,c.

mysql> ALTER IGNORE TABLE duplicati ADD UNIQUE INDEX(a,b);
mysql> select * from duplicati;

3 rows in set (0.00 sec)
Il gioco è fatto.

MySQL Tips and Tricks: i processi

Trovare chi fa cosa ed “uccidere” un processo se necessario:

In questo esempio elimineremo il processo n° 321:

mysql> show processlist;

mysql>kill 321

La colonna “State” mostra che query sta facendo l’utente, ecco alcuni esempi:

Locked: la query formulata dall’utente è bloccata da un’altra query
Sending data: il thread sta processando le righe di un comando “SELECT” e sta inviando al client il risultato
Sorting for group: il thread sta facendo un raggruppamento per soddisfare la clausola “GROUP BY”
Sorting for order: il thread sta facendo un raggruppamento per soddisfare la clausola “ORDER BY”