Detaillierte Erklärung zur Verwendung von NULL und NOT NULL beim Erstellen von Tabellen in MySQL

Bezüglich einiger MySQL-Spezifikationen haben manche Unternehmen in ihren Spezifikationen zur Tabellenerstellung die Anforderung, dass keines der Felder leer sein darf, was bedeutet, dass ein Standardwert gespeichert wird, wenn kein Wert vorhanden ist. Eigentlich sollte man sagen, dass alle Felder absolut nicht leer sind. Man sollte sagen, dass sie so nicht leer wie möglich sind. In manchen Fällen ist es unmöglich, einen Standardwert anzugeben.
Welche Überlegungen stehen also hinter dieser Anforderung, Speicherplatz? Wie ist die Leistung der zugehörigen Hinzufügungs-, Lösch-, Abfrage- und Änderungsvorgänge? Oder gibt es noch andere Überlegungen? In diesem Artikel werde ich eine grobe Analyse vornehmen, ob diese Theorie auf der Grundlage meines persönlichen Verständnisses sinnvoll und durchführbar ist.

1. Speicherbasierte Überlegungen

Die Speicheranalyse erfordert hier ein klares Verständnis des Speicherformats von MySQL-Datenzeilen. Hier können wir einige Schlussfolgerungen direkt aus diesem Artikel übernehmen, der eine sehr klare Analyse liefert (tatsächlich bezieht er sich auch auf „MySQL Technical Content Innodb Storage Engine“).
Für die standardmäßige Datenzeilenstruktur im dynamischen oder kompakten Format lautet das Zeilenstrukturformat wie folgt:
|Feldlängenliste mit variabler Länge (1–2 Bytes) |NULL-Flag (1 Byte) |Datensatzkopfinformationen (5 Bytes) |RowID (6 Bytes) |Transaktions-ID (6 Bytes) |Rollback-Zeiger (7 Bytes) |Zeileninhalt

1. Bei Feldern mit variabler Länge gilt: Wenn der zugehörige Feldwert NULL ist, belegt das zugehörige Feld keinen Speicherplatz. NULL-Werte werden nicht gespeichert und beanspruchen keinen Platz, erfordern aber ein Flag-Bit (eines pro Zeile).
2. Bei Feldern mit variabler Länge erfordern die entsprechenden Felder NOT NULL. Wenn sie als '' gespeichert werden, nehmen sie keinen Platz ein. Wenn alle Wörterbücher in einer Tabelle NOT NULL sind, benötigt der Zeilenkopf kein NULL-Flag.
3. Alle Felder haben eine feste Länge. Unabhängig davon, ob sie NOT NULL sein müssen oder nicht, ist kein Flag erforderlich. Gleichzeitig besteht keine Notwendigkeit, die Länge von Spalten mit variabler Länge zu speichern.

Im Hinblick auf die beiden Fälle von Nullwert und Nichtleerwert (nicht Null-Standardwert) gilt: Wenn der in einem Feld gespeicherte Inhalt leer ist, also nichts vorhanden ist, wird ersterer als Nullwert und letzterer als leere Zeichenfolge gespeichert. Die Speicherplatzgröße des Feldinhalts selbst ist dieselbe.
Wenn eine Tabelle jedoch nullbare Felder speichert, erfordert der Header der entsprechenden Datenzeile ein 1-Byte-NULL-Flag. Dies bestimmt, dass beim Speichern derselben Daten, wenn null zulässig ist, jede Zeile ein Byte mehr Speicherplatz hat als im Fall von nicht null.
Dieser Faktor kann einer der Gründe sein, warum manche Unternehmen oder Einzelpersonen auf der Überzeugung beharren, dass „Nullfelder in allen Tabellen verboten sind“ (ich persönlich stehe dem negativ gegenüber; Sie können versuchen, alle Felder in der Datenbank auf ungleich null zu setzen und dann einen Standardwert festzulegen, um zu sehen, ob es zu Chaos kommt).
Wir werden hier keine „Mikro“-Analyse durchführen, sondern die Unterschiede direkt aus einer „Makro“-Perspektive betrachten.

Testdemo

Erstellen Sie direkt eine Tabelle mit derselben Struktur, aber ein Tabellenfeld ist nicht null und das andere Tabellenfeld ist null. Verwenden Sie dann diese gespeicherte Prozedur, um Daten gleichzeitig mit einem Verhältnis von 1:10 zwischen Nullwerten und Nicht-Nullwerten in die beiden Tabellen zu schreiben. Das heißt, es werden 6 Millionen Datenzeilen geschrieben, wobei für jeweils 10 Datenzeilen eine Datenfeldzeile null ist.

CREATE TABLE eine
(
 id INT AUTO_INCREMENT,
 c2 VARCHAR(50) NICHT NULL STANDARD '',
 c3 VARCHAR(50) NICHT NULL STANDARD '',
 PRIMÄRSCHLÜSSEL (id)
);

Tabelle erstellen b
(
 id INT AUTO_INCREMENT,
 c2 VARCHAR(50),
 c3 VARCHAR(50),
 PRIMÄRSCHLÜSSEL (id)
);


CREATE DEFINER=`root`@`%` VERFAHREN `create_test_data`(
 IN `loop_cnt` INT
)
SPRACHE SQL
NICHT DETERMINISTISCH
ENTHÄLT SQL
SQL-SICHERHEITSDEFINIERER
KOMMENTAR ''
BEGINNEN
 DECLARE v2, v3 VARCHAR(36);
 
 TRANSAKTION STARTEN;
 
 während loop_cnt>0 mache
 SET v2 = UUID();
 SET v3 = UUID();

 
 wenn (loop_cnt MOD 10) = 0 dann
 EINFÜGEN IN a (c2,c3) WERTE(STANDARD,STANDARD);
 EINFÜGEN IN b (c2,c3) WERTE(STANDARD,STANDARD);
 anders
 EINFÜGEN IN a (c2,c3) WERTE (v2,v3);
 EINFÜGEN IN b (c2,c3) WERTE (v2,v3);
 ENDE wenn;
 
 SETZEN Sie loop_cnt=loop_cnt-1;
 ENDE während;
 BEGEHEN;

Die Tabellen a und b liefern genau dieselben Daten.

Überprüfen Sie den belegten Speicherplatz und fragen Sie die Speicherinformationen der beiden Tabellen aus information_schema.TABLES ab.

1. Der Unterschied von einem Byte spiegelt sich in avg_row_length wider. Da alle Felder in Tabelle a nicht null sind, spart jede Zeile im Vergleich zu Tabelle b ein Byte Speicherplatz.
2. Der Unterschied im Gesamtspeicherplatz: Tabelle a hat 662683648/1024/1024=631.98437500MB, Tabelle b hat 666877952/1024/1024=635.98437500MB,
Im vorliegenden Fall beträgt der Unterschied bei 6 Millionen Datenzeilen 4 MB, also innerhalb von 1 %. Tatsächlich ist dieser Unterschied in tatsächlichen Situationen, wenn mehr Felder vorhanden sind und die Tabelle größer ist, viel kleiner als 1 %.

Wenn Sie mir in Bezug auf den Speicherplatz sagen, dass Ihnen 1 GB Speicherplatz für eine 1T-Datenbank wichtig ist, sind jeglicher Daten-/Indexfragmentierungsspeicher, ein wenig reservierter Speicherplatz, Speicherplatz für Junk-Dateien, nutzloser Indexspeicherplatz usw. weitaus größer als der zusätzliche Unterschied, der durch die Nullbarkeit entsteht.

2. Effizienz beim Hinzufügen, Löschen, Prüfen und Ändern

Vergleichen Sie die Lese- und Schreibvorgänge durch kontinuierliches Lesen und Schreiben von Daten innerhalb eines Bereichs, um die Lesebedingungen der Tabellen a und b zu vergleichen.
2.1.) Erstens ist der Pufferpool viel größer als die Tabellengröße, sodass Sie sich keine Sorgen über den durch physische E/A verursachten Unterschied machen müssen. Derzeit werden die Daten der beiden Tabellen vollständig im Pufferpool gespeichert.
2.1.) Der Lesetestvorgang wird auf der Maschine der MySQL-Instanz ausgeführt, sodass die durch die Netzwerkinstabilität verursachten Unterschiede ignoriert werden können.

Der Unterschied zwischen Hinzufügen, Löschen, Prüfen und Ändern ist ähnlich dem Unterschied beim Speicherplatz oder sogar noch kleiner, da sich eine einzelne Zeile um 1 Byte unterscheidet und ein Unterschied von 5 MB nur bei einer Vergrößerung auf 600 W+ sichtbar wird. Beim Hinzufügen, Löschen, Prüfen und Ändern wurde nach verschiedenen Tests kein offensichtlicher Unterschied festgestellt.

#!/usr/bin/env python3
pymysql importieren
Importzeit
mysql_conn_conf = {'Host': '127.0.0.1', 'Port': 3306, 'Benutzer': 'root', 'Passwort': '******', 'db': 'db01'}


def mysql_read(Tabellenname):
 conn = pymysql.connect(Host=mysql_conn_conf['Host'], Port=mysql_conn_conf['Port'], Datenbank=mysql_conn_conf['DB'], Benutzer=mysql_conn_conf['Benutzer'], Passwort = mysql_conn_conf['Passwort'])
 Cursor = conn.cursor()
 versuchen:
 cursor.execute(''' wähle id,c2,c3 aus {0}, wobei id>3888888 und id<3889999;'''.format(Tabellenname))
 Zeile = Cursor.fetchall()
 außer pymysql.Fehler als e:
 drucken("mysql-Ausführungsfehler:", e)
 cursor.schließen()
 conn.close()


def mysql_write(Schleife,Tabellenname):
 conn = pymysql.connect(Host=mysql_conn_conf['Host'], Port=mysql_conn_conf['Port'], Datenbank=mysql_conn_conf['DB'], Benutzer=mysql_conn_conf['Benutzer'], Passwort = mysql_conn_conf['Passwort'])
 Cursor = conn.cursor()
 versuchen:
 wenn Schleife%10 == 0:
 cursor.execute(''' einfügen in {0}} (c2,c3) Werte(DEFAULT,DEFAULT)'''.format(Tabellenname))
 anders:
 cursor.execute(''' einfügen in {1}} (c2,c3) Werte(uuid(),uuid())'''.format(Tabellenname))
 außer pymysql.Fehler als e:
 drucken("mysql-Ausführungsfehler:", e)
 cursor.schließen()
 conn.commit()
 conn.close()


wenn __name__ == '__main__':
 Zeit_Start = Zeit.Zeit()
 Schleife = 10
 while-Schleife>0:
 mysql_write(Schleife)
 Schleife = Schleife - 1

 Zeit_Ende = Zeit.Zeit()
 Zeit_c = Zeit_Ende - Zeit_Start
 drucken('Zeitaufwand', time_c, 's')

3. Semantische Analyse und logische Überlegungen zu verwandten Feldern

Zu diesem Punkt gibt es zu viele Meinungsverschiedenheiten und er ist auch der Punkt, der am ehesten Anlass zu Klatsch und Streit gibt.

1. Bei Zeichentypen bedeutet NULL Nichtexistenz und '' bedeutet leer. Nichtexistenz und leer sind nicht dasselbe. Ich bin nicht damit einverstanden, dass wir NOT NULL verwenden und einen Standardwert angeben müssen.
2. Bei Zeichentypen ist NULL in keiner Datenbank gleich NULL, da bei der Verarbeitung von Verknüpfungen oder Filtern auf verwandten Feldern die Situation, in der beide Seiten der Verbindung NULL sind, nicht berücksichtigt werden muss. Sobald NULL durch '' ersetzt wird, ist '' gleich '', und dies führt zu einer Semantik, die sich völlig von der Speicherung von NULL unterscheidet.
3. Wie kann man bei Zeichentypen zwischen „“ und einer leeren Zeichenfolge unterscheiden, wenn die entsprechenden Felder standardmäßig auf „“ gesetzt sind? Beispielsweise lässt das Feld „Anmerkungen“ kein NULL zu und der Standardwert ist „“. Wie kann man zwischen dem leeren Ausdruck von NULL und der leeren Zeichenfolge des Standardwerts „“ unterscheiden?
4. Bei verwandten Abfragevorgängen gilt: Wenn NULL zulässig ist, wird das Filtern von Nicht-NULL-Werten mit „where *** is not null“ durchgeführt, was in Bezug auf die Semantik sehr klar und intuitiv ist. Sobald das Feld nicht leer ist, wird standardmäßig „“ verwendet, und es wird die äußerst ekelhafte Schreibweise „where *** <>“ verwendet. Was genau wird ausgedrückt, und die Semantik hat begonnen, vage zu werden
5. Für Zeittypen sind Standardwerte in den meisten Fällen nicht zulässig. Was ist ein geeigneter Standardwert? Ist die aktuelle Zeit geeignet? Ist das Jahr 2000 geeignet? Ist die Eröffnungszeit der Olympischen Spiele 2008 in Peking geeignet?
6. Wenn NULL für numerische Typen wie int und decimal verboten ist, was ist der geeignete Standardwert? Ist 0 angemessen? Ist -1 angemessen? -9999999 ... Ist das angemessen? Ist 10086 geeignet? Ist 1024 geeignet? Ehrlich gesagt gibt es keinen geeigneten Standardwert. NULL selbst ist am geeignetsten.

Meine persönliche Meinung ist ganz klar. Sofern keine spezielle Anforderung besteht, dass ein Feld keinen NULL-Wert haben darf, ist NULL unter normalen Umständen NULL.
Wenn NULL keine Bedeutung hat, sollte die Datenbank einfach kein NULL enthalten. Welche Datenbank hat überhaupt keinen NULL-Typ?
Natürlich ist es nicht ausgeschlossen, dass sich einige DBAs unbegründete Dinge ausdenken, um professionell zu wirken. Es gibt derzeit den Trend, dass sie sich umso überlegener fühlen, je mehr Einschränkungen sie der Datenbank auferlegen können.

Mir ist eine interessante Sache zu den Standardwerten eingefallen. Als ich mir ein Video auf Bilibili ansah, erwähnte ein bestimmter Uploader, dass der Uploader nach der Analyse der Klicks des Benutzers unverständliche Daten erhalten habe, weil Bilibili die registrierten Benutzer standardmäßig als männlich und das Geburtsdatum als ein bestimmtes Datum einstellte.

Mein persönliches Wissen ist begrenzt, aber die Daten sind ehrlich. Ich möchte wirklich wissen, welche anderen positiven Effekte „alle Felder sind nicht null“ mit sich bringt und wie man diesen positiven Faktor misst. Und haben Sie das wirklich getan? Können Sie verhindern, dass die Felder in allen Datenbanktabellen der gesamten Instanz null sein können?

Dies ist das Ende dieses Artikels über die detaillierte Verwendung von null und nicht null beim Erstellen von Tabellen in MySQL. Weitere relevante Inhalte zu null und nicht null beim Erstellen von Tabellen in MySQL 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!