如何讓你的SQL運行得更快 [推薦！！！]
人們在使用SQL時往往會陷入一個誤區(qū)，即太關(guān)注于所得的結(jié)果是否正確，而忽略
了不同的實現(xiàn)方法之間可能存在的性能差異，這種性能差異在大型的或是復(fù)雜的數(shù)據(jù)庫
環(huán)境中（如聯(lián)機(jī)事務(wù)處理OLTP或決策支持系統(tǒng)DSS）中表現(xiàn)得尤為明顯。筆者在工作實踐
中發(fā)現(xiàn)，不良的SQL往往來自于不恰當(dāng)?shù)乃饕O(shè)計、不充份的連接條件和不可優(yōu)化的whe
re子句。在對它們進(jìn)行適當(dāng)?shù)膬?yōu)化后，其運行速度有了明顯地提高！下面我將從這三個
方面分別進(jìn)行總結(jié)：
---- 為了更直觀地說明問題，所有實例中的SQL運行時間均經(jīng)過測試，不超過１秒的均
表示為（< 1秒）。
---- 測試環(huán)境--
---- 主機(jī)：HP LH II
---- 主頻：330MHZ
---- 內(nèi)存：128兆
---- 操作系統(tǒng)：Operserver5.0.4
----數(shù)據(jù)庫：Sybase11.0.3
一、不合理的索引設(shè)計
----例：表record有620000行，試看在不同的索引下，下面幾個 SQL的運行情況：
---- 1.在date上建有一非個群集索引
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ù)頁上，在
范圍查找時，必須執(zhí)行一次表掃描才能找到這一范圍內(nèi)的全部行。
---- 2.在date上的一個群集索引
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ù)值也排列在一起，因而在范
圍查找時，可以先找到這個范圍的起末點，且只在這個范圍內(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秒）
---- 分析：
---- 這是一個不很合理的組合索引，因為它的前導(dǎo)列是place，第一和第二條SQL沒有引
用place，因此也沒有利用上索引；第三個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秒）
---- 分析：
---- 這是一個合理的組合索引。它將date作為前導(dǎo)列，使每個SQL都可以利用索引，并
且在第一和第三個SQL中形成了索引覆蓋，因而性能達(dá)到了最優(yōu)。
---- 5.總結(jié)：
---- 缺省情況下建立的索引是非群集索引，但有時它并不是最佳的；合理的索引設(shè)計要
建立在對各種查詢的分析和預(yù)測上。一般來說：
---- ①.有大量重復(fù)值、且經(jīng)常有范圍查詢
（between, >,< ，>=,< =）和order by
、group by發(fā)生的列，可考慮建立群集索引；
---- ②.經(jīng)常同時存取多列，且每列都含有重復(fù)值可考慮建立組合索引；
---- ③.組合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列。
二、不充份的連接條件：
---- 例：表card有7896行，在card_no上有一個非聚集索引，表account有191122行，在
account_no上有一個非聚集索引，試看在不同的表連接條件下，兩個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秒）
---- 分析：
---- 在第一個連接條件下，最佳查詢方案是將account作外層表，card作內(nèi)層表，利用
card上的索引，其I/O次數(shù)可由以下公式估算為：
---- 外層表account上的22541頁+（外層表account的191122行*內(nèi)層表card上對應(yīng)外層
表第一行所要查找的3頁）=595907次I/O
---- 在第二個連接條件下，最佳查詢方案是將card作外層表，account作內(nèi)層表，利用
account上的索引，其I/O次數(shù)可由以下公式估算為：
---- 外層表card上的1944頁+（外層表card的7896行*內(nèi)層表account上對應(yīng)外層表每一
行所要查找的4頁）= 33528次I/O
---- 可見，只有充份的連接條件，真正的最佳方案才會被執(zhí)行。
---- 總結(jié)：
---- 1.多表操作在被實際執(zhí)行前，查詢優(yōu)化器會根據(jù)連接條件，列出幾組可能的連接方
案并從中找出系統(tǒng)開銷最小的最佳方案。連接條件要充份考慮帶有索引的表、行數(shù)多的
表；內(nèi)外表的選擇可由公式：外層表中的匹配行數(shù)*內(nèi)層表中每一次查找的次數(shù)確定，乘
積最小為最佳方案。
---- 2.查看執(zhí)行方案的方法-- 用set showplanon，打開showplan選項，就可以看到連
接順序、使用何種索引的信息；想看更詳細(xì)的信息，需用sa角色執(zhí)行dbcc(3604,310,30
2)。
三、不可優(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子句中對列的任何操作結(jié)果都是在SQL運行時逐列計算得到的，因此它不得不
進(jìn)行表搜索，而沒有使用該列上面的索引；如果這些結(jié)果在查詢編譯時就能得到，那么
就可以被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秒）
---- 你會發(fā)現(xiàn)SQL明顯快起來！
---- 2.例：表stuff有200000行，id_no上有非群集索引，請看下面這個SQL：
select count(*) from stuff where id_no in('0','1')
（23秒）
---- 分析：
---- where條件中的'in'在邏輯上相當(dāng)于'or'，所以語法分析器會將in ('0','1')轉(zhuǎn)化
為id_no ='0' or id_no='1'來執(zhí)行。我們期望它會根據(jù)每個or子句分別查找，再將結(jié)果
相加，這樣可以利用id_no上的索引；但實際上（根據(jù)showplan）,它卻采用了"OR策略"
，即先取出滿足每個or子句的行，存入臨時數(shù)據(jù)庫的工作表中，再建立唯一索引以去掉
重復(fù)行，最后從這個臨時表中計算結(jié)果。因此，實際過程沒有利用id_no上索引，并且完
成時間還要受tempdb數(shù)據(jù)庫性能的影響。
---- 實踐證明，表的行數(shù)越多，工作表的性能就越差，當(dāng)stuff有620000行時，執(zhí)行時
間竟達(dá)到220秒！還不如將or子句分開：
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
---- 得到兩個結(jié)果，再作一次加法合算。因為每句都使用了索引，執(zhí)行時間只有3秒，
在620000行下，時間也只有4秒。或者，用更好的方法，寫一個簡單的存儲過程：
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í)行時間同上面一樣快！
---- 總結(jié)：
---- 可見，所謂優(yōu)化即where子句利用了索引，不可優(yōu)化即發(fā)生了表掃描或額外開銷。
---- 1.任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計算表達(dá)式等等，查詢時
要盡可能將操作移至等號右邊。
---- 2.in、or子句常會使用工作表，使索引失效；如果不產(chǎn)生大量重復(fù)值，可以考慮把
子句拆開；拆開的子句中應(yīng)該包含索引。
---- 3.要善于使用存儲過程，它使SQL變得更加靈活和高效。
---- 從以上這些例子可以看出，SQL優(yōu)化的實質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可
以識別的語句，充份利用索引，減少表掃描的I/O次數(shù)，盡量避免表搜索的發(fā)生。其實S
QL的性能優(yōu)化是一個復(fù)雜的過程，上述這些只是在應(yīng)用層次的一種體現(xiàn)，深入研究還會
涉及數(shù)據(jù)庫層的資源配置、網(wǎng)絡(luò)層的流量控制以及操作系統(tǒng)層的總體設(shè)計。
1．合理使用索引
索引是數(shù)據(jù)庫中重要的數(shù)據(jù)結(jié)構(gòu)，它的根本目的就是為了提高查詢效率。現(xiàn)在大多數(shù)的數(shù)據(jù)庫產(chǎn)品都采用IBM最先提出的ISAM索引結(jié)構(gòu)。索引的使用要恰到好處，其使用原則如下：
●在經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的列上建立索引，而不經(jīng)常連接的字段則由優(yōu)化器自動生成索引。
●在頻繁進(jìn)行排序或分組（即進(jìn)行g(shù)roup by或order by操作）的列上建立索引。
●在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴(yán)重降低更新速度。
●如果待排序的列有多個，可以在這些列上建立復(fù)合索引（compound index）。
●使用系統(tǒng)工具。如Informix數(shù)據(jù)庫有一個tbcheck工具，可以在可疑的索引上進(jìn)行檢查。在一些數(shù)據(jù)庫服務(wù)器上，索引可能失效或者因為頻繁操作而使得讀取效率降低，如果一個使用索引的查詢不明不白地慢下來，可以試著用tbcheck工具檢查索引的完整性，必要時進(jìn)行修復(fù)。另外，當(dāng)數(shù)據(jù)庫表更新大量數(shù)據(jù)后，刪除并重建索引可以提高查詢速度。
2．避免或簡化排序
應(yīng)當(dāng)簡化或避免對大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時，優(yōu)化器就避免了排序的步驟。以下是一些影響因素：
●索引中不包括一個或幾個待排序的列；
●group by或order by子句中列的次序與索引的次序不一樣；
●排序的列來自不同的表。
為了避免不必要的排序，就要正確地增建索引，合理地合并數(shù)據(jù)庫表（盡管有時可能影響表的規(guī)范化，但相對于效率的提高是值得的）。如果排序不可避免，那么應(yīng)當(dāng)試圖簡化它，如縮小排序的列的范圍等。
3．消除對大型表行數(shù)據(jù)的順序存取
在嵌套查詢中，對表的順序存取對查詢效率可能產(chǎn)生致命的影響。比如采用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那么這個查詢就要查詢10億行數(shù)據(jù)。避免這種情況的主要方法就是對連接的列進(jìn)行索引。例如，兩個表：學(xué)生表（學(xué)號、姓名、年齡……）和選課表（學(xué)號、課程號、成績）。如果兩個表要做連接，就要在“學(xué)號”這個連接字段上建立索引。
還可以使用并集來避免順序存取。盡管在所有的檢查列上都有索引，但某些形式的where子句強迫優(yōu)化器使用順序存取。下面的查詢將強迫對orders表執(zhí)行順序操作：
SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
雖然在customer_num和order_num上建有索引，但是在上面的語句中優(yōu)化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應(yīng)該改為如下語句：
SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROM orders WHERE order_num=1008
這樣就能利用索引路徑處理查詢。
4．避免相關(guān)子查詢
一個列的標(biāo)簽同時在主查詢和where子句中的查詢中出現(xiàn)，那么很可能當(dāng)主查詢中的列值改變之后，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應(yīng)當(dāng)盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。
5．避免困難的正規(guī)表達(dá)式
MATCHES和LIKE關(guān)鍵字支持通配符匹配，技術(shù)上叫正規(guī)表達(dá)式。但這種匹配特別耗費時間。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引，在這種情況下也還是采用順序掃描的方式。如果把語句改為SELECT ＊ FROM customer WHERE zipcode >“98000”，在執(zhí)行查詢時就會利用索引來查詢，顯然會大大提高速度。
另外，還要避免非開始的子串。例如語句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“80”，在where子句中采用了非開始子串，因而這個語句也不會使用索引。
6．使用臨時表加速查詢
把表的一個子集進(jìn)行排序并創(chuàng)建臨時表，有時能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優(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
如果這個查詢要被執(zhí)行多次而不止一次，可以把所有未付款的客戶找出來放在一個臨時文件中，并按客戶的名字進(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
然后以下面的方式在臨時表中查詢：
SELECT ＊ FROM cust_with_balance
WHERE postcode>“98000”
臨時表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，所以查詢工作量可以得到大幅減少。
注意：臨時表創(chuàng)建后不會反映主表的修改。在主表中數(shù)據(jù)頻繁修改的情況下，注意不要丟失數(shù)據(jù)。
7．用排序來取代非順序存取
非順序磁盤存取是最慢的操作，表現(xiàn)在磁盤存取臂的來回移動。SQL語句隱藏了這一情況，使得我們在寫應(yīng)用程序時很容易寫出要求存取大量非順序頁的查詢。
有些時候，用數(shù)據(jù)庫的排序能力來替代非順序的存取能改進(jìn)查詢。
3.優(yōu)化 tempdb 性能
對 tempdb 數(shù)據(jù)庫的物理位置和數(shù)據(jù)庫選項設(shè)置的一般建議包括：
使 tempdb 數(shù)據(jù)庫得以按需自動擴(kuò)展。這確保在執(zhí)行完成前不終止查詢，該查詢所生成的存儲在 tempdb 數(shù)據(jù)庫內(nèi)的中間結(jié)果集比預(yù)期大得多。
將 tempdb 數(shù)據(jù)庫文件的初始大小設(shè)置為合理的大小，以避免當(dāng)需要更多空間時文件自動擴(kuò)展。如果 tempdb 數(shù)據(jù)庫擴(kuò)展得過于頻繁，性能會受不良影響。
將文件增長增量百分比設(shè)置為合理的大小，以避免 tempdb 數(shù)據(jù)庫文件按太小的值增長。如果文件增長幅度與寫入 tempdb 數(shù)據(jù)庫的數(shù)據(jù)量相比太小，則 tempdb 數(shù)據(jù)庫可能需要始終擴(kuò)展，因而將妨害性能。
將 tempdb 數(shù)據(jù)庫放在快速 I/O 子系統(tǒng)上以確保好的性能。在多個磁盤上條帶化 tempdb 數(shù)據(jù)庫以獲得更好的性能。將 tempdb 數(shù)據(jù)庫放在除用戶數(shù)據(jù)庫所使用的磁盤之外的磁盤上。有關(guān)更多信息，請參見擴(kuò)充數(shù)據(jù)庫。
4.優(yōu)化服務(wù)器:
使用內(nèi)存配置選項優(yōu)化服務(wù)器性能
Microsoft® SQL Server™ 2000 的內(nèi)存管理組件消除了對 SQL Server 可用的內(nèi)存進(jìn)行手工管理的需要。SQL Server 在啟動時根據(jù)操作系統(tǒng)和其它應(yīng)用程序當(dāng)前正在使用的內(nèi)存量，動態(tài)確定應(yīng)分配的內(nèi)存量。當(dāng)計算機(jī)和SQL Server 上的負(fù)荷更改時，分配的內(nèi)存也隨之更改。有關(guān)更多信息，請參見內(nèi)存構(gòu)架。
下列服務(wù)器配置選項可用于配置內(nèi)存使用并影響服務(wù)器性能：
min server memory
max server memory
max worker threads
index create memory
min memory per query
min server memory 服務(wù)器配置選項可用于確保 SQL Server 在達(dá)到該值后不會釋放內(nèi)存。可以基于 SQL Server 的大小及活動將該配置選項設(shè)置為特定的值。如果選擇設(shè)置此選項，必須為操作系統(tǒng)和其他程序留出足夠的內(nèi)存。如果操作系統(tǒng)沒有足夠的內(nèi)存，會向 SQL Server 請求內(nèi)存，從而導(dǎo)致影響 SQL Server 性能。
max server memory 服務(wù)器配置選項可用于：在 SQL Server 啟動及運行時，指定 SQL Server 可以分配的最大內(nèi)存量。如果知道有多個應(yīng)用程序與 SQL Server 同時運行，而且想保障這些應(yīng)用程序有足夠的內(nèi)存運行，可以將該配置選項設(shè)置為特定的值。如果這些其它應(yīng)用程序（如 Web 服務(wù)器或電子郵件服務(wù)器）只根據(jù)需要請求內(nèi)存，則 SQL Server 將根據(jù)需要給它們釋放內(nèi)存，因此不要設(shè)置 max server memory 服務(wù)器配置選項。然而，應(yīng)用程序通常在啟動時不假選擇地使用可用內(nèi)存，而如果需要更多內(nèi)存也不請求。如果有這種行為方式的應(yīng)用程序與 SQL Server 同時運行在相同的計算機(jī)上，則將 max server memory 服務(wù)器配置選項設(shè)置為特定的值，以保障應(yīng)用程序所需的內(nèi)存不由 SQL Server 分配出。
不要將 min server memory 和 max server memory 服務(wù)器配置選項設(shè)置為相同的值，這樣做會使分配給 SQL Server 的內(nèi)存量固定。動態(tài)內(nèi)存分配可以隨時間提供最佳的總體性能。有關(guān)更多信息，請參見服務(wù)器內(nèi)存選項。
max worker threads 服務(wù)器配置選項可用于指定為用戶連接到 SQL Server 提供支持的線程數(shù)。255 這一默認(rèn)設(shè)置對一些配置可能稍微偏高，這要具體取決于并發(fā)用戶數(shù)。由于每個工作線程都已分配，因此即使線程沒有正在使用（因為并發(fā)連接比分配的工作線程少），可由其它操作（如高速緩沖存儲器）更好地利用的內(nèi)存資源也可能是未使用的。一般情況下，應(yīng)將該配置值設(shè)置為并發(fā)連接數(shù)，但不能超過 32727。并發(fā)連接與用戶登錄連接不同。SQL Server 實例的工作線程池只需要足夠大，以便為同時正在該實例中執(zhí)行批處理的用戶連接提供服務(wù)。如果增加工作線程的數(shù)量超過默認(rèn)值，會降低服務(wù)器性能。有關(guān)更多信息，請參見max worker threads 選項。
說明 當(dāng) SQL Server 運行在 Microsoft Windows® 98 上時，最大工作線程服務(wù)器配置選項不起作用。
index create memory 服務(wù)器配置選項控制創(chuàng)建索引時排序操作所使用的內(nèi)存量。在生產(chǎn)系統(tǒng)上創(chuàng)建索引通常是不常執(zhí)行的任務(wù)，通常調(diào)度為在非峰值時間執(zhí)行的作業(yè)。因此，不常創(chuàng)建索引且在非峰值時間時，增加該值可提高索引創(chuàng)建的性能。不過，最好將 min memory per query 配置選項保持在一個較低的值，這樣即使所有請求的內(nèi)存都不可用，索引創(chuàng)建作業(yè)仍能開始。有關(guān)更多信息，請參見 index create memory 選項。
min memory per query 服務(wù)器配置選項可用于指定分配給查詢執(zhí)行的最小內(nèi)存量。當(dāng)系統(tǒng)內(nèi)有許多查詢并發(fā)執(zhí)行時，增大 min memory per query 的值有助于提高消耗大量內(nèi)存的查詢（如大型排序和哈希操作）的性能。不過，不要將 min memory per query 服務(wù)器配置選項設(shè)置得太高，尤其是在很忙的系統(tǒng)上，因為查詢將不得不等到能確保占有請求的最小內(nèi)存、或等到超過 query wait 服務(wù)器配置選項內(nèi)所指定的值。如果可用內(nèi)存比執(zhí)行查詢所需的指定最小內(nèi)存多，則只要查詢能對多出的內(nèi)存加以有效的利用，就可以使用多出的內(nèi)存。有關(guān)更多信息，請參見 min memory per query 選項和 query wait 選項。
使用 I/O 配置選項優(yōu)化服務(wù)器性能
下列服務(wù)器配置選項可用于配置 I/O 的使用并影響服務(wù)器性能：
recovery interval
recovery interval 服務(wù)器配置選項控制 Microsoft® SQL Server™ 2000 在每個數(shù)據(jù)庫內(nèi)發(fā)出檢查點的時間。默認(rèn)情況下，SQL Server 確定執(zhí)行檢查點操作的最佳時間。然而，若要確定這是否為適當(dāng)?shù)脑O(shè)置，需要使用 Windows NT 性能監(jiān)視器監(jiān)視數(shù)據(jù)庫文件上的磁盤寫入活動。導(dǎo)致磁盤利用率達(dá)到 100% 的活動尖峰值會妨害性能。若更改該參數(shù)以使檢查點進(jìn)程較少出現(xiàn)，通常可以提高這種情況下的總體性能。但仍須繼續(xù)監(jiān)視性能以確定新值是否已對性能產(chǎn)生正面影響。有關(guān)更多信息，請參見recovery interval 選項。
5.優(yōu)化數(shù)據(jù)庫文件
分區(qū)
將數(shù)據(jù)庫分區(qū)可提高其性能并易于維護(hù)。通過將一個大表拆分成更小的單個表，只訪問一小部分?jǐn)?shù)據(jù)的查詢可以執(zhí)行得更快，因為需要掃描的數(shù)據(jù)較少。而且可以更快地執(zhí)行維護(hù)任務(wù)（如重建索引或備份表）。
實現(xiàn)分區(qū)操作時可以不拆分表，而將表物理地放置在個別的磁盤驅(qū)動器上。例如，將表放在某個物理驅(qū)動器上并將相關(guān)的表放在與之分離的驅(qū)動器上可提高查詢性能，因為當(dāng)執(zhí)行涉及表之間聯(lián)接的查詢時，多個磁頭同時讀取數(shù)據(jù)。可以使用 Microsoft® SQL Server™ 2000 文件組指定將表放置在哪些磁盤上。
硬件分區(qū)
硬件分區(qū)將數(shù)據(jù)庫設(shè)計為利用可用的硬件構(gòu)架。硬件分區(qū)的示例包括：
允許多線程執(zhí)行的多處理器，使得可以同時執(zhí)行許多查詢。換句話說，在多處理器上可以同時執(zhí)行查詢的各個組件，因此使單個查詢的速度更快。例如，查詢內(nèi)引用的每個表可同時由不同的線程掃描。
RAID（獨立磁盤冗余陣列）設(shè)備允許數(shù)據(jù)在多個磁盤驅(qū)動器中條帶化，使更多的讀/寫磁頭同時讀取數(shù)據(jù)，因此可以更快地訪問數(shù)據(jù)。在多個驅(qū)動器中條帶化的表一般比存儲在一個驅(qū)動器上的相同的表掃描速度要快。換句話說，將表與相關(guān)的表分開存儲在不同的驅(qū)動器上可以顯著提高聯(lián)接那些表的查詢的性能。
水平分區(qū)
水平分區(qū)將一個表分段為多個表，每個表包含相同數(shù)目的列和較少的行。例如，可以將一個包含十億行的表水平分區(qū)成 12 個表，每個小表代表特定年份內(nèi)一個月的數(shù)據(jù)。任何需要特定月份數(shù)據(jù)的查詢只引用相應(yīng)月份的表。
具體如何將表進(jìn)行水平分區(qū)取決于如何分析數(shù)據(jù)。將表進(jìn)行分區(qū)是為了使查詢引用盡可能少的表。否則，查詢時須使用過多的 UNION 查詢來邏輯合并表，而這會削弱查詢性能。有關(guān)查詢水平分區(qū)的表的更多信息，請參見視圖使用方案。
常用的方法是根據(jù)時期/使用對數(shù)據(jù)進(jìn)行水平分區(qū)。例如，一個表可能包含最近五年的數(shù)據(jù)，但是只定期訪問本年度的數(shù)據(jù)。在這種情況下，可考慮將數(shù)據(jù)分區(qū)成五個表，每個表只包含一年的數(shù)據(jù)。
垂直分區(qū)
垂直分區(qū)將一個表分段為多個表，每個表包含較少的列。垂直分區(qū)的兩種類型是規(guī)范化和行拆分。
規(guī)范化是個標(biāo)準(zhǔn)數(shù)據(jù)庫進(jìn)程，該進(jìn)程從表中刪除冗余列并將其放到次表中，次表按主鍵與外鍵的關(guān)系鏈接到主表。
行拆分將原始表垂直分成多個只包含較少列的表。拆分的表內(nèi)的每個邏輯行與其它表內(nèi)的相同邏輯行匹配。例如，聯(lián)接每個拆分的表內(nèi)的第十行將重新創(chuàng)建原始行。
與水平分區(qū)一樣，垂直分區(qū)使查詢得以掃描較少的數(shù)據(jù)，因此提高查詢性能。例如有一個包含七列的表，通常只引用該表的前四列，那么將該表的后三列拆分到一個單獨的表中可獲得性能收益。
應(yīng)謹(jǐn)慎考慮垂直分區(qū)操作，因為分析多個分區(qū)內(nèi)的數(shù)據(jù)需要有聯(lián)接表的查詢，而如果分區(qū)非常大將可能影響性能。
（一）深入淺出理解索引結(jié)構(gòu)
實際上，您可以把索引理解為一種特殊的目錄。微軟的SQL SERVER提供了兩種索引：聚集索引（clustered index，也稱聚類索引、簇集索引）和非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）。下面，我們舉例來說明一下聚集索引和非聚集索引的區(qū)別：
其實，我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查“安”字，就會很自然地翻開字典的前幾頁，因為“安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字母“a”開頭并以“z”結(jié)尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那么就說明您的字典中沒有這個字；同樣的，如果查“張”字，那您也會將您的字典翻到最后部分，因為“張”的拼音是“zhang”。也就是說，字典的正文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內(nèi)容。
我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為“聚集索引”。
如果您認(rèn)識某個字，您可以快速地從自動中查到這個字。但您也可能會遇到您不認(rèn)識的字，不知道它的發(fā)音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個字后的頁碼直接翻到某頁來找到您要找的字。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字并不是真正的分別位于“張”字的上下方，現(xiàn)在您看到的連續(xù)的“馳、張、弩”三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結(jié)果，然后再翻到您所需要的頁碼。
我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。
通過以上例子，我們可以理解到什么是“聚集索引”和“非聚集索引”。
進(jìn)一步引申一下，我們可以很容易的理解：每個表只能有一個聚集索引，因為目錄只能按照一種方法進(jìn)行排序。
（二）何時使用聚集索引或非聚集索引
下面的表總結(jié)了何時使用聚集索引或非聚集索引（很重要）。
動作描述
使用聚集索引
使用非聚集索引
列經(jīng)常被分組排序
應(yīng)
應(yīng)
返回某范圍內(nèi)的數(shù)據(jù)
應(yīng)
不應(yīng)
一個或極少不同值
不應(yīng)
不應(yīng)
小數(shù)目的不同值
應(yīng)
不應(yīng)
大數(shù)目的不同值
不應(yīng)
應(yīng)
頻繁更新的列
不應(yīng)
應(yīng)
外鍵列
應(yīng)
應(yīng)
主鍵列
應(yīng)
應(yīng)
頻繁修改索引列
不應(yīng)
應(yīng)
事實上，我們可以通過前面聚集索引和非聚集索引的定義的例子來理解上表。如：返回某范圍內(nèi)的數(shù)據(jù)一項。比如您的某個表有一個時間列，恰好您把聚合索引建立在了該列，這時您查詢2004年1月1日至2004年10月1日之間的全部數(shù)據(jù)時，這個速度就將是很快的，因為您的這本字典正文是按日期進(jìn)行排序的，聚類索引只需要找到要檢索的所有數(shù)據(jù)中的開頭和結(jié)尾數(shù)據(jù)即可；而不像非聚集索引，必須先查到目錄中查到每一項數(shù)據(jù)對應(yīng)的頁碼，然后再根據(jù)頁碼查到具體內(nèi)容。
（三）結(jié)合實際，談索引使用的誤區(qū)
理論的目的是應(yīng)用。雖然我們剛才列出了何時應(yīng)使用聚集索引或非聚集索引，但在實踐中以上規(guī)則卻很容易被忽視或不能根據(jù)實際情況進(jìn)行綜合分析。下面我們將根據(jù)在實踐中遇到的實際問題來談一下索引使用的誤區(qū)，以便于大家掌握索引建立的方法。
1、主鍵就是聚集索引
這種想法筆者認(rèn)為是極端錯誤的，是對聚集索引的一種浪費。雖然SQL SERVER默認(rèn)是在主鍵上建立聚集索引的。
通常，我們會在每個表中都建立一個ID列，以區(qū)分每條數(shù)據(jù)，并且這個ID列是自動增大的，步長一般為1。我們的這個辦公自動化的實例中的列Gid就是如此。此時，如果我們將這個列設(shè)為主鍵，SQL SERVER會將此列默認(rèn)為聚集索引。這樣做有好處，就是可以讓您的數(shù)據(jù)在數(shù)據(jù)庫中按照ID進(jìn)行物理排序，但筆者認(rèn)為這樣做意義不大。
顯而易見，聚集索引的優(yōu)勢是很明顯的，而每個表中只能有一個聚集索引的規(guī)則，這使得聚集索引變得更加珍貴。
從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據(jù)查詢要求，迅速縮小查詢范圍，避免全表掃描。在實際應(yīng)用中，因為ID號是自動生成的，我們并不知道每條記錄的ID號，所以我們很難在實踐中用ID號來進(jìn)行查詢。這就使讓ID號這個主鍵作為聚集索引成為一種資源浪費。其次，讓每個ID號都不同的字段作為聚集索引也不符合“大數(shù)目的不同值情況下不應(yīng)建立聚合索引”規(guī)則；當(dāng)然，這種情況只是針對用戶經(jīng)常修改記錄內(nèi)容，特別是索引項的時候會負(fù)作用，但對于查詢速度并沒有影響。
在辦公自動化系統(tǒng)中，無論是系統(tǒng)首頁顯示的需要用戶簽收的文件、會議還是用戶進(jìn)行文件查詢等任何情況下進(jìn)行數(shù)據(jù)查詢都離不開字段的是“日期”還有用戶本身的“用戶名”。
通常，辦公自動化的首頁會顯示每個用戶尚未簽收的文件或會議。雖然我們的where語句可以僅僅限制當(dāng)前用戶尚未簽收的情況，但如果您的系統(tǒng)已建立了很長時間，并且數(shù)據(jù)量很大，那么，每次每個用戶打開首頁的時候都進(jìn)行一次全表掃描，這樣做意義是不大的，絕大多數(shù)的用戶1個月前的文件都已經(jīng)瀏覽過了，這樣做只能徒增數(shù)據(jù)庫的開銷而已。事實上，我們完全可以讓用戶打開系統(tǒng)首頁時，數(shù)據(jù)庫僅僅查詢這個用戶近3個月來未閱覽的文件，通過“日期”這個字段來限制表掃描，提高查詢速度。如果您的辦公自動化系統(tǒng)已經(jīng)建立的2年，那么您的首頁顯示速度理論上將是原來速度8倍，甚至更快。
在這里之所以提到“理論上”三字，是因為如果您的聚集索引還是盲目地建在ID這個主鍵上時，您的查詢速度是沒有這么高的，即使您在“日期”這個字段上建立的索引（非聚合索引）。下面我們就來看一下在1000萬條數(shù)據(jù)量的情況下各種查詢的速度表現(xiàn)（3個月內(nèi)的數(shù)據(jù)為25萬條）：
（1）僅在主鍵上建立聚集索引，并且不劃分時間段：
Select gid,fariqi,neibuyonghu,title from tgongwen
用時：128470毫秒（即：128秒）
（2）在主鍵上建立聚集索引，在fariq上建立非聚集索引：
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用時：53763毫秒（54秒）
（3）將聚合索引建立在日期列（fariqi）上：
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用時：2423毫秒（2秒）
雖然每條語句提取出來的都是25萬條數(shù)據(jù)，各種情況的差異卻是巨大的，特別是將聚集索引建立在日期列時的差異。事實上，如果您的數(shù)據(jù)庫真的有1000萬容量的話，把主鍵建立在ID列上，就像以上的第1、2種情況，在網(wǎng)頁上的表現(xiàn)就是超時，根本就無法顯示。這也是我摒棄ID列作為聚集索引的一個最重要的因素。
得出以上速度的方法是：在各個select語句前加：declare @d datetime
set @d=getdate()
并在select語句后加：
select [語句執(zhí)行花費時間(毫秒)]=datediff(ms,@d,getdate())
2、只要建立索引就能顯著提高查詢速度
事實上，我們可以發(fā)現(xiàn)上面的例子中，第2、3條語句完全相同，且建立索引的字段也相同；不同的僅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查詢速度卻有著天壤之別。所以，并非是在任何字段上簡單地建立索引就能提高查詢速度。
從建表的語句中，我們可以看到這個有著1000萬數(shù)據(jù)的表中fariqi字段有5003個不同記錄。在此字段上建立聚合索引是再合適不過了。在現(xiàn)實中，我們每天都會發(fā)幾個文件，這幾個文件的發(fā)文日期就相同，這完全符合建立聚集索引要求的：“既不能絕大多數(shù)都相同，又不能只有極少數(shù)相同”的規(guī)則。由此看來，我們建立“適當(dāng)”的聚合索引對于我們提高查詢速度是非常重要的。
3、把所有需要提高查詢速度的字段都加進(jìn)聚集索引，以提高查詢速度
上面已經(jīng)談到：在進(jìn)行數(shù)據(jù)查詢時都離不開字段的是“日期”還有用戶本身的“用戶名”。既然這兩個字段都是如此的重要，我們可以把他們合并起來，建立一個復(fù)合索引（compound index）。
很多人認(rèn)為只要把任何字段加進(jìn)聚集索引，就能提高查詢速度，也有人感到迷惑：如果把復(fù)合的聚集索引字段分開查詢，那么查詢速度會減慢嗎？帶著這個問題，我們來看一下以下的查詢速度（結(jié)果集都是25萬條數(shù)據(jù)）：（日期列fariqi首先排在復(fù)合聚集索引的起始列，用戶名neibuyonghu排在后列）
（1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'
查詢速度：2513毫秒
（2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='辦公室'
查詢速度：2516毫秒
（3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='辦公室'
查詢速度：60280毫秒
從以上試驗中，我們可以看到如果僅用聚集索引的起始列作為查詢條件和同時用到復(fù)合聚集索引的全部列的查詢速度是幾乎一樣的，甚至比用上全部的復(fù)合索引列還要略快（在查詢結(jié)果集數(shù)目一樣的情況下）；而如果僅用復(fù)合聚集索引的非起始列作為查詢條件的話，這個索引是不起任何作用的。當(dāng)然，語句1、2的查詢速度一樣是因為查詢的條目數(shù)一樣，如果復(fù)合索引的所有列都用上，而且查詢結(jié)果少的話，這樣就會形成“索引覆蓋”，因而性能可以達(dá)到最優(yōu)。同時，請記住：無論您是否經(jīng)常使用聚合索引的其他列，但其前導(dǎo)列一定要是使用最頻繁的列。
評論Feed: http://77521.cn/feed.asp?q=comment&id=173
引用鏈接: http://77521.cn/trackback.asp?id=173