MySQL: IP-Adressen optimal speichern

Wer sich noch nie Gedanken über die Speicherung von IP-Adressen gemacht hat, wird kaum auf die Problematik bei der Speicherung von IP-Adressen stoßen. Und so ist es an der Tagesordnung, dass eine IP-Adresse in aller Regel in einem Feld mit der Definition varchar(15) gespeichert wird. Wenn die IP-Adresse dort nur abgelegt wird, mag diese Definition genügen. Soll die IP-Adresse aber in einem SQL-SELECT abgefragt werden, soll sie eventuell eindeutig in der Tabelle vorhanden sein und/oder wird die Tabelle sehr groß, ist diese Feld-Tabelle alles andere als optimal.

Eine kurze IP-Adressen-Definition (IP4)
Die IP-Adresse besteht aus 4 Byte (32 Bit). Jedes Byte kann einen Wert zwischen 0 und 255 annehmen. Dargestellt wird  die IP-Adresse in vier Oketten, also beispielsweise 192.168.0.1. Somit ist die kleinste IP-Adresse die 0.0.0.0, die größte IP-Adresse 255.255.255.255.
Gleiches gilt für die eventuelle Speicherung einer Subnetzmaske. Diese Adresse enthält einen Netzanteil und einen Hostanteil und dient zur Wegfindung eines Computers zu einem bestimmten Zielnetz. Die Trennung von Netz- und Hostanteil erfolgt mit Hilfe der Subnetzmaske. Auch hier ist die kleinste Adresse die 0.0.0.0, die größte 255.255.255.255

Sind vier TinyInt-Blöcke eine Lösung?
Da wir nun wissen, dass es vier Blöcke gibt, deren Werte von 0 bis 255 annehmen können, wäre ein TinyInt ohne Vorzeichen ein optimaler Datentyp, da dieser Datentyp Werte von 0 bis 255 aufnehmen kann. Aus Speicherplatz-Sicht auf jeden Fall eine deutliche Verbesserung, denn der Datentyp TinyInt verbraucht nur ein Byte. Vier Bytes im Vergleich zu einem 16 Bytes bei einem varchar(15) eine deutliche Verbesserung. Doch eine Abfrage oder der Index über vier Spalten ist natürlich alles andere als optimal.

Die Lösung liegt im Integer
Vier TinyInt-Blöcke können auch in einem Integer abgebildet werden. Aus Speicherplatz-Sicht nimmt ein Integer genauso viel Platz ein, wie vier Tiny-Int, nämlich vier Byte. Doch aus Abfrage/Index-Sicht ist es natürlich wesentlich einfacher, die IP-Adresse in einem einfachen Integer-Feld abzulegen. Glücklicherweise bietet MySQL die Funktionen „INET_ATON()“ und „INET_NTOA()“ (Funktionen in PHP sind sprechender: ip2long() und long2ip()).

Diese Funktionen liefern den entsprechenden Integer-Wert einer IP-Adresse oder die IP-Adresse anhand des Integer-Wertes. In der Praxis bietet dies viele Vorteile und einen erheblichen Performance-Vorteil bei größeren Tabellen.

Beispiele:
PHP mit MySQL-Funktion:
$result = „INSERT user SET ip_int = INET_ATON(‚{$_SERVER[‚REMOTE_ADDR‘]}‘) WHERE id = “ . $user_id;

Oder PHP mit PHP-Funktion ip2long:
$result = „INSERT user SET ip_int = “.ip2long($_SERVER[‚REMOTE_ADDR‘]) .” WHERE id = “ . $user_id;

UPDATE: Fehlerhafte IP-Adressen bei der Rückrechnung

Wenn bei der Rückrechnung der IP-Adressen die Adresse „127.255.255.255“ gehäuft auftritt, sollte man sich schleunigst die MySQL-Konfiguration des Integer-Datentyps anschauen. Schauen wir uns doch mal das Ergebnis der Umrechnung an:

<?php
echo ip2long("127.255.255.255");
?>

Das Ergebnis dieser Ausgabe ist die Zahl „2147483647“. Wie wir im Artikel „MySQL Tabellen-Optimierung: Der optimale Datentyp“ nachlesen können, bietet der Integer in MySQL Platz bis zur 4.294.967.295; allerdings nur im positiven Bereich. Dieser Bereich halbiert sich, wenn auch negative Zahlen im Integer-Bereich platzfinden sollen. Dann ist eben bei der „2.147.483.647“ Schluss.

CREATE TABLE `integer_ohne_vorzeichen` (
`ip` int(11) unsigned default NULL
);
CREATE TABLE `integer_mit_vorzeichen` (
`ip` int(11) default NULL
);

Dieses Update schreibe ich (leider) aus gutem Grund, denn bei einem aktuellen Projekt habe ich dies nicht beachtet und mich auf die „default“-Einstellung des MySQL Integers verlassen. MySQL hat die IP-Adressen > 127 klaglos angenommen, den Integer jedoch beim Speichern auf den höchsten zulässigen Wert gekürzt.

Schreibe einen Kommentar