2021-数据库开发-Exam0-复习提纲

Exam0-历年试卷

1. 选择题

1. 【2020】考试选择题来自平时教学立方的选择题

2. 简答题

2.1. B+ Tree索引是大多数DBMS缺省的索引类型，请详细描述一下几个问题

1. B树的结构：
   1. 有K个子树的中间节点包含K个元素，每个节点不保存数据，只保存索引，所有的数据都存储在叶子节点。
   2. 所有叶子节点中包含了全部元素的信息，以及指向含这些元素记录的指针，并且叶子节点本身按照关键字大小自小而大顺序链接。
2. B树支持的查询
   1. 全键值查询 Where x = 123 (depth + 1次的固定次数)
   2. 键值范围 Where 45 < x < 123 (先进行x=45，然后顺序读取直到x>=123)
   3. 键前缀查找 where x LIKE J%’
3. B树的使用范围
   1. 仅需要通过索引访问基本表的一部分(检索的结果集与集体的百分比为10%以下)
   2. 如果要处理表中的多列，可以使用索引而不使用表
4. 为什么系统的为外键构建索引是普遍的要求
   1. 建立外键索引可以更快速地保证数据的一致性：比如A持有B的外键，B删除记录时需要检查A中的相应行，如果没有外键索引的话则需要对A进行全表遍历，而建立外键索引则可以更快速的完成。
   2. 建立外键索引可以避免死锁：还是如上所说，如果对A全表遍历耗时很长，可能会导致其他进程与之死锁导致双双失败。
   3. 例外：如果表很少被修改或者表很小，则可以不必建立外键索引。

2.2. 试简要描述DBMS中SQL语句的执行过程，并简单对各个步骤锁花费的代价大小进行描述和比较

1. 语法分析：语法是否符合规范、表达式是否有含义
2. 语义分析：检查语句中的数据库对象是否存在、检查用户权限
3. 执行解析：解析部分是SQL优化过程中最消耗资源的部分
   1. 优化器对每一个表达式的等价变化生成解析树，然后进行评估，由优化器选择一个最优的执行路径来生成执行计划。
   2. 执行路径选择中会经历
      1. 视图转换：将设计视图的查询语句转换为相应的对基表查询语句
      2. 表达式转换：将复杂的SQL表达式转换为较简单的等效连接表达
      3. 选择优化器：不同的优化器一般产生不同的“执行计划”
      4. 选择连接方式表：Oracle有三种连接范式，对多表连接Oracle可选择适当的链接方式
      5. 选择连接顺序：对多表连接Oracle可选择适当的连接方式，
      6. 选择数据的搜索路径：根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他方式。
4. 之后将选定的执行计划导入到执行引擎后在数据库中执行查询。
5. 将查询得到结果集进行返回

2.3. 简述查询优化器的工作原理和局限性

1. 工作原理：请参照执行解析过程中的各个步骤
2. 局限性：
   1. 查询优化器不能发现SQL本身表达逻辑的错误
   2. 查询优化器不能优化查询的中间结果集

2.4. 请解释硬解析和软解析的含义和区别

1. 分为硬解析和软解析。
   1. 硬解析指使用优化器对SQL进行优化，将SQL转化为一些等价语句，并选择代价最小的语句生成执行计划。
   2. 软解析是指共享池中已经存在有对应的执行计划，则不再进行优化，直接使用该执行计划。
   3. 硬解析代价最大，软解析代价较小。
2. 若在共享池中没有找到已有的执行计划则进行硬解析，否则进行软解析。
3. 运行执行计划，返回执行结构。运行执行计划的代价根据SQL语句的不同可大可小。

2.5. 一个运行一段时间的大型数据库系统中有一条SQL语句变慢了，查询特别耗时间，猜测什么原因，你该怎么做？

1. 数据量增加对性能的预估
   1. 隐藏在查询背后对数据量的高敏感性
   2. 比如max()对高数据量的敏感，而直接引起子查询性能缓慢降低，必须使用非关联子查询。
2. 排序的影响:最关键的问题是排序的数据是否都在内存中(有无磁盘消耗)
   1. 字节数量而不是记录数量
   2. 也就是被排序的总数据量
   3. Join应该延后到查询的最后阶段：对尽可能少的数据进行排序
3. Join延迟到查询的最后阶段
   1. 例子：查询一年内的10大客户的名称和地址
   2. 目标，对尽量少的数据进行排序

2.6. DBMS中索引有很多种，请简要描述

• 什么是索引：索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。
• 哈希索引
  1. 哈希索引的结构：哈希索引是对索引键值进行哈希计算，将得到的结果作为键值创建索引
  2. 哈希索引的适用范围
     1. 哈希索引支持全键值查询、=、in、不等于等运算
     2. 哈希索引不支持部分键值匹配、排序、原字段顺序查询
  3. 如何避免哈希冲突：使用二次哈希法或者链表法(参考HashMap)

• 位图索引
  1. 位图索引的存储结构：
     1. 位图索引的索引存储指向多行的指针。
     2. 位图索引每次进行修改都会锁住全部索引，对于低选择字段建议不要修改或尽可能小的修改。
  2. 位图索引的用途
     1. 相异基数低：即字段可以取的值比较少
     2. 大量临时查询的聚合

• 位图联结索引(Bitmap join index)
  1. 位图联结索引的结构：允许使用另外某个表的列对一个给定表建立索引。
  2. 用途：为了解决位图索引中更新导致全表锁定的问题。

• 函数索引(function-based index)
  1. 函数索引的含义：函数索引也就是对F(x)的值构建索引，在通过对索引读取x所指向的记录行。
  2. 函数索引的用途
     1. 不区分大小写的查询：使用函数输入(构建一个全大写的函数索引)
        1. Creat index emp_upper_idx on emp(upper(ename))
        2. Select * from emp where upper(name) = 'KING'
     2. T、F的巨大差异下的索引：(True / False) 如何找到少量的F(函数索引：T映射到NULL，F映射到非空，然后对该函数建立索引，建立索引的结果全为False)
     3. 有选择的唯一性：Active的活动的名称不能相同Create unique index active_project_must_be_unique on projects(case when status = 'ACTIVE' then name end)，这样子只要有一个创建，另一个只能回滚。
• 反向键索引或逆向索引(inverse index)的含义和用途
  1. 含义：将索引的字段翻转过来作为索引的键值
  2. 用途：用来解决高并发下的系统生成键的创建和插入问题。

2.7. 请详细描述关于数据库范式和逆范式(或称为反范式)的以下几个问题

1. 什么是逆范式？逆范式是为了放弃规范化，控制冗余
2. 你认为判断何时该使用逆范式的条件有哪些？系统有相对比较低的修改率和较高的查询率时可以选择打破范式。
   1. 情况1：合并一对一关系，可能会导致NULL、大量空间浪费等问题。
   2. 情况2：一对多关系复制非关键字属性，例如订单的金额和货品的金额，一般会使用触发器来同步修改属性。
   3. 情况3：一对多关系复制外部关键字，如果实体A访问实体C一定要通过实体B，那么我们可以在C中复制关键字A，直接关联避开B
   4. 情况4：多对多关系复制属性：将title、用户名等信息添加到关联关系中
   5. 情况5：引入重复组：引入地址、电话号码等，主表中可以存放一个缺省的地址和电话号码来避开连接查询
   6. 情况6：创建提取表：合并基本表和查找表
   7. 情况7：分区

2.8. 为什么说关系数据库比层状/网状数据库更"科学"？

1. 层次性数据库最早出现：存在逻辑嵌套，而不是线性排列，适用于部分场景，耦合性过强限制了我们对数据的自由操作
2. 网状数据库(多层次连接)：灵活多了，但是数据操作还是困难。
3. 关系型数据库在灵活性、数据访问、数据的组织找到了很好的平衡点，是场景普适的存储方式，对于特定的存储不太合适。

2.9. 请举例描述邻接模型和物化路径模型将树状结构存储到关系表结构的设计方法，并通过不同的查询(包括自顶向下的查询、自底向上的查询、集合查询)来比较不同表结构设计下SQL的效率。

1. 父子结构（parent/child link）–tree structure
2. 主从结构（master/detail relationship）：通过外键，来形成主从结构
3. 差异
   1. 树状结构保存只需要一张表：代表层次的树。所有节点的类型都相同，节点的属性都相似，表(节点)和自己有主从关系而不是其他表
   2. 深度：主从结构没有深度的概念
   3. 所有权：主从结构可以明确外键完整性约束，但是树状结构不需要定义所有权
   4. 多重父节点：单一父节点描述父子关系(子节点引用父节点)，先解决单一父节点的树
4. 在数据库设计中，树通常三种模型
   1. Adjacency model 邻接模型
      1. 层次中父节点id作为子节点id的一个属性pid，不能确定兄弟节点的排序
      2. 难以处理的，是递归的
   2. Materialized path model 物化路径模型
      1. 将树中间的每一个节点和在树中的位置描述成数据的结合
      2. 是所有子节点的祖先节点的id的串联(1.2.3)
      3. 能够知道兄弟节点之间的排名，家谱
   3. Nested set model 嵌套集合模型 1996
      1. 每一个节点被赋予了一对数字(left number, right number)
      2. 父节点的左数字和右数字之间包含了它所有的子节点的左数字、右数字

2.10. 关系理论中的空值和实际关系数据库中控制的处理有何差异？

2.11. 高并发下的为了确保性能，锁的解决方案是什么样的？解决资源竞争有哪些方法？

1. 锁的解决方案
   1. 不要随便使用表级锁，尽量使用细粒度锁
   2. 只有使用了索引才能使用行锁
   3. 尽量缩短加锁的时间
      1. 需要频繁的提交，但是不建议批处理文件这么做
      2. 数据库开销最大的是日志的记录
2. 解决资源竞争的方法
   1. DBA解决方案：与业务逻辑弱相关或无关
      1. 事务空间（Transaction space）：调整事务锁占空间的大小，事务条目占用是重要原因，DBA可以增加分配给事务条目的空间来解决
      2. 可用列表（Free list）：insert操作在不同物理块中，可以借助存储管理手段
   2. 架构解决方案
      1. 分区（Partitioning）
      2. 逆序索引（Reverse index）
      3. 索引组织表（Index organized table）：原本资源竞争包括基本表和索引，但是现在索引和基本表合并了，可以降低冲突发生的位置
   3. 开发解决方案
      1. 调节并发数：限制最高的session数量
      2. 不使用系统产生键：如果键没有意义，那不妨使用随机值
3. 在insert和session等情况下的表现
   1. DBA：不显著
   2. 架构：索引强制约束，导致索引的竞争增加，导致CPU计算资源成为瓶颈
   3. 开发者：小范围生成随机数(2倍预计)会导致多次碰撞(还有redo的消耗)，大范围生成随机数则没有这个碰撞(100倍)

3. 重点：2021年

6道题目：1.25道的代码题，3.75道简答和论述，1道主观题(没有正确答案，10分主观，占比60%)

3.1. 不同存储引擎的索引处理方式

1. 对于B+树索引，不少数据库都有自己的处理方式，比如，MySQL中不同的存储引擎使用了不同的方式把索引保存到磁盘上，他们会影响性能。
   1. MyISAM使用前缀压缩以减少索引，而InnoDB不会压缩索引，(有啥差别? )
   2. MyISAM索引按照行存储的物理位置引用被索引的行，但是InnDB按照主键值引用行，(有啥差别?)

3.2. 定义结果集的查询条件：

1. 对过滤条件进行优化
   1. 好条件：能过滤掉不满足条件的数据多
   2. 坏条件：同样
2. exists和in可以暗示在哪里进行优化
3. 降低表连接次数
   1. 改写SQL
   2. 设计(反范式等)：1对1合并、1对多合并等等

3.3. 数据库的事务隔离级别 必考

4个隔离级别与脏读、不可重复读、幻读的关系，举例子的论述

3.3.1. 三种问题

1. 脏读：读到了其他事务未提交的数据

2. 不可重复读：执行SELECT操作时没有获得读锁或SELECT操作值执行完成后立刻释放了锁，而另一个事务对数据进行更新得到了不同的结果

3. 幻读；不可重复读的一种特殊场景，事务1两次Select检索一定范围内的数据，事务2在两次之间创建了一条新的符合检索条件的记录，导致两次查询的结果集不同。

3.3.2. 四种事务隔离级别

1. 未提交读：一个事务开始写数据时，另一个事务不可以写数据，但是可以读数据。
2. 已提交读：读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将禁止其他事务访问该行，会对该写锁一直保持直到事务提交。
3. 可重复读：介于已提交读和可串行化，是InnoDB的默认隔离级别。当使用可重复读隔离级别时，在事务执行期间会锁定该事务以任何方式引用的所有行。
4. 可串行化：要求在选定对象上的读锁和写锁保持直到完成事务结束后才能释放。

4. 扩展知识

4.1. 使用SQL需要考虑的因素

1. 获得结果集所需访问的数据量
2. 定义结果集所需的查询条件：过滤条件的效率有高有低，受到其他因素的影响很大
3. 结果集的大小：考虑用户体验，熟练的开发者应该努力使响应时间与返回的记录数成比例
4. 获得结果集所涉及的表的数量
   1. 表的数量增加导致优化器的优化难度指数型上升
   2. 复杂查询和复杂视图：当视图返回的数据远多于上级查询所需要的时候，就放弃使用该视图
5. 并发用户数
   1. 数据块访问争用（block-access contention）
   2. 阻塞(locking)
   3. 闩定(latching)
   4. 保证读取一致性(read consistency)

4.2. 预定义

1. substr(str, position, length)
2. length(str)
3. replace(str, source, targer)
4. str regexp ‘[^0-9a-zA-Z]’
5. concat_ws 连接字符串
6. TRAILING str1 str2 将str2中后缀str1移除，如果没有则不操作
7. coalesce(col, 0)将空值替换
8. case when[条件] then xxx else xxx end as xxx
9. 日期计算interval x day/month/year
10. date_add(col, const)
11. datediff(t1, t2)
12. date_format(xxx, “%a”) “Sat” “Sun”
13. last_day()
14. current_date
15. dayofyear()
16. month()
17. year()
18. monthname()
19. dayname()
20. union all 叠加结果集

# 求众数
select sal
from emp
where deptno = 20
group by sal
having count(*) >= all( select count(*)
                        from emp
                        where deptno = 20
                        group by sal )

# 求中位数
select avg(sal)
from (
   select e.sal
      from emp e, emp d
   where e.deptno = d.deptno
   and e.deptno = 20
   group by e.sal
   having sum(case when e.sal = d.sal then 1 else 0 end)
         >= abs(sum(sign(e.sal - d.sal)))
)x

4.3. 数据表的物理实现

1. IoT(Index-Organized Table) 索引组织表：其中的记录是排序的
2. 分区
   1. 循环分区：分区为各个磁盘的存储区域，保持更改带来的磁盘I/O操作的平衡
   2. 哈希分区：对分区键计算哈希值存放，不改善范围查询，负载均衡，提高并发能力
   3. 范围分区：非常适用于处理历史数据，按照范围来存储，设置Else分区来存放其他数据
   4. 列表分区：定制特定的解决方案
   5. 数据分区的最佳方法
      1. 整体改善业务处理的操作，才是选择非缺省的存储选项的目标
      2. 更新分区键会引起移动数据，但是应该避免这么做
         1. 实现服务队列
         2. 实现按请求分区
         3. 实现按状态分区
   6. 总而言之
      1. 除了堆文件之外的任何存储方法，都会带来复杂性
      2. 除了单库单表之外任何的存储方式，都会带来复杂性
      3. 选错存储方式会带来大幅度的性能降低

4.4. 应对大数据量

1. 可选的唯一方法：引入其他条件（例如时间范围）
   1. 设定上限(完成限制)
   2. 不是单纯的技术问题
   3. 还依赖于业务需求
2. 数据量增加后对性能的预估
   1. 隐藏在查询背后对数据量的高敏感性
   2. 比如max()对高数据量的敏感，而直接引起子查询性能缓慢降低，必须使用非关联子查询。
   3. 排序的影响:最关键的问题是排序的数据是否都在内存中(有无磁盘消耗)
   4. JOIN尽量延迟到查询的最后阶段
   5. 消除关联子查询、使用分区技术

4.5. 为性能而设计

1. 告诫
   1. 过分精益求精会使精力分散
   2. 给子类型表指定完全独立于父表主键的主键，是极其错误的
   3. 数据中存在隐含约束是一种不良设计
2. 规范化的价值
   1. 合理规范化的模型可应对需求变更
   2. 规范化数据重复降至最少
3. 操作模式（operating mode）
   1. 异步模式处理（批处理）
   2. 同步模式处理（实时交易）

5. 代码题

5.1. 代码题三

5.2. 代码题四

5.3. 代码题五

5.4. 代码题六