mysql数据库八股

数据库设计

三大范式

为什么要数据规范化？
1、 信息重复
2、更新异常
3、插入异常（无法正常显示信息）
4、删除异常 （丢失有效的信息）

第一范式

数据库表中的所有字段值都是不可分解的原子值

在上面表中 家庭信息和学历信息不满足原子性要求，不满足第一范式，进行调整如下:

第二范式

满足第一范式，需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（每张表只描述一件事情）

在上述表中，同一个订单号可以包括不同产品号，因此主键必须是“订单号”、“产品号”联合组成。

但可以发现 产品号、产品数量、产品价格与“订单号”、“产品号”都相关，但是订单金额 订单时间、订单人仅与订单号相关，与“产品号”无关

因此不满足第二范式要求进行调整如下分成两张表：

第三范式

满足第二范式前提下，需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关（消除传递依赖）

上表所有属性完全依赖于学号，满足第二范式，但是“班主任性别”、“班主任年龄”直接依赖于“班主任姓名”，并非主键“学号”

进行调整如下：

数据库的事务

事务内的语句要么全部执行成功，要么全部执行失败

事务的四大特性

原子性：原子性是指包含事务的操作要么全部执行成功，要么全部失败回滚。

一致性：一致性指事务在执行前后状态是一致的。
隔离性：一个事务所进行的修改在最终提交之前，对其他事务是不可见的。

持久性：数据一旦提交，其所作的修改将永久地保存到数据库中。

InnoDB如何保证事务的原子性、持久性和一致性

原子性：undo log。该log保存了事务发生之前的数据版本，可以用于回滚，从而保证事务原子性。

持久性：redo log。该log关注于事务的恢复。在重启Mysql服务的时候，根据redo log进行重做，从而使事务有持久性。

利用undo log+redo log保障一致性。事务中的执行需要redo log，如果执行失败，需要undo log回滚

数据库中多个事务同时进行可能会出现什么问题

脏读：事务A更新了数据，但还没有提交，这时事务B读取到事务A更新后的数据，然后事务A回滚了，事务B读取到的数据就成为脏数据了。

不可重复读：事务A对数据进行多次读取，事务B在事务A多次读取的过程中执行了更新操作并提交了，导致事务A多次读取到的数据并不一致。

幻读：事务A在读取数据后，事务B向事务A读取的数据中插入了几条数据，事务A再次读取数据时发现多了几条数据，和之前读取的数据不一致。

丢失修改：事务A和事务B都对同一个数据进行修改，事务A先修改，事务B随后修改，事务B的修改覆盖了事务A的修改。

SQL的事务隔离级别有哪些？

未提交读：一个事务在提交前，它的修改对其他事务也是可见的。
提交读：一个事务提交之后，它的修改才能被其他事务看到。
可重复读：在同一个事务中多次读取到的数据是一致的。
串行化：需要加锁实现，会强制事务串行执行。

数据库的隔离级别分别可以解决数据库的脏读、不可重复读、幻读等问题

隔离级别是如何实现的？

锁机制和MVCC(多版本并发控制)实现的，提交读和可重复读可以通过MVCC实现，串行化可以通过锁机制实现。

什么是MVCC？

MVCC意思是多版本并发控制，也就是同一条记录在系统中存在多个版本。

作用：在不加锁的情况下，解决数据库读写冲突问题，并且解决脏读、幻读、不可重复读等问题，但是不能解决丢失修改问题。

目的：保证数据一致性前提下提供一种高并发的访问性能。

前提知识：
1、当前读
读取的是数据库的最新版本，并且要保证读取时其他事务不会修改当前记录，所以会对读取的记录加锁
2、快照读
即不加锁读取操作，使用MVCC来读取快照中的数据，避免加锁带来的性能损耗

实现原理：

版本号
1、系统版本号：自增ID，每开启一个事务，系统版本号都会递增
2、事务版本号：即事务开始时的系统版本号，可以通过事务版本号的大小判断事务的时间顺序

行记录隐藏的列
DB_ROW_ID:
所需空间6byte，隐含的自增ID，用来生成聚簇索引，如果数据表没有指定的聚簇索引，InnoDB会利用它来创建聚簇索引
DB_TRX_ID:
所需空间6byte，最近修改的事务ID，记录这条记录或最后一次修改
DB_ROLL_PTR:
所需空间7byte，回滚指针，指向这条记录的上一个版本

undo日志
MVCC使用到的快照会存储在Undo日志中，该日志通过回滚指针将一个一个数据行的所有快照连接起来

举个例子，比如最开始的某条记录长这样：

现在来了一个事务对他的年龄字段进行了修改，变成了这样

现在又来了一个事务2对性别进行了修改

从上面的分析可以看出，事务对同一记录的修改，记录的各个会在undo日志中连接成一个线性表，在表头的就是最新的旧纪录。

小结：在InnoDB中，事务在开始前会向事务系统申请一个事务ID，该ID时按申请顺序严格递增的。每行数据具有多个版本，每次事务更新数据都会产生新的数据版本，而不会直接覆盖旧的数据版本。数据的行结构中包含多个信息字段。其中MVCC的主要涉及最近更改该行数据的事务ID（DB_TRX_ID）和可以找到历史数据版本的指针（DB_ROLL_PTR）。InnoDB在每个事务开启瞬间会为其构造一个记录当前已经开启但未提交的事务ID的视图数组。通过比较链表中的事务ID与改行数据的值与对应的DB_TRX_ID，并通过DB_ROLL_PTR找到历史数据的值以及对应的DB_TRX_ID来决定当前版本的数据是否应该被当前事务所见。最终实现在不加锁的情况下保证数据的一致性。

读提交和可重复读都基于MVCC实现，有什么区别？

在可重复读级别下，只会在事务开始前创建视图，事务中后续的查询共用一个视图。

而读提交级别下每个语句执行前都会创建新的视图。因此对于可重复读，查询只能看到事务创建前就已经提交的数据。而对于读提交，查询能看到每个语句启动前已经提交的数据。

索引

索引是啥？

索引是存储引擎中用于快速找到记录的一种数据结构。在关系型数据库中，索引具体是一种对数据库中一列或多列的值进行排序的存储结构。

索引种类

主键索引：数据列不允许重复，不能为NULL，一个表只能有一个主键索引（非空唯一）
唯一索引：数据列不允许重复，可以为NULL，索引列的值必须唯一的，如果是组合索引，则列值的组合必须唯一。
全文索引：对文本的内容进行搜索。根据关键字反向索引字符串较长的索引。
普通索引：基本的索引类型，可以为NULL
组合索引：由多个列值组成的索引。

索引优缺点

优点：
1、大大加快数据检索的速度
2、将随机I/O变成顺序I/O（B+树的叶子节点是连接在一起的）
3、加速表与表之间的连接
缺点：
1、建立索引需要占用物理空间，
2、创建和维护索引都要花时间，例如对数据进行增删改的时候需要维护索引

索引的数据结构

哈希表（hash）
树：二叉树、红黑树、AVL树（平衡二叉树）、B树、B+树

InnoDB存储引擎的索引类型：B+树（默认）和哈希索引。

InnoDB为什么用B+树

1、哈希表

优点：如果是等值查询，那么非常快

缺点：
（1）哈希冲突会造成数据散列不均匀，产生大量线性查询，比较浪费时间。
（2）不支持范围查询，当进行范围查询时，必须挨个遍历
（3）对于内存空间要求比较高

2、二叉查找树

左子树的键值<根的键值，右子树的键值大于根的键值，查询效率是o（logn）

缺点：
数据插入是递增或递减的顺序时，会退化成链表，但是这棵二叉树的查询效率就低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称AVL树。

3、平衡二叉树（AVL）

平衡二叉树在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。

缺点：
为了保证平衡，在插入数据的时候必须要旋转，比较适合写少读多的情况。通过插入性能损失来弥补查询性能提升，当读写请求一样多的时候怎么办？并且插入数据越多，树就会变得越深（因为是二叉），考虑把原来的有序二叉树变成有序多叉树，每个节点可以存储多个数据，由此引申到另一种结构B树

另一种说法：
索引放在索引文件中，存放在磁盘中，每次只能从磁盘中读取一个磁盘页的数据到内存中。平衡二叉树的物理实现是数组，逻辑结构上是平衡二叉树，但是在逻辑结构上相近的节点在物理结构上相差可能很远。因此，每次读取的磁盘页中由很多数据用不上，多次IO操作读取，即平衡二叉树没能充分利用磁盘预读功能

4、平衡多路查找树（B-Tree）

首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

B-树特性：
1、 ki(i=1,…n)为关键字，且关键字升序排序。
2、 Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：
1、根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2、比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3、根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4、比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5、根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6、在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

缺点：
每个节点都放了数据记录，占用了空间考虑根节点只存记录，不存数据。

5、B+树

B+树在B树上做了一些优化，如下：
1、叶子节点两两用指针相互连接，顺序查询性能更高。
2、非叶子节点放关键字，叶子节点放关键字和数据。

小结：
数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。
B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

聚簇索引

按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录。

InnoDB通过主键聚集数据，若没有定义主键，InnoDB会选择非空的唯一索引代替。若没有，会隐式的定义一个主键作为聚簇索引。

非聚簇索引

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

主键索引

InnoDB表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

辅助索引

在聚簇索引上创建的索引，辅助索引访问数据总是要二次查找。

特点：叶子节点存储的不是数据行的物理位置，而是主键值。

InnoDB辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含相应行数据的聚簇索引键。例如，下图为定义在Col3上的一个辅助索引：

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。