一、

人們?cè)谑褂肧QL時(shí)往往會(huì)陷入一個(gè)誤區(qū)，即太關(guān)注于所得的結(jié)果是否正確，而忽略了不同的實(shí)現(xiàn)方法之間可能存在的性能差異，這種性能差異在大型的或是復(fù)雜的數(shù)據(jù)庫環(huán)境中（如聯(lián)機(jī)事務(wù)處理OLTP或決策支持系統(tǒng)DSS）中表現(xiàn)得尤為明顯。筆者在工作實(shí)踐中發(fā)現(xiàn)，不良的SQL往往來自于不恰當(dāng)?shù)乃饕O(shè)計(jì)、不充份的連接條件和不可優(yōu)化的where子句。在對(duì)它們進(jìn)行適當(dāng)?shù)膬?yōu)化后，其運(yùn)行速度有了明顯地提高！下面我將從這三個(gè)方面分別進(jìn)行總結(jié)：

---- ? 為了更直觀地說明問題，所有實(shí)例中的SQL運(yùn)行時(shí)間均經(jīng)過測試，不超過１秒的均表示為（< ? 1秒）。

---- ? 測試環(huán)境--

---- ? 主機(jī)：HP ? LH ? II

---- ? 主頻：330MHZ

---- ? 內(nèi)存：128兆

---- ? 操作系統(tǒng)：Operserver5.0.4

----數(shù)據(jù)庫：Sybase11.0.3

一、不合理的索引設(shè)計(jì)

----例：表record有620000行，試看在不同的索引下，下面幾個(gè) ? SQL的運(yùn)行情況：

---- ? 1.在date上建有一非個(gè)群集索引

select ? count(*) ? from ? record ? where ? date ? >

'19991201' ? and ? date ? < ? '19991214'and ? amount ? >

2000 ? (25秒)

select ? date,sum(amount) ? from ? record ? group ? by ? date

(55秒)

select ? count(*) ? from ? record ? where ? date ? >

'19990901' ? and ? place ? in ? ('BJ','SH') ? (27秒)

---- ? 分析：

----date上有大量的重復(fù)值，在非群集索引下，數(shù)據(jù)在物理上隨機(jī)存放在數(shù)據(jù)頁上，在范圍查找時(shí)，必須執(zhí)行一次表掃描才能找到這一范圍內(nèi)的全部行。

---- ? 2.在date上的一個(gè)群集索引

select ? count(*) ? from ? record ? where ? date ? >

'19991201' ? and ? date ? < ? '19991214' ? and ? amount ? >

2000 ? （14秒）

select ? date,sum(amount) ? from ? record ? group ? by ? date

（28秒）

select ? count(*) ? from ? record ? where ? date ? >

'19990901' ? and ? place ? in ? ('BJ','SH')（14秒）

---- ? 分析：

---- ? 在群集索引下，數(shù)據(jù)在物理上按順序在數(shù)據(jù)頁上，重復(fù)值也排列在一起，因而在范圍查找時(shí)，可以先找到這個(gè)范圍的起末點(diǎn)，且只在這個(gè)范圍內(nèi)掃描數(shù)據(jù)頁，避免了大范圍掃描，提高了查詢速度。

---- ? 3.在place，date，amount上的組合索引

select ? count(*) ? from ? record ? where ? date ? >

'19991201' ? and ? date ? < ? '19991214' ? and ? amount ? >

2000 ? （26秒）

select ? date,sum(amount) ? from ? record ? group ? by ? date

（27秒）

select ? count(*) ? from ? record ? where ? date ? >

'19990901' ? and ? place ? in ? ('BJ, ? 'SH')（< ? 1秒）

---- ? 分析：

---- ? 這是一個(gè)不很合理的組合索引，因?yàn)樗那皩?dǎo)列是place，第一和第二條SQL沒有引用place，因此也沒有利用上索引；第三個(gè)SQL使用了place，且引用的所有列都包含在組合索引中，形成了索引覆蓋，所以它的速度是非常快的。

---- ? 4.在date，place，amount上的組合索引

select ? count(*) ? from ? record ? where ? date ? >

'19991201' ? and ? date ? < ? '19991214' ? and ? amount ? >

2000(< ? 1秒)

select ? date,sum(amount) ? from ? record ? group ? by ? date

（11秒）

select ? count(*) ? from ? record ? where ? date ? >

'19990901' ? and ? place ? in ? ('BJ','SH')（< ? 1秒）

---- ? 分析：

---- ? 這是一個(gè)合理的組合索引。它將date作為前導(dǎo)列，使每個(gè)SQL都可以利用索引，并且在第一和第三個(gè)SQL中形成了索引覆蓋，因而性能達(dá)到了最優(yōu)。

---- ? 5.總結(jié)：

---- ? 缺省情況下建立的索引是非群集索引，但有時(shí)它并不是最佳的；合理的索引設(shè)計(jì)要建立在對(duì)各種查詢的分析和預(yù)測上。一般來說：

---- ? ①.有大量重復(fù)值、且經(jīng)常有范圍查詢

（between, ? >,< ? ，>=,< ? =）和order ? by

、group ? by發(fā)生的列，可考慮建立群集索引；

---- ? ②.經(jīng)常同時(shí)存取多列，且每列都含有重復(fù)值可考慮建立組合索引；

---- ? ③.組合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列。

二、不充份的連接條件：

---- ? 例：表card有7896行，在card_no上有一個(gè)非聚集索引，表account有191122行，在 ? account_no上有一個(gè)非聚集索引，試看在不同的表連接條件下，兩個(gè)SQL的執(zhí)行情況：

select ? sum(a.amount) ? from ? account ? a,

card ? b ? where ? a.card_no ? = ? b.card_no（20秒）

---- ? 將SQL改為：

select ? sum(a.amount) ? from ? account ? a,

card ? b ? where ? a.card_no ? = ? b.card_no ? and ? a.

account_no=b.account_no（< ? 1秒）

---- ? 分析：

---- ? 在第一個(gè)連接條件下，最佳查詢方案是將account作外層表，card作內(nèi)層表，利用card上的索引，其I/O次數(shù)可由以下公式估算為：

---- ? 外層表account上的22541頁+（外層表account的191122行*內(nèi)層表card上對(duì)應(yīng)外層表第一行所要查找的3頁）=595907次I/O

---- ? 在第二個(gè)連接條件下，最佳查詢方案是將card作外層表，account作內(nèi)層表，利用account上的索引，其I/O次數(shù)可由以下公式估算為：

---- ? 外層表card上的1944頁+（外層表card的7896行*內(nèi)層表account上對(duì)應(yīng)外層表每一行所要查找的4頁）= ? 33528次I/O

---- ? 可見，只有充份的連接條件，真正的最佳方案才會(huì)被執(zhí)行。

---- ? 總結(jié)：

---- ? 1.多表操作在被實(shí)際執(zhí)行前，查詢優(yōu)化器會(huì)根據(jù)連接條件，列出幾組可能的連接方案并從中找出系統(tǒng)開銷最小的最佳方案。連接條件要充份考慮帶有索引的表、行數(shù)多的表；內(nèi)外表的選擇可由公式：外層表中的匹配行數(shù)*內(nèi)層表中每一次查找的次數(shù)確定，乘積最小為最佳方案。

---- ? 2.查看執(zhí)行方案的方法-- ? 用set ? showplanon，打開showplan選項(xiàng)，就可以看到連接順序、使用何種索引的信息；想看更詳細(xì)的信息，需用sa角色執(zhí)行dbcc(3604,310,302)。

三、不可優(yōu)化的where子句

---- ? 1.例：下列SQL條件語句中的列都建有恰當(dāng)?shù)乃饕珗?zhí)行速度卻非常慢：

select ? * ? from ? record ? where

substring(card_no,1,4)='5378'(13秒)

select ? * ? from ? record ? where

amount/30< ? 1000（11秒）

select ? * ? from ? record ? where

convert(char(10),date,112)='19991201'（10秒）

---- ? 分析：

---- ? where子句中對(duì)列的任何操作結(jié)果都是在SQL運(yùn)行時(shí)逐列計(jì)算得到的，因此它不得不進(jìn)行表搜索，而沒有使用該列上面的索引；如果這些結(jié)果在查詢編譯時(shí)就能得到，那么就可以被SQL優(yōu)化器優(yōu)化，使用索引，避免表搜索，因此將SQL重寫成下面這樣：

select ? * ? from ? record ? where ? card_no ? like

'5378%'（< ? 1秒）

select ? * ? from ? record ? where ? amount

< ? 1000*30（< ? 1秒）

select ? * ? from ? record ? where ? date= ? '1999/12/01'

（< ? 1秒）

---- ? 你會(huì)發(fā)現(xiàn)SQL明顯快起來！

---- ? 2.例：表stuff有200000行，id_no上有非群集索引，請(qǐng)看下面這個(gè)SQL：

select ? count(*) ? from ? stuff ? where ? id_no ? in('0','1')

（23秒）

---- ? 分析：

---- ? where條件中的'in'在邏輯上相當(dāng)于'or'，所以語法分析器會(huì)將in ? ('0','1')轉(zhuǎn)化為id_no ? ='0' ? or ? id_no='1'來執(zhí)行。我們期望它會(huì)根據(jù)每個(gè)or子句分別查找，再將結(jié)果相加，這樣可以利用id_no上的索引；但實(shí)際上（根據(jù)showplan）,它卻采用了"OR策略"，即先取出滿足每個(gè)or子句的行，存入臨時(shí)數(shù)據(jù)庫的工作表中，再建立唯一索引以去掉重復(fù)行，最后從這個(gè)臨時(shí)表中計(jì)算結(jié)果。因此，實(shí)際過程沒有利用id_no上索引，并且完成時(shí)間還要受tempdb數(shù)據(jù)庫性能的影響。

---- ? 實(shí)踐證明，表的行數(shù)越多，工作表的性能就越差，當(dāng)stuff有620000行時(shí)，執(zhí)行時(shí)間竟達(dá)到220秒！還不如將or子句分開：

select ? count(*) ? from ? stuff ? where ? id_no='0'

select ? count(*) ? from ? stuff ? where ? id_no='1'

---- ? 得到兩個(gè)結(jié)果，再作一次加法合算。因?yàn)槊烤涠际褂昧怂饕?，?zhí)行時(shí)間只有3秒，在620000行下，時(shí)間也只有4秒?；蛘?，用更好的方法，寫一個(gè)簡單的存儲(chǔ)過程：

create ? proc ? count_stuff ? as

declare ? @a ? int

declare ? @b ? int

declare ? @c ? int

declare ? @d ? char(10)

begin

select ? @a=count(*) ? from ? stuff ? where ? id_no='0'

select ? @b=count(*) ? from ? stuff ? where ? id_no='1'

end

select ? @c=@a+@b

select ? @d=convert(char(10),@c)

print ? @d

---- ? 直接算出結(jié)果，執(zhí)行時(shí)間同上面一樣快！

---- ? 總結(jié)：

---- ? 可見，所謂優(yōu)化即where子句利用了索引，不可優(yōu)化即發(fā)生了表掃描或額外開銷。

---- ? 1.任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊。

---- ? 2.in、or子句常會(huì)使用工作表，使索引失效；如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開；拆開的子句中應(yīng)該包含索引。

---- ? 3.要善于使用存儲(chǔ)過程，它使SQL變得更加靈活和高效。

---- ? 從以上這些例子可以看出，SQL優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識(shí)別的語句，充份利用索引，減少表掃描的I/O次數(shù)，盡量避免表搜索的發(fā)生。其實(shí)SQL的性能優(yōu)化是一個(gè)復(fù)雜的過程，上述這些只是在應(yīng)用層次的一種體現(xiàn)，深入研究還會(huì)涉及數(shù)據(jù)庫層的資源配置、網(wǎng)絡(luò)層的流量控制以及操作系統(tǒng)層的總體設(shè)計(jì)。

二、

1、硬件調(diào)整性能

最有可能影響性能的是磁盤和網(wǎng)絡(luò)吞吐量,解決辦法

擴(kuò)大虛擬內(nèi)存，并保證有足夠可以擴(kuò)充的空間；把數(shù)據(jù)庫服務(wù)器上的不必要服務(wù)關(guān)閉掉

把數(shù)據(jù)庫服務(wù)器和主域服務(wù)器分開

把SQL數(shù)據(jù)庫服務(wù)器的吞吐量調(diào)為最大

在具有一個(gè)以上處理器的機(jī)器上運(yùn)行SQL

2、調(diào)整數(shù)據(jù)庫

若對(duì)該表的查詢頻率比較高，則建立索引；建立索引時(shí)，想盡對(duì)該表的所有查詢搜索操作，按照where選擇條件建立索引，盡量為整型鍵建立為有且只有一個(gè)簇集索引，數(shù)據(jù)在物理上按順序在數(shù)據(jù)頁上，縮短查找范圍，為在查詢經(jīng)常使用的全部列建立非簇集索引，能最大地覆蓋查詢；但是索引不可太多，執(zhí)行UPDATE ? DELETE ? INSERT語句需要用于維護(hù)這些索引的開銷量急劇增加；避免在索引中有太多的索引鍵；避免使用大型數(shù)據(jù)類型的列為索引；保證每個(gè)索引鍵值有少數(shù)行。

3、使用存儲(chǔ)過程

應(yīng)用程序的實(shí)現(xiàn)過程中，能夠采用存儲(chǔ)過程實(shí)現(xiàn)的對(duì)數(shù)據(jù)庫的操作盡量通過存儲(chǔ)過程來實(shí)現(xiàn)，因?yàn)榇鎯?chǔ)過程是存放在數(shù)據(jù)庫服務(wù)器上的一次性被設(shè)計(jì)、編碼、測試，并被再次使用，需要執(zhí)行該任務(wù)的應(yīng)用可以簡單地執(zhí)行存儲(chǔ)過程，并且只返回結(jié)果集或者數(shù)值，這樣不僅可以使程序模塊化，同時(shí)提高響應(yīng)速度，減少網(wǎng)絡(luò)流量，并且通過輸入?yún)?shù)接受輸入，使得在應(yīng)用中完成邏輯的一致性實(shí)現(xiàn)。

4、應(yīng)用程序結(jié)構(gòu)和算法

建立查詢條件索引僅僅是提高速度的前提條件，響應(yīng)速度的提高還依賴于對(duì)索引的使用。因?yàn)槿藗冊(cè)谑褂肧QL時(shí)往往會(huì)陷入一個(gè)誤區(qū)，即太關(guān)注于所得的結(jié)果是否正確，特別是對(duì)數(shù)據(jù)量不是特別大的數(shù)據(jù)庫操作時(shí)，是否建立索引和使用索引的好壞對(duì)程序的響應(yīng)速度并不大，因此程序員在書寫程序時(shí)就忽略了不同的實(shí)現(xiàn)方法之間可能存在的性能差異，這種性能差異在數(shù)據(jù)量特別大時(shí)或者大型的或是復(fù)雜的數(shù)據(jù)庫環(huán)境中（如聯(lián)機(jī)事務(wù)處理OLTP或決策支持系統(tǒng)DSS）中表現(xiàn)得尤為明顯。在工作實(shí)踐中發(fā)現(xiàn)，不良的SQL往往來自于不恰當(dāng)?shù)乃饕O(shè)計(jì)、不充份的連接條件和不可優(yōu)化的where子句。在對(duì)它們進(jìn)行適當(dāng)?shù)膬?yōu)化后，其運(yùn)行速度有了明顯地提高！

因此在書寫應(yīng)用程序的SQL的 ? where子句時(shí)，注意以下幾種情況:

1、避免使用不兼容的數(shù)據(jù)類型。例如float和int、char和varchar、binary和varbinary是不兼容的。數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進(jìn)行的優(yōu)化操作。例如:

SELECT ? name ? FROM ? employee ? WHERE ? salary ? ＞ ? 60000

在這條語句中,如salary字段是money型的,則優(yōu)化器很難對(duì)其進(jìn)行優(yōu)化,因?yàn)?0000是個(gè)整型數(shù)。我們應(yīng)當(dāng)在編程時(shí)將整型轉(zhuǎn)化成為錢幣型,而不要等到運(yùn)行時(shí)轉(zhuǎn)化。

2、避免對(duì)搜索參數(shù)使用其它數(shù)學(xué)操作符,如要將

SELECT ? name ? FROM ? employee ? WHERE ? SUBSTRING(id, ? 1, ? 1) ? = ? 'B'

SELECT ? name ? FROM ? emplyee ? WHERE ? salary ? * ? 12 ? ＞ ? 30000

寫成為:

SELECT ? name ? FROM ? employee ? WHERE ? id ? like ? ‘B%'

SELECT ? name ? FROM ? emplyee ? WHERE ? salary ? ＞ ? 3000

3、、避免使用!=或＜＞等這樣的操作符,因?yàn)檫@會(huì)使系統(tǒng)無法使用索引,而只能直接搜索表中的數(shù)據(jù)。例如:

SELECT ? id ? FROM ? employeeWHERE ? id ? != ? 'B%'

優(yōu)化器將無法通過索引來確定將要命中的行數(shù),因此需要搜索該表的所有行。

4、在應(yīng)用程序中，保證在實(shí)現(xiàn)功能的基礎(chǔ)上，盡量減少對(duì)數(shù)據(jù)庫的訪問次數(shù)；通過搜索參數(shù)，盡量減少對(duì)表的訪問行數(shù),最小化結(jié)果集，從而減輕網(wǎng)絡(luò)負(fù)擔(dān)；能夠分開的操作盡量分開處理，提高每次的響應(yīng)速度；在數(shù)據(jù)窗口使用SQL時(shí)，盡量把使用的索引放在選擇的首列；算法的結(jié)構(gòu)盡量簡單；在查詢時(shí)，不要過多地使用通配符如select ? * ? 語句；盡量不要在應(yīng)用中使用數(shù)據(jù)庫游標(biāo)，游標(biāo)是非常有用的工具，但比使用常規(guī)的、面向集的SQL語句需要更大的開銷；按照特定順序提取數(shù)據(jù)的查找。

上面我們提到的是一些基本的提高查詢速度的注意事項(xiàng),但是在更多的情況下,程序員往往需要反復(fù)試驗(yàn)比較不同的語句以得到最佳方案。此外更為重要的是需要數(shù)據(jù)庫管理員在數(shù)據(jù)庫的服務(wù)器一端調(diào)整數(shù)據(jù)庫管理系統(tǒng)的參數(shù),以得到更快的響應(yīng)性能。

三、

1、存儲(chǔ)

將硬盤分成NTFS格式，NTFS比FAT32快，并看你的數(shù)據(jù)文件大小，1G以上你可以采用多數(shù)據(jù)庫文件，這樣可以將存取負(fù)載分散到多個(gè)物理硬盤或磁盤陣列上。

2、tempdb

tempdb也應(yīng)該被單獨(dú)的物理硬盤或磁盤陣列上,建議放在RAID ? 0上，這樣它的性能最高,不要對(duì)它設(shè)置最大值讓它自動(dòng)增長

3、日志文件

日志文件也應(yīng)該和數(shù)據(jù)文件分開在不同的理硬盤或磁盤陣列上，這樣也可以提高硬盤I/O性能。

4、分區(qū)視圖

就是將你的數(shù)據(jù)水平分割在集群服務(wù)器上，它適合大規(guī)模OLTP,SQL群集上，如果你數(shù)據(jù)庫不是訪問特別大不建議使用。

5、簇索引

你的表一定有個(gè)簇索引，在使用簇索引查詢的時(shí)候，區(qū)塊查詢是最快的，如用between，應(yīng)為他是物理連續(xù)的，你應(yīng)該盡量減少對(duì)它的updaet,應(yīng)為這可以使它物理不連續(xù)。

6、非簇索引

非簇索引與物理順序無關(guān)，設(shè)計(jì)它時(shí)必須有高度的可選擇性，可以提高查詢速度，但對(duì)表update的時(shí)候這些非簇索引會(huì)影響速度，且占用空間大，如果你愿意用空間和修改時(shí)間換取速度可以考慮。

7、索引視圖

如果在視圖上建立索引,那視圖的結(jié)果集就會(huì)被存儲(chǔ)起來，對(duì)與特定的查詢性能可以提高很多，但同樣對(duì)update語句時(shí)它也會(huì)嚴(yán)重減低性能，一般用在數(shù)據(jù)相對(duì)穩(wěn)定的數(shù)據(jù)倉庫中。

8、維護(hù)索引

你在將索引建好后，定期維護(hù)是很重要的，用dbcc ? showcontig來觀察頁密度、掃描密度等等，及時(shí)用dbcc ? indexdefrag來整理表或視圖的索引,在必要的時(shí)候用dbcc ? dbreindex來重建索引可以受到良好的效果。

不論你是用幾個(gè)表1、2、3點(diǎn)都可以提高一定的性能，5、6、8點(diǎn)你是必須做的，至于4、7點(diǎn)看你的需求，我個(gè)人是不建議的。

四、

．使用臨時(shí)表加速查詢

把表的一個(gè)子集進(jìn)行排序并創(chuàng)建臨時(shí)表，有時(shí)能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器的工作。例如：

SELECT ? cust.name，rcvbles.balance，……other ? columns

FROM ? cust，rcvbles

WHERE ? cust.customer_id ? = ? rcvlbes.customer_id

AND ? rcvblls.balance>0

AND ? cust.postcode>“98000”

ORDER ? BY ? cust.name

如果這個(gè)查詢要被執(zhí)行多次而不止一次，可以把所有未付款的客戶找出來放在一個(gè)臨時(shí)文件中，并按客戶的名字進(jìn)行排序：

SELECT ? cust.name，rcvbles.balance，……other ? columns

FROM ? cust，rcvbles

WHERE ? cust.customer_id ? = ? rcvlbes.customer_id

AND ? rcvblls.balance>0

ORDER ? BY ? cust.name

INTO ? TEMP ? cust_with_balance

然后以下面的方式在臨時(shí)表中查詢：

SELECT ? ＊ ? FROM ? cust_with_balance

WHERE ? postcode>“98000”

臨時(shí)表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，所以查詢工作量可以得到大幅減少。

注意：臨時(shí)表創(chuàng)建后不會(huì)反映主表的修改。在主表中數(shù)據(jù)頻繁修改的情況下，注意不要丟失數(shù)據(jù)。

7．用排序來取代非順序存取

非順序磁盤存取是最慢的操作，表現(xiàn)在磁盤存取臂的來回移動(dòng)。SQL語句隱藏了這一情況，使得我們?cè)趯憫?yīng)用程序時(shí)很容易寫出要求存取大量非順序頁的查詢。

有些時(shí)候，用數(shù)據(jù)庫的排序能力來替代非順序的存取能改進(jìn)查詢。

實(shí)例分析

下面我們舉一個(gè)制造公司的例子來說明如何進(jìn)行查詢優(yōu)化。制造公司數(shù)據(jù)庫中包括3個(gè)表，模式如下所示：

1．part表

零件號(hào)零件描述其他列

（part_num）（part_desc）（other ? column）

102，032Seageat ? 30G ? disk……

500，049Novel ? 10M ? network ? card……

……

2．vendor表

廠商號(hào)廠商名其他列

（vendor ? _num）（vendor_name） ? （other ? column）

910，257Seageat ? Corp……

523，045IBM ? Corp……

……

3．parven表

零件號(hào)廠商號(hào)零件數(shù)量

（part_num）（vendor_num）（part_amount）

102，032910，2573,450,000

234，423321，0014，000，000

……

下面的查詢將在這些表上定期運(yùn)行，并產(chǎn)生關(guān)于所有零件數(shù)量的報(bào)表：

SELECT ? part_desc，vendor_name，part_amount

FROM ? part，vendor，parven

WHERE ? part.part_num=parven.part_num

AND ? parven.vendor_num ? = ? vendor.vendor_num

ORDER ? BY ? part.part_num

如果不建立索引，上述查詢代碼的開銷將十分巨大。為此，我們?cè)诹慵?hào)和廠商號(hào)上建立索引。索引的建立避免了在嵌套中反復(fù)掃描。關(guān)于表與索引的統(tǒng)計(jì)信息如下：

表行尺寸行數(shù)量每頁行數(shù)量數(shù)據(jù)頁數(shù)量

（table）(row ? size)（Row ? count）（Rows/Pages）（Data ? Pages）

part15010，00025400

Vendor1501，000 ? 2540

Parven13 ? 15，000300 ? 50

索引鍵尺寸每頁鍵數(shù)量頁面數(shù)量

（Indexes）(Key ? Size)（Keys/Page)(Leaf ? Pages）

part450020

Vendor45002

Parven825060

看起來是個(gè)相對(duì)簡單的3表連接，但是其查詢開銷是很大的。通過查看系統(tǒng)表可以看到，在part_num上和vendor_num上有簇索引，因此索引是按照物理順序存放的。parven表沒有特定的存放次序。這些表的大小說明從緩沖頁中非順序存取的成功率很小。此語句的優(yōu)化查詢規(guī)劃是：首先從part中順序讀取400頁，然后再對(duì)parven表非順序存取1萬次，每次2頁（一個(gè)索引頁、一個(gè)數(shù)據(jù)頁），總計(jì)2萬個(gè)磁盤頁，最后對(duì)vendor表非順序存取1.5萬次，合3萬個(gè)磁盤頁?？梢钥闯鲈谶@個(gè)索引好的連接上花費(fèi)的磁盤存取為5.04萬次。

實(shí)際上，我們可以通過使用臨時(shí)表分3個(gè)步驟來提高查詢效率：

1．從parven表中按vendor_num的次序讀數(shù)據(jù)：

SELECT ? part_num，vendor_num，price

FROM ? parven

ORDER ? BY ? vendor_num

INTO ? temp ? pv_by_vn

這個(gè)語句順序讀parven（50頁），寫一個(gè)臨時(shí)表（50頁），并排序。假定排序的開銷為200頁，總共是300頁。

2．把臨時(shí)表和vendor表連接，把結(jié)果輸出到一個(gè)臨時(shí)表，并按part_num排序：

SELECT ? pv_by_vn，＊ ? vendor.vendor_num

FROM ? pv_by_vn，vendor

WHERE ? pv_by_vn.vendor_num=vendor.vendor_num

ORDER ? BY ? pv_by_vn.part_num

INTO ? TMP ? pvvn_by_pn

DROP ? TABLE ? pv_by_vn

這個(gè)查詢讀取pv_by_vn(50頁)，它通過索引存取vendor表1.5萬次，但由于按vendor_num次序排列，實(shí)際上只是通過索引順序地讀vendor表（40＋2=42頁），輸出的表每頁約95行，共160頁。寫并存取這些頁引發(fā)5＊160=800次的讀寫，索引共讀寫892頁。

3．把輸出和part連接得到最后的結(jié)果：

SELECT ? pvvn_by_pn.＊，part.part_desc

FROM ? pvvn_by_pn，part

WHERE ? pvvn_by_pn.part_num=part.part_num

DROP ? TABLE ? pvvn_by_pn

這樣，查詢順序地讀pvvn_by_pn(160頁)，通過索引讀part表1.5萬次，由于建有索引，所以實(shí)際上進(jìn)行1772次磁盤讀寫，優(yōu)化比例為30∶1。筆者在Informix ? Dynamic ? Sever上做同樣的實(shí)驗(yàn)，發(fā)現(xiàn)在時(shí)間耗費(fèi)上的優(yōu)化比例為5∶1(如果增加數(shù)據(jù)量，比例可能會(huì)更大)。

小結(jié)

20％的代碼用去了80％的時(shí)間，這是程序設(shè)計(jì)中的一個(gè)著名定律，在數(shù)據(jù)庫應(yīng)用程序中也同樣如此。我們的優(yōu)化要抓住關(guān)鍵問題，對(duì)于數(shù)據(jù)庫應(yīng)用程序來說，重點(diǎn)在于SQL的執(zhí)行效率。查詢優(yōu)化的重點(diǎn)環(huán)節(jié)是使得數(shù)據(jù)庫服務(wù)器少從磁盤中讀數(shù)據(jù)以及順序讀頁而不是非順序讀頁。

頂

0

踩