mysql知识点问题详解

一： mysql设计表三大范式

范式是符合某一种设计要求的总结，设计一个结构合理的关系型数据库，就必须要满足一定的范式。

1. 原子性

要保证列的原子性，保证列不可再被分。数据库的每一列都是不可分割的原子数据项，而不是集合，数组，记录等非原子数据项，如果实体中的某个属性有多个值时，必须拆分为不同的属性。

2.主键策略

第二范式是在第一范式的基础上建立的，满足第二范式一定要满足第一范式；数据库中每个实例或记录必须可以被唯一地区分，选取一个能区分每个实体的属性或者属性组，通常我们需要设计一个主键（主键不包含业务逻辑），作为实体的唯一标识。

3.非主依赖

满足第三大范式必须先满足第二大范式；在第二大范式的基础上，任何的非主属性不能依赖于其他的非主属性(消除传递依赖)，所有属性都应该和主键依赖。

二：数据库索引

索引是一种特殊的数据库结构，可以用来快速的查询数据库表中的特定记录。索引是提高数据库性能的重要方式。

1. 聚簇索引

将数据和索引放在一起，找到索引也就找到了数据。

2. 非聚簇索引

数据和索引分开的，索引结构的叶子节点指向了数据对应的行。

3. InnoDB索引

innodb采用的是聚簇索引，每个innodb表都会有一个聚簇索引，它使用的是B+树构建。叶子结点存储的数据是整行记录。一般情况下聚簇索引等同于主键索引，当一个表没有构建主键索引时，innodb会自动创建一个ROWID字段来构建聚簇索引。在innodb中，除了聚簇索引之外的所有都成为辅助索引，辅助索引中的叶子节点存储的数据是该行的主键值，检索时，innodb使用主键值在聚簇索引中搜索行记录。

4. MyISAM索引

myisam的数据文件和索引文件是分开的，采用的是非聚簇索引。myisam使用B+树构建索引树时，叶子节点总存储的键值为索引列的值，数据为索引所在行的磁盘地址。

5.B+树和B树的区别

1）B树的每个节点存储数据，所有的节点组成这棵树。B+树只有叶子节点存储数据，叶子节点包含了这棵树的所有数据，所有的叶子节点使用链表相连接，便于区间查找和遍历，所有的非叶节点起到索引的作用。

2）B树中叶节点包含的关键字和其他节点包含的关键字是不重复的；B+树的索引项只包含对应子数的最大关键字和指向该子树 的指针，不含有该关键字对应记录的存储地址。

3）B树中每个节点(非根节点）关键字个数的范围为[m/2(向上取整-1，m-1]，根节点为[1，m-1]，并且具有n个关键字的节点包含(n+1)棵子数。 B+树中每个节点(非根节点)关键字的个数范围为：[m/2（向上取整），m] ，根节点为[1，m]，具有n个关键字包含n个子树。

4）B+树中查找，无论查找是否成功，每次都是一条从根节点到叶节点的路径。

6.使用B+树的好处

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

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

3）B+数所有的数据都存储在叶子节点中，分支节点均为索引，方便扫库，只需要扫一遍叶子节点即可。但是B树的分支节点也存储的有数据，我们要找到具体的数据，需要进行一次中序遍历来扫描，所以B＋树更适合于在区间查询的情况。

三：数据库事务

数据库的事务就是一系列的sql操作。

1. 事务的四大特性

1）原子性：事务中包含的所有操作，要么都做，要么都不做，必须保证数据库是一致的。

2）一致性：数据库的事务操作在操作前和操作后都必须要满足约束规则。

3）隔离性：当多个用户共同访问数据库时，数据库为每个用户开启的事务相互间互不干扰。

4）持久性：事务一旦被提交，那么它对数据库的操作就是永久的，就算数据库发生故障也不能对其产生影响。

2. 事务中出现并发的问题

1）脏读：一个事务处理过程中读取了另外一个未提交的事务的数据。

2）不可重复读：一个事务在他运行期间，两次查找相同的表，查到的数据不同。

3）幻读：在一个事务中读取到了别的事务插入的数据，导致前后查询到的结果不同。

3. 事务的隔离级别

1）读未提交（Read Uncommitted）：在其中一个事务中，可以读取到其他的事务没有提交的数据变化。这种读取其他事务还没提交的现象会产生脏读问题。并且也无法限制不可重复读和幻读。

2）读已提交（Read Committed）：在其中一个事务中，可以读取到其他事物已经提交的数据变化。可以有效的防止脏读现象，但是无法限制不可重复读和幻读。

3）可重复读（Repetable Read）：在其中一个事务中，直到事务结束前，都可以反复读取到事务刚开始看到的数据，并且一直不会发生变化，这样就有效的解决了不可重复读和脏读问题。

4）串行（Serializable）：串行化，在多个事务运行时，只有运行完一个事务之后，才能运行其他事务。

4.事务更新一行数据的流程

先用排它锁将他锁定，然后记录redo，再将修改前的值复制到undo.log中去，修改当前行的值，填写事务编号，最后使用回滚指针指向undo.log中修改前的行，事务提交后，释放锁。

三：MVCC(多版本并发控制)

MVCC在mysql innodb中的实现主要是为了提高数据库的并发性能，用更好的方式去处理读写冲突，做到即使有读写冲突时，也可以不加锁。

1. 原理

通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本，通过比较版本号来决定数据是否显示出来，读取事务的时候不加锁就可以保证事务的隔离效果。

2. 优点

1）可以让读写互相不阻塞，读不阻塞写，写不阻塞读，提高数据的并发处理能力。

2）降低了死锁的概率，mvcc采用了乐观锁的方式，读取数据时，不需要加锁，写操作，只需要锁定必要的行。

3）解决了一致性读的问题，当我们朝向某个数据库在时间点的快照时，只能看到这个时间点之前事务提交更新的结果，不能看到这个时间点之后事务更新提交的结果。

3. 流程

首先获取事务自己的版本号，获取到Read view ，查询得到的数据和Read view中的版本号进行比较，如果不符合Read view规则，就需要undolog中的历史快照，最后返回符合规则的数据。

4.当前读和快照读

1）当前读：就是读取记录的都是最新版本，读取时还要保证去他并发事务不能修改当前的事务，会对读取的记录进行加锁。像;select lock in share mode(共享锁)，select for update;update,insert,delete(排它锁) 这些都是一种当前读。

2）快照读：不加锁的select操作就是快照读。快照读的前提是当前的隔离级别不是串行级别，串行级别下快照读会退化成当前读。

mvcc就是实现读-写不冲突，不加锁，这个读就是指的快照读，而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

5.Innodb存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空），这里的版本号并不是实际的时间值，而是系统的版本号。每次开始新的事务时，系统版本号都会自动递增。事务开始时候的系统版本号会作为事务版本号，用来和查询每行记录的版本号进行比较。
每个事务都有自己的版本号，这样事务内执行操作时，就可以通过版本号的比较来达到数据版本控制的目的。