ip datenbank speichernIn (fast) allen Webprojekten werden die IP ‘s der Nutzer gespeichert. Solange die Seite nur wenige IP ´s speichert, ist das sicher kein Problem. Die Probleme beginnen, wenn sich nach einiger Zeit mehrere 10.000 Einträge angesammelt haben und die Suche nach einem speziellen Wert immer länger dauert. Wie man IP ‘s optimal in der Datenbank speichert soll in diesm Beitrag geklärt werden.

Mögliche Datenbank-Daten-Typen

  1. Datentyp Text: Braucht in MySQL genau 17 Byte (Textlänge + 2 Byte)
  2. Datentyp VarChar(15): Braucht in MySQL genau 16 Byte (Textlänge + 1 Byte)
  3. Datentyp Long: Braucht in MySQL genau 4 Byte

Wie man an dieser Auflistung sieht verbraucht der Datentyp Long mit Abstand am wenigsten Speicher, d.h. die Datenbank wird im Verhältnis zu den anderen Möglichkeiten wesentlich kleiner. Dieser Speichervorteil kann durch die Datenbank-Engine: Storage noch weiter verbessert werden. Ein weiterer Vorteil liegt darin, dass Mysql Zahlen wesentlich schneller durchsuchen kann als Text.

Wie kann man IPs in Long werte umwandeln?

Dafür gibt es zwei Möglichkeiten, die performancetechnisch identisch sind.

IP umwandeln mittels PHP

PHP bietet die netten Funktionen IP2LONG und LONG2IP. Damit lassen sich höchst komfortabel IPs in Long werte und Long-Werte in IP ‘s umwandeln. Bitte beachte: Die entstehenden Long-Werte können auch negative sein!

IP umwandeln mittels Datenbank

Alle (von mir untersuchten) Datenbanksysteme bietet eine eigene Implementierung der Funktionen. In Mysql heißen diese beispielsweise INET_ATON bzw. INET_NTOA.

Mein Testscript

Mein Testscript funktioniert so:

  1. Test-Tabelle erstellen (in der jeweils die zu testenden Datentypen hinterlegt sind)
  2. Test-Tabelle mit zufälligen IPs auffüllen
  3. jeweils 500 Anfragen an jede Spalte der Test-Tabelle
  4. Rückgabe der Resultate
$db_server = "localhost";
$db_name = "test";
$db_user = "root";
$db_passwort = "";

//Ab hier nichts mehr ändern!

$db = mysql_connect($db_server,$db_user,$db_passwort) or die ("Konnte keine Verbindung zur Datenbank herstellen, bitte versuchen Sie es später erneut!");
$db_check = MYSQL_SELECT_DB($db_name);

//Tabelle erstellen

mysql_query(' CREATE TABLE IF NOT EXISTS `ip` (
`ip_int` INT NOT NULL ,
`ip_int_index` INT NOT NULL ,
`ip_varchar` VARCHAR( 15 ) NOT NULL ,
`ip_varchar_index` VARCHAR( 15 ) NOT NULL ,
`ip_text` TEXT NOT NULL ,
INDEX ( `ip_int_index` , `ip_varchar_index` )
) ENGINE = MYISAM ');


//Tabelle mit zufälligen IPs füllen
for ($x=1;$x<10000;$x++)
  {
  $ip=rand(150,244).'.'.rand(0,254).'.'.rand(0,254).'.'.rand(0,254);
  mysql_query('insert into ip (ip_int,ip_int_index,ip_varchar,ip_varchar_index,ip_text) values ("'.ip2long($ip).'","'.ip2long($ip).'","'.$ip.'","'.$ip.'","'.$ip.'")');
  }
echo 'DB mit 10.000 IPs gefüllt
';
flush();



//Zeit Messen: INT mit Index
$ip=rand(150,244).'.'.rand(0,254).'.'.rand(0,254).'.'.rand(0,254);
$t2=microtime(true);

for ($x=1;$x<500;$x++)
  {
  mysql_query('select * from ip where ip_int_index="'.(ip2long($ip)+$x).'" LIMIT 1');
  }
$t2=microtime(true)-$t2;
echo 'INT (LONG) mit Index    :'.$t2.' s
';
flush();

//Zeit Messen: INT ohne Index
$ip=rand(150,244).'.'.rand(0,254).'.'.rand(0,254).'.'.rand(0,254);
$t1=microtime(true);
for ($x=1;$x<500;$x++)
  {
  mysql_query('select * from ip where ip_int="'.(ip2long($ip)+$x).'" LIMIT 1');
  }
$t1=microtime(true)-$t1;
echo 'INT (LONG) ohne Index   :'.$t1.' s
';
flush();

//Zeit Messen: VarChar ohne Index
$ip=rand(150,244).'.'.rand(0,254).'.'.rand(0,254).'.'.rand(0,254);
$t3=microtime(true);
for ($x=1;$x<500;$x++)
  {
  mysql_query('select * from ip where ip_varchar="'.long2ip(ip2long($ip)+$x).'" LIMIT 1');
  }
$t3=microtime(true)-$t3;
echo 'VarChar (15) ohne Index   :'.$t3.' s
';
flush();

//Zeit Messen: VarChar mit Index
$ip=rand(150,244).'.'.rand(0,254).'.'.rand(0,254).'.'.rand(0,254);
$t4=microtime(true);
for ($x=1;$x<500;$x++)
  {
  mysql_query('select * from ip where ip_varchar_index="'.long2ip(ip2long($ip)+$x).'" LIMIT 1');
  }
$t4=microtime(true)-$t4;
echo 'VarChar (15) mit Index   :'.$t4.' s
';
flush();

//Zeit Messen: Text ohne Index
$ip=rand(150,244).'.'.rand(0,254).'.'.rand(0,254).'.'.rand(0,254);
$t5=microtime(true);
for ($x=1;$x<500;$x++)
  {
  mysql_query('select * from ip where ip_text="'.long2ip(ip2long($ip)+$x).'" LIMIT 1');
  }
$t5=microtime(true)-$t5;
echo 'Text ohne Index  :'.$t5.' s
';
flush();




mysql_query('DROP TABLE ip');

Hinweis:: Ich benutze für jede SQL-Anfrage eine unique IP (ip2long($ip)+$x). Grund dafür ist, dass ich verhindern möchte, dass die Ergebnis aus dem Query-Cache kommen!

Ergebnisse meines Benchmarks

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

Ergebnis

Die Benchmarkergebnistabelle zeigt, dass die Abfragen der IP mit Datentyp INT (LONG) mit Index mit Abstand die schnellsten sind. Alles Andere ist etwa gleich langsam. Die Speichereinsparung von 75% der INT-Werte gegenüber Varchar oder Text sollte ebenfalls berücksichtigt werden!

Update ip_v6

Wer das ganze auch für ip_v6 braucht kann die folgende PHP-Funktion von php.net verwenden:

$ipv6 = "2001:4860:a005::68";

function ip2long6($ipv6) {
  $ip_n = inet_pton($ipv6);
  $bits = 15; // 16 x 8 bit = 128bit
  while ($bits >= 0) {
    $bin = sprintf("%08b",(ord($ip_n[$bits])));
    $ipv6long = $bin.$ipv6long;
    $bits--;
  }
  return gmp_strval(gmp_init($ipv6long,2),10);
}

function long2ip6($ipv6long) {

  $bin = gmp_strval(gmp_init($ipv6long,10),2);
  if (strlen($bin) < 128) {
    $pad = 128 - strlen($bin);
    for ($i = 1; $i <= $pad; $i++) {
    $bin = "0".$bin;
    }
  }
  $bits = 0;
  while ($bits <= 7) {
    $bin_part = substr($bin,($bits*16),16);
    $ipv6 .= dechex(bindec($bin_part)).":";
    $bits++;
  }
  // compress

  return inet_ntop(inet_pton(substr($ipv6,0,-1)));
}

print $ipv6long =  ip2long6($ipv6)."n";
print $ipv6 = long2ip6($ipv6long)."n";