MySQL
L'optimisation des performances

Syntaxe

• N'utilisez pas DISTINCT et GROUP BY dans le même SELECT.

• Ne paginez pas avec OFFSET, "rappelez-vous où vous vous êtes arrêté".

• WHERE (a, b) = (22,33) n'optimise pas du tout.

• Dites explicitement ALL ou DISTINCT après UNION - cela vous rappelle de choisir entre le ALL plus rapide ou le DISTINCT plus lent.

• N'utilisez pas SELECT *, surtout si vous n'avez pas besoin de colonnes TEXT ou BLOB. Il y a une surcharge dans les tables tmp et la transmission.

• C'est plus rapide lorsque GROUP BY et ORDER BY peuvent avoir exactement la même liste.

• N'utilisez pas FORCE INDEX; ça peut aider aujourd'hui, mais ça va probablement faire mal demain.

Remarques

Ajouter le bon index

C'est un sujet énorme, mais c'est aussi le plus important problème de "performance".

La principale leçon pour un novice est d'apprendre des index "composites". Voici un exemple rapide:

INDEX(last_name, first_name)

est excellent pour ceux-ci:

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

mais pas pour

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

Définissez le cache correctement

innodb_buffer_pool_size devrait représenter environ 70% de la mémoire RAM disponible.

Évitez les constructions inefficaces

x IN ( SELECT ... )

se transformer en un JOIN

Si possible, évitez OR .

Ne «cache» pas une colonne indexée dans une fonction, telle que WHERE DATE(x) = ... ; reformuler comme WHERE x = ...

Vous pouvez généralement éviter WHERE LCASE(name1) = LCASE(name2) en ayant un classement approprié.

Ne pas utiliser OFFSET pour la "pagination", au lieu de cela "rappelez-vous où vous vous êtes arrêté".

Évitez SELECT * ... (à moins que le débogage).

Note à Maria Deleva, Barranka, Batsu: Ceci est un espace réservé; veuillez supprimer ces éléments lorsque vous construisez des exemples à grande échelle. Une fois que vous avez fait ceux que vous pouvez, je vais aller de l'avant pour élaborer le reste et / ou les lancer.

Les négatifs

Voici certaines choses qui ne sont pas susceptibles d’aider les performances. Ils proviennent d'informations obsolètes et / ou de naïveté.

• InnoDB s'est amélioré au point où il est peu probable que MyISAM soit meilleur.
• PARTITIONing offre rarement des avantages de performance; cela peut même nuire à la performance.
• La définition de query_cache_size supérieure à 100M nuira généralement aux performances.
• L'augmentation du nombre de valeurs dans my.cnf peut conduire à un «échange», ce qui constitue un grave problème de performance.
• Les "index de préfixe" (tels que INDEX(foo(20)) ) sont généralement inutiles.
• OPTIMIZE TABLE est presque toujours inutile. (Et cela implique de verrouiller la table.)

Avoir un INDEX

La chose la plus importante pour accélérer une requête sur une table non minuscule est d'avoir un index approprié.

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

Ne cache pas dans la fonction

Une erreur courante consiste à masquer une colonne indexée dans un appel de fonction. Par exemple, cela ne peut être aidé par un index:

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

Au lieu de cela, INDEX(dt) peut alors utiliser l'index:

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

Cela fonctionne pour DATE , DATETIME , TIMESTAMP et même DATETIME(6) (microsecondes):

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

OU

En général OR tue l'optimisation.

WHERE a = 12 OR b = 78

ne peut pas utiliser INDEX(a,b) , et peut ou non utiliser INDEX(a), INDEX(b) via "index merge". La fusion d'index est meilleure que rien, mais seulement à peine.

WHERE x = 3 OR x = 5

est transformé en

WHERE x IN (3, 5)

qui peut utiliser un index avec x dedans.

Sous-requêtes

Les sous-requêtes existent en plusieurs versions et ont un potentiel d'optimisation différent. Tout d'abord, notez que les sous-requêtes peuvent être "corrélées" ou "non corrélées". Corrélé signifie qu'ils dépendent d'une valeur extérieure à la sous-requête. Cela implique généralement que la sous-requête doit être réévaluée pour chaque valeur externe.

Cette sous-requête corrélée est souvent très bonne. Note: il doit retourner au plus 1 valeur. Il est souvent utile comme alternative à, mais pas nécessairement plus rapide que, un 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 ...

Ceci est généralement non corrélé:

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

Notes sur le FROM-SELECT :

• Si elle retourne 1 ligne, super.
• Un bon paradigme (encore une fois "1 row") est que la sous-requête soit ( SELECT @n := 0 ) , initialisant ainsi une variable @ @ à utiliser dans le reste ou dans la requête.
• Si elle renvoie de nombreuses lignes et que le JOIN est également ( SELECT ... ) avec plusieurs lignes, l'efficacité peut être terrible. Avant le 5.6, il n'y avait pas d'index, donc c'est devenu un CROSS JOIN ; 5.6+ consiste à déduire le meilleur index sur les tables temporaires, puis à le générer, pour le jeter ensuite une fois terminé avec SELECT .

JOIN + GROUP BY

Un problème commun qui conduit à une requête inefficace est quelque chose comme ceci:

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

Tout d'abord, le JOIN développe le nombre de lignes. puis le GROUP BY réduit le nombre de lignes dans a .

Il n'y a peut-être pas de bons choix pour résoudre ce problème d'implosion. Une option possible consiste à transformer JOIN en une sous-requête corrélée dans SELECT . Cela élimine également le GROUP BY .