校招数据库知识整理（索引以及事务）

1 数据库范式

• 第一范式：列不可分，eg:【联系人】（姓名，性别，电话），一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF；

• 第二范式：有主键，保证完全依赖。eg:订单明细表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID，不符合2NF；

• 第三范式：无传递依赖(非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况)，eg:订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。

设计表结构的时候会用到。（面试中一般不会涉及）

2 数据库索引

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。

• 1

索引的实现通常使用B_TREE及其变种。索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

（4）文件索引和数据库索引为什么使用B+树?

文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。
而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。
数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，而红黑树这种结构，高度明显要深的多，并且由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性。
最重要的是，B+树还有一个最大的好处：方便扫库。 B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因。

 

2） 索引的优点

• 大大加快数据的检索速度，这也是创建索引的最主要的原因；

• 加速表和表之间的连接；

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

3）什么情况下设置了索引但无法使用？

• 以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
• OR语句前后没有同时使用索引；
• 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
• 对于多列索引，必须满足 最左匹配原则 (eg：多列索引col1、col2和col3，则 索引生效的情形包括 （col1）或（col1，col2）或（col1，col2，col3）)。

4）什么样的字段适合创建索引？

• 经常作查询选择的字段

• 经常作表连接的字段

• 经常出现在order by, group by, distinct 后面的字段

5）创建索引时需要注意什么？

• **非空字段：**应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

• **索引字段越小越好：**数据库的数据存储以页为单位，一页存储的数据越多，一次IO操作获取的数据越大效率越高。

6） 索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；

• 空间方面：索引需要占物理空间。

7）索引的分类

• 普通索引和唯一性索引：索引列的值的唯一性

• 单个索引和复合索引：索引列所包含的列数

• 聚簇索引与非聚簇索引：聚簇索引按照数据的物理存储进行划分的。
  对于一堆记录来说，使用聚集索引就是对这堆记录进行堆划分，即主要描述的是物理上的存储。正是因为这种划分方法，导致聚簇索引必须是唯一的。
  聚集索引可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了；
  而非聚集索引是把一个很大的范围，转换成一个小的地图，然后你需要在这个小地图中找你要寻找的信息的位置，最后通过这个位置，再去找你所需要的记录。

8） 主键、自增主键、主键索引与唯一索引概念区别

• 主键：指字段 唯一、不为空值 的列；

• 主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；

• 自增主键：字段类型为数字、自增、并且是主键；

• 唯一索引：索引列的值必须唯一，但允许有空值。主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，主键不允许有空值，所以不能说唯一索引也是主键。

9）主键就是聚集索引吗？主键和索引有什么区别？

主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引。
在SQLServer中，主键的创建必须依赖于索引，默认创建的是聚集索引，但也可以显式指定为非聚集索引。
InnoDB作为MySQL存储引擎时，默认按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。
如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。
所以，对于聚集索引来说，你创建主键的时候，自动就创建了主键的聚集索引。

 

3 数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。

（1）事务的特征

• 原子性(Atomicity)：事务所包含的一系列数据库操作要么全部成功执行，要么全部回滚；

• 一致性(Consistency)：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态；

• 隔离性(Isolation)：并发执行的事务之间不能相互影响；

• 持久性(Durability)：事务一旦提交，对数据库中数据的改变是永久性的。

（2）事务并发带来的问题

• 脏读：一个事务读取了另一个事务未提交的数据；

• 不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；

• 幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。

（3）隔离级别

隔离级别决定了一个session中的事务可能对另一个session中的事务的影响。ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持，分别是：

• READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没commit的数据，这样可能会提高性能，但是会导致脏读问题；

• READ COMMITTED：在一个事务中只允许对其它事务已经commit的记录可见，该隔离级别不能避免不可重复读问题；

• REPEATABLE READ：在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务commit或rollback。但是，其他事务的insert/delete操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复select的结果一样，除非本事务中update数据库。

• SERIALIZABLE：最高级别的隔离，只允许事务串行执行。

MySQL默认的隔离级别是REPEATABLE READ。

（4）mysql的事务支持

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：

• MyISAM：不支持事务，用于只读程序提高性能；
• InnoDB：支持ACID事务、行级锁、并发；
• Berkeley DB：支持事务。