Memorizzare indirizzi IPv4 nel database

Durante la ricerca di qualche plugin decente per tenere traccia delle statistiche di questo blog ho avuto modo di riscontrare nuovamente la tendenza di moltissimi sviluppatori nel memorizzare indirizzi IP nei database sotto forma di stringa. Tali campi vengono spesso utilizzati in tabelle sfruttate alla stessa stregua di log in linea per cui sarebbe lecito aspettarsi da queste tabelle una considerevole quantità di record con una conseguente occupazione notevole di spazio. In realtà esiste una soluzione per ridurre sensibilmente questo problema anche se a quanto pare risulta sconosciuta a molti, vediamo quindi come fare utilizzando MySQL (comunque il discorso vale, seppur con qualche minima differenza, anche per molti altri database).

Per memorizzare una stringa relativamente breve nel database vengono usati due tipi di campi: CHAR e VARCHAR. Riassumendo la differenza fondamentale tra questi due tipi risiede nel modo in cui i relativi dati vengono memorizzati. Nel caso di CHAR la lunghezza del campo è fissa per cui l'inserimento di un valore prevede che esso venga memorizzato con un pad di spazi verso destra per soddisfare la lunghezza specificata nella struttura della tabella per il campo stesso, mentre in fase di lettura viene effettuato un trim prima di restituire il valore. Per un VARCHAR invece la lunghezza del campo è dinamica per cui vengono memorizzati solamente tanti caratteri quanti sono quelli necessari per rappresentare il valore, più un byte dove ne viene memorizzata la relativa lunghezza.

Per memorizzare un indirizzo IPv4 in formato stringa e quindi nella sua notazione decimale puntata occorrono al massimo 15 caratteri, come esempio estremo potremmo pensare all'indirizzo di broadcast (255.255.255.255), ma è ovvio che non tutti gli indirizzi IP hanno la stessa "lunghezza" per cui risulta già evidente come l'utilizzo di un campo CHAR(15) comporti uno spreco di spazio promuovendo automaticamente VARCHAR(15) come soluzione per ottenere un buon compromesso.

Con lo scopo di proporre qualche calcolo su dati reali per dare un'idea migliore delle differenze tra i vari approcci che studieremo ho analizzato il log generato da un server HTTP abbastanza trafficato che utilizziamo in ufficio e da cui ho potuto estrapolare, per un giorno scelto casualmente, un totale di 571.028 record. Durante l'analisi di questi record è emerso che la lunghezza media di un indirizzo IP espresso come stringa è di 12 caratteri (approssimato per difetto poichè il risultato era 12,32), per cui se volessimo memorizzare questi dati utilizzando un campo CHAR(15) otterremmo un'occupazione di spazio fissa di 15 byte per ogni record per un totale in questo caso pari a 8.565.420 byte, circa 8,5 MB. Se invece utilizzassimo un campo VARCHAR(15) avremmo in media un'occupazione di 12+1 byte per ogni record (non dimentichiamo il byte che indica la lunghezza reale del valore) per un totale di 7.423.364 byte, quasi 7,5 MB ovvero approssimativamente 1 MB in meno rispetto al primo approccio. Un buon risparmio del 13% circa, ma non è abbastanza perchè si può fare decisamente di meglio.

Un argomento spesso molto trascurato è la vera natura di un indirizzo IP e quindi come è rappresentato "internamente", perchè in realtà non è una stringa. La sua notazione decimale puntata è semplicemente un modo per renderlo più leggibile e di conseguenza mnemonico, ma un indirizzo IP in realtà è una sequenza di 32 bit (4 byte) che può essere pertanto espressa sotto forma di numero intero a 32 bit. Questo è quanto ci occorre sapere per continuare la nostra analisi ma per chiarimenti o maggiori dettagli questa pagina è un'ottimo punto di partenza nonchè una lettura consigliata. Abbiamo stabilito quindi che un indirizzo IP è rappresentabile come numero intero, a questo punto nessuno ci vieta di memorizzarlo all'interno del database come tale.

Per facilitarci in questo compito MySQL supporta due funzioni per effettuare automaticamente le conversioni da notazione decimale puntata a numero intero a 32 bit senza segno e viceversa, rispettivamente INET_ATON() e INET_NTOA(). Supponiamo di voler convertire l'indirizzo 192.168.1.1 nel corrispettivo numero intero che lo rappresenta:

mysql> SELECT INET_ATON('192.168.1.1') AS IpAsNumber;
+------------+
| IpAsNumber |
+------------+
| 3232235777 |
+------------+
1 row in set (0.00 sec)

Il risultato che otteniamo è la rappresentazione numerica sotto forma di intero a 32 bit senza segno dell'indirizzo 192.168.1.1 e per dimostrare la coerenza a chi ha ancora qualche dubbio effettuiamo l'operazione di conversione inversa:

mysql> SELECT INET_NTOA(3232235777) AS IpAsAddress;
+-------------+
| IpAsAddress |
+-------------+
| 192.168.1.1 |
+-------------+
1 row in set (0.00 sec)

Possiamo notare come la SELECT ci abbia restituito esattamente l'indirizzo inizialmente specificato.

In questo modo possiamo ridurre l'occupazione di ogni indirizzo IPv4 nel nostro database allo spazio necessario per memorizzare un intero a 32 bit ovvero 4 byte. Andando ora a ricalcolare l'occupazione totale del nostro esempio precedente sfruttando questo approccio otteniamo 2.284.112 byte di spazio utilizzato ovvero circa 2,3 MB: il 69% in meno con un guadagno di circa 5,2 MB rispetto alla memorizzazione dell'IP in formato stringa con un campo VARCHAR.

In questo modo si ottiene un notevole risparmio in termini di dimensioni dei dati memorizzati nelle tabelle che si traduce in una maggiore rapidità nella scansione delle tabelle stesse, mentre l'overhead generato dalle due funzioni di conversione è decisamente trascurabile. In più la comparazione tra due o più indirizzi IP si riduce a semplici comparazioni tra numeri, risultando molto più performante rispetto a comparazioni tra stringhe.

Occorre comunque specificare, in relazione all'esempio studiato con MySQL, che la dimensione finale su disco del file di tabella potrebbe trarre in inganno. MySQL infatti per sfruttare le tabelle dinamiche (ovvero contenenti colonne di lunghezza variabile come appunto quelle di tipo VARCHAR) utilizza una struttura più complessa che aggiunge un overhead maggiore in termini di spazio rispetto alle tabelle fisse (contenenti unicamente colonne di lunghezza fissa) quindi mentre in questo nostro caso abbiamo effettivamente utilizzato meno spazio per memorizzare la stessa informazione, la dimensione finale del file di tabella potrebbe non rispecchiare completamente questo guadagno e in questo senso molto dipende dalla struttura della tabella stessa e cioè da quali altri tipi di colonne e dati vi saranno inseriti.


Puoi scrivere un commento oppure inviare un trackback dal tuo sito.

1 commento a “Memorizzare indirizzi IPv4 nel database”

  1. Lavorare con indirizzi IP in Ruby

    Lavorare con indirizzi IP (sia v4 che v6) in Ruby è molto semplice poiché dalla versione 1.8.0 è stata inclusa nella libreria standard la classe IPAddr che mette a disposizione un set di metodi per le operazioni più comuni. Possiamo creare un&#8217...

Lascia un commento

Puoi utilizzare i seguenti tag XHTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>