参考文章:数据库面试问题集锦
一:数据库范式
找出关系模式中不合适的数据依赖,解决插入、删除、更新异常和数据冗余问题
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就会定义隐藏的主键然后在上面进行聚集。所以,对于聚集索引来说,你创建主键的时候,自动就创建了主键的聚集索引。
五:存储过程
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。进一步的说,存储过程是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候调用就行。存储过程的特点:
- 存储过程只在创建时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,使用存储过程可提高数据库执行效率
- 当SQL语句有变动时,可以只修改数据库中的存储过程而不必修改代码
- 减少网络传输,在客户端调用一个存储过程比执行一串SQL传输的数据量要小
- 通过存储过程能够使没有权限的用户在控制之下间接地存取数据库
系统存储过程、本地存储过程、临时存储过程、远程存储过程、扩展存储过程