数据库基础重要知识-CSDN博客

参考文章：数据库面试问题集锦

一：数据库范式

找出关系模式中不合适的数据依赖，解决插入、删除、更新异常和数据冗余问题

1、第一范式无重复的列

是在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体属性有多个值时，必须拆分为不同的属性。

2、第二范式属性完全依赖于主键【消除部分子函数依赖】

a：第二范式是在第一范式的基础上建立的，即满足第一范式必须先满足第一范式。

b：数据表中的每个实例或行必须可以被唯一区分，即实体的属性完全依赖于主关键字。

3、第三范式属性不依赖于其它非主属性【消除传递依赖】

在第二范式基础上，任何非主属性不依赖于其它非主属性

4、BCNF 3NF的改进

在第三范式的基础上，数据库表中不存在任何字段对任一候选关键字段的传递函数依赖，即任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）

1NF——2NF：消除非主属性对码的部分函数依赖

2NF——3NF：消除非主属性对码的传递函数依赖

3NF——BCNF：消除主属性对码的部分和传递函数依赖

BCNF——4NF：消除非平凡且非函数依赖的多值依赖

二：事务

a：事务是数据库系统区别于其它一切系统的重要特性之一

b：事务是一组具有原子性的SQL查询，或者说是一个独立的工作单元

符合ACID特性：原子性、一致性、隔离性和持久性

1、原子性

一个事务必须被视为不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。

2、一致性

事务将数据库从一个一致性状态切换到另一个一致性状态。在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。

3、隔离性

隔离性要求一个事务对数据库中数据的修改，在未提交完成前对其它事务是不可见的。

SQL标准中定义的四种隔离级别：隔离性由低到高，并发性由高到底

a：未提交读；b：已提交读；c：可重复读；d：可串行化

4、持久性

一旦事务提交，则所做的修改就会永远保存在数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失。

处理大事务

1、避免一次处理太多的数据

2、移出不必要在事务中的SELECT操作

三：MySQL存储引擎

MyISAM是MySQL的5.5版本之前的默认数据引擎，不支持事务处理；

InnoDB是MySQL的数据库引擎之一，最大的特点就是支持ACID兼容的事务功能

1、存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm存储表的定义，数据文件的扩展名是.MYD, 索引文件的扩展名是.MYI。

InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件）。InnoDB的大小只受限于操作系统文件的大小，一般2GB。

2、存储空间

MyISAM：可被压缩，存储空间小。支持三种不同的存储格式：静态表、动态表和压缩表

InnoDB：数据存储在表空间，会在主内存建立其专用的缓冲池用于高速缓冲数据和索引

3、可移植性、备份及恢复

MyISAM：数据以文件的形式存储，在备份和恢复时可单独针对某个表进行操作，不支持事务、不支持崩溃后的安全恢复。

InnoDB：免费的方案是拷贝数据文件、备份binlog或者用mysqldump

4、事务支持

MyISAM：强调的是性能，每次查询具有原子性，执行速度比InnoDB快，不提供事务支持

InnoDB：提供事务支持，外部键等高级数据库功能。具有事务、回滚和崩溃修复能力的事务安全型表

5、AUTO_INCREMENT

MyISAM：可以和其它字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，可以根据前面几列进行排序后递增。

InnoDB：InnoDB必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列

6、表锁差异

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

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

7、表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值

8、外键

MyISAM：不支持

InnoDB：支持

四：数据库索引

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据表中数据。索引的实现通常使用B_TREE及其变种。索引加速列数据访问，因为存储引擎不再去扫描整张表得到需要的数据，从根结点开始，根节点保存了字节点的指针，存储引擎会根据指针快速寻找数据。

1、索引的底层实现原理和优化

二叉搜索树和AVL树在数据量较大时，都会由于树的深度过大而造成I/O读写过于频繁，导致查询效率低下，因此对索引而言，多叉树结构是不二选择。B-Tree的各种操作能使B树保持较低的高度，从而保证高效的查询效率。

a：B-Tree（平衡多路查找树）

B_TREE是一种多路查找树，是一种动态查找效率很高的树形结构。B_TREE的查找类似二叉排序树的查找，所不同的是B-Tree每个结点上是多关键码的有序表，在到达某个结点时，先在有序表中查找，找到则查找成功；否则，按照对应的指针信息指向的子树中去查找，当到达叶子节点时，则说明树中没有对应的关键码。由于B_TREE的高检索效率，B-Tree主要应用在文件系统和数据库中，对于存储在硬盘上的大型数据库文件，可以极大程度减少访问硬盘次数，大幅提高数据检索效率。

b：B+Tree：InnoDB存储引擎的索引实现

是B_Tree树的变形树，一棵m阶的B+树和m阶的B_Tree的差异在于以下三点：

n棵子树的节点中含有那个关键码
所有的叶子节点中包含了全部关键码的信息，及指向含有这些关键码记录的指针，且叶子节点本身依关键码的大小自小而大的顺序链接。
非终端节点可以看成是索引部分，节点中仅含有其子树根结点中最大（最小）关键码

对于B+Tree，不管查找成功与否，每次查找都是走了一条从根到叶子节点的路径。

c：B+-Tree比B树更适合实际应用中操作系统的文件索引和数据库索引

1、B+Tree的磁盘读写代价更低：B+Tree的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小。如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也越多，相对来说IO读写次数也就降低了。

2、B+Tree的查询效率更加稳定：由于内部节点并不是最终指向文件内容的节点，而是叶子节点中关键字的索引，所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3、数据库索引采用B+Tree而不是B树的原因：B+树只要遍历叶子节点就可以实现整颗树的遍历，而B树只能中序遍历所有节点。

d：文件索引和数据库索引为什么使用B+树

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

2、索引的优点

大大加快数据的检索速度
加速表和表之间的连接
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
通过创建唯一性索引，可以保证数据库表中每一行睡的唯一性

3、什么情况下设置路索引但无法使用

以“%（表示任意0个或多个字符）”开头的LIKE语句，模糊匹配
OR语句前后没有同时使用索引
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转型为int型）
对于多列索引，必须满足最左匹配原则

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

经常作查询选择的字段
经常作表连接的字段
经常出现在order by，group by，distinct后面的字段

5、创建索引时需要注意的

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

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

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

6、索引的分类

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

7、主键、自增主键、主键索引和唯一索引

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

主键索引：即主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库会为主键创建一个唯一索引

自增主键：字段类型为数字，自增，并且是主键

唯一索引：索引列的值必须唯一，但允许有空值。

8、主键和索引有什么区别

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