Forums, scripts, cours, services pour webmaster, manuels (PHP, MySQL, HTML...) en français !
PHP / MySql
Recherche
 
ManuelPHP.com
Rendre vos tables aussi compactes que possible
<<<
Comment MySQL utilise les index Index de colonnes
>>>

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 .

<< Comment MySQL utilise les index >>
Rendre vos tables aussi compactes que possible Optimisation de la structure de la base de données Index de colonnes





Google

Services webmasters

Les manuels
 

ManuelPHP.com © 2003-2012 Tigersun - Crédits
Webmaster Site Internet - Télécharger des Logiciels - Programme TV - Entraide Informatique - Agence de Communication
2017-10-21