Optimisation de la base de données MySQL (4) - Optimisation de l'index MySQL

1. Bases de l'index
Type d'index :
1. Index ordinaire : créé dans n'importe quel type de données
2. Index unique : index restreint La valeur doit être unique
3. Index de texte intégral : il ne peut être créé que sur des champs de type char, varchar et texte. Il est principalement utilisé pour améliorer la vitesse des requêtes de texte. Prise en charge du moteur MyISAM.
4. Index à colonne unique : créez un index pour un seul champ dans le tableau
5. Index multi-colonnes : créez un index pour plusieurs champs
6. Index spatial : créé à l'aide de paramètres spatiaux pour fournir le système avec l'efficacité d'obtenir des données de contrôle
Opérations de base de l'index :

CREATE TABLE t_user1(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX (userName)  
             );
             
CREATE TABLE t_user2(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     UNIQUE INDEX index_userName(userName)
             );
           
CREATE TABLE t_user3(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX index_userName_password(userName,PASSWORD)//多列索引
             );
             
CREATE  INDEX index_userName ON t_user4(userName);--对已经创建的表指定索引


CREATE  UNIQUE INDEX index_userName ON t_user4(userName);


CREATE  INDEX index_userName_password ON t_user4(userName,PASSWORD);


ALTER TABLE t_user5 ADD INDEX index_userName(userName);--修改索引


ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);


ALTER TABLE t_user5 ADD INDEX index_userName_password(userName,PASSWORD);


DROP INDEX index_userName ON t_user5;


DROP INDEX index_userName_password ON t_user5;

L'ajout d'un index peut accélérer la requête efficacité et évitez les requêtes de données de table complètes, mais les données cibles sont trouvées en recherchant dans l'index. select t.name from user t où t.id=5, si un index est créé sur la colonne Actor_id, mysql utilise l'index pour trouver la ligne avec id=5, c'est-à-dire qu'il recherche d'abord par valeur sur l'index , puis renvoie toutes les lignes contenant la ligne de données pour la valeur.
L'index peut contenir une ou plusieurs colonnes. Si l'index contient plusieurs colonnes, l'ordre des colonnes est également très important, car MySQL ne peut utiliser efficacement que la colonne de préfixe la plus à gauche de l'index. De plus, la création et la maintenance d'index nécessitent également des ressources système, ce qui implique de créer des index efficaces pour améliorer l'efficacité des requêtes.
2. Type d'index MySQL
De manière générale, l'index de données lui-même est également très volumineux et il est impossible de tout stocker en mémoire, donc l'index est souvent basé sur le fichier d'index Le formulaire est stocké sur le disque. Dans ce cas, la consommation d'E/S disque se produira pendant le processus de recherche d'index, par rapport à l'accès à la mémoire, de sorte que l'organisation structurelle de l'index doit minimiser le nombre d'accès d'E/S disque pendant le processus de recherche. Cela nécessite une structure de données de haute qualité pour organiser et stocker les index de base de données.
Les types d'index généraux utilisent des algorithmes de structure de données. Par exemple, B-Tree est à la fois un type d'index dans MySQL et un arbre de recherche dynamique : arbre de recherche binaire et arbre de recherche binaire équilibré, arbre rouge-noir, structure arborescente binaire. de B-tree/B-tree/B*-tree (B~Tree).
Étant donné que ces types d'index MySQL sont basés sur la mise en œuvre d'algorithmes de structure de données, le développement général commence à être appliqué sur la base. Les applications des index créés sur la base de différentes structures de données sont également différentes.

3. Malentendus courants dans l'utilisation de l'index
1. > Utilisation incorrecte de
 : sélectionnez t.name from user t où t.age 1=5 Pour cette condition age 1=5, mysql ne peut pas analyser automatiquement Même si un index est créé sur la colonne age, mysql n'utilisera pas les requêtes d'indexation, les analyses complètes des tables seront toujours effectuées.
Utilisation incorrecte de  : sélectionnez t.name de l'utilisateur t où TO_DAYS(`CURRENT_DATE`()) -TO_DAYS(date_col)<=10; >
Principe correct : Placez toujours la colonne d'index du côté séparé de l'opérateur de comparaison. 2. Index multi-colonnes

Erreurs courantes : Créez un index indépendant sans colonnes, ou créez plusieurs colonnes dans le mauvais sens. ordre Index, ou indexez simplement toutes les colonnes dans la condition Where.
Utilisation correcte : Sélectionnez l'ordre d'index approprié pour différents types d'index Par exemple, sélectionnez t.name de l'utilisateur t où t.staffId = 2 et custom_id=7;En réponse à la requête ci-dessus, devons-nous créer un (staffId, custom_id) ou inverser l'ordre de ces deux colonnes.

En prenant B-Tree comme exemple, l'ordre des colonnes d'index signifie que l'index sera trié d'abord par la colonne la plus à gauche, de gauche à droite. Il est généralement judicieux de placer les champs présentant la fréquence de conditions de filtre la plus élevée au début de l’index. L'index ainsi conçu peut filtrer les lignes requises le plus rapidement possible.

4.Résumé

J'ai examiné un projet de plate-forme cloud Java sur lequel j'ai travaillé auparavant, en utilisant le framework ORM. À cette époque, le projet connaissait des requêtes en cascade lentes, il combinait donc la programmation de formulaires combinés JDBC ORM. bases de données, et j'ai encore quelques problèmes avec l'optimisation des index. Cela doit être réappliqué. Généralement, les index créés utilisent uniquement des index à colonne unique et sont créés à l'aide de clés primaires. Cela ne pose pas de gros problème, mais si le temps de requête est atteint. Des problèmes de consommation surviennent, l'optimisation de la structure des tables, l'optimisation des index et l'optimisation des requêtes doivent aller de pair, mais s'appuyer sur la re-sélection ou la ré-optimisation du cadre ORM ne peut pas résoudre le problème.

Ce qui précède est le contenu de l'optimisation de la base de données MySQL (4) - Optimisation de l'index MySQL Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !