MySQL基础知识

本文主要是介绍MySQL基础知识，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

三范式

• 第一范式： 数据表的每一列都要保持它的原子特性，也就是列不能再被分割
• 第二范式： 属性必须完全依赖于主键，保证一张表只描述一件事情
• 第三范式： 保证每列都和主键直接相关，所有的非主属性不依赖于其他的非主属性

如何判断

判断的时候首先依次取每一个非主属性查看其与主键的关系,若每个非主属性与所有主键都相关,那么可以说这张表满足了第二范式。

更进一步,依次取每一个非主属性查看其与其他非主属性的关系,若每个非主属性之间相互独立,那么可以说这张表满足了第三范式。

二三范式区别

第二范式是完全函数依赖,第三范式是消除传递依赖。2NF消除的是部分依赖，针对的是主键包含多个字段的情况，要求非主键列（非关键字段）必须依赖整个主键（关键字段），而不能依赖其中的一个字段或几个字段；3NF消除的是传递依赖，消除的是A->B->C这种，就是虽然C和A有关系，但是是通过B建立起来的关系。

个人理解

第二范式是数据库不建议使用联合主键。第三范式是消除传递依赖

事务四大特性

• 原子性： 事务是一个不可分割的整体，事务开始后的所有操作，要么全部完成，要么全部不做。
• 一致性： 系统从一个正确的状态，迁移到另一个正确的状态。
• 隔离性： 每个事务的对象对其他事务的对象操作互相分离，事务提交前对其他事务不可见。
• 持久性： 事务一旦提交，则其结果是永久性的。

一致性解释：这是针对数据库层面而言，而不是业务层面，事务的一致性只是在数据库预先定义的约束有关，满足了约束即满足了一致性。
具体到某个表的某个字段，比如你在定义表的时候，给这个字段的类型是number类型，并且它的值不能小于0，那么你在某个 transaction 中给这个字段插入（更改）为一个 String 值或者是负值是不可以的，这不是一个“合法”的transaction，也就是说它不满足我们给数据库定义的一些规则（约束条件）。

事务隔离等级

• Read Uncommitted（读未提交）： 事务能读到不同事物没有提交（未commit）的数据结果，实际应用比较少，会产生脏读，事务已经读到其他事务未提交的数据，但数据被回滚，称为脏读。
• Read Committed（读已提交）： 事务读取其他事物已经提交的数据，读取到的是最新的数据，所以会出现在同一事务中 select 读取到的数据前后不一致，会出现不可重复读问题，不可重复读问题就是我们在同一个事务中执行完全相同的 select 语句时可能看到不一样的结果。
• Repeatable Read（可重复读）：mysql 默认事物隔离级别，在同一事务中多次读取同样的数据结果是一样的，解决了不可重复读的问题，此级别会出现幻读的问题，即当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的行。
• Serializable（串行化）： 最高的事物隔离级别，串行化强制事物排序阻塞，避免事物冲突，解决了上述所有的问题，它使用了共享锁，执行效率低下，会导致大量的超时和锁切换竞争现象，实际开发应用很少。
  

幻读和不可重复读都是在同一个事务中多次读取了其他事务已经提交的事务的数据导致每次读取的数据不一致，所不同的是：

• 不可重复读读取的是同一条数据
• 幻读针对的是一批数据整体的统计(比如数据的个数)

各类锁

上面对几种锁类型进行了简要分析，其实平时工作开发中接触到最多的还是行锁，行锁的实现有以下几种：

注意： 在Innodb 存储引擎中，行锁的实现是基于索引的
Record Lock(记录锁)：

它是会锁住索引记录，比如 update table where id = 1， id 是主键，然后在聚簇索引上对 id=1 的个索引记录进行加锁；

Gap Lock(间隙锁)：

实质上是对索引前后的间隙上锁，不对索引本身上锁,目的是为了防止幻读。

当使用范围条件查询而不是等值条件检索数据，并请求排他锁、或共享锁时，对于该范围内不存在的记录，不允许其修改插入。

举个例子：当表中只有一条id=101的记录，一个事务执行select * from user where user_id > 100 for update；此时另一个事务执行插入一条id=102的数据是会阻塞的，必须等待第一个事务提交后才能完成。

间隙锁是针对事务隔离级别为可重复读或以上级别的。

Next-Key Lock：

Next-Key Lock 是记录锁和间隙锁的结合，会同时锁住记录与间隙。

在innodb存储引擎中，如果没有通过 索引项 进行查询时：

①、在RR隔离级别下，会以Next-Key Lock的方式对数据行进行加锁，通过 行锁+间隙锁 实现了 “锁表” 的效果，但请记住这不是添加的表锁；

②、而在 RU、RC 隔离级别下还是只会锁行记录，为什么呢？因为在innodb存储引擎下的四种事务隔离级别中都支持行锁，但是间隙锁只存在于RR、Serializable 两种隔离级别下。

举个例子：当一个表的索引字段有以下值：1,3,5,8,10，那么一共会产生(1,3]，(3,5]，(5,8]，(8,10]等区间（区间左开右闭），id=5会同时锁定(3,5]，(5,8]两个区间。

MVCC 是什么：

锁机制可以控制并发操作，来保证一致性，但是系统开销会很大；在RC、RR的隔离级别下，MySQL的InnoDB存储引擎通过 MVCC (多版本并发控制) 机制来解决幻读。

使用MVCC时具体的体现是什么呢？

使事务在并发过程中，SELECT 操作不用加锁，读写不冲突从而提高性能。

那么实现MVCC机制的原理是什么呢？

其原理是通过保存数据在某个时间点的快照来实现的；通过在每行记录后面保存隐藏列来存放事务ID，这样每一个事务，都会对应一个递增的事务ID。

假设三个事务同时更新来同一行数据，那么就会对应三个数据版本，但实际上版本1、版本2并不是物理存在的，而是通过关联记录在undo log 回滚日志中，这样就可以通过undo log找回数据的历史版本，比如回滚的操作，会使用上一个版本的数据覆盖数据页上的数据。

RC级别下，每次SELECT生成一个新的ReadView
RR级别下，同一个事务使用第一次查询的ReadView

为什么有了MVCC还要引入间隙锁：

mysql数据库的主从复制依靠的是binlog。而在mysql5.0之前，binlog模式只有statement格式。这种模式的特点：binlog的记录顺序是按照数据库事务commit顺序为顺序的。

当不存在间隙锁的情况下，会有如下的场景：
master库有这么两个事务：

1、事务a先delete id<6，然后在commit前；
2、事务b直接insert id=3，并且完成commit；
3、事务a进行commit；

此时binlog记录的日志是：事务b先执行，事务a在执行（binlog记录的是commit顺序）

那么主库此时表里面有id=3的记录，但是从库是先插入再删除，从库里面是没有记录的。

这就导致了主从数据不一致。

为了解决这个bug，所以RR级别引入了间隙锁。

这篇关于MySQL基础知识的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！