Mysql--日志、锁、RR的实现以及避免幻读，MyISAM 和 INNODB的区别等

最新推荐文章于 2024-01-28 23:25:09 发布

cj_eryue

最新推荐文章于 2024-01-28 23:25:09 发布

阅读量383

点赞数 1

分类专栏： MySql 文章标签： mysql 数据库索引

本文链接：https://blog.csdn.net/cj_eryue/article/details/115356327

版权

MySql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

mysql的事务隔离级别

Read uncommitted：读未提交，一个事务可以读取到另一个未提交事务的数据，会产生脏读；

Read committed：读提交，一个事务要等另一个事务提交后才能读数据，会产生不可重复读；（sqlserver oracle默认级别）

Repeatable read：重复读，就是在开始读取数据（事务开启）时，不再允许修改操作，可能产生幻读。（mysql默认级别）

Serializable：是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读，但效率低下，比较耗数据库性能，一般不使用

MyISAM 和 INNODB的区别？

1. 事务安全（MyISAM不支持事务，INNODB支持事务）

2. 外键 MyISAM 不支持外键， INNODB支持外键.

3. 锁机制（MyISAM只支持表锁，innodb支持行锁、表锁）

4. 查询和添加速度（MyISAM批量插入速度快）

5. 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）

6.MyISAM内存空间使用率比InnoDB低

7.InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

Innodb：frm是表定义文件，ibd是数据文件

Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

MyISAM 和 INNODB如何选择

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差

InnoDB为什么推荐使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

可重复读隔离级别如何实现的

--开启事务的时候，创建视图/快照

可重复读隔离级别的事务启动的时候会创建一个视图 read-view，这个视图是不会改变的，之后事务执行期间，即使有其他事务修改了数据，事务看到的仍然跟在启动时看到的一样。也就是说，一个在可重复读隔离级别下执行的事务，好像与世无争，不受外界影响。

读提交的级别的事务会在每次执行sql语句前重新生成一个read-view。

可重复读隔离级别怎么避免幻读

可重复读隔离级别（Repeatable Read）：可避免脏读，不可重复读。

即：可重复读隔离级别下，当前事务不能看到其他事务对已有记录的更新（A），但是会看到其他事务对记录的新增/删除（B）

A是如何保证的呢？

使用MVCC（多版本并发控制），表象。InnoDB 为每行记录添加了一个版本号，每当修改数据时，版本号加1。在读取事务开启时，系统会给事务一个当前版本号，事务会读取小于当前版本号的数据（快照读），

这时就算另一个事务插入一个数据并立马提交，新插入的这条数据的版本号会比当前读取事务的版本号高，读取不到这条记录，即select 语句可避免幻读，但是update和delete避免不了幻读（B产生的原因），因为对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。

在执行这几个操作时会读取最新的记录，即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。

（1）当查询条件是聚簇索引或唯一索引时：不会用到间隙锁，可防止幻读

（2）当id是非唯一索引时：会用到间隙锁，

就是同一个事务中，连续做两次当前读 (例如：SELECT * FROM user WHERE id = 10 FOR UPDATE;)，这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，即第二次的当前读，不会比第一次返回更多的记录 (幻象)。

　　　　如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生

间隙锁（next-key lock）（内在，真正的避免幻读）

快照读和当前读

快照读历史数据－mvcc

innodb的默认事务隔离级别是rr（可重复读）。它的实现技术是mvcc（MVCC只在读提交可重复读两种隔离级别下工作）。基于版本的控制协议。该技术不仅可以保证innodb的可重复读，而且可以防止幻读。但是它防止的是快照读，也就是读取的数据虽然是一致的，但是数据是历史数据。

当前读最新数据－next-key lock

如何做到保证数据是一致的（也就是一个事务，其内部读取对应某一个数据的时候，数据都是一样的），同时读取的数据是最新的数据。innodb提供了next－key lock，也就是结合gap锁与行锁，达到最终目的。

当前读由行锁+间隙锁实现

1. 快照读(snapshot read)

单条普通的select语句属于快照读--由mvcc+undolog实现

2.当前读(current read)

select for update , insert, update, delete 属于当前读

在RR级别下，快照读是通过MVCC(多版本控制)和undo log来实现的，当前读是通过加record lock(行锁)和gap lock(间隙锁)来实现的，即Next-Key Lock

行锁和间隙锁

行锁（Record Lock）：行锁锁定的是索引记录，而不是行数据，也就是说锁定的是key。行锁是通过给索引加锁来实现的如果没有索引，InnoDB会通过隐藏的聚簇索引来对记录进行加锁（全表扫描，也就是表锁）分为共享锁和排他锁

行级锁定实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。其他注意事项：

在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以即使是访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

即便在条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别。

什么是间隙锁？

正常等值条件并且值存在的情况下加的是行锁

如果等值条件值不存在的情况下加的是间隙锁，或者范围查询，加的也是间隙锁

举个例子：

根据主键id，不只是有五个行锁，还会有六个间隙锁，左开右闭原则，(-∞，5](5，10](10，15](15，20](20，25](25，+∞]

例如 select * from table where id = 10 for update; 等值条件，id是存在的，加行锁就可以了

select * from table where id = 7 for update; 等值条件，id不存在，加(5，10] 间隙锁，这范围间不允许插入数据，直到这个事务提交完成释放锁

select * from table where id > 24; 范围条件，加间隙锁

通过行锁+间隙锁的机制保证了事务A select之后，其他事务相应的insert操作会阻塞

Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。

什么是readView?

快照存放了当前活跃的一些事务版本号，以及上一个版本的地址. 用来做可见性判断

readview根据生成时间不同，产生了RC,RR两种可见性

RC（Read committed：读提交）：每条select创建一个新的readview ，所以导致读提交读到的都是最新提交的！

RR（Repeatable read：重复读）：事务开始的时候创建一个readview, 一直到事务结束都用的这个readview，也就避免了不可重复读

什么是MVCC？

多版本并发控制(Multi-Version Concurrency Control, MVCC)

仅在读提交和可重复读两种隔离级别下生效

每行记录字段都保存有一个最近变更事务Id 一个最新删除的事务Id

事务读数据的原则就是：读版本号小于等于当前版本的数据(意思就是读不到在当前事务之后修改的数据避免了不可重复读)

读删除事务版本号大于等于当前版本的数据(意思就是如果这条数据在之后的事务里删了，当前事务也不能再读了)

InnoDB实现mvcc 是通过 readview+undolog 来实现

mysql的日志介绍

有六种日志文件，
分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）

binlog是Mysql Service层记录的日志,记录的是数据库所有增删改操作（sql语句），逻辑日志; 适合用来备份，主从复制就是根据binlog 来实现数据同步的（想一下redis的AOF）;

undolog、redolog 是InnoDB引擎记录的日志，用以来支持事务。redolog记录修改后的值，记录的是数据库事务操作中产生的变化，，undolog记录事务操作前的数据值。

例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是5，修改后的值为15，那么undo日志为<T1, X, 5>，redo日志为<T1, X, 15>。

undolog 日志是Mysql用来实现事务原子性的，在InnoDB引擎中，

redolog 和binlog 的两阶段提交：

1、prepare阶段，写redo log；

2、commit阶段，写binlog并且将redo log的状态改成commit状态；

事务的持久化： redolog在事务执行的过程中不断记录事务操作的变化，redo日志有prepare和commit两种状态（来保证binlog与redolog 的一致性），

事务操作完成并且binlog写入完成时，relog会从prepare状态转变为commit 状态，若在事务过程中发生系统故障时，数据库会根据redolog日志状态（prepare状态）恢复到事务前的状态；若事务已成功提交但数据未更新，数据库会根据relog日志（此时为commited状态）更新到事务完成后的状态