Astuces :
1. Comment détecter les déclarations inefficaces ?
Sous MySQL, en définissant --log-slow-queries=[file name] dans les paramètres de démarrage, vous pouvez enregistrer les instructions SQL dont le temps d'exécution dépasse long_query_time (la valeur par défaut est de 10 secondes) dans le fichier journal spécifié. Vous pouvez également modifier le temps de requête longue dans le fichier de configuration de démarrage, comme :
# Définir le temps de requête longue à 8 secondes
long_query_time=8
2. Comment interroger l'index d'une table ?
Vous pouvez utiliser l'instruction SHOW INDEX, telle que :
SHOW INDEX FROM [nom de la table]
3. Comment interroger l'utilisation de l'index d'une certaine instruction ?
Vous pouvez utiliser l'instruction EXPLAIN pour voir l'utilisation de l'index d'une certaine instruction SELECT. S'il s'agit d'une instruction UPDATE ou DELETE, elle doit d'abord être convertie en instruction SELECT.
4. Comment exporter le contenu du moteur INNODB vers le fichier journal des erreurs ?
Nous pouvons utiliser la commande SHOW INNODB STATUS pour afficher de nombreuses informations utiles sur le moteur INNODB, telles que le processus en cours, les transactions, les erreurs de clé étrangère, les blocages. problèmes et autres statistiques. Comment permettre que ces informations soient enregistrées dans le fichier journal ? Tant que vous créez la table innodb_monitor à l'aide de l'instruction suivante, MySQL écrira le système dans le fichier journal des erreurs toutes les 15 secondes :
CREATE
TABLE innodb_monitor (a INT) ENGINE=INNODB;
, supprimez simplement Cette table peut être :
DROP TABLE innodb_monitor;
5. Comment supprimer régulièrement d'énormes fichiers journaux ?
Définissez simplement le délai d'expiration du journal dans le fichier de configuration de démarrage :
expire_logs_days=10
Remarques :
1. Concentrez-vous sur l'index
. Ce qui suit utilise la table TSK_TASK comme exemple pour illustrer le processus d'optimisation des instructions SQL. La table TSK_TASK permet de sauvegarder les tâches de surveillance du système. Les champs et index pertinents sont les suivants :
ID : clé primaire ;
MON_TIME : heure de surveillance ;
STATUS_ID : état de la tâche établie avec SYS_HIER_INFO.ID.
Remarque : MySQL créera automatiquement des index pour les clés étrangères. Au cours de ce processus d'optimisation, il a été constaté que ces index de clés étrangères créés automatiquement provoqueraient des interférences inutiles avec l'efficacité des instructions SQL.
Tout d'abord, nous avons constaté dans le fichier journal que l'exécution de l'instruction suivante était relativement lente, plus de 10 secondes :
# Query_time : 18 Lock_time : 0 Rows_sent : 295 Rows_examined : 88143
select * from TSK_TASK WHERE STATUS_ID = 1064 et MON_TIME >= ' 2007-11 -22' et MON_TIME < '2007-11-23';
Il s'avère qu'il faut trouver 295 enregistrements qui remplissent les conditions parmi 88143 enregistrements, ce qui est bien sûr lent. Utilisez rapidement l'instruction EXPLAIN pour vérifier l'utilisation de l'index :
+----+-------------+--------------+------+- ---------
| id | select_type table | clé_possible | lignes supplémentaires
| ----------+------+-----------
1 | SIMPLE |TSK_TASK
| FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME |
+-------------+----------+------+--------- --On
voit que là Deux index sont disponibles : FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME et l'index de clé étrangère sur STATUS_ID est utilisé lorsque l'instruction est finalement exécutée.
Regardons à nouveau l'index de la table TSK_TASK :
+----------+------------------------------- -- --------
| Table | Nom_clé | Cardinalité
| -- ---------------
| TSK_TASK | ID PRIMAIRE |
999149
|
-------------------------Sous Oracle ou d'autres bases de données relationnelles, les conditions WHERE L'ordre des champs dans l'index joue un rôle important dans la sélection des l'indice. Ajustons l'ordre des champs, mettons STATUS_ID à la fin et EXPLAIN à nouveau :
EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' et MON_TIME < '2007-11-23' et STATUS_ID = 1064
; aucun effet, MySQL utilise toujours l'index de clé étrangère STATUS_ID créé par le système.
Après une analyse minutieuse, il semble que l'attribut Cardinalité (c'est-à-dire le nombre de valeurs uniques dans l'index) joue un rôle extrêmement important dans la sélection de l'index. MySQL sélectionne l'index avec le plus petit nombre de valeurs uniques. dans l'index comme index de l'instruction entière.
Pour cette instruction, si vous utilisez FK_task_status_id_TO_SYS_HIER_INFO comme index et que la table TSK_TASK stocke les données pendant plusieurs jours, le nombre d'enregistrements analysés sera important et la vitesse sera lente. Il existe plusieurs solutions d'optimisation disponibles :
S'il n'y a pas beaucoup de tâches dans une journée, nous supprimons l'index FK_task_status_id_TO_SYS_HIER_INFO, puis MySQL utilisera l'index TSK_TASK_KEY_MON_TIME, puis analysera les enregistrements avec STATUS_ID 1064 dans les données de cette journée, ce qui n'est pas lent. ;
S'il y a plusieurs tâches dans une journée, nous devons supprimer les index FK_task_status_id_TO_SYS_HIER_INFO et TSK_TASK_KEY_MON_TIME, puis créer un index commun de STATUS_ID, MON_TIME, qui sera certainement très efficace.
Par conséquent, il est recommandé de ne pas utiliser de clés étrangères pour les tables comportant un grand nombre d'enregistrements afin d'éviter une réduction importante de l'efficacité des performances.
2. Essayez de contrôler le nombre d'enregistrements dans chaque table.Lorsque
le nombre d'enregistrements dans une table est important, la gestion et la maintenance seront très difficiles. Par exemple, la maintenance des index prendra beaucoup de temps, ce qui entraînera de grandes perturbations. fonctionnement normal du système.
Pour les tables dont le volume de données continue de croître au fil du temps, nous pouvons distinguer les données en temps réel et les données historiques en fonction du temps. Nous pouvons utiliser le programme de service en arrière-plan pour déplacer régulièrement les données de la table en temps réel vers la table historique, contrôlant ainsi. le nombre d'enregistrements dans la table en temps réel et en améliorant les performances des requêtes et l'efficacité opérationnelle. Mais notez que le temps de chaque déplacement doit être suffisamment court pour ne pas affecter l'écriture des données du programme normal. Si cela prend trop de temps, cela peut provoquer un problème de blocage.
3. Stratégie de hachage (partition) des données :
lorsque le nombre de clients atteint une certaine échelle, une seule base de données ne sera pas en mesure de prendre en charge un accès simultané plus élevé. À ce stade, vous pouvez envisager de hacher (partitionner) les données client dans plusieurs bases de données. partager la charge. Améliorer les performances globales et l’efficacité du système.