建立物理模型精品課件

1、建立物理模型第1頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Contents在SQL Server中建立物理模型(Creating the Physical Model with SQL Server) 索引方面的考慮(Indexing Considerations)建立抽象層(Creating an Abstraction Layer in SQL Server)第2頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Creating the Physical Model with SQL Server是時(shí)候生成數(shù)據(jù)庫(kù)了,本節(jié)先介紹SQL Server對(duì)象的命名規(guī)則(建議),

2、再討論物理模型的建立.命名原則(Naming Guidelines) 生成物理模型(Deriving the Physical Model)實(shí)現(xiàn)商業(yè)規(guī)則(Implementing Business Rules in the Physical Model)第3頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming Guidelines在創(chuàng)建物理模型時(shí),使用命名原則極度重要,可能存在數(shù)百種命名標(biāo)準(zhǔn),使用哪一種無(wú)所謂,關(guān)鍵是要有.標(biāo)準(zhǔn)可以指示對(duì)象的類(lèi)型.如果table均以tbl打頭,而view用vw打頭,那么用戶一看名稱(chēng)就知道操縱的是表還是視圖.這可以節(jié)約很多時(shí)間,特別是在查找表現(xiàn)不好

3、的T-SQL代碼時(shí).評(píng)價(jià)標(biāo)準(zhǔn): (1)是否易用易記; (2)別人能不能理解; (3)標(biāo)準(zhǔn)是否保持一致. 即不能老是變來(lái)變?nèi)?第4頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三General Naming Guidelines本課程后邊使用的標(biāo)準(zhǔn).不在對(duì)象名中用空格(Never Use Spaces in Object Names)不在對(duì)象名中用連字符(-)(Never Use Hyphens in Object Names)不得用SQL Server關(guān)鍵字來(lái)命名對(duì)象(Do Not Name Objects Using SQL Server Keywords)命名時(shí)盡量短一點(diǎn)(Keep

4、 the Names Short)合理使用大小寫(xiě)(Using Case in Your Names)SELECT where, and, name, dateFROM INSERTWHERE and = 1AND where = Omaha會(huì)報(bào)錯(cuò),但若改為:SELECT where, and, name, dateFROM INSERTWHERE and = 1AND where = Omaha這是正確的,但顯然可讀性很差第5頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming Tables命名方法:”tbl_”后跟一個(gè)有意義的名字.Mountain View Music數(shù)據(jù)庫(kù)

5、中的部分表:tbl_ordertbl_customertbl_producttbl_employee另外,對(duì)表達(dá)多對(duì)多聯(lián)系的表,表示為:tbl_表1_表2,如:tbl_customer_address第6頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming Columns列名不要前綴,它是前綴規(guī)則中唯一的例外.第7頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming Views用”vw_”作為一個(gè)描述性名字的前綴.若視圖數(shù)據(jù)來(lái)自于多個(gè)表,則把各表名用”_”分開(kāi),例如:vw_customer_product第8頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期

6、三Naming Stored Procedures存儲(chǔ)過(guò)程用前綴”prc_”,也可用其它前綴,但是最好別用”sp_”,因?yàn)樗荕icrosoft在SQL Server中用來(lái)為系統(tǒng)存儲(chǔ)過(guò)程取名用的,免得造成混淆.第9頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming User-Defined Functions用戶定義函數(shù)用前綴”udf_”或其它. 不要用”fn_”,因?yàn)橄到y(tǒng)用了.第10頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming Triggers用前綴”trg_”跟一描述性文字第11頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming

7、Indexes以“idx_”為前綴第12頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming User-Defined Data Types直接取一個(gè)描述性的名字，不用前綴第13頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming Primary Keys and Foreign Keys主碼用前綴”P(pán)K_”后跟表名；外碼前綴“FK_”+參照表名+”_”+被參照表名第14頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Naming Constraints缺省值約束用前綴”DF_”,后跟表名和列名check約束用前綴”CK_”唯一性約束用前綴“UNQ_”第

8、15頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Creating the Physical Model with SQL Server是時(shí)候生成數(shù)據(jù)庫(kù)了,本節(jié)先介紹SQL Server對(duì)象的命名規(guī)則(建議),再討論物理模型的建立.命名原則(Naming Guidelines)生成物理模型(Deriving the Physical Model) 實(shí)現(xiàn)商業(yè)規(guī)則(Implementing Business Rules in the Physical Model)第16頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Deriving the Physical Model需要注意，

9、表和實(shí)體未必是一對(duì)一的關(guān)系，因?yàn)閷?shí)體是對(duì)現(xiàn)實(shí)世界建模，而表要附合關(guān)系數(shù)據(jù)庫(kù)的理論建立物理模型的過(guò)程是，先為每個(gè)實(shí)體建一張表，然后再分裂或者組合表Using Entities to Model Tables Using Relationships to Model Keys Using Attributes to Model Columns第17頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Using Entities to Model Tables(1)可以依賴軟件工具來(lái)把邏輯模式轉(zhuǎn)換為物理模型。也可以手工直接到SQL Server中去做。這里用的方法是先把所有實(shí)體分成若干個(gè)子模型(

10、submodel)，這樣就不用一次面對(duì)所有的實(shí)體。為Mountain View Music建立的子模型有：Products 包含所有關(guān)于產(chǎn)品和供應(yīng)商的細(xì)節(jié)Inventory 包含公司物理庫(kù)存的細(xì)節(jié)Orders 包含與訂購(gòu)、支付和客戶相關(guān)的實(shí)體Web Session 包含實(shí)現(xiàn)web購(gòu)物車(chē)相關(guān)的實(shí)體Lists 包含實(shí)現(xiàn)查找表的兩個(gè)實(shí)體MVM的完整模型與子模型舉例第18頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Using Entities to Model Tables(2)在建立物理模型時(shí),一次處理一個(gè)邏輯子模型.注意有的實(shí)體出現(xiàn)在多個(gè)子模型中,應(yīng)把它放入真正所屬的子模型中處理.下邊

11、,首先處理子模型中的實(shí)體并找出其中物理模型中的位置,稍后再處理聯(lián)系.第19頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Products Submodel有六個(gè)實(shí)體需要處理本例屬于比較簡(jiǎn)單的情況,每個(gè)實(shí)體對(duì)應(yīng)到一張表,初建的物理模型如下圖(一開(kāi)始只有表名和主碼)第20頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Products Submodel initial physical model第21頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Inventory Submodel與Products子模型比較相似,也沒(méi)有多少工作可做第22頁(yè)，共86頁(yè)，2022年，5月

12、20日，8點(diǎn)19分，星期三Inventory Submodel initial physical model注意到Vendors 和 Products是第二次出現(xiàn),對(duì)它們啥也不需要做第23頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Orders Submodel這是整個(gè)模型中最復(fù)雜的部分,包含12個(gè)實(shí)體.如圖所示.其中,Products和Employees已經(jīng)處理過(guò)了, Shipments, ShippingMethods, ShippingCarriers 和 Order Details可以直接轉(zhuǎn)換為表.對(duì)于Customers, 希望為每個(gè)客戶存儲(chǔ)多個(gè)地址,故需要把地址信息分離成

13、單獨(dú)的表,并與Customers建立聯(lián)系.如圖.Orders和Employees實(shí)體也有相似的地址問(wèn)題,可以暫時(shí)為它們加上聯(lián)系.最后,還要考察Payments實(shí)體的子類(lèi)型結(jié)構(gòu).第24頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Payments實(shí)體的子類(lèi)型結(jié)構(gòu)(1)如前所述,實(shí)現(xiàn)子類(lèi)型有三種方法:用一個(gè)表實(shí)現(xiàn)超類(lèi)型和所有的子類(lèi)型.為每個(gè)子類(lèi)型建一張表,把超類(lèi)型的數(shù)據(jù)加入到每個(gè)子類(lèi)型表中.把超類(lèi)型用一張表實(shí)現(xiàn),各子類(lèi)型用另外的表實(shí)現(xiàn).( Implement the supertype as a table and all the subtypes as additional table

14、s.)這里選擇第一種方法.如果選用第二/三種方法,聯(lián)系將變得非常的復(fù)雜.由此得到Payments表結(jié)構(gòu)如下:第25頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Payments實(shí)體的子類(lèi)型結(jié)構(gòu)(2)注意到這個(gè)表中的問(wèn)題,不少屬性是可選的(允許NULL值),如何保證三種支付方式一定存在一種呢?稍后討論商業(yè)規(guī)則時(shí)再說(shuō).第26頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Orders Submodel physical model至此,也Order有關(guān)的所有表基本成形了,如下圖所示.第27頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Web Session and Li

15、sts Submodel最后是兩個(gè)小的子模型,其中,web session只有一個(gè)新實(shí)體shopping cart, 它作為Customers 和 Products的連接,存儲(chǔ)客戶購(gòu)買(mǎi)的產(chǎn)品,顯然向表轉(zhuǎn)換得加入Customers ID和Product ID.Lists Submodel中是兩個(gè)用于查找的表: Lists 和List Items, 它們的作用是為前臺(tái)應(yīng)用提供一個(gè)場(chǎng)所存儲(chǔ)相關(guān)的數(shù)據(jù)列表,如訂單狀態(tài)或各種產(chǎn)品屬性. 只需要直接轉(zhuǎn)換.到此,所有的邏輯模型實(shí)體都找到了其物理模型中的歸屬,下邊處理聯(lián)系,即通過(guò)聯(lián)系來(lái)找出碼.第28頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Der

16、iving the Physical Model需要注意，表和實(shí)體未必是一對(duì)一的關(guān)系，因?yàn)閷?shí)體是對(duì)現(xiàn)實(shí)世界建模，而表要附合關(guān)系數(shù)據(jù)庫(kù)的理論建立物理模型的過(guò)程是，先為每個(gè)實(shí)體建一張表，然后再分裂或者組合表Using Entities to Model TablesUsing Relationships to Model Keys Using Attributes to Model Columns第29頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Using Relationships to Model Keys已經(jīng)看到，我們使用屬性objid作為每個(gè)實(shí)體的主碼名字。外碼的名字使用被引用

17、的表名跟上“_objid”。例：加上外碼的orders子模型第30頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Deriving the Physical Model需要注意，表和實(shí)體未必是一對(duì)一的關(guān)系，因?yàn)閷?shí)體是對(duì)現(xiàn)實(shí)世界建模，而表要附合關(guān)系數(shù)據(jù)庫(kù)的理論建立物理模型的過(guò)程是，先為每個(gè)實(shí)體建一張表，然后再分裂或者組合表Using Entities to Model TablesUsing Relationships to Model KeysUsing Attributes to Model Columns 第31頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Using A

18、ttributes to Model Columns使用實(shí)體的屬性來(lái)對(duì)表屬性建模。需要注意的是，由于存在實(shí)體的拆分和組合，屬性也存在合并和分拆到相應(yīng)表的問(wèn)題。也需要注意數(shù)據(jù)類(lèi)型，如果前邊的工作完全按照本課程中的要求做了，那么屬性建模非常簡(jiǎn)單，建模軟件可以完成所有的事情-拷過(guò)去就行了?？梢钥闯?，買(mǎi)一個(gè)好的建模軟件是非常重要的。這樣，你要做的僅僅按照標(biāo)準(zhǔn)改名字。第32頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Creating the Physical Model with SQL Server是時(shí)候生成數(shù)據(jù)庫(kù)了,本節(jié)先介紹SQL Server對(duì)象的命名規(guī)則(建議),再討論物理模型的建

19、立.命名原則(Naming Guidelines)生成物理模型(Deriving the Physical Model)實(shí)現(xiàn)商業(yè)規(guī)則(Implementing Business Rules in the Physical Model) 第33頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三在物理模型中實(shí)現(xiàn)商業(yè)規(guī)則在SQL Server中實(shí)現(xiàn)盡可能多的商業(yè)規(guī)則可能是一種比較好的方案。（因?yàn)槲覀儾恍湃螒?yīng)用程序？）本節(jié)介紹利用SQL Server來(lái)實(shí)現(xiàn)商業(yè)規(guī)則的各種方案：Using Constraints to Implement Business Rules Using Triggers

20、to Implement Business RulesImplementing Advanced Cardinality第34頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三使用約束來(lái)實(shí)現(xiàn)商業(yè)規(guī)則約束是控制進(jìn)入數(shù)據(jù)庫(kù)的數(shù)據(jù)的機(jī)制，這里介紹三種約束：Default - 為屬性提供缺省值Unique 指定屬性中的值保持唯一Check 根據(jù)要求自定義屬性應(yīng)滿足的條件第35頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Default Constraints添加default約束的代碼：ALTER TABLE dbo.tbl_employeeADD CONSTRAINT DF_stat

21、usDEFAULT 1 FOR status使用自動(dòng)義函數(shù)來(lái)產(chǎn)生缺省值：ALTER TABLE dbo.tbl_orderADD CONSTRAINT DF_ordernumberDEFAULT dbo.udf_new_orderid() FOR ordernumber第36頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Check Constraints用來(lái)保證裝入數(shù)據(jù)庫(kù)的數(shù)據(jù)確實(shí)是你所需要的。前邊介紹的customers表中，homephone, workphone, mobilephone都是允許為空的，但我們要求至少提供一個(gè)電話號(hào)碼，可以使用如下代碼添加check約束：ALTE

22、R TABLE dbo.tbl_customer WITH CHECKADD CONSTRAINT CK_phone_numberCHECK (homephone IS NOT NULL OR workphone IS NOT NULL OR mobilephone IS NOT NULL)第37頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Unique Constraints用來(lái)保證列沒(méi)有重復(fù)值,可作用于一列或者多列.例：ALTER TABLE dbo.tbl_orderADD CONSTRAINT UNQ_ordernumber UNIQUE NONCLUSTERED (orde

23、rnumber)所以在選擇了主碼之后,要評(píng)估所有的候選碼,它們是實(shí)施唯一性約束的最好的對(duì)象.第38頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三在物理模型中實(shí)現(xiàn)商業(yè)規(guī)則在SQL Server中實(shí)現(xiàn)盡可能多的商業(yè)規(guī)則可能是一種比較好的方案。（因?yàn)槲覀儾恍湃螒?yīng)用程序？）本節(jié)介紹利用SQL Server來(lái)實(shí)現(xiàn)商業(yè)規(guī)則的各種方案：Using Constraints to Implement Business RulesUsing Triggers to Implement Business Rules Implementing Advanced Cardinality第39頁(yè)，共86頁(yè)，20

24、22年，5月20日，8點(diǎn)19分，星期三Using Triggers to Implement Business RulesUsing triggers, you can write custom T-SQL code to run after something has happened to a table.Triggers can be set up to run after an INSERT, UPDATE, or DELETE or even instead of one of these actions.Keep in mind, however, that triggers fi

25、re as part of the transaction that started them, and they fire each time the action occurs. 可能導(dǎo)致性能低下.第40頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三MVM中的觸發(fā)器前文中用一個(gè)表來(lái)實(shí)現(xiàn)實(shí)體Payments，必須使用某種支付方式（支付方式不為空），當(dāng)使用某種支付方式的時(shí)候，需要保證與該支付方式相對(duì)應(yīng)的屬性不得為空。例如，當(dāng)選用信用卡支付時(shí)，相關(guān)的屬性the credit card number, expiration date, type, and credit card veri

26、fication (CCV) code不能為空。可以用觸發(fā)器來(lái)實(shí)現(xiàn)這樣的要求。使用觸發(fā)器來(lái)實(shí)現(xiàn)商業(yè)規(guī)則可以得到更健壯的系統(tǒng)，盡管這些規(guī)則可以用應(yīng)用程序代碼來(lái)實(shí)現(xiàn)。第41頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三在物理模型中實(shí)現(xiàn)商業(yè)規(guī)則在SQL Server中實(shí)現(xiàn)盡可能多的商業(yè)規(guī)則可能是一種比較好的方案。（因?yàn)槲覀儾恍湃螒?yīng)用程序？）本節(jié)介紹利用SQL Server來(lái)實(shí)現(xiàn)商業(yè)規(guī)則的各種方案：Using Constraints to Implement Business RulesUsing Triggers to Implement Business RulesImplementin

27、g Advanced Cardinality 第42頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Implementing Advanced Cardinality一到多的聯(lián)系相對(duì)簡(jiǎn)單，前邊已經(jīng)討論過(guò)，多到多的聯(lián)系可以通過(guò)兩個(gè)一到多的聯(lián)系和一個(gè)聯(lián)系表來(lái)實(shí)現(xiàn)?，F(xiàn)在介紹一對(duì)一的聯(lián)系以及“更高級(jí)的”如一對(duì)二的聯(lián)系。例：一個(gè)學(xué)校有多個(gè)學(xué)院，若有規(guī)則一個(gè)學(xué)院只有一個(gè)院長(zhǎng)，每個(gè)院長(zhǎng)只能是一個(gè)學(xué)院的院長(zhǎng)?？山⒛Ｐ腿缦拢猴@然這是一個(gè)一對(duì)多的聯(lián)系，如何保證一對(duì)一的特性呢？即一個(gè)人只能是一個(gè)學(xué)院的院長(zhǎng)可以使用觸發(fā)器來(lái)實(shí)現(xiàn)：第43頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三保證學(xué)院與院長(zhǎng)一對(duì)

28、一聯(lián)系的觸發(fā)器ALTER TRIGGER trg_one_dean_per_collegeON tbl_collegeFOR INSERT, UPDATEASDECLARE college_count intSELECT college_count = COUNT(tbl_college.id)FROM tbl_collegeJOIN tbl_facultyON tbl_college.dean_id = tbl_faculty.idWHERE tbl_faculty.id = (SELECT dean_id FROM INSERTED)IF college_count 1BEGINRAISE

29、RROR(This faculty member is dean of another college,11,1)ROLLBACKEND第44頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Pitfalls如果college表中數(shù)據(jù)很多，每次插入數(shù)據(jù)都要執(zhí)行觸發(fā)器代碼，可能導(dǎo)致較差的性能。可以考慮使用instead of觸發(fā)器，在插入前評(píng)估本次插入。但每個(gè)表的每個(gè)動(dòng)作只能定義一個(gè)instead of 觸發(fā)器。一個(gè)構(gòu)建良好的商業(yè)規(guī)則層，表現(xiàn)總是優(yōu)于觸發(fā)器。第45頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Contents在SQL Server中建立物理模型(Creating

30、the Physical Model with SQL Server)索引方面的考慮(Indexing Considerations) 建立抽象層(Creating an Abstraction Layer in SQL Server)第46頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Indexing Considerations索引的作用是加快檢索數(shù)據(jù)的速度。本節(jié)討論什么是索引，如何確定要建立的索引，如何實(shí)現(xiàn)。Indexing Overview Database Usage Requirements Determining the Appropriate IndexesImple

31、menting Indexes in SQL Server第47頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Indexing Overview討論索引前，需要對(duì)SQL Server 2008如何把數(shù)據(jù)存儲(chǔ)到磁盤(pán)上有一個(gè)粗略的了解。數(shù)據(jù)庫(kù)以文件的形式存儲(chǔ)，每個(gè)數(shù)據(jù)庫(kù)至少有兩個(gè)文件(數(shù)據(jù)文件和日志文件各一)數(shù)據(jù)文件內(nèi)部包含若干個(gè)extents(分區(qū)？)，每個(gè)extents包含8個(gè)pages(頁(yè)面？)，每個(gè)pages占用8K空間,每個(gè)page通常有自己的標(biāo)識(shí)符(identifier)，記錄在頁(yè)面上按插入數(shù)據(jù)庫(kù)的順序存儲(chǔ)。第48頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Wha

32、t Are Indexes?By default, when you create a new table in a SQL Server database, the server assigns a starting number of extents to that table. When you start inserting data, it adds rows of data to the pages inside the extents.Simply put, an index is a referencing set of pointers to rows of data.Ind

33、exes physically exist on disk, and thus they take up disk space separately from, and in addition to, your actual table data.第49頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三索引的類(lèi)型The two basic index types are clustered and nonclustered indexes. All indexes that you define on your tables will be one of these two types.

34、Clustered indexes actually restructure the data on disk.A nonclustered index is one that simply stores pointers to the pages that contain the rows of data you are looking for.SQL Server 2008還提供了其它類(lèi)型的索引.第50頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Other Index TypesUnique indexesIndexes with Included Columns : Cons

35、ider using this feature when youre building indexes to satisfy very specific queries and the index has gotten too large.XML IndexesSpatialFull-Text IndexesIndexed Views第51頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Indexing Considerations索引的作用是加快檢索數(shù)據(jù)的速度。本節(jié)討論什么是索引，如何確定要建立的索引，如何實(shí)現(xiàn)。Indexing OverviewDatabase Usage Requ

36、irements Determining the Appropriate IndexesImplementing Indexes in SQL Server第52頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Database Usage RequirementsReads versus WritesTransaction Data第53頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Indexing Considerations索引的作用是加快檢索數(shù)據(jù)的速度。本節(jié)討論什么是索引，如何確定要建立的索引，如何實(shí)現(xiàn)。Indexing OverviewDatabase Usage R

37、equirements Determining the Appropriate Indexes Implementing Indexes in SQL Server第54頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Determining the Appropriate IndexesReviewing Data Access PatternsBalancing IndexesCovering IndexesIndex StatisticsIndex Maintenance Considerations第55頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Indexing C

38、onsiderations索引的作用是加快檢索數(shù)據(jù)的速度。本節(jié)討論什么是索引，如何確定要建立的索引，如何實(shí)現(xiàn)。Indexing OverviewDatabase Usage Requirements Determining the Appropriate IndexesImplementing Indexes in SQL Server 第56頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Implementing Indexes in SQL ServerNaming Guidelinesidx_Customer_LastName_FirstNameCreating IndexesC

39、REATE NONCLUSTERED INDEX idx_Customer_LastName_FirstNameON Customer (LastName ASC, FirstName ASC)WITH (FILLFACTOR = 70,SORT_IN_TEMPDB = ON,ONLINE = ON)N IndexFileGroupFilegroups: Filegroups are a method of storing database data files in a separated fashion.Setting Up Index MaintenanceRebuilds versus

40、 Reorganization第57頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Contents在SQL Server中建立物理模型(Creating the Physical Model with SQL Server)索引方面的考慮(Indexing Considerations)建立抽象層(Creating an Abstraction Layer in SQL Server) 第58頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Creating an Abstraction Layer in SQL Server現(xiàn)在，可以把數(shù)據(jù)庫(kù)交給數(shù)據(jù)庫(kù)管理員進(jìn)行實(shí)施和管理了。

41、但是，大多數(shù)的應(yīng)用程序員都未必知道怎樣以最好的方式訪問(wèn)和使用數(shù)據(jù)庫(kù)。因此，還應(yīng)該做一項(xiàng)工作建立抽象層。What Is an Abstraction Layer?Why Use an Abstraction Layer?An Abstraction Layers Relationship to the Logical ModelAn Abstraction Layers Relationship to Object-Oriented ProgrammingImplementing an Abstraction Layer第59頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三What Is

42、 an Abstraction Layer?In general terms, an abstraction layer is a way of hiding the complex details about the functionality of a process.抽象層可以看成一個(gè)用戶接口，這里的用戶可能是一個(gè)程序。一般抽象層被實(shí)現(xiàn)為一個(gè)軟件層供用戶或其它程序訪問(wèn)。例如windows硬件抽象層(HAL)，OSI模型，開(kāi)放圖形庫(kù)(OpenGL)等。在數(shù)據(jù)庫(kù)中，抽象層的作用是隱藏模式的復(fù)雜性。在SQL Server中的抽象層由視圖，存儲(chǔ)過(guò)程，用戶定義函數(shù)和幾個(gè)其它的SQL Server對(duì)

43、象組成。對(duì)于定義良好的抽象層，任何用戶或程序不能直接訪問(wèn)物理表，任何事情都通過(guò)抽象層來(lái)處理。第60頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Why Use an Abstraction Layer?抽象層屏蔽了數(shù)據(jù)庫(kù)結(jié)構(gòu)的復(fù)雜性，至少有這幾個(gè)好處：提供了一種管理安全性的方法，該方法不損害數(shù)據(jù)庫(kù)的數(shù)據(jù)建立了可擴(kuò)展的數(shù)據(jù)庫(kù)提供了最大限度的靈活性。第61頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Security(1)建立抽象層為安全性控制提供了更多的選擇。表中可能包含敏感數(shù)據(jù)，涉及用戶隱私，還可能包含用戶登錄的口令。例如customer表。第62頁(yè)，共86頁(yè)，2022年，

44、5月20日，8點(diǎn)19分，星期三Security(2)可以創(chuàng)建視圖屏蔽數(shù)據(jù)。CREATE VIEW vw_customer_detailASSELECT email ,customer_id ,firstname ,lastname ,homephone ,workphone ,mobilephoneFROM tbl_customer第63頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Security(3)另外，數(shù)據(jù)庫(kù)建好后，將來(lái)可能向其中添加隱私數(shù)據(jù)，例如身份證號(hào)碼。使用抽象層，必須額外添加才能訪問(wèn)這些數(shù)據(jù)。（當(dāng)然，前提是你在定義視圖時(shí)沒(méi)有使用 “select * ” 這樣的子句。）

45、第64頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Extensibility and Flexibility(1)可擴(kuò)展性指將來(lái)修改數(shù)據(jù)模型的方便程度。靈活性指在不造成重大影響的情況下可以修改模型多少內(nèi)容。一般說(shuō)來(lái)靈活的模型總是有好的可擴(kuò)展性，但也并不總是如此。抽象層把應(yīng)用程序與物理數(shù)據(jù)隔離開(kāi)來(lái)，它幾乎允許你在不影響應(yīng)用程序的情況下做任何事。只要所有的應(yīng)用和用戶都通過(guò)視圖來(lái)讀數(shù)據(jù)，通過(guò)存儲(chǔ)過(guò)程來(lái)操縱數(shù)據(jù)，當(dāng)修改發(fā)生時(shí)，只需要對(duì)視圖和存儲(chǔ)過(guò)程作出相應(yīng)的調(diào)整。第65頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Extensibility and Flexibility(2)修

46、改SQL Server代碼與修改應(yīng)用程序代碼有什么區(qū)別？SQL 代碼不用編譯，意思是你可以隨時(shí)修改，而不用等到夜深人靜的時(shí)候。只有一個(gè)數(shù)據(jù)庫(kù)，而應(yīng)用程序被修改后要重新編譯和部署。第66頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三An Abstraction Layers Relationship to the Logical ModelWhen its time to build your abstraction layer, you should find that it more closely ties to the logical model than to the phy

47、sical model.要記住邏輯模型比物理模型更加“用戶友好”。不要為一個(gè)表創(chuàng)建四個(gè)存儲(chǔ)過(guò)程分別用于插入、刪除、更新和查詢。而是應(yīng)該創(chuàng)建一個(gè)過(guò)程來(lái)存儲(chǔ)實(shí)體，創(chuàng)建一個(gè)過(guò)程來(lái)插入和更新。第67頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三An Abstraction Layers Relationship to Object-Oriented ProgrammingOOP的核心概念是對(duì)象,而抽象層與邏輯模型緊緊地聯(lián)系在一起,其中心是實(shí)體. 實(shí)體與對(duì)象很接近,表現(xiàn)在實(shí)體的屬性與對(duì)象的屬性幾乎是一一對(duì)應(yīng)的,但對(duì)象有方法(methods),實(shí)體沒(méi)有,反映到抽象層,可以用存儲(chǔ)過(guò)程,當(dāng)對(duì)象要操縱

48、其數(shù)據(jù),例如order對(duì)象要存儲(chǔ)訂單,可以調(diào)用一個(gè)存儲(chǔ)過(guò)程向數(shù)據(jù)庫(kù)插入.若你的數(shù)據(jù)庫(kù)與一個(gè)應(yīng)用開(kāi)發(fā)項(xiàng)目緊密地結(jié)合在一起, 建議使用對(duì)象模型來(lái)指導(dǎo)抽象層的建立.第68頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Implementing an Abstraction Layer實(shí)現(xiàn)抽象層指在數(shù)據(jù)庫(kù)中創(chuàng)建對(duì)象(包括視圖,存儲(chǔ)過(guò)程和函數(shù))來(lái)作為應(yīng)用程序代碼與核心數(shù)據(jù)庫(kù)對(duì)象的中介.ViewsStored ProceduresOther Components of an Abstraction Layer第69頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Views使用視圖的目的是想

49、基于用戶的需求以一種有意義的方式把實(shí)體展示給最終用戶.創(chuàng)建視圖時(shí)一定要清楚用戶能不能真正理解展示在其面前的數(shù)據(jù).創(chuàng)建視圖來(lái)使應(yīng)用程序邏輯更簡(jiǎn)捷是一個(gè)好主意.避免使用”select *”語(yǔ)法和不帶值列表的insert語(yǔ)句.查看客戶(customer)數(shù)據(jù)的視圖第70頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三視圖的例子(1)CREATE VIEW vw_customersASSELECTobjid,email,customer_id,firstname,lastname,homephone,workphone,mobilephoneFROM tbl_customer第71頁(yè)，共86頁(yè)

50、，2022年，5月20日，8點(diǎn)19分，星期三視圖的例子(2)CREATE VIEW vw_customer_addressesASSELECTaddress_objid = objid,address_label,addressline1,addressline2,city,region,zipcode,customer_objidFROM tbl_addressWHERE customer_objid IS NOT NULL第72頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Stored Procedures（1）存儲(chǔ)過(guò)程有著與視圖類(lèi)似的規(guī)則。創(chuàng)建存儲(chǔ)過(guò)程的時(shí)候，考慮一下它們?cè)噲D影

51、響的實(shí)體?？梢钥紤]創(chuàng)建一個(gè)標(biāo)準(zhǔn)來(lái)控制怎樣和為何創(chuàng)建存儲(chǔ)過(guò)程。正確實(shí)施存儲(chǔ)過(guò)程與環(huán)境和自身選擇有關(guān)，沒(méi)有絕對(duì)正確的答案。常見(jiàn)的做法是為每個(gè)實(shí)體創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，它向所有相關(guān)的表更新或插入數(shù)據(jù)。第73頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Stored Procedures（2）例：為MVM中的Customer實(shí)體存儲(chǔ)變更的步驟確定記錄是否已經(jīng)存在、若不存在，則插入若存在，則更新返回插入或更新的信息相應(yīng)的存儲(chǔ)過(guò)程如下：ALTER PROCEDURE prc_save_customeremail varchar(50),customer_id char(10),firstname v

52、archar(50),lastname varchar(50)第74頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三Stored Procedures（3）,homephone varchar(15),workphone varchar(15),mobilephone varchar(15),addresses CustomerAddress ReadOnly,customer_objid int OUTPUTASMERGE tbl_customer AS pri_customerUSING(SELECT customer_id = customer_id)AS source_cust

53、omer(customer_id)第75頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三ON(pri_customer.customer_id =source_customer.customer_id)WHEN NOT MATCHED THENINSERT (email,customer_id,firstname,lastname,homephone,workphone,mobilephone)第76頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三VALUES (email,customer_id,firstname,lastname,homephone,workphone,m

54、obilephone)WHEN MATCHED THENUPDATESET email=email,firstname=firstname,lastname=lastname,homephone=homephone,workphone=workphone,mobilephone=mobilephone;第77頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三SELECT customer_objid = objidFROM tbl_customerWHERE customer_id = customer_id;MERGE tbl_address AS current_addressesU

55、SING(SELECT customer_objid = customer_objid,address_label,addressline1,addressline2,city,region,country,zipcode,is_deletedFROM addresses)第78頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三AS source_addresses(customer_objid,address_label,addressline1,addressline2,city,region,country,zipcode,is_deleted)ON(current_addresses.address_label =source_addresses.address_labelANDcurrent_addresses.customer_objid =source_addresses.customer_objid)第79頁(yè)，共86頁(yè)，2022年，5月20日，8點(diǎn)19分，星期三WHEN NOT MATCHED THENINSERT (address_label,addressline1,addressline2,city,regio