104. 史上最全的数据库面试题，面试前刷一刷（二）

三、事务

1.什么是事务？

事务是对数据库中一系列操作进行统一的回滚或者提交的操作，主要用来保证数据的完整性和一致性。

2.事务四大特性（ACID）原子性、一致性、隔离性、持久性?

原子性（Atomicity）:

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）:

事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）:

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

持久性（Durability）:

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

3.事务的并发?事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别?

从理论上来说, 事务应该彼此完全隔离, 以避免并发事务所导致的问题，然而, 那样会对性能产生极大的影响, 因为事务必须按顺序运行， 在实际开发中, 为了提升性能, 事务会以较低的隔离级别运行， 事务的隔离级别可以通过隔离事务属性指定。

事务的并发问题

**1、脏读：**事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

**2、不可重复读：**事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。

**3、幻读：**幻读解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。

例如：事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作 这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有跟没有修改一样，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

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

事务的隔离级别

**读未提交：**另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读

**不可重复读：**事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。

**可重复读：**在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象

**串行化：**最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

特别注意：

MySQL默认的事务隔离级别为repeatable-read

MySQL 支持 4 种事务隔离级别.

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

**Oracle 支持的 2 种事务隔离级别：**READ_COMMITED , SERIALIZABLE

SQL规范所规定的标准，不同的数据库具体的实现可能会有些差异

MySQL中默认事务隔离级别是“可重复读”时并不会锁住读取到的行

**事务隔离级别：**未提交读时，写数据只会锁住相应的行。

**事务隔离级别为：**可重复读时，写数据会锁住整张表。

**事务隔离级别为：**串行化时，读写数据都会锁住整张表。

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

4.事务传播行为

PROPAGATION_REQUIRED：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。

PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。

PROPAGATION_MANDATORY：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。

PROPAGATION_REQUIRES_NEW：创建新事务，无论当前存不存在事务，都创建新事务。

PROPAGATION_NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。

PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

5.嵌套事务

什么是嵌套事务？

嵌套是子事务套在父事务中执行，子事务是父事务的一部分，在进入子事务之前，父事务建立一个回滚点，叫save point，然后执行子事务，这个子事务的执行也算是父事务的一部分，然后子事务执行结束，父事务继续执行。重点就在于那个save point。看几个问题就明了了：

如果子事务回滚，会发生什么？

父事务会回滚到进入子事务前建立的save point，然后尝试其他的事务或者其他的业务逻辑，父事务之前的操作不会受到影响，更不会自动回滚。

如果父事务回滚，会发生什么？

父事务回滚，子事务也会跟着回滚！为什么呢，因为父事务结束之前，子事务是不会提交的，我们说子事务是父事务的一部分，正是这个道理。那么：

事务的提交，是什么情况？

是父事务先提交，然后子事务提交，还是子事务先提交，父事务再提交？答案是第二种情况，还是那句话，子事务是父事务的一部分，由父事务统一提交。

参考文章：https://blog.csdn.net/liangxw1/article/details/51197560

四、存储引擎

1.MySQL常见的三种存储引擎（InnoDB、MyISAM、MEMORY）的区别?

两种存储引擎的大致区别表现在：

InnoDB支持事务，MyISAM不支持， 这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

MyISAM适合查询以及插入为主的应用。

InnoDB适合频繁修改以及涉及到安全性较高的应用。

InnoDB支持外键，MyISAM不支持。

从MySQL5.5.5以后，InnoDB是默认引擎。

InnoDB不支持FULLTEXT类型的索引。

InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表。

对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除，效率非常慢。MyISAM则会重建表。

InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’。

2.MySQL存储引擎MyISAM与InnoDB如何选择

MySQL有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

虽然MySQL里的存储引擎不只是MyISAM与InnoDB这两个，但常用的就是两个。

关于MySQL数据库提供的两种存储引擎，MyISAM与InnoDB选择使用：

INNODB会支持一些关系数据库的高级功能，如事务功能和行级锁，MyISAM不支持。

MyISAM的性能更优，占用的存储空间少，所以，选择何种存储引擎，视具体应用而定。

如果你的应用程序一定要使用事务，毫无疑问你要选择INNODB引擎。但要注意，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表。比如DELETE FROM mytable这样的删除语句。

如果你的应用程序对查询性能要求较高，就要使用MyISAM了。MyISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

有人说MyISAM只能用于小型应用，其实这只是一种偏见。如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，而不是单纯地依赖存储引擎。

现在一般都是选用innodb了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

MEMORY存储引擎

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。

值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

3.MySQL的MyISAM与InnoDB两种存储引擎在，事务、锁级别，各自的适用场景?

事务处理上方面

**MyISAM：**强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。

**InnoDB：**提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

锁级别

**MyISAM：**只支持表级锁，用户在操作MyISAM表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

**InnoDB：**支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

关于存储引擎MyISAM和InnoDB的其他参考资料如下：

http://blog.csdn.net/lc0817/article/details/52757194

https://www.cnblogs.com/kevingrace/p/5685355.html

五、优化

1.查询语句不同元素（where、jion、limit、group by、having等等）执行先后顺序?

1.查询中用到的关键词主要包含六个，并且他们的顺序依次为 select–from–where–group by–having–order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序 与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行

**from:**需要从哪个数据表检索数据

**where:**过滤表中数据的条件

**group by:**如何将上面过滤出的数据分组

**having:**对上面已经分组的数据进行过滤的条件

**select:**查看结果集中的哪个列，或列的计算结果

**order by 😗*按照什么样的顺序来查看返回的数据

2.from后面的表关联，是自右向左解析 而where条件的解析顺序是自下而上的。

也就是说，在写SQL语句的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表），而把能筛选出小量数据的条件放在where语句的最左边 （用小表去匹配大表）

其他参考资源：

http://www.cnblogs.com/huminxxl/p/3149097.html

2.使用explain优化sql和索引?

对于复杂、效率低的sql语句，我们通常是使用explain sql 来分析sql语句，这个语句可以打印出，语句的执行。这样方便我们分析，进行优化

**table：**显示这一行的数据是关于哪张表的

**type：**这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

all：full table scan ;MySQL将遍历全表以找到匹配的行；

index: index scan; index 和 all的区别在于index类型只遍历索引；

**range：**索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between ，等查询；

**ref：**非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；

**eq_ref：**唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；

**const，system：**当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，MySQL就能将该查询转化为一个常量。

**possible_keys：**显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

**key：**实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引

**key_len：**使用的索引的长度。在不损失精确性的情况下，长度越短越好

**ref：**显示索引的哪一列被使用了，如果可能的话，是一个常数

**rows：**MySQL认为必须检查的用来返回请求数据的行数

**Extra：**关于MySQL如何解析查询的额外信息。

3.MySQL慢查询怎么解决?

slow_query_log 慢查询开启状态。

slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）。

long_query_time 查询超过多少秒才记录。