MySQL面试中遇到的问题

  Java后端开发（大三实习），面试的时候遇到的MySQL的问题

1、三大范式

1. 第一范式：列不可再分，保证每列的原子性。
2. 第二范式：属性完全依赖于主键，满足第二范式必须先满足第一范式，要确保数据表中的每一列都与主键相关，而不是与主键（联合主键）的某一部分相关。
3. 第三范式：确保每列都与主直接相关，而不是间接相关，不能存在传递依赖。

---

2、事务

1. 是什么：
   是一组SQL语句，是一个独立的工作单元，一个事务中的所有语句要不全部执行成功，要不全部执行失败
2. 事务特性：ACID原则
   • 原子性：一个事务在逻辑上是不可分割的最小单元，整个事务的操作要不全部成功，要不全部失败。
   • 一致性：数据库总是从一个一致的状态转到另一个一致的状态。
   • 隔离性：针对并发事务而言，一个事务在提交以前对其他事务是不可见的。
   • 持久性：一旦事务提交成功，其修改的数据就会永久保存到数据库中。
3. 隔离级别：
   • 未提交读：该级别中事务的修改即使没有被提交，对其他事务而言也是可见的，事务可以提交其他事务修改完但未提交的数据，这种情况叫做脏读。
   • 提交读：满足了隔离性的简单定义，一个事务在提交以后对其他事务才可见，也叫不可重复读，因为俩次读的结果可能不一样。
   • 可重复读（MySQL的默认级别）：解决了不可重复读的问题，保证了多次读取结果一致。但是会幻读，InnoDb通过多版本并发控制解决了幻读的问题。
   • 可串行化：事务的最高隔离级别，解决了幻读，在读取每行时都会加锁，会导致大量的超时问题。

---

3、MVCC多版本并发控制
 通过记录多个修改的历史版本代替锁，实现事务间的隔离效果，保证非阻塞读。
MVCC实现核心：

1. 表的隐藏列：记录事务的id以及上个版本数据地址。
2. undo log：记录数据个版本修改历史即事务链。
3. Read View：读视图，判断哪些版本对于当前事务可见。

---

4、InnoDB和MyISAM

	InnoDB	MylSAM
事务控制	支持	不支持
外键	支持	不支持
全文索引	不支持	支持
数据行锁定	支持	不支持

InnoDB 安全性高，支持事务的处理，多表多用户的操作
MylSAM 节约空间，速度快

InnoDB使用MVCC支持高并发，实现了四个隔离级别。数据存在表空间中。

MySQL5.1以前，MylSAM是默认的存储引擎，读取快，是对整张表加锁。

---

5、索引

1. 索引的分类：普通索引、唯一索引、主键索引、组合索引、全文索引
2. 为什么索引可以提高查询效率?  Innodb引擎使用B+树作为索引的数据结构，在B+Tree中，所有数据记录结点都是按照键值的大小顺序存放在同一层的叶子节点上，而非叶子结点上值存储Key值，这样可以降低树的高度。
   如图，是一颗b+Tree，索引是建立在磁盘上的，如果要查找数据28，先把磁盘块1读取到内存中，此时发生了一次磁盘IO，然后通过二分查找确定28在17和35之间，锁定P2指针，将磁盘块3读取到内存中，发生第二次磁盘IO，同理，二分查找找到28在26和30之间，在读取磁盘块8，发生第三次磁盘IO，二分查找找到最后的结果28。这个过程一共发生了3次磁盘IO。3层的b+Tree可以存储上百万条数据，如果没有索引，百万条数据所需要的磁盘IO是巨大的。
3. 索引失效的情况：
   1. 使用like模糊查询前面有%会失效，后面有不会失效。
   2. or语句前后没有同时使用索引
   3. 组合索引没有遵循最左匹配原则
   4. 在索引字段上使用<、>、!= 会失效，优化方法，key>value or key <value
   5. 对索引字段进行计算操作或者使用函数失效
   6. 当mysql认为全表扫描要比索引快的时候，索引会失效
4. 聚簇索引和非聚簇索引
   1. 聚簇索引：是一种数据的存放方式，按照每张表的主键构造一棵b+Tree，叶子结点存放的是行数据记录，叶子结点也成为数据页。每张表只能有一个聚簇索引。
      优点：数据访问更快，对于主键的排序查找和范围查找快
      缺点：插入速度过度依赖于插入顺序，对于主键的顺序插入是最快的插入方式，否则会出现页分裂，影响性能。因此Innodb引擎一般需要添加一个自增的主键
   2. 非聚簇索引：建立在聚簇索引之上的索引称为辅助索引，辅助索引查找数据需要进行二次查找，先找到主键，再通过主键查找数据。
5. 联合索引：使用多个字段同时建立的索引叫联合索引，想要命中索引，需要按照联合索引的顺序来使用。

---

6、锁

1. 乐观锁：需要自己实现，可以在数据表中添加version字段实现，在修改数据的时候会读取version的值，在修改完毕以后再次读取，如果version的值没有改变，就进行修改，否则，修改失败。
2. 悲观锁：先获取锁再执行业务，select…for update是MySQL提供的悲观锁的方式，也可以在语法层面使用synchronized实现。悲观锁会加大数据库的开销，可能会导致死锁。

---

7、其他问题

1. 为什么尽量不要使用select * ？
   select * 可能会查询出多个不需要的字段，增加sql的执行时间，同时大量的多余字段会增加网络开销。还可能多出的不使用的列导致无法使用索引覆盖，增大查询成本。
2. 索引覆盖：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

---

更新：

1. 给性别添加索引有没有用？
   不合适，性别字段大量重复，而且性别字段不可能是主键，作为索引搜索的时候会进行回表操作，消耗大量的磁盘IO。