数据库专题（关于连接/存储过程/锁/范式/事务的问题）

一，内连接和外连接为区别？

有如下两个表：


内连接只显示符合连接条件的记录

外连接分左外连接、右外连接、全外连接三种：
1）左外连接：即以左表为基准，到右表找匹配的数据，找不到匹配的用 NULL 补齐。


2）右外连接
即以右表为基准，到左表找匹配的数据，找不到匹配的用 NULL 补齐。 显示右表的全部记录及左表符合连接条件的记录。

3）全外连接
除了显示符合连接条件的记录外，在 2 个表中的其他记录也显示出来。

二．inner join 和 left join 的性能比较

1.在解析阶段，左连接是内连接的下一阶段，内连接结束后，把存在于左输入而未存在于右输入的集，加回总的结果集，因此如果少了这一步效率应该要 高些。
2.在编译的优化阶段，如果左连接的结果集和内连接一样时，左连接查询会 转换成内连接查询，即编译优化器认为内连接要比左连接高效。

三，数据库中两个表求交集、并集、差集

有如下两个表：
表A：

表B：

求两者交集（inner join）：

求两者差集（left join或right join）

四，存储过程的概念以及优缺点

存储过程：就是一些编译好了的 sql 语句，这些 SQL 语句代码 像一个方法一样实现一些功能（对单表或多表的增删改查），然后 再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。
优点：
1.存储过程因为 SQL 语句已经预编译过了，因此运行的速度比 较快。
2.存储过程在服务器端运行，减少客户端的压力。
3.允许模块化程序设计，就是说只需要创建一次过程，以后在程 序中就可以调用该过程任意次，类似方法的复用。
4.减少网络流量，客户端调用存储过程只需要传存储过程名和相 关参数即可，与传输 SQL 语句相比自然数据量少了很多。

• 增强了使用的安全性，充分利用系统管理员可以对执行的某 一个存储过程进行权限限制，从而能够实现对某些数据访问的限制， 避免非授权用户对数据的访问，保证数据的安全。程序员直接调用 存储过程，根本不知道表结构是什么，有什么字段，没有直接暴露 表名以及字段名给程序员。
  缺点：
  调试麻烦（至少没有像开发程序那样容易），可移植性不灵活（因 为存储过程是依赖于具体的数据库）。

五，有关范式的问题

数据库的三级范式：
1NF:字段不可再分，原子性。
2NF：满足第二范式（ 2NF ）必须先满足第一范式（ 1NF ）。 一个表只能说明一个事物。非主键属性必须完全依赖于主键属性。
3NF:满足第三范式（ 3NF ） 必须先满足第二范式（ 2NF ） 。每 列都与主键有直接关系，不存在传递依赖。任何非主属性不依赖于其它 非主属性。
不符合第一范式的例子(关系数据库中 create 不出这样的表)：
表:字段 1, 字段 2(字段 2.1, 字段 2.2), 字段 3 …
不符合第二范式的例子:
表:学号, 姓名, 年龄, 课程名称, 成绩, 学分; 这个表明显说明了两个事物:学生信息, 课程信息。
不符合第三范式的例子:
学号, 姓名, 年龄, 所在学院, 学院地点，学院联系电话，主键为" 学号“；存在依赖传递: (学号) → (所在学院) → (学院地点, 学院电话)

六，有关事务的问题

1.数据库事务正确执行的四个基本要素（事务的 4 个属性）：

• 原子性：
  一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成， 不会结束在中间某个环节。事务在执行过程中发生错误，会被 回滚 （Rollback）到事务开 始前的状态，就像这个事务从来没有执行过一样。
• 一致性：
  在事务开始和完成时，数据库中的数据都保持一致的状态，数据的完整性约 束没有被破坏。（事务的执行使得数据库从一种正确状态转换成另一种正确状态）。具体 来说就是，比如表与表之间存在外键约束关系，那么你对数据库进行的修改操作就必需要 满足约束条件，即如果你修改了一张表中的数据，那你还需要修改与之存在外键约束关系 的其他表中对应的数据，以达到一致性。
• 隔离性：
  一个事务的执行不能被其他事务干扰。为了防止事务操作间的混淆，必须串 行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。（在事务正确提交之前， 不允许把该事务对数据的任何改变提供给任何其他事务）。（事务处理过程中的中间状态 对外部是不可见的）。隔离性通过锁就可以实现。
• 持久性：
  一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，并不会被 回滚。

2.并发事务带来的问题：

• 更新丢失：
  两个事务 Tl 和 T2 读入同一数据并修改，T2 提交的结果覆盖了 Tl 提交 的结果，导致 Tl 的修改被丢失。
• 脏读：
  事务 Tl 修改某一数据，并将其写回磁盘，事务 T2 读取同一数据后，Tl 由 于某种原因被撤销，这时 Tl 已修改过的数据恢复原值，T2 读到的数据就与数 据库中的数据不一致，则 T2 读到的数据就为“脏”数据，即不正确的数据。
• 不可重复读：
  是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一 个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第 二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就 发生了在一个事务内两次相同的查询读到的数据是不一样的，因此称为是不可 重复读。
  例如：在事务 A 中，读取到张三的工资为 5000，操作没有完成，事务还没提交。 与此同时，事务 B 把张三的工资改为 8000，并提交了事务。随后，在事务 A 中， 再次读取张三的工资，此时工资变为 8000。在一个事务中前后两次读取的结果 并不致，导致了不可重复读。
• 幻读：
  例如：目前工资为 5000 的员工有 10 人，事务 A 读取所有工资为 5000 的人数为 10 人。此时，事务 B 插入一条工资也为 5000 的记录。这是，事务 A 再次读取 工资为 5000 的员工，记录为 11 人。此时产生了幻读。

不可重复读的重点是修改： 同样的条件，你读取过的数据，再次读取出来发现值不一样了。
幻读的重点在于新增或者删除： 同样的条件，第 1 次和第 2 次读出来的记录数不一样。
3.数据库事务的隔离级别介绍、举例说明。
数据库提供了 4 种隔离级别（由低到高）：
这 4 个级别可逐个解决脏读，不可重复读和幻读这几个问题
1.读未提交数据 ：
允许事务读取未被其他事务提交的变更，可能有脏读，不可重复读和幻读的问题。
比如：某时刻会话 a 修改了一个数据，但还未提交，此时会话 b 读取了该数据，这是， 会话 a 回滚了事务，这就导致数据出现了不一致状态，这就是脏读。
2.读已提交数据 ：
允许事务读取已经被其他事务提交的变更，可以避免脏读，可能有不可重复读和幻读 的问题。
例如：某时刻会话 a 的一个事务里查询一个数据，得到的数据是 1，这时会话 b 修改 了该数据的值为 2，并提交了，此时会话 a 的事务又要读取该数据，这时的数据是 2，就样 就出现了同一个事务内，读的结果不一样，这就是不可重复读。
不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数 据。
3.可重复读（Mysql 的默认隔离级别） ：
确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务 对这个字段进行更新，可以避免脏读和不可重复读，可能会有幻读。
4.可串行化 :
所有事务都一个接一个地串行执行。可以避免脏读，不可重复读，幻读。

七，有关锁的问题

1.说下数据库的锁机制，数据库中都有哪些锁：
锁是一种并发控制技术，锁是用来在多个用户同时访问同一个数据的时候 保护数据的。
有 2 种基本的锁类型：
共享（S)锁：
多个事务可封锁一个共享页；任何事务都不能修改该页； 通常是该页被 读取完毕，S 锁立即被释放。在执行 select 语句的时候需要给操作对象（表或者一些记录） 加上共享锁，但加锁之前需要检查是否有排他锁，如果没有，则可以加共享锁（一个对象 上可以加 n 个共享锁），否则不行。共享锁通常在执行完 select 语句之后被释放，当然也 有可能是在事务结束（包括正常结束和异常结束）的时候被释放，主要取决与数据库所设 置的事务隔离级别。
排它（X)锁：
仅允许一个事务封锁此页；其他任何事务必须等到 X 锁被释放才能对该 页进行访问；X 锁一直到事务结束才能被释放。执行 insert、update、delete 语句的时候需 要给操作的对象加排他锁，在加排他锁之前必须确认该对象上没有其他任何锁，一旦加上 排他锁之后，就不能再给这个对象加其他任何锁。排他锁的释放通常是在事务结束的时候 （当然也有例外，就是在数据库事务隔离级别被设置成 Read Uncommitted（读未提交数 据）的时候，这种情况下排他锁会在执行完更新操作之后就释放，而不是在事务结束的时 候）。
2.产生死锁的四个必要条件
（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不可剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 环路等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
只要系统发生了死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死 锁。
预防死锁的方法：
预防死锁的发生只需破坏死锁产生的四个必要条件之一即可。
1). 破坏互斥条件：
如果允许系统资源都能共享使用，则系统不会进入死锁状态。但有些资源根本不能同时 访问，如打印机等临界资源只能互斥使用。所以，破坏互斥条件而预防死锁的方法不太可 行，而且在有的场合应该保护这种互斥性。
2) 破坏不剥夺条件：
当一个已保持了某些不可剥夺资源的进程，请求新的资源而得不到满足时，它必须释放 已经保持的所有资源，待以后需要时再重新申请。这意味着，一个进程已占有的资源会被 暂时释放，或者说是被剥夺了，或从而破坏了不可剥夺条件。
该策略实现起来比较复杂，释放已获得的资源可能造成前一阶段工作的失效，反复地 申请和释放资源会增加系统开销，降低系统吞吐量。这种方法常用于状态易于保存和恢复 的资源，如 CPU 的寄存器及内存资源，一般不能用于打印机之类的资源。
3) 破坏请求和保持条件
釆用预先静态分配方法，即进程在运行前一次申请完它所需要的全部资源，在它的资 源未满足前，不把它投入运行。一旦投入运行后，这些资源就一直归它所有，也不再提出 其他资源请求，这样就可以保证系统不会发生死锁。
这种方式实现简单，但缺点也显而易见，系统资源被严重浪费，其中有些资源可能仅 在运行初期或运行快结束时才使用，甚至根本不使用。而且还会导致“饥饿”现象，当由于个别资源长期被其他进程占用时，将致使等待该资源的进程迟迟不能开始运行。
4) 破坏环路等待条件
为了破坏循环等待条件，可釆用顺序资源分配法。首先给系统中的资源编号，规定每 个进程，必须按编号递增的顺序请求资源，同类资源一次申请完。也就是说，只要进程提 出申请分配资源 Ri，则该进程在以后的资源申请中，只能申请编号大于 Ri 的资源。
避免死锁的方法：
银行家算法
检测死锁的方法：
死锁定理
解除死锁的方法：
资源剥夺
撤销进程
进程回退
3.mysql锁的粒度（锁的级别）

• 1.表级锁：
  直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果 你是写锁，则其它进程则读也不允许。
  特点：开销小，加锁快；不会出现死锁；锁定粒度 最大，发生锁冲突的概率最高，并发度最低。
  有 2 种模式：
  表共享读锁和表独占写锁。加读锁的命令:lock table 表名 read;去掉锁 的命令：unlock tables。
  支持并发插入：支持查询和插入操作并发进行（在表尾并发插入）。
  锁调度机制：写锁优先。
• 2.行级锁：
  仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
  特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  InnoDB 存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。
• 3.页级锁：
  一次锁定相邻的一组记录。开销和加锁时间界于表锁和行锁之间；会出现 死锁；锁定粒度界于表锁和行锁之间，并发度一般。

最常用的处理多用户并发访问的方法是加锁。当一个用户锁住数据库中的 某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁 的粒度上。比如，(表锁)放在一个表上的锁限制对整个表的并发访问；（页锁） 放在数据页上的锁限制了对整个数据页的访问；（行锁）放在行上的锁只限制 对该行的并发访问。

4.乐观锁和悲观锁的概念，实现方式和适用场景

• 悲观锁
  锁如其名，他对世界是悲观的，他认为别人访问正在改变的数据 的概率是很高的，所以从数据开始更改时就将数据锁住，直到更改完成才释放。
  例如：
  select * from account where name=”Erica” for update
  这条 sql 语句锁定了 account 表中所有符合检索条件（ name=”Erica” ）的 记录。 本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修 改这些记录。该语句用来锁定特定的行（如果有 where 子句，就是满足 where 条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改 或删除这些行，直到该语句的事务被 commit 语句或 rollback 语句结束为止。
  悲观锁可能会造成加锁的时间很长，并发性不好，特别是长事务，影响系统的整体性能。
  悲观锁的实现方式：
  悲观锁，也是基于数据库的锁机制实现。传统的关系型数据库里边就用到 了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先 上锁。
• 乐观锁：
  认为别人访问正在改变的数据的概率是很低 的，所以直到修改完成准备提交所做的修改到数据库的时候才会将数据锁住， 当你读取以及改变该对象时并不加锁，完成更改后释放。乐观锁不能解决脏读 的问题。
  乐观锁的实现方式：
  1.大多是基于数据版本（Version）记录机制实现，需要为每一行数据增加 一个版本标识（也就是每一行数据多一个字段 version），每次更新数据都要 更新对应的版本号+1。
  工作原理：
  读出数据时，将此版本号一同读出，之后更新时，对此版本号 加一。此时，将提交数据的版本信息与数据库表对应记录的当前版本信息进行 比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据，不得不重新读取该对象并作出更改。
  2.使用时间戳来实现：
  同样是在需要乐观锁控制的 table 中增加一个字段，名称无所谓，字段类型 使用时间戳 （timestamp）, 和上面的 version 类似，也是在更新提交的时候 检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一 致则 OK，否则就是版本冲突。

使用场景：
如果并发量不大，可以使用悲观锁解决并发问题；但如果系统的并发量非常大的话,悲观锁定会带来非常大的性能问题,所以我们就要选择乐观锁定的方 法.现在大部分应用都应该是乐观锁的。