MySQL秋招复习

数据库理论

1、参照完整性被破坏时：

1）拒绝执行（No action）
2） 级联操作（Cascade）： 当删除或修改被参照表时的一个元组导致与参照表不同时，删除或修改参照表中的所有导致不一致的元组。
3）设置为空值： 当删除或修改被参照表时的一个元组导致与参照表不同时， 则将参照表中的所有造成不一致的元组的对应属性设置为空值。

2、查询处理步骤：

查询分析（语法）、 查询检查（语义）、查询优化和查询执行

3、选择（select）操作的实现

1. 全表扫描法
2. 索引扫描法

4、连接操作的实现

SELECT * FROM Student, SC WHERE Student.Sno=SC.Sno;

1. 循环嵌套算法
    ◼ 对外层循环(Student 表)的每一个元组(s)，检索内层循环(SC 表)中的每一个元组(sc)

   ◼ 检查这两个元组在连接属性(Sno)上是否相等

   ◼ 如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止。

2. 排序-合并算法
   ◼ 如果连接的表没有排好序，先对 Student 表和 SC 表按连接属性 Sno 排序

   ◼ 取 Student 表中第一个 Sno，依次扫描 SC 表中具有相同 Sno 的元组

   ◼ 当扫描到 Sno 不相同的第一个 SC 元组时，返回 Student 表扫描它的下一个元组，再扫描 SC 表中具有相同 Sno 的元组，把它们连接起来

   ◼ 重复上述步骤直到 Student 表扫描完

3. 索引连接算法

4. Hash Join算法

5、查询优化的优点（为什么需要查询优化）

1. 用户不必考虑如何最好地表达查询以获得较好的效率
2. 系统可以比用户程序的“优化”做得更好

6、代数优化

代数优化策略：通过对关系代数表达式的等价变换来提高查询效率

7、物理优化

物理优化就是要选择高效合理的操作算法或存取路径，求得优化的查询计划

事物与数据库恢复技术

事务处理技术包括数据库恢复技术和并发控制技术。
数据库恢复机制和并发控制机制是 DBMS 的重要组成部分。

事物的概念

• 事务： 是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。
• 事务和程序比较

1. 在关系数据库中，一个事务可以是一条或多条 SQL 语句,也可以包含一个或多个程序。
2. 一个程序通常包含多个事务

事物的特性

1. 原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做；
2. 一致性（consistency）：事务必须使数据库从一个一致性状态变成另一个一致性状态；
3. 隔离性（isolation）：一个事务的执行不能被其他事务干扰；
4. 持续性（durability）：也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的．

恢复的实现技术

恢复操作的基本原理：冗余

日志文件

日志文件的格式：

1. 以记录为单位：
   日志文件中需要登记的内容包括：
   ① 各个事务的开始标记(BEGIN TRANSACTION)
   ② 各个事务的结束标记(COMMIT 或 ROLLBACK)
   ③ 各个事务的所有更新操作
   以上均作为日志文件中的一个日志记录
   每个日志记录的内容：
   ① 事务标识（标明是哪个事务）
   ② 操作类型（插入、删除或修改）
   ③ 操作对象（记录内部标识）
   ④ 更新前数据的旧值（对插入操作而言，此项为空值）
   ⑤ 更新后数据的新值（对删除操作而言, 此项为空值）

2. 以数据块为单位
   日志记录内容包括：
   事务标识（标明是哪个事务）
   被更新的数据块

登记的次序严格按并行事务执行的时间次序、必须先写日志文件，后写数据库
 为什么要先写日志文件？

1）写数据库和写日志文件是两个不同的操作， 在这两个操作之间可能发生故障
2） 如果先写了数据库修改，而在日志文件中没有登记下这个修改，则以后就无法恢复这个修改了
3） 如果先写日志，但没有修改数据库，按日志文件恢复时只不过是多执行一次不必要的 UNDO 操作，并不会影响数据库的正确性

事物故障的恢复

• 事务故障：事务在运行至正常终止点前被终止
• 恢复方法： 由恢复子系统应利用日志文件撤消（UNDO）此事务已对数据库进行的修改
• 事务故障的恢复由系统自动完成，对用户是透明的，不需要用户干预
• 事务故障的恢复步骤：
   1. 反向扫描文件日志，查找该事务的更新操作。
   2. 对该事务的更新操作执行逆操作。即将日志记录中“更新前的值” 写入数据库
    插入操作，“更新前的值”为空，则相当于做删除操作
  删除操作，“更新后的值”为空，则相当于做插入操作
  若是修改操作，则相当于用修改前值代替修改后值
  3. 继续反向扫描日志文件，查找该事务的其他更新操作，并做同样处理。
  4. 如此处理下去，直至读到此事务的开始标记，事务故障恢复就完成了。

系统故障的恢复

恢复方法：

1. Undo故障发生时未完成的事物
2. Redo已完成的事物

并发控制概述

并发操作带来的数据不一致性包括：

1. 丢失修改
2. 不可重复读
3. 读脏数据

并发控制的主要技术：

1. 封锁（主要使用）
2. 时间戳
3. 乐观控制法
4. 多版本并发控制

• 封锁
   封锁： 封锁就是事务 T 在对某个数据对象（例如表、记录等）操作之前，先向系统发出请求，对其加锁。加锁后事务 T 就对该数据对象有了一定的控制，在事务 T 释放它的锁之前，其它的事务不能更新此数据对象
  分为排它锁、共享锁
  一级封锁协议： 事务 T 在修改数据 R 之前， 必须先对其加 X 锁， 直到事务结束（commit/rollback） 才释放。
  二级封锁协议： 在一级封锁协议的基础（写要加 X 锁， 事务结束释放） 上， 增加事务 T 在读入数据 R 之前必须先对其加 S 锁，读完后即可释放 S 锁。（读要加 S 锁，读完即释放）
  三级封锁协议： 在一级封锁协议基础上增加事务 T 在读取数据 R 之前必须先对其加 S 锁， 直到事务结束后释放。

可串行化调度

定义：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同， 称这种调度策略为可串行化调度（serializable）。

两段锁协议

两段锁协议是指所有事务必须分两个阶段对数据对象进行加锁和解锁。
1）在对任何数据进行读写操作以前，首先要申请并获得对该数据的锁。
2） 在释放一个锁之后，事务不再申请和获得其他任何的锁。

意向锁

意向锁： 如果对一个节点加意向锁，则可说明该节点的下层节点正在被加锁； 对任一节点加锁时，必须先对它的上层节点加意向锁。
 三种常用的意向锁：意向共享锁（Intent Share Lock， IS 锁）； 意向排它锁（Intent Exclusive Lock， IX 锁）；共享意向排它锁（Share Intent Exclusive Lock， SIX 锁）。

1、 IS 锁
如果对一个数据对象加 IS 锁，表示它的子节点拟加 S 锁。
例如：事务 T1 要对 R1 中某个元组加 S 锁，则要首先对关系 R1 和数据库加 IS 锁

2、 IX 锁
如果对一个数据对象加 IX 锁，表示它的子节点拟加 X 锁。
例如：事务 T1 要对 R1 中某个元组加 X 锁，则要首先对关系 R1 和数据库加 IX 锁

3、 SIX 锁
如果对一个数据对象加 SIX 锁，表示对它加 S 锁，再加 IX 锁，即 SIX = S + IX。

并发控制的方法除了封锁技术外，还有时间戳方法、乐观控制法和多版本并发控制。
时间戳方法：给每一个事务盖上一个时标，即事务开始的时间。每个事务具有唯一的时间戳，并按照这个时间戳来解决事务的冲突操作。 如果发生冲突操作，就回滚到具有较早时间戳的事务， 以保证其他事务的正常执行， 被回滚的事务被赋予新的时间戳被从头开始执行。乐观控制法认为事务执行时很少发生冲突，所以不对事务进行特殊的管制， 而是让它自由执行， 事务提交前再进行正确性检查。如果检查后发现该事务执行中出现过冲突并影响了可串行性，则拒绝提交并回滚该事务。 又称为验证方法多版本控制是指在数据库中通过维护数据对象的多个版本信息来实现高效并发的一种策略。

数据库范式

• 1NF 列不可分
• 2NF 消除了非主属性对键的部分函数依赖
• 3NF 消除了非主属性对键的传递函数依赖
• BCNF 消除了主属性对键的部分函数依赖和传递函数依赖

反范式

• Pattern1：合并 1 对 1 关系
• Pattern2： 1 对 N 关系中复制非键属性以减少连接
• Pattern3： 1 对 N 关系中复制外键以减少连接
• Pattern4： N 对 N 关系中复制属性，把两张表中经常需要的内容复制到中间关系表中以减少连接
• Pattern5： 引入重复值
• Pattern6：建立提取表
• Pattern7：分表

BLOB和TEXT

当 BLOB 和 TEXT 值太大时， InnoDB 会使用专门的外部存储区域来进行存储，此时单个值在行内需要 1~4 个字节存储一个指针，然后在外部存储区域存储实际的值。

MySQL 会 BLOB 和 TEXT 进 行 排 序 与 其 他 类 型 是 不 同 的 ： 它 只 对 每 个 类 的 最 前max_sort_length 字节而不是整个字符串进行排序。

MySQL 不能将 BLOB 和 TEXT 列全部长度的字符串进行索引，也不能使用这些索引消除排序

时间和日期类型

当你需要同时包含日期和时间信息的值时则使用 DATETIME 类型。 MySQL 以’YYYY-MM-DD HH:MM:SS’格式检索和显示 DATETIME 值。支持的范围为’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。
当你只需要日期值而不需要时间部分时应使用 DATE 类型。 MySQL 用’YYYY-MM-DD’格式检索和显示 DATE 值。支持的范围是’1000-01-01’到 ‘9999-12-31’。
TIMESTAMP 类型同样包含日期和时间，范围从’1970-01-01 00:00:01’ UTC 到’2038-01-19 03:14:07’ UTC。

视图

视图本身不包含数据，因此它们返回的数据时从其他表中检索出来的。在添加和更改这些表中的数据时，视图将返回改变过的数据。
更新视图就是更新其基表(视图本身没有数据)。

B+树与B树的比较

• 组织方式不一样

B+树：所有有效的索引关键字值都必须存储在叶结点中，其内部结点中的键值只用于索引项的查找定位。
B 树：有效的索引关键字值可以出现在 B 树的任意一个结点中。
因此：
B+树：所有关键字的查找速度基本一致
B 树：依赖于查找关键字所在结点的层次

• 叶结点不同

B+树中叶节点间增加链表指针， 提供对索引关键字的顺序扫描功能；叶节点的个数未必符合 m 叉查找树的要求， 它依赖于键值字节数和指针字节数，为 m1 阶。

为什么 B+比 B 树更适合实际应用中操作系统的文件索引和数据库索引

1) B+的磁盘读写代价更低
B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。
 2) B+树的查询效率更加稳定
 3）树的遍历效率较高
 B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低）

聚簇索引

InnoDB通过主键聚簇数据
聚簇索引是一级索引。
聚簇索引以外的所有索引都称为二级索引。

索引分类——从逻辑角度

1. 主键索引
    索引列的值必须唯一，并且不允许有空值

2. 唯一索引
    与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一

3. 普通索引

4. 复合索引

5. 全文索引

6. 空间索引
    空间索引：空间索引是对空间数据类型的字段建立的索引

使用 UUID 作为主键的缺点

1）写入的目标页可能已经刷到磁盘上并从缓存中移除，或者还没有被加载到缓存中，
InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机 IO

2）因为写入是乱序的， InnoDB 不得不频繁地做页分裂操作， 以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少修改三个页而不是一个页。

3）由于频繁的页分裂，页会变得稀疏并被不规则填充，所以最终数据会有碎片。

覆盖索引

如果一个索引包含了所有需要查询字段的值，就称为覆盖索引。 覆盖索引必须要存储索引列的值， MySQL 只能使用 B 树索引做覆盖索引

如果 EXPLAIN 出来的 type 列的值为 index，则说明 MySQL 使用了索引顺序扫描来做排序。

慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句

MySQL的查询流程：

MySQL 客户端通过协议与 MySQL 服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析

MySQL 客户端–>MySQL 服务器–>缓存–>查询检查–>查询优化–>执行查询

MySQL中的锁

分类

操作类型：

• 读锁和写锁

数据操作粒度：

• 表锁和行锁

MyISAM表锁

MyISAM 在读表前自动对表加读锁，在写表前自动对表加写锁。

• 用户 A 给表 A 加了读锁之后，只能读表 A，不能写表 A（报错），也不能读写其他表（报错）。此时用户 B 可以读表 A，可以读写其他表，但是写表 A 时会出现阻塞（未报错），直至用户A 释放表 A 的锁之后才解除阻塞，执行命令。

1、对 MyISAM 表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2、对 MyISAM 表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

InnoDB 行锁

⚫ 锁粒度小，并发度高；开销大，加锁慢，会出现死锁

⚫ 支持事务

锁出现的问题：

⚫ 脏读（读-DB 结果不一致）： 在一个事务中， 读取其他事务未提交的数据，其他事务回滚后，导致读到的数据与数据库中的数据不一致；

⚫ 不可重复读（读-读结果不一致）：一个事务中多次读取相同记录结果不一致（另一事务对该记录进行增改删）；

⚫ 幻读（读-写，用写来验证读，结果不一致）： 一个事务中读取某个范围内的记录，另一个事务在该范围内插入新的记录，虽然直接查询读取不到，但在插入同 PK（同另一个事务插入记录的 PK）时会冲突，并且更新范围记录时会同时更新另一个事务新插入的
记录。 插入同 PK 和更新范围记录虽然是写，但是在写之前也是要读的， 所以也算在读到不同的记录里面了。

事务隔离级别：

⚫ 读未提交（都不能避免） 事务中的数据即使没有提交， 也会对其他事务可见；

⚫ 读已提交（可避免脏读， 提交读，可以立即读到其他事务提交的数据）：一个事务从开始直接提交之前，所做的任何修改对其他事务都是不可见的；

⚫ 可重复读（可避免脏读、 不可重复读， 快照读，一致性读）： 一个事务中多次读取相同的记录，结果是一致的；如果使用 select … for update、 lock in share mode 才会避免幻读，在第二次读的时候便
可读到其他事务更新的数据（相当于破坏了可重复读，但是不会出现幻影）。

InnoDB 使用 MVCC 来实现可重复读（也可实现读已提交），但没有解决幻读问题；
另外， InnoDB 提供了这样的机制： 在默认的可重复读的隔离级别里，可以使用加锁读
去查询最新的数据。 这个加锁读使用到的机制就是 next-key locks。

⚫ 串行化（都可避免）

事务隔离级别的实现

• 读未提交

无锁

• 读已提交

MVCC

• 可重复读

MVCC 只工作在 REPEATABLE READ 和 READ COMMITED 隔离级别下。

MVCC 最大的作用是: 实现了非阻塞的读操作,写操作也只锁定了必要的行.

• 可序列化

读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论。

InnoDB锁分类

• record lock
   1） InnoDB 里的行锁（record lock）是索引记录的锁
  2） record lock 锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么 innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

• gap lock
   锁定一个范围的记录,但不包括记录本身。 锁加在未使用的空闲空间上,可能是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间

• next-key lock
   行锁与间隙锁组合起来用就叫做 Next-Key Lock。锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

• 意向锁
  InnoDB 的意向锁主要用户多粒度的锁并存的情况。比如事务 A 要在一个表上加 S 锁，如果表中的一行已被事务 B 加了 X 锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。举个例子，如果表中记录 1 亿，事务 A 把其中有几条记录上了行锁了，这时事务 B 需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务 B 先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

说白了意向锁的主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”