面试官：来考考你几道常见的MySQL题目吧

三范式

三范式指定是：
第一范式：字段不可分（最小原子单元）
比如

ID	address
1	中国北京
2	美国纽约

应该改成

ID	Country	city
1	中国	北京
2	美国	纽约

第二范式：先满足第一范式，然后有主键，非主键字段依赖主键（每张表只描述一件事）
比如：

名称	值
产品编号	P001
订单编号	O001
产品长度	100
订购日期	2000-01-01

应该改成两张表（产品表，订单表）

产品编号	产品长度
P001	100

订单编号	订购日期
O001	2000-01-01

第三范式：非主键字段不能相互依赖;
比如

名称	值
订单编号	O001
订购日期	2000-01-01
顾客编号	P001
顾客姓名	TOM

顾客姓名依赖于非主键顾客编号，这就不满足第三范式

范式的出现能够最大减少字段的冗余，但是，没有冗余的设计不一定是好的设计。往往在获取某些字段的值时，需要关联多张表，导致降低数据库查询效率。设计数据库不能只按照三范式的要求，适当的采用反三范式，增加字段的冗余，减少表的关联，从而增加查询效率。这是一种空间换时间的设计。

事务

事务的特征(ACID)

A（atomicity）原子性：一个事务要不全部成功，要不全部失败
C（consistency）一致性：事务在执行前和执行后，数据都必须保证一致性
I（isolation）隔离性：多个并发事务之间要相互隔离，不能互相干扰
D（Durability）持久性：事务一旦被提交，对数据库数据的改变是永久的

事务并发问题

虽然事务的特征里有隔离性，但那是理想的状态。实际情况是，多个事务并发时，可能会有如下问题
1脏读：事务A读取事务B，然后事务B回滚，这时候A读到的是脏数据
2不可重复度：事务A多次读取事务B的同一个数据，事务B在期间修改数据并提交，导致事务两次读到的数据不一致
3幻读：事务A修改了一个表的全部数据，事务B往表里插入一条新的数据，事务A这时发现还有一条没被修改，觉得很奇怪，像幻觉一样，因此叫幻读

事务隔离级别

针对这些问题，mysql制定的事务的隔离级别。隔离级别越高，并解决的事务并发问题就越多。最高的隔离级别可以解决所有的并发问题。隔离级别如下

隔离级别	脏读	不可重复度	幻读
读未提交 read-uncommitted	是	是	是
读已提交 read-committed	否	是	是
可重读 repeatable-read	否	否	是
串行化 serializable	否	否	否

读未提交 read-uncommitted ：跟名字一样，这种隔离级别下，事务可以读取未提交的事务的数据。级别最低，没法解决事务并发问题。
读已提交 read-committed ：只能看到已提交的事务的执行结果，解决了脏读问题
可重读 repeatable-read(MySQL默认) ： 确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。解决了不可重复度问题（当然脏读也解决了）
串行化 serializable :就像串丸子一样，每个事务都会一个个执行，其实就变成没法并发了，单线程了。事务并发的问题都能解决了。当然，效率最低了。

索引

索引是数据库的重点考察对象

索引的分类

唯一索引，主键索引，组合索引，一般索引，全文索引

索引的优点

一个字：快（前提是索引不要乱加）
为啥快？
1）大大减少了服务器扫描的数据量（不是全表扫描啦）
2）将随机IO转变为顺序IO
3）帮助服务器避免排序和临时表

索引采用的数据结构

Hash表

这个是存储引擎为memory时，采用的数据结构。长这样：

（有点像hashMap）
对于每个数据的存储，存储引擎都会先用hash算法算出对应的hash码。相同的hash码的数据会形成一个链表进行存储。有点像队伍啦，要找其中一个人只要知道在哪一列，然后在这列依次寻找就可以了。同样的道理，要查找这种结构下的数据，首先会先用hash算法算出对应的hash码值，然后找到所在列对应的链表，然后遍历链表查询就可以了。
因此hash索引查询非常快，只要算出hash码值基本上就能快速的定位到数据。
但是，它也有些缺点（主要）：
1）hash索引只能进行等值查找，不能进行范围查找，比如where a>10这样就不行
2）hash索引下的数据不是通过hash值的顺序进行存储的，没法进行排序
3）不支持部分索引匹配查找，因为hash索引是根据全部的数据进行hash计算的
4）如果hash冲突很多的话，维护的代价就会很高（链表太长啦）

B+数

B+数索引有如下特点
1）所有的非子节点存的都是指针，指向下一个节点
2）B+数索引下，不同的存储引擎下叶节点存的东西也不同。存储引擎是MylSAM的话，叶节点存的是文件地址，通过这个文件地址去找到真正的数据。存储引擎是InnoDB的话，存的是数据
3）每个节点其实一页（16KB）的数据，由于非子节点存储是指针，一般为14个字节，那么非子节点（2层）能存的指针大小数量为1170x1170=1368900。再由这些指针指到第三层，那么一共能存储1368900x16=21902400个数据。一个三层的B+数能够存储2千万条的数据

聚簇索引和非聚簇索引

这个是针对B+数索引来说的，严格上来说是一种数据存储方式。其实就是上面B+树特点的第二条
聚簇索引：数据行跟索引文件存在一起
非聚簇索引：数据行跟索引文件分开存放

回表

回表：简单来说就是查多次，先查出主键值，然后再查出数据记录
回表是InnoDB才会出现。
假设这条sql
select * from student where age = 10;
在这里age是辅助索引，学生的id是主键索引，那么如果是InnoDB的存储引擎，用辅助索引age查到的并不是数据行，而是主键id，然后再利用这个主键id重新去以主键为索引的B+树里查找，最后找到数据行。这种需要在进行一次的查询称为回表
为什么MylSAM没有这种情况呢。因为MylSAM是非聚簇索引，索引文件和数据行分开。那么不管是辅助索引还是主键索引，查到的都是文件地址，再通过这个地址去找到数据，所以MylSAM不存在回表

索引覆盖

索引覆盖：只需在一棵索引数上就能获取出所有的数据。也就是指只需要查询一次，不需要进行回表
因为回表的原因是由于第一次查询的数据里没有找到需要的值，因此利用回表重新查找一次找到数据。那么如果在第一次查询的数据里就能找到需要的值，那么就不需要进行回表了。不进行回表，说明一次查询就命中目标，我们也称为索引覆盖**

组合索引

如何实现索引覆盖。可以使用联合索引，比如
select name from student where age = 10
可以把name和age联合索引，这样就不会回表了

最左匹配原则

这是联合索引需要遵守的原则。
假设有个联合索引（a,b,c）。那么
如果sql语句是
select * from table where a = ? and b =? and c =?这个会遵守最左匹配。
假如查询条件是a和c，那么只有遵守部分最左匹配。假如查询条件为b,c那么将不会遵守最左匹配。
所谓的最左匹配，即最左优先。在检索数据时从联合索引的最左边开始匹配。

索引下推

在MySQL5.6之前是没有索引下推的。
假设有一个联合索引（name，age）有这么一条sql：
select * from table where name like '张%' and age = 20
有无索引下推的区别如下：

无索引下推的话会忽略age这个字段查到两条记录，然后根据这两条记录的主键值分别进行回表查询。这个例子回表了两次。
有索引下推的话不会忽略age这个字段，会在索引内部进行判断，然后找出一条记录再进行回表。这个例子回表了一次。

存储引擎

memory

memory用得比较不多，因此它是基于内存的存储引擎，不能进行持久化。索引使用的数据结构是Hash表。Hash表的索引查询比较快，但是必须精准查找，不能范围查找。

InnoDB

MySQL5.5之后的默认存储引擎
它具有如下特点：
支持事务
支持行锁
是聚簇索引
支持外键
支持全文索引
总的来说，InnoDB的最大特点是支持事务，但是这个是牺牲性能换来。由于事务和行锁，所以适合更新删除插入等操作。5.5版本后，它也被设为默认的存储引擎。如果不知道用什么类的存储引擎，那用InnoDB是不会错的。

MylSAM

MySQL5.5之前默认的存储引擎
它的特点如下：
非聚簇索引
不支持事务
表级别的锁
MylSAM的特点就是性能，当然这是牺牲其他功能例如事务换来的。如果场景是查询为主，那么可以考虑用MylSAM。

这是InnoDB和MylSAM的对比图

数据库锁

从性能上来说，分为乐观锁和悲观锁
从对数据库的操作上来分，分为读锁和写锁（都是悲观锁）
从对数据的操作的粒度来分，分为表锁，页锁和行锁

乐观锁和悲观锁

严格来说，乐观锁和悲观锁是一种思想。不止在数据库，在其他技术上也有。
乐观锁则是持有乐观的态度，乐观的认为每次修改数据不会发生问题，只需要提交的时候做个检测。
悲观锁的意思是悲观的认为每次修改都会发生冲突，并发问题，所以每次修改就加上锁，保证不会发生这些问题。

乐观锁简单来说是在应用层面上做并发控制。
常用的是通过加版本号version或者时间戳timestamp来进行控制。
具体的做法是提交之前先比较本次的版本号和之前的版本号是否一致，一致的话就进行数据的更新，并且把版本号加一。时间戳也是类似。（这不就是cas吗？）

悲观锁是数据库层面的并发控制，在进行数据库操作的时候会对数据进行加锁（这里才是真正的加锁）。
悲观锁的实现又可分为读锁（共享锁）和写锁（排它锁）。刚好对应着上面的按照数据库的操作进行分类的情况。

读锁和写锁

读锁（共享锁）：实现原理是在末尾加上lock in share mode。这样的话，其他的session可以查看也继续可以加锁，但是无法直到加锁的session释放完锁。
写锁（排它锁）：实现原理是在末尾加上for update。比如select * from table for update。这样所有的row都会加上排他锁，加了排它锁后，其他session不能进行加读锁和排它锁。

行锁和页锁和表锁

行锁：开销大，加锁慢，容易出现死锁，出现锁冲突概率小（行比较小），锁粒度小，并发比较高。InnoDB支持，MylSAM不支持
表锁：开销小，加锁快，不容易出现死锁，出现锁的冲突概率大（毕竟锁了一整个表），锁粒度大，并发低。InnoDB和MylSAM都支持
页锁：介于行数和表锁之间