Einführung in gängige MySQL-Speicher-Engines sowie Parametereinstellung und -optimierung

MyISAM, eine häufig verwendete Speicher-Engine in MySQL

Merkmal:
1. Parallelität und Sperrebenen
2. Tischschäden reparieren
Tabelle Tabellenname prüfen
Reparaturtabelle Tabellenname
3. Von MyISAM-Tabellen unterstützte Indextypen ①, Volltextindex ②, Präfixindex
4. MyISAM-Tabellen unterstützen Datenkomprimierung
Abonnieren
Limit:
Die Standardtabellengröße beträgt 4G für Versionen < MySQL 5.0
Wenn der Speicher die Anforderungen erfüllt, ändern Sie MAX_Rows und AVG_ROW_LENGTH
Die Standardunterstützung für Versionen > MySQL 5.0 beträgt 256 TB

Anwendbare Szenarien:
1. Nicht-transaktionale Anwendungen
2. Nur-Lese-Anwendungen
3. Weltraumanwendungen

Innodb, eine häufig verwendete Speicher-Engine in MySQL

Eigenschaften der Innodb-Speicher-Engine
1. Innodb ist eine transaktionale Speicher-Engine
2. Vollständige Unterstützung der ACID-Funktionen von Transaktionen
3. Redo-Log und Undo-Log
4. Innodb unterstützt Zeilensperren

Innodb verwendet Tablespaces zur Datenspeicherung und erstellt für jede Tabelle einen separaten Tablespace
innodb_datei_pro_tabelle
EIN: Unabhängiger Tabellenbereich: Tabellenname.ibd
AUS: Systemtabellenbereich: ibdataX (X ist eine Zahl, die bei 1 beginnt)

So wählen und vergleichen Sie System- und unabhängige Tablespaces:
Der Systemtabellenbereich kann nicht verkleinert werden. Der unabhängige Tabellenbereich kann mit dem Befehl „Tabelle optimieren“ verkleinert werden. Der Tabellenbereich des Systemdateisystems verursacht einen E/A-Engpass. Der unabhängige Tabellenbereich kann Daten gleichzeitig in mehreren Dateien aktualisieren.

Schritte zur Tabellenübertragung:
1. Verwenden Sie mysqldump, um alle Datenbanktabellendaten zu sichern
2. Stoppen Sie den MySQL-Dienst, ändern Sie die Parameter und löschen Sie Innodb-bezogene Dateien
3. Starten Sie den MySQL-Dienst neu und erstellen Sie den Innodb-Systemtabellenbereich neu
4. Daten erneut importieren

CSV, eine gängige Speicher-Engine in MySQL

Speichereigenschaften des Dateisystems
1. Daten werden im Textformat gespeichert
2. CSV-Datei speichert Tabelleninhalte
3. Die .csm-Datei speichert Tabellenmetadaten wie Tabellenstatus und Datenvolumen
4. Die .frm-Datei speichert Informationen zur Tabellenstruktur
5. Im CSV-Format speichern
6. Keine der Spalten darf Nullwerte enthalten
7. Keine Indexunterstützung

Anwendbare Szenarien:
Geeignet als Zwischentabelle zum Datenaustausch (Tabellenkalkulation -> csv-Datei -> MySQL-Datenbankverzeichnis)

Archive, eine häufig verwendete Speicher-Engine in MySQL

Speichereigenschaften des Dateisystems

1. Komprimieren Sie Tabellendaten mit zlib, um den Festplatten-E/A zu reduzieren
2. Die Daten werden in Dateien mit der Endung ARZ gespeichert

Funktionen der Archivspeicher-Engine
1. Es werden nur Einfüge- und Auswahlvorgänge unterstützt
2. Erlauben Sie nur Indizes für automatisch inkrementierende ID-Spalten

Anwendbare Szenarien:
Protokollierungs- und Datenerfassungsanwendungen

MySQL Common Storage Engine-Speicher

Speichereigenschaften des Dateisystems
1. Es wird auch als HEAP-Speicher-Engine bezeichnet, sodass die Daten im Speicher gespeichert werden

Merkmale:
1. Unterstützt HASH-Index und Btree-Index
2. Alle Felder haben eine feste Länge varchar(10)=char(10)
3. Unterstützt keine großen Felder wie BLOG und TEXT
4. Die Speicher-Storage-Engine verwendet Sperren auf Tabellenebene
5. Die maximale Größe wird durch den Parameter max_heap_table_size bestimmt

Anwendbare Szenarien:
1. Wird zum Suchen oder Zuordnen von Tabellen verwendet, z. B. der Korrespondenztabelle zwischen Postleitzahlen und Regionen
2. Wird zum Speichern der bei der Datenablenkung generierten Zwischentabelle verwendet
3. Ergebnistabelle für das Zwischenspeichern periodischer Aggregatdaten

MySQL Common Storage Engine föderiert

Merkmale:
1. Bietet eine Methode zum Zugriff auf Tabellen auf Remote-MySQL-Servern
2. Daten werden nicht lokal gespeichert, und alle Daten werden auf dem Remote-Server abgelegt
3. Die Tabellenstruktur und die Verbindungsinformationen zum Remote-Server müssen lokal gespeichert werden

So verwenden Sie den statischen Standardzustand und aktivieren die Notwendigkeit, den föderierten Parameter beim Start zu erhöhen
mysql://Benutzername[:Passwort]@Hostname[:Port]/Datenbankname/Tabellenname

Anwendbare Szenarien:
Gelegentliche statistische Analysen und manuelle Abfragen

So wählen Sie die richtige Speicher-Engine aus

Referenzbedingungen
1. Ob Transaktionen unterstützt werden sollen
2. Regelmäßiges Backup
3. Wiederherstellung nach einem Absturz
4. Einzigartige Funktionen von Speicher-Engines

Einführung in Mysql-Serverparameter

MySQL erhält den Konfigurationsinformationspfad

1. Kommandozeilenparameter

mysqld_safe --datadir=/data/sql_data

2. Konfigurationsdatei

Zeigen Sie den Befehl zur Konfigurationsdatei an:
[root@localhost ~]# mysqld --help --verbose | egrep -A 1 'Standardoptionen'
Ein gültiger Pfad zu einer Konfigurationsdatei
/etc/meine.cnf /etc/mysql/meine.cnf /usr/etc/meine.cnf ~/.meine.cnf

Umfang der MySQL-Konfigurationsparameter

1. Globale Parameter
globalen Parameternamen = Parameterwert festlegen;
setze @@global.parameter name:=Parameterwert;
2. Sitzungsparameter
setze [Sitzung] Parametername = Parameterwert;
setze @@session.parameter name:=Parameterwert;

Speicherkonfigurationsbezogene Parameter
1. Bestimmen Sie die Obergrenze des verfügbaren Speichers
2. Bestimmen Sie den von jeder MySQL-Verbindung verwendeten Speicher
Sortierpuffergröße
Verbindungspuffergröße
Puffergröße lesen
Puffergröße lesen
3. Bestimmen Sie, wie viel Speicher Sie für das Betriebssystem reservieren müssen
4. So weisen Sie Speicher für den Cache-Pool zu
Innodb_buffer_pool_size
Hinweis: Die Kriterien für die Festlegung der Größe des Cache-Pools sind: Gesamtspeicher - (für jedes Programm erforderlicher Speicher * Anzahl der Verbindungen) - vom System reservierter Speicher
Schlüsselpuffergröße
Wählen Sie Summe(Indexlänge) aus Information_Schema.Tabellen, wobei Engines = "myisam" sind.

E/A-bezogene Konfigurationsparameter
Innodo I/O-bezogene Konfiguration
Innodb_log_file_size Die Größe eines einzelnen Transaktionsprotokolls
Innodb_log_files_in_group steuert die Anzahl der Dateitage. Gesamtgröße des Transaktionsprotokolls = Innodb_log_files_in_group * Innodb_log_file_size
Innodb_log_buffer_size = (32 M oder 128 M)
Innodb_flush_log_at_trx_commint
0: Das Protokoll wird einmal pro Sekunde in den Cache geschrieben und auf die Festplatte geschrieben.
1 [Standard]: Protokoll in den Cache schreiben und bei jedem Transaktions-Commit auf die Festplatte übertragen
2[Vorschlag]: Bei jedem Festschreiben einer Transaktion werden die Protokolldaten in den Cache geschrieben und das Protokoll einmal pro Sekunde auf die Festplatte geschrieben.
Innodb_flush_method=O_DIRECT
Innodb_file_per_table = 1
Innodb_doublewrite = 1

MyISAM I/O-bezogene Konfiguration
Verzögerung_Schlüssel_Schreiben
AUS: Nach jedem Schreibvorgang werden verschmutzte Blöcke im Schlüsselpuffer auf die Festplatte geschrieben.
EIN: Die verzögerte Aktualisierung wird nur für Tabellen verwendet, für die beim Eingeben der Kennung für die Tabelle die Option „delay_key_write“ angegeben wurde.
ALL: Verzögertes Schreiben für alle MyISAM-Tabellen verwenden

Sicherheitsrelevante Konfigurationsparameter
expire_logs_days gibt die Anzahl der Tage an, nach denen das Binärprotokoll automatisch bereinigt werden soll
max_allowed_packet steuert die Paketgröße, mit der MySQL eine Verbindung herstellen kann. Es wird empfohlen, den Wert auf 32M einzustellen. Wenn Master-Slave-Replikation verwendet wird, sollte der Parameter auf den gleichen Wert eingestellt werden.
skip_name_resolve deaktiviert DNS-Lookups
sysdate_is_now Stellt sicher, dass sysdate() ein gültiges Datum zurückgibt
read_only verbietet Schreibvorgänge von Benutzern ohne Superberechtigungen. Hinweis: Es wird empfohlen, diese Funktion in der Slave-Datenbank bei der Master-Slave-Replikation zu aktivieren. Um sicherzustellen, dass die Vorgänge in der Slave-Datenbank nicht geändert werden können, können sie nur von der Master-Datenbank synchronisiert werden
skip_slave_start deaktiviert die automatische Wiederherstellung des Slaves (wird aus den Einstellungen in der Bibliothek verwendet)
sql_mode legt den von MySQL verwendeten SQL-Modus fest (Vorsicht, dies kann dazu führen, dass MySQL nicht ausgeführt wird).
① strict_trans_tables Wenn die angegebenen Daten nicht in die Datenbank eingefügt werden können, beendet die Transaktions-Engine den Vorgang. Dies hat keine Auswirkungen auf die Nicht-Transaktions-Engine. ② no_engine_subitiution Wenn beim Angeben von Engines bei „Tabelle erstellen“ die Engine nicht verfügbar ist, wird die Standard-Engine nicht zum Erstellen der Tabelle verwendet. ③ no_zero_date Das Datum mit 0 Jahren, 0 Monaten und 0 Tagen kann nicht in die Tabelle eingefügt werden. ④ no_zero_in_date Akzeptiert kein Datum mit einem Teilwert von 0. ⑤ noly_full_group_by

Andere häufig verwendete Konfigurationsparameter
sync_binlog steuert, wie MySQL das Binärprotokoll auf die Festplatte schreibt.
tmp_table_size und max_heap_table_size steuern die Größe der temporären Speichertabellen (stellen Sie sie nicht zu groß ein, um einen Speicherüberlauf zu vermeiden)
max_connections steuert die maximal zulässige Anzahl von Verbindungen (der Standardwert ist 100, was etwas wenig ist; passen Sie die Größe entsprechend Ihrem Unternehmen an).

Was beeinflusst die Leistung?

Der Einfluss des Datenbankdesigns auf die Leistung
1. Übermäßige Denormalisierung erzeugt zu viele Spalten für die Tabelle
2. Übermäßige Normalisierung führt zu zu vielen Tabellenzuordnungen (die Anzahl der zugeordneten Tabellen sollte möglichst auf 10 begrenzt werden).
3. Verwenden von Partitionstabellen ohne Präfix in OLTP-Umgebungen
4. Verwenden Sie Fremdschlüssel, um die Datenintegrität sicherzustellen

Zusammenfassen

Reihenfolge der Leistungsoptimierung
1. Datenbankstrukturdesign und SQL-Anweisungen
2. Konfiguration der Auswahlparameter der Datenbankspeicher-Engine
3. Systemauswahl und -optimierung
4. Hardware-Upgrade