20 Résumé de l'optimisation de MySQL

Le contenu de cet article concerne le résumé de l'optimisation de MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

Avant-propos

De nos jours, les opérations de base de données deviennent de plus en plus le goulot d'étranglement des performances de l'ensemble de l'application, en particulier pour les applications Web. Par conséquent, j'ai compilé quelques suggestions pour l'optimisation de MySQL. J'espère que ces techniques d'optimisation vous seront utiles. Si vous ne parvenez pas à les résumer, vous êtes invités à les ajouter.

Raisons de l'exécution lente de SQL

1. Vitesse du réseau lente, mémoire insuffisante, faible débit d'E/S, espace disque plein et autres problèmes matériels

2. Il n'y a pas d'index ou l'index n'est pas valide

3. Il y a trop d'enregistrements de données dans la table de données

4. Réglage du serveur et divers réglages de paramètres Cela peut également affecter l'efficacité du SQL écrit par les développeurs

6. Autres

1. EXPLAIN Analysez votre requête SELECT

Dans de nombreux cas, l'utilisation du mot-clé EXPLAIN peut vous permettre de savoir comment MySQL traite votre instruction SQL, ce qui peut vous aider à analyser votre instruction de requête et peut-être à trouver des optimisations dès que possible. . méthodes et problèmes de performances potentiels. Pour l'utilisation spécifique d'EXPLAIN et la signification de chaque paramètre, veuillez vous référer aux documents concernés.

2. La requête SELECT doit spécifier le nom du champ

La requête SELECT * ajoutera beaucoup de consommation inutile (comme le CPU, les E/S, etc.). cela peut également accroître l’utilisation d’index de couverture. Par conséquent, lors de l'exécution d'une requête SELECT, il est nécessaire de spécifier directement le nom du champ correspondant à interroger à la fin.

3. Lors de l'interrogation d'une donnée, utilisez LIMIT 1

pour réduire les requêtes redondantes, car après avoir spécifié la limite 1, la requête ne continuera plus après l'interrogation d'une donnée, ce qui rend le colonne de type dans EXPLAIN Pour atteindre le type const, l'instruction de requête est meilleure.

4. Créez un index pour le champ WHERE recherché

Généralement, nous définirons une clé primaire pour chaque table, et l'index n'est pas nécessairement la clé primaire. S'il y a un champ dans votre table que vous utilisez toujours pour les requêtes et les recherches WHERE, et qu'il est plus lu qu'écrit, veuillez créer un index pour celui-ci. Si vous souhaitez en savoir plus sur les principes de l'indexation, vous pouvez obtenir des informations pertinentes. être trouvé.

5. N'utilisez jamais ORDER BY RAND()

Si vous souhaitez obtenir des données de manière aléatoire, peut-être que le premier vous dira directement d'utiliser des nombres aléatoires. N'oubliez pas qu'à ce moment, vous devez contrôler. votre cerveau de continuer à penser dans cette direction et d'arrêter cette terrible pensée. Car ce type de requête n'a aucun bénéfice sur les performances de la base de données (consommateur de CPU). L'une des meilleures solutions consiste à trouver d'abord le nombre N de données, puis à utiliser LIMIT N, 1 pour effectuer une requête comme celle-ci.

6. Assurez-vous que chaque table a un identifiant de clé primaire

Nous devons développer une habitude chaque fois que nous créons une nouvelle table, nous devons concevoir un champ d'identification pour celle-ci et en faire le. clé primaire, de préférence de type INT (certains utilisent également UUID), et définissez le champ ID sur l'indicateur AUTO_INCREMENT.

8. Utilisez NOT NULL autant que possible

Ne pensez pas que NULL n'a pas besoin d'espace. Le fait est que NULL a également besoin d'espace supplémentaire. Peut-être que beaucoup de gens ne l'ont pas remarqué mais l'ont fait. Je l'ai rencontré. Les champs NULL sont dans Lors de l'interrogation et de la comparaison, c'est plus gênant. Bien sûr, si vous avez vraiment besoin de NULL, alors pas de problème, utilisez-le simplement. Sinon, il est recommandé d'utiliser NOT NULL.

8. Choisissez le moteur de stockage approprié

Il existe deux moteurs de stockage, MyISAM et InnoDB, dans MySQL. Les deux ont leurs propres avantages et inconvénients, nous devons donc comprendre les différences entre les deux. puis prenez la meilleure décision. Des choix appropriés, par exemple, InnoDB prend en charge les transactions mais pas MyISAM, les requêtes MyISAM sont plus rapides qu'InnoDB, etc. en bref, si vous ne savez pas quoi choisir, utilisez InnoDB.

9. Enregistrez l'adresse IP sous UNSIGNED INT

Lorsque vous rencontrez le besoin de stocker l'adresse IP, la première pensée de nombreuses personnes est de stocker le type de chaîne VARCHAR (15), et vous ne le feriez pas. pensez à utiliser le type entier INT pour le stocker ; si vous utilisez le type entier pour le stocker, cela ne prend que 4 octets et vous pouvez avoir des champs de longueur fixe, ce qui vous apportera des avantages dans les requêtes.

10. Essayez de ne pas juger la valeur nulle d'un champ lors de la requête WHERE

Nous savons tous que lorsque nous jugeons un champ comme nul, cela sera plus lent. un jugement amènera le moteur à abandonner l’utilisation de tous les index existants et à effectuer une recherche par analyse de table complète.

11. Essayez de ne pas utiliser de requête floue avec le préfixe % LIKE

Requête floue, nous la rencontrons souvent dans le développement quotidien, mais je pense que beaucoup de gens utilisent directement LIKE '%key_word%' ou LIKE '%key_word' Rechercher dans de cette façon, ces deux méthodes de recherche entraîneront l'échec de l'index et effectueront une recherche par analyse de table complète. Si vous souhaitez résoudre la requête floue ci-dessus, la réponse est d'utiliser "utiliser l'index de texte intégral". Si vous êtes intéressé par une utilisation spécifique, vous pouvez vérifier les informations vous-même.

12. Évitez d'effectuer des opérations d'expression sur les champs lors des requêtes WHERE

Par exemple, dans l'instruction de requête SELECT id FROM table WHERE num * 2 = 50;, une telle requête effectuera une opération arithmétique consistant à multiplier le numéro du champ par 2. Provoque un échec de l’index.

14. Réduisez les tris inutiles

Les opérations de tri consommeront plus de ressources CPU, donc la réduction des tris inutiles peut réduire les performances SQL lorsque le taux de réussite du cache est élevé et le temps de réponse des E/S est suffisant.

14. Il est recommandé d'utiliser JOIN au lieu d'une sous-requête

Certaines personnes diront que les performances de JOIN ne sont pas très bonnes, mais elles présentent toujours de grands avantages en termes de performances par rapport à la sous-requête. Plus précisément, vous pouvez en apprendre davantage sur les problèmes liés au plan d’exécution d’une sous-requête.

15. Évitez la conversion de type implicite

La conversion de type fait principalement référence à la conversion de type qui se produit lorsque le type du champ dans la clause WHERE est incohérent avec le type du paramètre transmis ; Parce que si le type de données que nous transmettons n'est pas cohérent avec le type de champ, MySQL peut effectuer des opérations de conversion de type sur les données que nous transmettons, ou il peut ne pas les traiter et les transmettre directement au moteur de stockage pour traitement. , l'index peut ne pas pouvoir être traité. Problèmes de plan d'exécution causés par les conditions d'utilisation.

16. Évitez les types de champs incohérents dans les requêtes multi-tables

Lorsque nous rencontrons une requête conjointe de plusieurs tables, lorsque nous concevons la structure de la table, essayez de garder les champs associés de la table cohérents avec la table et les index doivent être définis. Dans le même temps, lorsque vous effectuez des requêtes de connexion multi-tables, essayez d'utiliser la table avec un petit jeu de résultats comme table de pilotage.

17. Il est recommandé d'activer le cache de requêtes

La plupart des serveurs MySQL ont le cache de requêtes activé. C'est l'un des moyens les plus efficaces d'améliorer les performances, car le cache de requêtes est activé. automatiquement traité par le moteur de base de données MySQL. Lorsque plusieurs des mêmes requêtes sont exécutées plusieurs fois, ces résultats de requête seront placés dans un cache, de sorte que les requêtes identiques suivantes n'auront pas besoin d'exploiter la table mais d'accéder directement aux résultats mis en cache.

18. Utilisez UNION au lieu d'une table temporaire

La requête UNION peut fusionner deux ou plusieurs résultats de requête SELECT en une seule requête, il n'est donc pas nécessaire de créer une table temporaire pour la terminer. Il convient de noter que le nombre de champs dans toutes les instructions SELECT utilisant UNION doit être le même.

19. Utilisez les requêtes IN avec prudence

Soyez prudent avec les requêtes IN et NOT IN, car elles peuvent conduire à une analyse complète de la table, et pour les valeurs continues, n'utilisez pas IN si vous le pouvez. utiliser ENTRE.

20. Bienvenue pour ajouter

Conclusion

Il s'agit principalement de considérer l'optimisation du point de vue de la requête, ainsi que certaines sous-tables, la technologie de partitionnement et la lecture-écriture. séparation ; l'optimisation ci-dessus. Si les points mentionnés ci-dessus ne sont pas en place, veuillez comprendre qu'il existe de nombreux endroits où MySQL peut être optimisé. D'autres suggestions d'optimisation sont les bienvenues, merci.

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!