Wir optimieren derzeit eine MySQL-Tabelle (InnoDB), die mehr als 100 Millionen Zeilen haben wird.
In einer Spalte speichern wir IP-Adressen (VARCHAR 45). Wir müssen einen Index für diese Spalte erstellen, da wir alle Zeilen pro spezifischer IP-Adresse abrufen können.
70% aller Zeilen speichern jedoch keine IP-Adresse ( leer ).
Unsere Frage: Sollen wir diese leeren Werte als NULL speichern und somit NULL für diese Spalte zulassen (fügt jeder Zeile 1 Byte hinzu). Oder sollen wir NULL nicht zulassen und diese leeren Werte als "(leere Zeichenfolge) speichern?
Was ist das Beste für die Leistung?
Wir werden nie nach Zeilen suchen müssen, die leer sind (= '') oder null (IS NULL), nur nach bestimmten IP-Adressen (= '123.456.789.123') suchen.
Update: Es gibt tatsächlich viele Fragen zu SO, die ähnliche Szenarien behandeln. Einige Antworten scheinen jedoch widersprüchlich zu sein oder sagen "es kommt darauf an". Wir werden einige Tests durchführen und unsere Ergebnisse für unser spezifisches Szenario hier veröffentlichen.
VARCHAR(39) ist ausreichend für IPv4 (das alte Format, für das keine weiteren Werte verfügbar sind) und IPv6.
Der Optimierer kann Fehler machen, wenn 70% der Werte gleich sind ('' oder NULL). Ich schlage vor, Sie haben eine andere Tabelle mit der IP und eine ID für die Rückkehr zu Ihrer ursprünglichen Tabelle. Wenn in der zweiten Tabelle keine "leeren" IPs vorhanden sind, ist es wahrscheinlicher, dass der Optimierer "das Richtige tut".
Damit kann LEFT JOIN verwendet werden, um zu sehen, ob es eine IP gibt.
IPv6 kann in BINARY (16) gespeichert werden, um Speicherplatz zu sparen.
Gehen Sie mit NULL -Werten. InnoDB hat keine Platzkosten für NULL s und NULL Werte sind von Indizes ausgeschlossen, so dass Sie schneller nach den Werten suchen können, die vorhanden sind.
Was die Speicherung der IP selbst betrifft (String-Verus-Nummer), scheint das ein viel weniger wichtiger Punkt der Optimierung zu sein.
Der Hauptunterschied zwischen NULL und einer leeren Zeichenfolge bezieht sich auf Werte vergleichen. Zwei leere Zeichenfolgen werden als gleich angesehen. Zwei NULL Werte sind nicht. Zum Beispiel, wenn Sie zwei Tabellen basierend auf verbinden möchten IP-Wert Spalten, wird das Ergebnis für NULL und anders sein leere Zeichenfolgen und höchstwahrscheinlich möchten Sie das Verhalten von NULL.
Wenn Sie nur nach bestimmten IP-Adressen suchen, verwenden Sie NULL oder eine leere Zeichenfolge sollte keine Rolle spielen. Wenn die IP-Wert-Spalte indiziert ist, Der Optimierer erhält eine Schätzung von InnoDB über die Anzahl von Zeilen mit dem spezifischen Wert. Die allgemeine Statistik über die Anzahl der Zeilen pro Wert werden in diesem Fall nicht verwendet.
Wenn Sie NULL-Werte vermeiden, sparen Sie 30 MB bei 100 Millionen Zeilen, wenn 70% der Zeilen sind NULL. (Für Zeilen, deren Wert eine leere Zeichenfolge ist, Sie werden keinen Speicherplatz speichern, da Sie ein Byte benötigen, um das zu speichern Längeninformationen statt.) Verglichen mit dem, was Sie speichern können IP-Werte als Binär-String, das ist nichts, und ich denke nicht Storage Overhead ist ein berechtigtes Anliegen.
Tags und Links mysql performance indexing null