Résumé des techniques d'analyse et d'optimisation des instructions de base de données SQL (outil d'optimisation SQL)

Habituellement, les bases de données SQL doivent être optimisées et analysées, et il existe certaines compétences. Plusieurs méthodes d'optimisation SQL ne seront pas présentées en détail ici. Cet article résumera l'optimisation des instructions SQL, et l'outil d'optimisation SQL Tuning Expert l'est également. ci-joint pour Oracle et comment l'utiliser, nous devons d'abord suivre plusieurs principes d'optimisation de la base de données :

1. Essayez d'éviter d'effectuer des opérations sur les colonnes, ce qui entraînerait un échec de l'index ; Utiliser la jointure Les petits ensembles de résultats doivent être utilisés pour générer des ensembles de résultats volumineux, et les requêtes de jointure complexes doivent être divisées en plusieurs requêtes. Sinon, plus vous rejoignez de tables, plus il y aura de verrous et de congestions.

3. Faites attention à l'utilisation de requêtes floues et évitez d'utiliser %%, par exemple, sélectionnez * à partir d'un nom Where comme '%de%'

Instruction de remplacement : sélectionnez * ; from a which name > ;= 'de' et name

4. Répertoriez uniquement les champs qui doivent être interrogés, n'utilisez pas select * from... pour économiser de la mémoire ; 🎜>

5. Utilisez les instructions Insérer par lots pour enregistrer l'interaction

6 Lorsque la base limite est relativement grande, utilisez entre... et ...

insert into a (id ,name)
values(2,'a'),
(3,'s');

. 7. N'utilisez pas la fonction rand pour obtenir des enregistrements de manière aléatoire ;

8. Évitez d'utiliser null Cela nécessite de le définir sur non null lors de la création d'une table pour améliorer les performances des requêtes ; ne pas utiliser count(id), mais count(* )

10 Ne faites pas de tri inutile, complétez le tri dans l'index autant que possible

Regardons d'abord un SQL ; :

Dans le sql ci-dessus, nous sommes Une sous-requête est utilisée dans from, ce qui est très préjudiciable à la requête

Une meilleure approche est l'instruction suivante :

 select
                    ii.product_id, 
                    p.product_name, 
                    count(distinct pim.pallet_id) count_pallet_id, 
                    if(round(sum(itg.quantity),2) > -1 && round(sum(itg.quantity),2) < 0.005, 0, round(sum(itg.quantity),2)) quantity,
                    round(ifnull(sum(itag.locked_quantity), 0.00000),2) locked_quantity,
                    pc.container_unit_code_name,
                    if(round(sum(itg.qoh),2) > -1 && round(sum(itg.qoh),2) < 0.005, 0, round(sum(itg.qoh),2)) qoh,
                    round(ifnull(sum(itag.locked_qoh), 0.00000),2) locked_qoh,
                    p.unit_code,
                    p.unit_code_name
                from (select 
                        it.inventory_item_id item_id, 
                        sum(it.quantity) quantity, 
                        sum(it.real_quantity) qoh 
                    from 
                        ws_inventory_transaction it
                    where 
                        it.enabled = 1 
                    group by 
                        it.inventory_item_id  
                    ) itg 
                    left join (select 
                                    ita.inventory_item_id item_id, 
                                    sum(ita.quantity) locked_quantity, 
                                    sum(ita.real_quantity) locked_qoh 
                               from 
                                    ws_inventory_transaction_action ita
                               where 
                                    1=1 and ita.type in (&#39;locked&#39;, &#39;release&#39;) 
                               group by 
                                    ita.inventory_item_id 
                               )itag on itg.item_id = itag.item_id
                    inner join ws_inventory_item ii on itg.item_id = ii.inventory_item_id 
                    inner join ws_pallet_item_mapping pim on ii.inventory_item_id = pim.inventory_item_id  
                    inner join ws_product p on ii.product_id = p.product_id and p.status = &#39;OK&#39;
                    left join ws_product_container pc on ii.container_id = pc.container_id
//总起来说关联太多表，设计表时可以多一些冗余字段，减少表之间的关联查询；
                where 
                    ii.inventory_type = &#39;raw_material&#39; and 
                    ii.inventory_status = &#39;in_stock&#39; and 
                    ii.facility_id = &#39;25&#39; and 
                    datediff(now(),ii.last_updated_time) < 3  //违反了第一个原则
                     and p.product_type = &#39;goods&#39;
                     and p.product_name like &#39;%果%&#39;   // 违反原则3

                group by 
                    ii.product_id
                having 
                    qoh < 0.005
                order by 
                    qoh desc

Remarque :

select  
                t.facility_id,
                f.facility_name,
                t.inventory_status,
                wis.inventory_status_name,
                t.inventory_type,
                t.product_type,
                t.product_id, 
                p.product_name,
                t.container_id, 
                t.unit_quantity, 
                p.unit_code,
                p.unit_code_name,
                pc.container_unit_code_name,
                t.secret_key,
                sum(t.quantity) quantity,
                sum(t.real_quantity) real_quantity,
                sum(t.locked_quantity) locked_quantity,
                sum(t.locked_real_quantity) locked_real_quantity
            from ( select 
                        ii.facility_id,
                        ii.inventory_status,
                        ii.inventory_type,
                        ii.product_type,
                        ii.product_id, 
                        ii.container_id, 
                        ii.unit_quantity, 
                        ita.secret_key,
                        ii.quantity quantity,
                        ii.real_quantity real_quantity,
                        sum(ita.quantity) locked_quantity,
                        sum(ita.real_quantity) locked_real_quantity
                    from 
                        ws_inventory_item ii 
                        inner join ws_inventory_transaction_action ita on ii.inventory_item_id = ita.inventory_item_id
                    where 
                        ii.facility_id = '{$facility_id}' and 
                        ii.inventory_status = '{$inventory_status}' and 
                        ii.product_type = '{$product_type}' and 
                        ii.inventory_type = '{$inventory_type}' and
                        ii.locked_real_quantity > 0 and 
                        ita.type in ('locked', 'release') 
                    group by 
                        ii.product_id, ita.secret_key, ii.container_id, ita.inventory_item_id
                    having 
                        locked_real_quantity > 0 
            ) as t
                inner join ws_product p on t.product_id = p.product_id 
                left join ws_facility f on t.facility_id = f.facility_id
                left join ws_inventory_status wis on wis.inventory_status = t.inventory_status
                left join ws_product_container pc on pc.container_id = t.container_id            
            group by 
                t.product_id, t.secret_key, t.container_id

1. N'utilisez pas de sous-requêtes dans l'instruction from

2. limiter et restreindre la portée de la recherche ;

3. Faire un usage raisonnable des index

4.

Utilisez l'outil SQL Tuning Expert pour Oracle pour optimiser les instructions SQL

Pour les développeurs SQL et les administrateurs de bases de données, rédigez une instruction basée sur les besoins de l'entreprise. Corriger SQL est simple. Mais qu’en est-il des performances d’exécution de SQL ? Peut-il être optimisé pour fonctionner plus rapidement ? Si vous n'êtes pas un DBA senior, de nombreuses personnes peuvent ne pas avoir confiance.

Heureusement, des outils d'optimisation automatisés peuvent nous aider à résoudre ce problème. Il s'agit de l'outil Tosska SQL Tuning Expert pour Oracle que je vais vous présenter aujourd'hui.

Télécharger

https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/

L'inventeur de cet outil, Richard À, Ancien ingénieur en chef chez Dell, avec plus de 20 ans d'expérience en optimisation SQL

1. Créer une connexion à une base de données, qui peut également être créée ultérieurement. Remplissez les informations de connexion et cliquez sur le bouton « Connecter ».

Si vous avez installé le client Oracle et configuré TNS sur le client Oracle, vous pouvez sélectionner « TNS » comme « Mode de connexion » dans cette fenêtre, puis sélectionner le TNS configuré comme base de données « Alias ​​de base de données ». alias.

Si vous n'avez pas installé le client Oracle ou si vous ne souhaitez pas installer le client Oracle, vous pouvez sélectionner "Type de base" comme "Mode de connexion" et n'avoir besoin que de la base de données IP du serveur, port et service Juste le nom.

2. Entrez SQL avec des problèmes de performances

3. Cliquez sur le bouton Tune pour générer automatiquement un grand nombre de. équivalents SQL et démarrez l'exécution. Bien que les tests ne soient pas encore terminés, nous pouvons déjà constater que les performances de SQL 20 se sont améliorées de 100 %.

Regardons de plus près SQL 20, qui utilise deux indices et se distingue par la vitesse d'exécution la plus rapide. Le SQL d'origine prend 0,99 seconde et le temps d'exécution SQL optimisé est proche de 0 seconde.

Étant donné que ce SQL est exécuté des dizaines de milliers de fois dans la base de données chaque jour, il peut économiser environ 165 secondes de temps d'exécution de la base de données après optimisation.

Enfin, remplacez le SQL incriminé dans le code source de l'application par le SQL 20 équivalent. Recompilation de l'application et performances améliorées.

La tâche de réglage a été terminée avec succès !

Articles connexes :

Résumé de l'optimisation des performances SQL et optimisation des instructions SQL

Principes d'optimisation des instructions SQL, optimisation des instructions SQL

Vidéos associées :

Tutoriel vidéo d'optimisation MySQL – Éducation booléenne

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!