第 4 章 資料庫的管理與規劃

本章提要 認識系統資料庫 系統資料表 資料庫的規劃 SQL Server 資料庫的實體架構 索引的結構與運作方式

認識系統資料庫 master msdb model tempdb

master master 資料庫記錄的是有關 SQL Server 的資訊, 包括所有的登入帳戶、系統的組態、各資料的初始資訊等各類重要資料。

msdb msdb 是另一個供系統使用的資料庫, 它是供 SQL Server Agent 做各類排程作業 (job) 所用的資料庫。

model 在 SQL Server 中建立新的資料庫時, SQL Server 會以 model 資料庫為範本, 將其內容複製到我們的新資料庫, 因此在所有新建的資料庫中, 都會有和 model 資料庫內容一樣的系統資料表和檢視表等資料庫物件。

tempdb tempdb 是用來存放暫時性資料用的, 像是使用者在進行各種查詢或排序時, SQL Server 就會在此建立這些暫時性的工作資料表。

系統資料表 資料庫目錄資料表 Master 資料庫特有的系統資料表 其它的系統資料表

資料庫目錄資料表 sysallcoations syscolumns syscomments sysconstraints sysdepends sysfilegroups sysfiles sysforeignkeys sysfulltextcatalogs sysindexes sysindexkeys sysmembers sysobjects syspermissions sysprotects sysrefereneces systypes sysusers

master 資料庫特有的系統資料表 sysaltfiles syscacheobjects syscharsets sysconfigures、syscurconfigs sysdatabases sysdevices syslanguages syslockinfo syslogins、 sysxlogins sysmessages sysoledbusers sysperfinfo sysprocesses sysremotelogins sysservers

資料庫的規劃 檔案 檔案群組 資料檔的大小 檔案的成長與縮減 使用 raw partition

檔案 主要資料檔 (primary data file) 次要資料檔 (secondary data file) 交易記錄檔 (log file)

檔案群組 主檔案群組(Primary filegroup) 使用者定義檔案群組 (User-defined filegroup) 預設檔案群組 (Default filegroup)

檔案群組 若有建立使用者定義檔案群組, 並想指定使用者定義檔案群組為預設檔案群組的話, 可用如下的 SQL 語法：

檔案群組 下圖就是將 My Data 資料庫中的 Secondary 檔案群設為預設檔案群組的情形：

檔案群組 可用 ON 參數來設定要將資料表或資料庫放在哪個群組中：

資料檔的大小

檔案的成長與縮減 除非另行指定, 否則 SQL Server 預設是每當空間不夠時, 就自動讓資料檔增加 10% 的大小。 除了每次成長的數量外, 另一個要考慮的就是成長的上限, 也就是允許這個資料庫最多佔用多少的磁碟空間。預設是不限制檔案成長。　

手動加大檔案 以下敘述可以將 Test 資料庫中的 Test 資料檔之大小設為 10 MB：

檔案的縮減 在 Enterprise Manager 中, 於要處理的資料庫名稱上按滑鼠右鈕, 在快顯功能表中執行所有工作 / 壓縮資料庫命令：

檔案的縮減

檔案的縮減 按下檔案鈕：

使用 raw partition raw partition 是指已經用磁碟分割程式　(例如 FDISK) 分割出的磁碟分區, 但尚未被格式化 (FORMAT), 因此作業系統仍無法使用該磁碟空間。 使用 raw partition 的好處, 是當資料庫讀寫頻繁到磁碟的 I/O 已經成為效率的瓶頸時, 可對效率有些微的提昇。

使用 raw partition 的缺點 無法在 raw partition 中執行複製、搬移、和刪除檔案等操作。

使用 raw partition 步驟 1. 先用磁碟管理工具建立新的分割區, 但不格式化。 2. 在 CREATE DATABASE 敘述中指定資料庫檔的存放路徑, 是在前一步驟所建立的未格式化分割區上。

使用 raw partition 步驟

SQL Server 資料庫的實體架構 Page text、ntext、和 image Extent 資料檔的結構

Page Page (頁) 是最基本的儲存單位, 每個 Page 的大小寫為 8K 位元組。 雖然 page 的大小是 8K, 但在其開頭有個 96 位元組大小的 page header, 用來記錄此頁的相關資訊。

Page

text、 ntext 、和 image

text、 ntext 、和 image 若 text、ntext、或 image 的資料很少或很大時, 存放的情況又會有些變化： 若資料量小於 64 位元組, 則資料會直接存於 text root 結構中。 若資料量大於 32KB, 表示資料會被切割成相當多塊以存於不同的 page 中。

text、 ntext 、和 image sp_tableoption 的用法如下：

Extent 依其 page 內容分類 Mixed extent Uniform extent

資料檔的結構 檔頭 PFS (Page Free Space) page GAM (Global Allocation Map) SGAM (Shared GAM)

索引的結構 資料頁中存放的是一筆筆的記錄, 索引頁存放的則是每一筆記錄中的索引欄位。 B-Tree 結構就是從根節點到任一個末節點的距離都是一樣的, 因此採用這種索引結構, 可以保證索引任一筆資料的效率都是一樣快。

B-tree 結構

叢集索引

非叢集索引

SQL Server 使用索引的方式