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.

Juge problème SQL

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.

Obtenir le problème 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.

  Vues internes associées telles que comme v$, $session_wait, etc.

  • Compétences en écriture SQL

  Il existe plusieurs compétences générales en écriture SQL :
• Utiliser les index de manière rationnelle

Avec moins d'index, les requêtes seront plus lentes ; prennent plus de place et vous devez maintenir l'index de manière dynamique lors de l'exécution d'ajouts, de suppressions et de modifications, ce qui affecte les performances. Le taux de sélection est élevé (moins de valeurs en double) et est fréquemment référencé par où les index B-tree doivent le faire. être établi ;

Généralement, les colonnes de jointure doivent être indexées ; les requêtes de type de document complexes utilisent des index de texte intégral pour une meilleure efficacité ; l'établissement d'index doit être effectué entre les requêtes et le DML. Trouver un équilibre entre les performances ; attention aux requêtes basées sur des colonnes non principales

• Utilisez UNION ALL au lieu de UNION

UNION 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écution

terminé 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.

Cas 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

1. 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.

2. 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

3. 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

4. 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é

Afficher l'explication du plan d'exécution

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

1. Réfléchissez aux points d'optimisation possibles en fonction le plan d'exécution

2. Effectuer des modifications de structure de table, ajouter des index, réécrire SQL, etc. en fonction des points d'optimisation possibles. Opération

3. Afficher le temps d'exécution optimisé et le plan d'exécution

4. Si l'effet d'optimisation n'est pas évident, répétez la quatrième étape

5. Recommandations associées : "

   tutoriel mysql
"

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!