数据库常见面试题整理

DML DDL DCL DQL

DQL数据查询语言

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE

子句组成的查询块： 
SELECT <字段名表> 
FROM <表或视图名> 
WHERE <查询条件>

DML数据操纵语言

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。

数据操纵语言DML主要有三种形式：

1.插入：INSERT

2.更新：UPDATE

3.删除：DELETE

DDL数据定义语言

用来定义数据库对象：数据库、表、列、视图、索引、同义词、聚簇等等。

关键字：create/drop/alter

DDL操作是隐性提交的.(没有显式的commit)。不能rollback 

DCL数据控制语言

用来定义数据库的访问权限和安全级别，及创建用户。

关键字：grant/revoke

提交数据有三种类型：显式提交、隐式提交及自动提交

显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：

SQL>COMMIT；

隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：

SQL>SET AUTOCOMMIT ON；

触发器

触发器是一种特殊的存储过程，主要是通过时间来触发而被执行的，它可以强化约束来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化，可以级联运算。

存储过程

存储过程是一个预编译的sql语句，允许模块化的设计，只需要创建一次，就可以被多次调用。如果需要执行多次sql，那么存储过程的速度更快，可以用命令对象来调用存储过程。

优点:存储过程是一个预编译的 SQL 语句，执行效率高；放在数据库中，直接调用，减少网络通信；安全性高；可重复使用。

缺点: 移植性差

事务以及四大特性？

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

事务具有4个特性：原子性、一致性、隔离性、持久性(ACID)

原子性(A)：事务是数据库的逻辑工作单位，事务中包含的诸操作要么全做，要么全不做。事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性(C)：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。

隔离性(I)：一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(D)：是指一个事务一旦提交，它对数据库中数据的改变就应该是永久的，接下来的其他操作或故障不应该对其执行结果有任何影响。

一致性

"ensuring the consistency is the responsibility of user, not DBMS."

ACID里的AID都是数据库的特征,也就是依赖数据库的具体实现.而唯独这个C,实际上它依赖于应用层,也就是依赖于开发者.这里的一致性是指系统从一个正确的状态,迁移到另一个正确的状态.什么叫正确的状态呢?就是当前的状态满足预定的约束就叫做正确的状态.而事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性.

这里我们举个大家都在说的财务系统的例子.

A要向B支付100元,而A的账户中只有90元,并且我们给定账户余额这一列的约束是,不能小于0. 
那么很明显这条事务执行会失败,因为90-100=-10,小于我们给定的约束了.

这个例子里,支付之前我们数据库里的数据都是符合约束的,但是如果事务执行成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证.然后我们再看个例子

A要向B支付100元,而A的账户中只有90元,我们的账户余额列没有任何约束. 
但是我们业务上不允许账户余额小于0.因此支付完成后我们会检查A的账户余额, 发现余额小于0了,
于是我们进行了事务的回滚.

这个例子里,如果事务执行成功,虽然没有破坏数据库的约束,但是破坏了我们应用层的约束.而事务的回滚保证了我们的约束,因此也可以说事务提供了一致性保证(ps:事实上,是我们应用层利用事务回滚保证了我们的约束不被破坏).最后我们再看个例子

A要向B支付100元,而A的账户中只有90元,我们的账户余额列没有任何约束.然后支付成功了.

这里,如果按照很多人的理解,事务不是保证一致性么?直观上账户余额为什么能为负呢.但这里事务执行前和执行后,我们的系统没有任何的约束被破坏.一直都是保持正确的状态.

MySQL如何保证ACID？

一致性：从数据库层面来说，数据库是通过原子性、隔离性、持久性来保证一致性。一致性是目的，原子性、隔离性、持久性是手段；从应用层面来说，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据(比如在转账的例子中，代码里故意不给B账户加钱，那一致性还是无法保证的)；

原子性：利用undo log回滚日志。

为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方就叫做undo log)，然后进行数据的修改。如果出现了错误或者用户执行了rollback语句，系统可以利用undo log中的备份将数据恢复到事务开始之前的状态。

例如

1.delete一条数据的时候，就会记录这条数据的曾经的信息，回滚的时候，insert这条旧数据

2.update一条数据的时候，就会记录之前的旧值，回滚的时候，根据旧值执行update操作

3.insert一条数据的时候，就会这条记录的主键，回滚的时候，根据主键执行delete操作

持久性：利用redo log重做日志。

Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。 怎么解决这个问题？ 简单啊，事务提交前直接把数据写入磁盘就行啊。 这么做有什么问题？

• 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
• 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据(未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定)。

采用redo log的好处？ 其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下

• redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
• redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

隔离性：利用的是读写锁和MVCC机制(多版本并发控制)

• 读写锁：最简单直接的事务隔离实现方式，每次读操作需要获取一个共享锁，每次写操作需要获取一个排他锁。共享锁之间不会产生互斥，共享锁和排他锁之间、排他锁与排他锁之间会产生互斥。当发生锁竞争时，需要等待其中一个操作释放锁后，另一个操作才能获取到锁。
• MVCC：在读写锁中，读和写的排斥作用大大降低了事务的并发效率，于是又提出了能不能让读写之间也不冲突的方法，就是读取数据时通过一种类似快照的方式将数据保存下来，这样读锁和写锁就不冲突了。不同的事务会看到自己特定版本的数据，即使其他事务更新了数据，但是对本事务仍然不可见，本事务看到的数据始终是第一次查询到的数据。在数据库中，这个快照的处理方式叫多版本并发控制。这种方式真正实现了非阻塞读，只有在写操作时才需要加行级锁，因此并发效率更高。

MVCC多版本并发控制

MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，进行非阻塞并发读。

当前读：像select ... lock in share mode(共享锁)，select ... for update/insert/delete(排他锁)这些操作都是一种当前读，它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读：像不加锁的select操作就是快照读，即不加锁的非阻塞读。快照读的前提是隔离级别不是串行级别，串行级别下的快照会退化成当前读。之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC，可认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销。既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

MVCC的实现机制

为了实现MVCC，innodb会为每一行都加上两个隐含的列，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增(保存这两个额外系统版本号使大多数读操作可以不加锁)。在repeated read的隔离级别下，具体各种数据库操作的实现：

• select：满足以下两个条件innodb会返回该行数据：

该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地。

该行的删除版本号大于当前版本或者为空，这可以确保事务读取的行在事务开始之前未被删除。

• insert：将新插入的行的创建版本号设置为当前系统的版本号。
• delete：将要删除的行的删除版本号设置为当前系统的版本号。
• update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。

其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。

binlog定义以及作用

binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。默认情况下，binlog是二进制格式的，不能使用查看文本工具的命令(比如cat,vi等)查看，而使用mysqlbinlog解析查看。

binlog是用来记录数据库增删改，不记录查询的二进制日志。

作用是用于数据恢复。(当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里。使用mysqldump备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到binlog日志了)

数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）？

事务的隔离性由锁机制实现

事务的原子性、一致性、持久性由事务的redo日志和undo日志来保证。

undo log回滚日志：

undo log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用了undo log来实现多版本并发控制(MVCC)

事务的原子性即事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(rollback)到事务开始前的状态，就像这个事务从来没有执行过。

undo log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方就叫做undo log)，然后进行数据的修改。如果出现了错误或者用户执行了rollback语句，系统可以利用undo log中的备份将数据恢复到事务开始之前的状态。

之所以能同时保证原子性和持久性，是因为以下特点：

-更新数据前记录undo log

-为了保持持久性，必须将数据在事务提交前写到磁盘，只要事务成功提交，数据必然已经持久化

-undo log必须先于数据持久化到磁盘，如果在G,H之间系统崩溃，undo log是完整的， 可以用来回滚事务。

-如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。

如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即Redo Log。

redo log重做日志:

原理和undo log相反，redo log记录的是新数据的备份。在事务提交前，只要将redo log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是redo log已经持久化，系统可以根据redo log的内容，将所有数据恢复到最新的状态。

MySQL的binlog有几种录入格式？分别有什么区别？

有三种格式：statement;row;mixed

• statement模式下，记录单元为语句，即每一个sql造成的影响会被记录。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制
• row级别下，记录单元为每一行的改动，基本是可以全部记下来的，但是由于很多操作，会导致行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大
• mixed，一种折衷的方案普通操作使用statement记录，当无法使用statement的时候使用row

事务的并发；事务并发会引起的问题；事务隔离级别；MySQL默认的事务隔离级别；隔离的实现；

事务并发：从理论上来说，事务应该彼此完全隔离，即按顺序运行，但是这样的话会对性能产生极大的影响。实际上，一个数据库可能拥有多个访问客户端，这些客户端都可以以并发方式访问数据库。数据库中的数据可能会同时被多个事务访问，如果没有采取必要的隔离措施，就会导致各种并发问题。

事务的并发问题：

• 脏读：事务A读取事务B尚未提交的更改数据，并在这个数据的基础上操作，如果恰巧事务B进行回滚操作，那么事务A读到的数据根本是不被承认的。
• 不可重复读：事务A多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时先后两次读到的数据结果会不一致。(锁行)
• 幻读：一个事务中两次查询，但第二次查询比第一次查询多了或少了几行或几列数据。例如：事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据，而对应的这个数据项的数值还是为“1”并且提交给数据库。 而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有跟没有修改一样，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。(锁表)

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

4个事务隔离级别：

• Read uncommitted (未提交读)：这个隔离级别下，其他事务可以看到本事务没有提交的部分修改，因此会造成脏读的问题(读取到了其他事务未提交的部分，而之后该事务进行了回滚)
• Read committed (已提交读)：可避免脏读情况发生。其他事务只能读取到本事务已经提交的部分，这个隔离级别有不可重复读的问题，在同一个事务内的两次读取，拿到的结果竟然不一样，因为另外一个事务对数据进行了修改。
• Repeatable Read（可重复读）：该隔离级别要求事务只能读取在它开始之前已经提交的事务对数据库的修改,在它开始以后,所有其他事务对数据库的修改对它来说均不可见.从而实现了可重复读,但是仍有可能幻读。该隔离级别下，事务会锁定查询中使用的所有数据以防止其他用户更新，但其他用户可以将新的幻像行插入数据集，且幻想行包括在当前事务后续读取中。
• Serializable （可串行化）：是最高的事务隔离级别。同一时候代价也花费最高，性能非常低，一般非常少使用，在该级别下，事务顺序运行，不仅能够避免脏读、不可反复读，还避免了幻读。

MySQL默认的事务隔离级别为REPEATABLE-READ

• 查看当前会话隔离级别

select @@tx_isolation;

• 查看系统当前隔离级别

select @@global.tx_isolation;

• 设置当前会话的隔离级别

set session transaction isolation level repeated read;

• 设置系统当前隔离级别

set global transaction isolation level repeated read;

Oracle 支持的 2 种事务隔离级别：READ_COMMITED , SERIALIZABLE。默认系统事务隔离级别是READ_COMMITED(读已提交)。

事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持.

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

4个隔离级别是怎么实现的？

MySQL的InnoDB存储引擎才支持事务，其中可重复读是默认的隔离级别。

读未提交和串行化基本上是不需要考虑的级别，前者不加锁限制，后者相当于单线程执行，效率太差。

读已提交解决了脏读问题，行锁解决了并发更新的问题。并且MySQL在可重复读级别解决了幻读问题，是通过行锁和间隙锁的组合实现的。

可重复读：

MySQL 采用了 MVVC (多版本并发控制) 的方式。

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，而这个字段就是使其产生的事务的 id，事务 ID 记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。

按照上面这张图理解，一行记录现在有 3 个版本，每一个版本都记录这使其产生的事务 ID，比如事务A的transaction id 是100，那么版本1的row trx_id 就是 100，同理版本2和版本3。

在上面介绍读提交和可重复读的时候都提到了一个词，叫做快照，学名叫做一致性视图，这也是可重复读和不可重复读的关键，可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照。

对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

1.当前事务内的更新，可以读到；

2.版本未提交，不能读到；

3.版本已提交，但是却在快照创建后提交的，不能读到；

4.版本已提交，且是在快照创建前提交的，可以读到；

利用上面的规则，再返回去套用到读提交和可重复读的那两张图上就很清晰了。还是要强调，两者主要的区别就是在快照的创建上，可重复读仅在事务开始是创建一次，而读已提交每次执行语句的时候都要重新创建一次。

并发写问题

存在这种情况，两个事务，对同一条数据做修改。最后结果肯定要是时间靠后的那个事务修改的结果。并且更新之前要先读数据，这里所说的读和上面说到的读不一样，更新之前的读叫做“当前读”，总是当前版本的数据，也就是多版本中最新一次提交的那版。

假设事务A执行 update 操作， update 的时候要对所修改的行加行锁，这个行锁会在事务提交之后才释放。而在事务A提交之前，事务B也想 update 这行数据，于是申请行锁，但是由于已经被事务A占有，事务B是申请