Teilen Sie Ideen zur MySQL-Optimierung

1. Allgemeine Optimierungsidee

Erstellen Sie zunächst ein Skript, um die Anzahl der Abfragen, die Anzahl der Verbindungen und andere Daten zu beobachten, die Umgebungsgründe und die internen SQL-Ausführungsgründe zu ermitteln und dann Führen Sie eine spezifische Verarbeitung entsprechend den spezifischen Gründen durch.

Empfohlen: „MySQL-Video-Tutorial“

2. Skript-Beobachtungsstatus erstellen

mysqladmin -uroot -p  ext \G

Mit diesem Befehl können Informationen wie die aktuelle Anzahl der Abfragen abgerufen, regelmäßig abgefragt und die Ergebnisse in Text umgeleitet und anschließend in Diagrammen verarbeitet werden.

3. Gegenmaßnahmen

1. Wenn regelmäßig langsame Abfragen auftreten, berücksichtigen Sie das Cache-Lawinenproblem.

Für dieses Problem müssen Sie sich nur mit der Cache-Ablaufzeit befassen, damit sie nicht gleichzeitig zu ähnlichen Zeiten abläuft. Die Ablaufzeit sollte so diskret wie möglich sein oder sich auf Mitternacht konzentrieren.

2. Wenn unregelmäßige Abfragen langsam sind, berücksichtigen Sie, dass das Design nicht optimiert ist.

Verarbeitungsmethode:

a: Aktivieren Sie die Profilerstellung, um Abfragevorgänge aufzuzeichnen und Details zur Anweisungsausführung zu erhalten

show variables like '%profiling%';
set profiling=on;
select count(*) from user; 
show profiles;
show profile for query 1;
>>>
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000060 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000049 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000192 |
| init                           | 0.000006 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000014 |
| preparing                      | 0.000017 |
| executing                      | 0.001111 |
| end                            | 0.000006 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000015 |
| closing tables                 | 0.000011 |
| freeing items                  | 0.000085 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+

b: Verwenden Sie EXPLAIN, um die Anweisungsausführung, die Indexnutzung, den Scanbereich usw. anzuzeigen.

mysql> explain select count(*) from goods \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: index   
possible_keys: NULL
          key: gid
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index

c: Verwandte Optimierungstechniken

Tabellenoptimierung und Spaltentypauswahl

Spaltenauswahlprinzipien:

1: Feldtyppriorität Ganzzahl > Datum, Uhrzeit > > Grund: Ganzzahliger Typ, Zeitoperation ist schnell und spart Platz

char/varchar muss beim Sortieren die Konvertierung von Zeichensatz und Korrekturlesesatz berücksichtigen und ist langsam

Blob kann keinen temporären Speicher verwenden Tabelle

2: Gerade genug, seien Sie nicht großzügig (z. B. smallint, varchar(N))

Grund: Große Felder verschwenden Speicher und beeinträchtigen die Geschwindigkeit

Verwenden Sie Varchar (10), varchar( 300) speichert den gleichen Inhalt, aber beim Abfragen von Tabellen benötigt varchar(300) mehr Speicher

3: Vermeiden Sie die Verwendung von NULL

Grund: NULL ist nicht förderlich Zur Indizierung verwenden Sie daher spezielle Bytes zum Markieren.

nimmt tatsächlich mehr Platz auf der Festplatte ein

Indextyp

1.1 B-Tree-Index (sortierte Schnellsuchstruktur)

Hinweis: In Myisam, innodb, wird standardmäßig der B-Tree-Index verwendet

1.2 Hash-Index

In der Speichertabelle ist der Standardwert der Hash-Index und die theoretische Überprüfung der Hash-Abfragezeit ist O(1)

Frage: Da der Hash-Index so effizient ist, warum nicht verwenden?

Das von der a.hash-Funktion berechnete Ergebnis ist zufällig. Wenn die Daten am Beispiel des Primärschlüssels als ID auf der Festplatte abgelegt werden, wächst die der ID entsprechende Zeile zufällig auf der Festplatte platziert.

b. Bereichsabfrage kann nicht optimiert werden

c. Der Wert der Feldspalte kann beispielsweise nicht „helloworld“ verwendet werden xx=hello/xx =helloworld kann den Index (linker Präfixindex) verwenden, der Hash-Index jedoch nicht, da hash(hello) und hash(helloworld) nicht miteinander verbunden sind.

d. Die Sortierung kann nicht optimiert werden

e. Zeilen müssen zurückgegeben werden, um den Datenspeicherort über den Index zu erhalten, und Daten müssen an die Tabelle zurückgegeben werden. b-tree Häufige Missverständnisse über Indizes

2.1 Fügen Sie Indizes zu den Spalten hinzu, die häufig in Where-Bedingungen verwendet werden

Beispiel: where cat_id=3 and price>100; //Fragen Sie die dritte Spalte ab, mehr als 100 Yuan Das Produkt

ist falsch: Sowohl cat_id als auch der Preis sind indexiert. Tatsächlich kann nur ein Index verwendet werden, es handelt sich bei allen um unabhängige Indizes.

2.2 Nachdem ein Index für mehrere Spalten erstellt wurde, funktioniert der Index unabhängig davon, welche Spalte abgefragt wird.

2.2 Erstellen eines Index für mehrere Spalten Nach der Indizierung spielt der Index unabhängig von der abgefragten Spalte eine Rolle

Richtige Antwort: Damit ein mehrspaltiger Index funktioniert, muss der Index die Anforderungen an das linke Präfix erfüllen (geschichteter Index). )

Mit index(a, b,c) Zum Beispiel:

语句 索引是否发挥作用
where a=3 是
where a=3 and b=5 是
where a=3 and b=5 and c=4 是
where b=3 or where c=4 否
where a=3 and c=4 a列能发挥索引作用，c列不能
where a=3 and b>10 and c=7 a,b能发挥索引作用，c列不能

1 Wenn sich die Datendateien unter dem Knoten befinden, wird die Aufteilung des Knotens langsamer. Versuchen Sie, für den Primärschlüssel von innodb den Integer-Typ zu verwenden, und es handelt sich um einen aufsteigenden Integer-Typ. 2. Die Länge des Index wirkt sich direkt auf die Größe der Indexdatei aus, wirkt sich auf die Geschwindigkeit von Hinzufügungen, Löschungen und Änderungen aus und wirkt sich indirekt auf die Abfragegeschwindigkeit aus (beansprucht mehr Speicher).

3. Fangen Sie für die Werte in der Spalte den Teil von links nach rechts ab, um einen Index zu erstellen.

a. Je kürzer der Schnitt, desto höher die Wiederholung, desto geringer ist der Indexierungseffekt.

b Je länger der Schnitt, desto besser Indexdatei wird größer. Beeinflusst die Geschwindigkeit. Versuchen Sie also, einen Gleichgewichtspunkt in der Länge zu finden, um die Leistung zu maximieren 🎜> Nach Abschluss des Tests kann der Index entsprechend der aus dem Test erhaltenen optimalen Länge erstellt werden

select count(distinct left(word, 1)) / count(*) from table;

1. Häufige Abfragen

2. Diskriminierung hoch

Geringe Länge

Versuchen Sie, gängige Abfragefelder abzudecken

Das obige ist der detaillierte Inhalt vonTeilen Sie Ideen zur MySQL-Optimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!