L'optimisation MySQL comprend trois aspects

La base de données est devenue une dépendance sous-jacente indispensable pour les applications Internet, parmi lesquelles MySQL est plus largement utilisé comme base de données open source. Récemment, je me suis concentré sur le développement de projets de projet. J'ai résumé certains principes d'optimisation de bases de données utilisés dans le processus de développement, dans l'espoir d'aider davantage de développeurs d'applications à mieux utiliser la base de données MySQL.

L'optimisation de MySQL comprend principalement trois aspects Le premier est l'optimisation des instructions SQL, et le second est le. optimisation de la structure des tables. Il s'agit principalement de l'optimisation des index, et enfin de l'optimisation de la configuration du serveur.

1. Optimisation de SQL instruction

1) L'instruction de requête doit d'abord essayer d'éviter l'analyse complète de la table Envisagez de créer des index sur la clause Where et la clause OrderBy, mais chaque instruction SQL n'utilisera qu'un seul index au maximum, et créer trop d'index Cela entraîne une surcharge lors de l'insertion et de la mise à jour. Dans le même temps, pour les champs avec peu de distinction, vous devriez essayer d'éviter de créer des index. Vous pouvez utiliser le mot-clé explain avant l'instruction de requête pour afficher le SQL. Instruction . Plan d'exécution pour déterminer si l'instruction de requête utilise un index

2) doit essayer d'utiliser EXIST et NOT EXISTRemplacez IN ​​​​et NOT IN, car ce dernier est susceptible d'entraîner l'abandon de l'utilisation de l'index par l'analyse complète de la table

3) Vous devriez essayer d'éviter le jugement NULL sur les champs de la clause Where, car le NULL le jugement mènera à une analyse complète de la table

4) Vous devriez essayer d'éviter d'utiliser ou comme condition de jointure dans le Where, car elle conduira également à une analyse complète de la table

5)

doit être évitée dans le Où clause ! Les opérateurs = ou <> provoqueront également une analyse complète de la table ;

6) L'utilisation de like "%abc%" ou like "%abc" entraînera également une analyse complète du tableau, tandis que like « abc% » utilisera l'index.

7) Lorsque vous utilisez l'opérateur Union, vous devez vous demander si vous pouvez utiliser Union ALL à la place, car Lorsque l'opérateur Union effectue une opération de tri sur les résultats et supprime les enregistrements en double, Union ALL doit être utilisé pour les applications qui n'ont pas cette exigence. Ces dernières simplement les résultats sont fusionnés et. renvoyé, ce qui peut grandement améliorer les performances ;

8) devrait essayer d'éviter d'utiliser des opérateurs d'expression dans la clause Where car cela mènera. à une analyse de table complète ;

9) Vous devriez essayer d'éviter d'utiliser des fonctions sur les champs dans la clause Where, car cela entraînera également cause Analyse complète de la table

10) Sélectionner Essayez de éviter d'utiliser "*" , car pendant le processus d'analyse de l'instruction SQL, "*" sera converti en noms de colonnes de toutes les colonnes, et ce travail est complété en interrogeant le dictionnaire de données, il y a un certain Overhead ;

11) Dans la clause Where, la condition de connexion à la table doit être écrite avant les autres conditions, car l'analyse du Where est De l'arrière vers l'avant, alors essayez de mettre les restrictions qui peuvent filtrer la plupart des enregistrements à la fin de la clause Où

 ; 12) S'il existe un index conjoint tel que index(a,b,c) sur la table de la base de données, l'ordre d'apparition des champs de condition dans la clause Where doit être le même que l'ordre d'apparition des champs d'index Cohérent, sinon l'index conjoint ne peut pas être utilisé

13) L'ordre d'apparition des tables dans le De ; La clause affectera également l'ordre de l'instruction SQL Les performances d'exécution sont affectées. La clause From est analysée de l'arrière vers l'avant, c'est-à-dire le tableau écrit à la fin. sera traitée en premier. La table avec moins d'enregistrements doit être sélectionnée comme table de base et placée plus tard. S'il y a 3 et 3 ou plus de requêtes de connexion à la table, la table croisée doit être traitée en premier. utilisé comme table de base

14) Essayez d'utiliser l'opérateur >= au lieu de l'opérateur >, par exemple, le SQL suivant instruction, select dbInstanceIdentifier from DBInstance Where id > 3, cette instruction doit être remplacée par select dbInstanceIdentifier from DBInstance Where id >=4 , les résultats d'exécution des deux instructions sont les pareil, mais les performances sont différentes, ce dernier est plus efficace, car lorsque le premier est exécuté, il trouvera d'abord l'enregistrement égal à 3, puis avancera, tandis que le second localisera directement l'enregistrement égal à 3 à 4.

2. Optimisation de la structure des tables

Cela fait principalement référence à la façon de créer correctement des index, car des index déraisonnables conduiront à interroger l'intégralité L'analyse des tables et un trop grand nombre d'index entraîneront une surcharge de performances pour l'insertion et la mise à jour

1) Tout d'abord, assurez-vous que chaque élément ; est clair Les instructions SQL ne peuvent utiliser qu'un seul index au maximum. S'il existe plusieurs index pouvant être utilisés, le système sélectionnera un index à exécuter en fonction du coût d'exécution

2) Pour la table Innodb, bien que le système génère automatiquement une colonne de clé primaire si l'utilisateur ne spécifie pas de clé primaire, la La colonne de clé primaire générée automatiquement présente plusieurs problèmes 1 Performances insuffisantes, impossible d'utiliser le cache pour lire 2.Concurrence insuffisante, toutes les tables sans clé primaire dans la colonne ; Le système partage une colonne globale Auto_Increment. Par conséquent, pour toutes les tables dans InnoDB, la clé primaire doit être spécifiée lors de la création de la table.

3) Ne créez pas d'index pour des champs peu distinctifs

4) Il vous suffit de créer un index pour un champ. Il n'est pas nécessaire de créer un index unique et de créer un index INDEX.

5) Pour les champs de texte volumineux ou les champs BLOB, ne créez pas d'index

6) Le champ de connexion de la requête de connexion doit être indexé

7) Le champ de tri doit généralement être indexé ; ;

8) Les champs statistiques de groupe doivent généralement être indexés

9) Utilisez correctement les index conjoints. Le premier champ de l'index conjoint peut être utilisé seul. Par exemple, l'index conjoint suivant index(userID,dbInstanceID), les instructions de requête suivantes peuvent utiliser cet index, select. dbInstanceIdentifier from DBInstance Where userID=? , mais l'instruction select dbInstanceIdentifier from DBInstance Where dbInstanceID=? ne peut pas utiliser cet index

10) Les index sont généralement utilisés pour les tables avec de nombreux enregistrements. S'il existe une table DBInstance, toutes les requêtes auront le champ de condition userID. On sait actuellement que ce champ peut distinguer. enregistre très bien, c'est-à-dire que chaque requête Il n'y a pas beaucoup d'enregistrements sous un seul userID, donc la table n'a besoin que de créer un index sur userID Même si d'autres champs conditionnels sont utilisés, puisque chaque userIDIl n'y a pas beaucoup de données d'enregistrement correspondantes, il n'y a donc fondamentalement aucun impact si d'autres champs ne sont pas indexés. En même temps, la surcharge de performances liée à l'insertion et à la mise à jour causée par l'établissement d'un trop grand nombre d'index peut être augmentée. évité;

3． MySQLoptimisation de la configuration du serveur

Optimisation 1) Le serveur MySQL

dispose d'un journal de connexion lente, qui peut enregistrer les instructions de requête qui dépassent un certain intervalle de temps et n'utilisent pas d'index pour faciliter le suivi des développeurs. Activez et désactivez la fonction de journal de connexion lente en définissant

slow_query_log=ON/. OFF, slow_query_log_file définit le nom du fichier du journal de connexion lente, long_query_time définit le délai d'attente, l'unité est ms,Faites attention à la connexion lente logMySQL est désactivé par défaut

2) MySQL a une fonction de cache de requêtes. Le serveur enregistrera les instructions de requête et les résultats de retour correspondants pour réduire la surcharge du serveur causée par la même requête. Vous pouvez définir la requête en définissant query_cache_size <.> La taille du cache, 0 signifie désactiver le cache de requêtes, mais il convient de noter qu'une fois la table mise à jour, tous les caches de requêtes seront invalides, MySQL désactive la requête En cache

3)

Vous pouvez définir le nombre maximum de connexions à la base de données en configurant max_connections, wait_timeout pour définir le nombre maximum de connexions Temps de rétention long, l'unité de temps est s, MySQL par défaut est 8 heures, une fois qu'elle dépasse 8 heures, la base de données déconnectera automatiquement la connexion, vous devez faire attention lorsque vous utilisez le pool de connexions à la base de données, car la connexion dans le pool de connexions peut avoir été déconnectée par le serveur à ce moment-là, et le pool de connexions ne le sait pas. l'application fera une erreur lorsqu'elle obtiendra la connexion du pool de connexions et l'utilisera max_connect_errorsConfigurez si l'application rencontre plusieurs exceptions, la connexion de l'hôte à la base de données sera interrompue ;

【Recommandations associées】

1

Tutoriel vidéo en ligne MySQL gratuit

2.

Dernier tutoriel manuel MySQL

3 .

Choses sur la conception de bases de données

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!