sql數(shù)據(jù)庫語句如何優(yōu)化

這篇文章主要介紹sql數(shù)據(jù)庫語句如何優(yōu)化，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

通常sql數(shù)據(jù)庫需要進(jìn)行優(yōu)化分析，并且還有一定的技巧，sql優(yōu)化的幾種方法這里就不做詳細(xì)介紹了，本文將會(huì)sql語句優(yōu)化進(jìn)行總結(jié)，后面還附了優(yōu)化工具SQL Tuning Expert for Oracle及使用方法，首先我們要遵隨數(shù)據(jù)庫優(yōu)化的幾個(gè)原則：

1.盡量避免在列上做運(yùn)算，這樣會(huì)導(dǎo)致索引失敗；

2.使用join是應(yīng)該用小結(jié)果集驅(qū)動(dòng)大結(jié)果集，同時(shí)把復(fù)雜的join查詢拆分成多個(gè)query。不然join的越多表，就會(huì)導(dǎo)致越多的鎖定和堵塞。

3.注意like模糊查詢的使用，避免使用%%，例如select * from a where name like '%de%';

代替語句：select * from a where name >= 'de' and name < 'df';

4.僅列出需要查詢的字段，不要使用select * from ...，節(jié)省內(nèi)存；

5.使用批量插入語句，節(jié)省交互；

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

6.limit基數(shù)比較大時(shí)，使用between ... and ...

7.不要使用rand函數(shù)隨機(jī)獲取記錄；

8.避免使用null ，這就需要在建表時(shí)，盡量設(shè)置為not null，提升查詢性能；

9，不要使用count(id),而應(yīng)該是count(*)

10.不要做無謂的排序，盡可能在索引中完成排序；

我們先來看一個(gè)sql：

 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 ('locked', 'release') 
                               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 = 'OK'
                    left join ws_product_container pc on ii.container_id = pc.container_id
//總起來說關(guān)聯(lián)太多表，設(shè)計(jì)表時(shí)可以多一些冗余字段，減少表之間的關(guān)聯(lián)查詢；
                where 
                    ii.inventory_type = 'raw_material' and 
                    ii.inventory_status = 'in_stock' and 
                    ii.facility_id = '25' and 
                    datediff(now(),ii.last_updated_time) < 3  //違反了第一個(gè)原則
                     and p.product_type = 'goods'
                     and p.product_name like '%果%'   // 違反原則3

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

上面的sql我們?cè)趂rom 中使用了子查詢，這樣對(duì)查詢是非常不利的；

更好的一種做法是下面的語句：

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、from 語句中一定不要使用子查詢；

2、使用更多的where加以限制，縮小查找范圍；

3、合理利用索引；

4、通過explain查看sql性能；

使用工具 SQL Tuning Expert for Oracle 優(yōu)化SQL語句

對(duì)于SQL開發(fā)人員和DBA來說，根據(jù)業(yè)務(wù)需求寫出一條正確的SQL很容易。但是SQL的執(zhí)行性能怎么樣呢？能優(yōu)化一下跑得更快嗎？如果不是資深
DBA,估計(jì)很多人都沒有信心。

幸運(yùn)的是，自動(dòng)化優(yōu)化工具可以幫助我們解決這個(gè)難題。這就是今天要介紹的 Tosska SQL Tuning Expert for Oracle 工具。

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

本工具發(fā)明人Richard To, Dell的前首席工程師, 擁有超過20年的SQL優(yōu)化經(jīng)驗(yàn).

1、創(chuàng)建數(shù)據(jù)庫連接，也可以稍后創(chuàng)建。填好連接信息，點(diǎn)擊 “Connect” 按鈕。

如果您已經(jīng)安裝Oracle客戶端，并且在Oracle客戶端配置了TNS，可以在本窗口選擇“TNS”作為”Connection Mode”，然后在”Database Alias”中選擇配置好的TNS作為數(shù)據(jù)庫別名。

如果您沒有安裝Oracle客戶端或者不想安裝Oracle客戶端， 可以選擇“Basic Type”作為”Connection Mode”，只需數(shù)據(jù)庫服務(wù)器IP, 端口和服務(wù)名即可。

2、輸入有性能問題的SQL

3、點(diǎn)擊Tune按鈕，自動(dòng)生成大量的等價(jià)SQL并且開始執(zhí)行。雖然測(cè)試還沒有完成，我們已經(jīng)可以看到 SQL 20 的性能提升了100%。

讓我們仔細(xì)看一下SQL 20, 它使用了兩個(gè)Hints, 以最快的執(zhí)行速度脫穎而出。原來的SQL要0.99秒，優(yōu)化后的SQL執(zhí)行時(shí)間接近0秒。

由于這條SQL每天要在數(shù)據(jù)庫中執(zhí)行上萬次，優(yōu)化后可節(jié)省大約 165秒的數(shù)據(jù)庫執(zhí)行時(shí)間。

最后，用等價(jià)的SQL 20 替換 應(yīng)用程序源代碼中有性能問題的SQL。重新編譯應(yīng)用程序，性能得到了提高。

以上是“sql數(shù)據(jù)庫語句如何優(yōu)化”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！