SOL優(yōu)化技術(shù)及應(yīng)用sQL優(yōu)化技術(shù)及應(yīng)用徐新靜(天津天鐵冶金集團(tuán)有限公司計(jì)控電訊廠,河北省涉縣056404[摘要]隨著信息化進(jìn)程的加快管理工作的逐步細(xì)化QL的優(yōu)化直接影響著系統(tǒng)的運(yùn)行狀態(tài)和速度。根據(jù)軟件設(shè)計(jì)開發(fā)過(guò)程中數(shù)據(jù)庫(kù)設(shè)計(jì)和程序開發(fā)的技術(shù)和經(jīng)驗(yàn)通過(guò)實(shí)例總結(jié)了數(shù)據(jù)庫(kù)訪問(wèn)SQL優(yōu)化的方法和技巧及在項(xiàng)目中的應(yīng)用。通過(guò)優(yōu)化,提高了系統(tǒng)運(yùn)行速度減少了系統(tǒng)故障用戶反饋良好關(guān)鍵詞數(shù)據(jù)庫(kù)主鍵外鍵索引關(guān)聯(lián)優(yōu)化1前言況下我們關(guān)心的是進(jìn)出廠貨物的凈重,如果每次查隨著信息化技術(shù)在天鐵的逐步推廣應(yīng)用信息化詢數(shù)據(jù)分析都由計(jì)算得出凈重必然耗費(fèi)時(shí)間,所以已經(jīng)覆蓋了包括進(jìn)出廠物資的計(jì)量、設(shè)備材料及備品增加了凈重這個(gè)冗余字段以提高速度,同時(shí)也降低了飛備件的采購(gòu)發(fā)放、生產(chǎn)過(guò)程的控制、產(chǎn)成品的銷售等SQL語(yǔ)句的復(fù)雜度。產(chǎn)、供、銷的各個(gè)環(huán)節(jié)。隨著管理的進(jìn)一步細(xì)化數(shù)據(jù)例: lect車號(hào),日期,凈重from<稱重表題量逐步增加,要快速有效地訪問(wèn)這些數(shù)據(jù),對(duì)這些數(shù) where凈重>60據(jù)進(jìn)行綜合的分析,并且做到并發(fā)共享使用這些數(shù)Select車號(hào),日期,毛重-皮重frm<稱重表>據(jù),就要想方設(shè)法優(yōu)化數(shù)據(jù)結(jié)構(gòu)和訪問(wèn)的SQL語(yǔ)句, where毛重-皮重>60提高其運(yùn)行效率212主鍵的選擇2SQL優(yōu)化方法及應(yīng)用一個(gè)表只有一個(gè)主鍵,主鍵能唯一確定一個(gè)實(shí)拋卻數(shù)據(jù)庫(kù)系統(tǒng)的因素來(lái)優(yōu)化SQL,不外乎從數(shù)體,其他字段就是主鍵所確定實(shí)體的一些屬性特點(diǎn),據(jù)庫(kù)設(shè)計(jì)和程序?qū)崿F(xiàn)兩個(gè)方面進(jìn)行優(yōu)化。在工作中同它們之間沒(méi)有相互依賴關(guān)系。主鍵將頻繁地用于增樣的數(shù)據(jù)庫(kù)服務(wù)器,由于不同的設(shè)計(jì)理念造成了不同刪、改、查詢等SQL語(yǔ)句主鍵的選擇至關(guān)重要。主鍵的數(shù)據(jù)庫(kù)設(shè)計(jì)、不同的訪問(wèn)速度結(jié)果是大相徑庭。怎般以常用的列作為主鍵,主鍵盡可能短;對(duì)于復(fù)合樣提高數(shù)據(jù)訪問(wèn)速度對(duì)一個(gè)系統(tǒng)程序員來(lái)說(shuō)至關(guān)重主鍵,需要幾個(gè)字段聯(lián)合做主鍵時(shí),主鍵列要盡可能要,也是一個(gè)應(yīng)用系統(tǒng)成敗的關(guān)鍵地少實(shí)在不行就人為增加一單一的主鍵列,因?yàn)橹骶?1數(shù)據(jù)庫(kù)設(shè)計(jì)階段的優(yōu)化鍵的長(zhǎng)短將影響索引的大小和外建表的大小,導(dǎo)致增211建表加磁盤的M0和表關(guān)聯(lián)的難度。建表時(shí)要基本滿足三個(gè)范式。即:表的記錄要求21.3外鍵外鍵的作用是建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間的鏈性具有原子性不可再分解對(duì)字段冗余的約束即表接,即關(guān)系型數(shù)搖庫(kù)中的表間關(guān)系它實(shí)觀了數(shù)據(jù)庫(kù)里的每一個(gè)字段都不能由別的字段衍生和計(jì)算得出。表間的參照完整性約束外鍵約束不僅控制存儲(chǔ)在外鸚但在實(shí)際工作中可根據(jù)實(shí)際情況設(shè)置字段冗余,以達(dá)鍵表中的數(shù)據(jù),還可以控制對(duì)主鍵表中數(shù)據(jù)的修改。到空間換時(shí)間的效果。如稱重表中有毛重、皮重凈重如果不通過(guò)外鍵建立這種表間關(guān)系,那么在表上進(jìn)行三個(gè)字段,凈重可由毛重減去皮重獲得,在大多數(shù)情記錄的增刪改時(shí)就會(huì)使數(shù)據(jù)的完整性遭到破壞。護(hù)》護(hù)護(hù)護(hù)機(jī)護(hù)機(jī)護(hù)護(hù)護(hù)護(hù)護(hù)器加》護(hù)護(hù)護(hù)器加機(jī)》加護(hù)器器護(hù)加》》護(hù)》》護(hù)機(jī)機(jī)護(hù)》》》5]劉金海劉懷章孫開明劉日新鋼管熱處理過(guò)程的“黑匣子”動(dòng)態(tài)溫度測(cè)試A1]2004年全國(guó)煉鋼軋鋼生產(chǎn)技術(shù)會(huì)議文集[C12004作者簡(jiǎn)介6]JBr37367-1994質(zhì)量管理中常用的統(tǒng)計(jì)工具S劉金海,男,1987年畢業(yè)于北京鋼鐵學(xué)院熱能工程系,高級(jí)工7張樹坤張利民36Mn2V鋼石油套管的亞溫淬火強(qiáng)韌化處理工程師中國(guó)煤化工人事鋼管熱處理和螺紋藝鋼管,2005(6)20-22CNMH保部部長(zhǎng)。8]陳建偉,趙冰27 MnCrv鋼石油套管強(qiáng)韌化熱處理工藝研究J天津鋼管,2007(1):1-3.微機(jī)應(yīng)用與自動(dòng)化例:銷售系統(tǒng)訂單表中有訂單號(hào)、訂貨品種、訂貨中,由于數(shù)據(jù)量小數(shù)據(jù)訪問(wèn)不是很頻繁、并發(fā)用戶少量、訂貨用戶編號(hào)…客戶表中有訂貨用戶編號(hào)、地等原因,SQL語(yǔ)句的優(yōu)劣基本體現(xiàn)不出來(lái),而對(duì)于數(shù)址、稅號(hào)、銀行賬號(hào)、聯(lián)系方式……在客戶表中訂貨用據(jù)量大、數(shù)據(jù)關(guān)系復(fù)雜的數(shù)據(jù)庫(kù)環(huán)境,優(yōu)劣立現(xiàn),尤其戶編號(hào)是主鍵,而在訂單表中訂貨用戶編號(hào)是外鍵。,是 Where子句優(yōu)化,它的優(yōu)化在很大程度上就是盡量如果刪除客戶表中的某個(gè)客戶,必須保證訂單表中沒(méi)減少全表掃描。我在工作實(shí)踐中總結(jié)出來(lái),按照下面有該客戶的訂單,否則破壞了數(shù)據(jù)的完整性找不到的一些規(guī)則對(duì)SQL進(jìn)行適當(dāng)?shù)膬?yōu)化后,其運(yùn)行速度有該訂單的客戶信息,系統(tǒng)無(wú)法進(jìn)行下一步的發(fā)貨結(jié)了明顯地提高算等一系列的工作。221語(yǔ)句優(yōu)化2.14索引在 select語(yǔ)句中一定要明確選取的字段,不要把索引就像字典的目錄,利用索引能提高數(shù)據(jù)訪問(wèn)沒(méi)用的字段列入字段表,更不能用“ select*from表的速度就不難理解了。建立索引的目的是加快對(duì)表中名”這樣的語(yǔ)句。記錄的查找或排序。但是為表設(shè)置索引是要付出代價(jià)在公司產(chǎn)銷系統(tǒng)中,有幾個(gè)畫面總提示數(shù)據(jù)字段的,它不但增加了數(shù)據(jù)庫(kù)的存儲(chǔ)空間,而且在插入和總數(shù)超出界定范圍,但實(shí)際并沒(méi)有超出,經(jīng)過(guò)分析修改數(shù)據(jù)時(shí)要花費(fèi)時(shí)間來(lái)維護(hù)索引,所以并不是索引SQL語(yǔ)句發(fā)現(xiàn),這些畫面所涉及的表其字段多達(dá)100越多越好,弄不好會(huì)事倍功半。這里建議在經(jīng)常需要多個(gè),有的甚至上200個(gè),而畫面所取的字段不過(guò)用于 where子句條件、排序、分組以及多表關(guān)聯(lián)的列二十個(gè),這時(shí)如果用 select*fom表名 where<條上建立索引。而對(duì)于查詢幾率小、數(shù)據(jù)值少(性別取值件>這樣不但浪費(fèi)了大量的Uo資源、服務(wù)器資源數(shù)只有男女兩個(gè))字段數(shù)據(jù)量大(text、imae等數(shù)據(jù)類據(jù)查詢的速度也大打折扣并且因字段太多而前臺(tái)畫型)的列則不適合建立索面報(bào)錯(cuò)。在使用索引時(shí)要注意查詢條件列的順序和索引能用 where語(yǔ)句的盡量避免使用 having子句,的關(guān)系。聚合索引條件放在前面其次是非聚合索引, having子句要在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)最后是普通字段條件,條件語(yǔ)句中用到復(fù)合索引中的行過(guò)濾。例起始字段才能顯著提高速度,如果僅用復(fù)合索引的非優(yōu)化前 select司磅工,日期, count(*)from稱重表起始列則沒(méi)什么效果。where貨名=精礦’ group by司磅2.1.5表的分割工,日期 having日期>’201001-01表的字段數(shù)目過(guò)多或者根據(jù)實(shí)際情況預(yù)測(cè)表的優(yōu)化后 select司磅工,日期,coun(磅單號(hào))記錄數(shù)非常大,那么要考慮表的分割。對(duì)于前者可按fom稱重表 where貨名=精礦'and照字段使用的頻度進(jìn)行垂直分割,后者按時(shí)間或記錄日期>2010-01-01’ group by司磅工,日期的完整與否等規(guī)則實(shí)施水平分割。對(duì)于同一SQL語(yǔ)句,其字段列表、 where子句條公司產(chǎn)銷系統(tǒng)在運(yùn)行半年以后,一些履歷表中的件盡量按規(guī)范和順序填寫。字段列要寫在運(yùn)算符的左記錄數(shù)已達(dá)到相當(dāng)?shù)囊?guī)模,有數(shù)百萬(wàn)條之多,而且還邊值寫運(yùn)算符右邊,如; where年齡>22和 where22會(huì)逐日增加,如果有多用戶并發(fā)操作,同時(shí)進(jìn)行增、年齡兩個(gè)語(yǔ)句含義和結(jié)果一樣,執(zhí)行效率卻不一樣,改、查詢等操作,這時(shí)就出現(xiàn)速度超慢或干脆服務(wù)吊如果年齡字段有索引的話,后者因不符合規(guī)范不但死現(xiàn)象。為了解決這個(gè)問(wèn)題,對(duì)履歷表按照一定規(guī)則增加了語(yǔ)句分析的次數(shù),還放棄索引進(jìn)行全表掃描,距進(jìn)行了水平分割,運(yùn)行表只保留最近N天的數(shù)據(jù)而降低了執(zhí)行效率。另外,同樣的語(yǔ)句按規(guī)范和順序書抵把N天以前的數(shù)據(jù)放到歷史表中,一般情況下只對(duì)運(yùn)寫第一次用這個(gè)語(yǔ)句時(shí)可能耗時(shí)長(zhǎng),但再次調(diào)用該行表進(jìn)行各種操作,速度會(huì)有很大提高,如果需要查語(yǔ)句時(shí)速度大大加快,否則增加了數(shù)據(jù)庫(kù)系統(tǒng)分析語(yǔ)詢歷史數(shù)據(jù)時(shí)從歷史表中查詢即可,不會(huì)影響運(yùn)行表句所需的時(shí)間和服務(wù)器資源占用。例如:在一個(gè)有而影響現(xiàn)場(chǎng)工作900多萬(wàn)條的一個(gè) Oracle數(shù)據(jù)表里運(yùn)行“ select字段22程序設(shè)計(jì)階段的優(yōu)化列中國(guó)煤化工條件2”查詢語(yǔ)句,我們?cè)谑褂肧QL語(yǔ)句時(shí)往往關(guān)注于結(jié)果的正確第CNMHG運(yùn)行時(shí)僅用218與否,而忽略了其速度和成本。在一些小的應(yīng)用系統(tǒng)∞〈微機(jī)應(yīng)用與自動(dòng)化)∞L優(yōu)化技術(shù)及應(yīng)用通過(guò)case、 decode等一些函數(shù)減少對(duì)數(shù)據(jù)庫(kù)訪問(wèn)表掃描,對(duì)于已建立索引的列建議將“∞”或“!=”操的次數(shù)。例在雇員表 employee中有雇員編號(hào)h,出作符改用其他操作符替代。生日期bith兩個(gè)字段,要求統(tǒng)計(jì)各個(gè)年齡段的人數(shù)。例如:字段名a為已建立索引列,建議將a∞>0改方法1為(a<0oa>0)Select count(bh)“30歲以下” from employee where避免在操作符的左邊對(duì)索引列進(jìn)行各種運(yùn)算,那樣系統(tǒng)將放棄索引而進(jìn)行全表掃描Select count(bh)“30到45歲”" from employee where盡量使用 UNION ALL操作符,而不是UNON操datediff(year, birth, getdate0)>=30 and datediff(year.bih,作符。223其它Select count(bh)"45歲以上" from employee where要盡量避免大的事務(wù),尤其不能在事務(wù)中間與用5datedifflyear, birth, getdate)>45戶交互。大的事務(wù)占用資源多耗時(shí)長(zhǎng),還常常是獨(dú)占E器方法2:資源狀態(tài),這樣影響其他事務(wù)的響應(yīng)速度,如果事務(wù)select count(bh),(case when datediff(year, birth getdate(0)<中間有與用戶交互信息這時(shí)如果用戶不操作事務(wù)30then30歲以下將一直占用資源到用戶操作為止,容易發(fā)生死鎖。when datediff (year, birth, getdate O)between'"3結(jié)束語(yǔ)多30and45then30到45歲在項(xiàng)目設(shè)計(jì)和后續(xù)的維護(hù)過(guò)程中,通過(guò)對(duì)這些優(yōu)Lelse45歲以上’end)化技術(shù)和方法的合理運(yùn)用,提高了系統(tǒng)運(yùn)行速度減少了系統(tǒng)故障,用戶反饋良好。尤其是公司銷售系統(tǒng),group by( case when datediff (year. birth, getdate 0)<30在初期運(yùn)行階段經(jīng)常報(bào)數(shù)據(jù)溢出或查詢吊死錯(cuò)誤,通ghen30歲以下過(guò)對(duì)選取字段的控制和大數(shù)據(jù)量表的水平分割問(wèn)題when datediff (year, birth, getdate O)between得到解決,系統(tǒng)運(yùn)行兩年來(lái)沒(méi)有再出現(xiàn)類似問(wèn)題查30and45then30到45歲詢的平均響應(yīng)時(shí)間也縮短了30%左右。另外SQL的else45歲以上’end優(yōu)化,各種數(shù)據(jù)庫(kù)之間都是互通的,但也不僅相同,有22,2操作符的優(yōu)化些還與所采用的優(yōu)化器的不同而需區(qū)別對(duì)待在工作減少IN、 NOT IN操作符的使用,對(duì)于能轉(zhuǎn)換成中還要根據(jù)實(shí)際情況來(lái)具體對(duì)待。多表連接的要轉(zhuǎn)換成多表連接。因?yàn)?ORACLE遇到IN時(shí)會(huì)試圖將IN操作符轉(zhuǎn)換成多個(gè)表的連接,用IN收稿2010-12-10責(zé)編趙實(shí)鳴)操作符將增加轉(zhuǎn)換時(shí)間。避免對(duì)索引列NULL值進(jìn)行判斷,對(duì)NULL值的作者簡(jiǎn)介判斷將產(chǎn)生全表掃描,不如在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)給字段設(shè)徐新靜,女,19%年畢業(yè)于河北科技大學(xué)計(jì)算機(jī)及應(yīng)用專業(yè),置默認(rèn)值.沒(méi)有默認(rèn)值的盡量用其它更優(yōu)的等價(jià)語(yǔ)句高級(jí)工程師現(xiàn)在天鐵集團(tuán)計(jì)控電訊廠計(jì)算機(jī)中心從事軟件開發(fā)工包數(shù)作“不等于”操作符不用索引,它的處理將會(huì)產(chǎn)生全天津鋼管5億元短融券票面利率為483%信天津鋼管集團(tuán)股份有限公司3月31日發(fā)布公告稱,29日發(fā)行的5億元366天期短融券票面利率為483%。本期短融券起息日為3月30日,上市流通日為3月31日,到期兌付日為2012年月30日公司早前公告稱,募集資金中3億元償還集H中國(guó)煤化工于購(gòu)買原材料對(duì)「■流動(dòng)資金的需求CNMHG(摘自一財(cái)網(wǎng)2011-3-31)∞〈微機(jī)應(yīng)用與自動(dòng)化〉∞