MySQL面试题

数据库概念

一、ACID 讲一下

（阿里校招）
ACID是事务的四个特性。分别是 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability) 。

• 原子性
  原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。最经典的就是转账案例，我们把转入和转出当做一个事物的话，就需要在SQL中显式指定开启事务。
• 一致性
  一致性是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性 。我们可以从数据库层面和业务层面两方面来保证，数据库层面我们可以设置触发器，外键，表，行约束等来保证，业务层面就是我们Java工程师的工作啦
• 隔离性
  隔离性指的是多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。 多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。 这个点又引申出了下面两道题，以及后边的加锁和阻塞。
• 持久性
  持久性意味着即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中，不会被回滚

二、说一下脏读，不可重复读和幻读

• 脏读
  脏读： 意味着一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。即这个事务读取的数据是不正确的
• 不可重复读
  不可重复读： 在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。即这个事物在读的过程中被修改了
• 幻读
  幻读：当一个事务对整个table进行修改之后，第二个事物向表中插入了一行数据，此时第一个事物发现了新插入的没有修改的数据行，好像发生了幻觉一样。eg：本身应该是修改5行数据，但是现在有6行数据发生修改。

三、 数据库的隔离级别

• 读未提交(read uncommitted)
  理解：一个事务还没提交时，它做的变更就能被别的事务看到
  影响：会出现幻读，不可重复读，脏读
  具体操作：更新数据时加上行级共享锁，事务结束即释放
  具体操作：

  操作1：开启事务1和2，并设置隔离级别为未提交数据（read uncommitted）
  
  
  操作2：事务1对数据库内容进行更新，但并未提交事务；
  
  操作3：事务2查询数据库，查到的是事务1更改后但是还未提交的数据。故出现了“脏读”现象。
  
  操作4：事务1对事务进行提交或者回滚；commit or rollback；
  该级别问题：会出现“脏读”现象。
  解决办法：提高隔离级别，设置为read committed；

• 读已提交(read committed)
  解释：一个事务提交之后，它做的变更才会被其他事务看到
  影响：会出现幻读，不可重复读，不会出现脏读
  解决方法：写数据加行级排他锁，这样写过程是无法读取的，直到事务处理完毕才释放排他锁，给读的数据加行级共享锁，这样读的时候也是无法写的，但是一旦读完该行就释放共享锁
  MySQL会在SQL语句开始执行时创建一个视图
  具体操作：
  操作1：给两个事务都设置隔离级别为read committed；
  
  
  操作2：事务1对第一行数据进行修改操作，但并未提交；
  
  操作3：事务2对数据库内容进行查询；
  
  解释：查询到的依然是未进行更改前的内容，故解决了“脏读”问题。
  出现问题：会出现不可重复读现象，即在一次事务中，事务2两次查询到的数据不一致。

  操作4：事务1提交更改操作；
  
  操作5：此时事务2再次对数据库进行查询
  
  解释：此时读到的数据出现了不一致情况
  解决办法：提高隔离级别，设置为可重复读（repeatable read）级别

• 可重复读 (repeatable read)
  解释：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
  影响：会出现幻读，不会出现不可重复读，脏读
  解决方法：给写的数据加行级排他锁，事务结束释放，给读的数据加行级共享锁，事务结束后释放
  MySQL会在事务开始时创建一个一致性视图(接下面的MVCC)，事物结束时销毁
  具体操作：
  操作1：更改事务1，2的隔离级别
  
  
  操作2：对事务1进行更改操作
  
  操作3：事务2查询数据库
  
  操作4：事务1提交
  
  操作5：事务2再次查询
  
  操作6：事务2提交，并再次读取数据库中的数据。
  
  解释：事务2提交之后，再次查询即能查询到最新数据。事务1进行更改操作的前后，事务2对数据库两次查询操作结果一致。解决了不可重复读问题。保证在一次事务中，事务2所读到的内容是完全一致的。
  问题：会出现“幻读”情况。
  操作1：事务1和事务2都开启新的事务；
  操作2：事务2进行插入操作并提交。
  
  操作3：事务1对数据库进行更新操作；
  
  问题：“幻读”–原本是两行，但是却显示3行数据改变。

• 可串行化(serializable)
  解释：当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
  影响：不会出现幻读，不可重复读，脏读
  解决方法：事务读数据则加表级共享锁，事务写数据则加表级排他锁
  不区分快照度与当前读
  具体操作：
  操作1：设置事务1和事务2的隔离级别为可串行化。
  操作2：对事务1进行更新操作
  
  操作3：对事务2进行插入操作
  
  解释：此时出现错误
  操作4：提交事务1
  操作5：对事务2进行插入操作
  
  解释：此时可以正常操作。相当于是加锁，等一个事务完成后，才能进行插入操纵。

其中，Oracle和SQLServer都是读已提交，但MySQL默认的隔离级别是可重复读 ，这是一个MySQL5.0之前的上古遗留版本问题。当时的binlog只有STATEMENT格式，用RC会出现bug。
当面试官问我们项目用的哪个隔离级别，就我自己来说，如果看我之前的文章，我写过我搭建了一个博客，对于博客来说，我肯定要说读已提交。这样的并发度更高，同时，在我们的互联网项目中可以接受重复读带来的一些不便。
同时，既然问到了数据库的隔离级别，那么离下面的锁也不远了

四、三大范式

• 第一范式
  第一范式： 所有字段值都是不可分解的原子值 。例如有一个列是电话号码一个人可能有一个办公电话一个移动电话。第一范式就需要拆开成两个属性。
• 第二范式
  第二范式：非主属性完全函数依赖于候选键。如PersonID，ProductID，ProductName，PersonName可以看到，PersonID和ProductID是联合主键，但是ProductName是依赖于ProductID的，只依赖了部分主键，没有依赖全部主键。需要拆分成三个表：PersonID、PersonName &&ProductID, ProductName&&PersonID, ProductID
• 第三范式
  第三范式： 每一列数据都和主键直接相关，而不能间接相关。即没有传递依赖。
  如OrderID，ProductID，ProductName，OrderID是主键，但是ProductID依赖了OrderID，而ProductName依赖了ProductID，等于说是间接依赖了OrderID，所以需要拆分为两个表：OrderID, ProductID和ProductID, ProductName
  这里需要指明范式不是最好的，我们需要混合使用范式和反范式
• 范式的优点
  因为相对来说有较少的重复数据，范式化的更新操作要比反范式快。同时范式化需要更少的distinct和order by。
• 范式的缺点
  通常需要关联，不仅代价昂贵，也可能会使的一些索引无效。
• 常用的反范式方法：
  复制：在两个表中根据实际业务情况存储部分相同的字段列，即有利于查询，也不会把表搞的太大
  缓存：对于需要多次join查询的表，可以在一个表中加入一个缓存列，用来缓存所join表的部分常用数据，如count等，我们需要实时更新该缓存

五、说一下内连接和外连接

• 内连接
  内连接分为等值连接和自然连接，等值连接是给定条件，只要满足条件即可连接。自然连接是要连接两个表中具有相同属性的数据。只有两个表相匹配的行才能在结果集中出现。返回的结果集选取两个表中所匹配的数据，舍弃不匹配的数据。（此处可以再进行优化）

select fieldlist from table1 [inner] join table2 on table1.column = table2.column

• 外连接
  内连接保证两个表中的所有行都满足条件，而外连接则不然，外连接不仅仅包含符合连接条件的行，而且还包括左表（左外连接），右表（右外连接），或者两个边表（全外连接）中的所有数据行 。

select fieldlist from table1 left/ right outer join table2