Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-datenbank / ip-adressen-optimal-speichern.php

Benchmark

In (fast) allen Webprojekten müssen IP-Adressen gespeichert werden. Wenn nur einige wenige Einträge in der Datenbank vorhanden sind, ist das sicher kein Problem. Interessant wird es, wenn mehrere 1000 Einträge durchsucht werden müssen, um festzustellen, ob eine IP vorhanden ist, oder nicht. Wie man das ganze effektive steuert wird im folgenden erläutert.

Neuer Redakteur

Wie vielleicht bemerkt wurde, hat Jan momentan etwas weniger Zeit. Deshalb werde ich (hoffentlich) regelmäßig über Interessante Themen bloggen. Für alle, die mich nicht kennen: Mein Name ist Martin Kiesewetter (25). Ich bin seit vielen Jahren Webentwickler bei Kies-Media.de und werde bald mein Informatik-Studium abschließen.

Das Problem: Wie IP-Adressen optimal speichern?

In (fast) allen Webprojekten müssen IP-Adressen gespeichert werden. Wenn nur einige wenige Einträge in der Datenbank vorhanden sind, ist das sicher kein Problem. Interessant wird es wenn mehrere 1000 Einträge durchsucht werden müssen, um festzustellen, ob eine bestimmte IP vorhanden ist, oder nicht. Leider sehe Ich regelmäßig PHP-Scripte, die mir Tränen in die Augen treiben:

1. Möglichkeit: Datentyp: Text

Braucht in MySQL genau 17 Byte (Textlänge + 2 Byte)

2. Möglichkeit: Datentyp: VarChar(15)

Braucht in MySQL genau 16 Byte (Textlänge + 1 Byte)

3. Möglichkeit: Datentyp: Long

Nur Wenige kennen die PHP-Funktion: IP2LONG, die eine IP-Adresse in einen Longwert umwandelt. Dieser verbraucht in MySQL mit 4 Byte den wenigsten Speicher. „Kleiner“ Nebeneffekt: Die Suche über Zahlen ist in MySQL wesentlich schneller als über Texte (VarChar oder Text). Nebenbei: es gibt auch die Umkehrfunktion Long2IP.

Benchmark

Mein Benchmarkscript funktioniert so:

Bemerkung: Ich benutze für jede SQL-Anfrage eine andere IP (ip2long($ip)+$x), damit die Anfrage wirklich ausgeführt wird, und das Ergebnis nicht aus dem Query-Cache kommt!

Benchmarkergebnisse

Variante Speicherbedarf Suchzeit in s
INT (LONG) mit Index 4 Byte 0.104
VarChar(15) mit Index 16 Byte 2.794
VarChar(15) ohne Index 16 Byte 2.799
Text 17 Byte 2.804
INT (LONG) ohne Index 4 Byte 2.981

Bemerkung zu ip2long

Man könnte auf die Idee kommen, dass die Performance, die man durch den besseren Datentyp erreicht durch die zusätzliche Umrechnung der IP in Longwerte aufgefressen wird. Deshalb habe ich die Funktion IP2LONG ausgiebig getestet und festgestellt dass auf meinem Shared-Server 100.000 Berechnungen etwa 0.05 Sekunden dauern, d.h. die Funktion braucht bei mir etwa 0,0000005 Sekunden, was absolut zu vernachlässigen ist.

$ip=rand(150,244).'.'.rand(0,254).'.'.rand(0,254).'.'.rand(0,254); $t=microtime(true); for ($x=1;$x<100000;$x++) { ip2long($ip); } echo microtime(true)-$t;

Alternativ zur PHP-Funktion IP2LONG kann man auch die MySQL funktion INET_ATON verwenden. Performancemäßig sind die Unterschiede zwischen beiden zu vernachlässigen.

Bemerkung zur Speicherengine ARCHIVE

Getestet mit einer Spalte: Datentyp: INT: und 10.000 Datensätzen (IPs als INT)
MYISAM
Speicherverbrauch: ca. 140kbyte + 1kbyte (index)
Einfügungszeit für 10.000 Einträge: 2,35s (gemittelt)
ARCHIVE
Speicherverbrauch: ca. 89kbyte + kein extra index
Einfügungszeit für 10.000 Einträge: 2,37s (gemittelt)

Wie man sieht, ist der Zeitunterschied extrem klein (kann also auch an Meßungenauigkeit liegen). Der Speicherbedarf ist bei ARCHIVE erwartungsgemäß wesentlich kleiner als bei MYISAM.

Fazit

Wie man an der Benchmarkergebnistabelle sehen kann, sind die Abfragen der IP mit Datentyp INT (LONG) mit Index mit Abstand die schnellsten. Alles Andere ist etwa gleich langsam. Man beachte auch die Speichereinsparung von 75% der INT-Werte gegenüber Varchar oder Text! Mich hat das schlechte abschneiden der INT-Anfragen ohne Index etwas verwundert. Falls jemand weiß warum das so ist, kann er sich gerne bei mir melden.