基于索引的sql語句設(shè)計及優(yōu)化

0提高系統(tǒng)速度的方法及途徑現(xiàn)在，信息技術(shù)在生產(chǎn)中得到了廣泛應(yīng)用，大量應(yīng)用程序處理的數(shù)據(jù)相對較大。如何處理這些大量的數(shù)據(jù)，以及通過保障系統(tǒng)的快速響應(yīng)，是系統(tǒng)設(shè)計中必須解決的最重要問題。影響系統(tǒng)速度的原因有多種多樣,改善系統(tǒng)速度的方法及途徑也是多種多樣的,對于這些提高速度的方法及途徑從本質(zhì)上說大致可分為兩大類:軟件或硬件的改變,對于大部分系統(tǒng)而言,升級硬件意味著成本增加,通?？蛻魺o法承受這一點,因此常見改善系統(tǒng)性能的做法是從軟件入手,通過改善軟件結(jié)構(gòu)、算法、語句效率等帶來系統(tǒng)整體性能的提高。很多應(yīng)用系統(tǒng)是通過SQL語句來操作數(shù)據(jù)庫中的海量數(shù)據(jù),使用精心設(shè)計的SQL語句或優(yōu)化后的SQL語句能有效地改變系統(tǒng)運行速度。本文將從索引的角度分析如何設(shè)計及優(yōu)化SQL語句以提高系統(tǒng)運行速度。1查詢模式優(yōu)化要對SQL語句進行優(yōu)化,首先必須清楚SQL語句的執(zhí)行過程,以從中得到一些可能的優(yōu)化途徑,SQL語句的執(zhí)行過程一般分成4步:(1)語法分析:進行SQL語法分析,檢查SQL語法是否正確。(2)優(yōu)化分析:優(yōu)化器采用基于規(guī)則的優(yōu)化模式(RBO)或者是采用基于代價的優(yōu)化模式(CBO)去選擇效率最優(yōu)的查詢模式。(3)選擇優(yōu)化:優(yōu)化器采用優(yōu)化后的方案,產(chǎn)生SQL語句執(zhí)行計劃。(4)SQL語句執(zhí)行引擎:按照該執(zhí)行計劃運行,并返回查詢結(jié)果。在SQL語句的執(zhí)行過程中,優(yōu)化器決定了SQL語句執(zhí)行的優(yōu)化方案,這是SQL語句執(zhí)行過程中的最重要的一步,以CBO優(yōu)化方式為例說明優(yōu)化過程:(1)SQL語句轉(zhuǎn)換:把經(jīng)過語法分析后的SQL語句轉(zhuǎn)換成更合理的SQL語句。(2)計劃生成:根據(jù)表、聚集和索引在數(shù)據(jù)字典中存放的特性統(tǒng)計信息以及根據(jù)訪問路徑、連接方式、連接順序的不同組合,可產(chǎn)生不同的執(zhí)行計劃,并計算出執(zhí)行每個計劃的代價。其中訪問路徑對執(zhí)行代價有很大的影響。(3)選擇計劃:比較各個計劃,選擇代價最低的形成優(yōu)化方案并執(zhí)行。根據(jù)以上SQL語句的執(zhí)行過程,可以看出:SQL的優(yōu)化關(guān)鍵在優(yōu)化器選擇的執(zhí)行計劃,執(zhí)行計劃的關(guān)鍵是訪問路徑的選擇,而訪問路徑的選擇是索引,利用好索引,是提高系統(tǒng)操作數(shù)據(jù)庫速度的最重要因素之一。2建立聚族索引索引一般分聚族索引(Clusteredindex)和非聚族索引(Nonclusteredindex)兩種。一個表只能有一個聚族索引,可有多個非聚族索引,使用聚族索引查詢數(shù)據(jù)要比使用非聚族索引快。聚族索引和非聚族索引的選擇對數(shù)據(jù)庫的性能影響很大。聚族索引是一種數(shù)據(jù)表的物理順序與索引順序相同的索引,占用的空間相對較小,建立聚族索引的策略是:(1)在where子句中的列上建立聚族索引,包括where子句中的范圍查詢、模糊查詢以及數(shù)據(jù)高度重復(fù)的列;(2)在join操作中連接的列上建立聚族索引;(3)在orderby和groupby子句中的列上建立聚族索引;(4)聚族索引不利于插入操作,所以不適用于insert語句。非聚族索引是一種數(shù)據(jù)表的物理順序與索引順序不相同的索引,與聚族索引相比,非聚族索引占用空間大,而且效率低,建立非聚族索引的策略是:(1)在where子句中的列上建立非聚族索引,包括范圍查詢、模糊查詢、以及使用到的主鍵列和外鍵列,但前提條件是在沒有建立聚族索引的情況下,并且適用于小范圍查詢(返回的結(jié)果集小于整表數(shù)據(jù)的20%);(2)在join操作中連接的主建列上建立非聚族索引;(3)在orderby和groupby子句中的主鍵列上建立非聚族索引。建立索引會提高數(shù)據(jù)檢索速度,但不能盲目的建立很多索引,因為索引也有其弊端,一是建立索引要占有大量磁盤空間;二是增加了維護代價,因為在對表進行增加、刪除和更新的時候,索引也需要動態(tài)的維護。3sql織物的不合理通過上面的分析可以看到利用合理的索引提高系統(tǒng)性能效果是明顯的,但如果在程序中書寫SQL語句不合理,就有可能會造成優(yōu)化器不使用索引。在不使用索引后就采用全表掃描,不必要的全表掃描會造成不必要的數(shù)據(jù)處理,而且有時還會拖垮整個數(shù)據(jù)庫。因此在編寫SQL語句時應(yīng)該清楚在哪些情況下不會使用已經(jīng)建立的索引,這有助于提高SQL語句的性能。(1)選擇索引方式如果采用通配符%在搜索詞首出現(xiàn),是不會使用需要搜索列的索引。比如:select*fromcustomerwherenamelike′%am%′(name列建立了索引)。name列的索引是不會被使用的,如果通配符出現(xiàn)在非詞首,優(yōu)化器是能夠使用其索引。比如:select*fromcustomerwherenamelike′am%′(name列建立了索引)。這樣就使用了name列的索引。(2)避免使用索引not操作符可以用來取反,但是一旦使用了not操作符就不會使用到索引。在使用與not操作符有同樣效果的<>運算符的時候,同樣不會使用到索引。在某些情況下,我們可以避免使用not或者<>。比如:select*fromcustomerwhereage<>40(age列建立了索引)。將以上的查詢改成select*fromcustomerwhereage>40orage<40(age列建立了索引)。第二種查詢方式比第一種查詢方式會快一些,因為使用到了age列的索引。(3)生成索引vendoridor或者in子句查詢的優(yōu)化依賴于在這些子句中使用的表上的索引,如果or或者in子句查詢中使用的列上有任一個列沒有索引,則執(zhí)行全表掃描。比如:selectnamefromcustomerswherecreditrating=1andvendorIDin(selectvendorIDfromcustomerswhereminorderoty>=20andaverageleadtime<16)(vendorID列上沒有建立索引)。以上SQL語句執(zhí)行全表掃描,效率較低。(4)索引有多列isnull或者isnotnull語句操作符是不會引用索引的,任何包含空值的列將不會被包含在索引中。即使索引有多列,但只要這些列中有一列含有null,該列就會從索引中排除。也就是說如果某列存在空值,即使對該列建索引也不會提高性能。比如:select*fromemployeewheresalaryisnotnull(salary列上建立了索引)。該語句是不會用到salary列上的索引。4書寫sql句子綜上所述,恰當(dāng)?shù)亟⑺饕墒箖?yōu)化器優(yōu)化SQL語句的執(zhí)行,從而使訪問的數(shù)