Table des matières
1. Introduction
2. Intercepter les instructions SQL
3. Commandes d'analyse de base pour l'optimisation des requêtes
4. Plusieurs directions pour l'optimisation des requêtes
5. Optimisation de l'index
5.1 Avantages de l'index :
5.2 Inconvénients de l'index
5.3 Sélection de l'index
5.4. Etude détaillée de l'index
6. Optimisation des sous-requêtes
7. Réécrire le prédicat équivalent :
8. Simplification et optimisation conditionnelles
9. Optimisation des connexions externes
10. Autre optimisation des requêtes
Maison base de données tutoriel mysql Introduction détaillée à l'optimisation des requêtes MySQL

Introduction détaillée à l'optimisation des requêtes MySQL

Mar 26, 2017 am 11:46 AM

1. Introduction

La chose la plus importante pour une bonne application Web est d'avoir d'excellentes performances d'accès. La base de données MySQL fait partie intégrante de l'application Web et constitue un élément important qui détermine ses performances. Il est donc crucial d’améliorer les performances de MySQL.

L'amélioration des performances de MySQL peut être divisée en trois parties, à savoir le matériel, le réseau et les logiciels. Parmi eux, le matériel et le réseau dépendent des ressources financières de l'entreprise et nécessitent beaucoup d'argent, je ne les aborderai donc pas ici. Le logiciel est subdivisé en plusieurs types. Ici, nous obtenons une amélioration des performances grâce à l'optimisation des requêtes MySQL.

Récemment, j'ai lu des livres sur l'optimisation des requêtes, ainsi que des articles écrits par des seniors en ligne.

Voici quelques résumés sur l'optimisation des requêtes que j'ai compilés et dont j'ai appris :

2. Intercepter les instructions SQL

1. Journal complet des requêtes

2. . Journal des requêtes lentes

3. Journal binaire

4. Liste des processus

AFFICHER LA LISTE COMPLÈTE DES PROCESSUS ;

. . .

3. Commandes d'analyse de base pour l'optimisation des requêtes

1. EXPLAIN {PARTITIONS|EXTENDED}

2. AFFICHER l'onglet CRÉER TABLE ;

3. AFFICHER INDEX DE l'onglet ;

 4. AFFICHER LE STATUT DE LA TABLE COMME 'onglet';

 5. AFFICHER LE STATUT [GLOBAL|SESSION] COMME '';

 6. AFFICHER LES VARIABLES

​. . . .

ps : J'ai personnellement l'impression qu'ils sont tous nutritionnellement dépourvus de tout nutriment. Voici les vraies choses.

4. Plusieurs directions pour l'optimisation des requêtes

1. Essayez d'éviter l'analyse de texte intégral, ajoutez des index aux champs correspondants et utilisez des index pour interroger

2. Supprimez les index inutilisés. ou des index en double

3. Réécriture de requêtes, conversion équivalente (prédicat, sous-requête, requête de jointure)

4. Supprimer les instructions inutiles qui répètent le contenu et rationaliser les instructions

5. Intégrer déclarations exécutées à plusieurs reprises

6. Mettre en cache les résultats des requêtes

5. Optimisation de l'index

5.1 Avantages de l'index :

1. Maintenir l'intégrité des données

  2. Améliorer les performances des requêtes de données

3. Améliorer les opérations de connexion aux tables (jion)

4. Trier les résultats des requêtes. S'il n'y a pas d'index, l'algorithme de tri des fichiers interne sera utilisé pour le tri, ce qui est plus efficace

5. Simplifier l'opération d'agrégation des données

5.2 Inconvénients de l'index

. 1. L'index doit occuper une certaine quantité d'espace Espace de stockage

2. L'insertion, la mise à jour et la suppression de données seront affectées par l'index et les performances seront réduites. Parce que les données changent, l'index doit également être mis à jour

3. Plusieurs index, si l'optimiseur prend du temps, le meilleur choix est

5.3 Sélection de l'index

1. . Lorsque la quantité de données est importante, utilisez

2. Lorsque les données sont très répétitives, n'utilisez pas

3. Si la requête récupère plus de 20 % des données, texte intégral. le scanning sera utilisé sans indexation

5.4. Etude détaillée de l'index

Requête de données :

InnoDB et MyISAM dans MySQL sont des index de type B-Tree

B-Tree comprend : PRIMARY KEY, UNIQUE, INDEX et FULLTEXT

L'index de type B-Tree n'est pas pris en charge (c'est-à-dire que lorsque le champ utilise les symboles suivants, l'index ne sera pas utilisé) :

 >, <, >=, <=, BETWEEN, !=, < >,like '%**'

 【Permettez-moi d'abord de vous présenter l'index de couverture】

 Permettez-moi de le présenter d'une manière que je comprends. Les index de couverture n'existent pas vraiment comme les index de clé primaire et les index uniques. Il s'agit simplement d'une définition de certains scénarios spécifiques d'application d'index [une autre compréhension : la colonne interrogée est une colonne d'index, donc la colonne est couverte par l'index]. Il peut dépasser les limitations traditionnelles, utiliser les opérateurs ci-dessus et continuer à utiliser des index pour les requêtes.

Étant donné que la colonne de requête est une colonne d'index, il n'est pas nécessaire de lire la ligne, seules les données du champ de la colonne doivent être lues. [Par exemple, si vous lisez un livre et avez besoin de trouver un certain contenu, et que ce contenu apparaît dans la table des matières, alors vous n'avez pas besoin de tourner page par page, localisez simplement la page dans la table des matières. contenu et recherche]

Comment activer Qu'en est-il de la couverture des index ? Qu'est-ce qu'un scénario spécifique ?

Le champ d'index apparaît simplement dans la sélection.

L'indice composé peut également avoir d'autres scénarios spéciaux. Par exemple, pour un index composite à trois colonnes, il suffit que la colonne la plus à gauche de l'index composite apparaisse une fois dans select,where, group by et order by pour activer l'utilisation de l'index de couverture.

Vue :

Extra dans EXPLAIN affiche Using index, indiquant que cette instruction utilise un index de couverture.

Conclusion :

Il n'est pas recommandé d'utiliser select*from lors de l'interrogation. Vous devez écrire les champs requis et ajouter les index correspondants pour améliorer les performances des requêtes.

Résultats de mesure réels pour les opérateurs ci-dessus :

1. Sous la forme de select*from, la clé primaire dans où peut être utilisée pour tuer [sauf comme] (utilisez la clé primaire pour requête); l'index ne fonctionne pas du tout Can.

2. Test sous forme de champ de sélection a partir de l'onglet où champ un "opérateur ci-dessus", le résultat peut toujours être interrogé à l'aide de l'index. [Utilisation de l'index de couverture]

  Autres méthodes d'optimisation d'index :

1. Utiliser des mots-clés d'index comme conditions de connexion

2. Utiliser des index composites

3. La fusion d'index ou et, impliquera Les champs impliqués sont fusionnés dans un index composite

4. Ajouter un index aux champs impliqués dans Where, et regrouper par

6. Optimisation des sous-requêtes

Dans from, c'est une sous-requête non corrélée peut être extraite vers la couche parent. Dans les requêtes de jointure multi-tables, tenez compte du coût de jointure avant de sélectionner.

L'optimiseur de requêtes utilise généralement une exécution imbriquée pour les sous-requêtes, c'est-à-dire qu'elle exécute la sous-requête une fois pour chaque ligne de la requête parent, de sorte que la sous-requête soit exécutée plusieurs fois. Cette méthode d'exécution est très inefficace.

Avantages de la conversion des sous-requêtes en requêtes de jointure :

1. La sous-requête n'a pas besoin d'être exécutée plusieurs fois

2. L'optimiseur peut choisir différentes méthodes et ordres de connexion en fonction sur les informations

3. Les conditions de connexion et les conditions de filtrage de la sous-requête deviennent les conditions de filtrage de la requête parent pour améliorer l'efficacité.

Optimisation :

Fusion de sous-requêtes S'il existe plusieurs sous-requêtes, essayez de les fusionner autant que possible.

Expansion de sous-requête, c'est-à-dire que le pull-up devient une requête multi-tables (les modifications équivalentes sont garanties à tout moment)

Remarque :

L'expansion de sous-requête ne peut s'étendre que de manière simple requêtes. Si la sous-requête Si la requête contient des fonctions d'agrégation, GROUP BY et DISTINCT, elle ne peut pas être extraite.

sélectionnez * dans t1 (sélectionnez*dans l'onglet où id>10) comme t2 où t1.age>10 et t2.age<25;

sélectionnez*dans t1,onglet comme t2 où t1.age>10 et t2.age<25 et t2.id>10;

Étapes spécifiques :

1. Fusionner de et depuis, modifier les paramètres correspondants

2 , fusionner où avec où, utiliser et pour connecter

3. Modifier le prédicat correspondant (in est changé en =)

7. Réécrire le prédicat équivalent :

1 . ENTRE ET Réécrivez-le comme >=, <= et ainsi de suite. Mesure réelle : 100 000 données, temps avant et après réécriture, 1,45 s, 0,06 s

 2. En convertit plusieurs ou. Lorsque le champ est un index, les deux peuvent utiliser l'index, ou est plus efficace que dans

3. Le nom comme 'abc%' est réécrit comme nom>='abc' et nom<'abd';

Remarque : dans le test de données au niveau d'un million, like est plus rapide que cette dernière requête avant que le nom ne soit indexé après avoir ajouté un index au champ, cette dernière requête est un peu plus rapide, mais il n'y a pas beaucoup de différence, car les deux méthodes sont utilisées lors de l'interrogation de l'index.

. . . .

8. Simplification et optimisation conditionnelles

1. Combinez où, avoir (quand il n'y a pas de fonctions groupby et d'agrégation) et conditions de jointure autant que possible

2. Supprimez les parenthèses inutiles, réduisez les couches de syntaxe ou et et et réduisez la consommation du processeur

3. Transfert constant. a=b et b=2 sont convertis en a=2 et b=2. Essayez de ne pas utiliser les variables a=b ou a=@var

4. Éliminez les conditions SQL inutiles

5. Essayez de ne pas calculer d'expressions à droite du signe où égal ; utilisez des champs dans lesquels Calculer des expressions et utiliser des fonctions

6. Transformation d'identité et transformation des inégalités. Exemple : tester des millions de données a>b et b>10 devient a>b et a>10 et b>10 avec une optimisation significative

9. Optimisation des connexions externes

C'est-à-dire convertir des données externes connexions Pour les jointures internes

Avantages :

1. Le processeur d'optimisation gère les jointures externes avec plus d'étapes que les jointures internes et prend du temps

2. Une fois les jointures externes éliminé, l'optimiseur sélectionne plusieurs tables Il y a plus de choix pour la séquence de connexion, vous pouvez choisir la meilleure

3. Vous pouvez utiliser la table avec les conditions de filtrage les plus strictes comme apparence (le recto de la séquence de connexion est la couche de boucle externe du corps de boucle multicouche),

Cela peut réduire les frais d'E/S inutiles et accélérer l'exécution de l'algorithme.

 La différence entre on a.id=b.id et où a.id=b.id, on signifie que la table sera connectée, et où fera la comparaison des données

Remarque : Le principe doit être que le résultat est une abstinence NULL (c'est-à-dire que la condition n'est limitée à aucune ligne de données NULL, sémantiquement il s'agit d'une jointure interne)

Principes d'optimisation :

Rationaliser la requête, la connexion élimination, conversion équivalente, suppression des connexions d'objets de table redondantes

Par exemple : la clé primaire/clé unique est utilisée comme condition de connexion, et la colonne de table intermédiaire n'est utilisée que comme condition équivalente, la connexion de table intermédiaire peut être supprimé

10. Autre optimisation des requêtes

1. Ce qui suit sera Provoque l'abandon de la requête d'index et l'analyse du texte intégral

1.1. > dans la clause Where Remarque : prise en charge de la clé primaire. Les clés non primaires ne prennent pas en charge

1.2 Évitez d'utiliser ou d'utiliser, la situation spécifique doit donc être analysée au cas par cas.

  Optimisation similaire :

  sélectionnez * dans l'onglet name='aa' ou name='bb';

  =>

  sélectionnez * dans l'onglet name='aa'

  union all

  select * from tab name='bb';

  Mesure réelle :

 1. Cent mille données test, Sans aucun index, la requête ci-dessus est deux fois plus rapide que la requête ci-dessous.

2. Dans le test de 300 000 données, lorsque aa et bb sont indexés séparément, la vitesse de requête suivante est un peu plus rapide que ou.

 1.3. Évitez d'utiliser not in

 not in ne peut généralement pas utiliser d'index ; le champ de clé primaire peut être

 1.4. 🎜> 1.5. like ne peut pas être précédé d'un signe de pourcentage comme '%.com'

Solution :

1. Si vous devez utiliser % devant et que la longueur des données n'est pas grande, comme URL, vous pouvez retourner les données et les enregistrer. Entrez dans la base de données et vérifiez à nouveau. LIKE REVERSE'%.com';

2. Utiliser l'index de couverture

1.6 Lors de l'utilisation de champs d'index comme conditions, s'il s'agit d'un index composite, le nom du champ avec le préfixe le plus à gauche du. l'index doit être utilisé

2. Remplacer existe dans in

sélectionnez num à partir de a où num in (sélectionnez num à partir de b)

sélectionnez num à partir de a où existe (sélectionnez 1 à partir de b où num =a.num)

Avec un million de données, il faut 6,65 s et 4,18 s pour filtrer 59 417 données. Aucune autre optimisation n'a été effectuée, il suffit de remplacer exist par in.

3. La définition du champ est une chaîne. Il n'y a pas de guillemets lors de l'interrogation et aucune analyse de texte intégral ne sera effectuée.

[Ce qui suit est un extrait du billet de blog de Luantanqin http://www.cnblogs.com/lingiu/p/3414134.html Je n'ai pas effectué le test correspondant]

4. Essayez de l'utiliser autant que possible. Les variables de table remplacent les tables temporaires

5. Évitez de créer et de supprimer fréquemment des tables temporaires pour réduire la consommation des ressources des tables système

6. Si une table temporaire est utilisée , assurez-vous de l'ajouter à la fin de la procédure stockée. Supprimez explicitement toutes les tables temporaires, tronquez d'abord la table, puis supprimez la table. Cela peut éviter un verrouillage de longue durée des tables système

7. Essayez d'éviter d'utiliser. curseurs, car les curseurs sont moins efficaces. Si l'opération du curseur est Si les données dépassent 10 000 lignes, vous devriez alors envisager de réécrire

8. Volume de données important Si le volume de données est trop important, vous devez vous demander si le volume de données est trop important. les exigences correspondantes sont raisonnables.

9. Essayez d'éviter les opérations de transactions volumineuses et d'améliorer la simultanéité du système.

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!

Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn

Outils d'IA chauds

Undresser.AI Undress

Undresser.AI Undress

Application basée sur l'IA pour créer des photos de nu réalistes

AI Clothes Remover

AI Clothes Remover

Outil d'IA en ligne pour supprimer les vêtements des photos.

Undress AI Tool

Undress AI Tool

Images de déshabillage gratuites

Clothoff.io

Clothoff.io

Dissolvant de vêtements AI

Video Face Swap

Video Face Swap

Échangez les visages dans n'importe quelle vidéo sans effort grâce à notre outil d'échange de visage AI entièrement gratuit !

Outils chauds

Bloc-notes++7.3.1

Bloc-notes++7.3.1

Éditeur de code facile à utiliser et gratuit

SublimeText3 version chinoise

SublimeText3 version chinoise

Version chinoise, très simple à utiliser

Envoyer Studio 13.0.1

Envoyer Studio 13.0.1

Puissant environnement de développement intégré PHP

Dreamweaver CS6

Dreamweaver CS6

Outils de développement Web visuel

SublimeText3 version Mac

SublimeText3 version Mac

Logiciel d'édition de code au niveau de Dieu (SublimeText3)

MySQL: une introduction à la base de données la plus populaire au monde MySQL: une introduction à la base de données la plus populaire au monde Apr 12, 2025 am 12:18 AM

MySQL est un système de gestion de la base de données relationnel open source, principalement utilisé pour stocker et récupérer les données rapidement et de manière fiable. Son principe de travail comprend les demandes des clients, la résolution de requête, l'exécution des requêtes et les résultats de retour. Des exemples d'utilisation comprennent la création de tables, l'insertion et la question des données et les fonctionnalités avancées telles que les opérations de jointure. Les erreurs communes impliquent la syntaxe SQL, les types de données et les autorisations, et les suggestions d'optimisation incluent l'utilisation d'index, les requêtes optimisées et la partition de tables.

Place de MySQL: bases de données et programmation Place de MySQL: bases de données et programmation Apr 13, 2025 am 12:18 AM

La position de MySQL dans les bases de données et la programmation est très importante. Il s'agit d'un système de gestion de base de données relationnel open source qui est largement utilisé dans divers scénarios d'application. 1) MySQL fournit des fonctions efficaces de stockage de données, d'organisation et de récupération, en prenant en charge les systèmes Web, mobiles et de niveau d'entreprise. 2) Il utilise une architecture client-serveur, prend en charge plusieurs moteurs de stockage et optimisation d'index. 3) Les usages de base incluent la création de tables et l'insertion de données, et les usages avancés impliquent des jointures multiples et des requêtes complexes. 4) Des questions fréquemment posées telles que les erreurs de syntaxe SQL et les problèmes de performances peuvent être déboguées via la commande Explication et le journal de requête lente. 5) Les méthodes d'optimisation des performances comprennent l'utilisation rationnelle des indices, la requête optimisée et l'utilisation des caches. Les meilleures pratiques incluent l'utilisation des transactions et des acteurs préparés

Pourquoi utiliser MySQL? Avantages et avantages Pourquoi utiliser MySQL? Avantages et avantages Apr 12, 2025 am 12:17 AM

MySQL est choisi pour ses performances, sa fiabilité, sa facilité d'utilisation et son soutien communautaire. 1.MySQL fournit des fonctions de stockage et de récupération de données efficaces, prenant en charge plusieurs types de données et opérations de requête avancées. 2. Adoptez l'architecture client-serveur et plusieurs moteurs de stockage pour prendre en charge l'optimisation des transactions et des requêtes. 3. Facile à utiliser, prend en charge une variété de systèmes d'exploitation et de langages de programmation. 4. Avoir un solide soutien communautaire et fournir des ressources et des solutions riches.

Comment se connecter à la base de données d'Apache Comment se connecter à la base de données d'Apache Apr 13, 2025 pm 01:03 PM

Apache se connecte à une base de données nécessite les étapes suivantes: Installez le pilote de base de données. Configurez le fichier web.xml pour créer un pool de connexion. Créez une source de données JDBC et spécifiez les paramètres de connexion. Utilisez l'API JDBC pour accéder à la base de données à partir du code Java, y compris l'obtention de connexions, la création d'instructions, les paramètres de liaison, l'exécution de requêtes ou de mises à jour et de traitement des résultats.

Comment démarrer MySQL par Docker Comment démarrer MySQL par Docker Apr 15, 2025 pm 12:09 PM

Le processus de démarrage de MySQL dans Docker se compose des étapes suivantes: Tirez l'image MySQL pour créer et démarrer le conteneur, définir le mot de passe de l'utilisateur racine et mapper la connexion de vérification du port Créez la base de données et l'utilisateur accorde toutes les autorisations sur la base de données

CentOS installe MySQL CentOS installe MySQL Apr 14, 2025 pm 08:09 PM

L'installation de MySQL sur CENTOS implique les étapes suivantes: Ajout de la source MySQL YUM appropriée. Exécutez la commande YUM Install MySQL-Server pour installer le serveur MySQL. Utilisez la commande mysql_secure_installation pour créer des paramètres de sécurité, tels que la définition du mot de passe de l'utilisateur racine. Personnalisez le fichier de configuration MySQL selon les besoins. Écoutez les paramètres MySQL et optimisez les bases de données pour les performances.

Rôle de MySQL: Bases de données dans les applications Web Rôle de MySQL: Bases de données dans les applications Web Apr 17, 2025 am 12:23 AM

Le rôle principal de MySQL dans les applications Web est de stocker et de gérer les données. 1.MySQL traite efficacement les informations utilisateur, les catalogues de produits, les enregistrements de transaction et autres données. 2. Grâce à SQL Query, les développeurs peuvent extraire des informations de la base de données pour générer du contenu dynamique. 3.MySQL fonctionne basé sur le modèle client-serveur pour assurer une vitesse de requête acceptable.

Comment installer MySQL dans CentOS7 Comment installer MySQL dans CentOS7 Apr 14, 2025 pm 08:30 PM

La clé de l'installation de MySQL est d'élégance pour ajouter le référentiel MySQL officiel. Les étapes spécifiques sont les suivantes: Téléchargez la clé GPG officielle MySQL pour empêcher les attaques de phishing. Ajouter un fichier de référentiel MySQL: RPM -UVH https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Mise à jour du référentiel Cache: Yum Update Installation Mysql: Yum install install install starting starting mysql Service: SystemCTL start start mysqld starger bugo boartup Service mysql Service: SystemCTL start start mysqld starger bugo bo onthing staring Service mysql Service: SystemCTL Start Start MySQLD Set Out Up Boaching Staring Service MySQL Service: SystemCTL Start Start MysQL

See all articles