数据库面试题锦集（MySQL）

谈谈MySQL的存储引擎

对于MySQL数据库而言，存储引擎就是指表的类型以及表在计算机上的存储方式，存储引擎这个概念也是MySQL数据库的一个特性，譬如Oracle就没有专门的存储引擎概念，只有有OLTP和OLAP模式的划分。

常见的三种存储引擎

MySQL数据库中的存储引擎有很多种，我们平时比较常见的，一般是InnoDB、MyISAM、MEMORY这三种，这个可以在指令窗口输入SHOW ENGINES进行查看。
三种引擎各自的特点

A、InnoDB引擎

InnoDB引擎是我们平时使用MySQL数据库默认的存储引擎，它为MySQL数据库表提供了良好的事务处理、崩溃修复能力和并发控制的功能。缺点是读写效率较差，占用的数据空间相对较大。

B、MyISAM引擎

MyISAM引擎是MySQL使用中常见的存储引擎，也曾经是MySQL的默认存储引擎。MyISAM的优点是占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

C、MEMORY引擎

MEMORY是MySQL中一个比较特殊的存储引擎。它的存储和读取都是基于内存的，包括建表插入和读取，包括数据，都是在内存上的，由于这个原因，可以知道MEMORY的读写速度会非常快，但同时也不适合存储过大的数据。

其实，在同一个数据库内也可以使用多种存储引擎，即对每个表进行针对性的设置。如果数据库表对事务的要求比较高，可以选择InnoDB引擎；如果数据库表对查询效率要求比较高，可以选择MyISAM存储引擎；如果数据库表是作为一个数据量小，且用于查询的临时表，那么可以选择MEMORY引擎。

MySQL中存储引擎InnoDB与MyISAM的比较

InnoDB引擎

支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
不存储总行数；
数据存储文件可以是一个文件，也有可能是多个，这个受操作系统文件大小的限制（一般为2G）；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，产生文件的大调整。

MyISAM引擎

不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作对整个表加锁；
存储表的总行数；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

因为MyISAM引擎相对简单，所以在查询效率上要优于InnoDB，即如果对数据的操作是读多、写少，对原子性要求也比较低，那么MyISAM是最好的选择。
但如果系统是偏业务性的，对数据的操作是写多、读少，尤其是高并发写入的时候，我们只能选InnoDB。

说说MySQL的索引类型，并分别简述一下各自的场景

普通索引：没有任何限制条件的索引，该索引可以在任何数据类型中创建。
唯一索引：使用UNIQUE参数可以设置唯一索引。创建该索引时，索引列的值必须唯一，但允许有空值。通过唯一索引，用户可以快速地定位某条记录，主键索引是一种特殊的唯一索引。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引耗时耗空间。
空间索引：只能建立在空间数据类型上。这样可以提高系统获取空间数据类型的效率。仅可用于 MyISAM 表，索引的字段不能为空值。使用SPATIAL参数可以设置索引为空间索引。
单列索引：只对应一个字段的索引。
多列索引：在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询，想使用该索引，用户必须使用这些字段中的一个字段。

从数据结构角度说下MySQL的索引分类

B+Tree索引：这是MySQL数据库使用最频繁的索引结构，也是存储引擎InnoDB和MyISAM默认的索引结构。
Hash索引：MySQL数据库中，存储引擎Memory默认的索引结构。

谈谈事务的 ACID 特性

MySQL数据库事务的四个基本要素，一般被简称为ACID，即：原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
A原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停 滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开 始前的状态，就像这个事务从来没有执行过一样。
C一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
I隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。 如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保 每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了 防止事务操作间的混淆， 必须串行化或序列化请 求，使得在同一时间仅有一个 请求用于同一数据。
D持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据 库之中，并不会被回滚。

谈谈乐观锁与悲观锁

从字面意思上看，就是很悲观，对于数据被修改持谨慎态度，认为数据随时会被修改，于是在整个处理过程中，都要对数据加锁，悲观锁一般可以依靠关系型数据库提供的锁机制实现，像我们一般接触到的表锁、行锁都是悲观锁。

就是很乐观，每次操作数据时都认为基本没有人会来修改它，所以也不去主加锁，但是在更新的时候需要去判断在此期间数据有没有被修改。这个一般需要用户自己去实现，所以也不会产生并发出现抢占资源的情况，只在提交操作时检查是否违反数据完整性即可。

乐观锁和悲观锁的使用

对于读的操作频度远高于写的操作的时候，这时候如果更新操作加锁就会阻塞所有的读取操作，会严重降低吞吐量，这时最好的策略就是使用乐观锁；如果读写比例差距不是很大，且系统资源也很充足，没有出现过吞吐量瓶颈问题，我们就可以选择使用悲观锁。

谈谈事务隔离级别

未提交读（READ UNCOMMITTED）：未提交读隔离级别一般也被称为读脏，就是事务可以读取其它事务未提交的数据。
已提交读（READ COMMITTED）：是在事务未提交之前所做的修改其它事务是不可见的，只能读取到已提交的数据。
可重复读（REPEATABLE READ）：保证同一个事务中的多次相同的查询的结果是一致的，比如一个事务一开始查询了一条记录然后过了几秒钟又执行了相同的查询，保证两次查询的结果是相同的，可重复读也是 mysql 的默认隔离级别。
可串行化（SERIALIZABLE）：可串行化就是保证读取的范围内没有新的数据插入，比如事务第一次查询得到某个范围的数据，第二次查询也同样得到了相同范围的数据， 中间没有新的数据插入到该范围中。

谈谈事务隔离级别导致的问题

脏读是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。

不可重复读

是指在一个事务内，多次读统一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次督导的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

事务隔离级别及事务隔离级别导致问题的对应关系

谈谈优化SQL的方法

选取最适用的字段类型，尽可能减少定义字段宽度；
尽量把字段设置NOTNULL，如一些’省份’、’性别’最好使用ENUM；
尽量使用连接(JOIN)来代替子查询；
适用联合(UNION)来代替手动创建的临时表；
优化事务处理，优化表格或数据锁定；
适当的使用外键；
建立合适的索引。

谈谈三大范式

第一范式：要求字段属性具有原子性，不可再分解。是对属性的原子性约束。
第二范式：要求非主属性完全函数依赖于键码。是对记录的唯一性约束，要求记录有唯一标识。
第三范式：要求非主属性不传递函数依赖于键码。是对字段冗余性的约束，即任何字段不能由其他字段派生出来。

范式化设计的优缺点

优点：减少数据冗余，使得更新速度快，表格体积小；
缺点：对于查询需要多个表进行关联的情况，降低了查询效率，难以进行索引优化。

反范式化设计的优缺点

优点：可以减少表的关联，更好地进行索引优化；
缺点：数据冗余以及数据异常，数据的修改需要更多的成本。

数据库索引设计原则

对查询频次较高，且数据量比较大的表建立索引；
使用唯一索引，区分度越高，使用索引的效率越高；
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合；
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率；
性能和效率方面考虑，索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就越高。对于插入/更新/删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，从而影响DML操作的效率。