数据库基础

一、数据库架构

如何设计一个关系型数据库？

                                        

要设计关系型数据库，首先要将其划分成两大部分。一个部分是存储部分，该部分主要是将数据持久化到存储设备当中去。第二个部分就是程序实例模块，来对存储部分进行逻辑上的管理。主要包含以下几个部分：将数据的逻辑关系转换成物理存储关系的存储管理模块；优化执行效率的缓存模块；将SQL语句进行解析的SQL解析模块；记录操作的日志管理模块；进行多用户管理的权限划分模块；灾难恢复模块；优化数据查询效率的索引模块；是数据库支持并发操作的锁模块。

二、索引模块

1、为什么要使用索引

索引（Index）可以避免让我们全表扫描去查找数据，提升检索效率。

问：什么样的信息能够成为索引

答：主键唯一键等只要能让数据具有唯一区分性的字段都能成为索引

问：索引的数据结构

答：主流是B+树，还有hash结构、以及bitmap等，其中MySQL不支持bitmap

2、索引常见的数据结构

• 二叉查找树索引介绍

二叉查找树规定父节点的左节点要小于父节点，右节点要大于父节点，查找的时间复杂度为O(logn)；但是在对节点进行增删改查之后，会打破树的平衡，转变为线性树，这样的树在时间复杂度方面会变为O(n)。不能满足查询效率的要求。

• B-tree索引介绍

B-tree树的每个节点中包含了关键字和指向孩子节点的指针。

B-tree的特征：

1. 根节点至少包含两个孩子
2. 树中每个结点最多有m个孩子结点（m>=2）；
3. 除根结点外，其它结点至少有(int)m/2+1个孩子结点；
4. 所有的叶结点都在同一层上。
5. 节点的排列顺序，其实就是根据父节点的关键字来排序，满足小于，大于小于，大于的规律。

B-tree很好好的规避的二叉查找树在执行删除插入操作之后无法达到平衡的缺点。

• B+-tree索引介绍

B+tree是B-tree的变体，其基本定义与B-tree相同，除了：

1. 非叶子节点的子树指针与关键字个数相同
2. 非叶子节点的子树指针P[i]，指向关键字值[K[i],K[i+1])的子树
3. 非叶子节点节点只用来作为索引，数据全部保存在叶子节点中
4. 所有叶子节点均有一个链指针指向下一个叶子结点，方便做统计操作，即<、>、in等操作

B+tree更适合用来做存储索引，原因：

1. B+tree的磁盘读写代价更低，因为B+树的非叶子节点不存放数据，只有在叶子节点中才存放数据；
2. B+树的查询效率更加稳定，因为非叶子节点不存放数据，查询数据时必须走到相同深度的叶子节点，所以每次查询的时间和效率都会稳定
3. B+树更利于对数据库的扫描，比如说范围查询

• Hash索引介绍

不经常使用，原因：

1. 仅仅能满足“=”，“in”，不能使用范围查询
2. 无法被用来避免数据的排序操作
3. 不能利用部分索引键查询
4. 不能避免表扫描
5. 遇到大量Hash值相等的情况后性能并不一定就会比B树索引高

• BitMap（位图索引）索引介绍

使用地方不是很多，不利于做并发操作

3、密集索引（聚簇索引）和稀疏索引（非聚簇索引）的区别

密集索引采用B+树的存储方式，它的叶子节点中不仅保存键值，还包含当前键值所在记录行的其他键位的信息，也就是说它的每个叶子节点都存储着一条记录。一个表只能创建一个密集索引，通常将主键设置为密集索引。

稀疏索引也是采用B+树的存储方式，它的叶子节点只保存键位信息和对应行数据的地址，定位到叶子结点之后，通过叶子节点中的数据地址去获取表中的数据。

4、MySQL的两种存储引擎——InnoDB和MyISAM

• InnoDB的特点：

1. 若一个主键被定义，该主键为密集索引
2. 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
3. 如不满足以上条件，innodb内部会生成一个隐藏的主键作为密集索引
4. 表中除了主键索引/密集索引之外的其他索引均为稀疏索引，且索引中的存储的是相关键位和对应的主键的值，通过主键的值来进行第二次查找，查找过程如下图：其中绿线为主键索引查找过程，红线为辅助索引（稀疏索引）查找过程。

                                      

• MyISAM的特点：

表中所有的索引均为稀疏索引，表的记录和索引是单独存放的（Innodb中的表记录和索引是一起存放的），无论是主键索引还是辅助索引都是只查找一次，都通过索引中键对应的地址去表中查找到相应的记录，查找流程如下：

                               

• 如何进行SQL调优

1. 根据慢日志定位到慢查询SQL
2. 使用explain分析sql
3. 修改sql或者尽量让sql走索引

• 索引是建立的越多越好么

这种说法是错的，原因：

1、数据量小的表不需要建立索引，建立会增加额外的索引开销。

2、数据变更需要维护索引，因此更多的索引意味着更大的维护成本

3、更多的索引需要更多的存储空间

三、锁模块

1、MyISAM与InnoDB关于锁方面的区别是什么

• MyISAM默认用的是表级锁，不支持行级锁。
• InnoDB默认用的是行级锁，也支持表级锁。

2、MyISAM适用的场景

• 频繁执行全表count语句。MyISAM用一个变量保存了整个表的行数
• 对数据进行增删改的频率不高，查询非常频繁。增删改会有锁表操作。
• 适合没有事务的场景

3、InnoDB适用的场景

• 数据增删改查都非常频繁
• 可靠性要求比较高，要求支持事务

4、数据库锁的分类

• 按锁的粒度划分，可分为表级锁、行级锁、页级锁
• 按锁级别划分，可分为共享锁、排他锁
• 按加锁方式划分，可分为自动锁、显式锁
• 按操作划分，可分为DML锁（表数据变更时加的锁）、DDL锁（表结构变更加的锁）
• 按使用方式划分，可分为乐观锁、悲观锁

5、数据库事务的四大特征（ACID）

• 原子性（Atomic）：事务包含的所有操作要么全部执行，要么全不执行，失败回滚
• 一致性（Consistency）：数据能够满足完整性约束，两个有关联的数据，一个改变，另一个也随之改变
• 隔离性（Isolation）：当多个用户并发访问数据 库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
• 持久性（Durability）：指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

6、事务的隔离级别以及各级别下的并发访问问题

(1)、事务并发访问引起的问题

• 更新丢失（Lost Update）：两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来
• 脏读（Dirty Read）：又称无效数据读出。一个事务读取另外一个事务还没有提交的数据叫脏读。
  例如：事务T1修改了一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后事务T1因为某种原因回滚了，那么事务T2读取的数据就是脏的
• 不可重复读（Non-Repeatable Read）：是指在一个事务中两次读同一行数据，可是这两次读到的数据不一样。
  例如：事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果
• 幻读：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据
  例如：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样。这就叫幻读

(2)、事务的隔离级别

以上的4种问题（更新丢失、脏读、不可重复读、幻读）都和事务的隔离级别有关。通过设置事务的隔离级别，可以避免上述问题的发生。

• 读未提交（Read Uncommitted）：读事务不阻塞其他读事务和写事务，未提交的写事务阻塞其他写事务但不阻塞读事务。
  此隔离级别可以防止更新丢失，但不能防止脏读、不可重复读、幻读。
  此隔离级别可以通过“排他写锁”实现。
• 读已提交（Read Committed）：读事务允许其他读事务和写事务，未提交的写事务禁止其他读事务和写事务。
  此隔离级别可以防止更新丢失、脏读，但不能防止不可重复读、幻读。
  此隔离级别可以通过“瞬间共享读锁”和“排他写锁”实现。
• 可重复读取（Repeatable Read）：以操作同一行数据为前提，读事务禁止其他写事务但不阻塞读事务，未提交的写事务禁止其他读事务和写事务。
  此隔离级别可以防止更新丢失、脏读、不可重复读，但不能防止幻读。
  此隔离级别可以通过“共享读锁”和“排他写锁”实现。
• 序列化（Serializable）：提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。
  此隔离级别可以防止更新丢失、脏读、不可重复读、幻读。
  如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免更新丢失、脏读，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

四、关键语法

• group by
• having
• 统计相关：count、sum、max、min、avg

五、数据库的五种范式

• 第一范式（1NF）：对于一张二维表，最基本的要求就是：每一个分量必须是不可分的数据项，也就是说同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。满足了这个条件的关系表就属于第一范式。即每一列属性都是不可再分的属性值，确保每一列的原子性；两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
• 第二范式（2NF）：第二范式的定义：如果关系表R属于第一范式，并且每一个非主属性完全函数依赖于某个候选键，则此关系表R属于第二范式。（关于函数依赖以及主键。候选键等概念可以查阅其他资料）。即每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
• 第三范式（3NF）：第三范式定义：数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话），这样的表结构，我们应该拆开来，如下：（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）
• BC范式（BCNF）：对于一个关系表（模式）R<U, F>属于第一范式，若X→Y且X不包含Y时X必含有键，则R属于BC范式。BCNF是在3NF的基础上消除了主属性对键的部分函数依赖和传递函数依赖。
• 第四范式（4NF）：关系表R<U, F>属于第一范式，如果对于R的每个非平凡多值依赖X→→Y（Y不包含于X），X都含有键，则称R属于4NF。4NF是在3NF的基础上消除了属性间的非平凡且非函数依赖的多值依赖。