Comment optimiser les requêtes SQL ? (explication détaillée)

Pourquoi devrions-nous optimiser

Le goulot d'étranglement du débit du système apparaît souvent dans la vitesse d'accès à la base de données, c'est-à-dire qu'au fur et à mesure que l'application s'exécute, il y aura de plus en plus de données dans la base de données et le temps de traitement ralentira en conséquence. De plus, les données sont stockées sur le disque et la vitesse de lecture et d'écriture ne peut être comparée à celle-ci. celui de la mémoire.

Que pensez-vous de l'optimisation

1 Lors de la conception de la base de données : conception des tables et des champs de la base de données, du moteur de stockage

2. Faites bon usage des fonctions fournies par MySQL lui-même, telles que les index et l'optimisation de l'écriture des instructions

3. Cluster MySQL, sous-base de données et sous-table, séparation lecture-écriture

Internet. a beaucoup d'expérience dans l'optimisation des instructions SQL, cet article les met donc de côté et tente d'optimiser la couche DAO et l'optimisation de la conception de la base de données, et répertorie deux exemples simples

Exemple 1 : ERP. Optimisation des requêtes

Analyse de la situation actuelle :

1. Manque d'index associés
2. Mysql lui-même a des performances limitées et ne prend pas bien en charge l'association de plusieurs tables. . Les performances actuelles se concentrent principalement sur les requêtes de liste, qui associent de nombreuses tables

Contre-mesures :

1 Ajouter les index nécessaires : affichez les enregistrements d'exécution via Explication et ajoutez des index en fonction des paramètres. plan d'exécution ;
2 Comptez d'abord les clés primaires de la table principale de données commerciales, obtenez un ensemble de résultats plus petit, puis utilisez la requête associée à l'ensemble de résultats
1) Commencez par interroger et afficher la clé primaire du ; données commerciales basées sur la table principale et les conditions
2) Utilisez la clé primaire comme condition de requête, puis associez d'autres tables associées pour interroger les champs commerciaux requis
3) Lors de l'interrogation de la table principale, pour les conditions de requête qui doivent être associés à d'autres tables, vous devez configurer l'association de tables uniquement si vous définissez cette condition

例如 有如下表 TT_A   TT_B    TT_C  TT_D

假设未优化前的SQL是这样的

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....

FROM  TT_A A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?.....

那么优化后的SQL是

第一步

SELECT
    A.ID

FROM  TT_A A
WHERE 1=1AND A.XX = ?AND A.VV = ?第二步

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....
FROM  ( SELECT A.ID,..... FROM  TT_A  WHERE ID IN (1,2,3..)  ) A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?

Résumé :

Ce type d'optimisation est convient aux requêtes de liste, car les conditions d'une requête de liste sont généralement liées à la table principale, utilisez donc ceci pour établir des index de champs clés, et en même temps réduisez considérablement la table principale grâce à la restriction des conditions de requête de la quantité de données. De cette façon, il sera beaucoup plus rapide d'associer d'autres tables

Exemple 2 : Optimisation de la recherche d'articles

Supposons que vous souhaitiez créer une fonction de recherche d'articles pour Tieba, la manière la plus simple et la plus directe La structure de stockage consiste à utiliser une base de données relationnelle pour créer une telle table de base de données relationnelle TT_ARTICLES pour stocker les articles :

Ensuite, si le mot-clé de recherche actuel est " target", nous pouvons utiliser la correspondance de chaîne pour effectuer des requêtes de correspondance sur la colonne CONTENT :

select * from ARTICLES where CONTENT like '% 目标 %';

Cela implémente facilement la fonction de recherche. Cependant, cette approche présente des problèmes évidents, c'est-à-dire que l'utilisation de % pour la correspondance de chaînes est très inefficace, donc une telle requête doit parcourir la table entière (analyse complète de la table). Lorsqu’il y a quelques ou dizaines d’articles, ce n’est pas un problème, mais s’il y a des centaines de milliers ou des millions d’articles, cette méthode est totalement irréalisable. Sans oublier qu'une seule table de base de données relationnelle ne peut pas accueillir une si grande quantité de données. Même si elle peut les accueillir, le coût en temps ici est inimaginable si elle doit être analysée

Nous avons donc. introduire la technologie « d'inversion » « d'indexation ». Dans le scénario décrit ci-dessus, nous pouvons diviser ce concept en deux parties pour expliquer : D'accord, la table ARTICLES ci-dessus existe toujours, mais maintenant une table de mots-clés KEYWORDS doit être ajoutée et la colonne KEYWORD doit donc être indexée. de ce mot-clé peut être rapidement trouvé :

Bien sûr, nous avons également besoin d'une table de relations pour combiner la table KEYWORDS et la table ARTICLES, KEYWORD_ID et ARTICLE_ID comme unions Clé primaire

Vous voyez, il s'agit en fait d'une relation plusieurs-à-plusieurs, c'est-à-dire que le même mot-clé peut apparaître dans plusieurs articles et qu'un article peut contenir plusieurs mots-clés différents. De cette façon, nous pouvons d'abord trouver le KEYWORD_ID correspondant dans la table KEYWARDS en fonction des mots-clés indexés, puis trouver l'ARTICLE_ID en fonction de sa table d'association ci-dessus, puis l'utiliser pour trouver l'article correspondant dans la table ARTICLES.

Résumé :

Cela semble être trois recherches, mais comme l'index est utilisé à chaque fois, une analyse complète de la table est éliminée lorsque la quantité de données est faible. , La vitesse n'est pas lente et lorsqu'il est implémenté à l'aide de SQL, ce processus peut être entièrement intégré dans une instruction SQL. Lorsque la quantité de données est faible, la méthode ci-dessus est suffisante. Cela résout les problèmes de performances causés par les analyses de table complètes et les requêtes de correspondance de % de chaîne.

Résumé :

Lors de l'entretien technique, si vous pouvez donner des exemples pratiques, ou parler directement des problèmes et des gains de votre processus de développement, la branche entretien sera impressionné. Ajoutez-en beaucoup et vos réponses devraient être plus logiques. N'allez pas ici et là, ce qui peut facilement vous confondre. Par exemple, lorsqu'on vous demande comment optimiser SQL, vous ne devez pas répondre directement en ajoutant des index. Vous pouvez répondre comme ceci :

Bonjour l'intervieweur, tout d'abord, le volume de données de la base de données de notre projet a rencontré un goulot d'étranglement, ce qui rend la requête de liste très lente et donne aux utilisateurs une mauvaise expérience. Afin de résoudre ce problème, il existe de nombreuses méthodes, comme la plupart. conception de base de table de base de données Optimisation SQL de base, clustering MYSQL, séparation lecture-écriture, sous-base de données et sous-table, ajout d'une couche de cache à l'architecture, etc. Leurs avantages et inconvénients... Nous les combinons puis les combinons avec. les caractéristiques de notre projet. Enfin, nous choisissons lors de la sélection de la technologie.

Si vous répondez aux questions de manière aussi ordonnée et bien fondée et que vous parlez également de tant de points de connaissances en dehors des questions, l'intervieweur pensera que vous n'êtes pas seulement une personne capable d'écrire du code, mais que vous avez une logique claire. Vous avez votre propre compréhension et réflexion sur la sélection technologique

Cet article provient de la colonne Tutoriel SQL, bienvenue pour apprendre !

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!