Optimiser les requêtes MySQL

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.