Résumé des techniques d'analyse et d'optimisation des instructions de base de données SQL (outil d'optimisation SQL)-tutoriel mysql-php.cn

Maison

base de données

tutoriel mysql

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

php是最好的语言

Aug 03, 2018 pm 05:37 PM

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,&#39;a&#39;),
(3,&#39;s&#39;);

Copier après la connexion

. 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

Copier après la connexion

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 = &#39;{$facility_id}&#39; and 
                        ii.inventory_status = &#39;{$inventory_status}&#39; and 
                        ii.product_type = &#39;{$product_type}&#39; and 
                        ii.inventory_type = &#39;{$inventory_type}&#39; and
                        ii.locked_real_quantity > 0 and 
                        ita.type in (&#39;locked&#39;, &#39;release&#39;) 
                    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

Copier après la connexion

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

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.