本文目录
- 谁知道数据库优化设计方案有哪些
- access数据库 车票查询 设计实例
- sql数据库XX系统的设计实例
- 数据库设计实例
谁知道数据库优化设计方案有哪些
本文首先讨论了基于第三范式的数据库表的基本设计，着重论述了建立主键和索引的策略和方案，然后从数据库表的扩展设计和库表对象的放置等角度概述了数据库管理系统的优化方案。 关键词： 优化（Optimizing） 第三范式（3NF） 冗余数据（Redundant Data） 索引（Index） 数据分割（Data Partitioning） 对象放置（Object Placement） 1 引言 数据库优化的目标无非是避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争。为了便于读者阅读和理解，笔者参阅了Sybase、Informix和Oracle等大型数据库系统参考资料，基于多年的工程实践经验，从基本表设计、扩展设计和数据库表对象放置等角度进行讨论，着重讨论了如何避免磁盘I/O瓶颈和减少资源竞争，相信读者会一目了然。 2 基于第三范式的基本表设计 在基于表驱动的信息管理系统（MIS）中，基本表的设计规范是第三范式（3NF）。第三范式的基本特征是非主键属性只依赖于主键属性。基于第三范式的数据库表设计具有很多优点：一是消除了冗余数据，节省了磁盘存储空间；二是有良好的数据完整性限制，即基于主外键的参照完整限制和基于主键的实体完整性限制，这使得数据容易维护，也容易移植和更新；三是数据的可逆性好，在做连接（Join）查询或者合并表时不遗漏、也不重复；四是因消除了冗余数据（冗余列），在查询（Select）时每个数据页存的数据行就多，这样就有效地减少了逻辑I/O，每个Cash存的页面就多，也减少物理I/O；五是对大多数事务(Transaction)而言，运行性能好；六是物理设计(Physical Design)的机动性较大，能满足日益增长的用户需求。 在基本表设计中，表的主键、外键、索引设计占有非常重要的地位，但系统设计人员往往只注重于满足用户要求，而没有从系统优化的高度来认识和重视它们。实际上，它们与系统的运行性能密切相关。现在从系统数据库优化角度讨论这些基本概念及其重要意义： （1）主键(Primary Key)：主键被用于复杂的SQL语句时，频繁地在数据访问中被用到。一个表只有一个主键。主键应该有固定值（不能为Null或缺省值，要有相对稳定性），不含代码信息，易访问。把常用（众所周知）的列作为主键才有意义。短主键最佳（小于25bytes），主键的长短影响索引的大小，索引的大小影响索引页的大小，从而影响磁盘I/O。主键分为自然主键和人为主键。自然主键由实体的属性构成，自然主键可以是复合性的，在形成复合主键时，主键列不能太多，复合主键使得Join*作复杂化、也增加了外键表的大小。人为主键是，在没有合适的自然属性键、或自然属性复杂或灵敏度高时，人为形成的。人为主键一般是整型值（满足最小化要求），没有实际意义，也略微增加了表的大小；但减少了把它作为外键的表的大小。 （2）外键（Foreign Key）：外键的作用是建立关系型数据库中表之间的关系（参照完整性），主键只能从独立的实体迁移到非独立的实体，成为后者的一个属性，被称为外键。 （3）索引(Index)：利用索引优化系统性能是显而易见的，对所有常用于查询中的Where子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样减少数据存取时间；利用索引可以优化或排除耗时的分类*作；把数据分散到不同的页面上，就分散了插入的数据；主键自动建立了唯一索引，因此唯一索引也能确保数据的唯一性（即实体完整性）；索引码越小，定位就越直接；新建的索引效能最好，因此定期更新索引非常必要。索引也有代价：有空间开销，建立它也要花费时间，在进行Insert、Delete和Update*作时，也有维护代价。索引有两种：聚族索引和非聚族索引。一个表只能有一个聚族索引，可有多个非聚族索引。使用聚族索引查询数据要比使用非聚族索引快。在建索引前，应利用数据库系统函数估算索引的大小。 ① 聚族索引（Clustered Index）：聚族索引的数据页按物理有序储存，占用空间小。选择策略是，被用于Where子句的列：包括范围查询、模糊查询或高度重复的列（连续磁盘扫描）；被用于连接Join*作的列；被用于Order by和Group by子句的列。聚族索引不利于插入*作，另外没有必要用主键建聚族索引。 ② 非聚族索引（Nonclustered Index）：与聚族索引相比，占用空间大，而且效率低。选择策略是，被用于Where子句的列：包括范围查询、模糊查询（在没有聚族索引时）、主键或外键列、点（指针类）或小范围（返回的结果域小于整表数据的20%）查询；被用于连接Join*作的列、主键列（范围查询）；被用于Order by和Group by子句的列；需要被覆盖的列。对只读表建多个非聚族索引有利。索引也有其弊端，一是创建索引要耗费时间，二是索引要占有大量磁盘空间，三是增加了维护代价（在修改带索引的数据列时索引会减缓修改速度）。那么，在哪种情况下不建索引呢？对于小表（数据小于5页）、小到中表（不直接访问单行数据或结果集不用排序）、单值域（返回值密集）、索引列值太长（大于20bitys）、容易变化的列、高度重复的列、Null值列，对没有被用于Where子语句和Join查询的列都不能建索引。另外，对主要用于数据录入的，尽可能少建索引。当然，也要防止建立无效索引，当Where语句中多于5个条件时，维护索引的开销大于索引的效益，这时，建立临时表存储有关数据更有效。 批量导入数据时的注意事项：在实际应用中，大批量的计算（如电信话单计费）用C语言程序做，这种基于主外键关系数据计算而得的批量数据（文本文件），可利用系统的自身功能函数（如Sybase的BCP命令）快速批量导入，在导入数据库表时，可先删除相应库表的索引，这有利于加快导入速度，减少导入时间。在导入后再重建索引以便优化查询。 （4）锁：锁是并行处理的重要机制，能保持数据并发的一致性，即按事务进行处理；系统利用锁，保证数据完整性。因此，我们避免不了死锁，但在设计时可以充分考虑如何避免长事务，减少排它锁时间，减少在事务中与用户的交互，杜绝让用户控制事务的长短；要避免批量数据同时执行，尤其是耗时并用到相同的数据表。锁的征用：一个表同时只能有一个排它锁，一个用户用时，其它用户在等待。若用户数增加，则Server的性能下降，出现“假死”现象。如何避免死锁呢？从页级锁到行级锁，减少了锁征用；给小表增加无效记录，从页级锁到行级锁没有影响，若在同一页内竞争有影响，可选择合适的聚族索引把数据分配到不同的页面；创建冗余表；保持事务简短；同一批处理应该没有网络交互。 （5）查询优化规则：在访问数据库表的数据(Access Data)时，要尽可能避免排序（Sort）、连接(Join)和相关子查询*作。经验告诉我们，在优化查询时，必须做到： ① 尽可能少的行； ② 避免排序或为尽可能少的行排序，若要做大量数据排序，最好将相关数据放在临时表中*作；用简单的键（列）排序，如整型或短字符串排序； ③ 避免表内的相关子查询； ④ 避免在Where子句中使用复杂的表达式或非起始的子字符串、用长字符串连接； ⑤ 在Where子句中多使用“与”（And）连接，少使用“或”(Or)连接； ⑥ 利用临时数据库。在查询多表、有多个连接、查询复杂、数据要过滤时，可以建临时表（索引）以减少I/O。但缺点是增加了空间开销。 除非每个列都有索引支持，否则在有连接的查询时分别找出两个动态索引，放在工作表中重新排序。 3 基本表扩展设计 基于第三范式设计的库表虽然有其优越性（见本文第一部分），然而在实际应用中有时不利于系统运行性能的优化：如需要部分数据时而要扫描整表，许多过程同时竞争同一数据，反复用相同行计算相同的结果，过程从多表获取数据时引发大量的连接*作，当数据来源于多表时的连接*作；这都消耗了磁盘I/O和CPU时间。 尤其在遇到下列情形时，我们要对基本表进行扩展设计：许多过程要频繁访问一个表、子集数据访问、重复计算和冗余数据，有时用户要求一些过程优先或低的响应时间。 如何避免这些不利因素呢？根据访问的频繁程度对相关表进行分割处理、存储冗余数据、存储衍生列、合并相关表处理，这些都是克服这些不利因素和优化系统运行的有效途径。 3.1 分割表或储存冗余数据 分割表分为水平分割表和垂直分割表两种。分割表增加了维护数据完整性的代价。 水平分割表：一种是当多个过程频繁访问数据表的不同行时，水平分割表，并消除新表中的冗余数据列；若个别过程要访问整个数据，则要用连接*作，这也无妨分割表；典型案例是电信话单按月分割存放。另一种是当主要过程要重复访问部分行时，最好将被重复访问的这些行单独形成子集表（冗余储存），这在不考虑磁盘空间开销时显得十分重要；但在分割表以后，增加了维护难度，要用触发器立即更新、或存储过程或应用代码批量更新，这也会增加额外的磁盘I/O开销。 垂直分割表（不破坏第三范式），一种是当多个过程频繁访问表的不同列时，可将表垂直分成几个表，减少磁盘I/O（每行的数据列少，每页存的数据行就多，相应占用的页就少），更新时不必考虑锁，没有冗余数据。缺点是要在插入或删除数据时要考虑数据的完整性，用存储过程维护。另一种是当主要过程反复访问部分列时，最好将这部分被频繁访问的列数据单独存为一个子集表（冗余储存），这在不考虑磁盘空间开销时显得十分重要；但这增加了重叠列的维护难度，要用触发器立即更新、或存储过程或应用代码批量更新，这也会增加额外的磁盘I/O开销。垂直分割表可以达到最大化利用Cache的目的。 总之，为主要过程分割表的方法适用于：各个过程需要表的不联结的子集，各个过程需要表的子集，访问频率高的主要过程不需要整表。在主要的、频繁访问的主表需要表的子集而其它主要频繁访问的过程需要整表时则产生冗余子集表。 注意，在分割表以后，要考虑重新建立索引。 3.2 存储衍生数据 对一些要做大量重复性计算的过程而言，若重复计算过程得到的结果相同（源列数据稳定，因此计算结果也不变），或计算牵扯多行数据需额外的磁盘I/O开销，或计算复杂需要大量的CPU时间，就考虑存储计算结果（冗余储存）。现予以分类说明： 若在一行内重复计算，就在表内增加列存储结果。但若参与计算的列被更新时，必须要用触发器更新这个新列。 若对表按类进行重复计算，就增加新表（一般而言，存放类和结果两列就可以了）存储相关结果。但若参与计算的列被更新时，就必须要用触发器立即更新、或存储过程或应用代码批量更新这个新表。 若对多行进行重复性计算（如排名次），就在表内增加列存储结果。但若参与计算的列被更新时，必须要用触发器或存储过程更新这个新列。 总之，存储冗余数据有利于加快访问速度；但违反了第三范式，这会增加维护数据完整性的代价，必须用触发器立即更新、或存储过程或应用代码批量更新，以维护数据的完整性。 3.3 消除昂贵结合 对于频繁同时访问多表的一些主要过程，考虑在主表内存储冗余数据，即存储冗余列或衍生列（它不依赖于主键），但破坏了第三范式，也增加了维护难度。在源表的相关列发生变化时，必须要用触发器或存储过程更新这个冗余列。当主要过程总同时访问两个表时可以合并表，这样可以减少磁盘I/O*作，但破坏了第三范式，也增加了维护难度。对父子表和1：1关系表合并方法不同：合并父子表后，产生冗余表；合并1：1关系表后，在表内产生冗余数据。 4 数据库对象的放置策略 数据库对象的放置策略是均匀地把数据分布在系统的磁盘中，平衡I/O访问，避免I/O瓶颈。 ⑴ 访问分散到不同的磁盘，即使用户数据尽可能跨越多个设备，多个I/O运转，避免I/O竞争，克服访问瓶颈；分别放置随机访问和连续访问数据。 ⑵ 分离系统数据库I/O和应用数据库I/O。把系统审计表和临时库表放在不忙的磁盘上。 ⑶ 把事务日志放在单独的磁盘上，减少磁盘I/O开销，这还有利于在障碍后恢复，提高了系统的安全性。 ⑷ 把频繁访问的“活性”表放在不同的磁盘上；把频繁用的表、频繁做Join*作的表分别放在单独的磁盘上，甚至把把频繁访问的表的字段放在不同的磁盘上，把访问分散到不同的磁盘上，避免I/O争夺； ⑸ 利用段分离频繁访问的表及其索引（非聚族的）、分离文本和图像数据。段的目的是平衡I/O，避免瓶颈，增加吞吐量，实现并行扫描，提高并发度，最大化磁盘的吞吐量。利用逻辑段功能，分别放置“活性”表及其非聚族索引以平衡I/O。当然最好利用系统的默认段。另外，利用段可以使备份和恢复数据更加灵活，使系统授权更加灵活。
access数据库 车票查询 设计实例
公共基础知识Access数据库程序设计基本要求1. 具有数据库系统的基础知识。2. 基本了解面向对象的概念。3. 掌握关系数据库的基本原理。4. 掌握数据库程序设计方法。5. 能使用Access建立一个小型数据库应用系统。考试内容一、数据库基础知识1. 基本概念：数据库，数据模型，数据库管理系统，类和对象，事件。2. 关系数据库基本概念：关系模型（实体的完整性，参照的完整性，用户定义的完整性），关系模式，关系，元组，属性，字段，域，值，主关键字等。3. 关系运算基本概念：选择运算，投影运算，连接运算。4. SQL基本命令：查询命令，操作命令。5. Access系统简介：（1） Access系统的基本特点。（2） 基本对象：表，查询，窗体，报表，页，宏，模块。二、数据库和表的基本操作1. 创建数据库：（1） 创建空数据库。（2） 使用向导创建数据库。2. 表的建立：（1） 建立表结构：使用向导，使用表设计器，使用数据表。（2） 设置字段属性。（3） 输入数据：直接输入数据，获取外部数据。3. 表间关系的建立与修改：（1） 表间关系的概念：一对一，一对多。（2） 建立表间关系。（3） 设置参照完整性。4. 表的维护：（1） 修改表结构：添加字段，修改字段，删除字段，重新设置主关键字。（2） 编辑表内容：添加记录，修改记录，删除记录，复制记录。（3） 调整表外观。5. 表的其他操作：（1） 查找数据。（2） 替换数据。（3） 排序记录。（4） 筛选记录。三、查询的基本操作1. 查询分类：（1） 选择查询。（2） 参数查询。（3） 交*表查询。（4） 操作查询。（5） SQL查询。2. 查询准则：（1） 运算符。（2） 函数。（3） 表达式。3. 创建查询：（1） 使用向导创建查询。（2） 使用设计器创建查询。（3） 在查询中计算。4. 操作已创建的查询：（1） 运行已创建的查询。（2） 编辑查询中的字段。（3） 编辑查询中的数据源。（4） 排序查询的结果。四、窗体的基本操作1. 窗体分类：（1） 纵栏式窗体。（2） 表格式窗体。（3） 主/子窗体。（4） 数据表窗体。（5） 图表窗体。（6） 数据透视表窗体。2. 创建窗体：（1） 使用向导创建窗体。（2） 使用设计器创建窗体：控件的含义及种类，在窗体中添加和修改控件，设置控件的常见属性。五、报表的基本操作1. 报表分类：（1） 纵栏式报表；（2） 表格式报表。（3） 图表报表。（4） 标签报表。2. 使用向导创建报表。3. 使用设计器编辑报表。4. 在报表中计算和汇总。六、页的基本操作1. 数据访问页的概念。2. 创建数据访问页：（1） 自动创建数据访问页。（2） 使用向导数据访问页。七、宏1. 宏的基本概念。2. 宏的基本操作：（1） 创建宏：创建一个宏，创建宏组。（2） 运行宏。（3） 在宏中使用条件。（4） 设置宏操作参数。（5） 常用的宏操作。八、模块1. 模块的基本概念：（1） 类模块。（2） 标准模块。（3） 将宏转换为模块。2. 创建模块：（1） 创建VBA模块：在模块中加入过程，在模块中执行宏。（2） 编写事件过程：键盘事件，鼠标事件，窗口事件，操作事件和其他事件。3. 调用和参数传递。4. VBA程序设计基础：（1） 面向对象程序设计的基本概念。（2） VBA编程环境：进入VBE，VBE界面。（3） VBA编程基础：常量，变量，表达式。（4） VBA程序流程控制：顺序控制，选择控制，循环控制。（5） VBA程序的调试：设置断点，单步跟踪，设置监视点。考试方式1. 笔试：90分钟，满分100分，其中含公共基础知识部分的30分。2. 上机操作：90分钟，满分100分。上机操作包括：（1） 基本操作。（2） 简单应用。（3） 综合应用
sql数据库XX系统的设计实例
我这里有个现成的，简单的图书管理系统数据库。数据库名称：BookManager图书信息表(BookInfo)字段名称类型是否为空(Y/N)说明BookID编号Int(4)N主键BookName书的名称Varchar(50)NBookAuthor作者Varchar(50)NBookTypeID图书的类型Int(4)N外键BookSpec图书规格Varchar(50)YBookConcert出版社Varchar(50)YBookPrice价格doubleNBookCount数量Int(10)NBookDates入库时间DatatimeNBookState图书状态Varchar(20)Y是否已借出BookRemark备注Varchar(100)Yfee费用DoubleN结帐付费图书类型表(BookType)字段名称类型是否为空(Y/N)说明ID编号Int(4)N主键,自动增加TypeName图书类型Varchar(50)N
数据库设计实例
某种“数据库技术”课程的作业吧把表的结构提供给你，其它的最好看看书自己做吧车主（车主ID，...）汽车（汽车ID，...，所属车主ID）事故（事故ID，...，所属汽车ID）所有的主ID应该有索引，车主姓名和车牌号也可以有索引视图：车主与汽车的连立，车主、汽车与事故的连立E-R图就是三大区域，中间有两个1:n连接这是3NF范式（主外码连接无冗余）