MySQL
Tuning van prestaties

Syntaxis

• Gebruik DISTINCT en GROUP BY niet in dezelfde SELECT.

• Pagineer niet via OFFSET, "onthoud waar je gebleven was".

• WAAR (a, b) = (22,33) optimaliseert helemaal niet.

• Zeg expliciet ALL of DISTINCT na UNION - het herinnert je eraan dat je moet kiezen tussen de snellere ALL of de langzamere DISTINCT.

• Gebruik geen SELECT *, vooral als u TEKST- of BLOB-kolommen hebt die u niet nodig hebt. Er is overhead in tmp-tabellen en transmissie.

• Het gaat sneller wanneer de GROUP BY en ORDER BY exact dezelfde lijst kunnen hebben.

• Gebruik geen FORCE INDEX; het kan vandaag helpen, maar zal waarschijnlijk morgen pijn doen.

Opmerkingen

Voeg de juiste index toe

Dit is een enorm onderwerp, maar het is ook het belangrijkste "prestatie" -probleem.

De belangrijkste les voor een beginner is om te leren van "samengestelde" indexen. Hier is een snel voorbeeld:

INDEX(last_name, first_name)

is uitstekend voor deze:

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

maar niet voor

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

Stel de cache correct in

innodb_buffer_pool_size zou ongeveer 70% van het beschikbare RAM moeten zijn.

Vermijd inefficiënte constructies

x IN ( SELECT ... )

veranderen in een JOIN

Vermijd indien mogelijk OR .

Verberg een geïndexeerde kolom niet in een functie, zoals WHERE DATE(x) = ... ; herformuleren als WHERE x = ...

Over het algemeen kunt u WHERE LCASE(name1) = LCASE(name2) door een geschikte sortering te hebben.

Gebruik OFFSET voor 'paginering', maar 'onthoud waar u bent gebleven'.

Vermijd SELECT * ... (tenzij u fouten opspoort).

Opmerking voor Maria Deleva, Barranka, Batsu: dit is een plaatshouder; zorg ervoor dat u deze items verwijdert terwijl u volledige voorbeelden maakt. Nadat je hebt gedaan wat je kunt, zal ik ingaan op de rest en / of gooien.

minpunten

Hier zijn enkele dingen die de prestaties waarschijnlijk niet zullen helpen. Ze komen voort uit verouderde informatie en / of naïviteit.

• InnoDB is zodanig verbeterd dat MyISAM waarschijnlijk niet beter zal zijn.
• PARTITIONing biedt zelden prestatievoordelen; het kan zelfs de prestaties schaden.
• Het instellen van query_cache_size groter dan 100M zal meestal de prestaties query_cache_size beïnvloeden.
• Het verhogen van veel waarden in my.cnf kan leiden tot 'swapping', wat een serieus prestatieprobleem is.
• "Prefix-indexen" (zoals INDEX(foo(20)) ) zijn over het algemeen nutteloos.
• OPTIMIZE TABLE is bijna altijd nutteloos. (En het gaat om het vergrendelen van de tafel.)

Heb een INDEX

Het belangrijkste voor het versnellen van een zoekopdracht op een niet-kleine tabel is om een geschikte index te hebben.

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

Niet verbergen in functie

Een veel voorkomende fout is het verbergen van een geïndexeerde kolom in een functieaanroep. Dit kan bijvoorbeeld niet worden geholpen door een index:

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

In plaats daarvan kunnen deze, gegeven INDEX(dt) de index gebruiken:

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

Dit werkt voor DATE , DATETIME , TIMESTAMP en zelfs DATETIME(6) (microseconden):

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

OF

Over het algemeen doodt OR optimalisatie.

WHERE a = 12 OR b = 78

kan INDEX(a,b) niet gebruiken en kan INDEX(a), INDEX(b) al dan niet gebruiken via "index merge". Index samenvoegen is beter dan niets, maar slechts nauwelijks.

WHERE x = 3 OR x = 5

is veranderd in

WHERE x IN (3, 5)

die een index kan gebruiken met x erin.

Subzoekopdrachten

Subquery's zijn er in verschillende smaken en ze hebben een verschillend optimalisatiepotentieel. Merk ten eerste op dat subquery's "gecorreleerd" of "niet-gecorreleerd" kunnen zijn. Gecorreleerd betekent dat ze afhankelijk zijn van enige waarde van buiten de subquery. Dit betekent in het algemeen dat de subquery voor elke buitenwaarde opnieuw moet worden geëvalueerd.

Deze gecorreleerde subquery is vaak redelijk goed. Opmerking: deze moet maximaal 1 waarde retourneren. Het is vaak nuttig als alternatief voor, hoewel niet noodzakelijkerwijs sneller dan, een 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 ...

Dit is meestal niet gecorreleerd:

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

Opmerkingen over de FROM-SELECT :

• Als het 1 rij teruggeeft, geweldig.
• Een goed paradigma (weer "1 rij") is dat de subquery ( SELECT @n := 0 ) , waardoor een `@variable voor gebruik in de rest of de query wordt geïnitialiseerd.
• Als het veel rijen retourneert en de JOIN ook ( SELECT ... ) met veel rijen, dan kan de efficiëntie verschrikkelijk zijn. Pre-5.6, er was geen index, dus werd het een CROSS JOIN ; 5.6+ omvat het afleiden van de beste index op de tijdelijke tabellen en deze vervolgens genereren, alleen om deze weg te gooien wanneer u klaar bent met de SELECT .

DOE MEE + GROEPEN DOOR

Een veelvoorkomend probleem dat tot een inefficiënte zoekopdracht leidt, gaat ongeveer zo:

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

Eerst breidt de JOIN het aantal rijen uit; vervolgens zet de GROUP BY het aantal rijen in a .

Er zijn misschien geen goede keuzes om dit probleem met exploderen en imploderen op te lossen. Een mogelijke optie is om van de JOIN een gecorreleerde subquery te maken in SELECT . Dit elimineert ook de GROUP BY .