5.4 Optimisation de la structure de la base de données
5 Optimisation de MySQL
Manuel de Référence MySQL 4.1 : Version Française
. Conception
. Rendre vos tables aussi compactes que possible
->Comment MySQL utilise les index
. Index de colonnes
. Index sur plusieurs colonnes
. Pourquoi tant de tables ouvertes ?
. Quand MySQL ouvre et ferme les tables
. Inconvénients de la création d'un grand nombre de tables dans la même base de données
|
5.4.3 Comment MySQL utilise les index
Les index sont utilisés pour trouver des lignes de résultat avec une
valeur spécifique, très rapidement. Sans index, MySQL doit lire successivement
toutes les lignes, et à chaque fois, faire les comparaisons nécessaires
pour extraire un résultat pertinent. Plus la table est grosse, plus
c'est coûteux. Si la table dispose d'un index pour les colonnes
utilisées, MySQL peut alors trouver rapidement les positions des lignes
dans le fichier de données, sans avoir à fouiller toute la table.
Si une table à 1000 lignes, l'opération sera alors 100 fois plus rapide
qu'une lecture séquentielle. Notez que si vous devez lire la presque
totalité des 1000 lignes, la lecture séquentielle se révélera alors plus
rapide, malgré tout.
Tous les index de MySQL (
PRIMARY
,
UNIQUE
et
INDEX
) sont stockés sous la forme de B-trees. Les chaînes sont automatiquement
préfixée et leurs espaces terminaux sont supprimés. Syntaxe de
CREATE INDEX
.Les index sont utilisés pour :
-
Trouver rapidement des lignes qui satisfont une clause
WHERE
.
-
Lire des lignes dans d'autres tables lors des jointures.
-
Trouver les valeurs
MAX()
et
MIN()
pour une colonne indexée.
C'est une opération qui est optimisée par le préprocesseur, qui vérifie
si vous utilisez la constante
WHERE
key_part_# = sur toute les parties
de clés inférieures à < N. Dans ce cas, MySQL va faire une simple recherche
de clé et remplacer l'expression par une constante. Si toutes les expressions
sont remplacées par des constantes, la requête va alors être rapidement calculée :
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
|
-
Trier ou grouper des lignes dans une table, si le tri ou le regroupement
est fait avec un préfixe à gauche utilisable (par exemple,
ORDER BY key_part_1,key_part_2
). La clé est lue en ordre inverse,
si toutes les parties de clés sont suivies du mot clé
DESC
.
Comment MySQL optimise
ORDER BY
.
-
Dans certains cas, la requête peut être optimisée pour lire des valeurs
sans consulter le fichier de données. Si cette possibilité est utilisée
avec des colonnes qui sont toutes numériques, et forme le préfixe de
gauche d'une clé, les valeurs peuvent être lues depuis l'index, à grande
vitesse :
SELECT key_part3 FROM table_name WHERE key_part1=1
|
Supposez que vous utilisiez la commande
SELECT
suivante :
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
|
Si un index multi-colonne existe sur les colonnes
col1
et
col2
,
les lignes appropriées seront directement lues. Si des index séparés sur les
colonnes
col1
et
col2
existent, l'optimiseur va essayer
de trouver l'index le plus restrictif des deux, en décidant quel index
débouche sur le moins de lignes possibles.
Si une table a un index multi-colonne, tout préfixe d'index peut être
utilisé par l'optimiseur pour trouver des lignes. Par exemple, si vous
avez un index à trois colonnes
(col1,col2,col3)
, vous pouvez faire
des recherches accélérées sur les combinaisons de colonnes
(col1)
,
(col1,col2)
et
(col1,col2,col3)
.
MySQL ne peut utiliser d'index partiel sir les colonnes ne forment pas un
préfix d'index. Supposez que vous avez la commande
SELECT
suivante :
mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
|
Si un index existe sur les colonnes
(col1,col2,col3)
, seule la première requête
pourra utiliser l'index ci-dessus. Les deux autres requêtes utilisent des
colonnes indexées, mais les colonnes
(col2)
et
(col2,col3)
ne font
pas partie du préfixe des colonnes
(col1,col2,col3)
.
MySQL utilise aussi les index lors des comparaisons avec l'opérateur
LIKE
si l'argument de
LIKE
est une chaîne constante qui ne commence
pas par un caractère joker. Par exemple, les requêtes
SELECT
suivantes
utilisent des index :
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";
|
Dans le premier exemple, seules les lignes avec
"Patrick" <= key_col < "Patricl"
sont considérées. Dans le second
exemple,
"Pat" <= key_col < "Pau"
sont considérées.Les commandes
SELECT
suivantes n'utilisent pas d'index :
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;
|
Dans la première requête, la valeur associée à
LIKE
commence avec
un caractère joker. Dans le second exemple, la valeur associée à
LIKE
n'est pas une valeur constante.MySQL 4.0 fait une autre optimisation avec l'opérateur
LIKE
. Si vous
utilisez
... LIKE "%string%"
et que
string
est plus grand que 3 caractères,
MySQL va utiliser l'algorithme
Turbo Boyer-Moore
qui prend une valeur initiale
pour résoudre le masque, et l'exploite pour accélérer la recherche.
Les recherches qui utilisent la fonction
column_name IS NULL
vont
utiliser les index si
column_name
sont des index.
MySQL normalement utilise l'index qui génère le moins de lignes possible.
Un index est utilisé avec les colonnes que vous spécifiez, et les opérateurs
suivants :
=
,
>
,
>=
,
<
,
<=
,
BETWEEN
et l'opérateur
LIKE
sans préfixe joker, c'est à dire de la forme
'quelquechose%'
.
Aucun n'index qui ne s'applique pas à tous les niveaux de
AND
dans une
requête
WHERE
, ne sera pas utilisé pour optimiser la requête. En d'autres
termes, pour être capable d'utiliser un index pour optimiser une requête,
un préfixe de l'index doit être utilisé dans toutes les parties de la
formule logique contenant
AND
.
Les clauses
WHERE
suivantes utilisent des index :
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
/* optimisé par "index_part1='hello'" */
... WHERE index1=1 and index2=2 or index1=3 and index3=3;
/* peut utiliser un index sur index1 mais pas sur index2 ou index 3 */
|
Ces clauses
WHERE
n'utilisent
PAS
d'index :
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 n'est pas utilisé */
... WHERE index=1 OR A=10 /* Index n'est pas utilisé sur les deux parties du AND */
... WHERE index_part1=1 OR index_part2=10 /* Aucun index ne s'applique à toutes les colonnes */
|
Notez que dans certains cas, MySQL ne va pas utiliser un index, même si
il en reste de disponible. Voici certains de ces cas :
-
Si l'utilisation de l'index requiert que MySQL accède à plus de
30% des lignes de la table (dans ce cas, un scan de table
est probablement plus rapide, et demandera moins d'accès disques).
Notez que si une telle requête utilise la clause
LIMIT
pour
ne lire qu'une partie des lignes, MySQL utilisera tout de même l'index,
car il va trouver plus rapidement les quelques lignes de résultat.
-
Si un intervalle d'index contient des valeurs
NULL
et que vous utilisez
la clause
ORDER BY ... DESC
.
|
