王軍弟

【摘 要】隨著數(shù)據(jù)量的激增，數(shù)據(jù)庫(kù)的查詢優(yōu)化越來(lái)越重要，本文研究了適合大容量數(shù)據(jù)的數(shù)據(jù)庫(kù)查詢優(yōu)化方案，提出了SQL語(yǔ)句中有效的查詢優(yōu)化方法，有效提高查詢效率以及數(shù)據(jù)庫(kù)的整體性能。

【關(guān)鍵詞】SQL Server；查詢優(yōu)化；方法

數(shù)據(jù)庫(kù)系統(tǒng)是管理信息系統(tǒng)的核心，是最為重要的計(jì)算機(jī)應(yīng)用之一。從大多數(shù)系統(tǒng)的應(yīng)用實(shí)例來(lái)看，查詢操作在各種數(shù)據(jù)庫(kù)操作中所占據(jù)的比重最大，而查詢操作所基于的SELECT語(yǔ)句在SQL語(yǔ)句中又是代價(jià)最大的語(yǔ)句。舉例來(lái)說(shuō)，如果數(shù)據(jù)信息積累到上百萬(wàn)甚至上千萬(wàn)條記錄，全表掃描一次往往需要數(shù)十分鐘，甚至數(shù)小時(shí)。如果采用比全表掃描更好的查詢策略，往往可以使查詢時(shí)間降為幾分鐘，由此可見(jiàn)查詢優(yōu)化技術(shù)的重要性。

1 查詢優(yōu)化的必要性

關(guān)系系統(tǒng)的查詢優(yōu)化既是RDBMS實(shí)現(xiàn)的關(guān)鍵技術(shù)又是關(guān)系系統(tǒng)的優(yōu)點(diǎn)所在。它減輕了用戶選擇存取路徑的負(fù)擔(dān)。用戶只要提出“干什么”，不必指出“怎么干”。查詢優(yōu)化的優(yōu)點(diǎn)不僅在于用戶不必考慮如何最好地表達(dá)查詢以獲得較好的效率，而且在于系統(tǒng)可以比用戶程序的“優(yōu)化”做得更好。所以查詢優(yōu)化在查詢處理階段對(duì)于選擇執(zhí)行查詢的最有效策略起著至關(guān)重要的作用。當(dāng)提交一條SQL語(yǔ)句時(shí)，DBMS 進(jìn)行語(yǔ)法檢查后，將語(yǔ)句提交給查詢優(yōu)化器，優(yōu)化器再將SQL語(yǔ)句按照一定的優(yōu)化方法分析為各個(gè)組成。SQL Server的查詢優(yōu)化器是一個(gè)基于成本的優(yōu)化器。它為一個(gè)給定的查詢分析出很多的候選的查詢計(jì)劃，并且估算每個(gè)候選計(jì)劃的成本，從而選擇一個(gè)成本最低的計(jì)劃進(jìn)行執(zhí)行。實(shí)際上，因?yàn)椴樵儍?yōu)化器不可能對(duì)每一個(gè)產(chǎn)生的候選計(jì)劃進(jìn)行優(yōu)化，所以查詢優(yōu)化器會(huì)在優(yōu)化時(shí)間和查詢計(jì)劃的質(zhì)量之間進(jìn)行一個(gè)平衡，盡可能的選擇一個(gè)“最優(yōu)”的計(jì)劃。用戶提交的SQL語(yǔ)句是系統(tǒng)優(yōu)化的基礎(chǔ)，一個(gè)不合理的查詢計(jì)劃僅通過(guò)查詢優(yōu)化器進(jìn)行優(yōu)化，不可能高效，因此SQL語(yǔ)句書寫的優(yōu)劣至關(guān)重要。

2 查詢優(yōu)化的策略

2.1 統(tǒng)一SQL語(yǔ)句的寫法

由于SQL不區(qū)分大小寫，對(duì)于上面兩句SQL語(yǔ)句，雖然程序員認(rèn)為是相同的，但查詢分析器就認(rèn)為是兩句不同的SQL語(yǔ)句，必須進(jìn)行兩次解析，生成2個(gè)執(zhí)行計(jì)劃。對(duì)于程序設(shè)計(jì)人員應(yīng)該遵循同樣的書寫格式。

2.2 合理使用索引

索引是數(shù)據(jù)庫(kù)中一個(gè)常用而重要的數(shù)據(jù)庫(kù)對(duì)象，而優(yōu)化查詢重要的方法是建立索引，在關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)的表上建立合適的索引，可以避免表掃描并減少因查詢而造成的I/O開銷，提高數(shù)據(jù)庫(kù)數(shù)據(jù)查詢的速度，改善數(shù)據(jù)庫(kù)性能。但是創(chuàng)建索引會(huì)增加系統(tǒng)的時(shí)間和空間的開銷。因此創(chuàng)建索引時(shí)必須要與實(shí)現(xiàn)應(yīng)用系統(tǒng)的查詢需求密切結(jié)合，才能達(dá)到優(yōu)化查詢的目的。

在SQL Server中有聚集索引和非聚集索引，其中聚集索引表示表中存儲(chǔ)的數(shù)據(jù)按照索引的順序存儲(chǔ)，檢索效率比非聚集索引高，但對(duì)數(shù)據(jù)更新影響較大。在表中選擇創(chuàng)建聚集索引時(shí)要特別注意，如果聚集索引沒(méi)有建在表的順序字段上，該表容易發(fā)生頁(yè)分裂，導(dǎo)致查詢操作效率低。

以訂單表為例，有訂單編號(hào)orderid，也有客戶編號(hào)customerid，聚集索引應(yīng)該建立在哪個(gè)字段上呢？對(duì)于該表，訂單編號(hào)是順序添加的，如果在orderid上加聚集索引，新增的行都是添加在末尾，這樣不容易經(jīng)常產(chǎn)生頁(yè)分裂。然而，由于大多數(shù)查詢都是根據(jù)客戶編號(hào)來(lái)查的，因此，將聚集索引加在customerid上才有意義。而customerid對(duì)于訂單表而言，并非順序字段。

SQL Server的聚集索引實(shí)際上是對(duì)表按照聚集索引字段的順序進(jìn)行了排序。SQL Server的聚集索引就是表本身的一種組織形式，所以它的效率是非常高的。也正因?yàn)榇?，插入一條記錄，它的位置不是隨便放的，而是要按照順序放在該放的數(shù)據(jù)頁(yè)，如果那個(gè)數(shù)據(jù)頁(yè)沒(méi)有空間了，就引起了頁(yè)分裂。所以很顯然，聚集索引沒(méi)有建在表的順序字段上，該表容易發(fā)生頁(yè)分裂，導(dǎo)致執(zhí)行效率低。

客戶“王盼盼”的“customerid”是001，那么“王盼盼”的訂單信息必須都放在這張表的第一個(gè)數(shù)據(jù)頁(yè)上，如果今天“王盼盼”新下了一個(gè)訂單，那該訂單信息不能放在表的最后一頁(yè)，而是第一頁(yè)！如果第一頁(yè)放滿了呢？則該表所有數(shù)據(jù)都要往后移動(dòng)為這條記錄騰地方，會(huì)導(dǎo)致更新操作效率大幅下降。對(duì)于聚集索引沒(méi)有建在順序字段上的表且大量進(jìn)行數(shù)據(jù)更新操作的表，建議給與較低的頁(yè)填充因子。

2.3 模糊查詢慎用%

SQL Server中提供了兩個(gè)模糊查詢關(guān)鍵字%和_，其中%表示包含零個(gè)或任意多字符串，_表示任何單個(gè)字符。

關(guān)鍵詞%josn%，由于josn前面用到了“%”，因此該查詢必然掃描全表，執(zhí)行效率低。除非必要，否則不要在關(guān)鍵詞前加%。

2.4 連接查詢優(yōu)化

SQL Server提供三種連接方式Merge Join、Nested Loop Join、Hash Join。在表連接時(shí)要注意以下幾方面：連接字段盡量選擇聚集索引所在的字段，由于聚集索引所在字段表的順序已經(jīng)排好，只要兩個(gè)表的數(shù)據(jù)連接即可，連接開銷相當(dāng)于是A表的數(shù)據(jù)加上B表的數(shù)據(jù)，執(zhí)行效率高。仔細(xì)考慮where條件，先做選擇運(yùn)算，在連接前先盡量減小A、B表的結(jié)果集，在此基礎(chǔ)上再進(jìn)行連接。

其中，costumer表的主鍵是costumerid，order表的主鍵是costumerid。在該查詢中通過(guò)選擇操作首先在costumer表中將所在城市是北京的客戶先篩選出來(lái)，在做連接操作，可以有效的提高查詢效率。

2.5 使用存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程Procedure是一組為了完成特定功能的SQL語(yǔ)句集合，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名稱并給出參數(shù)來(lái)執(zhí)行。存儲(chǔ)過(guò)程中可以包含邏輯控制語(yǔ)句和數(shù)據(jù)操縱語(yǔ)句，它可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及返回值。由于存儲(chǔ)過(guò)程在創(chuàng)建時(shí)即在數(shù)據(jù)庫(kù)服務(wù)器上進(jìn)行了編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中，所以存儲(chǔ)過(guò)程運(yùn)行要比單個(gè)的SQL語(yǔ)句塊要快。同時(shí)由于在調(diào)用時(shí)只需用提供存儲(chǔ)過(guò)程名和必要的參數(shù)信息，所以在一定程度上也可以減少網(wǎng)絡(luò)流量、簡(jiǎn)單網(wǎng)絡(luò)負(fù)擔(dān)。

3 結(jié)束語(yǔ)

在數(shù)據(jù)庫(kù)的開發(fā)和維護(hù)中，查詢的優(yōu)化設(shè)計(jì)可以提高系統(tǒng)性能，特別對(duì)于經(jīng)常要用于查詢且數(shù)據(jù)量大的數(shù)據(jù)庫(kù)系統(tǒng)更顯得重要。SQL查詢優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識(shí)別的語(yǔ)句，充分利用索引，減少表掃描的I/O次數(shù)。在執(zhí)行用戶的查詢請(qǐng)求時(shí)，根據(jù)上述優(yōu)化策略可以有效的減少查詢時(shí)間，提高查詢效率。因此，綜合運(yùn)用SQL Server2008的查詢優(yōu)化技術(shù)，對(duì)于大容量數(shù)據(jù)庫(kù)和海量數(shù)據(jù)庫(kù)的查詢操作，具有實(shí)際的意義。

【參考文獻(xiàn)】

[1]王珊，薩師煊.數(shù)據(jù)庫(kù)系統(tǒng)概論[M].北京：高等教育出版社，2014.

[2]李菲.SQL Server數(shù)據(jù)庫(kù)查詢優(yōu)化方法探究[J].福建電腦，2008（7）.

[3]劉維學(xué).SQL Server查詢優(yōu)化器原理與優(yōu)化實(shí)例分析[J].計(jì)算機(jī)技術(shù)與發(fā)展，2013（11）.

[4]張知青.基于關(guān)系數(shù)據(jù)庫(kù)的查詢方法及優(yōu)化技術(shù)分析[J].煤炭技術(shù)，2012（5）.

[責(zé)任編輯：王偉平]