MySQL所有常见问题

发布时间:2023年12月31日

一、事务

定义:一组操作要么全部成功,要么全部失败,目的是为了保证数据最终的一致性

在MySQL中,提供了一系列事务相关的命令:

  • start transaction | begin | begin work:开启一个事务
  • commit:提交一个事务
  • rollback:回滚一个事务

事务的ACID

  • 原子性(Atomicity):当前事务操作要么同时成功,要么同时失败。原子性由undo log日志来保证
  • 一致性(Consistency):使用事务的最终目的,由其他三个特性保证
  • 隔离性(Isolation):事务并发执行时,内部操作互不干扰。InnoDB中隔离性由各种锁和MVCC保证
  • 持久性(Durability):一旦提交事务,它对数据的改变是永久性的。持久性由redo log日志来保证

事务的隔离级别

  • read uncommit(读未提交):处于该隔离级别的数据库,脏读、不可重复读、幻读问题都有可能发生
  • read commit(读已提交):处于该隔离级别的数据库,解决了脏读问题,不可重复读、幻读问题依旧存在
  • repeatable read(可重复读):处于该隔离级别的数据库,解决了脏读、不可重复读问题,幻读问题依旧存在
  • serializable(串行):处于该隔离级别的数据库,以上问题全部解决

脏读、幻读、不可重复读问题

脏读:读取到其他事务未提交的数据,由于数据还没提交,因此可能产生回滚
 

脏读


幻读:主要针对插入删除操作来说,比如事务A对全部数据的某一字段做了修改并提交,若事务A提交前,事务B插入了一条数据,事务A再次查询会发现存在修改未生效的数据,如同幻觉
 

幻读


不可重复读:多次读取同一数据得到不同结果

不可重复读

区别:

  1. 脏读重在指一个事务读到了其他事务未提交的数据。
  2. 不可重复读主要在于一个事务中多次读到同一条数据,但前后读到的结果不一样,这是因为其他事务对数据进行修改并提交导致。
  3. 幻读则是因为被其他事务插入或者删除的数据影响,一个事务内同样条件的数据记录变多或者变少了。

MVCC

MVCC机制的全称为Multi-Version Concurrency Control,即多版本并发控制技术,主要是为了提升数据库并发性能而设计的,其中采用更好的方式处理了读-写并发冲突,做到即使有读写冲突时,也可以不加锁解决,从而确保了任何时刻的读操作都是非阻塞的。

对于使用 InnoDB 存储引擎的数据库表,它的聚簇索引记录中都包含下面两个隐藏列:

  • trx_id,当一个事务对某条聚簇索引记录进行改动时,就会把该事务的事务 id 记录在 trx_id 隐藏列里;
  • roll_pointer,每次对某条聚簇索引记录进行改动时,都会把旧版本的记录写入到 undo 日志中,然后这个隐藏列是个指针,指向每一个旧版本记录,于是就可以通过它找到修改前的记录。

InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。

MCC的多版本主要依赖Undo-log日志来实现,而并发控制则通过表的隐藏字段+ReadView快照来实现,通过Undo-log日志、隐藏字段、ReadView快照,就实现了MVCC机制

MySQL中的日志

undo日志

undo log 有两个作用:提供回滚和多个行版本控制(MVCC)
undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。

  • 执行 rollback 时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
  • 在应用到行版本控制的时候,也是通过undo log来实现的:当读取的某一行被其他事务锁定时,它可以从undo log中分析出该行记录以前的数据是什么,从而提供该行版本信息,让用户实现非锁定一致性读取。
  • undo log是采用段(segment)的方式来记录的,每个undo操作在记录的时候占用一个undo log segment
  • undo log也会产生redo log,因为undo log也要实现持久性保护。
  • undo 日志一般会在事务提交时被删除,但是如果 undo 日志为 MVCC 服务 则暂时保留
  • 一个事务会产生多个 undo 日志,mysql有专门的 undo 页 保存 undo 日志。innodb 会为每一个事务单独分配 undo 页链表(最多分配 4 个链表)

redo日志

InnoDB独有,用于MySQL崩溃后重启时的数据恢复
更新表数据的时候,如果发现 Buffer Pool 里存在要更新的数据,就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存(redo log buffer)里,接着刷盘到 redo log 文件里。

刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数(默认1),它支持三种策略

  1. 设置为0的时候,表示每次事务提交时不进行刷盘操作,只是保留在 redo log buffer中,mysql 崩溃会丢失1s的数据;
  2. 设置为1的时候,表示每次事务提交时都将进行刷盘操作(默认值),持久化到磁盘;
  3. 设置为2的时候,表示每次事务提交时都只把redo log buffer内容写入page cache(OS Buffer),OS宕机会丢失1s的数据,因为未进行持久化;

InnoDB 存储引擎有一个后台线程,每隔1秒,就会把 redo log buffer 中的内容写到文件系统缓存(OS Buffer),然后调用 fsync 刷盘。
redo log buffer占用的空间即将达到 innodb_log_buffer_size 一半的时候,后台线程会主动刷盘。

binlog日志

binlog 是归档日志,属于 Server 层的日志,是一个二进制格式的文件,记录内容是语句的原始逻辑,类似于“给 ID=2 这一行的 c 字段加 1”。
不管用什么存储引擎,只要发生了表数据更新,都会产生 binlog 日志。它的主要作用就是数据备份、主从复制。
binlog会记录所有涉及更新数据的逻辑操作,属于逻辑日志,并且是顺序写。

binglog格式

binlog 日志有三种格式,可以通过binlog_format参数指定。

  1. statement :记录的内容是SQL语句原文,存在数据一致性问题;
  2. row:记录包含操作的具体数据,能保证同步数据的一致性;
  3. mixed:记录的内容是前两者的混合,MySQL会判断这条SQL语句是否可能引起数据不一致:如果是,就用row格式,否则就用statement格式。</
文章来源:https://blog.csdn.net/softshow1026/article/details/135313057
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。