李素奇 射陽(yáng)縣中醫(yī)院

關(guān)于SQL視圖優(yōu)點(diǎn)和缺點(diǎn)的淺探

李素奇 射陽(yáng)縣中醫(yī)院

在SQL中查詢數(shù)據(jù)時(shí)，一般從設(shè)計(jì)SELECT語(yǔ)句開(kāi)始，將需要查詢的字段寫(xiě)下來(lái)，而每次你要來(lái)查詢數(shù)據(jù)時(shí)（同樣的條件下），那么就需要重復(fù)輸入一樣的查詢語(yǔ)句，效率低下。但將這個(gè)經(jīng)常要重復(fù)使用的查詢語(yǔ)句創(chuàng)建成視圖，就簡(jiǎn)單了！我們?cè)诔绦蛟O(shè)計(jì)的時(shí)，要先了解視圖的優(yōu)點(diǎn)和缺點(diǎn)，這樣就可以揚(yáng)長(zhǎng)避短，更合理的來(lái)使用它。

視圖 優(yōu)點(diǎn) 缺點(diǎn)

在SQL Server數(shù)據(jù)庫(kù)中，表定義了數(shù)據(jù)的編排方式和結(jié)構(gòu)。在SQL Server數(shù)據(jù)庫(kù)中查看存儲(chǔ)的數(shù)據(jù)的方法也不是唯一的，可通過(guò)定義SQL Server數(shù)據(jù)庫(kù)的數(shù)據(jù)視圖來(lái)展現(xiàn)。使用SELECT…FROM語(yǔ)句從一個(gè)或多個(gè)表中導(dǎo)出數(shù)據(jù)形成視圖，這些表我們稱之為基表。當(dāng)然 我們也可以從一個(gè)、多個(gè)視圖中生成新的視圖。視圖又可稱為虛表，是因?yàn)樵跀?shù)據(jù)庫(kù)中存有導(dǎo)出視圖的查詢語(yǔ)句，但是查詢出的相關(guān)視圖定義的數(shù)據(jù)在數(shù)據(jù)庫(kù)中并未增加，在生成的視圖中看到的數(shù)據(jù)是基于實(shí)際的表中數(shù)據(jù)，兩者在操作上并沒(méi)有區(qū)別，但兩者之間的差異在本質(zhì)是不同的。視圖并沒(méi)有保存任何記錄，它存儲(chǔ)的是查詢語(yǔ)句，其查詢的記錄實(shí)際來(lái)自于數(shù)據(jù)表，也可以為多張數(shù)據(jù)表，所以說(shuō)視圖應(yīng)用的寬度很大！我們可以根據(jù)各種查詢要求來(lái)創(chuàng)建不同視圖，且不會(huì)增加SQL Server數(shù)據(jù)庫(kù)中現(xiàn)有的數(shù)據(jù)量。

1 視圖的優(yōu)點(diǎn)

1．1 視圖的簡(jiǎn)易性：為了將重復(fù)、復(fù)雜的操作簡(jiǎn)化，我們可以把復(fù)雜的查詢直接定義為視圖。這樣就使用戶所需要的SQL Server數(shù)據(jù)庫(kù)結(jié)構(gòu)視圖更具“個(gè)性化”。

1．2 視圖的安全性：為了保護(hù)表的安全性，我們往往根據(jù)使用的類(lèi)別、范圍來(lái)劃分?jǐn)?shù)據(jù)庫(kù)表的操作權(quán)限，當(dāng)然有時(shí)這也存在一定的局限性，這時(shí)就可以在視圖上通過(guò)REVOKE和GRANT命令給用戶授予操作權(quán)限。通過(guò)這樣的視圖，他們只能修改或查詢視圖顯示的數(shù)據(jù)，而不能修改SQL Server數(shù)據(jù)庫(kù)表中不可見(jiàn)的數(shù)據(jù)。

1．3 數(shù)據(jù)的隔離變化：有時(shí)數(shù)據(jù)庫(kù)的表重新組合或發(fā)生變化更改，我們只需要重新定義視圖，還是可以在該視圖上取得一致、沒(méi)有變化的數(shù)據(jù)。

1．4 視圖維護(hù)的簡(jiǎn)易型：有時(shí)可以根據(jù)where條件中過(guò)濾，用過(guò)濾后查詢出來(lái)的數(shù)據(jù)集組成視圖。我們會(huì)發(fā)現(xiàn)調(diào)試查詢比調(diào)試視圖要更復(fù)雜。在跟蹤視圖過(guò)程中時(shí)，更加容易分析各個(gè)步驟中的錯(cuò)誤，這是因?yàn)橐晥D的組成部分包含了所有的執(zhí)行步驟。

2 視圖的缺點(diǎn)

2．1 視圖的性能：SQL Server對(duì)視圖的查詢是直接被轉(zhuǎn)化為對(duì)表的查詢，所以當(dāng)視圖由一個(gè)或者復(fù)雜的多表查詢生成時(shí)，即使該視圖只包含一個(gè)很簡(jiǎn)單的查詢，SQL Server也需要比直接對(duì)基本表查詢花費(fèi)更多的時(shí)間。

2．2 視圖的修改限制：當(dāng)視圖只涉及到簡(jiǎn)單的基本表時(shí)，我們修改視圖中行或列中的數(shù)據(jù)，很方便，但是如果該視圖由復(fù)雜的多表查詢生成時(shí)，可能數(shù)據(jù)并不能被修改。

3 創(chuàng)建視圖的分類(lèi)

3．1 水平視圖：創(chuàng)建視圖時(shí)，限制了只能夠存取表中的規(guī)定的一些數(shù)據(jù)行，即SQL Server數(shù)據(jù)庫(kù)中表中行的子集。

3．2 投影視圖：創(chuàng)建視圖時(shí)，限制了只能夠存取表中的規(guī)定的一些數(shù)據(jù)列，即SQL Server數(shù)據(jù)庫(kù)中表中列的子集。如在醫(yī)院信息管理系統(tǒng)（HIS系統(tǒng)）數(shù)據(jù)庫(kù)中我們只關(guān)心掛號(hào)表（allpat表）中病人的姓名、身份證號(hào)及金額。所以可以創(chuàng)建如下視圖：

Create view allpat_v

As

Select pat_name,sfz_no,je from allpat

在投影視圖中，SQL Server數(shù)據(jù)庫(kù)管理員可以按用戶的需求生成專用視圖，這個(gè)專用視圖只由所需要的列組成。

3．3 聯(lián)合視圖：當(dāng)我們使用視圖時(shí)的一個(gè)原因就是多表查詢的簡(jiǎn)化,在多個(gè)表中提取數(shù)據(jù)生成聯(lián)合視圖，查詢出的結(jié)果被當(dāng)做一個(gè)單獨(dú)的“可見(jiàn)表”。這種視圖一旦生成，就可以查詢這些視圖來(lái)替代多表查詢的請(qǐng)求，提高了查詢的效率。當(dāng)然在定義視圖時(shí)，外連接不能用。

3．4 視圖的視圖：即在視圖的基礎(chǔ)上創(chuàng)建視圖，不同于基于表創(chuàng)建的視圖。

4 視圖應(yīng)用的實(shí)例

有三張數(shù)據(jù)表：allpat、mzappitem和mzdj，其中allpat的記錄為1萬(wàn)條左右，mzdj中的數(shù)據(jù)為10萬(wàn)條mzappitem 100萬(wàn)條以上，現(xiàn)在以 allpat．pre_no=mzdj．pre_no and mzdj．dj=mzappitem．dj為關(guān)聯(lián)進(jìn)行數(shù)據(jù)篩選。這時(shí)做了個(gè)SQL SERVER視圖info，運(yùn)行該視圖的查詢，花時(shí)4秒得到了結(jié)果。

現(xiàn)根據(jù)特殊需求來(lái)編寫(xiě)有一個(gè)游標(biāo)的循環(huán)的存儲(chǔ)過(guò)程，該游標(biāo)反復(fù)300次循環(huán)，且在每次循環(huán)時(shí)都對(duì)info使用了查詢。

循環(huán)游標(biāo)體(重復(fù)300次）

select @pre_no=pre_no from info where dj_id=@id --其中@pre_no @id均為變量

update mzdj1 set pre_no =@ pre_no where dj_id=@id

在執(zhí)行上述存儲(chǔ)過(guò)程時(shí)，需要花費(fèi)大于2分鐘的時(shí)間，進(jìn)行分析后，運(yùn)行效率的低下在于對(duì)info視圖的掃描上，盡管單獨(dú)對(duì)該視圖進(jìn)行查詢耗時(shí)1秒，但重復(fù)讀取情況耗費(fèi)了太多的時(shí)間。

如何既能有效的使用視圖，又能高效的運(yùn)行呢？故此按如下修改存儲(chǔ)過(guò)程：

具體思路是：創(chuàng)建一個(gè)臨時(shí)表，把所需的數(shù)據(jù)先插入進(jìn)去，再運(yùn)行存儲(chǔ)過(guò)程。

create table #temp ( id nvarchar(20), pre_no nvarchar(20))

查詢info視圖，將記錄插入到#temp臨時(shí)表中．． 循環(huán)游標(biāo)體(重復(fù)500次）

select @pre_no=pre_no from #temp where dj_id=@id --其中@pre_no,@id均為變量

update mzdj1 set pre_no =@ pre_no where dj_id=@id}

運(yùn)行后該存儲(chǔ)過(guò)程只花費(fèi)10秒的時(shí)間。

綜上所述我們知道每種視圖都有各自的使用規(guī)則及范圍，使用不恰當(dāng)就會(huì)拖累性能，而使用得恰當(dāng)則會(huì)使性能提升，所以在定義視圖時(shí)，需謹(jǐn)慎權(quán)衡視圖的優(yōu)點(diǎn)和缺點(diǎn)。

總之，對(duì)視圖的善加運(yùn)用可以讓我們?cè)趯?duì)數(shù)據(jù)庫(kù)的設(shè)計(jì)、管理及使用都更加有效率、更加方便。

[1] 微軟公司，SQL Server數(shù)據(jù)庫(kù)程序設(shè)計(jì)，高等教育出版社，2005.

[2] 楊正洪鄭齊健鄭齊心鄭齊燕等，SQL Server關(guān)系數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)指南，機(jī)械工業(yè)出版社，2000

[3] sql之淺談視圖的作用，紅黑聯(lián)盟，2014.

[4]sql server 函數(shù)與視圖的使用，wgw5363240的專欄，2016.