Vorwort
Die Datenbank war schon immer eine Schwachstelle des Autors (Python+SQLalchemy) usw. Ich werde lieber ORM verwenden, aber ich hatte immer das Gefühl, dass die Formulierung von SQL eine relativ mühsame Sache ist (hauptsächlich, weil ich in früheren Wartungsprojekten nicht gut mit SQL vertraut bin). B. Codierungsprobleme, Verlust der Gleitkommagenauigkeit usw., um dies zu verhindern Wiederholen Sie die Falle in Zukunft.
Kapitel 1: Verwenden der Hilfe
Verwenden des in MySQL integrierten Hilfebefehls
msyql> ? data types : 查看数据类型
mysql> ? int
mysql> ? create table
Nach dem Login kopieren
Kapitel 2: Auswahl des Tabellentyps (Speicher-Engine)
Die beiden am häufigsten verwendeten Motoren:
1. Myisam ist die Standardspeicher-Engine von MySQL. Wenn beim Erstellen eine neue Tabelle erstellt wird und die Speicher-Engine der neuen Tabelle nicht angegeben wird, wird Myisam standardmäßig verwendet. Jedes MyISAM wird als drei Dateien auf der Festplatte gespeichert. Die Dateinamen sind mit den Tabellennamen identisch und die Erweiterungen sind .frm (Speichertabellendefinition), .MYD (MYData, Speicherdaten), .MYI (MYIndex, Speicher Index ). Datendateien und Indexdateien können in verschiedenen Verzeichnissen abgelegt werden, um E/A gleichmäßig zu verteilen und schnellere Geschwindigkeiten zu erzielen.
2. Die InnoDB-Speicher-Engine bietet Transaktions-Sicherheit mit Commit-, Rollback- und Crash-Recovery-Funktionen. Im Vergleich zur Speicher-Engine von Myisam ist die Schreibverarbeitungseffizienz von InnoDB jedoch weniger effizient und benötigt mehr Speicherplatz für die Aufbewahrung von Daten und Indizes.
Gemeinsame Umgebungen:
1. Die standardmäßige MySQL-Plug-in-Speicher-Engine, die am häufigsten in Web-, Data Warehousing- und anderen Anwendungsumgebungen verwendet wird
Eine der verwendeten Speicher-Engines
2. InnoDB: wird für Transaktionsverarbeitungsanwendungen verwendet, mit vielen Funktionen, einschließlich ACID-Transaktionsunterstützung.
Kapitel 3: Wählen Sie den geeigneten Datentyp
Wählen Sie zunächst die entsprechende Speicher-Engine basierend auf dem angegebenen Speicher aus Engine Bestimmen Sie den geeigneten Datentyp.
MyISAM: Es ist besser, Datenspalten fester Länge anstelle von Datenspalten variabler Länge zu verwenden.
InnoDB: Es wird empfohlen, Varchar zu verwenden
Einige zu beachtende Datentypen:
1. Char und Varchar: Die Speicher- und Abrufmethoden sind unterschiedlich, und auch die maximale Länge und die Beibehaltung nachfolgender Leerzeichen sind unterschiedlich. char hat eine feste Länge. Wenn die Länge nicht ausreicht, füllen Sie sie mit Leerzeichen. Wenn PAD_CHAR_TO_FULL_LENGTH beim Abrufen nicht festgelegt ist, werden nachfolgende Leerzeichen standardmäßig entfernt.
varchar variable lengthstring, nachfolgende Leerzeichen werden beim Abruf beibehalten. Beachten Sie, dass bei der Abfrage die Groß-/Kleinschreibung nicht beachtet wird, verwenden Sie nicht die Funktion func.binary.
2. Wenn Text, blob: text und Blob eine große Anzahl von Aktualisierungen oder Löschungen durchführen, bleiben große „Lücken“ zurück. Es wird empfohlen, regelmäßig die Funktion OPTIMIZE TABLE zu verwenden, um solche Tabellen zu defragmentieren. Vermeiden Sie das Abrufen großer Blob- oder Textwerte. Trennen Sie Text- und Blobspalten in separate Tabellen.
3. Gleitkommazahlen und Festkommazahlen:
Beachten Sie einige Punkte:
1. Gleitkommazahlen können jedoch einen größeren Datenbereich darstellen , aber es liegt ein Fehlerproblem vor.
2. Für präzisionsempfindliche Themen wie Währungen sollte Festkommaspeicher verwendet werden. In früheren Projekten gab es Fallstricke, und das Ergebnis war, dass wir eine Vergrößerungs- und Verkleinerungsmethode anwenden mussten, um das Problem zu lösen, was ziemlich hässlich war.
3.ProgrammierungWenn Sie auf Gleitkommazahlen stoßen, achten Sie auf Fehlerprobleme und versuchen Sie, Gleitkommavergleiche zu vermeiden (der Vergleich von Gleitkommazahlen erfordert eine Differenz, die kleiner als eine bestimmte Genauigkeit ist). ), in Python3.5 Es kann wie folgt verglichen werden: 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: Zeichensatz
Um zu Beginn den passenden Zeichensatz auszuwählen , Andernfalls wird ein späterer Austausch sehr teuer. Der Zeichensatz in Python2 ist ein seit langem bestehendes Problem, das viele Anfänger verwirrt. Das zuvor gepflegte Projekt verwendete den Standardzeichensatz latin1 von msyql, was dazu führte, dass die Zeichenfolge bei jedem Schreiben manuell in utf8 codiert werden musste. Kürzlich habe ich utf8 für Projekte mit Python3.5+flask verwendet und bin nie wieder auf Kodierungsprobleme gestoßen:
Datenbank erstellen Utf8 verwenden, DATENBANK ERSTELLEN, WENN NICHT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
sqlalchemy-Verbindungs-URL verwendet mysql://root:root@127.0.0.1:3306/my_db?charset=utf8. Sie müssen sich keine Sorgen mehr über verstümmelte Zeichen machen
Kapitel 5: Design und Verwendung des Index
Alle MySQL-Spaltentypen können indiziert werden. Die Verwendung von Indizes für verwandte Spalten ist die beste Möglichkeit, die Leistung ausgewählter Vorgänge zu verbessern. Prinzipien des Indexdesigns:
1. Die durchsuchte Indexspalte ist nicht unbedingt die auszuwählende Spalte. Die am besten geeigneten Spalten für den Index sind die Spalten, die in der Where-Klausel erscheinen, oder die in der Join-Klausel angegebenen Spalten und nicht die Spalten, die in der Auswahlliste nach dem Schlüsselwort select erscheinen.
2. Verwenden Sie einen eindeutigen Index. Bei Spalten mit eindeutigen Werten ist der Indexierungseffekt besser, bei Spalten mit mehreren doppelten Werten ist der Indexierungseffekt jedoch schlecht.
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. Beim Erstellen eines n-spaltigen Index werden tatsächlich n Indizes erstellt, die für MySQL verfügbar sind. Ein mehrspaltiger Index kann als mehrere Indizes fungieren, da Zeilen mithilfe des Spaltensatzes ganz links im Index abgeglichen werden können, der zum Präfix ganz links wird.
5. Überindizieren Sie nicht. Indizes verschwenden Speicherplatz und verringern die Schreibleistung.
6. Überlegen Sie, welche Arten von Vergleichen für Spalten durchgeführt werden sollen.
Kapitel 6: Sperrmechanismus und Transaktionssteuerung
Die InnoDB-Engine bietet Sperren auf Zeilenebene und unterstützt gemeinsame Sperren und zwei Sperrmodi für exklusive Sperren und vier verschiedene Isolationsstufen. MySQL unterstützt lokale Transaktionen durch Anweisungen wie AUTOCOMIT, START TRANSACTIONS, COMMIT und ROLLBACK.
Kapitel 7: Sicherheitsprobleme in SQL
SQL-Injection: Ausnutzung der externen Schnittstelle einiger Datenbanken Fügen Sie Benutzerdaten in die eigentliche DatenbankoperationSprache (SQL) ein, um den Zweck des Eindringens in die Datenbank oder sogar in das Betriebssystem zu erreichen. Der Hauptgrund ist, dass die vom Programm-Heap-Benutzer eingegebenen Daten nicht streng gefiltert werden, was zur Ausführung illegaler Datenbankabfrageanweisungen führt:
prepareStatement = Bind-variable, Verwenden Sie kein Splicing-SQL
Verwenden Sie die von der Anwendung bereitgestellte Konvertierungsfunktion
-
Benutzerdefinierte Funktionsüberprüfung (Formularüberprüfung usw.)
Kapitel 8: SQL-Modus und verwandte Probleme
Ändern Sie den Standard-MySQL-Ausführungsmodus, z der folgende strenge Modus Wenn das Einfügen oder Aktualisieren falsch ist, gibt MySQL einen Fehler aus und gibt den Vorgang auf. set session sql_mode='STRICT_TRANS_TABLES'. Das Festlegen von sql_mode erfordert, dass das Anwendungspersonal verschiedene Gewinne und Verluste abwägt und eine geeignete Wahl trifft.
Kapitel 9: Allgemeine SQL-Tipps
Zeilen mit Maximal-/Minimalwerten abrufen: MAX([DISTINCE] expr), MIN([DISTINCE] expr)
Verwenden Sie rand()/rand(n) geschickt, um zufällige Zeilen zu extrahieren
Verwenden Sie die Klauseln group by und with rollup, um Statistiken zu erstellen
Verwenden Sie bit group functions, um Statistiken zu erstellen
Kapitel 10: Andere Themen, die Aufmerksamkeit erfordern
Probleme mit der Groß-/Kleinschreibung von Datenbank- und Tabellennamen: Unterschiedliche Plattformen und Systeme unterscheiden die Groß-/Kleinschreibung unterschiedlich. Wir empfehlen, Namen immer in Kleinbuchstaben zu verwenden.
Dinge, die bei der Verwendung von Fremdschlüsseln zu beachten sind: InnoDB in MySQL unterstützt die Überprüfung von Einschränkungen für Fremdschlüsselwörter.
Kapitel 11: SQL-Optimierung
Allgemeine Schritte zur SQL-Optimierung:
1. Verwendung Zeigen Sie Status- und Anwendungsmerkmale an, um die Ausführungshäufigkeit verschiedener SQLs und das ungefähre Ausführungsverhältnis verschiedener SQLs zu verstehen. Zum Beispiel die Anzahl der Zeilen, die von der Abfrage des InnoDB-Parameters Innode_rows_read zurückgegeben werden, die Anzahl der Zeilen, die von Innodb_rows_inserted beim Einfügen eingefügt wurden, und die Anzahl der Zeilen, die von Innodb_rows_updated aktualisiert wurden. Es gibt auch ein paar Parameter: Verbindungsversuche, eine Verbindung zum MySQL-Server herzustellen, die Arbeitszeit des Uptime-Servers und die Anzahl der langsamen Slow_queries-Anfragen.
2. Suchen Sie SQL-Anweisungen mit geringer Ausführungseffizienz. Es gibt zwei Möglichkeiten: Eine besteht darin, Anweisungen mit geringer Ausführungseffizienz über langsame Abfrageprotokolle zu finden. Beim Starten mit der Option --log-slow-queries[=file_name] schreibt mysqld eine Protokolldatei, die alle SQL-Anweisungen enthält, deren Ausführungszeit long_query_time überschreitet Sekunden. Die andere besteht darin, die Prozessliste anzuzeigen, um die aktuellen MySQL-Threads anzuzeigen, einschließlich des -Status des Threads, ob die Tabelle gesperrt werden soll usw. Sie können den SQL-Ausführungsstatus in Echtzeit anzeigen und einige Sperrtabellenvorgänge optimieren.
3. Analysieren Sie den Ausführungsplan von ineffizientem SQL mithilfe von EXPLAIN: EXPLAIN kann erkennen, wann die Tabelle indiziert werden muss, um ein schnelleres SELECT zu erhalten, das den Index zum Suchen von Datensätzen verwendet. Nach der EXPLAIN-Ausführung folgt eine Beschreibung der erhaltenen Ergebnisse:
select_type: Typ auswählen
Tabelle: die Tabelle, die die Ergebnismenge ausgibt
Typ: stellt die dar Anschluss des Tabellentyps. Wenn es in der Tabelle nur eine Zeile mit dem Wert „system“ gibt, ist dies der beste Verbindungstyp, wenn ein Index für die Tabellenverbindung in der Auswahloperation verwendet wird; der Typwert ist „ref“, wenn dies bei der Auswahltabellenverbindung nicht der Fall ist Wenn Sie einen Index verwenden, wird häufig der Wert „ALL“ angezeigt. Dies bedeutet, dass ein vollständiger Tabellenscan durchgeführt wurde. Zu diesem Zeitpunkt müssen Sie über die Erstellung eines Index nachdenken, um die Effizienz der Tabellenverbindung zu verbessern.
possible_keys: Gibt die Indexspalten an, die bei der Abfrage verwendet werden können.
key: Gibt den verwendeten Index an
key_len: Indexlänge
rows: Scanbereich
Extra: Erläuterung und Beschreibung der Ausführung
4. Probleme bestätigen und entsprechende Optimierungsmaßnahmen ergreifen.
Indexproblem
Speicherklassifizierung des Index: Die Datendatei und die Indexdatei der Myisam-Tabelle werden automatisch getrennt, und die Daten und der Index von innodb werden an derselben Stelle innerhalb eines Tabellenbereichs platziert. Die Indexspeichertypen von Myisam und Innodb sind beide Btree
Wie MySQL Indizes verwendet: Indizes werden verwendet, um Zeilen mit bestimmten Werten in einer Spalte schnell zu finden. Die wichtigste Bedingung für die Verwendung eines Index in einer Abfrage ist die Verwendung des Indexschlüssels in der Abfragebedingung. Wenn es sich um einen mehrspaltigen Index handelt, kann der Index nur verwendet werden, wenn das Präfix ganz links im mehrspaltigen Schlüssel verwendet wird die Abfragebedingung, sonst ist die Verwendung von Indizes nicht möglich.
Indexnutzung anzeigen: Der Wert von Handler_read_key gibt an, wie oft eine Zeile indiziert wurde. Ein niedriger Wert bedeutet, dass der Index nicht häufig verwendet wird. Ein hoher Handler_read_rnd_next-Wert bedeutet, dass die Abfrage ineffizient ausgeführt wird und ein Index erstellt werden sollte, um das Problem zu beheben. show status like 'Handler_read%';
Zwei einfache und praktische Optimierungsmethoden
Regelmäßige Analysetabelle: ANALYSE-TABELLE, CHECK-TABELLE, CHECKSUMME TABELLE
OPTIMIZE-Tabelle verwenden;
Optimierung aus Client-Perspektive (Codeseite)
Verwenden Sie eine dauerhafte Verbindung zur Datenbank , um Verbindungsaufwand zu vermeiden. Im Code verwenden wir im Allgemeinen den Verbindungspool
, um zu überprüfen, ob alle Einfügungen tatsächlich die erforderlichen Indizes verwenden.
Vermeiden Sie die Ausführung komplexer Auswahlabfragen für häufig aktualisierte Tabellen, um Probleme im Zusammenhang mit Tabellensperren aufgrund von Lese- und Schreibkonflikten zu vermeiden.
Nutzen Sie die Vorteile von Standardwerten voll aus und fügen Sie Werte nur dann explizit ein, wenn sie vom Standardwert abweichen. Reduzieren Sie die Syntaxanalyse, die MySQL durchführen muss, um die Einfügungsgeschwindigkeit zu erhöhen.
Die Trennung von Lesen und Schreiben verbessert die Leistung
Versuchen Sie, keine selbsterweiternden Tabellenfelder in Variablen zu verwenden, um dies zu verhindern Dieses Feld verhindert das Auftreten unter Bedingungen hoher Parallelität. Die automatische Inkrementierung wirkt sich auf die Effizienz aus. Es wird empfohlen, die automatische Inkrementierung von Feldern über Anwendungen zu implementieren.
Kapitel 12: Datenbank-Objekte optimieren
Tabellendatentypen optimieren: PROCEDURE ANALYZE()Stellen Sie Optimierungsvorschläge zur Beurteilung des aktuellen Tabellentyps bereit. In der Praxis können statistische Informationen in Kombination mit tatsächlicher Optimierung verwendet werden.
Verbessern Sie die Effizienz des Tabellenzugriffs durch Aufteilung: Die Aufteilung gilt hier hauptsächlich für Tabellen vom Typ Myisam.
Vertikale Aufteilung: Entsprechend der Häufigkeit des Anwendungszugriffs werden die Felder, auf die häufig zugegriffen wird, und die Felder, auf die selten zugegriffen wird, in zwei Tabellen aufgeteilt. Felder, auf die häufig zugegriffen wird, sollten so sicher wie möglich sein. lang.
Horizontale Aufteilung: Je nach Anwendungssituation werden die Daten gezielt horizontal in mehrere Tabellen aufgeteilt oder durch Partitionen in mehrere Partitionen unterteilt, wodurch Sperrprobleme durch Myisam-Tabellen effektiv vermieden werden können Abrufen und Aktualisieren.
Denormalisierung: Daten sollten so wenig redundant wie möglich sein Das Testen wirft Probleme auf. Durch geeignete Redundanz kann der Zugriff auf mehrere Tabellen reduziert und die Abfrageeffizienz erheblich verbessert werden. In diesem Fall kann eine geeignete Redundanz zur Verbesserung der Effizienz in Betracht gezogen werden.
Verwenden Sie redundante Statistiktabellen: Verwenden Sie create temporary table für statistische Analysen
Wählen Sie einen geeigneteren Tabellentyp: 1. Wenn in der Anwendung schwerwiegende Sperrkonflikte auftreten, überlegen Sie bitte, ob Sie diese absichtlich ändern möchten Wenn Sie die Speicher-Engine mit InnoDB verbinden, kann der Zeilensperrmechanismus 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
Wartebedingungen für den Erhalt von Sperren: Statusvariablen table_locks_waited und table_locks_immediate zur Analyse des Systems Konflikt um Tabellensperre. Überprüfen Sie Innode_row_lock, um den Zeilensperrkonflikt zu analysieren.
Kapitel 14: Optimierung des MySQL-Servers
Anzeigen der aktuellen Parameter des MySQL-Servers
-
Standardwerte der Serverparameter anzeigen: mysqld --verbose --help
Zeigen Sie die tatsächlichen Serverparameterwerte an: shell> mysqladmin variables or mysql> SHOW VARIABLES
Zeigen Sie den Server-Laufstatuswert an: mysqladmin extended-status or mysql>SHOW STATUS
Wichtige Parameter, die sich auf die MySQL-Leistung auswirken
key_buffer_size: keycache
table_cache: geöffnet in die Datenbank Anzahl der Caches
innode_buffer_pool_size: Die Größe des Speicherpuffers zum Zwischenspeichern von InnoDB-Daten und -Indizes
innodb_flush_log_at_trx_commit: Es wird empfohlen, diese festzulegen Es wird in jeder Transaktion auf 1 gesetzt. Beim Festschreiben wird der Protokollpuffer in die Protokolldatei geschrieben und die Protokolldatei wird durch Festplattenvorgänge aktualisiert.
Kapitel 15: E/A-Probleme
Die Festplattensuche ist ein großer Leistungsengpass.
E/A mithilfe von Festplatten-Arrays oder virtuellen Dateivolumes verteilen
E/A mithilfe symbolischer Links verteilen
Kapitel 16: Anwendungsoptimierung
Verbindungspool verwenden: Die Kosten für den Verbindungsaufbau betragen relativ hoch, durch Richten Sie einen Verbindungspool ein, um die Zugriffsleistung zu verbessern.
Zugriff auf MySQL reduzieren: 1. Wiederholtes Abrufen von Einwilligungsdaten vermeiden. 2 Verwenden Sie den MySQL-Abfragecache
, um die Cache-Ebene zu erhöhen
Lastausgleich: 1. Verwenden Sie MySQL, um Abfragevorgänge zu kopieren und auszulagern. 2 Verteilte Datenbank Architektur
Zusammenfassung
Das obige ist der detaillierte Inhalt vonLernen Sie MySQL in einfachen Worten (Sammlung). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
