sql數(shù)據(jù)庫(kù)語(yǔ)句優(yōu)化區(qū)分與優(yōu)化技巧總結(jié)(sql優(yōu)化工具)
發(fā)表時(shí)間:2023-07-16 來(lái)源:明輝站整理相關(guān)軟件相關(guān)文章人氣:6
[摘要]通常sql數(shù)據(jù)庫(kù)需要進(jìn)行優(yōu)化分析,并且還有一定的技巧,sql優(yōu)化的幾種方法這里就不做詳細(xì)介紹了,本文將會(huì)sql語(yǔ)句優(yōu)化進(jìn)行總結(jié),后面還附了優(yōu)化工具SQL Tuning Expert for Orac...
通常sql數(shù)據(jù)庫(kù)需要進(jìn)行優(yōu)化分析,并且還有一定的技巧,sql優(yōu)化的幾種方法這里就不做詳細(xì)介紹了,本文將會(huì)sql語(yǔ)句優(yōu)化進(jìn)行總結(jié),后面還附了優(yōu)化工具SQL Tuning Expert for Oracle及使用方法,首先我們要遵隨數(shù)據(jù)庫(kù)優(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%';
代替語(yǔ)句:select * from a where name >= 'de' and name < 'df';
4.僅列出需要查詢的字段,不要使用select * from ...,節(jié)省內(nèi)存;
5.使用批量插入語(yǔ)句,節(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.不要做無(wú)謂的排序,盡可能在索引中完成排序;
我們先來(lái)看一個(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
//總起來(lái)說(shuō)關(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ì)查詢是非常不利的;
更好的一種做法是下面的語(yǔ)句:
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 語(yǔ)句中一定不要使用子查詢;
2、使用更多的where加以限制,縮小查找范圍;
3、合理利用索引;
4、通過(guò)explain查看sql性能;
使用工具 SQL Tuning Expert for Oracle 優(yōu)化SQL語(yǔ)句
對(duì)于SQL開(kāi)發(fā)人員和DBA來(lái)說(shuō),根據(jù)業(yè)務(wù)需求寫出一條正確的SQL很容易。但是SQL的執(zhí)行性能怎么樣呢?能優(yōu)化一下跑得更快嗎?如果不是資深
DBA,估計(jì)很多人都沒(méi)有信心。
幸運(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的前首席工程師, 擁有超過(guò)20年的SQL優(yōu)化經(jīng)驗(yàn).
1、創(chuàng)建數(shù)據(jù)庫(kù)連接,也可以稍后創(chuàng)建。填好連接信息,點(diǎn)擊 “Connect” 按鈕。
如果您已經(jīng)安裝Oracle客戶端,并且在Oracle客戶端配置了TNS,可以在本窗口選擇“TNS”作為”Connection Mode”,然后在”Database Alias”中選擇配置好的TNS作為數(shù)據(jù)庫(kù)別名。
如果您沒(méi)有安裝Oracle客戶端或者不想安裝Oracle客戶端, 可以選擇“Basic Type”作為”Connection Mode”,只需數(shù)據(jù)庫(kù)服務(wù)器IP, 端口和服務(wù)名即可。
2、輸入有性能問(wèn)題的SQL
3、點(diǎn)擊Tune按鈕,自動(dòng)生成大量的等價(jià)SQL并且開(kāi)始執(zhí)行。雖然測(cè)試還沒(méi)有完成,我們已經(jīng)可以看到 SQL 20 的性能提升了100%。
讓我們仔細(xì)看一下SQL 20, 它使用了兩個(gè)Hints, 以最快的執(zhí)行速度脫穎而出。原來(lái)的SQL要0.99秒,優(yōu)化后的SQL執(zhí)行時(shí)間接近0秒。
由于這條SQL每天要在數(shù)據(jù)庫(kù)中執(zhí)行上萬(wàn)次,優(yōu)化后可節(jié)省大約 165秒的數(shù)據(jù)庫(kù)執(zhí)行時(shí)間。
最后,用等價(jià)的SQL 20 替換 應(yīng)用程序源代碼中有性能問(wèn)題的SQL。重新編譯應(yīng)用程序,性能得到了提高。
調(diào)優(yōu)任務(wù)順利完成!
相關(guān)文章:
Sql效能優(yōu)化總結(jié)與sql語(yǔ)句優(yōu)化篇
SQL語(yǔ)句優(yōu)化原則,sql語(yǔ)句優(yōu)化
相關(guān)視頻:
MySQL優(yōu)化視頻教程—布爾教育
以上就是sql數(shù)據(jù)庫(kù)語(yǔ)句優(yōu)化分析和優(yōu)化技巧總結(jié)(sql優(yōu)化工具)的詳細(xì)內(nèi)容,更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章!
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識(shí)。