Optimiser les requêtes MySQL


Si l'interrogation est de prendre beaucoup de temps, et connexions de base de données et les retards de croissance, alors il est temps d'optimiser votre base de données. Dans MySQL, vous pouvez utiliser mysqlreport commande pour générer un rapport sur suffisamment utilisé la base de données et établir une base de référence pour de futures actions. Vous pouvez également être nécessaire de modifier le fichier de configuration (mon.cnf) de log des requêtes lentes. Identifier la lenteur de l'exécution des requêtes et les causes possibles. Généralement, les causes sont liées à des indices comme aucun indice ou de la mauvaise index. Cet article donne une description détaillée des commandes et des concepts que nous pouvons utiliser pour optimiser MySQL.

Etape 1

l'Utilisation d'index

l'Utilisation d'index fait plus rapidement la requête de la même façon qu'un index dans un livre d'aide à la recherche d'un mot en particulier.

Pour savoir comment et par qui les index sont utilisés dans une requête donnée, utiliser la commande explain comme un préfixe à une requête select.

EXPLIQUER SÉLECTIONNEZ emp_id DE ORGN_DATA OÙ dept_code = ' IT '

Cette commande retourne les informations suivantes:

Table table(s) sont utilisées dans la requête
Type - type de JOINTURE. Les valeurs peuvent être système, const, eq_ref, ref, plage, index, tous les
Clés Possibles - Toutes les touches qui peuvent être utilisées pour les index
- La clé utilisé pour l'indice
Longueur de la Clé - réduire les touches sont mieux
Ref - Autres Colonnes utilisées avec la clé pour obtenir les résultats
Lignes - Nombre de lignes à partir de l'endroit où les données vont venir
Extra - Extra d'informations. Certaines valeurs possibles peuvent être à l'aide de l'index, à l'aide de où, à l'aide temporaire, à l'aide de filesort

Vous devriez reconsidérer votre structure de la table, si Possible, des Touches de contenir la valeur NULL.
UNE valeur NULL dans le champ de la Clé indique l'absence d'index est utilisé. À l'aide de n indices de dissuader les performances si il y a trop d'enregistrements dans la table parce que, pour toute requête, tous les enregistrements seront recherchés pour la condition donnée.

Utiliser la commande ALTER pour ajouter de l'indice, telle que donnée ci-dessous:

ALTER TABLE nom_table ADD INDEX (nom_colonne)

Vous pouvez faire de l'indexation sur plusieurs colonnes à l'aide de:

ALTER TABLE nom_table ADD INDEX (column_name1,..,column_nameN)

MySQL utilise le plus à gauche précéder, donc automatiquement les index seront ajoutés pour column_name1, (column_name1,column_name2),...(column_name1,..,column_NameN-1). Cela est très utile dans des situations comme la recherche basée sur le nom de famille, donc un index sur (nom, prénom, middlename) va automatiquement ajouter plusieurs indices utilisés dans plus rapide de l'interrogation.

Points à retenir:

  • Choisissez la colonne(s) pour l'indexation très attentivement.
  • champs Indexés devraient être utilisés pour la recherche et la requête doit être recadrée si un calcul est effectué sur les champs indexés.
  • Index lui-même prend de l'espace de stockage.
  • Chaque opération sur la base de données nécessite la mise à jour de l'index.
  • Une Clé plus petite Longueur signalé par la commande explain est mieux. Plus petite longueur de clé signifie moins d'espace de stockage requis par le fichier d'index et de aussi que de temps à la recherche dans l'index est de moins en moins. Pour cela, des indices peuvent être définies sur une partie de colonnes. Par exemple, l'INDEX(col1(10),col2(20)). Encore une fois, vous avez judicieusement décider quelle est la longueur de colonnes à utiliser pour l'indexation.

Etape 2

à l'Aide de la commande ANALYSER

Utiliser la commande ANALYSER pour générer la clé de répartition pour la table pour être utilisé par MySQL optimiseur de requête pour décider laquelle les indices sont les meilleurs à utiliser dans une requête.

ANALYSER la TABLE table_name

Étape 3

Utilisation de la commande OPTIMIZE

Si il y a trop d'insertions/supprime dans une table, puis d'OPTIMISER la commande doit être fréquemment utilisé pour optimiser le disque de mouvement de la tête lors de la récupération. Ceci est particulièrement utile si la variable de la taille des colonnes sont utilisées comme varchar, text, blob etc. OPTIMISER la commande ne défragmentation de la fragmentation causée par les champs de longueur variable comme VARCHAR.

OPTIMIZE TABLE table_name

Étape 4

à l'Aide de fonctions spéciales pour le chargement de données

Pour le chargement de données à partir d'un fichier dans une table de base de données, à l'aide de LOAD DATA INFILE est beaucoup plus rapide que la lecture de données à partir du fichier de manière itérative à l'aide de la commande d'INSERTION. La syntaxe de cette commande est:

LOAD DATA INFILE 'filedata.dat' DANS la TABLE nom_table (col1,col2,...,colM) FIELDS TERMINATED BY '|'')

Étape 5

la définition de la PRIORITÉ des commandes

Si il y a plus de requêtes que les insertions de données, vous pouvez réduire la priorité de la commande d'INSERTION à l'aide de:

INSÉRER LOW_PRIORITY ou SÉLECTIONNEZ HIGH_PRIORITY

Si le client n'est pas intéressé par les résultats de l'INSERT, puis il peut être immédiatement libérée à l'aide de la commande suivante:

INSERT DELAYED
.

Ce qui rend le système plus rapide par le dosage des insertions.

Étape 6

SUPPRIMER vs TRONQUER

SUPPRIMER supprime la ligne par ligne, tandis que les TRONQUER supprime toutes les lignes à la fois. Donc, si vous n'êtes pas intéressé dans nombre de lignes supprimées à partir d'un tableau comme le résultat de la commande SUPPRIMER, puis utiliser TRUNCATE avec la syntaxe suivante:

TRUNCATE TABLE table_name

Etape 7

l'Abaissement de la vérification de l'autorisation de surcharge

de plus Simple, les autorisations de réduire la vérification de l'autorisation de frais généraux. L'utilisation de la SUBVENTION de commande pour définir les autorisations. La syntaxe générale pour la délivrance du brevet:

SUBVENTION Oper1,...,OperN SUR db_name.tb_name À nom_utilisateur@nom_ordinateur IDENTIFIÉ PAR un mot de passe

Par exemple:

GRANT SELECT,UPDATE SUR EMP_DET.SALARY_DET DE feroz@localhost identified BY 'abc123'

Si le compte d'utilisateur 'feroz' n'est pas là, alors il sera créé sur localhost (bien que l'utilisateur peut accéder aux données depuis n'importe quel ordinateur) et le mot de passe 'abc123' sera assigné à elle.

Etape 8

Utilisation de RÉFÉRENCE (fonction)

savoir combien une donnée MySQL fonction ou l'expression est de prendre le temps, utiliser MySQL intégré dans la fonction:

indice de RÉFÉRENCE(loop_count, expression)

Elle renvoie toujours 0 mais aussi imprime le temps pris par l'expression.

Etape 9

Synchrozing types de données

Si les colonnes dans les tableaux contiennent les mêmes informations, puis de faire leurs types de données de même afin que les jointures peuvent être exécutées plus rapidement.

Etape 10

Divers

  • Insérer plusieurs lignes avec une seule instruction SQL.
  • Supprimer les paranthesis dans la clause where.
  • Si SQL_SMALL_RESULT option est utilisée, MySQL utilise temporaire en mémoire des tables qui offrent un accès plus rapide.


l'optimisation de la Requête n'est pas un temps de travail. Si votre base de données est fortement utilisé, alors vous pourriez avoir à répéter le processus d'optimisation de tous les 5-6 mois.

Syed Feroz Zainvi maintient sa technique blogs:

http://www.crossroadsbyzainvi.blogspot.com









Optimiser les requetes MySQL


Optimiser les requetes MySQL : Plusieurs milliers de conseils pour vous faciliter la vie.


Si l'interrogation est de prendre beaucoup de temps, et connexions de base de donnees et les retards de croissance, alors il est temps d'optimiser votre base de donnees. Dans MySQL, vous pouvez utiliser mysqlreport commande pour generer un rapport sur suffisamment utilise la base de donnees et etablir une base de reference pour de futures actions. Vous pouvez egalement etre necessaire de modifier le fichier de configuration (mon.cnf) de log des requetes lentes. Identifier la lenteur de l'execution des requetes et les causes possibles. Generalement, les causes sont liees a des indices comme aucun indice ou de la mauvaise index. Cet article donne une description detaillee des commandes et des concepts que nous pouvons utiliser pour optimiser MySQL.

Etape 1

l'Utilisation d'index

l'Utilisation d'index fait plus rapidement la requete de la meme façon qu'un index dans un livre d'aide a la recherche d'un mot en particulier.

Pour savoir comment et par qui les index sont utilises dans une requete donnee, utiliser la commande explain comme un prefixe a une requete select.

EXPLIQUER SELECTIONNEZ emp_id DE ORGN_DATA OU dept_code = ' IT '

Cette commande retourne les informations suivantes:

Table table(s) sont utilisees dans la requete
Type - type de JOINTURE. Les valeurs peuvent etre systeme, const, eq_ref, ref, plage, index, tous les
Cles Possibles - Toutes les touches qui peuvent etre utilisees pour les index
- La cle utilise pour l'indice
Longueur de la Cle - reduire les touches sont mieux
Ref - Autres Colonnes utilisees avec la cle pour obtenir les resultats
Lignes - Nombre de lignes a partir de l'endroit ou les donnees vont venir
Extra - Extra d'informations. Certaines valeurs possibles peuvent etre a l'aide de l'index, a l'aide de ou, a l'aide temporaire, a l'aide de filesort

Vous devriez reconsiderer votre structure de la table, si Possible, des Touches de contenir la valeur NULL.
UNE valeur NULL dans le champ de la Cle indique l'absence d'index est utilise. A l'aide de n indices de dissuader les performances si il y a trop d'enregistrements dans la table parce que, pour toute requete, tous les enregistrements seront recherches pour la condition donnee.

Utiliser la commande ALTER pour ajouter de l'indice, telle que donnee ci-dessous:

ALTER TABLE nom_table ADD INDEX (nom_colonne)

Vous pouvez faire de l'indexation sur plusieurs colonnes a l'aide de:

ALTER TABLE nom_table ADD INDEX (column_name1,..,column_nameN)

MySQL utilise le plus a gauche preceder, donc automatiquement les index seront ajoutes pour column_name1, (column_name1,column_name2),...(column_name1,..,column_NameN-1). Cela est tres utile dans des situations comme la recherche basee sur le nom de famille, donc un index sur (nom, prenom, middlename) va automatiquement ajouter plusieurs indices utilises dans plus rapide de l'interrogation.

Points a retenir:

  • Choisissez la colonne(s) pour l'indexation tres attentivement.
  • champs Indexes devraient etre utilises pour la recherche et la requete doit etre recadree si un calcul est effectue sur les champs indexes.
  • Index lui-meme prend de l'espace de stockage.
  • Chaque operation sur la base de donnees necessite la mise a jour de l'index.
  • Une Cle plus petite Longueur signale par la commande explain est mieux. Plus petite longueur de cle signifie moins d'espace de stockage requis par le fichier d'index et de aussi que de temps a la recherche dans l'index est de moins en moins. Pour cela, des indices peuvent etre definies sur une partie de colonnes. Par exemple, l'INDEX(col1(10),col2(20)). Encore une fois, vous avez judicieusement decider quelle est la longueur de colonnes a utiliser pour l'indexation.

Etape 2

a l'Aide de la commande ANALYSER

Utiliser la commande ANALYSER pour generer la cle de repartition pour la table pour etre utilise par MySQL optimiseur de requete pour decider laquelle les indices sont les meilleurs a utiliser dans une requete.

ANALYSER la TABLE table_name

Etape 3

Utilisation de la commande OPTIMIZE

Si il y a trop d'insertions/supprime dans une table, puis d'OPTIMISER la commande doit etre frequemment utilise pour optimiser le disque de mouvement de la tete lors de la recuperation. Ceci est particulierement utile si la variable de la taille des colonnes sont utilisees comme varchar, text, blob etc. OPTIMISER la commande ne defragmentation de la fragmentation causee par les champs de longueur variable comme VARCHAR.

OPTIMIZE TABLE table_name

Etape 4

a l'Aide de fonctions speciales pour le chargement de donnees

Pour le chargement de donnees a partir d'un fichier dans une table de base de donnees, a l'aide de LOAD DATA INFILE est beaucoup plus rapide que la lecture de donnees a partir du fichier de maniere iterative a l'aide de la commande d'INSERTION. La syntaxe de cette commande est:

LOAD DATA INFILE 'filedata.dat' DANS la TABLE nom_table (col1,col2,...,colM) FIELDS TERMINATED BY '|'')

Etape 5

la definition de la PRIORITE des commandes

Si il y a plus de requetes que les insertions de donnees, vous pouvez reduire la priorite de la commande d'INSERTION a l'aide de:

INSERER LOW_PRIORITY ou SELECTIONNEZ HIGH_PRIORITY

Si le client n'est pas interesse par les resultats de l'INSERT, puis il peut etre immediatement liberee a l'aide de la commande suivante:

INSERT DELAYED
.

Ce qui rend le systeme plus rapide par le dosage des insertions.

Etape 6

SUPPRIMER vs TRONQUER

SUPPRIMER supprime la ligne par ligne, tandis que les TRONQUER supprime toutes les lignes a la fois. Donc, si vous n'etes pas interesse dans nombre de lignes supprimees a partir d'un tableau comme le resultat de la commande SUPPRIMER, puis utiliser TRUNCATE avec la syntaxe suivante:

TRUNCATE TABLE table_name

Etape 7

l'Abaissement de la verification de l'autorisation de surcharge

de plus Simple, les autorisations de reduire la verification de l'autorisation de frais generaux. L'utilisation de la SUBVENTION de commande pour definir les autorisations. La syntaxe generale pour la delivrance du brevet:

SUBVENTION Oper1,...,OperN SUR db_name.tb_name A nom_utilisateur@nom_ordinateur IDENTIFIE PAR un mot de passe

Par exemple:

GRANT SELECT,UPDATE SUR EMP_DET.SALARY_DET DE feroz@localhost identified BY 'abc123'

Si le compte d'utilisateur 'feroz' n'est pas la, alors il sera cree sur localhost (bien que l'utilisateur peut acceder aux donnees depuis n'importe quel ordinateur) et le mot de passe 'abc123' sera assigne a elle.

Etape 8

Utilisation de REFERENCE (fonction)

savoir combien une donnee MySQL fonction ou l'expression est de prendre le temps, utiliser MySQL integre dans la fonction:

indice de REFERENCE(loop_count, expression)

Elle renvoie toujours 0 mais aussi imprime le temps pris par l'expression.

Etape 9

Synchrozing types de donnees

Si les colonnes dans les tableaux contiennent les memes informations, puis de faire leurs types de donnees de meme afin que les jointures peuvent etre executees plus rapidement.

Etape 10

Divers

  • Inserer plusieurs lignes avec une seule instruction SQL.
  • Supprimer les paranthesis dans la clause where.
  • Si SQL_SMALL_RESULT option est utilisee, MySQL utilise temporaire en memoire des tables qui offrent un acces plus rapide.


l'optimisation de la Requete n'est pas un temps de travail. Si votre base de donnees est fortement utilise, alors vous pourriez avoir a repeter le processus d'optimisation de tous les 5-6 mois.

Syed Feroz Zainvi maintient sa technique blogs:

http://www.crossroadsbyzainvi.blogspot.com


Optimiser les requêtes MySQL

Optimiser les requêtes MySQL : Plusieurs milliers de conseils pour vous faciliter la vie.
Recommander aux amis
  • gplus
  • pinterest

Messages récents

Commentaire

Laisser un commentaire

évaluation