Beheben von Problemen bei der MySQL-Leistungsoptimierung

Umfassende Probleme bei der MySQL-Optimierung:

A. Die Tabelle soll rationalisiert werden (im Einklang mit 3 Paradigmen)

B. Fügen Sie den entsprechenden Index (Index) hinzu [vier Typen: Gewöhnlicher Index, Primärschlüsselindex, eindeutiger Index, eindeutiger Volltextindex]

C. Tabellenaufteilungstechnologie (horizontale Aufteilung, vertikale Aufteilung)

D : aktualisieren/löschen/hinzufügen ] Trennung

E. Gespeicherte Prozeduren [modulare Programmierung, die die Geschwindigkeit erhöhen kann]

F. Optimieren Sie die MySQL-Konfiguration [konfigurieren Sie die maximale Anzahl von Parallelitäten, passen Sie die Cache-Größe an von my.ini]

G. MySQL-Server empfohlenes Upgrade

H. Regelmäßig löschen und defragmentieren

Empfohlene MySQL-bezogene Video-Tutorials: https:/ /www.php.cn/course/list/51/type/2.html

1. Design von Datenbanktabellen

Erste Normalform: 1NF ist die Atomizität von Attributen Einschränkungen erfordern, dass Attribute (Spalten) atomar sind und nicht zerlegt werden können (solange die relationale Datenbank 1NF erfüllt)

Zweite Normalform: 2NF ist eine eindeutige Einschränkung für Datensätze, die erfordert, dass Datensätze eindeutige Bezeichner haben Das heißt, die Einzigartigkeit der Entität.

Dritte Normalform: 3NF ist eine Einschränkung der Feldredundanz, die erfordert, dass Felder nicht redundant sind. Das kann kein redundantes Datenbankdesign leisten.

2. Allgemeine Schritte zur SQL-Optimierung

Vorgangsschritte:

1. Verwenden Sie den Befehl „show status“, um die Ausführungshäufigkeit verschiedener SQLs zu verstehen.

2. Suchen Sie nach SQL-Anweisungen mit geringer Ausführungseffizienz.

3. Analysieren Sie die Ausführung ineffizienter SQL-Anweisungen durch EXPLAIN entsprechende Optimierungsmaßnahmen

MySQL kann mithilfe des Befehls show [session|global] status Informationen zum Serverstatus bereitstellen.

session stellt die statistischen Ergebnisse der aktuellen Verbindung dar, und global stellt die statistischen Ergebnisse seit dem letzten Start der Datenbank dar. Der Standardwert ist Sitzungsebene.

Status wie „Com_%“ anzeigen;

wobei Com_XXX angibt, wie oft die XXX-Anweisung ausgeführt wurde. Beispiel: Com_insert, Com_Select...

Wichtiger Hinweis: Com_select, Com_insert, Com_update, Com_delete. Anhand dieser Parameter können Sie leicht erkennen, ob die aktuelle Datenbankanwendung hauptsächlich auf Einfüge- und Aktualisierungsvorgängen oder Abfragevorgängen basiert Verschiedene Arten von Was ist das ungefähre Ausführungsverhältnis von SQL?

Verbindungen: Die Anzahl der Versuche, eine Verbindung zum MySQL-Server herzustellen
Betriebszeit: Die Zeit, die der Server arbeitet (in Sekunden)
Slow_queries: Die Anzahl der langsamen Abfragen (der Standardwert ist eine langsame Abfragezeit von 10 Sekunden)

Status wie „Handler_read%“ anzeigen verwendet die Anzahl der Abfragen

Langsame Abfragen finden:

Standardmäßig zeichnet MySQL keine vollständigen Abfragen auf Protokolle. Es muss beim Start gestartet werden. Geben Sie

an, wenn Sie binmysqld.exe- -safe-mode – slow-query-log angeben [mysql5.5 kann in my.ini angegeben werden]

binmysqld.exe- -log-slow- query=d:bac.log

Der spezifische Vorgang ist wie folgt:

Wenn die langsame Abfrage aktiviert ist, wird sie hier in MySQL gespeichert. INI-Datei standardmäßig

1. Starten Sie MySQL neu, suchen Sie den Pfad zu datadir und geben Sie mit cmd das übergeordnete Datenverzeichnis ein

2. Führen Sie den Befehl binmysqld.exe –safe-mode –slow-query-log aus (beachten Sie dies vor der Ausführung. Schließen Sie zuerst den MySQL-Dienst)

3. Die generierte Protokolldatei zeichnet alle Datensatzinformationen auf

Anzeige die Zeit der langsamen Abfrage: Variablen wie „long_query_time“ anzeigen;

Zeit für vollständige Abfrage zurücksetzen: long_query_time=2 setzen;

Ändern Sie das Befehlsendesymbol: (Damit der gespeicherte Prozess ausgeführt werden kann normal ausführen, wir müssen das Befehlsendesymbol ändern)

Trennzeichen $$

So zeichnen Sie die langsamen Abfrage-SQL-Anweisungen in unseren Protokollen auf (MySQL zeichnet sie standardmäßig nicht auf, Sie müssen dies tun Geben Sie beim Starten von MySQL die langsame Abfrage an.

3. Index

♥ Arten von Indizes:

★Vier Arten von Indizes ① Primärschlüsselindex ② Eindeutiger Index ③ Gewöhnlicher Index ④ Volltextindex

1.

hinzufügen 1.1

zum Primärschlüsselindex hinzufügen Wenn eine Spalte einer Tabelle als Primärschlüssel festgelegt ist, ist die Spalte der Primärschlüsselindex.

                                                                                                                                                                                     Createtable aaa(id int unsigned Primary Key.

                                           🎜>

Im Allgemeinen erstellen Sie für normale Indizes zuerst eine Tabelle und dann einen normalen Index.

Zum Beispiel:

Indexindexnamen aus Tabellennamen erstellen

1.3 Volltextindex erstellen

Volltextindex, hauptsächlich für Dateien, z Volltextindizierung von Artikeln Für myISAM wird es nicht für InnoDB verwendet.

Tabellenartikel erstellen (

ID INT UNSIGNEDAUTO_INCREMENT NOT NULL Primärschlüssel,

Titel Varchar (20 ),

🎜 🎜 🎜 🎜 🎜 🎜 🎜 " Körpertext,

Volltext (Titel, Körper)

)engine=myisam charsetutf8;

Falsche Verwendung:

%mysql%'[Volltextindex wird nicht verwendet]

Beweis:

Korrekte Verwendung: 2. Die àsphinx (coreseek)-Technologie ist wirksam für Englisch und verarbeitet Chinesisch

3. Die verwendete Methode, match(field name,...)against('keyword' )

                                                                                                                                                                                                   Stoppwörter. Da in einem Text ein unendliches Buch als Index erstellt wird, werden einige häufig vorkommende Wörter und Zeichen nicht erstellt. Diese Wörter werden Stoppwörter genannt.

1.4 Erstellen Sie einen eindeutigen Index                                                                                                                                                                                  Wenn a Spalte der Tabelle als eindeutige Einschränkung angegeben ist, ist diese Spalte der eindeutige Index

Zu diesem Zeitpunkt ist der Name standardmäßig der einzige Index

Das zweite Feld „Create Table Eee Primary Key = Unique+Not Null“

Das UNIQUE-Feld kann null sein und mehrere NULL-Werte haben, aber wenn es sich um einen bestimmten Inhalt handelt, kann es nicht wiederholt werden

Hauptschlüsselfelder, nicht null, oder nicht. Wiederholen Sie

2. Abfrage

1. Desc-Tabellenname [Nachteil dieser Methode, Indexname kann nicht realisiert werden]

2. Index aus dem Tabellennamen anzeigen; Schlüssel aus Tabellennamen anzeigen

3. Löschen Sie

Veränderbarer Tabellenname, Indexnamen löschen,

Veränderbarer Tabellenname, Primärschlüssel löschen. (Löschen Sie den Primärschlüsselindexnamen)

4. Ändern Sie

Zuerst löschen, dann alles löschen

2. Es ist relativ praktisch, das durch SQL-Schreiben verursachte langsame SQL zu optimieren . Wie im vorherigen Abschnitt erwähnt, kann die korrekte Verwendung von Indizes Abfragen beschleunigen. Daher müssen wir beim Schreiben von SQL auf die Regeln für Indizes achten:

1. Bei der Feldtypkonvertierung sind keine Indizes erforderlich , wie z. B. Zeichenfolgentypen. Verwenden Sie keine Anführungszeichen, verwenden Sie Anführungszeichen für numerische Typen usw. Dies kann dazu führen, dass der Index nicht verwendet wird und ein vollständiger Tabellenscan erfolgt.

2. MySQL unterstützt keine Funktionskonvertierung, daher Funktionen kann nicht vor Feldern hinzugefügt werden, da es sonst nicht zum Index verwendet wird.

3 Wenn die Zeichenfolge relativ ist Langfristig können Sie einen Teil davon indizieren, um die Größe der Indexdatei zu verringern und die Schreibeffizienz zu verbessern nicht für separate Abfragen basierend auf dem zweiten und den folgenden Feldern des gemeinsamen Index erforderlich; verwenden Sie nicht select *; 🎜>

9. Versuchen Sie, Union (Innodb) für oder-Abfragen zu verwenden.

11. Groupby-Felder werden in den Index aufgenommen, um die Sortierung zu reduzieren und die Effizienz zu erhöhen.

Zusätzlich zu den oben genannten Indexnutzungsregeln müssen Sie beim Schreiben von SQL besonders auf die folgenden Punkte achten:

1 Versuchen Sie, SQL mit großen Transaktionen zu vermeiden, da dies Auswirkungen auf die Parallelität hat Leistung und Leistung der Datenbank.

2. Bitte verwenden Sie „Truncate“, um alle Datensätze in der Tabelle zu löschen.

4. Lassen Sie MySQL nicht austrocknen.

5. Die Leistung ist relativ gut (Es handelt sich um eine Datenwörterbuchanalyse. Bitte fragen Sie die Informationen selbst ab.) Verwenden Sie „select count(*)“ auf Innodb, da Innodb statistische Informationen speichert () mit Vorsicht.

3. Anzahl der langsamen Abfragen anzeigen: Status wie „slow_queries“ anzeigen;

HEAP ist eine frühere MySQL-Version

4. Erklären Sie die Analyse ineffizienter SQL-Anweisungen:

erzeugt die folgenden Informationen:

select_type: bedeutet The Art der Abfrage.

Tabelle: Die Tabelle, die den Ergebnissatz ausgibt.

Typ: Gibt den Verbindungstyp der Tabelle an.

Mögliche Schlüssel: Gibt die Indizes an, die bei der Abfrage von

verwendet werden können

Schlüssel: Gibt den tatsächlich verwendeten Index an

Key_len: Die Länge des Indexfelds

rows: Die Anzahl der gescannten Zeilen (geschätzte Anzahl der Zeilen)

Extra: Beschreibung und Erklärung der Ausführung

Select_type-Typ:

primär: die äußerste Abfrage in der Unterabfrage

Unterabfrage: die erste Auswahl in der inneren Ebene der Unterabfrage, das Ergebnis hängt nicht von der äußeren Abfrage ab

abhängige Unterabfrage: Unterabfrage Die erste Auswahl in der inneren Ebene hängt von der externen Abfrage ab

Union: Union-Anweisung beginnt mit allen Auswahlen nach der zweiten Auswahl

einfach: einfacher Modus

Union-Ergebnis: Union Union-Ergebnis Ergebnisse zusammenführen in

Typ Typ:

alle: Ein vollständiger Tabellenscan ist normalerweise nicht gut

System: Die Tabelle hat nur eine Zeile ( =Systemtabelle) Dies ist ein const-Join-Typ. Ein Sonderfall

const: Die Tabelle hat höchstens eine passende Zeile

zusätzlicher Typ:

keine Tabelle: von dual is wird in der Abfrageanweisung verwendet oder enthält keine from-Klausel

VERWENDUNG VON FILESORT: Wenn die Abfrage den Vorgang „Order by“ enthält und der Index nicht zum Abschließen der Sortierung verwendet werden kann

Unmöglich, WO BEACHTET Nach dem Lesen von Const Tables: MySQL Query Optimizer

Es ist unmöglich, statistische Informationen durch Sammeln statistischer Informationen zu hinterlegen. Im Ergebnis

Temporär verwenden: Einige Vorgänge müssen temporäre Tabellen, gemeinsame Gruppierung nach, Reihenfolge nach

Wo: Sie müssen nicht alle Informationen in der Tabelle lesen, Sie können die erforderlichen Daten nur über den Index abrufen

4 Warum wird die Abfragegeschwindigkeit nach Verwendung des Index schneller? 🎜>

Wenn für eine normale Abfrage kein Index vorhanden ist, wird diese ständig ausgeführt und die Abfrage wird fortgesetzt, nachdem eine rechtzeitige Übereinstimmung erzielt wurde. Es gibt keine Garantie dafür, dass die Abfrage ausgeführt wird Haben Sie etwas, worüber Sie sich erkundigen möchten? Eine Volltextindizierung ist erforderlich.

■ Dinge, die bei der Verwendung von Indizes zu beachten sind

Kosten für die Indizierung:

1. Belegung Speicherplatz

2. Es hat Auswirkungen auf DML-Vorgänge (Einfügen, Aktualisieren, Erstellen) und verlangsamt die Geschwindigkeit

■Zusammenfassung: Indizes sollten nur erstellt werden, wenn die folgenden Bedingungen erfüllt sind

A. Wird auf jeden Fall häufig verwendet, wenn B. Der Inhalt dieses Feldes ist kein eindeutiger Wert (Geschlecht)

Der Inhalt des Feldes ändert sich nicht häufig

■ Vorsichtsmaßnahmen für die Verwendung von Indizes:

alter table dept add index myind (dname,loc); // dname ist die Spalte links, loc ist die Spalte rechts

Es ist möglich, den Index in den folgenden Situationen zu verwenden

a Für den erstellten mehrspaltigen Index wird im Allgemeinen der Index verwendet, solange die Abfragebedingung die Spalte ganz links verwendet dept where dname='aaa';

b Wenn die Abfragebedingung „%aaa“ lautet, wird der Index nicht verwendet und „aaa%“ verwendet den Index

Der Index wird in den folgenden Situationen nicht verwendet:

a. Wenn ein bedingter Index vorhanden ist, wird er nicht verwendet Vorschlag: Vermeiden Sie die Verwendung des Schlüsselworts oder

b. Bei mehrspaltigen Indizes wird der Index nicht verwendet

select * from dept where loc='aaa';// Bei der mehrspaltigen Indizierung ist loc die rechte Spalte, der Index wird nicht verwendet

c.like Die Abfrage beginnt mit % Wenn es sein muss Wenn der Spaltentyp eine Zeichenfolge ist, müssen die Daten in der Bedingung in Anführungszeichen gesetzt werden, andernfalls wird der Index nicht verwendet

e. Wenn MySQL der Meinung ist, dass die Verwendung eines vollständigen Tabellenscans besser ist als die Verwendung eines Indexblocks, dann verwenden Sie keinen Index

So wählen Sie die Speicher-Engine für MySQL aus

1: myISAM

Wenn die Tabelle stimmt Die Anforderungen an die Angelegenheiten sind nicht hoch. Die Kollegen fragen sie hauptsächlich ab und fügen sie hinzu, z. B. das Posten und Antworten in BBS.

2: InnoDB

Hohe Anforderungen an Transaktionen, die gespeicherten Daten sind wichtige Daten. Bei der Eingabe der Datenbank können Sie auch die Site zur Abfrage und Änderung aufrufen.

Der Unterschied zwischen myISAM und InnoDB:

1. MyISAM-Batch-Einfügung ist schnell, InnoDB-Einfügung ist langsam und myISAM sortiert beim Einfügen nicht.

2. InnoDB unterstützt Transaktionen, myISAM unterstützt jedoch keine Transaktionen.

3. MyISAM unterstützt den Volltextindex,

4. Sperrmechanismus, myISAM ist eine Tabellensperre, InnoDB ist eine Zeilensperre

5 Schlüssel, InnoDB unterstützt Jian-Fremdschlüssel

① In Anwendungen mit hohen Fortschrittsanforderungen wird empfohlen, Festkommadaten zum Speichern von Werten zu verwenden. Gruppe U, um die Genauigkeit der Daten sicherzustellen. Der Deciaml-Fortschritt ist höher als der Float Versuchen Sie,

② für die Speicherung zu verwenden. Wenn die myISAM-Datenbank der Engine gelöscht und geändert werden muss, muss die Funktion „optimize_table_name“ regelmäßig ausgeführt werden, um die Tabelle zu defragmentieren.

③ Als Datumstyp sollte der frühe Typ mit der kleinsten Speicherreferenz basierend auf den tatsächlichen Anforderungen ausgewählt werden.

Manuelle Sicherung der Datenbank:

1

2. Mysqldump –uroot –proot Datenbank [Tabellenname 1, Tabellenname 2…] > Dateipfad

Zum Beispiel: mysqldump -uroot -proot temp >

Sicherungsdateidaten wiederherstellen:

Quelle d:/temp.bak (in der MySQL-Konsole)

Angemessene Hardwareressourcen und Betriebssystem

Master

Slave1

Slave2

Slave3

Der Hauptdatenbank-Master wird zum Schreiben verwendet, und Slave1-Slave3 werden für die Auswahl verwendet.

Jede Datenbank teilt weniger viel Druck.

Um diese Methode zu implementieren, muss das Programm speziell entworfen werden. Der Master wird zum Schreiben und der

Slave zum Lesen verwendet, was eine zusätzliche Belastung für die Programmentwicklung darstellt. Natürlich gibt es bereits Middleware, um diesen

-Proxy zu implementieren, der für das Programm transparent ist, um welche Datenbanken lesen und schreiben können. Es gibt einen offiziellen MySQL-Proxy, aber

ist immer noch eine Alpha-Version. Sina hat Amobe für MySQL, das diesen Zweck auch erreichen kann. Die Struktur

ist wie folgt:

5. Tabellensegmentierung

Horizontale Segmentierung:

Wenn wir Tabellen mit großen Datenmengen abrufen, sollten wir den Standard der Tabelle entsprechend den Geschäftsanforderungen ermitteln, die Abrufmethode des Benutzers auf der Abrufseite einschränken und mit Paging zusammenarbeiten.

        Fall: Benutzertabelle mit großer Datenmenge

Drei Tabellen: qqlogin0, qqlogin1, qqlogin2

Fügen Sie die Benutzer-ID %3 entsprechend den Ergebnissen in verschiedene Tabellen ein

create tableqqlogin0(

id int unsigned not null Primary Key,/* Diese ID kann nicht auf automatische Inkrementierung eingestellt werden*/

name varchar (32) not null default'',

pwd varchar(32)not null default''

) engine = myisam default charset = utf8;

Erstelle Tabelle qqlogin1(

id int unsigned not null Primary Key, / *Diese ID kann nicht auf automatische Inkrementierung eingestellt werden* /

name varchar (32) not null default'',

pwd varchar ( 32) not null default''

) engine = myisam default charset = utf8;

Erstelle Tabelle qqlogin2 (

id int unsigned not null Primary Schlüssel, /*Diese ID kann nicht auf automatische Inkrementierung eingestellt werden /

Vertikale Aufteilung:

Einige Felder einer Tabelle einfügen. Diese Felder sind bei der Abfrage nicht relevant, die Datenmenge jedoch groß. Wir empfehlen, diese Felder in eine Tabelle einzufügen, um die Effizienz zu verbessern

6. Optimierte MySQL-Konfiguration

MY.INI

Port = 3306,

Wenn Sie den Port port = 3309 ändern möchten, beachten Sie, dass

query_cache_size = 15M in mysql_connect('localhost:3309', 'root', 'root'); des Abfragecaches

InnoDB-Parameter können ebenfalls verwendet werden. Erhöhen Sie die folgenden zwei Parameter

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size = 1G

myisam muss key_buffer_size anpassen

Das Anpassen von Parametern hängt auch vom Status ab. Verwenden Sie „Status anzeigen“. Sie können den aktuellen Status anzeigen, um zu entscheiden, welche Parameter angepasst werden sollen.

Inkrementelle Sicherung

Tatsächlich Fall:

So führen Sie eine inkrementelle Sicherung und Wiederherstellung durch

Schritte:

Konfigurieren Sie wie in Abbildung 1 gezeigt die Datei my.ini oder my.cof und aktivieren Sie die Binärsicherung

2, MySQL neu starten

Nach dem Start finden Sie einige generierte Dateien im Mylog-Verzeichnis

Darunter: E: Binärprotokoll-Indexdatei mylog.index, welche Sicherungsdateien gibt es?

E: Das Binärprotokoll mylog.000001 speichert die Datei für Benutzerobjekt-Datenbankoperationen

3. Wenn wir ausführen (auswählen)

, überprüfen Sie den Bin, der eingegeben werden muss MySQL-Installationsverzeichnis, und führen Sie dann die mysqlbinlog-Datei aus, hängen Sie den Dateipfad

an, wie in Abbildung 4 gezeigt, um die Zeit wiederherzustellen Zeitpunkt einer bestimmten Aussage

4,1 Antwort nach Zeitpunkt

Mysqlbinlog -stop-datetime="2013-01-17 12:00:23"d:/binlog/mylog. 000001 |. mysq -uroot - p

(Alle Daten vor der Stoppzeit wiederherstellen)

Mysqlbinlog-start-datetime="2013-01-17 12:00:23" d:/binlog /mylog.000001 |. mysq -uroot -p

(alle Daten nach Ablauf der Startzeit wiederherstellen)

4,2Je nach Position wiederherstellen

Mysqlbinlog-stop-position ="234"d: /binlog/mylog.000001 |. mysq -uroot -p

(Alle Daten vor der Stoppzeit wiederherstellen)

Mysqlbinlog-start-position="234" d: /binlog/mylog. 000001 |. mysq -uroot -p

(Alle Daten nach der Startzeit wiederherstellen)

Weitere verwandte Fragen finden Sie auf der chinesischen PHP-Website: https: //www.php .cn/

Das obige ist der detaillierte Inhalt vonBeheben von Problemen bei der MySQL-Leistungsoptimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!