一般來(lái)說(shuō),對(duì)于做B/S架構(gòu)的朋友來(lái)說(shuō),更有機(jī)會(huì)遇到高并發(fā)的數(shù)據(jù)庫(kù)訪問(wèn)情況,因?yàn)楝F(xiàn)在WEB的普及速度就像火箭升空,同時(shí)就會(huì)因?yàn)楦咴L問(wèn)量帶來(lái)一系列性能問(wèn)題,而數(shù)據(jù)庫(kù)一直是用戶與商人之間交流的重要平臺(tái).用戶是沒(méi)有耐心忍受一個(gè)查詢需要用上10秒以上的，或者更少些，如果經(jīng)常出現(xiàn)服務(wù)器死機(jī)或者是報(bào)查詢超時(shí)，想那將是失敗的項(xiàng)目。做了幾年的WEB工作,不才,一直沒(méi)有遇到過(guò)大訪問(wèn)量或者是海量數(shù)據(jù)的情況.這里并不是說(shuō)沒(méi)有海量數(shù)據(jù)的項(xiàng)目就不是好項(xiàng)目,要看項(xiàng)目的應(yīng)用場(chǎng)合.
最近做項(xiàng)目時(shí),偶然得到了這個(gè)機(jī)會(huì),在工作過(guò)程中,發(fā)現(xiàn)的單表記錄數(shù)高達(dá)9位數(shù).像訂單表什么的也有8位數(shù).在查詢訂單的時(shí)候往往不能通過(guò)單表查詢就能解決,還要和其它相關(guān)表進(jìn)行關(guān)聯(lián)查詢.如此關(guān)聯(lián)的表數(shù)據(jù)不大還好,一旦發(fā)生大表關(guān)聯(lián)大表,在查詢時(shí)就有可能出現(xiàn)慢長(zhǎng)的等待。
主旨: 如何避免這種情況的發(fā)生呢?既然有了這樣的數(shù)據(jù),需求還是要實(shí)現(xiàn),這里就我最近針對(duì)數(shù)據(jù)庫(kù)的優(yōu)化過(guò)程,我分兩篇文章來(lái)說(shuō)明下.
第一篇:如何盡量避免大表關(guān)聯(lián).
第二篇:對(duì)大表進(jìn)行分區(qū).
背景:有兩張表:
1:訂單表：記錄用戶訂單的詳細(xì)信息.order,其中有一個(gè)會(huì)員卡號(hào)字段cardNo,訂單產(chǎn)生時(shí)間.
2:會(huì)員表：記錄會(huì)員相關(guān)信息.member,一個(gè)會(huì)員有一個(gè)代理號(hào):proxyID,代理下面有許多的會(huì)員卡:cardNo,它們共用一個(gè)代理號(hào).
兩表通過(guò)cardNo來(lái)相關(guān)聯(lián).
需求:查詢一個(gè)用戶或者某些用戶某一時(shí)間段所有會(huì)員卡產(chǎn)生的訂單情況.
實(shí)現(xiàn)SQL:
select 字段 from order
inner join member on
order.cardNo=member.cardNo
and member.proxyID in(’a-01’,代理號(hào)二)
and 時(shí)間 between ’20080101’ and ’20080131’
考試大見(jiàn)解:我想一般的朋友看到這樣的需求大多會(huì)寫(xiě)出這樣的查詢SQL,如果不喜歡用in或者認(rèn)為in的性能不好的朋友可用union all 代替.SQL語(yǔ)句可以說(shuō)簡(jiǎn)單的不能再簡(jiǎn)單了,本身并無(wú)問(wèn)題,只是如果兩表的數(shù)據(jù)都在百萬(wàn)以上,而且字段都特別多.此時(shí)如果只有索引的幫忙下并不一定能達(dá)到預(yù)期的效果.
解決方案一:利用表變量來(lái)替換大表關(guān)聯(lián),表變量的作用域?yàn)橐粋€(gè)批處理,批處理完了,表變量也會(huì)隨之失效,比起臨時(shí)表有它獨(dú)特的優(yōu)點(diǎn)：不用手動(dòng)去刪除表變量以釋放內(nèi)存。
可行性:因?yàn)樾枨笾械妮敵鲎侄未蠖鄟?lái)自訂單表,member表只起到數(shù)據(jù)約束的作用,和查詢用戶會(huì)員卡號(hào)的作用,所有可以先把代理的會(huì)員卡號(hào)先取到表變量中,然后利用帶有卡號(hào)的表變量和訂單表相關(guān)聯(lián)查詢.
declare @t table
(cardNo int)
insert @t
select cardNo from member where in(’a-01’,代理號(hào)二)
select 字段 from order
inner join @t on
order.cardNo=@t.cardNo and 時(shí)間 between ’20080101’ and ’20080131’
這里我就不貼性能比較圖了,有興趣的朋友可以自己嘗試下.這種方法在查詢?nèi)藛T比較多的時(shí)候特別有幫助.它要開(kāi)發(fā)員根據(jù)實(shí)際情況詳細(xì)比較,結(jié)果并不是統(tǒng)一的,不同的環(huán)境結(jié)果可能不一樣.希望大家理解.
解決方案二:利用索引視圖來(lái)提高大表關(guān)聯(lián)的性能.
可行性:一般在大表關(guān)聯(lián)時(shí),我們的輸出列都遠(yuǎn)小于兩表的字段合,像上面的member表只用到了其中的兩個(gè)字段(cardNo,proxyID).設(shè)想一下,此時(shí)的member表如果只有這兩個(gè)字段情況會(huì)不會(huì)好些呢?答案不言而喻.
視圖這個(gè)名詞在我以前對(duì)它的印象中,從來(lái)沒(méi)有認(rèn)為視圖能優(yōu)化查詢,因?yàn)槲艺J(rèn)為視圖對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)就是一個(gè)虛假表,在數(shù)據(jù)庫(kù)中并無(wú)實(shí)際物理位置來(lái)存儲(chǔ)數(shù)據(jù).對(duì)于用戶來(lái)說(shuō)無(wú)非就是通過(guò)不同的視角來(lái)觀看結(jié)果.視圖數(shù)據(jù)
的產(chǎn)生都是實(shí)時(shí)的,即當(dāng)調(diào)用視圖時(shí),自動(dòng)擴(kuò)展視圖,去運(yùn)行里面相應(yīng)的select語(yǔ)句.后來(lái)才知道在2000后的版本中視圖分一般視圖和索引視圖,一般視圖就是沒(méi)有創(chuàng)建索引的我印象中的視圖.而創(chuàng)建了視圖后就稱(chēng)為索引視圖.索引視圖是物理存在的,可在視圖上首先創(chuàng)建一個(gè)的聚集索引,其它字段上也可創(chuàng)建非聚集索引.在不改變基礎(chǔ)表的情況下,起到了優(yōu)化的效果.
CREATE VIEW memberView
WITH SCHEMABINDING
AS
SELECT cardNo,proxyID from member
GO
--以會(huì)員卡號(hào)創(chuàng)建一個(gè)聚集索引
CREATE UNIQUE CLUSTERED INDEX ix_member_cardNo
ON member (cardNo);
GO
注意:創(chuàng)建索引視圖要點(diǎn):
1: CREATE VIEW memberView后面要跟上WITH SCHEMABINDING
理由:? 使用 schemaname.objectname 明確識(shí)別視圖所引用的所有對(duì)象,而不管是哪個(gè)用戶訪問(wèn)該視圖.
? 不會(huì)以導(dǎo)致視圖定義非法或強(qiáng)制 SQL Server 在該視圖上重新創(chuàng)建索引的方式,更改視圖定義中所引用的對(duì)象.
2:視圖上的第一個(gè)索引必須為 CLUSTERED 和 UNIQUE.
理由:必須為 UNIQUE 以便在維護(hù)索引視圖期間,輕松地按鍵值查找視圖中的記錄,并阻止創(chuàng)建帶有重復(fù)項(xiàng)目的視圖(要求維護(hù)特殊的邏輯).必須為 CLUSTERED,因?yàn)橹挥芯奂饕拍茉趶?qiáng)制性的同時(shí)存儲(chǔ)行.
3:以下情況可考慮創(chuàng)建索引視圖:
? 可預(yù)先計(jì)算聚合并將其保存在索引中,從而在查詢執(zhí)行時(shí),最小化高成本的計(jì)算.
? 可預(yù)先聯(lián)接各個(gè)表并保存最終獲得的數(shù)據(jù)集.
? 可保存聯(lián)接或聚合的組合.
4:基礎(chǔ)表的更新會(huì)引發(fā)索引視力的更新.
5:索引視圖的創(chuàng)建同時(shí)會(huì)帶來(lái)維護(hù)上的開(kāi)銷(xiāo).
理由:1:因?yàn)樗饕晥D是物理存在的.
2:要額外的維護(hù)索引.
實(shí)現(xiàn):SQL:select 字段 from order
inner join memberView on
order.cardNo=member.cardNo
and member.proxyID=in(’a-01’,代理號(hào)二)
and 時(shí)間 between ’20080101’ and ’20080131’
總結(jié):兩種解決方案來(lái)看,各有所長(zhǎng),一般可以優(yōu)先考慮使用索引視圖來(lái)優(yōu)化大表關(guān)聯(lián).以上是本人對(duì)于如何盡量避免發(fā)生大表關(guān)聯(lián)所采取的措施,望大家指教
最近做項(xiàng)目時(shí),偶然得到了這個(gè)機(jī)會(huì),在工作過(guò)程中,發(fā)現(xiàn)的單表記錄數(shù)高達(dá)9位數(shù).像訂單表什么的也有8位數(shù).在查詢訂單的時(shí)候往往不能通過(guò)單表查詢就能解決,還要和其它相關(guān)表進(jìn)行關(guān)聯(lián)查詢.如此關(guān)聯(lián)的表數(shù)據(jù)不大還好,一旦發(fā)生大表關(guān)聯(lián)大表,在查詢時(shí)就有可能出現(xiàn)慢長(zhǎng)的等待。
主旨: 如何避免這種情況的發(fā)生呢?既然有了這樣的數(shù)據(jù),需求還是要實(shí)現(xiàn),這里就我最近針對(duì)數(shù)據(jù)庫(kù)的優(yōu)化過(guò)程,我分兩篇文章來(lái)說(shuō)明下.
第一篇:如何盡量避免大表關(guān)聯(lián).
第二篇:對(duì)大表進(jìn)行分區(qū).
背景:有兩張表:
1:訂單表：記錄用戶訂單的詳細(xì)信息.order,其中有一個(gè)會(huì)員卡號(hào)字段cardNo,訂單產(chǎn)生時(shí)間.
2:會(huì)員表：記錄會(huì)員相關(guān)信息.member,一個(gè)會(huì)員有一個(gè)代理號(hào):proxyID,代理下面有許多的會(huì)員卡:cardNo,它們共用一個(gè)代理號(hào).
兩表通過(guò)cardNo來(lái)相關(guān)聯(lián).
需求:查詢一個(gè)用戶或者某些用戶某一時(shí)間段所有會(huì)員卡產(chǎn)生的訂單情況.
實(shí)現(xiàn)SQL:
select 字段 from order
inner join member on
order.cardNo=member.cardNo
and member.proxyID in(’a-01’,代理號(hào)二)
and 時(shí)間 between ’20080101’ and ’20080131’
考試大見(jiàn)解:我想一般的朋友看到這樣的需求大多會(huì)寫(xiě)出這樣的查詢SQL,如果不喜歡用in或者認(rèn)為in的性能不好的朋友可用union all 代替.SQL語(yǔ)句可以說(shuō)簡(jiǎn)單的不能再簡(jiǎn)單了,本身并無(wú)問(wèn)題,只是如果兩表的數(shù)據(jù)都在百萬(wàn)以上,而且字段都特別多.此時(shí)如果只有索引的幫忙下并不一定能達(dá)到預(yù)期的效果.
解決方案一:利用表變量來(lái)替換大表關(guān)聯(lián),表變量的作用域?yàn)橐粋€(gè)批處理,批處理完了,表變量也會(huì)隨之失效,比起臨時(shí)表有它獨(dú)特的優(yōu)點(diǎn)：不用手動(dòng)去刪除表變量以釋放內(nèi)存。
可行性:因?yàn)樾枨笾械妮敵鲎侄未蠖鄟?lái)自訂單表,member表只起到數(shù)據(jù)約束的作用,和查詢用戶會(huì)員卡號(hào)的作用,所有可以先把代理的會(huì)員卡號(hào)先取到表變量中,然后利用帶有卡號(hào)的表變量和訂單表相關(guān)聯(lián)查詢.
declare @t table
(cardNo int)
insert @t
select cardNo from member where in(’a-01’,代理號(hào)二)
select 字段 from order
inner join @t on
order.cardNo=@t.cardNo and 時(shí)間 between ’20080101’ and ’20080131’
這里我就不貼性能比較圖了,有興趣的朋友可以自己嘗試下.這種方法在查詢?nèi)藛T比較多的時(shí)候特別有幫助.它要開(kāi)發(fā)員根據(jù)實(shí)際情況詳細(xì)比較,結(jié)果并不是統(tǒng)一的,不同的環(huán)境結(jié)果可能不一樣.希望大家理解.
解決方案二:利用索引視圖來(lái)提高大表關(guān)聯(lián)的性能.
可行性:一般在大表關(guān)聯(lián)時(shí),我們的輸出列都遠(yuǎn)小于兩表的字段合,像上面的member表只用到了其中的兩個(gè)字段(cardNo,proxyID).設(shè)想一下,此時(shí)的member表如果只有這兩個(gè)字段情況會(huì)不會(huì)好些呢?答案不言而喻.
視圖這個(gè)名詞在我以前對(duì)它的印象中,從來(lái)沒(méi)有認(rèn)為視圖能優(yōu)化查詢,因?yàn)槲艺J(rèn)為視圖對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)就是一個(gè)虛假表,在數(shù)據(jù)庫(kù)中并無(wú)實(shí)際物理位置來(lái)存儲(chǔ)數(shù)據(jù).對(duì)于用戶來(lái)說(shuō)無(wú)非就是通過(guò)不同的視角來(lái)觀看結(jié)果.視圖數(shù)據(jù)
的產(chǎn)生都是實(shí)時(shí)的,即當(dāng)調(diào)用視圖時(shí),自動(dòng)擴(kuò)展視圖,去運(yùn)行里面相應(yīng)的select語(yǔ)句.后來(lái)才知道在2000后的版本中視圖分一般視圖和索引視圖,一般視圖就是沒(méi)有創(chuàng)建索引的我印象中的視圖.而創(chuàng)建了視圖后就稱(chēng)為索引視圖.索引視圖是物理存在的,可在視圖上首先創(chuàng)建一個(gè)的聚集索引,其它字段上也可創(chuàng)建非聚集索引.在不改變基礎(chǔ)表的情況下,起到了優(yōu)化的效果.
CREATE VIEW memberView
WITH SCHEMABINDING
AS
SELECT cardNo,proxyID from member
GO
--以會(huì)員卡號(hào)創(chuàng)建一個(gè)聚集索引
CREATE UNIQUE CLUSTERED INDEX ix_member_cardNo
ON member (cardNo);
GO
注意:創(chuàng)建索引視圖要點(diǎn):
1: CREATE VIEW memberView后面要跟上WITH SCHEMABINDING
理由:? 使用 schemaname.objectname 明確識(shí)別視圖所引用的所有對(duì)象,而不管是哪個(gè)用戶訪問(wèn)該視圖.
? 不會(huì)以導(dǎo)致視圖定義非法或強(qiáng)制 SQL Server 在該視圖上重新創(chuàng)建索引的方式,更改視圖定義中所引用的對(duì)象.
2:視圖上的第一個(gè)索引必須為 CLUSTERED 和 UNIQUE.
理由:必須為 UNIQUE 以便在維護(hù)索引視圖期間,輕松地按鍵值查找視圖中的記錄,并阻止創(chuàng)建帶有重復(fù)項(xiàng)目的視圖(要求維護(hù)特殊的邏輯).必須為 CLUSTERED,因?yàn)橹挥芯奂饕拍茉趶?qiáng)制性的同時(shí)存儲(chǔ)行.
3:以下情況可考慮創(chuàng)建索引視圖:
? 可預(yù)先計(jì)算聚合并將其保存在索引中,從而在查詢執(zhí)行時(shí),最小化高成本的計(jì)算.
? 可預(yù)先聯(lián)接各個(gè)表并保存最終獲得的數(shù)據(jù)集.
? 可保存聯(lián)接或聚合的組合.
4:基礎(chǔ)表的更新會(huì)引發(fā)索引視力的更新.
5:索引視圖的創(chuàng)建同時(shí)會(huì)帶來(lái)維護(hù)上的開(kāi)銷(xiāo).
理由:1:因?yàn)樗饕晥D是物理存在的.
2:要額外的維護(hù)索引.
實(shí)現(xiàn):SQL:select 字段 from order
inner join memberView on
order.cardNo=member.cardNo
and member.proxyID=in(’a-01’,代理號(hào)二)
and 時(shí)間 between ’20080101’ and ’20080131’
總結(jié):兩種解決方案來(lái)看,各有所長(zhǎng),一般可以優(yōu)先考慮使用索引視圖來(lái)優(yōu)化大表關(guān)聯(lián).以上是本人對(duì)于如何盡量避免發(fā)生大表關(guān)聯(lián)所采取的措施,望大家指教