数据库面经

Mysql 查询语句练习

链接: link.

什么是事务

事务是数据库区别于文件系统的重要特性之一，事务可以一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成(一系列操作)，事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都做修改，要么都不做，这就是事务的主要目的。

事务的特性有哪些？分别是怎么保证的？

ACID 隔离的原子 一致的持久

隔离性 并发事务互不影响
原子性 事务的操作要么都执行，要么都不执行。

一致性 保持数据库的约束完整性

持久性 事务发生了，对数据库的影响是持久的，不会回滚。

下面这段话摘自《MySQL技术内幕-InnoDB存储引擎》

原子性、一致性和持久性是通过数据库的redo log和undo log来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。而隔离性是通过锁实现的。【具体大家看书吧，书上比较详细。】

Mysql三大日志

二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）

面试官可能会问三种日志的区别和作用。

redo log：恢复提交事务修改的页操作；通常是物理日志，记录的是页的物理修改操作。

uodo log：回滚记录到某个特定版本；通常是逻辑日志，根据每行记录进行记录。

bin log：用来进行Point-In-Time(PIT)的恢复及主从复制环境的建立。

什么是undo_log? undo_log用于哪些场景？

undo_log是逻辑日志，用于记录数据更新之前的值。undo_log可以用于事务回滚，即事务的原子性，和多版本并发控制（MVCC）。

这里面试官可能会接着问binlog和redolog的区别？

回答：
（1）重做日志是在InnoDB存储引擎层产生的，而二进制日志是在MySQL数据库上层产生的，二进制日志不仅仅针对InnoDB存储引擎，任何存储引擎都会产生二进制日志。

（2）两种日志的记录内容形式不同。二进制日志是一种逻辑日志，记录的是SQL语句；而InnoDB存储引擎层面的重做日志是物理格式日志，记录的是对于每个页的修改。

（3）写入磁盘的时间不同，二进制日志只在事务提交完成后进行一次写入，而redo log在事务进行中不断的写入。

数据库中并发事务带来的问题

数据丢失（修改丢失）

指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据（或修改失败回滚）。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

脏读（读取未提交数据）

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。

不可重复读（前后多次读取，数据内容不一致）

事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

幻读（前后多次读取，数据总量不一致）

事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样。

不可重复读和幻读到底有什么区别呢？

（1）不可重复读是读取了其他事务更改的数据，针对insert与update操作

解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

（2）幻读是读取了其他事务新增的数据，针对insert与delete操作

解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

数据库的锁

一、共享锁
共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

用法

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁。

SELECT … LOCK IN SHARE MODE;
当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

二、排他锁
排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

用法

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁

SELECT … FOR UPDATE;
当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

三、总结
共享锁就是指多个事务只能读数据而不能修改数据。（即只读不能改）

排他锁是指一个事务在一行数据加上锁后，其它事务不能再对其加锁。

四、加锁原则
拿MySql的InnoDB引擎来说，对于insert、update、delete等操作。会自动给涉及的数据加排他锁；

对于一般的select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。

共享锁：SELECT … LOCK IN SHARE MODE;

排他锁：SELECT … FOR UPDATE;

数据库有哪些锁？lock和latch的区别

回答：数据库中有表锁和行锁等

lock锁：锁的对象是事务，用于锁定数据库中的对象，如表、页、行等，并且lock锁一般在commit或rollback后释放，有死锁机制。

latch锁：一般称为轻量级锁，要求锁定的时间必须非常短，在InnoDB中又可以分为mutex(互斥量)和rwlock(读写锁)。目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

数据库事务隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读 、不可重复读 、幻读 这几类问题。

• 读未提交（Read Uncommitted）：在事务 A 读取数据时，事务 B 读取和修改数据加了共享锁。这种隔离级别，会导致脏读、不可重复读以及幻读。

• 读提交（Read Committed）：在事务 A 读取数据时增加了共享锁，一旦读取，立即释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务 A 在读取数据时，事务 B 只能读取数据，不能修改。当事务 A 读取到数据后，事务 B才能修改。这种隔离级别，可以避免脏读，但依然存在不可重复读以及幻读的问题。

• 可重复读（Repeatable Read）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务A 在没有结束事务时，事务 B 只能读取数据，不能修改。当事务 A 结束事务，事务 B 才能修改。这种隔离级别，可以避免脏读、不可重复读，但依然存在幻读的问题。

• 可序列化（Serializable）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁。可序列化解决了脏读、不可重复读、幻读等问题，但隔离级别越来越高的同时，并发性会越来越低。
  

• MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

至于InnoDB为什么选用可重复读，我的个人理解是：在InnoDB存储引擎中，使用可重复读可以解决脏读、不可重复读，而幻读也有可能发生，但是是可以避免的，通过加Next-Key Lock锁可以解决幻读问题。并且并非隔离级别越高越好，隔离级别越高的话，并发性能越低，所以在实际的开发中，需要根据业务场景进行选择事务的隔离级别。

InnoDB存储引擎中的锁都有哪些类型？

可以分为共享锁、排他锁、意向锁、一致性非锁定读和一致性锁定读。
其中共享锁和排他锁均属于行级锁。

• 共享锁(S Lock)：运行事务读一行数据。

• 排他锁(X Lock)：允许事务删除或更新一行数据。

• 意向锁属于表级别的锁，又可以分为意向共享锁(IS Lock)和意向排他锁(IX Lock)。

  • 意向共享锁(IS Lock)：事务想要获得一张表中某几行的共享锁。
  • 意向排他锁(IX Lock)：事务想要获得一张表中某几行的排他锁。

• 一致性非锁定读：指InnoDB存储引擎通过多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此等待行上锁的释放，相反的，InnoDB存储引擎会读取一个快照数据。

• 一致性锁定读：InnoDB存储引擎对于SELECT语句支持两种一致性锁定读的操作：
  select … for update和select … lock in share mode。

什么是MVCC？

MVCC实现原理【MVCC多版本并发控制，指的是一种提高并发的技术。】
Multi-Version Concurrency Control。
最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。每次事务更新数据的时候，会给行数据生成新的版本，每个版本都有自己的row trx_id，表示更新的事务ID（随时间单调递增）。

MVCC能解决什么问题

数据库并发场景有三种，分别为：
读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC带来的好处

MVCC可以为数据库解决以下问题

• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

• MVCC只在读取已提交和可重复 读两种隔离级别下有作用

• MVCC常见的实现方式乐观锁和悲观锁

• MVCC是行级锁的变种，很多情况下避免了加锁操作。

• 应对高并发事务, MVCC比单纯的加锁更高效;

• InnoDB存储引擎在数据库每行数据的后面添加了三个字段, 不是两个!!

InnoDB存储引擎在数据库每行数据的后面添加了三个字段
分别是事务ID、回滚指针和
6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。

MyISAM和InnoDB区别

• InnoDB支持行锁 支持事务 支持外键 支持MVCC 支持崩溃后的安全恢复

• InnoDB也支持表级锁 但MyISAM只支持表级锁

• MyISAM强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快

数据库中的死锁概念

回答：死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。

解决死锁的办法：

• 一种是超时回滚
• 一种是采用死锁检测机制（wait-for graph等待图）
  如果面试官让你举例子，可以举例下面的例子：
  

在 MySQL 中，gap lock 默认是开启的，即innodb_locks_unsafe_for_binlog 参数值是disable 的，且 MySQL 中默认的是 RR 事务隔离级别。

当我们执行以下查询 SQL 时，由于 order_no 列为非唯一索引，此时又是 RR 事务隔离级别，所以 SELECT 的加锁类型为 gap lock，这里的 gap 范围是 (4,+∞）。

执行查询 SQL 语句获取的 gap lock 并不会导致阻塞，而当我们执行以下插入 SQL 时，会在插入间隙上再次获取插入意向锁。插入意向锁其实也是一种 gap 锁，它与 gap lock 是冲突的，所以当其它事务持有该间隙的 gap lock 时，需要等待其它事务释放 gap lock 之后，才能获取到插入意向锁。

以上事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁。

数据库索引

数据库储存引擎

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

• 外键：InnoDB 支持外键。

• 备份：InnoDB 支持在线热备份。

• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

• 其它特性：MyISAM 支持压缩表和空间数据索引。

另一个版本

• InnoDB支持行锁 支持事务 支持外键 支持MVCC 支持崩溃后的安全恢复

• InnoDB支持行级锁和表级锁 但MyISAM只支持表级锁

• MyISAM强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快

数据库范式

• 1NF(第一范式)
  属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。

• 2NF(第二范式)
  2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖

• 3NF(第三范式)
  3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。

总结
1NF：属性不可再分。
2NF：1NF 的基础之上，消除了非主属性对于码的部分函数依赖。
3NF：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖