1、数据库高级复习提纲的参考答案复习提纲1. 你认为DBA的职责是什么？应该具备哪些技能？答：每个数据库的DBA职责和应具备的技能有所差别，下面拿oracle举例：职责：1.实时监控数据库告警日志这是必须进行的工作，并且应该根据不同的严重级别，发送不同级别的告警，及时了解数据库的变化与告警。2.实时监控数据库的重要统计信息实施监控对于数据库运行至关重要、能够表征数据库重要变化的统计信息，并且据此发送报警信息。那么应当监控哪些统计信息呢？大家可以思考一下，对于单机、RAC环境各不相同。3.部署自动的Statspack/AWR报告生成机制每天检查前日的AWR报告，熟悉数据库的运行状况，做到对于数据库了
2、如指掌4.每天至少优化和熟悉一个Top SQL根据AWR或Statspack报告,每天至少了解或熟悉一个Top SQL，能优化的要提出优化和调整建议5.部署完善的监控和数据采样系统DBA应该对数据库部署完善的监控系统，并对重要信息进行采样，能够实时或定期生成数据库重要指标的曲线图，展现数据库的运行趋势。6.全面深入的了解应用架构不了解应用的DBA是没有前途的DBA，对应用了解不深入的DBA算不上Expert，所以一定要深入了解应用。7.撰写系统架构、现状、调整备忘录根据对数据库的研究和了解，不断记录数据库的状况，撰写数据库架构、现状及调整备忘录，不放过任何可能的优化与改进的机会。具备的技能：a
3、. 首先，必须懂ORACLE数据库。 b. 其次，必须让别人也知道你懂ORACLE，最好是具有认证(OCA,OCP)。c. Oracle的好处是在许多平台都可以运行，因此就是你必须明白如何让它在Solaris， HP-UX， WINDOWS， Linux， AIX， Tru64等系统上跑起来，所以你也必须是系统管理员。d. 由于ORACLE是基于网络的，所以在管理的时候，免不了用到许多网络的东西，所以DBA最好也是网络管理员，最好有个CCNA的证书。e. 必须要有编程经验，编程语言最好是Java方面的。f. 除了管理之外，另一方面也要协助开发人员做PROJECT，不管是B/S还是C/S 那个S
4、(server)总跑不了，所以DBA还要懂软件工程的知识。g. 现在由于用ORACLE做网站的很多，因此，不管是APPLICATION SERVER用的是ASP，JSP，ColdFusion还是别的什么，出了问题DBA要具备技能来处理以上的问题。2. 为什么完整性约束的实施会极大地影响系统性能？你将在数据库应用系统中实现所有的约束吗？为什么？你对完整性约束的约束类型及其实施程度（全部实施、部分实施还是不实施）有什么样的建议？答：完整性约束用于增强数据的完整性，Oracle提供了5种完整性约束：1) Check 2) NOT NULL3) Unique 删除或禁用唯一性约束通常同时删除相关联的唯
5、一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误4) Primary 5) Foreign key3. 一般而言，DBMS提供什么样的方法来实现完整性约束？答：分为过程性约束和非过程性约束 提供非过程性约束用CREATE TABLE及ALTER TABLE实现非过程性约束 NOT NULL UNIQUE CHECK PRIMARY KEY FOREIGN KEY 在创建表的定义时提供语法支持：对于实体完整性提供的方法：创建表时定义主键,DBMS自动支持实体完整性定义表语句的基本语法CREATE TABLE 表名（(NOT NULLNULL UNIQUE ,
6、 NOT NULLNULL UNIQUE, PRIMARY KEY （， ）, FOREIGN KEY （， ）REFERENCES ）；对于参照完整性提供的方法：创建表时定义外键,DBMS自动支持参照完整性定义表语句的基本语法CREATE TABLE 表名（(NOT NULLNULL UNIQUE , NOT NULLNULL UNIQUE, PRIMARY KEY （， ）, FOREIGN KEY （， ）REFERENCES ）； 提供过程性约束用触发器实现过程性约束（由于不同的DBMS产品不同，因此没有统一的create trigger语句） 4. 为什么说视图是虚表？视图定义中WI
7、TH CHECK OPTION的作用是什么？视图在安全性方面有什么特别的作用？答：因为只是保存在数据库中的一段select 语句，表中数据的逻辑表示，并不真正存放数据，像基表一样，所以是虚表。加强了表的安全性，用户只需访问视图，无需直接访问表。With check option的作用：如果视图定义包括条件（譬如 WHERE 子句）并且其意图是确保任何引用该视图的 INSERT 或 UPDATE 语句都应用 WHERE 子句，则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件，则返
8、回 SQL 错误。5. 视图的更新是否会破坏完整性约束？答：会。列子集视图可能破外参照完整性。行子集视图可能破坏实体完整性。 6. 为什么数据库系统中的安全性问题非常突出？答：数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄漏、更改或者是破坏。计算机系统都有这个问题，在数据库系统中有大量数据集中存放，为许多用户所共享，使得安全问题变得非常突出。7. 数据库系统常采用哪些安全措施？这些安全措施用于解决怎样安全隐患或威胁？答：1) 用户标识与认证用于解决：防止非法用户侵入系统2) 存取控制用于解决:防止非授权的对模式对象的存取。防止非授权的数据库存取。3) 审计用于解决：记录非法操作的
9、人，时间和内容等。4) 数据加密用于解决：防止数据在存储和传输的过程中失密。5) 统计数据库用于解决：查出隐蔽的信息通道6) 角色用于解决：同存取控制。8. 在安全性控制方面，ORACLE、DB2与SQL SERVER分别提供哪些安全管理和控制机制？它们各有何特点？答： Oracle利用下列机制管理数据库的安全性。1) 数据库用户和模式2) 权限3) 角色4) 存储设置和空间份额5) 资源限制6) 审计 SQL Server利用一下机制：1) 授权2) 用户3) 角色4) 许可5) 使用视图和存储过程增强安全性 DB2使用一下机制：1) 认证2) 权限和特权3) 使用视图控制对对象的访问4)
10、审计特点：ORACLE与DB2 UDB都支持X/Open SQL标准的Grant和Revoke语句的语法,只是在CASCADE|RESTRICT子句等细节方面不同ORACLE 和 DB2 UDB 都没有该必选的CASCADE|RESTRICT子句。ORACLE中语法的相应位置是一个可选的CASCADE CONSTRAINTS子句，这将使系统只删除与被撤消的REFERENCES权限有关的参照完整性约束。DB2 UDB中没有语法控制这种动作，当一个权限被撤消时，两个产品中与其有关的视图都缺省地变成无效的。ORACLE与DB2 UDB都有很多附加权限ORACLE的安全性特征ORACLE支持角色的概念
11、 CONNECT 允许进入数据 RESOURCE 允许创建数 DBA 除拥有CONNECT和表的数据作操纵,并具有限自主访问控制 通过角色授权 通过正常方式授权审计 AUDIT语句 NOAUDIT语句DB2 UDB的安全性特征DB2为用户赋予不同级别的权限(authorities)级和特权或权力(Privileges)数据库的4级权限 SYSADM 系统管理 SYSCTRL 系统控制 SYSMAINT 系统维护 DBADM 数据库管理数据库特权 只有具有系统管理或数据库管理权限的用户才可以向其他用户授予或收回特权SQL Server 2000中的安全性特征（角色）： 1）固定的服务器角色系统预
12、先定义了一些服务器固有各自具有某种或某些操作SQL Se限。用户不能删除也不能创建新色。 2）固定的数据库角色系统为每一个数据库预先定义数用户不能删除数据库固有角色，体的数据库中再创建新的数据库安 用户定义的数据库角色9. 在ORACLE、DB2与SQL SERVER中，数据字典（也就是所说的数据库中的元数据）被称为什么？包括哪些内容？主要作用是什么？以什么格式存放？以什么方式或命令查看？可以直接修改吗？答： oracle中就是数据字典，包括：1) 所有oracle用户的信息2) 表空间和数据文件的信息3) 权限及角色的信息，如那个用户拥有那些权限4) 模式对象的信息（如表，视图，过程，函数，
13、包及触发器等）5) 完整性约束的信息6) 有关数据库中对象的空间分布及当前使用情况7) 审计信息（如谁存取或修改过对象）8) 其他一般的数据库信息。以基表和视图的形式存放，只有oracle能读写这些表，而对于一些视图对于oracle所有的用户都是可以存取的，而其他的一些视图只能是管理员（SYS）才能存取的。不能直接修改，DBMS帮我们自动修改。主要作用如下：1) Oracle 通过存取数据字典以便获得有关用户模式对象以及存储结构等信息。2) 当执行了DDL语句之后，oracle便自动修改数据字典。3) 任何用户能够以只度读的形式获得数据库信息。通过select语句查看即可，只有三种前缀视图：D
14、BA_ USER_ ALL_例如查询用户：select username from dba_users;(只有DBA才可以做这件事情。) DB2中是系统目录 SQL SERVER中还是数据字典。10. 在创建数据库和表时，你指定存储设备和所需的存储空间了吗？ORACLE是如何进行磁盘资源分配的？为了提高系统性能，你能在物理结构设计方面做哪些努力？答： Oracle的磁盘资源分配：ORACLE在逻辑上将保存的数据划分为一个个小单元来进行存储和维护,高一级的逻辑存储结构都是由基本的小单元构成1) 块 块是ORACLE用来管理存储空间的最基本单元; 块是ORACLE执行I/O操作的基本单位; 一个O
15、RACLE块由一个或多个操作系统块组成; ORACLE 块的大小在数据库创建时决定,以后不能再改;2) 区 区由一系列连续的块组成; 块是ORACLE进行存储空间的分配和回收的基本单位;3) 段 段由多个区组成,这些区可以是连续的,也可以是不连续的; 一般情况下,一个对象(数据库中保存有数据的对象,如表、索引等)只拥有一个段； 不同类型的数据库对象拥有不同类型的段；表所对应的段称为数据段，索引所对应的段称为索引段；4) 表空间 数据库由若干个表空间组成；在创建数据库时会自动创建一个默认的SYSTEM表空间； 通过使用表空间，ORACLE将所有相关的（属于某个应用的）逻辑结构和对象组合在一起；
16、为了提高系统的性能，在物理结构设计方面可以：数据库的数据最终是存储在物理磁盘上的，对数据进行访问就是对这些物理磁盘进行读写，因此对于这些物理存储的优化是系统优化的一个重要部分。对于物理存储结构优化，主要是合理地分配逻辑结构的物理存储地址，这样虽不能减少对物理存储的读写次数，但却可以使这些读写尽量并行，减少磁盘读写竞争，从而提高效率，也可以通过对物理存储进行精密的计算减少不必要的物理存储结构扩充，从而提高系统利用率。1、磁盘读写并行优化:对于数据库的物理读写，Oracle系统本身会进行尽可能的并行优化，例如在一个最简单的表检索操作中，如果表结构和检索域上的索引不在一个物理结构上，那么在检索的过程
17、中，对索引的检索和对表的检索就是并行进行的。2、操作并行优化:操作并行的优化是基于操作语句的统计结果，首先是统计各个表的访问频率，表之间的连接频率，根据这些数据按如下原则分配表空间和物理磁盘，减少系统进程和用户进程的磁盘I/O竞争;把需要连接的表格在表空间/物理磁盘上分开;把高频访问的表格在表空间/物理磁盘上分开;把经常需要进行检索的表格的表结构和索引在表空间/物理磁盘上分开。3、减少存储结构扩展:如果应用系统的数据库比较脆弱，并在不断地增长或缩小，这样的系统在非动态变化周期内效率合理，但是当在动态变化周期内的时候，性能却很差，这是由于Oracle的动态扩展造成的。在动态扩张的过程中，Orac
18、le必须根据存储的要求，在创建行、行变化获取缺省值时，扩展和分配新的存储空间，而且表格的扩展往往并不是事情的终结，还可能导致数据文件、表空间的增长，这些扩展会导致在线系统反应缓慢。对于这样的系统，最好的办法就是在建立的时候预先分配足够的大小和合适的增长幅度。在一个对象建立的时候要根据应用充分地计算他们的大小，然后再根据这些数据来定义对象Initial、Next和Minextents的值，使数据库在物理存储上和动态增长次数上达到一个比较好的平衡点，使这些对象既不经常发生增长，也不过多地占用数据库。11. 在ORACLE中创建索引时，你使用了CREATE INDEX的哪些子句？如何使用这些子句创建
19、有效的索引以提高特定查询的效率？如何使用这些子句创建有效的索引以降低更新代价？答：1) 使用下面的子句：CREATE UNIQUE|BITMAP INDEX Schema. indexname ONSchema.tablename(columnname ASC|DESC, columnname ASC|DESC)TABLESPACE tblspacenameSTORAGE (INITIAL n K|M NEXT n K|M MINEXTENTS n MAXEXTENTS n PCTINCREASE n)PCTFREE nNOSORT2) 提高特定查询的效率：3) 降低更新代价：12. 为什么目
20、前的商用数据库系统都采用B+-树索引？答：1) 所有的叶子节点都具有相同的深度，因此，检索任何数据都有相同的速度2) B+树能够自动保持平衡3) 对于精确查询和范围查询，B+树都能提高很好的性能。4) B+树不影响插入、删除、更新的效率。5) 无论是大表还是小表，B+都具有相同的效率。13. 什么是聚簇索引和位图索引？它们的应用特点是什么？答： 位图索引位图索引的思想：有N行的表中，每一行都有一个原始编号，依次是0，1，2，,N-1。连续编号的行，无论在什么情况下，在磁盘上都是物理连续的，而且一定有一个函数能进行原始编号到ROWID的转换。而位图是一个有N位的序列（每一位的值是0或者1，8位是
21、一个字节），位图能表示任何一个ROWID的列表L：如果原始编号为k的ROWID值在列表L中，那么N位位图的第k位被设置为1，否则就被设置为0。每一个位图都代表了B树中一个键值所对应的排序了的ROWID列表。应用特点：位图索引主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等), 聚簇索引聚簇索引(clustered index) 所引用的行和键值顺序一样的索引称为聚簇索引。应用特点：当所需要的行彼此很靠近的时候，很多需要读取的行可能位于同一个页面，这样，可以节省处理I/O操作的时间，因此查询可以更有效地执行。14. 查询优化的最终目标是什么？答：查询优化的最终目的是：提高查
22、询效率，缩短查询请求的响应时间（查询请求的响应时间 = 优化时间+最佳的查询执行计划的执行时间）15. 一旦发现某个查询的效率很低，如何分析低效的原因？如何改进效率？答：16. 动态SQL与静态SQL的查询优化时机有何区别？如何设置优化器模式？答：17. ORACLE支持哪些优化器模式？这些优化器模式具有什么特点？适合哪些应用环境？答：四种优化器模式 Choose：是ORACLE默认的优化器模式，它通常根据统计数据的存在与否来确定调用哪一个优化器。如果统计数据不存在，则使用rule模式；否则，根据索引存在与否及其他参数来确定使用first_rows还是all_rows模式 Rule：忽略CBO
23、和统计数据，完全基于数据字典信息生成查询执行计划 First_rows：是一个基于成本的优化器模式，它将以最快的速度返回记录，但会造成总体查询速度的下降或者消耗更多的资源。它倾向于使用索引扫描而非全表扫描 All_rows：是一个基于成本的优化器模式，它确保总体查询时间最短，但可能在收到第一条记录的操作上花费更长的时间。它倾向于使用并行全表扫描而非索引扫描18. 基于代价的查询优化与启发式查询优化的区别与优劣；答：基于代价的查询优化：获取所有执行计划(逻辑和物理)相关的信息，通过对这些信息作计算分析，最后得出一个代价最小的执行计划作为最终的执行计划。启发式查询优化: 启发式优化建立在两段优化的
24、基础之上，但不同的是： 在逻辑优化部分，它利用“先选择、投影，后连接”等启发式规则和若干代数表达式的等价变换规则对语法树的操作的次序和组合做一些变化和调整，期望得到的关系代数表达式的代价较小，虽然得到的关系代数表达式不一定是所有等价表达式中执行时间最小的，但逻辑优化的时间一定比两段优化方法的逻辑优化的时间少。 在物理优化部分，根据关系操作本身的特性（如，操作中涉及的条件、操作结果所含的元组数）以及已存在的访问路径，根据一些常用的优化存取路径的规则选择合理的物理操作，达到优化的目的。同样，得到的QEP不一定是最佳的，但优化的时间一定比两段优化方法的物理优化的时间少。19. 事务的并发可能带来哪四
25、种数据不一致性？答： 并发带来的数据不一致性1) 丢失修改（Lost Update）2) 不可重复读（Non-Repeatable Read）3) 读“脏”数据（Dirty Read）4) 幻像（Phantom）20. 在并发控制方面，ORACLE、DB2与SQL SERVER支持哪些隔离级别？每种隔离级别可以防止哪些数据不一致性？答：隔离级别允许的数据不一致：隔离级别脏读不可重复读幻像未提交读是是是提交读否是是可重复读否否是可串行读否否否 SQL Server支持的隔离级别：1) 未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）2) 提交读3) 可重复读4) 可串行读（事务隔离的
26、最高级别，事务之间完全隔离）。 DB2支持的隔离级别：1) 未提交读(UR, Uncommitted read)也称为”脏读”,是DB2支持的最低级别的隔离级2) 游标稳定性(默认级别)(CS, Cursor Stability)锁定事务运行期间游标所在的任何行,对该行的锁定将保持到取出下一行或整个事务终止.该级别保证读取的数据是一致的(不会读未提交的数据)3）读稳定性(RS, Read Stability)锁定作为结果的那部分行.同一事务中多次执行同一查询时,返回的结果相同,并可能得到额外的幻像行4）可重复读(RR, Repeatable Read)锁定事务中引用的所有行(表锁),是最高级别
27、的隔离级别,不会出现幻像行 Oracle支持的隔离级别1) Read Committed这是ORACLE的默认级别，每个事务所执行的查询只能获取在该查询（不是该事务）开始之前已经提交的数据2) Serializable每个事务只能看到在该事务开始之前已经提交的数据，并且可以看到该事务自身执行INSERT、UPDATE和DELETE操作后的结果。3) Read-Only是SQL92中没有定义的模式，只读事务只能看到在该事务开始之前已经提交的数据，并且不能够在事务中对数据进行INSERT、UPDATE和DELETE等操作21. 故障的种类及对数据库的破坏程度？答： 故障的种类：1） 事务故障： 有
28、的是可以通过事务程序本身发现的，有的是非预期的，不能由事务程序处理的故障。数据库没有被破坏，但数据可能不正确。2）系统故障：造成系统停止运转的任何事件，使得系统要重新启动。可能一些未完成的事务的结果送入了物理数据库，而一些已完成的事务的结果却没有完全写回物理数据库。数据库没有被破坏，但数据可能不正确。3）介质故障：外存故障。破坏数据库。4）计算机病毒，破坏数据库。22. 检查点的作用是什么？答：检查点将最小化必须REDO的事务数量。避免： 要搜索整个日志 不必要的REDO处理23. 为什么在进行系统故障恢复时，既需要做UNDO操作，又需要做REDO操作？在进行介质故障恢复时，也需要做UNDO和
29、REDO操作吗？为什么？答：系统故障会导致的数据库不一致（1）未完成的事务对DB产生影响，这需要UNDO操作.（2）已完成的事务在缓冲区的内容未写入DB，这需哟REDO操作。需要，在归档日志模式下，在介质发生故障之后，应使用数据库备份，重构数据库，将数据库复原到备份操作执行时刻的状态，同时也应使用日志文件，重新执行已提交事务，回滚未完成事务。 24. 什么是数据库镜像？它的作用是什么？答：数据库镜像：根据DBA的要求，自动把整个数据库或其中的关键数据复制到另一磁盘上。每当主数据库更新时，DBMS自动把更新后的数据复制过去，即DBMS自动保证镜像数据与主数据的一致。作用：一旦出现介质故障，可由镜像磁盘继续提供使用，同时DBMS自动利用镜像磁盘数据进行数据库恢复，不需要关闭系统和重装数据库副本。在没有出现故障时，数据库镜像还可以用于并发操作。
