Warum sollten MySQL-Felder NOT NULL verwenden?

Ich habe vor Kurzem in einer neuen Firma angefangen und bin auf einige kleinere Probleme im Datenbankdesign gestoßen. Viele Datenbankfelder haben kein NOT NULL, was für einen Patienten mit Zwangsstörungen im Spätstadium einfach unerträglich ist, also habe ich diesen Artikel geschrieben.

Basierend auf dem aktuellen Entwicklungsstatus werden wir alle Felder auf NOT NULL setzen und Standardwerte zuweisen.

• Normalerweise wird der Standardwert folgendermaßen eingestellt:
• Ganzzahl, wir verwenden im Allgemeinen 0 als Standardwert.
• Zeichenfolge, standardmäßig leere Zeichenfolge

Die Zeit kann der Standardwert 1970-01-01 08:00:01 oder der Standardwert 0000-00-00 00:00:00 sein, aber die Verbindungsparameter müssen zeroDateTimeBehavior=convertToNull hinzufügen. Es wird empfohlen, dieses Standardzeitformat nicht zu verwenden.

Aber denken Sie über den Grund nach, warum es auf NOT NULL gesetzt werden sollte?

Es gibt eine Passage aus High Performance MySQL:

Versuchen Sie, NULL zu vermeiden

Viele Tabellen enthalten Spalten, die NULL (leere Werte) sein können, auch wenn die Anwendung keine NULL-Werte speichern muss, da NULL die Standardeigenschaft der Spalte ist. Normalerweise empfiehlt es sich, anzugeben, dass eine Spalte NICHT NULL ist, es sei denn, Sie müssen wirklich NULL-Werte speichern.

Abfragen mit NULL-Spalten sind für MySQL schwieriger zu optimieren, da NULL-Spalten Indizes, Indexstatistiken und Wertevergleiche komplizierter machen. Spalten, die NULL sein können, benötigen mehr Speicherplatz und erfordern eine spezielle Behandlung in MySQL. Beim Indizieren einer NULL-fähigen Spalte wird für jeden Indexdatensatz ein zusätzliches Byte benötigt, was in MyISAM sogar dazu führen kann, dass aus einem Index mit fester Größe (wie etwa einem Index für eine einzelne Ganzzahlspalte) ein Index mit variabler Größe wird.

Im Allgemeinen ist die Leistungsverbesserung durch die Änderung einer NULL-fähigen Spalte in NOT NULL gering. Es besteht daher keine Notwendigkeit, diese Situation zuerst in einem vorhandenen Schema zu finden und zu ändern (beim Optimieren), es sei denn, es ist sicher, dass dies Probleme verursachen wird. Wenn Sie jedoch vorhaben, einen Index für eine Spalte zu erstellen, sollten Sie versuchen, zu vermeiden, dass dieser NULL-Werte enthält.

Natürlich gibt es Ausnahmen. Erwähnenswert ist beispielsweise, dass InnoDB ein separates Bit zum Speichern von NULL-Werten verwendet und daher eine gute Speichereffizienz für spärliche Daten aufweist. Dies gilt nicht für MyISAM.

Die Beschreibung im Buch erwähnt mehrere größere Probleme. Ich werde das MyISAM-Problem hier einmal außer Acht lassen und mich hier auf InnoDB konzentrieren.

• Wenn NOT NULL nicht festgelegt ist, ist NULL der Standardwert der Spalte. Wenn dies nicht erforderlich ist, versuchen Sie, NULL nicht zu verwenden.
• Die Verwendung von NULL bringt weitere Probleme mit sich, z. B. kompliziertere Indizes, Indexstatistiken und Wertberechnungen. Wenn Sie Indizes verwenden, sollten Sie das Setzen von Spalten auf NULL vermeiden.
• Handelt es sich um eine Indexspalte, führt dies zu Speicherplatzproblemen, erfordert zusätzliche spezielle Verarbeitung und führt außerdem dazu, dass mehr Speicherplatz belegt wird.
• Es bietet eine bessere Platzeffizienz für spärliche Daten. Spärliche Daten beziehen sich auf die Situation, in der viele Werte NULL sind und nur wenige Zeilen in den Spalten andere Werte als NULL aufweisen.

Standardwert

Für MySql gilt: Wenn es nicht aktiv auf NOT NULL gesetzt wird, ist der Standardwert beim Einfügen von Daten NULL.

Die Bedeutungen der von NULL und NOT NULL verwendeten leeren Werte sind unterschiedlich. NULL kann bedeuten, dass der Wert dieser Spalte unbekannt ist, während der leere Wert bedeuten kann, dass wir den Wert kennen, er aber leer ist.

Wenn beispielsweise ein name in einer Tabelle NULL ist, können wir davon ausgehen, dass wir den Namen nicht kennen. Umgekehrt können wir bei einer leeren Zeichenfolge davon ausgehen, dass wir wissen, dass kein Name vorhanden ist und es sich um einen Nullwert handelt.

Für die meisten Programme besteht keine besondere Notwendigkeit, dass Felder NULL sind. Im Gegenteil, NULL-Werte verursachen Probleme wie Nullzeiger im Programm.

Für die meisten aktuellen Situationen, in denen MyBatis verwendet wird, empfehle ich die Verwendung der standardmäßig generierten insertSelective -Methode oder einer rein manuellen insert-Methode, um das Problem zu vermeiden, dass Standardwerte nicht wirksam werden oder Einfügefehler durch das Hinzufügen von NOT NULL-Feldern auftreten.

Wertberechnung

Ungenaue Aggregatfunktionen

Bei Spalten mit NULL-Werten werden NULL-Werte bei der Verwendung von Aggregatfunktionen ignoriert.

Jetzt haben wir eine Tabelle. name Namensfeld ist standardmäßig NULL. Zu diesem Zeitpunkt ist das Ergebnis count name 1, was falsch ist.

count(*) zählt die Anzahl der Zeilen in der Tabelle, während count(name) die nicht-NULL-Spalten in der Tabelle zählt.

= Ungültig

Bei Spalten mit NULL-Werten können Sie den Ausdruck = nicht zur Beurteilung verwenden. Die folgende Abfrage für name ist ungültig und Sie müssen is NULL verwenden.

Operationen mit anderen Werten

NULL und jede andere Wertoperation ist NULL, einschließlich des Werts des Ausdrucks, der ebenfalls NULL ist.

age des zweiten Datensatzes in user Benutzertabelle ist NULL, also ist es nach +1 immer noch NULL. name ist NULL, also ist das Ergebnis nach der concat immer noch NULL.

Sie können sich das folgende Beispiel noch einmal ansehen. Jede Operation mit NULL führt zu NULL. Stellen Sie sich vor, ein von Ihnen entworfenes Feld ist NULL und Sie führen versehentlich verschiedene Operationen darauf aus. Was wird das Ergebnis sein? . .

verschieden, gruppieren nach, sortieren nach

Bei „ distinct “ und group by werden alle NULL-Werte als gleich betrachtet, und bei order by “ werden aufsteigende NULL-Werte zuerst sortiert.

Andere Probleme

In der Tabelle gibt es nur einen Datensatz mit einem Namen. Zu diesem Zeitpunkt sollte das erwartete Ergebnis der Abfrage „Name !=a darin bestehen, die verbleibenden zwei Datensätze zu finden, aber es wird festgestellt, dass das erwartete Ergebnis nicht übereinstimmt.

Indizierungsprobleme

Um die Auswirkung von NULL-Feldern auf Indizes zu überprüfen, fügen Sie Indizes zu name und age hinzu.

Im Internet finden sich viele Meinungen, die besagen, dass Indizes nicht verwendet werden können, wenn die Abfrage NULL ist. Das ist nicht richtig. Laut dem offiziellen Dokument [3] können Indizes normal verwendet werden, wenn NULL- und Bereichsabfragen verwendet werden. Die tatsächlichen Überprüfungsergebnisse scheinen dieselben zu sein. Siehe das folgende Beispiel.

Anschließend fügten wir zum Testen weiterhin einige Daten in die Datenbank ein. Als die Anzahl der NULL-Spaltenwerte zunahm, stellten wir fest, dass der Index ungültig wurde.

Wir wissen, dass der Ausführungsprozess einer SQL-Abfrage ungefähr so ​​abläuft:

Der Connector ist zunächst dafür zuständig, eine Verbindung zur angegebenen Datenbank herzustellen und anschließend zu prüfen, ob sich diese Anweisung im Abfragecache befindet. Wenn ja, gibt er das Ergebnis direkt zurück.

Wenn der Cache keinen Treffer ergibt, muss der Analysator eine Syntax- und lexikalische Analyse der SQL-Anweisung durchführen, um zu bestimmen, ob die SQL-Anweisung zulässig ist.

Nun kommen wir zum Optimierer, der auswählt, welcher Index sinnvoller zu verwenden ist, und der spezifische Ausführungsplan der SQL-Anweisung bestimmt.

Schließlich ist der Executor dafür verantwortlich, die Anweisung auszuführen, abzufragen, ob eine Berechtigung vorliegt, und das Ausführungsergebnis zurückzugeben.

Aus den oben stehenden einfachen Testergebnissen können wir ersehen, dass, wenn die Indexspalte NULL-Werte enthält, der Optimierer bei der Indexauswahl komplizierter und schwieriger zu optimieren ist, wie im Buch erwähnt.

Stauraum

Eine Datensatzzeile in der Datenbank wird auch in der endgültigen Datenträgerdatei als Zeile gespeichert. Für InnoDB gibt es vier Zeilenspeicherformate: REDUNDANT , COMPACT , DYNAMIC und COMPRESSED .

Das standardmäßige Zeilenspeicherformat von InnoDB ist COMPACT . Das Speicherformat ist unten dargestellt. Der gepunktete Teil existiert möglicherweise nicht.

Liste mit Feldlängen variabler Länge: Wenn mehrere Felder vorhanden sind, werden sie in umgekehrter Reihenfolge gespeichert. Wir haben nur ein Feld, daher müssen wir nicht so viel beachten. Das Speicherformat ist hexadezimal. Wenn kein Feld variabler Länge vorhanden ist, wird dieser Teil nicht benötigt.

NULL-Werteliste: Wird verwendet, um die Fälle zu speichern, in denen der Wert in unserem Datensatz NULL ist. Wenn mehrere NULL-Werte vorhanden sind, werden sie auch in umgekehrter Reihenfolge gespeichert und müssen ein ganzzahliges Vielfaches von 8 Bits sein. Wenn es weniger als 8 Bits sind, werden die höchsten Bits mit 0 aufgefüllt. 1 steht für NULL und 0 steht für nicht NULL. Wenn beide NICHT NULL sind, dann existiert dies.

ROW_ID: Die eindeutige Kennung einer Datensatzzeile. Wenn kein Primärschlüssel angegeben ist, dient die automatisch generierte ROW_ID als Primärschlüssel.

TRX_ID: Transaktions-ID.

ROLL_PRT: Rollback-Zeiger.

Schließlich der Wert jeder Spalte.

Um das Problem mit dem Speicherformat zu veranschaulichen, erstelle ich zum Testen eine Tabelle. In dieser Tabelle ist nur das Feld c1 NICHT NULL und die anderen können NULL sein.

Liste variabler Feldlängen: Die Längen der Felder c1 und c3 betragen jeweils 1 und 2, sodass die in Hexadezimalzahlen umgewandelte Länge 0x01 0x02 und in umgekehrter Reihenfolge 0x02 0x01 ist.

NULL-Werteliste: Da es Spalten gibt, die NULL zulassen, sind c2,c3,c4 jeweils 010. Dasselbe gilt nach dem Umkehren. Gleichzeitig werden die hohen Bits mit 0 aufgefüllt, um 8 Bits zu erhalten. Das Ergebnis ist 00000010 .

Die anderen Felder ignorieren wir vorerst. Das Ergebnis des ersten Datensatzes ist natürlich das, das Ergebnis nach der Kodierung werden wir hier nicht berücksichtigen.

Dies ist das Format einer vollständigen Datenzeile. Wenn wir dagegen alle Felder auf NOT NULL setzen und die Daten a,bb,ccc,dddd einfügen, sollte das Speicherformat folgendermaßen aussehen:

Obwohl wir feststellen, dass NULL selbst keinen Speicherplatz belegt, belegt es, falls NULL vorhanden ist, ein zusätzliches Byte Flag-Speicherplatz.

Artikelreferenzdokumente:

https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html
https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
https://dev.mysql.com/doc/refman/5.6/en/is-null-optimization.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html
https://www.cnblogs.com/zhoujinyi/articles/2726462.html

Dies ist das Ende dieses Artikels darüber, warum MySQL-Felder NOT NULL verwenden. Weitere Informationen zur Verwendung von NOT NULL in MySQL-Feldern finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!