高级数据库开发技术-复习

1.index索引

B+能做的
• 全键值
• 键值范围
• 键前缀查找

索引目的：提高查询效率

索引的另一面（问题）

• 磁盘空间的开销
• 处理的开销
• 数据库系统处理的开销

索引的使用是否合理，首先取决于它是否有用
• 判断索引适用性的依据是检索比例（retrieval ratios）

什么时候应该使用B树索引
• 仅需要通过索引访问基本表的很少一部分行
• 如果要处理表中的多行，可以使用索引而不使用表（要访问的字段全部包含在索引中时）

索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大

B/B+树: 用在磁盘文件组织，数据索引和数据库索引。
Trie树(字典树): 用在统计和排序大量字符串，如自动机。
红黑树: 平衡二叉树，广泛用在C++的STL中。如map和set都是用红黑树实现的。

B树索引的结构：由根指向子节点，B树将索引按顺序存储，在叶节点保存索引到字段及其对应数据在表中的位置指针。从根节点开始搜索，通过节点中的指针进入下一级节点，通过比较要查找的索引值和节点中的指针大小选择进入哪个子节点，最终要么找到对应叶节点，要么没找到。叶节点中有指向表中数据的指针。
何时使用：
a、仅需要索引访问表中很少一部分行时，索引提供以原子粒度访问数据的功能，可以快速定位所查找数据的位置；
b、虽然查询行数比较多，当索引包含多个字段，而要访问的字段全部包含在索引中时，查找只需通过索引而不用访问全表时。

这些列不应该建立索引

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2. 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中， 结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3. 对于那些定义为 text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。
4. 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

2.分区

分区：
就是把一张表的数据分成N个区块
数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还是一张表。

特性：
分区能够提高并发性和并行性
从而增强系统架构的可伸缩性

循环分区：不受数据影响的内部机制，分区定义为各个磁盘的存储区域，可以看做是随意散布数据的机制，保持带来的磁盘 i/o 操作的平衡

分区的实现方式
哈希分区：把不同的列随机平均的分布到不同的物理环境，达到备份和恢复（写 undo 和 redo文件）效率高，降低错误回滚压力（为了管理）
范围分区：把字段的值分布到一个物理范围，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的
列表分区：把不同的列存到不同的物理环境，某列的值只有几个，容易按值进行分区

数据越聚集，检索效率越高，最多的是按时间来进行滑动窗口的分区。
小数据量分区会降低并发的访问性能，大数据量 —— 分区对并发的影响远小于查询带来的效率。

分区缺点

• 分区表相关：已经存在的表没有方法可以直接转化为分区表。
• 除了堆文件之外的任何存储方法，都会带来复杂性
• 选错存储方式会带来大幅度的性能降低
• 大数据量的并发写入更新效率较低
• 从本质上来说降低了并发的个数，但是在数据量非常庞大的情况下，降低并发所带来的缺陷远远小于分区所提高的性能
• 由于强制的数据聚合可能会导致其他数据的分散，所以不同的查询请求也可能会形成性能上的矛盾

分区的最佳方法

• 按什么字段进行分区要整体考虑，因为：更新分区键会引起移动数据，应该避免这么做。
• 当数据分区键均匀分布时，分区表查询收益最大

3.设计的原则

集中式存储VS分布式存储
在大数据环境中，数据存储在分布式文件系统（例如HDFS）上，而不是存储在中央服务器上。数据将分布在工作节点上，以便于处理。分布式文件系统更加安全和灵活。

集中式存储是指将所有数据都存储在同一个节点上。这样有助于提高数据库查询和修改的效率。但是集中式存储有很大的风险，若节点出现不可逆破坏，将导致数据库崩溃。比如地震导致数据库损坏。

分布式数据应用
定义：是指数据存放在计算机网络的不同场地的计算机中，每一场地都有自治处理能力并完成局部应用；而每一场地也参与(至少一种)全局应用程序的执行，全局应用程序可通过网络通信访问系统中的多个场地的数据。

结构：一个分布式数据库在逻辑上是一个统一的整体，在物理上则是分别存储在不同的物理节点上。一个应用程序通过网络的连接可以访问分布在不同地理位置的数据库。它的分布性表现在数据库中的数据不是存储在同一场地。更确切地讲，不存储在同一计算机的存储设备上。这就是与集中式数据库的区别。从用户的角度看，一个分布式数据库系统在逻辑上和集中式数据库系统一样，用户可以在任何一个场地执行全局应用。就好像那些数据是存储在同一台计算机上，有单个数据库管理系统（DBMS)管理一样，用户并没有什么感觉不一样。
分布式数据应用的使用场景往往是在组织或着地理上分布的单位的场合或者为了增加数据的可靠性而需要适当增加数据冗余程度的场合(所谓技术和组织两方面的原因)

适用：万维网

优点:
具有灵活的体系结构；
减少了单一节点的负担
适应分布式的管理和控制机构；
经济性能优越；
系统的可靠性高、可用性好；
局部应用的响应速度快；
可扩展性好，易于集成现有系统。
可以应对大规模的访问和高并发适用

缺点:
系统开销大，主要花在通信部分，远程数据的透明用访问代价很高；
复杂的存取结构，原来在集中式系统中有效存取数据的技术，在分布式系统中都不再适用；
不同数据源数据结合比较困难，存在复制数据的开销并且无法从诸如索引等的数据规划中收益
数据的安全性和保密性较难处理。
多个副本的一致性难以保证

集中式的优缺点与分布式正好相反，主要适用于组织或者地理分布集中、对数据库性能要求较高而对扩展性和可靠性要求相对较低的数据库系统。
定义：是指数据库中的数据集中存储在一台计算机上，数据的处理集中在一台计算机上完成。
数据集中化：提高数据库速度，并且在部署上服务离数据越近，速度会越快。Block使用充分，命中率高，所需io操作少
优点：在集中式数据库里，大多数功能（如修改、备份、查询、控制访问等）都很容易实现。

缺点是：
单点故障，会带来很大损失，解决方式是热备份或灾难备份。
当中心站点计算机或数据库系统不能运行时，在系统恢复之前所有用户都不能使用系统。
从终端到中心站点的通信开销是很昂贵的。
适合的应用缺少
对共享数据的集中控制会成为并发和吞吐的瓶颈
适用：ATM

4.SQL

SQL执行步骤

语法检查：检查 SQL 拼写是否符合语法规范
语义检查：检查访问对象是否符合存在及用户是否具有相应权限
解析：在共享池中检查是否有完全相同的之前完全解析好的，如果存在，跳过选择执行计划和产生计划，直接运行

• 硬解析：就是对提交的 SQL 完全重新从头进行解析，创建解析树，生成执行计划对 SQL 的执行来说是开销昂贵的动作，在很多项目中对功能相同的代码要保持一致性，用绑定变量
• 软解析：在共享池（shared pool）中找到了与之完全相同的 SQL 解析好的结果会跳过硬解析后面的两个步骤

执行计划：以缩排列表的方式显示 SQL 语句的执行步骤

使用 SQL 语句要考虑的因素

1. 数据总量
   Sql 考虑最重要的因素：必须访问的数据总量；没有确定目标容量之前，很难判断查询执行的效率

2. 定义结果集的查询条件
   好的查询条件：满足此条件的数据很少，可以过滤很多数据
   Where 字句：特别在子查询或视图中可能有多个 where 字句
   过滤的效率有高有低，受到其他因素的影响
   影响因素：过滤条件，主要的 sql 语句，庞大的数据对查询影响

3. 结果集的大小
   查询所返回的数据量，重要而被忽略
   取决于表的大小和过滤条件的细节
   例外是若干个独立使用效率不高的条件结合起来效率非常高
   从技术角度来看，查询结果集的大小并不重要，重要的是用户的感觉
   熟练的开发者应该努力使响应时间与返回的记录数成比例

4. 获得结果集所涉及的表的数量：表的数量会影响性能
   连接：太多的表连接（八张）就该质疑设计的正确性了；对于优化器，随着表数量增加，复杂度指数增长；编写太多表的复杂查询时，多种方式连接的选择失误几率很高
   视图：会掩盖多表连接的事实
   减少复杂查询和复杂视图

5. 并发的用户数（同时修改数据的用户数）
   设计时要注意：数据块访问争用，阻塞，闩定，保证读取的一致性
   一般而言，整体的吞吐量>个体响应时间
   数据存贮采用固定大小的区块，可以存取多条记录，I/O 交互简单，在内存与缓冲中好处理；但是当修改后的数据太长，则会进行迁移到另一个 block 存储；数据块的太大，会带来数据块的访问争用的问题，影响并发性能

查询优化器的启发式规则
1.选择运算应尽可能先做。最重要、最基本的一条。常常可以使执行代价节约几个数量级，使计算的中间结果大大变小
2.把投影运算和选择运算同时进行。如有若干投影和选择运算，并且它们都对同一个关系操作，则可以在扫描此关系的同时完成所有这些运算以避免重复扫描关系
3.把投影同其前或后的双目运算结合起来。没有必要为了去掉某些字段而扫描一遍关系
4.把某些选择同在它前面要执行的笛卡儿积结合起来成为一个连接运算。连接运算要比同样关系上的笛卡尔积省很多时间
5.找出公共子表达式

优化器：借助关系理论（关系代数）提供的语义无误的原始查询进行有效的等价变换，寻找最优路径，产生新能最优的执行方案
优化：在数据处理的真正被执行的时候发生
影响优化的因素：索引，数据的物理布局，可用内存大小，可用处理器个数，直接或间接涉及的表和索引的数据量
Sql 语句先执行关系操作，再执行非关系操作（order by）

优化器的有效范围

• 优化器需要借助数据库中找到的信息
• 能够进行数学意义上的等价变换
• 优化器考虑整体响应时间
• 优化器改善的是独立的查询语句
• 策略是：如果是若干个小查询，优化器会个个优化；如果是一个大查询，优化器会将它作为一个整体优化

5.数据库设计

邻接模型

1. 邻接模型：id，parent_id(指向上级)
   自顶向下查询，假设兄弟节点无序，主要用于单父节点。Connect by 相当容易实现

2. 特性：
   a) 插入、移动、删除节点快捷
   b) 只支持单父节点，不支持多父节点
   c) Connect by 容易实现
   d) 递归实现，用 oracle 的 with，表示出树的层次
   e) 删除子树较难
   f) 三种模型中性能最高，每秒返回的查询记录数最多，遍历一次，不是基于关系的处理，性能最好

物化路径

1. 物化模型：PathID（1，1.1，1.2，1.1.1，1.2.1，…），使用层次式的路径明确地标识出来，一般用字符串存路径。每一个节点都存储在树中的位置信息，它允许节点之间有顺序（因为路径的标识有顺序），比如家族族谱

2. 特性：
   a) 查询编写不困难，找出适当的记录并缩排显示算容易
   b) 计算由路径导出的层次不方便。
   c) 查询复杂度主要在路径字符串的处理
   d) 树的的深度要自己写函数计算，可以计算“.”的数目或者去掉“.”后字符串的长度
   e) 子节点有顺序，但不应该暗示任何兄弟节点的排序
   f) 会产生重复记录的问题
   g) 物化路径 path 不应该是 KEY，即使他们有唯一性
   h) 所选择的编码方式不需要完全中立
   i) 三种模型中性能中等