MySQL
Leistungsoptimierung

Syntax

• Verwenden Sie DISTINCT und GROUP BY nicht in demselben SELECT.

• Nicht über OFFSET paginieren, "sich erinnern, wo Sie aufgehört haben".

• WO (a, b) = (22,33) optimiert überhaupt nicht.

• Sagen Sie explizit ALL oder DISTINCT nach UNION - es erinnert Sie daran, zwischen dem schnelleren ALL oder dem langsameren DISTINCT zu wählen.

• Verwenden Sie SELECT * nicht, insbesondere wenn Sie keine TEXT- oder BLOB-Spalten haben. Es gibt Overhead in TMP-Tabellen und Übertragung.

• Es ist schneller, wenn GROUP BY und ORDER BY genau dieselbe Liste haben können.

• Verwenden Sie nicht FORCE INDEX. Es kann heute helfen, wird aber wahrscheinlich morgen weh tun.

Bemerkungen

Fügen Sie den richtigen Index hinzu

Dies ist ein riesiges Thema, aber es ist auch das wichtigste "Leistungsproblem".

Die wichtigste Lektion für einen Anfänger ist das Erlernen von "zusammengesetzten" Indizes. Hier ein kurzes Beispiel:

INDEX(last_name, first_name)

ist hervorragend für diese:

WHERE last_name = '...'
WHERE first_name = '...' AND last_name = '...'   -- (order in WHERE does not matter)

aber nicht für

WHERE first_name = '...'   -- order in INDEX _does_ matter
WHERE last_name = '...' OR first_name = '...'   -- "OR" is a killer

Stellen Sie den Cache richtig ein

innodb_buffer_pool_size sollte etwa 70% des verfügbaren Arbeitsspeichers innodb_buffer_pool_size .

Vermeiden Sie ineffiziente Konstrukte

x IN ( SELECT ... )

verwandeln Sie sich in einen JOIN

Wenn möglich, vermeiden Sie OR .

Verbergen Sie eine indizierte Spalte in einer Funktion nicht, z. B. WHERE DATE(x) = ... ; umformulieren als WHERE x = ...

Sie können WHERE LCASE(name1) = LCASE(name2) generell vermeiden, indem Sie eine geeignete Sortierung verwenden.

Verwenden Sie OFFSET für "Paginierung", sondern "Erinnern Sie sich, wo Sie OFFSET ".

Vermeiden Sie SELECT * ... (es sei denn, Sie debuggen).

Anmerkung zu Maria Deleva, Barranka, Batsu: Dies ist ein Platzhalter; Bitte entfernen Sie diese Elemente, wenn Sie umfassende Beispiele erstellen. Nachdem Sie diejenigen getan haben, die Sie können, werde ich mich um den Rest kümmern und / oder sie werfen.

Negative

Hier sind einige Dinge, die die Leistung wahrscheinlich nicht verbessern. Sie stammen aus veralteten Informationen und / oder Naivität.

• InnoDB hat sich soweit verbessert, dass MyISAM wahrscheinlich nicht besser ist.
• PARTITIONing bietet selten Leistungsvorteile; Es kann sogar die Leistung beeinträchtigen.
• Wenn Sie query_cache_size als 100 query_cache_size wird die Leistung normalerweise query_cache_size .
• Das Erhöhen my.cnf Werte in my.cnf kann zu 'Swapping' führen, was ein ernstes Leistungsproblem darstellt.
• "Prefix-Indizes" (wie INDEX(foo(20)) )) sind im Allgemeinen unbrauchbar.
• OPTIMIZE TABLE ist fast immer unbrauchbar. (Und es beinhaltet das Sperren der Tabelle.)

Habe einen INDEX

Das Wichtigste für die Beschleunigung einer Abfrage in einer nicht-kleinen Tabelle ist der geeignete Index.

WHERE a = 12  --> INDEX(a)
WHERE a > 12  --> INDEX(a)

WHERE a = 12 AND b > 78  --> INDEX(a,b) is more useful than INDEX(b,a)
WHERE a > 12 AND b > 78  --> INDEX(a) or INDEX(b); no way to handle both ranges

ORDER BY x  --> INDEX(x)
ORDER BY x, y  --> INDEX(x,y) in that order
ORDER BY x DESC, y ASC  --> No index helps - because of mixing ASC and DESC

Versteck dich nicht in der Funktion

Ein häufiger Fehler ist das Ausblenden einer indizierten Spalte in einem Funktionsaufruf. Zum Beispiel kann ein Index nicht helfen:

WHERE DATE(dt) = '2000-01-01'

Anstelle von INDEX(dt) können diese den Index verwenden:

WHERE dt = '2000-01-01'  -- if `dt` is datatype `DATE`

Dies funktioniert für DATE , DATETIME , TIMESTAMP und sogar DATETIME(6) (Mikrosekunden):

WHERE dt >= '2000-01-01'
  AND dt  < '2000-01-01' + INTERVAL 1 DAY

ODER

Im Allgemeinen tötet OR Optimierung.

WHERE a = 12 OR b = 78

kann INDEX(a,b) nicht verwenden und kann INDEX(a), INDEX(b) über "Index Merge" verwenden. Index Merge ist besser als nichts, aber nur kaum.

WHERE x = 3 OR x = 5

wird in umgewandelt

WHERE x IN (3, 5)

die einen Index mit verwenden x drin.

Unterabfragen

Unterabfragen gibt es in verschiedenen Ausführungen und sie haben unterschiedliche Optimierungsmöglichkeiten. Beachten Sie zunächst, dass Unterabfragen entweder "korreliert" oder "unkorreliert" sein können. Korreliert bedeutet, dass sie von einem Wert außerhalb der Unterabfrage abhängen. Dies bedeutet im Allgemeinen , dass die Unterabfrage muss für jeden Außenwert neu bewertet werden.

Diese korrelierte Unterabfrage ist oft ziemlich gut. Hinweis: Es muss höchstens ein Wert zurückgegeben werden. Es ist oft nützlich als Alternative zu einem LEFT JOIN , wenn auch nicht unbedingt schneller.

SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c
    FROM u ...
SELECT a, b, ( SELECT MAX(x) ... ) AS c
    FROM u ...
SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c
    FROM u ...

Dies ist normalerweise nicht korreliert:

SELECT ...
    FROM ( SELECT ... ) AS a
    JOIN b ON ...

Hinweise zum FROM-SELECT :

• Wenn es 1 Reihe zurückgibt, großartig.
• Ein gutes Paradigma (wieder "1 Zeile") ist, dass die Unterabfrage ( SELECT @n := 0 ) , wodurch eine @ -Variable für die Verwendung im Rest der Abfrage initialisiert wird.
• Wenn es viele Zeilen zurückgibt und der JOIN auch ( SELECT ... ) mit vielen Zeilen ist, kann die Effizienz schrecklich sein. Vor 5.6 gab es keinen Index, also wurde es ein CROSS JOIN . 5.6+ beinhaltet das Ableiten des besten Index für die temporären Tabellen und das Generieren dieses Werts, um ihn nach der Auswahl mit SELECT wegzuwerfen.

JOIN + GROUP BY

Ein häufiges Problem, das zu einer ineffizienten Abfrage führt, sieht etwa so aus:

SELECT ...
    FROM a
    JOIN b  ON ...
    WHERE ...
    GROUP BY a.id

Zuerst erweitert der JOIN die Anzahl der Zeilen. dann spult die GROUP BY die Anzahl der Reihen in a .

Es gibt möglicherweise keine guten Entscheidungen, um dieses Explosions-Implode-Problem zu lösen. Eine mögliche Option besteht darin, den JOIN in eine korrelierte Unterabfrage in SELECT umzuwandeln. Dadurch entfällt auch GROUP BY .