Lernen Sie MySQL auf einfache Weise

Vorwort

Die Datenbank war schon immer meine Schwachstelle. Ich werde meine eigenen Erfahrungen (Python + SQLAlchemy) kombinieren und eine Aufzeichnung machen. Ich bevorzuge die Verwendung von ORM. Ich habe immer das Gefühl, dass die Rechtschreibung von SQL eine schmerzhafte Sache ist (hauptsächlich, weil ich nicht gut in SQL bin). Ich bin in früheren Wartungsprojekten auch auf einige Datenbankfallen gestoßen, wie z. B. Kodierungsprobleme, Verlust der Gleitkommagenauigkeit usw., um zu verhindern, dass sich die Fallstricke in Zukunft wiederholen.

Kapitel 1: Verwenden der Hilfe

Verwenden Sie den in MySQL integrierten Hilfebefehl

1. msyql> ? data types
2. mysql> ? int
3. mysql> ? create table
   

Kapitel 2: Auswahl des Tabellentyps (Speicher-Engine)

Die beiden am häufigsten verwendeten Engines sind:

1. Myisam ist die Standardspeicher-Engine von MySQL. Wenn create eine neue Tabelle erstellt und die Speicher-Engine der neuen Tabelle nicht angibt, wird standardmäßig Myisam verwendet. Jedes MyISAM wird in drei Dateien auf der Festplatte gespeichert. Die Dateinamen sind dieselben wie die Tabellennamen und die Erweiterungen sind .frm (zum Speichern der Tabellendefinition), .MYD (MYData, zum Speichern von Daten) und .MYI (MYIndex, zum Speichern von Indizes). Datendateien und Indexdateien können in verschiedenen Verzeichnissen abgelegt werden, um die E/A gleichmäßig zu verteilen und eine höhere Geschwindigkeit zu erreichen.

2. Die InnoDB-Speicher-Engine bietet Transaktionssicherheit mit Commit-, Rollback- und Crash-Recovery-Funktionen. Im Vergleich zur MyISAM-Speicher-Engine schreibt InnoDB jedoch weniger effizient und benötigt mehr Speicherplatz, um Daten und Indizes beizubehalten.

Gemeinsame Umgebung:

1. MyISAM: Die standardmäßige MySQL-Plug-In-Speicher-Engine, die eine der am häufigsten verwendeten Speicher-Engines im Web, im Data Warehousing und in anderen Anwendungsumgebungen ist

2. InnoDB: Wird für Anwendungen zur Transaktionsverarbeitung verwendet und verfügt über viele Funktionen, einschließlich ACID-Transaktionsunterstützung.

Kapitel 3: Den richtigen Datentyp auswählen

Wählen Sie zunächst die entsprechende Speicher-Engine aus und bestimmen Sie den entsprechenden Datentyp basierend auf der angegebenen Speicher-Engine.

• MyISAM: Es ist am besten, Datenspalten mit fester Länge anstelle von Datenspalten mit variabler Länge zu verwenden.
• InnoDB: varchar wird empfohlen
  

Einige zu beachtende Datentypen:

1. char und varchar: Die Speicher- und Abrufmethoden sind unterschiedlich, und auch die maximale Länge und die Frage, ob der nachfolgende Leerraum beibehalten wird, sind unterschiedlich. char hat eine feste Länge. Wenn die Länge nicht ausreicht, wird es mit Leerzeichen aufgefüllt. Wenn PAD_CHAR_TO_FULL_LENGTH beim Abrufen nicht festgelegt ist, werden die nachstehenden Leerzeichen standardmäßig entfernt.
varchar-String mit variabler Länge, nachstehende Leerzeichen bleiben beim Abruf erhalten. Beachten Sie, dass die Abfrage nicht zwischen Groß- und Kleinschreibung unterscheidet. Wenn Sie sqlalchemy verwenden, um zwischen Groß- und Kleinschreibung zu unterscheiden, verwenden Sie nicht func.binary .

2. Text und blob: text und Blob in großen Mengen aktualisiert oder gelöscht werden, bleiben große „Löcher“ zurück. Es wird empfohlen, die Funktion OPTIMIZE TABLE zu verwenden, um solche Tabellen regelmäßig zu defragmentieren. Vermeiden Sie das Abrufen großer Blob- oder Textwerte. Trennen Sie Text- und Blob-Spalten in separate Tabellen.

3. Gleitkommazahl Float und Festkommazahl Dezimal:

Beachten Sie einige Punkte:

1. Obwohl Gleitkommazahlen einen größeren Datenbereich darstellen können, weisen sie Fehlerprobleme auf.

2. Für Probleme, bei denen es auf Genauigkeit ankommt, wie z. B. Währungen, sollte Festkommaspeicher verwendet werden. Ich bin bei früheren Projekten auf einige Probleme gestoßen und musste sie schließlich durch Vergrößern und Verkleinern lösen, was unschön war.

3. Wenn Sie beim Programmieren auf Gleitkommazahlen stoßen, achten Sie auf das Fehlerproblem und versuchen Sie, Gleitkommavergleiche zu vermeiden (beim Vergleich von Gleitkommazahlen muss der Unterschied kleiner als eine bestimmte Genauigkeit sein). In Python 3.5 können Sie folgendermaßen vergleichen: float_eq = partial(math.isclose, rel_tol=1e-09, abs_tol=0.0)

4. Achten Sie auf die Verarbeitung einiger Sonderwerte in Gleitkommazahlen.

Kapitel 4: Zeichensätze

Es ist wichtig, gleich zu Beginn den richtigen Zeichensatz auszuwählen, da eine spätere Änderung sonst sehr aufwändig wird. Der Zeichensatz ist ein seit langem bestehendes Problem in Python 2, das viele Neulinge verwirrt. Das Projekt, das ich vorher betreut habe, verwendete den Standard-Latin1-Zeichensatz von msyql, was dazu führte, dass die Zeichenfolge bei jedem Schreiben manuell in UTF-8 kodiert werden musste. Ich habe kürzlich bei der Durchführung von Projekten Python3.5+Flask verwendet und dabei direkt UTF-8 verwendet. Seitdem hatte ich nie wieder Probleme mit der Kodierung:

• Erstellen Sie eine Datenbank mit UTF-8, CREATE DATABASE IF NOT EXISTS my_db Standardzeichensatz UTF-8 COLLATE utf8_general_ci;
• Die SQLAlchemy-Verbindungs-URL verwendet mysql://root:[email protected]:3306/my_db?charset=utf8. Machen Sie sich wegen des verstümmelten Codes keine Sorgen.
  

Kapitel 5: Entwurf und Verwendung von Indizes

Alle MySQL-Spaltentypen können indiziert werden und die Verwendung von Indizes für relevante Spalten ist die beste Möglichkeit, die Leistung von Auswahlvorgängen zu verbessern. Grundsätze der Indexgestaltung:

1. Die durchsuchte Indexspalte ist nicht notwendigerweise die auszuwählende Spalte. Für die Indizierung sind die Spalten am besten geeignet, die in der Where-Klausel erscheinen oder in einer Join-Klausel angegeben sind, und nicht die Spalten, die in der Auswahlliste nach dem Select-Schlüsselwort erscheinen.

2. Verwenden Sie einen eindeutigen Index. Die Indizierung funktioniert gut bei Spalten mit eindeutigen Werten, jedoch schlecht bei Spalten mit mehreren doppelten Werten.

3. Verwenden Sie kurze Indizes. Wenn Sie eine Zeichenfolgenspalte indizieren, sollten Sie nach Möglichkeit eine Präfixlänge angeben.

4. Verwenden Sie das Präfix ganz links. Wenn Sie einen n-Spalten-Index erstellen, erstellen Sie tatsächlich n Indizes, die MySQL verwenden kann. Ein mehrspaltiger Index kann als mehrere Indizes fungieren, da die äußerste linke Spaltengruppe im Index zum Abgleichen von Zeilen verwendet werden kann; eine solche Spaltengruppe wird zu einem äußersten linken Präfix.

5. Überindizieren Sie nicht. Indizes verschwenden Speicherplatz und verringern die Schreibleistung.

6. Berücksichtigen Sie die Arten der Vergleiche, die an den Spalten durchgeführt werden.

Kapitel 6: Sperrmechanismus und Transaktionskontrolle

Die InnoDB-Engine bietet Sperren auf Zeilenebene, unterstützt zwei Sperrmodi: gemeinsame Sperren und exklusive Sperren sowie vier verschiedene Isolationsebenen. MySQL unterstützt lokale Transaktionen durch Anweisungen wie AUTOCOMIT, START TRANSACTIONS, COMMIT und ROLLBACK.

Kapitel 7: Sicherheitsprobleme in SQL

SQL-Injection: Nutzt die externe Schnittstelle einiger Datenbanken, um Benutzerdaten in die eigentliche Datenbankbetriebssprache (SQL) einzufügen und so in die Datenbank oder sogar das Betriebssystem einzudringen. Der Hauptgrund ist, dass das Programm die vom Benutzer eingegebenen Daten nicht streng filtert, was zur Ausführung illegaler Datenbankabfrageanweisungen führt. Vorbeugende Maßnahmen:

1. prepareStatement = Bind-variable , kein verknüpftes SQL verwenden
2. Verwenden von anwendungsseitig bereitgestellten Konvertierungsfunktionen
3. Benutzerdefinierte Funktionsvalidierung (Formularvalidierung usw.)
   

Kapitel 8: SQL-Modus und damit verbundene Probleme

Ändern Sie den standardmäßigen MySQL-Ausführungsmodus. Wenn beispielsweise ein Einfügen oder Aktualisieren im strikten Modus falsch ist, gibt MySQL einen Fehler aus und bricht den Vorgang ab. set session sql_mode='STRICT_TRANS_TABLES' . Das Festlegen des SQL-Modus erfordert, dass das Anwendungspersonal verschiedene Vor- und Nachteile abwägt und eine geeignete Wahl trifft.

Kapitel 9: Allgemeine SQL-Kenntnisse

1. Rufen Sie die Zeile ab, die den Maximal-/Minimalwert enthält: MAX([DISTINCE] expr), MIN([DISTINCE] expr)
2. So verwenden Sie rand()/rand(n) zum Extrahieren zufälliger Zeilen
3. Verwenden Sie group by und with rollup -Klauseln, um Statistiken zu erstellen
4. bit group functions für Statistiken nutzen
   

Kapitel 10: Weitere Punkte, die Aufmerksamkeit erfordern

Groß-/Kleinschreibung von Datenbank- und Tabellennamen: Unterschiedliche Plattformen und Systeme berücksichtigen unterschiedliche Groß-/Kleinschreibung. Es wird empfohlen, Namen immer in Kleinbuchstaben zu verwenden.
Bei der Verwendung von Fremdschlüsseln ist Folgendes zu beachten: InnoDB in MySQL unterstützt die Überprüfung externer Schlüsselworteinschränkungen.

Kapitel 11: SQL-Optimierung

Allgemeine Schritte zur Optimierung von SQL:

1. Verwenden Sie die Statusanzeige und die Anwendungsmerkmale, um die Ausführungshäufigkeit verschiedener SQL-Anweisungen und das ungefähre Ausführungsverhältnis verschiedener SQL-Anweisungen zu verstehen. Beispielsweise fragt der InnoDB-Parameter Innode_rows_read die Anzahl der zurückgegebenen Zeilen ab, Innodb_rows_inserted führt die Anzahl der durch Einfügen eingefügten Zeilen aus und Innodb_rows_updated führt die Anzahl der aktualisierten Zeilen aus. Darüber hinaus gibt es mehrere Parameter: Verbindungen: Verbindungsversuche mit dem MySQL-Server, Uptime-Server-Arbeitszeit, Slow_queries: Anzahl langsamer Abfragen.

2. Suchen Sie SQL-Anweisungen mit geringer Ausführungseffizienz. Es gibt zwei Möglichkeiten: Eine besteht darin, Anweisungen mit geringer Ausführungseffizienz über das langsame Abfrageprotokoll zu lokalisieren. Wenn mysqld mit der Option --log-slow-queries[=file_name] gestartet wird, schreibt es eine Protokolldatei mit allen SQL-Anweisungen, deren Ausführungszeit long_query_time Sekunden überschreitet. Die andere Möglichkeit besteht darin, mit „show processlist“ die aktuellen Threads in MySQL anzuzeigen, einschließlich des Thread-Status, ob die Tabelle gesperrt ist usw. Sie können den SQL-Ausführungsstatus in Echtzeit anzeigen und einige Tabellensperrvorgänge optimieren.

3. Analysieren Sie den Ausführungsplan von ineffizientem SQL mithilfe von EXPLAIN: EXPLAIN kann Ihnen sagen, wann Sie die Tabelle indizieren müssen, um ein schnelleres SELECT zu erhalten, das den Index zum Suchen von Datensätzen verwendet. Im Folgenden finden Sie eine Erklärung der Ergebnisse, die nach der Ausführung von EXPLAIN erzielt werden:

• select_type: Typ auswählen
• Tabelle: Tabelle des Ausgabeergebnissatzes
• Typ: Gibt den Verbindungstyp der Tabelle an. Wenn die Tabelle nur eine Zeile enthält und der Typwert „System“ ist, ist dies der beste Verbindungstyp. Wenn bei einer Auswahloperation ein Index zum Verbinden von Tabellen verwendet wird, ist der Typwert „Ref“. Wenn bei der ausgewählten Tabellenverbindung kein Index verwendet wird, wird der Typwert häufig als „ALL“ angezeigt, was darauf hinweist, dass die Tabelle vollständig gescannt wurde. In diesem Fall müssen Sie die Effizienz der Tabellenverbindung durch Erstellen eines Indexes verbessern.
• Possible_keys: Gibt die Indexspalten an, die bei der Abfrage verwendet werden können.
• Schlüssel: gibt den zu verwendenden Index an
• key_len: Indexlänge
• Zeilen: Scanbereich
• Extra: Hinweise und Beschreibung der Umsetzung

4. Identifizieren Sie das Problem und ergreifen Sie entsprechende Optimierungsmaßnahmen.

Indizierungsprobleme

1. Klassifizierung der Indexspeicherung: Die Datendateien und Indexdateien der Myisam-Tabelle werden automatisch getrennt und die Daten und Indizes von Innodb werden im selben Tabellenbereich abgelegt. Der Indexspeichertyp von MyISAM und InnoDB ist btree
2. So verwendet MySQL Indizes: Indizes werden verwendet, um schnell Zeilen mit einem bestimmten Wert in einer Spalte zu finden. Die wichtigste Voraussetzung für die Verwendung eines Index in einer Abfrage ist die Verwendung des Indexschlüsselworts in der Abfragebedingung. Handelt es sich um einen mehrspaltigen Index, kann der Index nur verwendet werden, wenn das am weitesten links stehende Präfix des mehrspaltigen Schlüsselworts in der Abfragebedingung verwendet wird. Andernfalls kann der Index nicht verwendet werden.
3. Überprüfen Sie die Verwendung des Index: Der Wert von Handler_read_key gibt an, wie oft eine Zeile indiziert wird. Ein niedriger Wert bedeutet, dass der Index nicht häufig verwendet wird. Ein hoher Handler_read_rnd_next-Wert bedeutet, dass Abfragen ineffizient ausgeführt werden. Dies sollte durch die Erstellung von Indizes behoben werden. show status like 'Handler_read%';

Zwei einfache und praktische Optimierungsmethoden

• Periodische Analysetabellen: ANALYSETABELLE, PRÜFTABELLE, PRÜFSUMMENTABELLE
• Verwenden Sie die OPTIMIZE-Tabelle.
  

Optimierung aus der Sicht des Clients (Codeseite)

1. Verwenden Sie dauerhafte Verbindungen zur Datenbank, um Verbindungs-Overhead zu vermeiden. Im Code verwenden wir im Allgemeinen den Verbindungspool
2. Überprüfen Sie, ob alle Zwischenspiele die erforderlichen Indizes verwenden.
3. Vermeiden Sie die Ausführung komplexer Auswahlabfragen bei häufig aktualisierten Tabellen, um Probleme im Zusammenhang mit dem Sperren von Tabellen aufgrund von Lese-/Schreibkonflikten zu vermeiden.
4. Machen Sie sich Standardwerte zunutze und fügen Sie Werte nur dann explizit ein, wenn sie vom Standard abweichen. Dadurch wird der Umfang der von MySQL durchzuführenden Syntaxanalyse verringert und die Einfügegeschwindigkeit erhöht.
5. Trennung von Lesen und Schreiben verbessert die Leistung
6. Versuchen Sie, in Tabellenfeldern keine Autoinkrementvariablen zu verwenden, um zu verhindern, dass das Autoinkrement des Felds die Effizienz in Situationen mit hoher Parallelität beeinträchtigt. Es wird empfohlen, das Autoinkrement von Feldern über Anwendungen zu implementieren.

Kapitel 12: Optimieren von Datenbankobjekten

Tabellendatentyp optimieren: PROCEDURE ANALYZE() macht Optimierungsvorschläge auf Basis des aktuellen Tabellentyps. In der Praxis können statistische Informationen mit tatsächlicher Anwendungsoptimierung kombiniert werden.

Verbessern Sie die Effizienz des Tabellenzugriffs durch Aufteilen: Das Aufteilen hier gilt hauptsächlich für Tabellen vom Typ MyISAM.

• Vertikale Aufteilung: Teilen Sie die häufig und selten aufgerufenen Felder in der Tabelle entsprechend der Häufigkeit des Anwendungszugriffs in zwei Tabellen auf. Häufig aufgerufene Felder sollten möglichst eine feste Länge haben.
• Horizontale Aufteilung: Je nach Anwendungssituation können die Daten horizontal in mehrere Tabellen aufgeteilt oder durch Partitionierung in mehrere Partitionen unterteilt werden. Dadurch können die durch das Lesen und Aktualisieren der MyISAM-Tabelle verursachten Sperrprobleme effektiv vermieden werden.
  

Denormalisierung: Das Normalisierungsdesign betont die Unabhängigkeit und minimiert die Datenredundanz. Mehr Redundanz bedeutet, dass mehr physischer Speicherplatz belegt wird, und bringt auch Probleme bei der Datenwartung und Konsistenzprüfung mit sich. Durch entsprechende Redundanz können Sie den Zugriff auf mehrere Tabellen reduzieren und die Abfrageeffizienz deutlich verbessern. In diesem Fall können Sie eine Verbesserung der Effizienz durch entsprechende Redundanz in Betracht ziehen.

Redundante Statistiktabellen verwenden: Verwenden Sie create temporary table für die statistische Analyse

Wählen Sie einen geeigneteren Tabellentyp: 1. Wenn die Anwendung schwerwiegende Sperrkonflikte aufweist, überlegen Sie, ob Sie die Speicher-Engine absichtlich auf InnoDB umstellen sollten. Der Zeilensperrmechanismus kann das Auftreten von Sperrkonflikten wirksam reduzieren. 2. Wenn die Anwendung viele Abfragevorgänge hat und keine strengen Anforderungen an die Transaktionsintegrität stellt, können Sie die Verwendung von Myisam in Betracht ziehen.

Kapitel 13: Sperrprobleme

Warten auf Sperren: Statusvariablen „table_locks_waited“ und „table_locks_immediate“ zur Analyse von Tabellensperrenkonflikten im System. Überprüfen Sie Innode_row_lock, um Zeilensperrenkonflikte zu analysieren.

Kapitel 14: Optimierung des MySQL-Servers

Zeigen Sie die aktuellen Parameter des MySQL-Servers an

1. Zeigen Sie die Standardwerte der Serverparameter an: mysqld --verbose --help
2. So zeigen Sie die tatsächlichen Werte der Serverparameter an: shell> mysqladmin variables or mysql> SHOW VARIABLES
3. Zeigen Sie den Statuswert des Servers an: mysqladmin extended-status or mysql>SHOW STATUS
   

Wichtige Parameter, die die MySQL-Leistung beeinflussen

1. key_buffer_size: Schlüsselcache
2. table_cache: Die Anzahl der in der Datenbank geöffneten Caches
3. innode_buffer_pool_size: Die Größe des Speicherpuffers zum Zwischenspeichern von InnoDB-Daten und -Indizes
4. innodb_flush_log_at_trx_commit: Es wird empfohlen, den Wert auf 1 zu setzen. Wenn jede Transaktion festgeschrieben wird, wird der Protokollpuffer in die Protokolldatei geschrieben und die Protokolldatei für Festplattenvorgänge aktualisiert.

Kapitel 15: E/A-Probleme

Suchvorgänge auf der Festplatte stellen einen enormen Leistungsengpass dar.

1. Verteilen Sie I/O mithilfe von Disk-Arrays oder virtuellen Datei-Volumes
2. Verteilen von E/A mithilfe symbolischer Links
   

Kapitel 16: Anwendungsoptimierung

1. Verbindungspool verwenden: Das Herstellen einer Verbindung ist relativ kostspielig und das Einrichten eines Verbindungspools kann die Zugriffsleistung verbessern.
2. Reduzieren Sie den Zugriff auf MySQL: 1. Vermeiden Sie das wiederholte Abrufen derselben Daten. 2 Verwenden des MySQL-Abfragecaches
3. Cache-Ebene hinzufügen
4. Lastausgleich: 1. Verwenden Sie MySQL, um Abfragevorgänge zu replizieren und zu verteilen. 2 Verteilte Datenbankarchitektur

Zusammenfassen

Das Obige ist der relevante Inhalt zu MySQL. Ich hoffe, dass der Inhalt dieses Artikels jedem beim Erlernen oder Verwenden von MySQL eine Hilfe sein kann. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen.