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