Wie man eine IP in MySQL speichert

Question

Wir haben diese Woche im Büro eine gesunde Debatte. Wir erstellen eine Db, um Proxy-Informationen zu speichern. Für den größten Teil haben wir das Schema bereits ausgearbeitet, außer wie wir IPs speichern sollen. Ein Lager möchte 4 smallints verwenden, eines für jedes Oktett, und das andere möchte einen großen Int, INET_ATON, verwenden.

Diese Tabellen werden riesig sein, also ist Leistung entscheidend. Ich stehe hier in der Mitte, da ich normalerweise MS SQL und 4 small ints in meiner Welt verwende. Ich habe nicht genug Erfahrung mit dieser Art von Volumen zur Speicherung von IPs.

Wir werden Perl- und Python-Skripte verwenden, um auf die Datenbank zuzugreifen und die Daten in mehrere andere Tabellen für Top-Talker, interessanten Traffic usw. weiter zu normalisieren.

Ich bin mir sicher, dass es hier in der Community einige gibt, die etwas Ähnliches gemacht haben, wie wir es tun, und ich interessiere mich dafür, von ihren Erfahrungen zu hören und welcher Weg am besten ist, ein großer Int oder 4 small ints für IP-Adressen.

BEARBEITEN - Eine unserer Bedenken ist der Speicherplatz, diese Datenbank wird riesig sein, wie in 500.000.000 Datensätzen pro Tag. Wir versuchen also, das Platzproblem zusammen mit dem Leistungsproblem abzuwägen.

BEARBEITEN 2 Ein Teil des Gesprächs hat sich auf das Datenvolumen verlagert, das wir speichern werden...das ist nicht meine Frage. Die Frage ist, welche Methode bevorzugt wird, um eine IP-Adresse zu speichern, und warum. Wie ich in meinen Kommentaren gesagt habe, arbeiten wir für ein großes Fortune-50-Unternehmen. Unsere Protokolldateien enthalten Nutzungsdaten unserer Benutzer. Diese Daten werden wiederum in einem Sicherheitskontext verwendet, um einige Metriken zu erstellen und mehrere Sicherheitstools zu unterstützen.

Answer 1

Ich würde vorschlagen, sich anzusehen, welche Art von Abfragen Sie ausführen werden, um zu entscheiden, welches Format Sie übernehmen.

Nur wenn Sie einzelne Oktette herausziehen oder vergleichen müssen, müssten Sie in Betracht ziehen, sie in separate Felder aufzuteilen.

Andernfalls speichern Sie es als 4-Byte-Integer. Das hat auch den Bonus, dass Sie die integrierten MySQL-Funktionen INET_ATON() und INET_NTOA() verwenden können.

Leistung vs. Speicherplatz

Speicherplatz:

Wenn Sie nur IPv4-Adressen unterstützen möchten, kann Ihr Datentyp in MySQL ein UNSIGNED INT sein, der nur 4 Bytes Speicherplatz verwendet.

Um die einzelnen Oktette zu speichern, müssten Sie nur UNSIGNED TINYINT Datentypen verwenden, keine SMALLINTS, die jeweils 1 Byte Speicherplatz verwenden würden.

Beide Methoden würden ähnlichen Speicherplatz verwenden, vielleicht etwas mehr für separate Felder für einen Overhead.

Weitere Informationen:

• Übersicht über numerische Datentypen
• Ganzzahlige Typen (Exakter Wert) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

Leistung:

Die Verwendung eines einzelnen Feldes führt zu wesentlich besseren Leistungen, es ist nur ein einziger Vergleich anstatt 4. Sie haben erwähnt, dass Sie nur Abfragen gegen die gesamte IP-Adresse ausführen werden, daher sollte es nicht notwendig sein, die Oktette separat zu halten. Die Verwendung der INET_* Funktionen von MySQL führt einmalig zur Konvertierung zwischen Text- und Ganzzahldarstellungen für den Vergleich.

Answer 2

Eine BIGINT ist in MySQL 8 Bytes groß.

Um IPv4-Adressen zu speichern, reicht ein UNSIGNED INT aus, was meiner Meinung nach das geeignete ist.

Ich kann mir kein Szenario vorstellen, in dem 4 Oktette mehr Leistung bringen würden als ein einzelnes INT, und letzteres ist auch viel praktischer.

Beachten Sie auch, dass wenn Sie Abfragen wie diese durchführen möchten:

SELECT  *
FROM    ips
WHERE   ? ZWISCHEN start_ip UND end_ip

, wobei start_ip und end_ip Spalten in Ihrer Tabelle sind, die Leistung schlecht sein wird.

Diese Abfragen werden verwendet, um festzustellen, ob eine gegebene IP innerhalb eines Subnetzbereichs liegt (normalerweise um sie zu sperren).

Um diese Abfragen effizient zu gestalten, sollten Sie den gesamten Bereich als ein LineString-Objekt mit einem SPATIAL-Index speichern und dann wie folgt abfragen:

SELECT  *
FROM    ips
WHERE   MBRContains(?, ip_range)

Weitere Details dazu finden Sie in diesem Eintrag in meinem Blog:

• IPs sperren

Answer 3

Verwenden Sie PostgreSQL, es gibt einen nativen Datentyp dafür.

Ernsthafter betrachtet, würde ich mich in das "eine 32-Bit-Integer" Lager einordnen. Eine IP-Adresse ergibt nur dann Sinn, wenn alle vier Oktette gemeinsam betrachtet werden, daher gibt es keinen Grund, die Oktette in separaten Spalten in der Datenbank zu speichern. Würden Sie eine Telefonnummer in drei (oder mehr) verschiedenen Feldern speichern?

Answer 4

Es klingt für mich nicht besonders sinnvoll, getrennte Felder zu haben - ähnlich wie das Aufteilen einer Postleitzahl in Abschnitte oder einer Telefonnummer.

Es könnte nützlich sein, wenn Sie spezifische Informationen zu den Abschnitten wünschen, aber ich sehe keinen wirklichen Grund, keine 32-Bit-Zahl zu verwenden.

Answer 5

Für die Kompatibilität mit sowohl IPv4 als auch IPv6 verwenden Sie VARBINARY(16), wobei IPv4 immer BINARY(4) sein wird und IPv6 immer BINARY(16) sein wird. Daher scheint VARBINARY(16) der effizienteste Weg zu sein, um beide zu unterstützen. Und um sie aus dem normalen lesbaren Format in binär zu konvertieren, verwenden Sie INET6_ATON('127.0.0.1'), und um das umzukehren, nutzen Sie INET6_NTOA(binär)