数据库概述及数据库的物理设计.ppt

第二讲第二讲 SQL Server 2000数数据库概述及数据库据库概述及数据库的物理设计的物理设计一、一、SQL Server 概述概述nSQL Server 数据库数据库nSQL Server 安全性安全性n使用使用 SQL Servern数据库的类型数据库的类型n数据库对象数据库对象nSQL Server 对象的引用对象的引用n系统表系统表n元数据检索元数据检索一、一、SQL Server 概述概述1、数据库的类型、数据库的类型系统数据库系统数据库用户数据库用户数据库mastermastermodelmodeltempdbtempdbmsdbmsdbpubspubsNorthwindNorthwindUser1User1distributiondistributionEmpNumEmpNumLastNameLastNameFirstNameFirstNameExtensionExtensionCtryCodeCtryCode101911019210202LabrieLabrieMartinAngelaEvaJosex19891x19433x21467SPFRFRLastModLastMod\HR\KarlD\HR\KarlD\HR\AmyL聚集索引聚集索引聚集索引聚集索引 AndersonAndersonBarr...2、数据库对象、数据库对象integerlongstringvarchar(20)char(6)char(2)longstring触发器触发器EmployeePhoneViewSELECT lastname, firstname, extensionFROM employee存储过程存储过程UpdatePhone主键主键外键外键检查检查x#####数据库对象概述数据库对象概述n数据库数据库描述怎样使用数据库表示、管理和访问数据。

描述怎样使用数据库表示、管理和访问数据n表表描述怎样使用表存储数据行和定义多个表之间的关系描述怎样使用表存储数据行和定义多个表之间的关系n索引索引描述怎样使用索引提高访问表中数据的速度描述怎样使用索引提高访问表中数据的速度n视图视图描述各种视图及其作用，它们的作用在于为查看一个或多个表描述各种视图及其作用，它们的作用在于为查看一个或多个表中的数据提供变通方式中的数据提供变通方式n存储过程存储过程描述这些描述这些 Transact-SQL 程序是怎样将业务规则、任务和进程序是怎样将业务规则、任务和进程集中在服务器内的程集中在服务器内的n用触发器强制执行业务规则用触发器强制执行业务规则描述作为特殊类型存储过程的触发器的描述作为特殊类型存储过程的触发器的功能，只有修改表中的数据时才执行这类存储过程功能，只有修改表中的数据时才执行这类存储过程n全文索引全文索引描述全文索引是如何提高对存储在基于字符的列中数据（描述全文索引是如何提高对存储在基于字符的列中数据（如如 varchar 和和 text）的查询功能的的查询功能的SQL Server 对象的引用对象的引用n完全限定名完全限定名server.database.owner.objectn部分限定名部分限定名lServer 以本地服务器的当前实例为默认值lDatabase 以当前数据库为默认值 lOwner 以当前登录账户在该数据库中对应的用户名为默认值CREATE TABLE Northwind.dbo.OrderHistory...3、系统表、系统表n系统表存储与系统和数据库对象有关的信息（元系统表存储与系统和数据库对象有关的信息（元数据）数据）n数据库目录存储与特定的数据库有关的元数据数据库目录存储与特定的数据库有关的元数据n数据库目录存储与整个系统和其他所有数据库有数据库目录存储与整个系统和其他所有数据库有关的元数据关的元数据Master数据库中的系统表数据库中的系统表4、元数据检索、元数据检索n系统存储过程系统存储过程n系统函数和元数据函数系统函数和元数据函数n信息架构视图信息架构视图EXEC sp_help EXEC sp_help EEmployeesmployeesSELECT USER_NAME(10)SELECT USER_NAME(10)SELECT * FROM INFORMATION_SCHEMA.TABLESSELECT * FROM INFORMATION_SCHEMA.TABLES一、一、SQL Server 概述概述nSQL Server 数据库数据库nSQL Server 安全性安全性n使用使用 SQL Server2、、SQL Server 安全性安全性n登录验证登录验证n验证模式验证模式n数据库用户账户和用户角色数据库用户账户和用户角色n角色的类型角色的类型n权限验证权限验证登录验证登录验证验证验证SQL Server验证信任连接SQL Server验证账户名和密码或或SQL ServerWindows 2000 组或用户Windows 2000SQL Server登录账户数据库用户账户和角色数据库用户账户和角色SQL Server 将登录分配给用户账户和角色数据库用户数据库用户数据库角色数据库角色Windows 2000组用户SQL Server登录账户SQL Server 验证信任连接SQL Server验证账户名和密码SQL ServerWindows 2000或或角色类型角色类型n固定服务器角色固定服务器角色l服务器级的管理权限分组n固定数据库角色固定数据库角色l数据库级的管理权限分组n用户定义的数据库角色用户定义的数据库角色 l创建自己的数据库角色来为公司里的一组职员分配权限SQL Server验证权限权限验证权限验证 验证通过；执行命令验证没通过；返回 错误2 23 3SELECT * FROM Members数据库用户执行命令1 1一、一、SQL Server 概述概述nSQL Server 数据库数据库nSQL Server 安全性安全性n使用使用 SQL Server3、使用、使用 SQL Servern管理管理 SQL Server 数据库数据库 n实现实现 SQL Server 数据库数据库 n为为 SQL Server 选择应用程序体系结构选择应用程序体系结构 n用用 API 设计应用程序设计应用程序 管理管理 SQL Server 数据库数据库n常用管理任务常用管理任务nSQL Server 企业管理器企业管理器nSQL Server 管理工具和向导管理工具和向导nSQL Server 命令行提示符管理工具命令行提示符管理工具 nSQL Server 帮助和帮助和 SQL Server 联机丛书联机丛书 实现实现 SQL Server 数据库数据库n实现数据库实现数据库n创建数据库和数据库对象创建数据库和数据库对象n测试和调整应用程序和数据库测试和调整应用程序和数据库n计划调度计划调度因特网因特网因特网因特网N N 层层层层智能智能智能智能客户端（客户端（客户端（客户端（2 2 层）层）层）层）智能智能智能智能服务器（服务器（服务器（服务器（2 2 层）层）层）层）为为 SQL Server 选择应用程序体系结构选择应用程序体系结构表示层业务层数据层浏览器客户表示层业务层数据层表示层业务层数据层业务层数据层表示层多层应用模型多层应用模型用用 API 设计应用程序设计应用程序数据对象接口数据对象接口应用程序接口应用程序接口ADOADOOLE DBOLE DB其他数据源其他数据源其他数据源其他数据源关系型数据库关系型数据库关系型数据库关系型数据库ADO .NET 对象模型对象模型DataSetSQL Server .NET Data ProviderOLE DB .NET Data ProviderSQL Server 7.0（及后续版本）（及后续版本）OLE DB 数据源数据源（（SQL Server 6.5））实验实验 ：进入：进入SQL Server 熟悉其操作界面熟悉其操作界面二、数据库的物理设计二、数据库的物理设计n数据库在物理设备上的存储结构与存取方法数据库在物理设备上的存储结构与存取方法称为数据库的物理结构，它依赖于给定的计称为数据库的物理结构，它依赖于给定的计算机系统。

算机系统n为一个给定的逻辑数据模型选取一个最适合为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程，就是数据库的应用要求的物理结构的过程，就是数据库的物理设计物理设计n 数据库的物理设计通常分为两步：数据库的物理设计通常分为两步：n (1)确定数据库的物理结构，在关系数据确定数据库的物理结构，在关系数据库中主要指存取方法和存储结构；库中主要指存取方法和存储结构；n (2)对物理结构进行评价，评价的重点是对物理结构进行评价，评价的重点是时间和空间效率时间和空间效率 数据库物理设计的步骤数据库物理设计的步骤 数据库物理设计数据库物理设计确定数确定数据库的据库的物理结物理结构构评价数据评价数据库的物理库的物理结构结构逻辑结逻辑结构设计构设计数据库数据库实施实施物理物理模型模型逻辑逻辑模型模型评价的重点是时间和空间效率满足原设计要求重新设计或修改修改数据模型 数据库的物理设计数据库的物理设计1 数据库的物理设计的内容和方法数据库的物理设计的内容和方法2 关系模式存取方法选择关系模式存取方法选择3 确定数据库的存储结构确定数据库的存储结构4 评价物理结构评价物理结构1 数据库的物理设计的内容和方法数据库的物理设计的内容和方法n设计物理数据库结构的准备工作设计物理数据库结构的准备工作l 1. 充分了解应用环境，详细分析要运行的事务，以获得选择物理数据库设计所需参数l 2. 充分了解所用RDBMS的内部特征，特别是系统提供的存取方法和存储结构数据库的物理设计的内容和方法（续数据库的物理设计的内容和方法（续））n选择物理数据库设计所需参数选择物理数据库设计所需参数l 数据库查询事务查询的关系 查询条件所涉及的属性 连接条件所涉及的属性 查询的投影属性 数据库的物理设计的内容和方法（续数据库的物理设计的内容和方法（续））n选择物理数据库设计所需参数选择物理数据库设计所需参数(续续)l数据更新事务被更新的关系每个关系上的更新操作条件所涉及的属性修改操作要改变的属性值l 每个事务在各关系上运行的频率和性能要求数据库的物理设计的内容和方法（续数据库的物理设计的内容和方法（续））n关系数据库物理设计的内容关系数据库物理设计的内容l1. 为关系模式选择存取方法(建立存取路径)l2. 设计关系、索引等数据库文件的物理存储结构 数据库的物理设计数据库的物理设计1 数据库的物理设计的内容和方法数据库的物理设计的内容和方法2 关系模式存取方法选择关系模式存取方法选择3 确定数据库的存储结构确定数据库的存储结构4 评价物理结构评价物理结构2 关系模式存取方法选择关系模式存取方法选择n数据库系统是多用户共享的系统，对同一个关系数据库系统是多用户共享的系统，对同一个关系要建立多条存取路径才能满足多用户的多种应用要建立多条存取路径才能满足多用户的多种应用要求。

要求n物理设计的第一个任务就是要确定选择哪些存取物理设计的第一个任务就是要确定选择哪些存取方法，即建立哪些存取路径方法，即建立哪些存取路径关系模式存取方法选择（续）关系模式存取方法选择（续）nDBMS常用存取方法常用存取方法l索引方法，目前主要是B+树索引方法l聚簇（Cluster）方法lHASH方法一、索引存取方法的选择一、索引存取方法的选择n选择索引存取方法的主要内容选择索引存取方法的主要内容根据应用要求确定根据应用要求确定l 对哪些属性列建立索引l 对哪些属性列建立组合索引l 对哪些索引要设计为唯一索引索引存取方法的选择（续）索引存取方法的选择（续）n选择索引存取方法的一般规则选择索引存取方法的一般规则l如果一个(或一组)属性经常在查询条件中出现，则考虑在这个(或这组)属性上建立索引(或组合索引)l如果一个属性经常作为最大值和最小值等聚集函数的参数，则考虑在这个属性上建立索引l如果一个(或一组)属性经常在连接操作的连接条件中出现，则考虑在这个(或这组)属性上建立索引索引存取方法的选择（续）索引存取方法的选择（续）n关系上定义的索引数过多会带来较多的额关系上定义的索引数过多会带来较多的额外开销外开销l 维护索引的开销l 查找索引的开销二、聚簇存取方法的选择二、聚簇存取方法的选择n什么是聚簇（簇集）什么是聚簇（簇集）l为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在一个物理块内或物理上相邻的区域内称为聚簇l例， 设有一关系TEACHER(教师)， 其上建有YR(出生年份)的索引。

若要查询1948年出生的教师， 设1948年出生的教师有100人， 在极端情况下， 这100个教师所对应的元组分散在100个不同的物理块中n在在做做这这种种查查询询时时，，即即使使不不考考虑虑访访问问索索引引的的I/O次次数数，，访访问问数数据据也也得得要要100次次I/O，，因因为为每每访访问问一一个个物物理理块块需要一次需要一次I/O操作 n如如果果按按出出生生年年份份集集中中存存放放了了TEACHER元元组组，，则则每每取取一一个个物物理理块块，，就就可可以以获获得得多多个个合合乎乎查查询询条条件件的的元组，从而显著地减少访问磁盘的次数元组，从而显著地减少访问磁盘的次数n现现代代的的DBMS一一般般允允许许按按某某一一簇簇集集键键(Cluster Key)集集中中存存放放元元组组，， 这这种种簇簇集集键键可可以以是是复复合合的的 具具有有同同一一簇簇集集键键值值的的元元组组，， 尽尽可可能能放放在在同同一一物物理理块块中中 如如果果放放不不下下，， 可可以以向向预预留留的的空空白白区区发发展展，， 或或链链接接多多个个物物理理块块 簇簇集集后后的的元元组组好好像像葡葡萄萄一一样样按按串串存存放，放， 簇集之名由此而来。

簇集之名由此而来 建立建立聚簇聚簇索引索引 （复习）（复习）n聚簇索引聚簇索引l建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放也即聚簇索引的索引项顺序与表中元组的物理顺序一致例：CREATE CLUSTER INDEX Stusname ON Student(Sname)； 在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放 建立建立聚簇聚簇索引索引 （复习）（复习）l在一个基本表上最多只能建立一个聚簇索引l聚簇索引的用途：对于某些类型的查询，可以提高查询效率l聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作 聚簇存取方法的选择（续）聚簇存取方法的选择（续）n聚簇的用途聚簇的用途l1. 大大提高按聚簇属性进行查询的效率l2. 节省存储空间聚簇以后，聚簇码相同的元组集中在一起了，因而聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了聚簇存取方法的选择（续）聚簇存取方法的选择（续）n聚簇的局限性聚簇的局限性l1. 聚簇只能提高某些特定应用的性能l2. 建立与维护聚簇的开销相当大对已有关系建立聚簇，将导致关系中元组移动其物理存储位置，并使此关系上原有的索引无效，必须重建。

当一个元组的聚簇码改变时，该元组的存储位置也要做相应移动聚簇存取方法的选择（续）聚簇存取方法的选择（续）n聚簇的适用范围聚簇的适用范围l1. 既适用于单个关系独立聚簇，也适用于多个关系组合聚簇例：假设用户经常要按系别查询学生成绩单，这一查询涉及学生关系和选修关系的连接操作，即需要按学号连接这两个关系，为提高连接操作的效率，可以把具有相同学号值的学生元组和选修元组在物理上聚簇在一起这就相当于把多个关系按“预连接”的形式存放，从而大大提高连接操作的效率聚簇存取方法的选择（续）聚簇存取方法的选择（续）l2. 当通过聚簇码进行访问或连接是该关系的主要应用，与聚簇码无关的其他访问很少或者是次要的时，可以使用聚簇尤其当SQL语句中包含有与聚簇码有关的ORDER BY，GROUP BY，UNION，DISTINCT等子句或短语时，使用聚簇特别有利，可以省去对结果集的排序操作聚簇存取方法的选择（续）聚簇存取方法的选择（续）n选择聚簇存取方法选择聚簇存取方法l1. 设计候选聚簇对经常在一起进行连接操作的关系可以建立组合聚簇；如果一个关系的一组属性经常出现在相等比较条件中，则该单个关系可建立聚簇；如果一个关系的一个(或一组)属性上的值重复率很高，则此单个关系可建立聚簇。

即对应每个聚簇码值的平均元组数不太少太少了，聚簇的效果不明显聚簇存取方法的选择（续）聚簇存取方法的选择（续）l2. 检查候选聚簇中的关系，取消其中不必要的关系从独立聚簇中删除经常进行全表扫描的关系；从独立/组合聚簇中删除更新操作远多于查询操作的关系；从独立/组合聚簇中删除重复出现的关系当一个关系同时加入多个聚簇时，必须从这多个聚簇方案(包括不建立聚簇)中选择一个较优的，即在这个聚簇上运行各种事务的总代价最小三、三、HASH存取方法的选择存取方法的选择n哈稀（哈稀（HASH））文件又称为散列文件，文件又称为散列文件， 是一种支持快速存取的文件存储方法是一种支持快速存取的文件存储方法 如如果用该方式存储一个文件，果用该方式存储一个文件， 必须指定文件的一个（或一组）域为查询的关键字，必须指定文件的一个（或一组）域为查询的关键字， 该域常称为该域常称为HASH域，域， 然后定义一个然后定义一个HASH域上的函数，域上的函数， 即即HASH函数，函数， 以此函数的以此函数的值作为记录查询的地址值作为记录查询的地址n选择选择HASH存取方法的规则存取方法的规则l当一个关系满足下列两个条件时，可以选择HASH存取方法该关系的属性主要出现在等值连接条件中或主要出现在相等比较选择条件中该关系的大小可预知，而且不变；或 该关系的大小动态改变，但所选用的DBMS提供了动态HASH存取方法。

 数据库的物理设计数据库的物理设计1 数据库的物理设计的内容和方法数据库的物理设计的内容和方法2 关系模式存取方法选择关系模式存取方法选择3 确定数据库的存储结构确定数据库的存储结构4 评价物理结构评价物理结构3 确定数据库的存储结构确定数据库的存储结构n确定数据库物理结构的内容确定数据库物理结构的内容l1. 确定数据的存放位置和存储结构 关系 索引 聚簇 日志 备份l2. 确定系统配置1. 确定数据的存放位置确定数据的存放位置n影响数据存放位置和存储结构的因素影响数据存放位置和存储结构的因素l硬件环境l应用需求存取时间存储空间利用率维护代价 这三个方面常常是相互矛盾的 例：消除一切冗余数据虽能够节约存储空间和减少维护代价，但往往会导致检索代价的增加 必须进行权衡，选择一个折中方案确定数据的存放位置（续）确定数据的存放位置（续）n基本原则基本原则l根据应用情况将易变部分与稳定部分存取频率较高部分与存取频率较低部分 分开存放，以提高系统性能确定数据的存放位置（续）确定数据的存放位置（续）例：数据库数据备份、日志文件备份等由于只在故障恢复时才使用，而且数据量很大，可以考虑存放在磁带上。

如果计算机有多个磁盘，可以考虑将表和索引分别放在不同的磁盘上，在查询时，由于两个磁盘驱动器分别在工作，因而可以保证物理读写速度比较快确定数据的存放位置（续）确定数据的存放位置（续）例（续）：可以将比较大的表分别放在两个磁盘上，以加快存取速度，这在多用户环境下特别有效可以将日志文件与数据库对象（表、索引等）放在不同的磁盘以改进系统的性能2. 确定系统配置确定系统配置nDBMS产品一般都提供了一些存储分配参数产品一般都提供了一些存储分配参数l 同时使用数据库的用户数l 同时打开的数据库对象数l 使用的缓冲区长度、个数l 时间片大小l 数据库的大小l 装填因子l 锁的数目l 等等确定系统配置（续）确定系统配置（续）n系统都为这些变量赋予了合理的缺省值系统都为这些变量赋予了合理的缺省值但是这些值不一定适合每一种应用环境，但是这些值不一定适合每一种应用环境，在进行物理设计时，需要根据应用环境确在进行物理设计时，需要根据应用环境确定这些参数值，以使系统性能最优定这些参数值，以使系统性能最优 n在物理设计时对系统配置变量的调整只是在物理设计时对系统配置变量的调整只是初步的，在系统运行时还要根据系统实际初步的，在系统运行时还要根据系统实际运行情况做进一步的调整，以期切实改进运行情况做进一步的调整，以期切实改进系统性能。

系统性能 数据库的物理设计数据库的物理设计1 数据库的物理设计的内容和方法数据库的物理设计的内容和方法2 关系模式存取方法选择关系模式存取方法选择3 确定数据库的存储结构确定数据库的存储结构4 评价物理结构评价物理结构4 评价物理结构评价物理结构n评价内容评价内容l对数据库物理设计过程中产生的多种方案进行细致的评价，从中选择一个较优的方案作为数据库的物理结构4 评价物理结构评价物理结构n评价方法评价方法l定量估算各种方案 存储空间 存取时间 维护代价l对估算结果进行权衡、比较，选择出一个较优的合理的物理结构l如果该结构不符合用户需求，则需要修改设计本章结束本章结束。