MySQL
Prestandajustering

Syntax

• Använd inte DISTINCT och GROUP BY i samma VÄLJ.

• Paginera inte via OFFSET, "kom ihåg var du slutade".

• VAR (a, b) = (22,33) inte optimerar alls.

• Säg uttryckligen ALL eller DISTINCT efter UNION - det påminner dig om att välja mellan snabbare ALL eller långsammare DISTINCT.

• Använd inte SELECT *, särskilt om du har TEXT- eller BLOB-kolumner som du inte behöver. Det finns overhead i tmp-tabeller och transmission.

• Det är snabbare när GROUP BY och ORDER BY kan ha exakt samma lista.

• Använd inte FORCE INDEX; det kan hjälpa idag, men kommer antagligen att skada i morgon.

Anmärkningar

Lägg till rätt index

Detta är ett enormt ämne, men det är också den viktigaste frågan om "prestanda".

Den huvudsakliga lektionen för en nybörjare är att lära sig om "sammansatta" index. Här är ett snabbt exempel:

INDEX(last_name, first_name)

är utmärkt för dessa:

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

men inte för

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

Ställ in cachen korrekt

innodb_buffer_pool_size bör vara cirka 70% av tillgängligt RAM-minne.

Undvik ineffektiva konstruktioner

x IN ( SELECT ... )

förvandlas till en JOIN

Undvik, om möjligt, OR .

Dölj inte en indexerad kolumn i en funktion, till exempel WHERE DATE(x) = ... ; omformulera som WHERE x = ...

Du kan i allmänhet undvika WHERE LCASE(name1) = LCASE(name2) genom att ha en lämplig sortering.

Använd inte OFFSET för "pagination", istället "kom ihåg var du slutade".

Undvik SELECT * ... (om inte felsökning).

Obs till Maria Deleva, Barranka, Batsu: Detta är en platsinnehavare; snälla ta bort dessa objekt när du bygger fullskaliga exempel. När du har gjort de du kan, kommer jag att flytta in för att utarbeta resten och / eller kasta dem.

negativ

Här är några saker som sannolikt inte hjälper till att prestera. De härrör från föråldrad information och / eller naivitet.

• InnoDB har förbättrats så att MyISAM osannolikt kommer att bli bättre.
• PARTITIONing ger sällan prestationsfördelar; det kan till och med skada prestanda.
• Att ställa query_cache_size större än 100M kommer vanligtvis att skada prestanda.
• Att öka massor av värden i my.cnf kan leda till "byte", vilket är ett allvarligt prestandaproblem.
• "Prefix-index" (som INDEX(foo(20)) ) är i allmänhet värdelösa.
• OPTIMIZE TABLE är nästan alltid värdelös. (Och det handlar om att låsa bordet.)

Ha en INDEX

Det viktigaste för att påskynda en fråga på något icke-litet bord är att ha ett lämpligt 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

Göm dig inte i funktion

Ett vanligt misstag är att dölja en indexerad kolumn i ett funktionssamtal. Till exempel kan detta inte hjälpa till av ett index:

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

I stället, med INDEX(dt) kan dessa använda indexet:

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

Detta fungerar för DATE , DATETIME , TIMESTAMP och till och med DATETIME(6) (mikrosekunder):

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

ELLER

Generellt dödar OR optimering.

WHERE a = 12 OR b = 78

kan inte använda INDEX(a,b) och kanske inte får använda INDEX(a), INDEX(b) via "indexfusion". Indexfusion är bättre än ingenting, men bara knappt.

WHERE x = 3 OR x = 5

förvandlas till

WHERE x IN (3, 5)

som kan använda ett index med x i det.

delfrågor

Underfrågor finns i flera smaker, och de har olika optimeringspotential. Först bör du notera att undersökningar kan antingen "korreleras" eller "okorrelerade". Korrelerade innebär att de är beroende av något värde utanför underkällan. Detta innebär i allmänhet att undervärden måste utvärderas för varje yttre värde.

Denna korrelerade undersökning är ofta ganska bra. Obs: Det måste återgå till högst 1 värde. Det är ofta användbart som ett alternativ till, men inte nödvändigtvis snabbare än, LEFT JOIN .

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 ...

Detta är vanligtvis okorrelerat:

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

Anmärkningar om FROM-SELECT :

• Om det returnerar 1 rad, bra.
• Ett bra paradigm (återigen "1 rad") är att underkällan ska vara ( SELECT @n := 0 ) , vilket därmed initialiserar en `@variabel för användning i resten eller frågan.
• Om det returnerar många rader och JOIN också är ( SELECT ... ) med många rader, kan effektiviteten vara fruktansvärd. Före 5.6 fanns det inget index, så det blev ett CROSS JOIN ; 5.6+ innebär att dra det bästa indexet på temp-tabellerna och sedan generera det, bara för att kasta bort det när du är klar med SELECT .

GÅ MED + GRUPP AV

Ett vanligt problem som leder till en ineffektiv fråga går något så här:

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

Först utökar JOIN antalet rader; sedan vittlar GROUP BY tillbaka antalet rader i a .

Det kanske inte finns några bra val för att lösa detta explod-implode-problem. Ett möjligt alternativ är att förvandla JOIN till en korrelerad underkälla i SELECT . Detta eliminerar också GROUP BY .