Indexation et optimisation MySQL

1. Sélectionnez le type de données de l'index

MySQL prend en charge de nombreux types de données. Le choix du type de données approprié pour stocker les données a un grand impact sur les performances. De manière générale, certaines directives peuvent être suivies :

(1) Les types de données plus petits sont généralement meilleurs : les types de données plus petits nécessitent généralement moins d'espace sur le disque, la mémoire et le cache du processeur. Le traitement est plus rapide.
(2) Les types de données simples sont meilleurs : les données entières ont moins de surcharge de traitement que les caractères, car la comparaison des chaînes est plus complexe. Dans MySQL, vous devez utiliser les types de données date et heure intégrés au lieu de chaînes pour stocker l'heure et utiliser des types de données entiers pour stocker les adresses IP.
(3) Essayez d'éviter NULL : la colonne doit être spécifiée comme NOT NULL, sauf si vous souhaitez stocker NULL. Dans MySQL, les colonnes contenant des valeurs nulles sont difficiles à optimiser dans les requêtes car elles compliquent les index, les statistiques d'index et les opérations de comparaison. Vous devez remplacer les valeurs nulles par 0, une valeur spéciale ou une chaîne vide.

1.1. Choisir un identifiant
Choisir un identifiant approprié est très important. Lors du choix, vous devez non seulement tenir compte du type de stockage, mais également de la manière dont MySQL effectue les opérations et les comparaisons. Une fois qu'un type de données est sélectionné, il convient de s'assurer que toutes les tables associées utilisent le même type de données.
(1) Entier : généralement le meilleur choix comme identifiant, car il peut être traité plus rapidement et peut être défini sur AUTO_INCREMENT.

(2) Chaîne : essayez d'éviter d'utiliser des chaînes comme identifiants, elles consomment plus d'espace et sont plus lentes à traiter. De plus, en général, les chaînes sont aléatoires, leur position dans l'index est donc également aléatoire, ce qui peut entraîner des fractionnements de pages, un accès aléatoire au disque et des fractionnements d'index clusterisés (pour les moteurs de stockage utilisant des index clusterisés).

2. Introduction aux index
Pour tout SGBD, les index sont le facteur d'optimisation le plus important. Pour une petite quantité de données, l’impact de ne pas avoir d’index approprié n’est pas grand, mais à mesure que la quantité de données augmente, les performances chuteront fortement.
Si plusieurs colonnes sont indexées (index combiné), l'ordre des colonnes est très important. MySQL ne peut effectuer une recherche efficace que sur le préfixe le plus à gauche de l'index. Par exemple :
Supposons qu'il existe un index combiné it1c1c2(c1,c2) ​​et que l'instruction de requête select * from t1 où c1=1 et c2=2 peut utiliser cet index. L'instruction de requête select * from t1 où c1=1 peut également utiliser cet index. Cependant, l'instruction de requête select * from t1 which c2=2 ne peut pas utiliser cet index car il n'y a pas de colonne de début de l'index combiné. Autrement dit, si vous souhaitez utiliser la colonne c2 pour la recherche, c1 doit être égal à une certaine valeur.

2.1. Type d'index
L'index est implémenté dans le moteur de stockage, pas dans la couche serveur. Par conséquent, les index de chaque moteur de stockage ne sont pas nécessairement exactement les mêmes et tous les moteurs de stockage ne prennent pas en charge tous les types d'index.
2.1.1, index B-Tree
Supposons qu'il existe le tableau suivant :

CREATE TABLE People (
     last_name varchar(50)    not null,
     first_name varchar(50)    not   null,
     dob        date           not null,
     gender     enum('m', 'f') not   null,
     key(last_name, first_name, dob)
);

Son index contient les colonnes last_name, first_name et dob ​​de chaque ligne du tableau. Sa structure est à peu près la suivante :

Les valeurs stockées dans l'index sont classées dans l'ordre dans la colonne d'index. Vous pouvez utiliser l'index B-Tree pour interroger tous les mots-clés, plages de mots-clés et préfixes de mots-clés. Bien sûr, si vous souhaitez utiliser l'index, vous devez vous assurer que vous interrogez par le préfixe le plus à gauche de l'index.
(1) Faire correspondre la valeur complète : spécifiez des valeurs spécifiques pour toutes les colonnes de l'index. Par exemple, l'index dans l'image ci-dessus peut vous aider à trouver Cuba Allen, né le 01/01/1960.
(2) Faites correspondre un préfixe le plus à gauche : vous pouvez utiliser l'index pour trouver la personne dont le nom de famille est Allen, en utilisant uniquement la première colonne de l'index.
(3) Faire correspondre un préfixe de colonne : par exemple, vous pouvez utiliser l'index pour rechercher des personnes dont le nom de famille commence par J. Cela utilise uniquement la première colonne de l'index.
(4) Faites correspondre une plage de valeurs : vous pouvez utiliser l'index pour rechercher des personnes dont le nom de famille est compris entre Allen et Barrymore, et n'utiliser que la première colonne de l'index.
(5) Faites correspondre exactement une partie et faites correspondre une plage sur une autre partie : Vous pouvez utiliser l'index pour trouver des personnes dont le nom de famille est Allen et dont le prénom commence par la lettre K.
(6) Requêtes d'index uniquement : si les colonnes interrogées sont toutes situées dans l'index, il n'est pas nécessaire de lire la valeur du tuple.
Étant donné que les nœuds du B-tree sont stockés séquentiellement, vous pouvez utiliser l'index pour rechercher (trouver certaines valeurs), ou vous pouvez ORDONNER PAR les résultats de la requête. Bien sûr, il existe certaines limites à l'utilisation des index B-tree :
(1) La requête doit commencer à partir de la colonne la plus à gauche de l'index. Ce point a été évoqué à plusieurs reprises. Par exemple, vous ne pouvez pas utiliser un index pour rechercher des personnes nées un certain jour.
(2) Une colonne d'index ne peut pas être ignorée. Par exemple, vous ne pouvez pas utiliser un index pour rechercher une personne dont le nom de famille était Smith et qui est née un certain jour.
(3) Le moteur de stockage ne peut pas utiliser les colonnes à droite de la condition de plage dans l'index. Par exemple, si votre instruction de requête est WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23', la requête utilisera uniquement les deux premières colonnes de l'index car LIKE est une requête par plage. .

Ce qui précède est le contenu de l'indexation et de l'optimisation Mysql. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !