《創(chuàng)建和管理數(shù)據(jù)庫(kù)》PPT幻燈片

1、第2章 創(chuàng)建和管理數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù) 創(chuàng)建文件組 管理數(shù)據(jù)庫(kù) 分離和附加數(shù)據(jù)庫(kù) 數(shù)據(jù)結(jié)構(gòu)介紹 推薦操作,創(chuàng)建數(shù)據(jù)庫(kù),定義數(shù)據(jù)庫(kù) 事務(wù)日志的工作過(guò)程 設(shè)置數(shù)據(jù)庫(kù)選項(xiàng) 檢索數(shù)據(jù)庫(kù)信息,2.1 創(chuàng)建數(shù)據(jù)庫(kù),定義數(shù)據(jù)庫(kù),定義一個(gè)數(shù)據(jù)庫(kù) 數(shù)據(jù)庫(kù)的名字 數(shù)據(jù)庫(kù)的大小 數(shù)據(jù)庫(kù)所在的文件 定義數(shù)據(jù)庫(kù)的兩種方法 SQL Server 企業(yè)管理器 在查詢(xún)分析器中輸入 CREATE DATABASE 語(yǔ)句,2.1.1 定義數(shù)據(jù)庫(kù),定義數(shù)據(jù)庫(kù)（續(xù)）,2.1.1 定義數(shù)據(jù)庫(kù),CREATE DATABASE SampleON PRIMARY ( NAME=SampleData, FILENAME=c:Program Fi

2、les.DataSample.mdf, SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=SampleLog, FILENAME= c:Program Files.DataSample.ldf, SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB)COLLATE Chinese_PRC_CI_AS,事務(wù)日志的工作過(guò)程,2.1.2 事務(wù)日志的工作過(guò)程,事務(wù)日志的工作過(guò)程（續(xù)）,數(shù)據(jù)庫(kù)恢復(fù)的三個(gè)階段 分析、恢復(fù)、撤銷(xiāo),2.1.2 事務(wù)日志的工作過(guò)程,設(shè)置數(shù)據(jù)庫(kù)選項(xiàng),設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)的兩種方式 企業(yè)管理器 ALTER

3、DATABASE 語(yǔ)句 一共有五類(lèi)數(shù)據(jù)庫(kù)選項(xiàng) 自動(dòng)選項(xiàng) AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS 游標(biāo)選項(xiàng) CURSOR_CLOSE_ON_COMMIT, URSOR_DEFAULT LOCAL | GLOBAL 恢復(fù)選項(xiàng) RECOVERY FULL | BULK_LOGGED | SIMPLE, TORN_PAGE_DETECTION SQL選項(xiàng) ANSI_NULL_DEFAULT, ANSI_NULLS 狀態(tài)選項(xiàng) READ_ONLY | READ_WRITE, SINGLE_USER | RESTRICTED_USER | MULTI_U

4、SER,2.1.3 設(shè)置數(shù)據(jù)庫(kù)選項(xiàng),檢索數(shù)據(jù)庫(kù)信息,檢索數(shù)據(jù)庫(kù)信息的兩種方法 使用 DATABASEPROPERTYEX 函數(shù)來(lái)查看數(shù)據(jù)庫(kù)的屬性 SELECT DATABASEPROPERTYEX（數(shù)據(jù)庫(kù), 屬性） 使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)顯示數(shù)據(jù)庫(kù)以及數(shù)據(jù)庫(kù)參數(shù)的信息 sp_helpdb sp_helpdb 數(shù)據(jù)庫(kù)名 sp_spaceused 對(duì)象名,2.1.4 檢索數(shù)據(jù)庫(kù)信息,第2章 創(chuàng)建和管理數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù) 創(chuàng)建文件組 管理數(shù)據(jù)庫(kù) 分離和附加數(shù)據(jù)庫(kù) 數(shù)據(jù)結(jié)構(gòu)介紹 推薦操作,創(chuàng)建文件組,2.2 創(chuàng)建文件組,文件組的概念 文件組是 SQL Server 中一個(gè)或多個(gè)文件的命名集合，它構(gòu)成分配或

5、用于數(shù)據(jù)庫(kù)管理的單個(gè)單元 文件組的使用場(chǎng)合 當(dāng)有多個(gè)磁盤(pán)，希望把文件分布在這些磁盤(pán)上以提高性能 文件組的類(lèi)型 兩種文件組：主文件組和用戶(hù)定義文件組 包含主文件的文件組為主文件組 用戶(hù)定義的文件組是指在首次創(chuàng)建或以后更改數(shù)據(jù)庫(kù)時(shí)，用戶(hù)明確創(chuàng)建的任何文件組,創(chuàng)建文件組（續(xù)）,2.2 創(chuàng)建文件組,指定默認(rèn)的文件組 數(shù)據(jù)庫(kù)建立時(shí)的默認(rèn)文件組為主文件組 未指定文件組的對(duì)象都將放到默認(rèn)文件組中 調(diào)整默認(rèn)的主文件組的大小 若主文件組沒(méi)有空間，就不能再往系統(tǒng)表中寫(xiě)入任何信息 查看文件組信息：使用函數(shù)和存儲(chǔ)過(guò)程 函數(shù)：FILE_NAME，F(xiàn)ILE_ID，F(xiàn)ILE_PROPERTY，F(xiàn)ILEGROUP_NAME，

6、FILEGROUP_ID 和 FILEGROUP_PROPERTY 系統(tǒng)存儲(chǔ)過(guò)程,第2章 創(chuàng)建和管理數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù) 創(chuàng)建文件組 管理數(shù)據(jù)庫(kù) 分離和附加數(shù)據(jù)庫(kù) 數(shù)據(jù)結(jié)構(gòu)介紹 推薦操作,管理數(shù)據(jù)庫(kù),管理數(shù)據(jù)文件和日志文件的增長(zhǎng) 監(jiān)視和擴(kuò)展事務(wù)日志 收縮數(shù)據(jù)庫(kù)或文件,2.3 管理數(shù)據(jù)庫(kù),管理數(shù)據(jù)文件和日志文件的增長(zhǎng),ALTER DATABASE Sample MODIFY FILE ( NAME = SampleLog, SIZE = 15MB) GO ALTER DATABASE Sample ADD FILE (NAME = SampleData2, FILENAME=c:Program F

7、iles. DataSample2.ndf, SIZE=15MB, MAXSIZE=20MB) GO,使文件自動(dòng)增長(zhǎng) 手動(dòng)擴(kuò)充數(shù)據(jù)庫(kù)文件 添加次要數(shù)據(jù)庫(kù)文件,2.3.1 管理數(shù)據(jù)文件和日志文件的增長(zhǎng),監(jiān)視和擴(kuò)展事務(wù)日志,監(jiān)視日志 企業(yè)管理器 DBCC SQLPERF（LOGSPACE） 語(yǔ)句 Windows 2000 系統(tǒng)監(jiān)視器 產(chǎn)生大量日志操作的情況 往有索引的表中裝入大量數(shù)據(jù) 大事務(wù) 進(jìn)行日志記錄的文本或圖像操作 在需要時(shí)擴(kuò)展日志 企業(yè)管理器或者 ALTER DATABASE 語(yǔ)句,2.3.2 監(jiān)視和擴(kuò)展事務(wù)日志,收縮數(shù)據(jù)庫(kù)或文件,收縮整個(gè)數(shù)據(jù)庫(kù) 企業(yè)管理器 DBCC SHRINKDATA

8、BASE 語(yǔ)句 DBCC SHRINKDATABASE (數(shù)據(jù)庫(kù)名 , 目標(biāo)百分比 , NOTRUNCATE | TRUNCATEONLY) 收縮數(shù)據(jù)庫(kù)中的一個(gè)數(shù)據(jù)文件 企業(yè)管理器 DBCC SHRINKFILE 語(yǔ)句 DBCC SHRINKFILE (文件名 | 文件id , 目標(biāo)大小 , EMPTYFILE | NOTRUNCATE | TRUNCATEONLY) 自動(dòng)收縮數(shù)據(jù)庫(kù) 設(shè)置數(shù)據(jù)庫(kù)選項(xiàng) autoshrink 為 true,2.3.3 收縮數(shù)據(jù)庫(kù)或文件,收縮數(shù)據(jù)庫(kù)或文件（續(xù)）,收縮語(yǔ)句中的選項(xiàng) 因?yàn)?DBCC SHRINKDATABASE 逐個(gè)文件地收縮數(shù)據(jù)庫(kù)，所以實(shí)際收縮所使用的

9、機(jī)理與 DBCC SHRINKFILE 的相同 NOTRUNCATE 選項(xiàng)導(dǎo)致在數(shù)據(jù)庫(kù)文件中保留所釋放的文件空間。 SQL Server 只通過(guò)將數(shù)據(jù)移動(dòng)到文件的前面壓縮數(shù)據(jù)。默認(rèn)情況是所有釋放的文件空間返回給操作系統(tǒng) TRUNCATEONLY 選項(xiàng)導(dǎo)致文件中的所有未使用的空間釋放給操作系統(tǒng)。不嘗試將其重新分配到未分配頁(yè)。當(dāng)使用TRUNCATEONLY 時(shí)，參數(shù) target_size 和 target_percent 被忽略 EMPTYFILE 選項(xiàng)，只適用于 DBCC SHRINKFILE，將清空數(shù)據(jù)文件的內(nèi)容并將數(shù)據(jù)移動(dòng)到同一文件組中的其他文件,2.3.3 收縮數(shù)據(jù)庫(kù)或文件,第2章 創(chuàng)建

10、和管理數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù) 創(chuàng)建文件組 管理數(shù)據(jù)庫(kù) 分離和附加數(shù)據(jù)庫(kù) 數(shù)據(jù)結(jié)構(gòu)介紹 推薦操作,分離數(shù)據(jù)庫(kù) 附加數(shù)據(jù)庫(kù),分離和附加數(shù)據(jù)庫(kù),2.4 分離和附加數(shù)據(jù)庫(kù),分離和附加數(shù)據(jù)庫(kù),SQL Server 2000 允許分離數(shù)據(jù)庫(kù)的數(shù)據(jù)和事務(wù)日志文件，然后將其重新附加到另一臺(tái)服務(wù)器，甚至同一臺(tái)服務(wù)器上 分離數(shù)據(jù)庫(kù) 分離數(shù)據(jù)庫(kù)將從 SQL Server 刪除數(shù)據(jù)庫(kù)，但是保持組成該數(shù)據(jù)庫(kù)的數(shù)據(jù)和事務(wù)日志文件中的數(shù)據(jù)庫(kù)完好無(wú)損 分離數(shù)據(jù)庫(kù)語(yǔ)法：EXEC sp_detach_db 數(shù)據(jù)庫(kù)名 分離數(shù)據(jù)庫(kù)要求沒(méi)有用戶(hù)正在使用數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)中沒(méi)有未完成的事務(wù)，并且在內(nèi)存中沒(méi)有這個(gè)數(shù)據(jù)庫(kù)的臟頁(yè) 一旦數(shù)據(jù)庫(kù)被分離，其

11、條目就從 master 數(shù)據(jù)庫(kù)的 sysdatabases 表中被移除。從 SQL Server 的角度看，數(shù)據(jù)庫(kù)似乎被刪除了但是其物理文件仍然保存在磁盤(pán)上,2.4 分離和附加數(shù)據(jù)庫(kù),分離和附加數(shù)據(jù)庫(kù)（續(xù)）,附加數(shù)據(jù)庫(kù) 當(dāng)附加在數(shù)據(jù)庫(kù)上時(shí)，必須指定主數(shù)據(jù)文件的名稱(chēng)和物理位置 附加數(shù)據(jù)庫(kù)語(yǔ)法 EXEC sp_attach_db 數(shù)據(jù)庫(kù)名=數(shù)據(jù)庫(kù)名 文件名=文件名16 只需要指定當(dāng)前的文件名，無(wú)論當(dāng)前的名字和從數(shù)據(jù)庫(kù)分離時(shí)的名字是否相同。亦可以指定不同于原來(lái)數(shù)據(jù)庫(kù)名字的新的數(shù)據(jù)庫(kù)名字。只有當(dāng)文件位置和在主文件首存儲(chǔ)的文件原始位置不同的時(shí)候，才需要指定文件名 若附加的文件超過(guò)了16個(gè)，可使用 CRE

12、ATE DATABASE 語(yǔ)句并指定 for attach 選項(xiàng),2.4 分離和附加數(shù)據(jù)庫(kù),刪除數(shù)據(jù)庫(kù),刪除數(shù)據(jù)庫(kù)的方法 企業(yè)管理器 DROP DATABASE 語(yǔ)句 DROP DATABASE 數(shù)據(jù)庫(kù)名 ,n 可以一次刪除多個(gè)數(shù)據(jù)庫(kù) 刪除數(shù)據(jù)庫(kù)的限制 不能刪除： 正在被還原的數(shù)據(jù)庫(kù) 任何用戶(hù)因?yàn)樽x或?qū)懚蜷_(kāi)的數(shù)據(jù)庫(kù) 正在發(fā)布它的任何一張表作為 SQLServer 復(fù)制組成部分的數(shù)據(jù)庫(kù) 系統(tǒng)數(shù)據(jù)庫(kù),2.4.1刪除數(shù)據(jù)庫(kù),第2章 創(chuàng)建和管理數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù) 創(chuàng)建文件組 管理數(shù)據(jù)庫(kù) 分離和附加數(shù)據(jù)庫(kù) 數(shù)據(jù)結(jié)構(gòu)介紹 推薦操作,數(shù)據(jù)結(jié)構(gòu)介紹,數(shù)據(jù)存儲(chǔ)方式 頁(yè)和擴(kuò)展盤(pán)區(qū)類(lèi)型 管理文件空間的頁(yè) 跟蹤表和

13、索引的頁(yè),2.5數(shù)據(jù)結(jié)構(gòu)介紹,數(shù)據(jù)庫(kù),數(shù)據(jù)存儲(chǔ)方式,擴(kuò)展盤(pán)區(qū) (8個(gè)連續(xù)的 8KB頁(yè)),頁(yè) (8 KB),表， 索引,數(shù)據(jù),每行大小最多 8 060 字節(jié),數(shù)據(jù) (文件).mdf 或 .ndf,日志 (文件).Idf,2.5.1 數(shù)據(jù)存儲(chǔ)方式,頁(yè)和擴(kuò)展盤(pán)區(qū)類(lèi)型,頁(yè)的類(lèi)型 跟蹤空間分配的頁(yè) 包含了緊密壓縮的信息，這樣 SQLServer 就能有效地將其保留在內(nèi)存中以簡(jiǎn)化跟蹤 包含用戶(hù)和索引數(shù)據(jù)的頁(yè) 八種頁(yè)類(lèi)型 數(shù)據(jù)頁(yè)、文本/圖像頁(yè)、索引頁(yè)、頁(yè)可用空間（PFS）頁(yè)、全局分配映射表（GAM 和 SGAM）頁(yè)、索引分配映射表（IAM）頁(yè)、大容量更改映射表頁(yè)和差異更改映射表頁(yè),2.5.2 頁(yè)和擴(kuò)展盤(pán)區(qū)類(lèi)

14、型,頁(yè)和擴(kuò)展盤(pán)區(qū)類(lèi)型（續(xù)）,統(tǒng)一擴(kuò)展盤(pán)區(qū),空閑空間,混合擴(kuò)展盤(pán)區(qū),擴(kuò)展盤(pán)區(qū)的類(lèi)型 混合擴(kuò)展盤(pán)區(qū)：包含 2 個(gè)或多個(gè)對(duì)象的頁(yè)的擴(kuò)展盤(pán)區(qū)稱(chēng)為“混合擴(kuò)展盤(pán)區(qū)”。每張表起始于一個(gè)混合擴(kuò)展盤(pán)區(qū)。主要為跟蹤空間的頁(yè)及包含小對(duì)象的頁(yè)使用混合擴(kuò)展盤(pán)區(qū) 統(tǒng)一擴(kuò)展盤(pán)區(qū)：將所有八頁(yè)分配給單個(gè)對(duì)象的擴(kuò)展盤(pán)區(qū)稱(chēng)為“統(tǒng)一擴(kuò)展盤(pán)區(qū)”。在表或索引需要超過(guò) 64 KB空間時(shí)使用,2.5.2 頁(yè)和擴(kuò)展盤(pán)區(qū)類(lèi)型,管理文件空間的頁(yè),2.5.3 管理文件空間的頁(yè),每個(gè)文件的第一個(gè)擴(kuò)展盤(pán)區(qū)是混合擴(kuò)展盤(pán)區(qū)，包含一個(gè)文件的頁(yè)首頁(yè)和緊接著的三個(gè)分配頁(yè) 文件的頁(yè)首頁(yè)：包含該文件的特性信息 PFS（頁(yè)可用空間）頁(yè)：包含有關(guān)文件中頁(yè)可用空間信息的分

15、配頁(yè) 每個(gè) PFS 頁(yè)可以跟蹤 8 000 個(gè)連續(xù)頁(yè)，將近 64 MB 的數(shù)據(jù)。PFS 頁(yè)對(duì)每一頁(yè)都有一個(gè)相應(yīng)的字節(jié)，跟蹤： 該頁(yè)是否已分配 該頁(yè)是在混合擴(kuò)展盤(pán)區(qū)還是在統(tǒng)一擴(kuò)展盤(pán)區(qū) 該頁(yè)可用空間的近似數(shù)量,管理文件空間的頁(yè)（續(xù)）,2.5.3 管理文件空間的頁(yè),GAM（全局分配映射表） 和 SGAM（共享全局分配映射表） 頁(yè) SQLServer 使用 GAM 和 SGAM 頁(yè)來(lái)確定未使用的擴(kuò)展盤(pán)區(qū)或有未使用頁(yè)的混合擴(kuò)展盤(pán)區(qū)的位置 GAM 和 SGAM 配合使用，每對(duì)管理 63 904 個(gè)擴(kuò)展盤(pán)區(qū),第2章 創(chuàng)建和管理數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù) 創(chuàng)建文件組 管理數(shù)據(jù)庫(kù) 分離和附加數(shù)據(jù)庫(kù) 數(shù)據(jù)結(jié)構(gòu)介紹 推薦操

16、作,推薦操作,2.6 推薦操作,目標(biāo) 創(chuàng)建數(shù)據(jù)庫(kù) 管理數(shù)據(jù)庫(kù)的增長(zhǎng) 改變數(shù)據(jù)庫(kù)選項(xiàng)以控制事務(wù)日志的清除頻率 練習(xí)1 創(chuàng)建 ClassNorthwind 數(shù)據(jù)庫(kù) 練習(xí)2 管理 ClassNorthwind 事務(wù)日志文件的增長(zhǎng) 練習(xí)3 設(shè)置數(shù)據(jù)庫(kù)恢復(fù)模型,實(shí)驗(yàn) 創(chuàng)建和管理數(shù)據(jù)庫(kù),回顧,學(xué)習(xí)完本章后，將能夠： 創(chuàng)建數(shù)據(jù)庫(kù) 創(chuàng)建文件組 管理數(shù)據(jù)庫(kù) 描述數(shù)據(jù)結(jié)構(gòu),隨堂練習(xí)1,在公司人事管理數(shù)據(jù)庫(kù)中使用表Employee記錄員工信息，表EmployeeDemographics記錄統(tǒng)計(jì)信息。公司大約有2000名員工，員工數(shù)量的變化率大約在2%。員工離職后，記錄會(huì)保留。統(tǒng)計(jì)信息每年變化9%。下面是數(shù)據(jù)庫(kù)的邏輯

17、設(shè)計(jì)和腳本設(shè)計(jì)：,隨堂練習(xí)1（續(xù)）,ALTER TABLE dbo.Employee WITH NOCHECK ADD CONSTRAINT pk_Employee PRIMARY KEY CLUSTERED (EmployeeID) WITH FILLFACTOR = 90 GO ALTER TABLE dbo.EmployeeDemographics WITH NOCHECK ADD CONSTRAINT pk_EmployeeDemographics PRIMARY KEY CLUSTERED (EmployeeID) WITH FILLFACTOR = 90 GO 假設(shè)所有的Varch

18、ar字段只用到50%，請(qǐng)問(wèn)創(chuàng)建時(shí)SIZE和FILEGROWTH分別為多少？,隨堂練習(xí)1 （續(xù)）,SIZE = 1GB SIZE = 1MB SIZE = 2048K FILEGROWTH = 20 FILEGROWTH = 5% FILEGROWTH = 0,隨堂練習(xí)2,使用腳本SQL創(chuàng)建一個(gè)名為Employees的數(shù)據(jù)庫(kù)。該 數(shù)據(jù)庫(kù)需要經(jīng)常更新，數(shù)據(jù)庫(kù)的大小為1G，假設(shè)C盤(pán)有 10G的空間。下面哪段SQL腳本語(yǔ)言正確？ CREATE DATABASE Employees ON (NAME = Employees_dat, FILENAME = C:DATAEmployees.mdf, SIZ

19、E = 10, MAXSIZE = 1GB, FILEGROWTH = 5) CREATE DATABASE Employees ON (NAME = Employees_dat, FILENAME = C:DATAEmployees.mdf, SIZE = 10, MAXSIZE = 1GB, FILEGROWTH = 10%),隨堂練習(xí)2（續(xù)）,CREATE DATABASE Employees ON (NAME = Employees_dat, FILENAME = C:DATAEmployees.mdf, SIZE = 100, MAXSIZE = 1GB, Maxsize = UNLIMITED) CREATE DATABASE Employees ON (NAME = Employees_dat, FILENAME = C:DATAEmployees.mdf, SIZE = 1GB),隨堂練習(xí)3,在構(gòu)建一個(gè)SQL Server 2000的網(wǎng)上數(shù)據(jù)庫(kù)時(shí)，使用 兩個(gè)RAID陣列來(lái)放置數(shù)據(jù)文件，所有經(jīng)常訪問(wèn)的 表至少有一列有索引，應(yīng)當(dāng)采用何種文件放置策 略來(lái)提高系統(tǒng)的I/O性能？ 將經(jīng)常連接的表放在同一個(gè)文件組中 將訪問(wèn)頻率高的表及其索引放置在不同的文件組中 將經(jīng)常連接的表放在不同一個(gè)文件組中 將訪問(wèn)頻率