高级数据库技术-高级数据库技术8-存储管理与索引技术课件

1、数据库存储缓冲区管理：替换策略LRU(最近最少使用)如果必须替换一个块，则替换最近最少使用的块MRU(最近最常使用)如果必须替换一个块，则替换最近最常使用的块缓冲区管理访问内存页面为快速找到页面，内存页面地址被散列给定dbid-标识(数据库ID, 文件号、页面号的组合)，计算其hash地址Lazywriter(缓冲池管理器) 使用时钟算法每个缓冲池有一个计数器，当有进程访问该缓冲池时，计数器加一当需要Lazywriter扫描每个缓冲池，如果其计数器为0，则释放该空间（如果发生修改，则写回磁盘）；如果计数器不为0，则计数器减一存储分配文件文件组页面区间文件一个数据库是操作系统文件的集合数据库之间

2、不能进行文件共享一个数据库至少包括一个数据文件和一个日志文件每个数据库最多32,767个文件文件类型主数据文件 - .mdf 每个数据库一个目录表sysfiles1必须完全包含在此文件中辅助数据文件 - .ndf 零个或多个日志文件 - .ldf 一个或多个包含事务日志文件文件初始大小、最大尺寸、最小尺寸、以及增长大小可以指定文件可以自动增长-按指定大小或者当前大小的百分比文件可以使用DBCC命令来减小尺寸驱动器不能是压缩的数据和日志文件不能放在同一驱动器上USE UserDB DBCC SHRINKFILE (DataFil1, 7)DBCC SHRINKDATABASE (UserDB,

3、10) 文件组许多文件可以形成一个命名的文件组进行统一的分配和管理，提供了灵活性组号1是主组号每个数据库中都存在包含数据库的系统表日志文件不加入文件组每个数据库最多256个文件组一个文件组包含多个文件，一个文件只属于一个文件组一个文件组包含多个对象，一个对象只属于一个文件组文件组CREATE DATABASE SalesON PRIMARY ( NAME = SPri1_dat, = c:SPri1dat.mdf,SIZE = 10, MAXSIZE = 50, = 15% ),( NAME = SPri2_dat, = c:SPri2dt.ndf,SIZE = 10, MAXSIZE = 5

4、0, = 15% ), SalesGroup1( NAME = SGrp1Fi1_dat, = c:SG1Fi1dt.ndf, SIZE = 10,MAXSIZE = 50, = 5 ),( NAME = SGrp1Fi2_dat, = c:SG1Fi2dt.ndf,SIZE = 10, MAXSIZE = 50, = 5 ), SalesGroup2( NAME = SGrp2Fi1_dat, = c:SG2Fi1dt.ndf,SIZE = 10, MAXSIZE = 50, = 5 ), ( NAME = SGrp2Fi2_dat, = c:SG2Fi2dt.ndf,SIZE = 10,

5、MAXSIZE = 50, = 5 )LOG ON( NAME = Sales_log, = c:salelog.ldf,SIZE = 5MB, MAXSIZE = 25MB, = 5MB )通过CREATE DATABASE和ALTER DATABASE来创建文件组页面数据文件被划分成8k的页面每个文件中的页面号都以0开始页面号的形式为(#)，如(3:124).mdf总是文件1，第一个日志文件是文件2区间8个连续页面构成一个区间64k总是从能被8整除的页面开始第一个区间区间0位于文件1，包含页面0到7第二个区间区间8，包含页面8到15存储分配总是按照区间为单位进行对象每次增长1个区间I/O可

6、以按页面(8 KB)或者区间(64 KB)来进行区间单一区间：区间被单一对象拥有混合区间：区间被多个对象共享(最多8个)对象的头8个页面从混合区间种分配An 8K pageExtent (8 pages = 64K)T1T2T1Mixed ExtentT3T40816243240T3T4区间12 个空闲区间4个空闲区间区间按比例从所有文件中分配(Round Robin )保证文件组中所有文件按相同比例填充GAM 全局分配位图记录文件当中哪些区间已经被分配的页面可以看成是一个8000个字节的位图每个位代表一个区间位0代表区间0，位1代表区间8，位2代表区间160：被使用1：未被使用差不多64,0

7、00位，所以可以表示 64,000个区间表达4 GB (3,994 MBs)数据空间如果文件大于4 GB ，可以增加新的GAM页第一个GAM位于文件的第三个页面(page2)下一个GAM位于第511,232个页面GAM总是位于它所控制范围的第一个页面SGAM 共享全局分配位图用于表示文件中哪些区间被作为共享区间分配并且仍然有空闲区间可供分配结构与GAM相同每一位代表一个区间位0代表区间0，位1代表区间8，位2代表区间160：区间要么不是共享区间，要么共享区间没有空闲页1：区间是共享区间，并且至少有一个空闲页表达4 GB数据空间，与对应的GAM表达范围相同如果文件大于4 GB ，可以增加新的SG

8、AM页第一个SGAM位于文件的第四个页面(page3)下一个SGAM位于第511,233个页面GAM与SGAMCurrent Use of ExtentGAM Bit SettingSGAM Bit SettingFree, not in use10Uniform extent, or full mixed extent00Mixed extent with free pages01如果需要一个新的、完全没有使用的区间，可以从GAM中寻找位为1的区间如果需要一个有空闲页的混合区间，可以寻找在GAM中位为0、在SGAM中位为1的区间PFS 空闲页空间记录文件中每个页面是否已经被分配以及有多少空闲

9、空间每一个页面在PFS页中有一个字节对应每个PFS覆盖8088个连续页面(64 MB)页面充满度：1-50%, 51-80%, 81-95%, 96-100%第一个PFS位于文件的第二个页面(page1)以后每8088都是一个PSF页IAM 索引分配位图如何发现一个特定对象的区间或页面？每个表/索引都至少有一个IAM，记录该对象拥有哪些区间一个IAM包含一个头、8个页面指针槽、一个表示文件内区间范围的位集合头表示该IAM所映射的头一个区间的地址8个页面指针槽表示在一个混合区间内哪些页面属于该对象IAM覆盖的范围与GAM相同，如果位为1，说明该区间被分配给该对象，如果位为0，说明该区间未被分配给

10、该对象如，说明第一、二个区间被分配给该对象一个对象每占据文件的4G空间，就需要一个IAM一个对象的IAM被随机安放在文件中对象的第一个IAM记录在sysindexes.firstIAM中对象的所有IAM构成一个双向链表DCM- 差异变化位图记录自从上次完全数据库备份之后，哪些区间发生了变化DCM位于文件的第7个页面(page6)DCM与GAM结构和表达范围相同每次进行完完全数据库备份，DCM所有位被置为0，以后如果区间的任何页面发生修改， DCM对应的位置为1当进行差异数据库备份时，将对应DCM中位为1的区间备份出去BCM 批量变化位图记录当发生批量更新操作(BULK INSERT, bcp,

11、 select into, create index等)时，哪些区间发生了变化， BCM对应的位置为1BCM位于文件的第8个页面(page7) BCM与GAM结构和表达范围相同每次进行完完全数据库备份获日志备份，BCM所有位被置为0当进行批量日志恢复时，将对应BCM中位为1的区间恢复数据库信息：sysdatabasesColumnInformationnameName of the database.dbidUnique database ID; can be reused when the database is dropped.sidSystem ID of the database cr

12、eator.modeLocking mode; used internally while a database is being created.statusBit mask that shows whether a database is read-only, off line, designated for use by a single user only, and so on. Some of the bits can be set by a database owner using the ALTER DATABASE command; others are set interna

13、lly. (The SQL Server documentation shows most of the possible bit-mask values.)status2Another bit mask-like status, with bits indicating additional database options.crdateFor user databases, the date when the database was created. For tempdb, this is the date and time that SQL Server was last starte

14、d. For other system databases, this date is not really useful. Depending on decisions made during installation, it could be the date Microsoft originally created the database prior to shipping the code, or it could be the date that you installed SQL Server.reservedReserved for future use.categoryAno

15、ther bit mask-like status. Contains information about whether the database is involved with replication.cmptlevelCompatibility level for the database. (Ill discuss this concept briefly at the end of the chapter.)Operating system path and name of the primary file.versionInternal version of SQL Server

16、 that was used to create the database查找数据表无聚集索引聚集索引非聚集索引没有聚集索引的表HeapExtent 160Extent 168Extent 176010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203ConFunkWhite.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.0102030405ConFunkWhiteDurkinLang.010203Smit

17、hOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203ConFunkWhite.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.0102030405DunnRandallOtaSlichterLaBrie.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203ConFunkWhite.010203SmithOtaJones.010

18、20304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203ConFunkWhite.010203SmithOtaJones.01020304AkhtarFunkSmithMartin.010203RuddWhiteBarr.010203GraffBaconKoch.ExtenteattleParisTokyoAtlanta.IAM160 1168 1176 0184 1Extent

19、Bit Mapidindid = 0First IAMsysindexes一般索引结构Page 140 - RootPage 141Page 145AkhtarGanioAkhtarMartinMartinSmithNode Pagesidindid = xrootsysindexesPage 100 Page 120 Page 130 AkhtarBarrConFunkFunk.23345678253413341534.MartinMartinOtaPhuaRudd.12347778587878786078.SmithSmithSmithWhiteWhite.1434577879782234

20、1634.Page 110 GanioHallJonesJonesJones.76788078243459782634.LeafPages聚集索引叶结点是数据本身在索引上码是唯一的，如果聚集索引不是唯一的索引，SQL Server 2000将添加在内部生成的值以使重复的键唯一聚集索引Clustered IndexPage 140 - RootPage 100 Page 120 Page 130 Page 141Page 145AkhtarBarrConFunkFunk.23345678253413341534.MartinMartinOtaPhuaRudd.123477785878787860

21、78.SmithSmithSmithWhiteWhite.14345778797822341634.AkhtarGanioAkhtarMartinMartinSmithPage 110 GanioHallJonesJonesJones.76788078243459782634.SELECT lastname, firstnameFROM memberWHERE lastname = OtaClustered IndexPage 140 - RootPage 100 Page 120 Page 130 Page 141Page 145AkhtarBarrConFunkFunk.233456782

22、53413341534.MartinMartinOtaPhuaRudd.12347778587878786078.SmithSmithSmithWhiteWhite.14345778797822341634.AkhtarGanioAkhtarMartinMartinSmithPage 110 GanioHallJonesJonesJones.76788078243459782634.MartinOta5878.Martinidindid = 1rootsysindexes非聚集索引叶结点包含索引码到数据行的标识标识可以是：RID (File #, Page #, Slot #)唯一的聚集索引码

23、堆上的非聚集索引HeapPage 707 Page 808Page 70901020304.AkhtarFunkSmithMatey.Page 704 Page 705 Page 706 010203.ConnFunkWhite.010203.RuddWhiteBarr.010203.SmithOtaJones.01020304.MartinPhuaJonesSmith.010203.GanioJonesHall. #4HeapPage 707 Page 808Page 70901020304.AkhtarFunkSmithMatey.Page 704 Page 705 Page 706 01

24、0203.ConnFunkWhite.010203.RuddWhiteBarr.010203.SmithOtaJones.01020304.MartinPhuaJonesSmith.010203.GanioJonesHall. #404.Matey02.Phua01.Rudd02.OtaSELECT lastname, firstnameFROM memberWHERE lastnameBETWEEN Masters AND Rudd NonclusteredIndexLeaf Level(Key Value)Page 41Page 51 Page 61 Page 71 AkhtarBarrC

25、onFunkFunk4:706:014:705:034:704:014:706:024:704:02SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01Leaf Level(Key Value)Page 41Page 51 Page 61 Page 71 AkhtarB

26、arrConFunkFunk4:706:014:705:034:704:014:706:024:704:02SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01Matey4:706:04Phua4:708:02Rudd4:705:01Ota4:707:02Non-Lea

27、fLevelPage 28Page 28Non-LeafLevelPage 12 - RootPage 37Akhtar.MartinMartinSmith.AkhtarGanio.Page 12 - RootPage 37Akhtar.MartinMartinSmith.AkhtarGanio.MartinMartinidindid = 2rootsysindexes聚集索引表上的非聚集索引Clustered IndexOn Last NameNonclusteredIndex onFirst NameNon-LeafLevelLeaf Level(ClusteredKey Value)Aa

28、ronDeannaAaron.JoseJoseNinaDeannaDonDougDaumHallHamptonAaronAdamAmieConBarrBaldwinJoseJudyMikeLugoKaethlerNashBarrAdamCoxDaumArletteDeannaKimKobaraLaBrieShaneLindaRyanNagataNashNixonSusanneMikeTobyBarrKimNagataOMeliaClustered IndexOn Last NameNonclusteredIndex onFirst NameNon-LeafLevelLeaf Level(Clu

29、steredKey Value)AaronDeannaAaron.JoseJoseNinaDeannaDonDougDaumHallHamptonAaronAdamAmieConBarrBaldwinJoseJudyMikeLugoKaethlerNashBarrAdamCoxDaumArletteDeannaKimKobaraLaBrieShaneLindaRyanNagataNashNixonSusanneMikeTobyBarrKimNagataOMeliaidindid = 2rootsysindexesMikeNashNagataNashMikeSELECT lastname, fi

30、rstname, phoneFROM memberWHERE firstname = Mike 表信息存储Sysobjectsname, object ID, ownerSysindexespointers to the storage space, tables size Indid0：表定义中没有主码或唯一性约束1：聚集索引2：非聚集索引Syscolumnscolumn name, datatype, length Sysreferences表信息存储sysconstraintsSELECTOBJECT_NAME(constid) Constraint name,constid Const

31、raint ID,CASE (status & 0 xF)WHEN 1 THEN Primary keyWHEN 2 THEN UniqueWHEN 3 THEN Foreign KeyWHEN 4 THEN CheckWHEN 5 THEN defaultend Constraint Type,CASE (status & 0 x30)WHEN 0 x10 THEN ColumnWHEN 0 x20 THEN Tableelse NAend levelfrom sysconstraintswhere id=OBJECT_ID(SC)表信息存储基本页结构所有页面包括页面头、页面体、页面槽页面头

32、96 字节(64字节已用,32字节保留)页面体存储数据行页面槽每行在页面内的偏移(每行两个字节)8,096字节基本页结构：页头字段含义Field What It ContainspageID and page number of this page in the databasenextPage and page number of the next page if this page is in a page chainprevPage and page number of the previous page if this page is in a page chainobjIDID of

33、 the object to which this page belongslsnLog sequence number (LSN) value used for changes and updates to this pageslotCntTotal number of slots (rows) used on this pagelevelLevel of this page in an index (always 0 for leaf pages)indexIdIndex ID of this page (always 0 for data pages)freeDataByte offse

34、t of the first free space on this pagepminlenNumber of bytes in fixed-length portion of rowsfreeCntNumber of free bytes on pagereservedCntNumber of bytes reserved by all transactionsxactreservedNumber of bytes reserved by the most recently started transactiontornBits1 bit per sector for detecting to

35、rn page writes flagBits2-byte bitmap that contains additional information about the page检视数据页ParameterDescriptiondbidID of the database containing the pagedbnameName of the database containing the page containing the pagepagenumPage number within the fileprintoptOptional print option; takes one of t

36、hese values: 0: Default; prints the buffer header and page header1: Prints the buffer header, page header, each row separately, and the row offset table2: Prints the buffer and page headers, the page as a whole, and the offset table3: Prints the buffer header, page header, each row separately, and t

37、he row offset table; each row is followed by each of its column values listed separatelyDBCC PAGE (dbid | dbname, , pagenum, printopt)检视数据页dbcc traceon(3604)godbcc page(5,1,88,1)goDBCC PAGE的输出划分为四部分 ：BUFFER, PAGE HEADER, DATA, OFFSET TABLE数据行结构定长元组的存储create table fixed(col1 char(5) not null,col2 int

38、 not null,col3 char(3) null,col4 char(6) not null,col5 float not null)insert into fixed values(ABCDE,123,NULL,CCCC,4567.8)ConvertPageNumsdbcc traceon(3604)dbcc page(chenlj,1,109,3)变长元组的存储create table variable(col1 char(3) not null,col2 varchar(250)not null,col3 varchar(5) null,col4 varchar(20)not nu

39、ll,col5 smallint null)insert into variablevalues(AAA,REPLICATE(X,250),NULL,ABC,123)ConvertPageNumsdbcc traceon(3604)dbcc page(chenlj,1,112,3)DBCC EXTENTINFO (Northwind,Orders,1)BLOB的存储BLOB的存储create table HasText(col1 char(3) not null,col2 textnot null,col3 varchar(5) null,col4 varchar(20)not null,co

40、l5 smallint null)insert into HasTextvalues(AAA,REPLICATE(X,250),NULL,ABC,123)ConvertPageNumsdbcc traceon(3604)dbcc page(chenlj,1,116,3)BLOB的存储将文本放入元组EXEC sp_tableoption HasText,text in row,500update HasTextset col2 = REPLICATE(Z,250)元组包含文本指针元组包含文本数据索引存储create table Clustered_Dupes(col1 char(5) not n

41、ull,col2 int not null,col3 char(3) null,col4 char(6) not null,col5 float not null)create clustered index Cl_dupes_col1 on Clustered_Dupes(col1)insert into Clustered_Dupes values(ABCDE,123,NULL,CCCC,4567.8)insert into Clustered_Dupes values(ABCDE,456,NULL,DDDD,4567.8)insert into Clustered_Dupes value

42、s(ABCDE,64,NULL,EEEE,4567.8)dbcc traceon(3604)dbcc page(chenlj,1,119,1)建立聚集索引时如果没有指定UNIQUE，对于重复码，生成4个字节的唯一标识符索引存储：索引行结构InformationMnemonicSizeStatus Bits ATagA Some of the relevant bits are:Bits 1 through 3: Taken as a 3-bit value, 0 indicates a primary record, 3 indicates an index record, and 5 ind

43、icates a ghost index record. Bit 4: Indicates that a NULL bitmap exists. Bit 5: Indicates that variable-length columns exist in the row. 1 byteFixed-length dataFdatapminlen - 1Number of columnsNcol2 bytesNULL bitmap (1 bit for each column in the table; a 1 indicates that the corresponding column is

44、NULL)NullbitsCeiling (Ncol / 8)Number of variable-length columns; only present if 0VarCount2 bytesVariable column offset array; only present if VarCount 0VarOffset2 * VarCountVariable-length data, if anyVarData聚集索引CREATE TABLE clustered_nodupes ( id int NOT NULL , str1 char (5) NOT NULL , str2 char

45、(600) NULL ) GOCREATE CLUSTERED INDEX idxCL ON clustered_nodupes(str1) GOSET NOCOUNT ONGODECLARE i intSET i = 1240WHILE i 13000 BEGIN INSERT INTO clustered_nodupes select i, cast(i AS char), cast(i AS char) SET i = i + 1 END GOSELECT first, root, id, indid FROM sysindexesWHERE id = object_id(cluster

46、ed_nodupes)聚集索引的根页面聚集索引的中间页面非聚集索引CREATE TABLE nc_heap_nodupes ( id int NOT NULL , str1 char (5) NOT NULL , str2 char (600) NULL ) GOCREATE UNIQUE INDEX idxNC_heap ON nc_heap_nodupes(str1) GOSET NOCOUNT ONGODECLARE i intSET i = 1240WHILE i 13000 BEGIN INSERT INTO nc_heap_nodupes select i, cast(i AS char), cast(i AS char) SET i = i + 1 END GOSELECT first, root, id, indid FROM sysindexesWHERE id = object_id(nc_heap_nodup