整理的數(shù)據(jù)庫(kù)查詢(xún)效率分析、優(yōu)化、注意事項(xiàng)

目錄TOC\o"1-5"\h\z\o"CurrentDocument"一、關(guān)于SQL查詢(xún)效率，100w數(shù)據(jù)，查詢(xún)只要1秒。2\o"CurrentDocument"二、SQL提高查詢(xún)效率注意事項(xiàng)4\o"CurrentDocument"三、提高SQL查詢(xún)效率（要點(diǎn)與技巧）9\o"CurrentDocument"四、建立索引與不建立索引的一個(gè)查詢(xún)效率分析：10\o"CurrentDocument"五、如何提高SQL語(yǔ)言的查詢(xún)效率11\o"CurrentDocument"六、使用SQL語(yǔ)句時(shí)應(yīng)注意以下幾點(diǎn)13\o"CurrentDocument"七、ORACAL中的應(yīng)用案例分析14關(guān)于SQL查詢(xún)效率，100w數(shù)據(jù)，查詢(xún)只?機(jī)器情況：p4:2.4?內(nèi)存：1GOs：windows2003數(shù)據(jù)庫(kù)：mssqlserver2000?目的：查詢(xún)性能測(cè)試，比較兩種查詢(xún)的性能>SQL查詢(xún)效率stepbystep：step1.--建表createtablet_userinfo(useridintidentity(1,1)primarykeynonclustered,nickvarchar(50)notnulldefault'',classidintnotnulldefault0,writetimedatetimenotnulldefaultgetdate())go--建索引createclusteredindexix_userinfo_classidont_userinfo(classid)gostep2.--插入數(shù)據(jù)，耗時(shí)08:27，需要耐心等待declare@iintdeclare@kintdeclare@nickvarchar(10)set@i=1while@i<1000000beginset@k=@i%10set@nick=convert(varchar,@i)insertintot_userinfo(nick,classid,writetime)values(@nick,@k,getdate())set@i=@i+1endstep3.--where查詢(xún)，耗時(shí)8秒，夠長(zhǎng)的selecttop20userid,nick,classid,writetimefromt_userinfowhereuseridnotin(selecttop900000useridfromt_userinfoorderbyuseridasc)step4.--where查詢(xún)，耗時(shí)1秒，太快了吧，不可以思議selecta.userid,b.nick,b.classid,b.writetimefrom(selecttop20a.useridfrom(selecttop900020useridfromt_userinfoorderbyuseridasc)aorderbya.useriddesc)ainnerjoint_userinfobona.userid=b.useridorderbya.useridascstep5--where查詢(xún)，耗時(shí)2秒selecttop20userid,nick,classid,writetimefromt_userinfowhereclassid=1anduseridnotin(selecttop90000useridfromt_userinfowhereclassid=1orderbyuseridasc)step6--where查詢(xún)，分析器顯示不到1秒selecta.userid,b.nick,b.classid,b.writetimefrom(selecttop20a.useridfrom(selecttop90000useridfromt_userinfowhereclassid=1orderbyuseridasc)aorderbya.useriddesc)ainnerjoint_userinfobona.userid=b.useridorderbya.useridasc查詢(xún)效率分析：子查詢(xún)?yōu)榇_保消除重復(fù)值，必須為外部查詢(xún)的每個(gè)結(jié)果都處理嵌套查詢(xún)。在這種情況下可以考慮用聯(lián)接查詢(xún)來(lái)取代。如果要用子查詢(xún)，那就用EXISTS替代IN、用NOTEXISTS替代NOTIN。因?yàn)镋XISTS引入的子查詢(xún)只是測(cè)試是否存在符合子查詢(xún)中指定條件的行，效率較高。無(wú)論在哪種情況下,NOTIN都是最低效的。因?yàn)樗鼘?duì)子查詢(xún)中的表執(zhí)行了一個(gè)全表遍歷。建立合理的索引，避免掃描多余數(shù)據(jù)，避免表掃描！幾百萬(wàn)條數(shù)據(jù)，照樣幾十毫秒完成查詢(xún)。二、SQL提高查詢(xún)效率注意事項(xiàng)1）對(duì)查詢(xún)進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在where及orderby涉及的列上建立索引。2）應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行null值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：selectidfromtwherenumisnull--可以在num上設(shè)置默認(rèn)值0，確保表中num列沒(méi)有null值，然后這樣查詢(xún)：selectidfromtwherenum=03）應(yīng)盡量避免在where子句中使用!=或＜＞操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。4）應(yīng)盡量避免在where子句中使用or來(lái)連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：selectidfromtwherenum=10ornum=20--可以這樣查詢(xún)：selectidfromtwherenum=10unionallselectidfromtwherenum=205）in和notin也要慎用，否則會(huì)導(dǎo)致全表掃描，如：selectidfromtwherenumin(1,2,3)--對(duì)于連續(xù)的數(shù)值，能用between就不要用in了：selectidfromtwherenumbetween1and3下面的查詢(xún)也將導(dǎo)致全表掃描：selectidfromtwherenamelike'%abc%'-若要提高效率，可以考慮全文檢索如果在where子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問(wèn)計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問(wèn)計(jì)劃，變量的值還是未知的，因而無(wú)法作為索引選擇的輸入項(xiàng)。如下面語(yǔ)句將進(jìn)行全表掃描：selectidfromtwherenum=@num可以改為強(qiáng)制查詢(xún)使用索引：selectidfromtwith(index(索引名力wherenum=@num應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：selectidfromtwherenum/2=100應(yīng)改為selectidfromtwherenum=100*2應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：selectidfromtwheresubstring(name,1,3)='abc'--name以abc開(kāi)頭的idselectidfromtwheredatediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id應(yīng)改為selectidfromtwherenamelike'abc%'selectidfromtwherecreatedate>='2005-11-30'andcreatedate<'2005-12-1'不要在where子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。不要寫(xiě)一些沒(méi)有意義的查詢(xún)，如需要生成一個(gè)空表結(jié)構(gòu)：selectcol1,col2into#tfromtwhere1=0這類(lèi)代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的，應(yīng)改成這樣：createtable#t(...)很多時(shí)候用exists代替in是一個(gè)好的選擇：selectnumfromawherenumin(selectnumfromb)用下面的語(yǔ)句替換：selectnumfromawhereexists(select1frombwherenum=a.num)并不是所有索引對(duì)查詢(xún)都有效，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢(xún)優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢(xún)可能不會(huì)去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對(duì)查詢(xún)效率起不了作用。索引并不是越多越好，索引固然可以提高相應(yīng)的select的效率，但同時(shí)也降低了insert及update的效率，因?yàn)閕nsert或update時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。應(yīng)盡可能的避免更新clustered索引數(shù)據(jù)列，因?yàn)閏lustered索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新clustered索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為clustered索引。17)盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢(xún)和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)。這是因?yàn)橐嬖谔幚聿樵?xún)和連接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。18）盡可能的使用varchar/nvarchar代替char/nchar，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢(xún)來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。19）任何地方都不要使用select*fromt，用具體的字段列表代替“*”，不要返回用不到的任何字段。20）盡量使用表變量來(lái)代替臨時(shí)表。如果表變量包含大量數(shù)據(jù)，請(qǐng)注意索引非常有限（只有主鍵索引）。21）避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。22）臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行В?，?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是，對(duì)于一次性事件，最好使用導(dǎo)出表。23）在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用selectinto代替createtable，避免造成大量log，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先createtable，然后insert。24）如果使用到了臨時(shí)表，在存儲(chǔ)過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先truncatetable，然后droptable，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。25）盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫(xiě)。26）使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來(lái)解決問(wèn)題，基于集的方法通常更有效。27）與臨時(shí)表一樣，游標(biāo)并不是不可使用。對(duì)小型數(shù)據(jù)集使用FAST_FORWARD游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開(kāi)發(fā)時(shí)間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。28）在所有的存儲(chǔ)過(guò)程和觸發(fā)器的開(kāi)始處設(shè)置SETNOCOUNTON，在結(jié)束時(shí)設(shè)置SETNOCOUNTOFF。無(wú)需在執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器的每個(gè)語(yǔ)句后向客戶(hù)端發(fā)送DONE_IN_PROC消息。29）盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。30）盡量避免向客戶(hù)端返回大數(shù)據(jù)量，若數(shù)據(jù)量過(guò)大，應(yīng)該考慮相應(yīng)需求是否合理。避免將字段設(shè)為“允許為空”?數(shù)據(jù)表設(shè)計(jì)要規(guī)范?深入分析數(shù)據(jù)操作所要對(duì)數(shù)據(jù)庫(kù)進(jìn)行的操作?盡量不要使用臨時(shí)表?多多使用事務(wù)?盡量不要使用游標(biāo)避免死鎖?要注意讀寫(xiě)鎖的使用?不要打開(kāi)大的數(shù)據(jù)集?不要使用服務(wù)器端游標(biāo)?在程序編碼時(shí)使用大數(shù)據(jù)量的數(shù)據(jù)庫(kù)不要給“性別”列創(chuàng)建索引注意超時(shí)問(wèn)題不要使用Select*在細(xì)節(jié)表中插入紀(jì)錄時(shí)，不要在主表執(zhí)行electMAX（ID）?盡量不要使用丁EXT數(shù)據(jù)類(lèi)型?使用參數(shù)查詢(xún)?不要使用Insert導(dǎo)入大批的數(shù)據(jù)?學(xué)會(huì)分析查詢(xún)?使用參照完整性?用INNERJOIN和LEFTJOIN代替Where三、提高SQL查詢(xún)效率(要點(diǎn)與技巧)＞技巧一：?jiǎn)栴}類(lèi)型：ACCESS數(shù)據(jù)庫(kù)字段中含有日文片假名或其它不明字符時(shí)查詢(xún)會(huì)提示內(nèi)存溢出。解決方法：修改查詢(xún)語(yǔ)句sql="select*fromtablenamewherecolumnlike,%"&word&"%'"改為sql="select*fromtablename"rs.filter="columnlike'%"&word&"%'"＞技巧二：?jiǎn)栴}類(lèi)型：如何用簡(jiǎn)易的辦法實(shí)現(xiàn)類(lèi)似百度的多關(guān)鍵詞查詢(xún)(多關(guān)鍵詞用空格或其它符號(hào)間隔)。解決方法：〃用空格分割查詢(xún)字符串ck=split(word,"")〃得到分割后的數(shù)量sck=UBound(ck)sql="select*tablenamewhere"在一個(gè)字段中查詢(xún)Fori=0TosckSQL=SQL&tempJoinWord&"("&_"columnlike'"&ck(i)&"%')"tempJoinWord="and"Next在二個(gè)字段中同時(shí)查詢(xún)Fori=0TosckSQL=SQL&tempJoinWord&"("&_"columnlike'"&ck(i)&"%'or"&_"columnllike'"&ck(i)&"%')"tempJoinWord="and"Next＞技巧三：大大提高查詢(xún)效率的幾種技巧1）盡量不要使用W，使用W會(huì)引起全表掃描，將大大降低查詢(xún)效率。2）經(jīng)過(guò)實(shí)踐驗(yàn)證，charindex（）并不比前面帆的like更能提高查詢(xún)效率，并^Charindex（）會(huì)使索引失去作用（指sqlserver數(shù)據(jù)庫(kù)）3）columnlike'%n&word&n%'會(huì)使索引不起作用4）columnlike'"&word&"%'會(huì)使索引起作用（去掉前面的符號(hào)）5）（指sqlserver數(shù)據(jù)庫(kù)）6）'%"&word&"%,與,n&word&n%,在查詢(xún)時(shí)的區(qū)別：7）比如你的字段內(nèi)容為一個(gè)容易受傷的女人8）'%"&word&"%':會(huì)通配所有字符串，不論查“受傷”還是查“一個(gè)都會(huì)顯示結(jié)果。9）'"&word&"%':只通配前面的字符串，例如查“受傷”是沒(méi)有結(jié)果的，只有查“一個(gè)才會(huì)顯示結(jié)果。10）字段提取要按照“需多少、提多少”的原則，避兔瞄*”，盡量使用“select字段1,字段2,字段3........”。實(shí)踐證明：每少提取一個(gè)字段，數(shù)據(jù)的提取速度就會(huì)有相應(yīng)的提升。提升的速度還要看您舍棄的字段的大小來(lái)判斷。11）orderby按聚集索引列排序效率最高。一個(gè)qlserver數(shù)據(jù)表只能建立一個(gè)聚集索引，一般默認(rèn)為/D，也可以改為其它的字段。12）為你的表建立適當(dāng)?shù)乃饕?，建立索引可以使你的查?xún)速度提高幾十幾百^sqlserver數(shù)據(jù)庫(kù)）四、建立索引與不建立索引的一個(gè)查詢(xún)效率分析：＞環(huán)境：?數(shù)據(jù)庫(kù)：Sqlserver?表：News?字段1山自動(dòng)編號(hào)Title：文章標(biāo)題Author：作者Content：內(nèi)容Star：優(yōu)先級(jí)Addtime：時(shí)間記錄：100萬(wàn)條?測(cè)試機(jī)器：P42.8/1G內(nèi)存/IDE硬盤(pán)＞方案1：--主鍵id,默認(rèn)為聚集索引，不建立其它非聚集索引select*fromNewswhereTitlelike'%"&word&"%'orAuthorlike'%"&word&"%'orderbyIddesc--從字段Title和Author中模糊檢索，按Id排序查詢(xún)時(shí)間：50秒＞方案2：--主鍵Id，默認(rèn)為聚集索引，在Title、Author.Star上建立非聚集索引select*fromNewswhereTitlelike'"&word&"%'orAuthorlike'"&word&"%'orderbyIddesc--從字段Title和Author中模糊檢索，按Id排序查詢(xún)時(shí)間：2-2.5秒＞方案3：--主鍵Id，默認(rèn)為聚集索引，在Title、Author.Star上建立非聚集索引select*fromNewswhereTitlelike'"&word&"%'orAuthorlike'"&word&"%'orderbyStardesc--從字段Title和Author中模糊檢索，按Star排序查詢(xún)時(shí)間：2秒＞方案4：--主鍵Id，默認(rèn)為聚集索引，在Title、Author.Star上建立非聚集索引select*fromNewswhereTitlelike'"&word&"%'orAuthorlike'"&word&"%'--從字段Title和Author中模糊檢索，不排序查詢(xún)時(shí)間：1.8-2秒＞方案5：--主鍵Id，默認(rèn)為聚集索引，在Title、Author.Star上建立非聚集索引select*fromNewswhereTitlelikel"&word&"%1或select*fromNewswhereAuthorlike|"&word&"%'--從字段Title或Author中檢索，不排序查詢(xún)時(shí)間：1秒五、如何提高SQL語(yǔ)言的查詢(xún)效率由于SQL是面向結(jié)果而不是面向過(guò)程的查詢(xún)語(yǔ)言，所以一般支持SQL語(yǔ)言的大型關(guān)系型數(shù)據(jù)庫(kù)都使用一個(gè)基于查詢(xún)成本的優(yōu)化器，為即時(shí)查詢(xún)提供一個(gè)最佳的執(zhí)行策略。對(duì)于優(yōu)化器，輸入是一條查詢(xún)語(yǔ)句，輸出是一個(gè)執(zhí)行策略。一條SQL查詢(xún)語(yǔ)句可以有多種執(zhí)行策略，優(yōu)化器將估計(jì)出全部執(zhí)行方法中所需時(shí)間最少的所謂成本最低的那一種方法。所有優(yōu)化都是基于用記所使用的查詢(xún)語(yǔ)句中的where子句，優(yōu)化器對(duì)where子句中的優(yōu)化主要用搜索參數(shù)(SerachArgument)o搜索參數(shù)的核心思想就是數(shù)據(jù)庫(kù)使用表中字段的索引來(lái)查詢(xún)數(shù)據(jù)，而不必直接查詢(xún)記錄中的數(shù)據(jù)。帶有二、＜、＜二、＞、＞二等操作符的條件語(yǔ)句可以直接使用索引，如下列是搜索參數(shù)：emp_id="10001"salary＞3000a=1andc=7而下列則不是搜索參數(shù)：salary=emp_salarydep_id!=10salary*12＞=3000a=1orc=7應(yīng)當(dāng)盡可能提供一些冗余的搜索參數(shù)，使優(yōu)化器有更多的選擇余地。請(qǐng)看以下3種方法：第一種方法：selectemployee.emp_name,department.dep_namefromdepartment,employeewhere(employee.dep_id=department.dep_id)and(department.dep_code="01")and(employee.dep_code="01");它的搜索分析結(jié)果如下：Estimate2I/OoperationsScandepartmentusingprimarykeyforrowswheredep_codeequals"01"Estimategettinghere1timesScanemployeesequentiallyEstimategettinghere5times第二種方法：selectemployee.emp_name,department.dep_namefromdepartment,employeewhere(employee.dep_id=department.dep_id)and(department.dep_code="01");它的搜索分析結(jié)果如下：Estimate2I/OoperationsScandepartmentusingprimarykeyforrowswheredep_codeequals"01"Estimategettinghere1timesScanemployeesequentiallyEstimategettinghere5times因?yàn)樗谝环N方法與第二種運(yùn)行效率相同，但第一種方法最好，為優(yōu)化器提供了更多的選擇機(jī)會(huì)。因?yàn)樗谌N方法:selectemployee.emp_name,department.dep_namefromdepartment,employeewhere(employee.dep_id=department.dep_id)and(employee.dep_code="01");這種方法最不好，因?yàn)樗鼰o(wú)法使用索引，也就是無(wú)法優(yōu)化六、使用SQL語(yǔ)句時(shí)應(yīng)注意以下幾點(diǎn)1、避免使用不兼容的數(shù)據(jù)類(lèi)型。例如，F(xiàn)loat和Integer，Char和Varchar，Binary和LongBinary不兼容的。數(shù)據(jù)類(lèi)型的不兼容可能使優(yōu)化器無(wú)法執(zhí)行一些本可以進(jìn)行的優(yōu)化操作。例如：selectemp_nameformemployeewheresalary>3000;在此語(yǔ)句中若salary是Float類(lèi)型的，則優(yōu)化器很難對(duì)其進(jìn)行優(yōu)化，因?yàn)?000是個(gè)整數(shù)，我們應(yīng)在編程時(shí)使用3000.0而不要等運(yùn)行時(shí)讓DBMS進(jìn)行轉(zhuǎn)化。2、盡量不要使用表達(dá)式，因它在編繹時(shí)是無(wú)法得到的，所以SQL只能使用其平均密度來(lái)估計(jì)將要命中的記錄數(shù)。3、避免對(duì)搜索參數(shù)使用其他的數(shù)學(xué)操作符。如：selectemp_namefromemployeewheresalary*12>3000;應(yīng)改為：selectemp_namefromemployeewheresalary>250;4、避免使用!=或<>等這樣的操作符，因?yàn)樗鼤?huì)使系統(tǒng)無(wú)法使用索引，而只能直接搜索表中的數(shù)據(jù)。七、ORACAL中的應(yīng)用案例分析一個(gè)1600萬(wàn)數(shù)據(jù)表一一短信上行表TBL_SMS_MO結(jié)構(gòu)：CREATETABLETBL_SMS_MO(SMS_IDNUMBER,MO_IDVARCHAR2(50),MOBILEVARCHAR2(11),SPNUMBERVARCHAR2(20),MESSAGEVARCHAR2(150),TRADE_CODEVARCHAR2(20),LINK_ID