数据库设计参考标准

数据库设计参考标准数据库设计参考标准 数据库设计参考标准 1 文档控制文档控制 文档属性文档属性 文档编号文档编号作者作者版本版本日期日期密级密级 EDS06黄翠微V12011 年 6 月 16 文档修订历史文档修订历史 版本版本状态状态日期日期作者作者修订描述修订描述 数据库设计参考标准 2 一 概述 为明确公司项目中数据库逻辑设计及物理设计的内容和流程 特制定本 规范 供数据库设计 开发及维护人员参考 数据库设计方法目前可分为四类 直观设计法 规范设计法 计算机辅 助设计法和自动化设计法 新奥尔良法是目前公认的比较完整和权威的一种 规范设计法 新奥尔良法将数据库设计分成需求分析 分析用户需求 概 念设计 信息分析和定义 逻辑设计 设计实现 和物理设计 物理数据 库设计 目前 常用的规范设计方法大多起源于新奥尔良法 并在设计的 每一阶段采用一些辅助方法来具体实现 以下是两种常用的规范设计方法 1 基于 E R 模型的数据库设计方法 该方法是由 P P S chen 于 1976 年 提出的数据库设计方法 其基本思想是在需求分析的基础上 用 E R 实体 联系 图构造一个反映现实世界实体之间联系的企业模式 然后再将此企业模式转换成基于某一特定的 DBMS 的概念模式 2 基于 3NF 的数据库设计方法 该方法是由 S Atre 提出的结构化设计方 法 其基本思想是在需求分析的基础上 确定数据库模式中的全部属性 和属性间的依赖关系 将它们组织在一个单一的关系模式中 然后再分 析模式中不符合 3NF 的约束条件 将其进行投影分解 规范成若干个 3NF 关系模式的集合 其具体设计步骤分为五个阶段 1 设计企业模式 利用规范化得到的 3NF 关系模式画出企业模式 2 设计数据库的概念模式 把企业模式转换成 DBMS 所能接受的 概念模式 并根据概念模式导出各个应用的外模式 3 设计数据库的物理模式 存储模式 4 对物理模式进行评价 5 实现数据库 备注 数据库设计规范 数据编程规范 数据库物理设计规范中以 Oracle 数据库为例 其它结构的数据库类似 数据库设计参考标准 3 二 数据库设计流程 以规范性设计为例 把数据库设计流程分为以下几个阶段 一 需求分析阶段 1 需求收集和分析 得到数据字典描述的数据需求和数据流图描述的处理 需求 2 需求分析的重点 调查 收集与分析用户在数据管理中的信息要求 处 理要求 安全性与完整性要求 3 需求分析的方法 调查组织机构情况 各部门的业务活动情况 协助用 户明确对新系统的各种要求 确定新系统的边界 4 常用的调查方法有 跟班作业 开调查会 请专人介绍 询问 设计 调查表请用户填写 查阅记录 5 分析和表达用户需求的方法 主要包括自顶向下和自底向上两类方法 采用逐层分解的方式分析系统 并把每一层用数据流图和数据字典描述 6 数据流图 Data Flow Diagram DFD 表达了数据和处理过程的关系 系统中的数据则借助数据字典 Data Dictionary 简称 DD 来描述 二 概念结构设计阶段 概念模型用于信息世界的建模 概念模型不依赖于某一个 DBMS 支持的数 据模型 通过对用户需求进行综合 归纳与抽象 形成一个独立于具体 DBMS 的概念模型 可以用 E R 图表示 概念模型可以转换为计算机上某一 DBMS 支持的特定数据模型 其特点为 1 具有较强的语义表达能力 能够方便 直接地表达应用中的各种语义知 识 2 应该简单 清晰 易于用户理解 是用户与数据库设计人员之间进行交 流的语言 三 逻辑设计阶段 将概念结构转换为某个 DBMS 所支持的数据模型 例如关系模型 并对其 进行优化 将 E R 图转换为关系模型实际上就是要将实体 实体的属性和实体 之间的联系转化为关系模式 这种转换一般遵循如下原则 1 一个实体型转换为一个关系模式 实体的属性就是关系的属性 2 一个多对多的联系转换为一个关系模式 数据模型的优化 确定数据依赖 消除冗余的联系 确定各关系模式分别 数据库设计参考标准 4 属于第几范式 确定是否要对它们进行合并或分解 一般来说将关系分解为 3NF 的标准 四 物理设计阶段 为逻辑数据模型选取一个最适合应用环境的物理结构 包括存储结构和存取 方法 根据 DBMS 特点和处理的需要 进行物理存储安排 设计索引 形成 数据库内模式 五 数据库实施阶段 运用 DBMS 提供的数据语言 例如 SQL 及其宿主语言 例如 C 根据逻辑 设计和物理设计的结果建立数据库 编制与调试应用程序 组织数据入库 并 进行试运行 六 数据库运行维护阶段 在数据库系统运行过程中必须不断地对其进行评价 调整与修改 内容包 括 数据库的转储和恢复 数据库的安全性 完整性控制 数据库性能的监督 分析和改进 数据库的重组织和重构造 为加快数据库设计速度 目前有很多数据库辅助工具 CASE 工具 如 Rational 公司的 Rational Rose CA 公司的 Erwin 和 Bpwin Sybase 公司的 PowerDesigner 以及 Oracle 公司的 Oracle Designer 等 三 数据库设计规范 一 数据库规范化的总体要求 1 数据表中避免可空列 虽然表中允许空列 但空字段为一种特殊的数据类型 数据库将对其进行 特殊的处理 为此将增加数据库处理记录的复杂性 且当表中存在较多空字段 时 在同等条件下 数据库处理的性能将降低许多 因此 在数据库表设计时 应尽量避免 若确实需要 可通过一些折中方式处理 让其对数据库性能的影 响降低至最少 在此推荐两个方式 A 设置默认值 默认值设置原则为业务启动后不可能达到的值 或者 为原始状态的对应值 如无法从数值上区分是否为原始状态则不推 荐设置默认值 以数值型字段为例 假设其用于存放采集终端所获 取的子舱位订座 由于订座数默认至少为大于等于 0 的数值 为此 可设置默认值为 1 另一个例子 字符型的航班状态 默认情况下 航班为正常执行的正班 当因为天气 机械或其它调控等原因状态 才发生改变 此时可设置默认值为 正班 日期型数据可默认为 1900 01 01 在应用层面显示需要根据实际情况进行转换 B 第二种方式是建立副表 这是当一张表中允许为空的列较多时 如 接近表全部列数的三分之一 且这些列数据在大多情况下均为空 此时建议另外建立一张副表 以保存这些列 主表与副表之间根据 关键字关联 这样将数据分别存储于两个独立的表中使得主表设计 更为简单 既保证了数据库性能 又能满足存储空值的应用需要 如果字段默认值设置导致部分业务需求无法满足或开发代价大量增加 数据库设计参考标准 5 此时不建议设置 保留可空 如数值型字段 如在该字段上需要与其他字 段进行组合运算 或基于该字段进行统计汇总时 空值具有不可替代的重 要作用 2 数据表中不应存在重复数据值或列 如客户数据与客户经理数据存储问题 如将二者放在同一张表中 为解决 多个客户经理问题 表中必须设置首选客户经理 备选客户经理相关信息 在 客户经理离职后 必须修改表中所有相关数据 造成很多不便且不利于追踪 为此 在数据库设计的时候要尽量避免现象发生 建议改变策略 将客户 信息存入一张表 客户经理信息存入一张表 而客户与客户经理之间的联系关 系存入第三张表 当联系关系发生变化时 仅需设置过期日期或状态即可 3 数据表主键设置为无意义数值序列号 在进行数据库表设计的时候 采用一个无意义的 数值型的 PKID 对行记 录进行唯一的标识 而不是通过航班号 航段 姓名 工号等具有业务意义的 字段区分记录 由于人工管理下很难保证 PKID 值的不重复 为此建议每个表 对应的 PKID 列的值由数据库自动管理 建议在原有业务上需要唯一区分的字 段或字段组合上 建立唯一性索引 以存储 10 年的航班计划表为例 业务上 唯一区分一个航班的通常包含三个字段 航班号 航段 起飞日期 先前的做 法是在这三个上建立复合主键 在此推荐用一个 Number 8 的 PKID 列作为 主键 原因是以每天 600 个航班 一年 365 天 10 年总计 219 万个航班 考 虑到业务的扩展速度为 10 年内翻三番 657 万 按理可设置 7 位 考虑到其它 可能损耗的 ID 值 多设置一位 故为 Number 8 在航班数据的增删改过程中 通常需要判断是否存在重复数据 是则为修改操作 否则为插入操作 此时仅 以 PKID 列无法区分 为此建议在航班号 航段 起飞日期上建立唯一性索引 由数据库后台执行一致性校验 利用 Sequence 生成的 PKID 若需要用于其他 数据库使用 需要在应用层面记录 以防止数据库出现问题时恢复后 sequence 不匹配问题 4 数据库对象前缀名统一 一个应用系统对应的数据库表 视图 过程等对象 通常均高达千计 为 对其进行规范管理与快速定位 要求遵循数据对象命名规范 具体命名规范详 见下文第 二 点 5 数据表上避免设置外键 外键的作用在于两方面 一是保证主从表数据一致性 这是数据库提供的 用于保证数据质量的强制性手段 二是定义主表数据删除时从表数据的级联删 除方式 二者是外键的优势所在 减少了开发人员不分工作量 但是 外键的存在同样带来困扰 一是从应用系统的三层架构上考虑 如 何设置外键关系到业务逻辑 这是将业务逻辑层部分功能后移至数据库层 导 致各层职责不清的紧耦合现象 而且当业务逻辑随着需求发展发生变化将引起 数据库层面与应用层面的双重修改 二是在开发过程中 开发人员不得不了解 数据库物理设计细节 在项目协作分工上造成不便 三是从后续主数据 各业 务方向公共数据建设上 为数据库管理员在执行数据表从 公有区 移入移出 操作增加难度 为此 建议不设置外键 如果设置了外键 则仅保留数据一致性校验功能 不建议设置级联删除的选项 此时要求开发人员通过培养一种良好的编程习惯 数据库设计参考标准 6 从程序逻辑上实现主表 从表数据的增 删 改 查的规范操作 具体可参考 如下说明 A 从表数据增加操作 首先从主表上查询是否存在所需数据 是则可进行增加操作 否则需先 增加主表数据后才可进行从表数据增加操作 B 从表数据删除操作 直接删除从表数据 当主表数据未被任何从表引用时 根据实际业务需 要判断是否级联删除主表数据 C 从表数据修改操作 从表数据修改后的内容必须是主表内存在的数据 否则需先增加主表数 据后才可进行数据修改操作 D 主表数据增加操作 直接数据数据 与从表数据无关 E 主表数据删除操作 删除之前判断是否存在引用的从表数据 是则根据业务规则决定是否继 续 并根据业务规则判断从表引用数据的处理方式为对应数据项置空或 删除从表数据记录 如果未存在引用的从表数据 则可直接删除 注意 保持事务的原子性 即主 从表数据同时提交或同时回滚 F 主表数据修改操作 判断是否存在引用的从表数据 是则首先修改从表数据 然后进行主表 数据修改 注意保持事务的原子性 即主 从表数据同时提交或同时回 滚 为了在应用程序上能顺利完成上述六项操作 开发人员除了严格记录主从 表调用关系外 还必须在程序上实现数据一致性检查功能 以及时发现可能的 数据不一致现象 如果应用程序未能实现这种一致性检查工作 则需将检查规 则提交予 DBA 由 DBA 在后台进行定期检查 6 避免在数据库上编写存储过程 代码包 触发器 通常存储过程 代码包 触发器包含很多业务逻辑 违背了三层架构设 计的松耦合原则 二 数据对象命名规范 1 命名规范总要求 所有数据对象命名必须遵循如下规范要求 数据库设计参考标准 7 1 通常采用 望文知义 的形式命名 部分对安全具有特殊要求的可 以编号命名 2 命名格式是 以字母打头 包含具有特定含义的一个或多个英文单 词 单词之间以下划线 间隔 长度不超过 30 个字符 3 大小写不敏感 4 禁止使用关键字命名 5 不同数据对象命名的前缀要求如下表所示 编号编号数据对象数据对象前缀要求前缀要求 1非临时数据表 4 位模块名 表名 2临时数据表 TMP 表名 3分区表 PT 4主键 PK 表名 5索引 IDX 6函数索引 FIDX 7视图 VW 8实体化视图 MV 9存储过程 SP 10触发器 TRG 11函数 SF 12数据包 PKG 13序列 SEQ 14数据类型 TYP 15后台任务 JB 数据库设计参考标准 8 2 表命名规范 对于同一应用 在命名上区分不同功能模块的所使用的表 假设一个系统 包含三个模块 基础维护 查询 系统管理 则基础维护表名前缀为 TB BASE 查询模块表名前缀为 TB QURY 后面加上具体的表名 对于一些特定作用的表 增加特定后缀表示 如历史航班表可命名为 TB FLIGHT HISTORY 复杂计算中间过程所产生的临时数据表可命名为 TB MIDTERM DATA TEMP 3 字段命名规范 字段命名规范符合命名规范总要求 字段类型选择满足 1 固定长度的字符串类型采用 CHAR 非固定长度的字串类型采用 VARCHAR2 2 日期型字段采用日期类型 3 数字型字段采用 NUMBER 类型 并标明长度与小数位数 4 如无特殊需要 避免使用大字段 BLOB CLOB LONG 等 5 相同业务字段出现在不同表中 使用相同命名 且保证类型和长度 一致 4 索引命名规范 A 复合索引至少包含前两个字段的缩写或全名 B 在表上创建主键时 必须显式指定主键唯一性约束名称 举例 为表 TB FLIGHT 创建主键 正确格式为 alter table TB FLIGHT add constraint PK FLIGHT primary key FLIGHT ID 语句中指定了主 键的唯一性约束名为 PK FLIGHT 而错误的格式 alter table TB FLIGHT add primary key FLIGHT ID 此中 主键唯一性约束将被系统随机分配一个名字 5 分区命名规范 采用可容易判断各分区内所存数据内容的名字 如对表 TB FLIGHT 按季 度进行分区 则各分区命名格式为 PT XXXXQN 其中 XXXX 为年份 N 为 1 至 4 的季度编号 6 触发器命名规范 命名规则 trg 表名 如表 TB FLIGHT 行 插入后的触发器名字为 TRG TB FLIGHT AFTER INS 数据库设计参考标准 9 三 数据库设计说明书格式 数据库设计说明书的格式至少包含如下部分 1 概述 从整体上说明数据库设计思想 采用的方法 作用范围 参考的 文献 文档结构组成等 2 数据表清单 编号表英文名表中文名称表描述使用的功能模块 1表 1 2表 2 3 ER 关系描述 描述表之间存在的主外键联系等 4 数据表定义 1 表 1 字段 编号 字段 名称 中文 描述 数据 类型 Null 标志 是否 主键 外键是否索引备注 1 2 备注数据量增长说明 外键关系说明等 2 表 2 5 各系统相关的其它信息 要求在 数据表清单 每一行的表名上定义 超链接 指向 数据表定义 中的对应内容 以方便地在文档中浏览跳转等 四 数据库编程规范 一 书写规范 1 关键字大写 其他代码统一使用小写 数据库设计参考标准 10 2 确保变量和参数在类型和长度上与表数据列类型和长度相匹配 建议采 用如下格式 vs flightno tb flight flight no TYPE 这样当 TB FLIGHT 表的 FLIGHT NO 字段定义发生变化时程序无需调 整 3 参数和变量命名前缀遵循如下规范 项目项目前缀要求前缀要求 传入参数 ip 传出参数 op 数值型变量 vn 字符型变量 vs 日期型变量 vd 4 程序块中的 begin end 独立成行 5 程序块采用缩进风格书写 保证代码清晰易读 缩进格数统一 6 一行仅写一条语句 7 同一语句占用多行时 每行的第一个关键字左对齐 8 对于 Insert values 和 update 语句 一行写一个字段 字段后面紧 跟注释 注释语句左对齐 values 和 insert 左对齐 左括号和右括 号与 insert values 左对齐 例 insert into table name user id 用户 ID 主键 user name 用户名 login name 登录名 values v user id v user name v login name 9 相对独立的程序块之间增加一空行 10 超过 110 列的语句要分行书写 长表达式应在低优先级操作符处换行 数据库设计参考标准 11 操作符或关键字放在新行之首 二 注释规范 1 以统一格式在头部对代码进行注释 以说明代码所要完成功能 传入参 数 传出参数 作者 创建时间 并在其后从修改日期 修改者 修改 内容角度描述各次修改历史 功能描述 传入参数 传出参数 返回结果 作者 创建日期 修改历史 1 YYYY MM DD 作者 1 修改内容 1 2 YYYY MM DD 作者 2 修改内容 2 2 在所有变量定义的右侧或上方 进行注释以说明变量的用途与含义 3 注释内容清晰 明了 并确保无二义性 4 对每一程序分支书写注释 5 在代码的功能分支 子层次上注释 以帮助维护人员理解代码 例 case vs type1 when 1 then 里程奖励 when 2 then 里程扣除 when 3 then 里程兑换 end case 6 代码注释应放在描述的代码上方或右方相近位置 不可放在下面 7 注释与所描述的内容进行字数的缩进排列 8 在函数中 对所返回的代码进行详细描述 9 在程序块的结束行右方加注释 以表示程序块结束 数据库设计参考标准 12 三 语法规范 1 存储过程的 In out 参数应按类别分开书写 不要交叉 2 存储过程中变量的声明应集中在 is 和 begin 关键字之间申明 3 尽可能使用相关表字段类型来定义相关的变量 如 type rowtype 4 删除无用的变量与参数 5 存储过程中存在多分支时 若有事务控制 需确保各分支均有事务结束 处理 且发生异常时 必须 Exception 中进行异常捕获 四 SQL 性能规范 1 避免在批量操作时频繁使用 commit 频繁的 COMMIT 会导致物理 I O 增大 同时会产生 log file sync 等待 但 长时间不提交将带来更多的性能问题 建议小于 3 秒的事务可以一次提交 大 于 3 秒的操作尽可能 3 秒左右提交一次 实际应用中使用 COMMIT 时必须保 证事务的完整性 2 使用动态 sql 时 需绑定变量 而非实际值 为了不重复解析相同的 SQL 语句 在第一次解析之后 ORACLE 将 SQL 语句缓存在内存中 当你执行一个 SQL 语句时 如果它和之前的执行过的语句 完全相同 ORACLE 就能很快获得内存中相同语句的执行计划 然后就直接执 行该语句 而不需要进行解析 如果没有使用绑定变量 虽然语句结构相同 但因传入的变量不一样导致 语句不相同 ORACLE 还是需要对每条结构相同的语句重新解析一遍 同时在 内存中缓存该语句 导致共享池中存放了 N 条结构相同的语句 既达不到共享 的目的 又浪费了大量的内存空间 如果语句执行的太频繁 会导致数据库无 法申请共享内存而出错 3 避免不必要的排序 对查询结果进行排序会大大的降低系统的性能 4 用 WHERE 子句替换 HAVING 子句 例如 SELECT NAME SUM AGE FROM EMPLOYEE GROUP BY NAME HAVING NAME ABC 修改为以下语句效果更好 SELECT NAME SUM AGE FROM EMPLOYEE WHERE NAME ABC GROUP BY NAME 5 用 替代 如 在 ID 列上建有索引 则语句 SELECT FROM EMPLOYEE WHERE 数据库设计参考标准 13 ID 9 要比语句 SELECT FROM EMPLOYEE WHERE ID 8 高效 这是 由于前者 DBMS 将直接跳到第一个 ID 等于 9 的记录而后者将首先定位到 8 的 记录并且向前扫描到第一个 DEPT 大于 8 的记录 6 删除表中所有记录时用 TRUNCATE 替代 DELETE 当删除表中的记录时 在通常情况下 回滚段用来存放可以被恢复的信息 如果你没有 COMMIT 事务 ORACLE 可以将数据恢复到删除之前的状态 而 当运用 TRUNCATE 时 回滚段不存放任何可被用于恢复的信息 当命令运行 后 数据不能被恢复 因此很少的资源被调用 执行时间也会很短 空间立即释放 detele 操作后的空间可以被重新利用 但不会释放 7 用 UNION ALL 代替 UNION 说明 UNION ALL 不过虑重复记录 UNION 过滤重复记录 所以需要先 排序 如果不需要过滤重复的记录 UNION ALL 比 UNION 性能更好 8 用 NOT EXISTS 替代 NOT IN 在许多基于驱动表的查询中 为了满足一个条件 往往需要对另一个表进 行联接 在这种情况下 使用 EXISTS 或 NOT EXISTS 通常将提高查询的效 率 例如 SELECT FROM EMP WHERE EMPNO 0 AND DEPTNO IN SELECT DEPTNO FROM DEPT WHERE LOC MELB 没有以下语句高效 SELECT FROM EMP WHERE EMPNO 0 AND EXISTS SELECT X FROM DEPT WHERE DEPT DEPTNO EMP DEPTNO AND LOC MELB 9 使用 DECODE 函数来减少处理时间 使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表 例 SELECT NAME UNDER AGE FROM EMPLOYEE WHERE AGE 18 可以改成 SELECT DECODE SIGN AGE 18 1 NAME UNDERAGE 1 NAME OVERAGE 数据库设计参考标准 14 FROM EMPLOYEE 10 使用表的别名 Alias 当在 SQL 语句中连接多个表时 应使用表的别名并把别名前缀于每个列上 这样可以减少解析的时间 并减少那些由 Column 歧义引起的语法错误 11 尽量减少对表的查询次数 说明 在含有子查询的 SQL 语句中 要特别注意减少对表的查询 例 UPDATE EMP SET EMP CAT SELECT MAX CTEGORY FROM EMP CATEGORIES SAL RANGE SELECT MAX SAL RANGE FROM EMP CATEGORIES WHERE EMP DEPT 0020 改写成下面这样会更高效 UPDATE EMP SET EMP CAT SAL RANGE SELECT MAX CATEGORY MAX SAL RANGE FROM EMP CATEGORIES WHERE EMP DEPT 0020 12 用表连接替换 EXISTS 例 SELECT ENAME FROM EMP E WHERE EXISTS SELECT X FROM DEPT WHERE DEPT NO E DEPT NO AND DEPT CAT A 改写成下面这样会更高效 SELECT ENAME FROM DEPT D EMP E WHERE E DEPT NO D DEPT NO AND DEPT CAT A 13 避免使用 DISTINCT 可以用 EXISTS 替换 DISTINCT 当提交一个包含一对多表信息 比如部门表和雇员表 的查询时 避免在 SELECT 子句中使用 DISTINCT 一般可以考虑用 EXIST 替换 例 SELECT DISTINCT DEPT NO DEPT NAME FROM DEPT D EMP E WHERE D DEPT NO E DEPT NO 改写成下面这样会更高效 SELECT DEPT NO DEPT NAME FROM DEPT D WHERE EXISTS SELECT X FROM EMP E WHERE E DEPT NO D DEPT NO EXISTS 使查询更为迅速 因为 RDBMS 核心模块将在子查询的条件一旦 数据库设计参考标准 15 满足后 立刻返回结果 14 避免使用耗费资源的操作 带有 DISTINCT UNION MINUS INTERSECT ORDER BY GROUP BY 的 SQL 语句会启动 SQL 引擎执行耗费资源的排序 SORT 功 能 如果可能 应尽量避免使用或改写 15 避免对索引列使用数据库函数 计算表达式等等 对查询索引列使用数据库函数 计算表达式会造成对索引失效 例 select t reserver1 count cout from custcare password login fail t where t operating source 1 and to char t login time yyyymmdd between 20080904 and 20080918 group by t reserver1 having count 30 custcare password login fail 表的 login time 列上建了索引 上面语句 to char 函数会造成索引失效 把 and to char t login time yyyymmdd between 20080904 and 20080918 修改成 and t login time between to date 20080904 yyyymmdd and to date 20080918 yyyymmdd 可以正确索引 查询时尽可能将操作移至等式的右边 甚至去掉函数 16 在查询条件中 避免不必要的类型转换 字符类型列赋予数值型数据时会使用隐含类型转换 而数值型列赋予字符 型数据时不会隐含类型转换 而是将右边值转换为数值类型 例 select from atp para cfg where cfg type 35 cfg type 是字符型列 上面语句会造成隐含类型转换 to number cfg type 35 即 Oracle 内部会做一次类型转换 17 尽量避免字段与 NULL 比较 索引并不存储 null 值 使用 NULL 作为条件将不会使用索引 例 select from staff member where address is null 或 not null 该语句将不会使用索引 18 在索引列上使用 和 like 将不会使用索引 例 select from staff member where dept no2001 select from staff member where first name like DON 以上语句都不会用到索引 注 like 语句改为 select from staff member where first name like DON 当前面没有 时 将使用索引 ORACLE 内部转换为范围操作 数据库设计参考标准 16 19 用 Case 语句合并多重扫描 例 select count from emp where sal5000 这样我们需要进行三次全表查询 但是如果我们使用 case 语句 select count sale when sal 5000 then 1 else null end count poor from emp 这样查询的结果一样 但是执行计划只进行了一次全表查询 五 JOB 使用规范 1 Job 的执行时间尽量避开业务高峰 2 如果有多个占用资源较多的 job 任务 考虑将它们执行的时间错开 3 禁止在 job 运行期间对其调用的存储过程进行修改和编译 4 在 Oracle10g 之后 利用 dbms schedule 建立 Job 六 索引使用规范 1 索引创建原则 1 表的主键 外键必须有索引 2 数据量超过 300 条的表应该有索引 3 经常与其他表进行连接的表 在连接字段上应该建立索引 4 经常出现在 Where 子句中的字段 特别是大表的字段 应该建立 索引 5 索引应该建在选择性高的字段上 6 索引应该建在小字段上 对于大的文本字段甚至超长字段 不要建 索引 7 复合索引的建立需要进行仔细分析 尽量考虑用单字段索引代替 正确选择复合索引中的主列字段 一般是选择性较好的字段 复合索引的几个字段是否经常同时以 AND 方式出现在 Where 子 数据库设计参考标准 17 句中 单字段查询是否极少甚至没有 如果是 则可以建立复合 索引 否则考虑单字段索引 如果复合索引中包含的字段经常单独出现在 Where 子句中 则 分解为多个单字段索引 如果复合索引所包含的字段超过 3 个 那么仔细考虑其必要性 考虑减少复合的字段 8 频繁进行数据增删改操作的表 不要建立太多的索引 9 删除无用的索引 避免对执行计划造成负面影响 2 索引使用建议 1 避免对条件字段进行操作 对条件字段的操作会导致该列的索引失效 这里所谓的操作包括数据库函 数 计算表达式等 如以下 3 个 sql 其条件列上都建有索引 但不会被使用 select from record where substrb CardNo 1 4 5378 13 秒 select from record where amount 30 1000 11 秒 select from record where to char ActionTime yyyymmdd 19991201 10 秒 可改为如下形式 select from record where CardNo like 5378 1 秒 select from record where amount 1000 30 1 秒 select from record where ActionTime to date 19991201 yyyymmdd 10 应该写为 select col1 col2 from tab1 where col1 10 3 尽量避免 尽量去掉 避免全表扫描 如果数据是枚举值 且取值范围固定 则 数据库设计参考标准 18 修改为 OR 方式 例 UPDATE SERVICEINFO SET STATE 0 WHERE STATE0 以上语句由于其中包含了 执行计划中用了全表扫描 TABLE ACCESS FULL 没有用到 state 字段上的索引 实际应用中 由于业务逻辑 的限制 字段 state 为枚举值 只能等于 0 1 或 2 而且 值等于 1 2 的很 少 因此可以去掉 利用索引来提高