Maison > Article > base de données > Utilisez des exemples pour vous expliquer comment optimiser SQL
Bien que les coûts du matériel aient baissé de nos jours, l'amélioration des performances du système en mettant à niveau le matériel est également une méthode d'optimisation courante. Les systèmes ayant des exigences élevées en temps réel doivent encore être optimisés du point de vue SQL. Aujourd'hui, nous allons présenter comment optimiser SQL à l'aide d'exemples.
Vous pouvez juger s'il y a un problème avec SQL à travers deux symptômes :
Symptômes au niveau du système
La consommation du CPU est sérieuse
L'attente IO est sérieuse
La page le temps de réponse est trop long Si le journal d'une longue
application présente un délai d'attente et d'autres erreurs
, vous pouvez utiliser la commande sar et la commande top pour afficher l'état actuel du système. Vous pouvez également observer l'état du système grâce à des outils de surveillance tels que Prometheus et Grafana.
Représentation des instructions SQL
Long
Le temps d'exécution est trop long
Obtenir les données de l'analyse complète de la table
Les lignes et les coûts dans le plan d'exécution sont très grand
Le SQL long est facile à comprendre. Si un SQL est trop long, la lisibilité sera mauvaise et la fréquence des problèmes sera certainement plus élevée. Pour mieux juger du problème SQL, nous devons commencer par le plan d'exécution, comme indiqué ci-dessous :
Le plan d'exécution nous indique que cette requête utilise une analyse de table complète Type=ALL et que les lignes sont très grandes (9950400 ). Nous pouvons essentiellement juger qu'il s'agit d'un paragraphe. Il y a une saveur dans SQL.
Différentes bases de données ont différentes méthodes pour l'obtenir. Ce qui suit est l'outil d'acquisition SQL de requêtes lentes pour les bases de données grand public actuelles
MySQL
Journal des requêtes lentes
Outil de test Loadrunner
.Percona ptquery et autres outils de l'entreprise
Oracle
Rapport AWR
outil de test Loadrunner etc.
Vues internes associées telles que v$, _attendre, etc.
Outil de surveillance GRID CONTROL
Base de données Dameng
Rapport AWR
Outil de test LoadRunner, etc.
Compétences en écriture SQL
• Utilisez UNION ALL au lieu de UNIONUNION ALL a une efficacité d'exécution supérieure à UNION, et UNION doit être dédupliqué lors de l'exécution. UNION doit trier les données
• Évitez d'écrire * select
Lors de l'exécution de SQL, l'optimiseur doit convertir * en colonnes spécifiques ; chaque requête doit revenir à la table et les index de couverture ne peuvent pas être utilisés.
• Il est recommandé de créer un index pour les champs JOIN
Généralement, les champs JOIN sont indexés à l'avance
• Évitez les instructions SQL complexes
Améliorez la lisibilité ; évitez la probabilité que des requêtes lentes puissent être converties en ; plusieurs requêtes courtes, utilisez le traitement final de l'entreprise
• Évitez l'écriture où 1 = 1
• Évitez l'écriture similaire order by rand()RAND() provoquant l'analyse de la colonne de données plusieurs fois
Optimisation SQL
plan d'exécutionterminé Lors de l'optimisation de SQL, vous devez d'abord lire le plan d'exécution. Le plan d'exécution vous indiquera où l'efficacité est faible et où une optimisation est nécessaire. Prenons MYSQL comme exemple pour voir quel est le plan d'exécution. (Le plan d'exécution de chaque base de données est différent, vous devez le comprendre par vous-même)
Champ | Explication |
---|---|
id | Chaque identification d'opération exécutée indépendamment identifie l'ordre dans lequel l'objet est utilisé. Plus la valeur de l'identifiant est grande, la première à être exécutée si elles sont identiques. l'ordre d'exécution est de haut en bas Ci-dessous |
select_type | Le type de chaque clause select dans la requête |
table | Le nom de l'objet sur lequel l'opération est effectuée, généralement le nom de la table, mais il en existe d'autres formats |
partitions | Les informations de partition correspondantes (pour les tables non partitionnées, la valeur est NULL) |
type | Type d'opération de jointure |
possible_keys | index possibles |
clé | L'index réellement utilisé par l'optimiseur (la colonne la plus importante) Les types de jointure du meilleur au pire sont const, eq_reg, ref, range, index et ALL. Quand ALL apparaît, cela signifie que le SQL actuel a une "mauvaise odeur" |
key_len | La longueur de la clé d'index sélectionnée par l'optimiseur, l'unité est en octets |
ref | Indique l'objet de référence de l'objet opéré dans cette ligne, l'objet non-référence est NULL |
rows | Le nombre de tuples analysés par l'exécution de la requête (pour innodb, cette valeur est une estimation) |
filtered | Le nombre de tuples dans la table conditionnelle où sont filtrées les données Pourcentage |
extra | Information supplémentaire importante du plan d'exécution, soyez prudent lorsque les mots Using filesort, Using Temporary apparaissent dans cette colonne, il est très probable que l'instruction SQL doive être optimisé |
Ensuite, nous utilisons un cas d'optimisation pratique pour expliquer le processus d'optimisation SQL et les techniques d'optimisation.
Structure des tables
CREATE TABLE `a` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_id` bigint(20) DEFAULT NULL, `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `b` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_name` varchar(100) DEFAULT NULL, `user_id` varchar(50) DEFAULT NULL, `user_name` varchar(100) DEFAULT NULL, `sales` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `c` ( `id` int(11) NOT NULLAUTO_INCREMENT, `user_id` varchar(50) DEFAULT NULL, `order_id` varchar(100) DEFAULT NULL, `state` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) );
Trois tables sont liées pour interroger l'état de la commande de l'utilisateur actuel 10 heures avant et après l'heure actuelle, et les trier par ordre croissant en fonction de l'heure de création de la commande. Le SQL spécifique est le suivant.
select a.seller_id, a.seller_name, b.user_name, c.state from a, b, c where a.seller_name = b.seller_name and b.user_id = c.user_id and c.user_id = 17 and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create;
Afficher le volume de données
Temps d'exécution d'origine
Plan d'exécution d'origine
Idées d'optimisation initiales
Le type du champ de condition Where dans SQL doit être cohérent avec le structure de la table. L'ID utilisateur dans la table est de type varchar(50). SQL réel Le type int utilisé a une conversion implicite et aucun index n'est ajouté. Modifiez le champ user_id dans les tableaux b et c en type int.
Parce qu'il y a une association entre la table b et la table c, créez un index sur le user_id de la table b et de la table c
Parce qu'il y a une association entre la table a et la table b, créez un index sur le seller_name champ de la table a et b
Utiliser un index composite Supprimer les tables temporaires et le tri
Optimisation préliminaire de SQL
alter table b modify `user_id` int(10) DEFAULT NULL; alter table c modify `user_id` int(10) DEFAULT NULL; alter table c add index `idx_user_id`(`user_id`); alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`); alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
Visualiser le temps d'exécution après optimisation
Voir le plan d'exécution après optimisation
Afficher les informations sur les avertissements
Continuer à optimiser, modifier la table et modifier "gmt_create" datetime DEFAULT NULL;
Résumé
S'il y a un message d'alarme, vérifiez les informations d'alarme qui affichent des avertissements ;
Affichez la structure de la table et les informations d'index impliquées dans SQL
Réfléchissez aux points d'optimisation possibles en fonction le plan d'exécution
Effectuer des modifications de structure de table, ajouter des index, réécrire SQL, etc. en fonction des points d'optimisation possibles. Opération
Afficher le temps d'exécution optimisé et le plan d'exécution
Si l'effet d'optimisation n'est pas évident, répétez la quatrième étape
Recommandations associées : "
tutoriel mysqlCe 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!