Tag 4
1 Dokumentationsaufträge
1.1 Storage Engines bei MySQL
1.2 Benutzer und Berechtigungen
1.3 Server-Konfiguration
1.4 Server-Betrieb
2 Übungen
2.1 Konfiguration eines RDBMS
2.1.1 Server-Konfiguration
Transaktions-Isolation : Verfizieren Sie welche Transaktions-Isolation auf Ihrem Server aktiviert ist. Dokumentieren Sie, was das bezüglich den Anomalien für Ihre Installation bedeutet.
SHOW VARIABLES LIKE "transaction_isolation";
mysql> SHOW VARIABLES LIKE "transaction_isolation";
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)
REPEATABLE-READ gewährleistet, dass die gleichen Daten bei wiederholten Lesevorgängen innerhalb der Transaktion konsistent bleiben. Phantom Reads sind möglich.
Exportieren Sie die aktuelle Liste an System-Variablen
-- Ausserhalb des MySQL Servers ausführen.
sudo mysql -u root -p -e "SHOW VARIABLES;" > ./Downloads/system_variables.txt
Netzwerkkonfiguration DBMS-Server anpassen und dokumentieren (wie kann man einen TCP Port öffnen?)
...
port = 33066
...
sudo mysql -u root -p -P 33066
2.1.2 Server-Betrieb
Protokollierung langsamer Abfragen aktivieren
...
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
...
Listen Sie den Inhalt des Data-Directories auf
...
datadir = /var/lib/mysql
...
sudo ls /var/lib/mysql
Dokumentieren Sie die Default-Datenbanken mysql, sys, performance_schema
mysql: Enthält systemrelevante Informationen, MySQL-Benutzerkonten und deren Zugriffsrechte. Sie ist für Verwaltung von Benutzerkonten und die Zugriffsrechte nötig.
sys: Diese Datenbank wirs automatisch erstellt und enthält Informationen über Speicher, CPU, Zugriffe usw. Es kann als Monitoring für das DBMS genutzt werden.
performance_schema: Detailliertere Informationen die für die Perfomance für das DBMS zuständig sind. Es kann verwendet werden um Abfragen zu analysieren.
Alle drei Datenbanken sind für MySQL essentiell und sollten nicht vom User bearbeitet oder gelöscht werden.
2.1.3 Datentypen / Attribute
Recherchieren Sie zu Datentypen in MySQL
Offizielle MySQL-Doku (ein bissschen unübersichtlich)
Eine bessere Übersicht gibt die folgende Tabelle Quelle:
| Datentyp | Speicherplatz | Beschreibung |
|---|---|---|
| Numerisch | ||
| TINYINT | 1 Byte | Ganzzahlen Bereich mit Vorzeichen: -128 bis 127 Bereich ohne Vorzeichen: 0 bis 255 |
| SMALLINT | 2 Bytes | Ganzzahlen Bereich mit Vorzeichen: -32.768 bis 32.767 Bereich ohne Vorzeichen: 0 bis 65.535 |
| MEDIUMINT | 3 Bytes | Ganzzahlen Bereich mit Vorzeichen: -8.388.608 bis 8.388.607 Bereich ohne Vorzeichen: 0 bis 16.277.215 |
| INT | 4 Bytes | Ganzzahlen Bereich mit Vorzeichen: -2.147.483.648 bis 2.147.483.647 Bereich ohne Vorzeichen: 0 bis 4.294.967.295 |
| BIGINT | 8 Bytes | Ganzzahlen Bereich mit Vorzeichen: -9.223.372.036.854.775.808 bis 9.223.372.036.854.775.807 Bereich ohne Vorzeichen: 0 bis 18.446.744.073.709.551.615 |
| DECIMAL | variiert (weitere Infos) | Fixkommazahlen (M, D) Die maximale Anzahl an Ziffern (M) ist 65 (Standard: 10), die maximale Anzahl an Nachkommastellen (D) ist 30 (Standard: 0) |
| FLOAT | 4 Bytes | Fließkommazahlen Erlaubte Werte sind -3,402823466 x 1038 bis -1,175494351 x 10-38, 0 und 1,175494351 x 10-38 bis 3,402823466 x 1038 |
| DOUBLE | 8 Bytes | Fließkommazahlen Erlaubte Werte sind -1,7976931348623157 x 10308 bis -2,2250738585072014 x 10-308, 0 und 2,2250738585072014 x 10-308 bis 1,7976931348623157 x 10308 |
| REAL | 8 Bytes | Synonym für DOUBLE Ausnahme: im SQL-Modus REAL_AS_FLOAT ein Synonym für FLOAT |
| BIT | ca. (M+7)/8 Bytes | Ein Bitfeld-Typ (M), der M Bits pro Wert speichert (Standard ist 1, Maximum ist 64) |
| BOOLEAN | 1 Byte | Ein Synonym für TINYINT(1), ein Null-Wert wird als falsch angesehen, Nicht-Null-Werte werden als Wahr angesehen |
| SERIAL | 8 Bytes | Ein Alias für BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE |
| Datum und Uhrzeit | ||
| DATE | 3 Bytes | Datum Unterstützter Bereich ist 1000-01-01 bis 9999-12-31 |
| DATETIME | 8 Bytes | Kombination aus Datum und Uhrzeit Unterstützter Bereich ist 1000-01-01 00:00:00 bis 9999-12-31 23:59:59 |
| TIMESTAMP | 4 Bytes | Zeitstempel Unterstützter Bereich ist 1970-01-01 00:00:01 UTC bis 2038-01-09 03:14:07 UTC Gespeichert als Anzahl der Sekunden seit Beginn der UNIX-Epoche (1970-01-01 00:00:00 UTC) |
| TIME | 3 Bytes | Uhrzeit Unterstützter Bereich ist -838:59:59 bis 838:59:59 |
| YEAR | 1 Byte | Jahr Vier- (4, Standard) oder zweistelliges (2) Format Erlaubte Werte sind 70 (1970) bis 69 (2069) oder 1901 bis 2155 und 0000 |
| Zeichenkette | ||
| CHAR | L Byte(s) | Zeichenkette mit fester Länge (0 bis 255, Standard: 1) L Rechts wird immer bis zur angegebenen Länge mit Leerzeichen aufgefüllt |
| VARCHAR | L+1 Byte(s) | Zeichenkette mit variabler Länge (0 bis 65.535) L Die tatsächliche maximale Länge hängt von der maximalen Anzahl an Zeilen ab |
| TINYTEXT | L+1 Byte(s) | TEXT-Spalte mit einer maximalen Länge von 255 Zeichen Gespeichert mit einem Ein-Byte-Präfix, der die Länge des Wertes in Bytes angibt |
| TEXT | L+2 Byte(s) | TEXT-Spalte mit einer maximalen Länge von 65.535 Zeichen Gespeichert mit einem Zwei-Byte-Präfix, der die Länge des Wertes in Bytes angibt |
| MEDIUMTEXT | L+3 Byte(s) | TEXT-Spalte mit einer maximalen Länge von 16.777.215 Zeichen Gespeichert mit einem Drei-Byte-Präfix, der die Länge des Wertes in Bytes angibt |
| LONGTEXT | L+4 Byte(s) | TEXT-Spalte mit einer maximalen Länge von 4.294.967.295 oder 4 GiB Zeichen Gespeichert mit einem Vier-Byte-Präfix, der die Länge des Wertes in Bytes angibt |
| BINARY | L Byte(s) | Ähnlich wie der CHAR-Typ, speichert aber binäre Byte-Zeichenketten anstelle von nicht-binären Buchstaben-Zeichenketten |
| VARBINARY | L+1 Byte(s) | Ähnlich wie der VARCHAR-Typ, speichert aber binäre Byte-Zeichenketten anstelle von nicht-binären Buchstaben-Zeichenketten |
| TINYBLOB | L+1 Byte(s) | BLOB-Spalte mit einer maximalen Länge L von 255 Zeichen Gespeichert mit einem Ein-Byte-Präfix, der die Länge des Wertes in Bytes angibt |
| BLOB | L+2 Byte(s) | BLOB-Spalte mit einer maximalen Länge von 65.535 Zeichen Gespeichert mit einem Zwei-Byte-Präfix, der die Länge des Wertes in Bytes angibt |
| MEDIUMBLOB | L+3 Byte(s) | BLOB-Spalte mit einer maximalen Länge von 16.777.215 Zeichen Gespeichert mit einem Drei-Byte-Präfix, der die Länge des Wertes in Bytes angibt |
| LONGBLOB | L+4 Byte(s) | BLOB-Spalte mit einer maximalen Länge von 4.294.967.295 oder 4 GiB Zeichen Gespeichert mit einem Vier-Byte-Präfix, der die Länge des Wertes in Bytes angibt |
| ENUM | 1-2 Byte(s) | Aufzählung Gewählt aus einer Liste von bis zu 65.535 Werten |
| SET | 1, 2, 3, 4 oder 8 Byte(s) | String-Objekt Ein einzelner Wert gewählt aus einem Satz von bis zu 64 Einträgen |
| Räumlich | ||
| GEOMETRY | Typ, der die Geometrie irgendeinen Typs speichern kann | |
| POINT | Punkt im 2-dimensionalen Raum | |
| LINESTRING | Kurve mit linearer Interpolation zwischen Punkten | |
| POLYGON | Vieleck (Polygon) | |
| MULTIPOINT | Punkte-Sammlung | |
| MULTILINESTRING | Kurven-Sammlung mit linearer Interpolation zwischen Punkten | |
| MULTIPOLYGON | Polygon-Sammlung | |
| GEOMETRYCOLLECTION | Sammlung von Geometrie-Objekten irgendeinen Typs |
Spezialtyp : JSON als Datentyp beschrieben (grob) und ein Beispiel dokumentiert
Seit der Version 5.7 von MySQL können in Tabellen JSON gespeichert werden. Folgende Vorteile gibt es für den Datentypen:
- Automatische Validierung von JSON (wenn Datentyp JSON genutzt wird)
- Das JSON wird in ein internes Format konvertiert, welches einen schnellen Lesezugriff erlaubt
| id | json_data |
|---|---|
| 1 | {"name": "John Doe", "age": 35, "email": "john.doe@example.com", "phone": {"home": "555-1234", "work": "555-5678"}, "tags": ["mysql", "json"]} |
| 2 | {"name": "Jane Smith", "age": 27, "email": "jane.smith@example.com", "phone": {"home": "555-9876"}, "tags": ["mysql", "database"]} |
Das Beispiel hat eine id und einen Spalte json_data. In der json_data-Spalte wird JSON gespeichert. Somit kann die Tabelle relativ klein gehalten werden, da für z.B. name, age, etc. nicht eine eigene Spalte erstellt werden muss.
Spezialtyp : ENUM als Datentyp beschrieben (grob) und ein Beispiel dokumentiert
Mit ENUM kann eine Liste von zulässigen Werten festgelegt werden. Somit kann sichergestellt werden, dass nur bestimmte Werte möglich sind. Dies verbessert die Datenkonsistenz und -integrität.
CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
gender ENUM('Male', 'Female') NOT NULL,
PRIMARY KEY (id)
);
In dieser Tabelle ist die Spalte gender ein ENUM-Datentyp. Er legt die beiden Werte Male und Female fest. Wird jetzt per INSERT INTO versucht in die Spalte gender den Wert Animal einzufügen, wird eine Fehlermeldung ausgegeben.
Beim erstellen von einem neuen Datensatz, muss der ENUM Wert als String übergeben werden.
INSERT INTO users (name, gender) VALUES ('John Doe', 'Male');
2.1.4 Indexierung
Indexierungstypen beschrieben und Anwendung dokumentieren
Mit einem Index kann das Auffinden von Datensätzen beschleunigt werden. Es ist eine spezielle Datenstruktur in MySQL. Ohne Indexierung wird bei einem WHERE die ganze Tabelle überprüft. Das kann bei einer Tabelle mit vielen Datensätzen viel Perfomance (und Zeit) fressen. Bei der Indexierung wird neben den normalen Datensätzen eine zusätzliche Datenstruktur erstellt, diese ist nur für die Indexierung zuständig.
CREATE INDEX idx_zip_code ON customers (zip_code);
Dieses Statement erstellt einen B-Tree-Index für die Spalte zip_code in der Tabelle customers. Wird jetzt eine Abfrage auf zip_code mit WHERE gemacht, wird MySQL den Index verwenden, um den Datensatz zu finden.
-- Das ist der Index
-- \/
SELECT * FROM customers WHERE zip_code = '12345';
Folgend die verschiedenen Typen der Indexierung:
- B-Tree-Indexierung: am häufigsten verwendet; geeignet für Spalten mit begrenzten Werten (
ENUModerBOOLEAN) oder Spalten mit vielen unterschiedlichen Werten (z.B. Zeitstempel) - Hash-Indexierung: geeignet für Splaten mit vielen zufälligen Werten; können nur verwendet werden um genaue Übereinstimmungen zu suchen, da ein Hash generiert wird; wird oft für Spalten mit Text oder Binärdaten verwendet
- Volltext-Indexierung: verwendet um Wörter oder Abschnitte in einem langen Text zu suchen; beschleunigt Suchanfragen in Dokumenten oder Nachrichten
- Räumliche Indexierung: verwendet um Ortsangaben zu suchen (z.B. Koordinaten); ermöglichen MySQL schnell nach Punkten, Linien und Polygonen zu suchen
Quellen: