精华内容
下载资源
问答
  • Automated Physical Database Design and Tuning, 自动物理数据库设计和调优
  • 物理数据库设计最佳实践,第 1 部分 表规范化和非规范化、索引设计最佳实践 内容提要 物理数据库设计是影响数据库性能的一个最重要的因素。物理数据库设计涵盖了所有和数据库物理结构相关的设计功能,比如表规范化和...

    DB2 最佳实践

    物理数据库设计最佳实践,第 1 部分

    表规范化和非规范化、索引设计最佳实践

    内容提要

    物理数据库设计是影响数据库性能的一个最重要的因素。物理数据库设计涵盖了所有和数据库物理结构相关的设计功能,比如表规范化和反规范化、索引、物化视图、数据集群、多维数据集群、表(range)分区还有数据库(hash)分区。

    良好的物理数据库设计不仅能够降低硬件资源使用率(I/O,CPU 和网络),而且还可以并提高你的管理效率。良好的物理数据库设计依次对你的业务提供了下面好处:

    • 增加使用数据库的应用程序性能,更快的响应时间和更高的终端用户满意度。
    • 减少 IT 管理成本,可以为你提供管理广泛数据库的能力,并能更快的适应这个应用程序的需求变化。而且还可以缩短应用程序的需求的响应时间。
    • 减少 IT 硬件开销
    • 更好的缩短用于备份和恢复花费的时间

    图 1 显示了一个物理数据库系统的说明。这 3 个深黑框的垂直矩形显示了 3 个不同的物理数据库系统;其他的正方形和矩形框表示的是磁盘上的存储块;所有着色的符号显示表中(比如地理或月份)的数据值。

    在这个例子中,一个表已经在 3 个实例 P1、P2 和 P3 上进行了哈希分区。同时,这个表也以 month 进行了范围分区,数据可以很容易的按月添加和删除。这也间接有助于用 month 的谓词查询。数据在每个表中被用多维集群(MDC)进行了集群,而且这是在每个 range 分区中进一步集群。表中的记录也建立了普通的基于记录的(RID-based)索引。对这个表创建一个物化查询表(MQT),它包括聚合数据(比如地理上的平均销售),这些已经编入索引和 MDC 中了。

    图 1. 物理数据库系统图解

    物理数据库系统图解

    最佳实践

    表规范化和非规范化

    • 对于大多数通用数据库系统使用第 3 范式(3NF)来规范你的表,维度查询用星型模式或雪花模型,以及对基础广泛的数据仓库 IBM 分层数据结构,在线分析处理(OLAP)和商业智能(BI)。

    索引设计

    • 使用工作负载谓词和主外键,来设计一个索引的基础集合。索引是一个最重要的物理数据库设计功能之一(对于 INSERT、UPDATE 和 DELETE 操作而言,索引和即时的 MQTs 会产生负面影响)。

    数据集群和 MDC

    • MDC 可用来提高查询性能以及转入和转出数据。

    数据库分区(不共享哈希分区)。

    • 对于大型 BI 应用程序,可以使用数据库分区以提高其可扩展性。
    • 在选择分区键时,同时还要关注分区键值的高基数并提高连接中的表并置。对不共享数据库来说,使用哈希分区完全是为了数据仓库。

    表(range)分区

    • 使用范围集群表(RCTs)来进行对数据的快速直接的访问。
    • 基于转入和转出特点来设计表分区。根据 month 或财季来进行分区是很好的策略。

    UNION ALL 视图分区

    • UNION ALL 视图,允许视图底层的不同对象有不同的特征。在一般的同质性中,它提供了更干净也更好维护的架构。然而,也有例外,那就需要有混合和匹配的能力。当某个范围的数据需要复制而其他范围不需要复制时,使用 UNION ALL 视图可从压缩中得到好处。
    • 较之 UNION ALL 视图,利用数据库分区来获得决策支持系统、商业智能、数据仓库和报告工作量的可扩展性更好。
    • 使用表分区来提高恢复效率和转出效率。

    表分区和 MDC 的数据转入和转出

    • 使用表分区来转出或在一个单独的维度通过使用 MDC 来转入。

    在同一个数据库中的数据库分区,表(范围)分区,和 MDC

    • 在同一个数据库设计中,通过实现数据库分区、表分区和 MDC,来部署大规模的应用程序。

    MQTs

    • 对数据库分区和提高对聚合数据的查询访问,使用复制 MQTs 来提高连接中的匹配。
    • 通过维护 MQT 统计信息的数据来实时帮助查询编译器来查找 MQTs,定义参考完整性(包括 MQT 中被定义为 NOT NULL 的 FK 列),并定义函数依赖。避免有问题的 MQT 设计因为使用 EXISTS、NOT EXISTS 和 SELECT DISTINCK 子句而发生路由困难,除非 MQT 和查询非常匹配。

    对现有数据库使用 post-design 工具可以改善设计

    • 使用解释工具来帮助我们了解你的设计选择。
    • 使用 DB2 设计顾问程序来对物理数据库设计改善(索引、MQTs 和分区)生成计划。如果这样做了,需要提供输入一批查询而不是一次一个查询。这让设计顾问程序可以在整个工作负载中做出取舍。
    • 利用 DB2 9.5 工作负载管理器(WLM),、Query Patroller、快照脚本或语句事件监控器来自动抓取 SQL 语句以输入解释工具以及 DB2 设计顾问程序。

    介绍物理数据库设计

    数据库设计分三个阶段执行:

    1. 逻辑数据库设计,包括:收集需求和具体关系模型。
    2. 把逻辑设计转换成表定义(通常由一个应用程序开发人员执行),包括:部署前设计、表定义、规范化、主外键关系和基础索引。
    3. 部署后物理数据库设计(通常由数据库管理员执行),包括提高性能,减少 I/O 和精简管理任务。

    物理数据库设计涵盖了数据库设计中影响数据库在磁盘上的具体结构的各个方面,如上面的条目 2 和 3 。 虽然你可以独立于数据库最终使用的平台之外来进行逻辑设计,但是大量物理数据库属性仍依赖于目标 DBMS 的具体内容和语义。物理数据库设计包括下面的属性:

    • 表规范化
    • 表非规范化
    • 索引
    • 集群
    • MDC
    • 数据库分区
    • 范围分区
    • MQTs
    • 内存分配
    • 数据库存储拓扑
    • 数据库存储对象分配

    本文包含了所有属性,除了“数据库存储拓扑”和“数据库存储对象分配”这些属性包含在“数据库存储最佳实践”白皮书中。这篇白皮书以及在它里面涉及的文章可以在 DeveloperWorks 的信息管理区里面找到:(http://www.ibm.com/developerworks/data.)

    物理数据库设计和数据库本一样古老,第一个关系型数据库原型诞生于 1970 年。因为关系型数据库很先进,所以新的技术被引入以提高操作效率。数据库设计的最初问题是关于表规范化和索引选择,这两点都会在后面讨论到。

    今天,我们可以通过正确的分割数据、分布数据和提高索引数据来达到 I/O 降低的目的。所有这些创新都提高了数据库的能力,扩展了物理数据库设计的范围,以及增加了设计选择,这也导致优化数据结构变得更复杂。虽然引入新的物理数据库设计能力主导了 1980s 和 1990s ,然而从那以后就致力于通过自动化和最佳实践来简化过程。

    绝大多数物理数据库设计功能和属性都以在运行中减少 I/O 使用为主要目的。然而,在较低程度上,有的在“物理设计方面”帮助提高管理效率和减少 CPU 或网络使用。另外,在 DB2 分区环境中,数据库设计会影响并行度,例如,并行查询处理。

    DB2 9.5 数据库系统提供的功能以及工具,已经实现了本文中的最佳实践。

    读者要求

    你熟悉所描述的物理数据库设计功能。因此,本文对于每一个功能只有很简略的描述。本文关注的是应用这些功能的最佳实践。关于每个功能的详细信息请参考 DB2 产品文档。

    物理数据库设计的目的

    一个高质量的物理数据库设计要达到下面的目标:

    • 将 I/O 减到最小。
    • 均衡设计功能,同时用来优化查询性能、事务性能和维护操作。
    • 提高数据库管理效率,比如数据的转入和转出。
    • 提高管理任务性能,比如索引创建或备份和恢复的过程。
    • 把备份和恢复所需要花费的时间减到最小。

    表规范化和非规范化的最佳实践

    表规范化就是通过减少它的关系直到最简的表格。在建立一个逻辑上的关系型数据库设计中,表规范化是一个关键步骤。规范化有助于避免冗余和不一致的数据;它通常是一个逻辑上的数据模式练习,整个结果在物理设计中实现。

    部署一个规范化设计有如下目标:

    • 消除冗余数据,例如在多个表中存储相同的数据。
    • 通过在表中只存储相关数据来强制有效数据的依赖,并把关系数据拆分到多个相关表中。
    • 将系统在数据结构和未来增长中的灵活性最大化

    规范化

    规范化的两到三个主要策略是:

    • 第三范式在在线交易处理(OLTP)和很多通用数据库中使用,包括企业数据仓库(也叫做原子仓库)。
    • 星型模式和雪花模式是规范化的三维模式,并且在数据仓库和 OLAP 中经常使用。

    第三范式(3NF)

    第三范式是由第一范式和第二范式中的规则组成的。下面是第三范式中的规则:

    • 消除重复组。为每个相关属性的集合建立单独的表,并为每个表建立一个主键。
    • 消除重复的列以及每个表中重复的数据。
    • 把应用到一个表中的多行柱形数据的子集,移动到不同的表中。
    • 在表之间用外键来建立联系
    • 消除那些与键值无关的列。如果属性不能解释一个键值,那就把它们移到另外一个表中。
    • 删除不依赖于主键的列

    数据库设计中的第一范式,第二范式和第三范式

    下面的图表显示了数据库设计的第一范式,第二范式,第三范式。

    非规范化模式

    非规范化模式

    第一范式:

    第一范式

    为了让非规范化模式遵守第一范式,重复的数据元素组、客户地址行和客户名称被规范化到不同的表中。

    第二范式:

    第二范式

    这个模式要遵守第二范式就必须遵守第一范式,并且所有属性必须完全依赖于一个组合键。

    第三范式:

    第三范式

    这个模式要遵守第三范式就必须消除所有传递依赖。当一个在非键值域的值取决于另一个非键值域,也就是非组合键的一部分中的值时,就发生了传递依赖。

    星型模式和雪花模式

    星型模式和雪花模式在数据仓库 BI 系统中已经变得非常普遍。星型模式的基础是从它的维度中分离出系统的事实表。维度是作为数据的属性来定义的,比如 location 或 customer name、或部分描述、事实表参考和数据相关的具体时间。

    例如,一个部分描述通常不会随着时间流逝而变化,因此它可以定义为一个维度。与此相反,部分每日销售是随时间变化的,因此是事实。之所以叫做星型模式是因为它的典型特点是一个保存了随着时间变化的大型的中央事实表,被一批维度表所环绕,其中存放着和事实表中事件条目的相关属性。

    雪花模式简单的扩充了星型模式。在一个雪花模式设计中,较低基数的属性经常从星型模式中的一个维度表移动到另外一个维度表中,并将这两个维度表建立起关系。

    非规范化

    和规范化相比,非规范化是压缩表数目的处理过程,因此有可能增加数据库中的冗余数据。非规范化可以非常有效的减少复杂性或进行表连接的数目,并通过减少表的数目来减少数据库的复杂性。非规范化的主要目的是将一个系统的性能最大化并降低系统管理的复杂度。

    IBM 分层数据结构

    IBM 分层数据结构为用户提供了多级的。每一层提供了不同的细节和数据摘要的级别来满足用户的需要,以便用户(分析者和执行人员)访问。数据年龄随着层次增加而增长(更多的表和每个表更少的数据)。这个结构是专门为混合工作负载、查询性能、快速合并新的数据源以及部署新的应用程序而设计的。

    分层结构启用并行的装载、查询、归档和维护,而不需要牺牲查询性能。多级数据可以用于多种类型的分析。

    图 2 显示了 5 层的 IBM 分层数据结构

    图 2. IBM 分层数据结构

    IBM 分层数据结构

    利用这个模式,数据库仓库管理员可以:

    1. 使用可视工具来优化设计多层数据仓库模式。
    2. 使用首选抽取、转换和装载(ETL)软件用来自于无数企业数据源规模、速度和丰富的变化来大块装入数据仓库的中间过渡层。
    3. 使用 SQL 数据仓库工具(SQWs)维护性能上和商业访问层上的分析结构,- 或者替换在仓库中的手工编码的 SQL 流程。

    关于这个多层结构的更多细节信息请参考“ Best Practices for Creating Scalable High Quality Data Warehouses with DB2 ”。

    使用以下规范化和非规范化的最佳实践:

    • 只要有可能就对大多数 OLTP 和通用数据库使用第三范式设计,以在系统设计中保持灵活性。这是经过考验的规范化模式。
    • 对于那些性能要求非常高的数据仓库和数据集市,一个星型模式或雪花模式通常是最优的维度查询处理模式。当然,还要验证星型模式或雪花模式是否遵守你设计的在规范化的逻辑数据模式中的关系。在“ Best Practices for Data Life Cycle Management ”白皮书中有更多关于关系型数据结构的用户的逻辑模式信息。
    • 有广泛基础的数据仓库可以有很多用途,比如图 1 所示的可操作数据仓库、报告、OLAP 和立方体、,通常使用分层数据结构。分层数据结构是一个强大的范式,在这里,有限的文字不能详细描述。详细信息请参见“更多阅读”章节。
    • 考虑非规范化那些非常小的表,每个记录长度是 30 或更少字节。数据库多余的表增加了查询的复杂性和管理的复杂度。

    索引设计最佳实践

    索引对性能来说非常关键。数据库使用它们以达到下列目的:

    • 应用谓词来提高快速查询数据在数据库中的位置,减少查阅的行数。
    • 避免 ORDER BY 和 GROUP BY 子句产生的索引。
    • 引导连接的顺序。
    • 提供 index-only 的访问,这避免了访问数据页的成本。
    • 作为在关系数据库中强制唯一性约束的唯一方法。

    然而,索引需要额外的硬件资源:

    • 它们增加 UPDATE、INSERT、DELETE 和 LOAD 操作的额外的 CPU 和 I/O 成本。
    • 它们会增加准备时间,因为它们为优化器提供更多的选择。
    • 他们会使用非常多的磁盘存储。

    在 DB2 数据库系统中,一个 B+ 树结构被用作实现索引的底层结构。所有数据都存储在叶子节点,而且键值被随意的链接进一个双向方式中以提供双向的索引扫描。如果指定了 DISALLOW REVERSE SCANS,那么索引不能被反向扫描(不过物理上它是和一个 ALLOW REVERSE SCANS 索引是一样的)。

    集群索引

    集群索引(特殊索引)告诉数据库管理器这个表中的索引对象必须根据索引定义在磁盘上被集群在一起。例如,如果集群索引被定义在一个日期键上,那么,数据库管理器将尝试在磁盘上、在表对象中,把有相同日期的记录存储在彼此周围。

    图 3 中的表定义了两个基于记录的索引:

    • 一个是在“ Region ”上的集群索引 。
    • 另外一个索引在“ Year ”上。
    图 3. 一个集群索引的普通表

    一个集群索引的普通表

    这个集群的价值是,如果后续查询有在集群属性上的谓词,就只需要运行已经大幅度减少的 I/O 。例如,一个以日期为条件进行的销售查询,如果被查询的日期的相关记录就在附近,因此这只需非常少的 I/O 。

    然而,集群索引不仅仅是到数据库的一个指示器。而且当新数据被插入到数据库中时,DB2 内核将尝试把这些记录放在有相同或相似属性的记录附近。如果空间不可用的话,新增加或更改的记录会被重定向到其他非集群位置(也就是说不在相关记录附近)。

    当一个 INSERT 发生时(或对集群键值的一个 UPDATE)DB2 内核会扫描集群索引来为这个记录判断一个恰当的位置。因此,在一个有集群 INDEX 的表上的 INSERT 和一些 UPDATE 操作会导致对索引访问的开销,这在非集群索引上不会发生。

    因此,集群索引提供了接近于集群的集群,而且随着时间推移数据经常变得不在集群。 REORG 实用工具可以把数据记录重组为完美的集群顺序,虽然这可能是一个费时而且日志密集的操作。

    要创建一个集群索引,如下面例子显示的,只需将 CLUSTER 键值简单添加到创建索引语句中,在这里一个集群索引 MyIndex 将基于 T1 表中的 C1 列被创建。每个表中只能有一个集群索引。

    CREATE INDEX MyIndex on T1 (C1) CLUSTER
    

    随着时间的推移数据集群会影响使用集群索引的时间,所以使用 MDC 进行集群是最佳实践的首选,因为它在任何时候都能保证集群,并提供了同时对多个维度进行集群的并行性。请参见在后面的关于如何判断使用何种方法中对 MDC 的讨论。

    利用下面定义索引的最佳实践:

    • 在一个数据库中对每一个主键(PK)和大多数外键(FKs)建立索引。大多数连接发生在主键和外键之间,因此只要有可能就在所有 PKs 和 FKs 上建立索引。索引建立在 FKs 上可以提高参照完整性检查的性能。

    • 为主键明确提供一个索引。如果不指定,DB2 数据库管理器则会用系统生成的名字自动对主键建立索引。自动生成索引时,系统生成的名字很难管理。

    • 在 WHERE 子句中频繁出现的列,是建立索引非常好的候选。如果谓词只提供了很小的过滤的情况下是这个规则的一个例外。一个例子就是一个不等式,比如 WHERE cost <>4 。因为只提供有限过滤,所以在不等式中很少使用索引。

    • 在用于相等和范围查询的列上指定索引。

    • 为每个和维度连接的事实表中列的集合创建一个索引。这些列不必是外键的一部分。创建索引允许星型连接访问来计划使用动态位图说因 ANDing 。考虑在事实表的列组合上创建索引。

      例如,如果 PRODKEY 和 STOREKEY 连接到生产并且各自存储维度,那么可以考虑在 PRODKEY、STOREKEY 上创建一个索引。这将有助于一个hub 或 cartesian星型连接访问计划。

    • 使用 db2pd 命令,这显示了从最高到最低使用索引的时间。这有助于判断哪个索引经常被使用,例如:

      db2pd -db MY_DATABASE -tcbstats index

      该指数是参照使用 IID,对于这个索引,它能被链接到 SYSIBM.SYSINDEXES 的 IID 。输出的最后(下面分两段显示)是一个索引统计信息的列表。“ Scans ”显示每个索引上的读取访问,这个输出中的其他指示器提供了对这个索引的写 / 更新活动的深入理解。

      输出的左边:

      输出的左边:

      输出的右边:

      输出的右边:

    • 使用 DB2 设计顾问程序来显示对一个特定工作负载发现哪个索引永远不会被访问,就可以被删除。

    • 仅在绝对必要的情况下添加索引。记住,索引会极大的影响 INSERT、UPDATE 和 DELETE 性能并需要存储空间。

    • 为了减少频繁重组的需求,在一个集群索引创建时指定一个恰当的 PCTFREE 在索引的每个叶子节点创建时留出一定比例的空闲空间。在未来的活动中,记录可以插入到索引中,以便减少页分割的可能性。页分离导致索引页面不连续,这会导致索引页面预取的效率降低。

      **注意:**指定的 PTCFEE 是在你创建关系型索引或重组时就保留出来的。

      删除并重建或重组关系型索引,会创建一个新的页面集合且基本上连续并能提高索引页面预取性能。虽然会更耗时和耗费资源,但是 REORG TABLE 使用工具同样确保数据页的集群。在扫描访问大量数据页面时,集群对索引有很大的帮助。

    • 使用范围或用 ORDER BY 子句来测试查询,以判断集群的维度。

    • 集群索引会导致 INSERTs 和一些 UPDATE 的额外的开销。如果你的工作负载需要使用大量索引,你需要权衡集群对查询的好处和给 INSERTS 和 UPDATES 带来的额外开销。在大多数情况下,好处远远大于成本,不过也有例外。

    • 避免或删除冗余索引。 一个冗余索引的例子就是:一个索引有一个account number 列,而另外一个索引也有和第一个索引相同的 account number 列。有相同或相似的列的索引会使查询优化更复杂,占用存储,并严重影响 INSERT、UPDATE 和 DELETE 性能,而且通常没有好处。

      虽然 DB2 数据库系统提供了动态位图索引、索引 ANDing、和索引 ORing,但是如果这些列经常在 WHERE 子句中被指定,最佳实践是指定组合索引也称作被多列索引。

    • 为一个组合索引选择引导列。引导列应该反映出经常被 WHERE 子句使用的列。 WHERE 子句中使用的引导列也被称为“匹配索引查询”,DB2 数据库系统只能从上到下的访问 B-tree 索引。如果一个索引的引导列不在 WHERE 子句中,优化器可能仍然会使用这个索引,但是优化器会被强制采用一个针对整个索引的“不匹配索引扫描”。

    参考

    https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0905physicaldesign1/index.html

    展开全文
  • 物理数据库设计 - 文件是否应该存储在数据库中  多媒体文件已经广泛应用在很多程序当中。比如用户的头像,汽车的产品图片等等。  从我个人以往的经验来看,将文件的路径存储入数据库,然后文件...

      多媒体文件已经广泛应用在很多程序当中。比如用户的头像,汽车的产品图片等等。

      从我个人以往的经验来看,将文件的路径存储入数据库,然后文件本身存储于硬盘当中已是万年不变的解决方案。

      其实,存储图片路径与存储图片文件本身,两种方案都有很好的立足点,但是大部分程序员都是将文件存储于数据库之外。虽然,这种方法没有什么大问题,但的确是存在一定的风险的。

    一、问题描述

      下面就来论述一下关于将文件存储于数据库外部的缺点。

      1、垃圾回收问题。

        当你想删除一张图片时,你只能够删除掉数据库中的记录,图片文件是没有办法由SQL语句删除的,你必须在你的高级程序中维护着这些图片。保证在删除数据行的同时删除掉图片文件。

      2、文件不支持数据库备份工具

        当我们备份数据库的时候,是没有办法连同外部文件一起备份的。所以在备份时,必须使用文件系统备份工具来同时备份外部图片。

      3、文件不支持SQL的访问权限设置

        外部文件会绕开通过GRANT和REVOKE SQL语句设定的访问权限。SQL权限管理这对表和列的访问,但它们并不能应用到外部文件。

      4、图片路径不是SQL数据类型

        图片路径并不是SQL的数据类型,它只是一个字符串,数据库并不会验证这是否是一个有效的文件路径。如果这个文件被重命名、移动或者删除了,数据库并不会自动更新对应的路径。

      同样文件存储于数据库外部也有其优点:

      1、数据库在没有图片的时候能够经意很多,因为图片相比于简单数据类型说更大。

      2、当不包含图片时,备份数据库会更快并且备份的文件更小,虽然必须执行一次额外的文件备份,但比备份一个大型数据库更容易管理。

      3、图片如果存储于数据库之外,那么对图片的预览或者编辑就能够使用更简单直接的处理方式。比如,经常要编辑或修改图片,那么存储于数据库之外就是很好的选择。

    二、解决方案 在需要时使用BLOB类型

      存储时,使用二进制BLOB存储文件。

      图片存储在数据库中,不需要额外加载,也就不会存在文件路径不正确的风险。

      删除一条记录的同时也删除了图片。

      更新记录的时候会加锁,因此不会有别的客户端并发更新图片。

      数据库备份会包含所有的图片。

      SQL权限控制对图片也有效。

      各种优缺点都有,依据自己的程序,斟酌选择正确的解决方案吧。

     
     
     
    0
    0
     
    (请您对文章做出评价)
     
    « 上一篇:物理数据库设计 - 限定列的有效值
    » 下一篇:查询反模式 - 正视NULL值
    posted on 2015-07-17 17:18 铭轩同学 阅读(...) 评论(...) 编辑 收藏

    转载于:https://www.cnblogs.com/mingxuantongxue/p/4655182.html

    展开全文
  • 我的妹妹小埋18岁,成绩优异体育万能。然而,只有我知道,小埋经常让我帮她辅导功课。本篇教程通过我与小埋的对话的方式来谈一谈物理数据库设计

    hello大家好,上次学习了逻辑数据库设计(点击查看),今天我们来学习物理数据库设计。教妹学数据库,没见过这么酷炫的标题吧?“语不惊人死不休”,没错,标题就是这么酷炫。

    我的妹妹小埋18岁,校园中女神一般的存在,成绩优异体育万能,个性温柔正直善良。然而,只有我知道,众人眼中光芒万丈的小埋,在过去是一个披着仓鼠斗篷,满地打滚,除了吃就是睡和玩的超级宅女。而这一切的转变,是从那一天晚上开始的。

    从此之后,小埋经常让我帮她辅导功课。今天她想了解物理数据库设计。本篇教程通过我与小埋的对话的方式来谈一谈物理数据库设计。

    物理数据库设计

    设计步骤

    1. 设计步骤1:分析数据库负载

    2. 设计步骤2:选择关系数据库的存取方法

    3. 设计步骤3:设计关系数据库的物理存储结构

    索引

    1. 构成
    • 索引键(indexkey):索引根据一组属性(索引键)来定位元组

    • 索引记录了元组的索引键值与元组地址的对应关系

    • 索引项(indexentry):索引中的(键值,地址)对

    • 索引中的索引项按索引键值排序
      在这里插入图片描述

    1. 分类
    • 主索引与二级索引,根据索引键是否是关系的主键

    • 唯一索引与非唯一索引,根据索引键值是否重复

    • 聚簇索引与非聚簇索引

    聚簇索引:索引中存储的是元组本身。一个关系上只能有一个聚簇索引。
    非聚簇索引:索引中存储的是元组地址

    1. 创建索引
    • 创建主索引

    • 创建二级索引

    • 创建唯一索引

    1. 删除索引

    索引的数据结构

    B+树

    1. B+树是大多数RDBMS所使用的索引结构
    2. B+树是一棵平衡多叉树,所有叶节点的深度都相同
    3. 索引项全部存储在B+树的叶节点中,并按索引键值排序存储

    B+树索引的限制

    1. 必须从索引的最左属性开始查找
    SELECT*FROMStudentWHERESage=19;
    不能在该索引上执行这个查询
    
    1. 条件中不能包含表达式
    SELECT*FROMStudent
    WHERESname='Elsa'AND2020-Sage=2000;在索引上只能根据条件Sname=’Elsa’进行查找,在返回的元组上验证条件2020-Sage=2000
    
    1. 不能跳过索引中的属性
    SELECT*FROMStudent
    WHERESname='Elsa'ANDSsex='F';在索引上只能根据条件Sname=’Elsa’进行查找,在返回的元组上验证条件Ssex=’F’
    
    1. 如果查询中有关于某个属性的范围查询,则其右边所有属性都无法使用索引查找
    SELECT*FROMStudentWHERESname='Elsa'ANDSageBETWEEN18AND20ANDSsex='F';在索引上只能根据条件Sname=’Elsa’ANDSageBETWEEN18
    AND20进行查找,在返回的元组上验证条件Ssex=’F’
    

    哈希索引

    1. 哈希索引是基于哈希表(hashtable)实现的
    2. 哈希表中的索引项是(索引键值的哈希值,元组地址)
    3. 哈希索引只支持对所有索引属性的精确匹配
      在这里插入图片描述

    哈希索引的限制

    1. 哈希索引不支持部分索引属性匹配
    SELECT*FROMStudentWHERESage=19;(不能使用索引)
    
    1. 哈希索引只支持等值比较查询(=,IN),不支持范围查询
    SELECT*FROMStudentWHERESname='Elsa'AND
    Sage<19ANDSsex='F';(不能使用索引)
    
    1. 哈希索引并不是按照索引值排序存储的,所以无法用于排序
    2. 哈希索引存在冲突问题

    索引的设计过程

    设计技巧1:伪哈希索引

    尽管有些存储引擎不支持哈希索引,但我们可以模拟哈希索引。

    • 优点: 查询速度快
    • 缺点: 仅支持等值查询,不支持范围查询I需要改写查询
    • 需要在数据更新时维护哈希值属性
      在这里插入图片描述

    设计技巧2:前缀索引

    1. 当索引很长的字符串时,索引会变得很大,而且很慢。当字符串的前缀(prefix)具有较好的选择性时,可以只索引字符串的前缀
      在这里插入图片描述
    2. 缺点
    • 前缀索引不支持排序(ORDERBY)
    • 前缀索引不支持分组查询(GROUPBY)
    1. 例子
      在这里插入图片描述

    设计技巧3:单个多属性索引vs.多个单属性索引

    1.
    在这里插入图片描述

    1. 多个单属性索引的缺点
    • 效率没有在单个多属性索引上做查询高
    • 索引合并涉及排序,要消耗大量计算和存储资源
    • 在查询优化时,索引合并的代价并不被计入,故“低估”了查询代价

    设计技巧4: 索引属性的顺序

    当不考虑排序(ORDERBY)和分组(GROUPBY)时,将选择性最高的属性放在最前面通常是好的,可以更快地过滤掉不满足条件的记录。

    设计技巧5: 聚簇索引

    1. 优点
    • 相关数据保存在一起,可以减少磁盘I/O

    • 无需“回表”,数据访问更快

    1. 缺点
    • 设计聚簇索引是为了提高I/O密集型应用的性能,如果数据全部在内存中,那么聚簇索引就没什么优势了

    • 聚簇索引上元组插入的速度严重依赖于元组的插入顺序

    • 更新聚簇索引键值的代价很高,需要将每个被更新的元组移动到新的位置

    • 如果每条元组都很大,需要占用更多的存储空间,全表扫描变慢
      在这里插入图片描述

    设计技巧6: 覆盖索引

    如果一个索引包含(覆盖)一个查询需要用到的所有属性,则称该索引为覆盖索引(coverageindex)

    避免不合理地使用索引

    一方面要设计好的索引,另一方面要避免不合理地使用索引
    若不合理地使用索引,则不但不会让查询变快,反而会使查询变慢
    在这里插入图片描述

    查询改写

    查询优化器不能保证总是找到好的查询计划
    用户不能给DBMS指定查询计划
    用户只能通过添加索引或改写查询来影响查询优化器的决策
    如果基于现有索引对查询进行改写就能获得好的查询计划,就没必要添加新的索引
    如何改写查询取决于查询优化器的实现
    在这里插入图片描述

    物理存储结构的设计

    1. 尽量使用可以正确存储数据的最小数据类型
    • 原因:最小数据类型占用空间更少,处理速度更快

    • 例:INTEGER或INT占4字节;SMALLINT占2字节;TINYINT占1字
      节;MEDIUMINT占3字节;BIGINT占8字节

    • 例:使用VARCHAR(5)和VARCHAR(100)来存储’hello’的空间开销是一
      样的;但在排序时,MySQL会按照类型分配固定大小的内存块

    1. 尽量选择简单的数据类型
    • 原因:简单数据类型的处理速度更快

    • 例:用DATE、DATETIME等类型来存储日期和时间,不要用字符串I例:用整型来存储IP地址,而不是用字符串

    1. 若无需存储空值,则最好将属性声明为NOTNULL
    • 原因:含空值的属性使得索引、统计、比较都更复杂

    标识符类型的选择

    为标识符属性选择合适的数据类型非常重要

    • 标识符属性通常会被当作索引属性,频繁地进行比较- 标识符属性通常会被当作主键或外键,频繁地进行连接
    • 在设计时,既要考虑标识符属性类型占用的空间,还要考虑比较的
      效率
      整型:最好的选择
    • 占用空间少- 比较速度快
    • 可声明为AUTO- NCREMENT,为应用提供便利
      ENUM和SET类型:糟糕的选择
    • MySQL内部用整型来存储ENUM和SET类型的值,占用空间少
    • 在比较时会被转换为字符串,比较速度慢
      字符串型:糟糕的选择
    • 占用空间大- 比较速度慢

    总结

    咱们玩归玩,闹归闹,别拿学习开玩笑。

    物理数据库的重点在于索引的设计,通过对比B+树索引哈希索引进行深入思考,同时结合实例记住索引的六大设计技巧

    备注

    在数据库中, true,反过来不一定是false,可能是unkown。null表示不知道,它不是c语言的null。
    null的比较会得到null的结果 null是无法比较的。所以对数据库要有约束,若不能为空则要声明

    NULL就好像你和朋友出去吃饭,点菜时她说随便。

    在这里插入图片描述

    展开全文
  • 物理数据库设计 - 限定列的有效值 一、说明问题  其实这篇非常简单,因为大家都是用这个方法解决的,我决定用自己的语言来描述清楚这一个问题。  假设,我们有一个列,这个列只能够取某些有效值...

    一、说明问题

      其实这篇非常简单,因为大家都是用这个方法解决的,我决定用自己的语言来描述清楚这一个问题。

      假设,我们有一个列,这个列只能够取某些有效值。比如一个用户表,我们有一个姓氏列,我们需要限定里面的值为中国的姓氏,比如:赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨。

    二、反模式

      对于这个问题,其实只有初学者可能会用这个方法,就是使用CHECK约束或者触发器来限定列的值,比如:

      CHECK (lastname IN ('','','','')); 

      这样做的缺点如下:

      1、获取所有可选值有困难,假设我要做个下拉列表,让用户选择可供输入的姓氏,那么SQL语句就复杂了,你需要查询系统视图。

      2、添加可选值,假如我们要增加一个外国的姓氏。那么你需要修改CHECK约束或触发器。

      3、删除可选值,假设从今天起又不支持外国姓氏了,但是数据库中又已经有了一个外国姓氏,你不得不保留这一个废弃值。但下拉列表框中又不能再让用户选,这下麻烦大了。

      4、移植性差,CHECK,约束,触发器的语法在各种数据库不相同,移植难度大。

    三、解决方案

      创建一张检查表,每一行包含允许姓氏出现的值,然后定义一个外键约束。

      这个你懂的,不再废话。

      除非,你很确定这些值不会变时,你可以使用CHECK约束或触发器等手段实现。例如性别:男,女。否则,还是使用检查表的方式比较好。

     
     
     
    0
    0
     
    (请您对文章做出评价)
     
    « 上一篇:NHibernate 帮助类(单例实际运用)
    » 下一篇:物理数据库设计 - 文件是否应该存储在数据库中
    posted on 2015-07-17 17:16 铭轩同学 阅读(...) 评论(...) 编辑 收藏

    转载于:https://www.cnblogs.com/mingxuantongxue/p/4655178.html

    展开全文
  • 物理数据库设计 瓶颈是指限制了系统性能的资源,总是存在的。在排除一个瓶颈的时候,具有转移瓶颈的效果,运气好的话,转移一个瓶颈时,新的瓶颈会宽一些。像SPECvirt测试,在转移瓶颈的时候却可能出现瓶颈转移到了...
  • 物理数据库设计最佳实践,第 2 部分 MDC、数据库分区、视图以及后设计工具介绍https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0909physicaldesign2/index.html?ca=drs-#icomments) 系列内容:...
  • 物理数据库设计的目的:将数据的逻辑描述转化为存取数据的技术规格说明。设计的目标是设计合理的存储数据的方法,以提供足够的性能,并确保数据库的完整性,安全性和可恢复性。 物理数据库设计不包括实现文件和...
  • 作者:developerWorks 中国网站编辑团队, 编辑, IBM 内容提要 物理数据库设计是影响数据库性能的一个最重要的...物理数据库设计涵盖了所有和数据库物理结构相关的设计功能,比如表规范化和反规范化、索引、物化...
  • 从我个人以往的经验来看,将文件的路径存储入数据库,然后文件本身存储于硬盘当中已是万年不变的解决方案。其实,存储图片路径与存储图片文件本身,两种方案都有很好的立足点,但是大部分程序员都是将文件存储于...
  • 物理数据库设计——索引、视图和存储技术》作者:Sam LightstoneToby TeoreyTom Nadeau译者:吴骅 王学昌 韩潼瑜ISBN:9787302239314定价:42.00元开本:185*260出...
  • 本书全面讲述数据库物理设计方案,主要包括物理数据库设计概况,基本索引方法,查询优化和方案选择,选择索引,物化视图选择,无共享分区,范围分区,多维群集,相互依赖的问题,物理设计探索中的计数和数据抽样,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,761
精华内容 1,904
关键字:

物理数据库设计