MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是 bin log
(二进制日志)和 redo log
(重做日志)和 undo log
(回滚日志)。
bin log
bin log
是MySQL数据库级别的文件,记录对MySQL数据库执行修改的所有操作,不会记录select和show语句,主要用于恢复数据库和同步数据库。
redo log
redo log
是innodb引擎级别,用来记录innodb存储引擎的事务日志,不管事务是否提交都会记录下来,用于数据恢复。当数据库发生故障,innoDB存储引擎会使用redo log
恢复到发生故障前的时刻,以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit
设置为1,那么在执行commit时会将redo log
同步写到磁盘。
undo log
除了记录redo log
外,当进行数据修改时还会记录undo log
,undo log
用于数据的撤回操作,它保留了记录修改前的内容。通过undo log
可以实现事务回滚,并且可以根据undo log
回溯到某个特定的版本的数据,实现MVCC。
bin log
会记录所有日志记录,包括InnoDB、MyISAM等存储引擎的日志;redo log
只记录innoDB自身的事务日志。bin log
只在事务提交前写入到磁盘,一个事务只写一次;而在事务进行过程,会有redo log
不断写入磁盘。bin log
是逻辑日志,记录的是SQL语句的原始逻辑;redo log
是物理日志,记录的是在某个数据页上做了什么修改。binlog 有 3 种格式类型,分别是 STATEMENT(默认格式)、ROW、 MIXED,区别如下:
注意:不同的日志类型在主从复制下除了有动态函数的问题,同样对对更新时间也有影响。一般来说,数据库中的update_time都会设置成ON UPDATE CURRENT_TIMESTAMP,即自动更新时间戳列。在主从复制下:
如果日志格式类型是STATEMENT,由于记录的是sql语句,在salve端是进行语句重放,那么更新时间也是重放时的时间,此时slave会有时间延迟的问题;
如果日志格式类型是ROW,这是记录行数据最终被修改成什么样了,这种从库的数据是与主服务器完全一致的。
WAL(Wite-Ahead Logging)技术是一种数据库事务日志管理技术,它确保在修改真正的数据之前,先将修改记录写入日志,这使得即使系统崩溃,通过日志也能恢复教据,保证了数据的特久性和一致性
WAL它的核心思想就是先写日志,再写数据,大致执行流程如下:
在 MySQL InnoDB 存储引擎中,重做日志 (Redo Log)就是 WAL的实现,用于保证事务的持久性和崩溃恢复能力。InnoDB 重做日志的工作机制如下:
因为 redo log 是物理日志,记录“某页(Page)某位置的数据被修改为某值”。它不记录逻辑操作(如“插入一行”,而是直接记录对页的变更。所以在插入操作中,redolog 记录的是事务在数据页上的修改
数据页的插入点、记录的偏移量和插入的实际数据并更新页目录、页头等元数据
插入操作 redo log 具体执行流程
有三种格式,statement,row和mixed。
通过使用 undo log,数据库系统可以记录事务过程中每次数据修改之前的旧值。当事务失败或被中止时,数据库可以利用这些记录将数据恢复到事务开始之前的状态,从而保证事务的原子性。
Undo log 的这种机制确保了即使在事务执行过程中出现错误,数据库仍然能够保持一致性和原子性。
慢查询日志用于捕捉和记录执行时间超过指定阈值的 SQL 查询。慢查询日志在数据库性能优化和问题诊断中发挥着重要作用。
事务特性ACID:原子性(Atomicity
)、一致性(Consistency
)、隔离性(Isolation
)、持久性(Durability
)。
常见的InnoDB是支持事务的,但是MyISAM是不支持事务的
详情可以看这篇文章:MySQL事务原理:从ACID到隔离级别的全解析
第一范式1NF
确保数据库表字段的原子性。
比如字段 userInfo
: 广东省 10086'
,依照第一范式必须拆分成 userInfo
: 广东省
userTel
: 10086
两个字段。
第二范式2NF
首先要满足第一范式,另外包含两部分内容,一是表必须有一个主键;二是非主键列必须完全依赖于主键,而不能只依赖于主键的一部分。
举个例子。假定选课关系表为student_course
(student_no, student_name, age, course_name, grade, credit),主键为(student_no, course_name)。其中学分完全依赖于课程名称,姓名年龄完全依赖学号,不符合第二范式,会导致数据冗余(学生选n门课,姓名年龄有n条记录)、插入异常(插入一门新课,因为没有学号,无法保存新课记录)等问题。
应该拆分成三个表:学生:student
(stuent_no, student_name, 年龄);课程:course
(course_name, credit);选课关系:student_course_relation
(student_no, course_name, grade)。
第三范式3NF
首先要满足第二范式,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。
假定学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone),主键为"学号",其中学院id依赖于学号,而学院地点和学院电话依赖于学院id,存在传递依赖,不符合第三范式。
可以把学生关系表分为如下两个表:学生:(student_no, student_name, age, academy_id);学院:(academy_id, academy_telephone)。
2NF和3NF的区别?
先了解下几个概念:脏读、不可重复读、幻读。
幻读其实可以看作是不可重复读的一种特殊情况,单独把幻读区分出来的原因主要是解决幻读和不可重复读的方案不一样。
事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。
MySQL数据库为我们提供的四种隔离级别:
生产环境大多使用RC。为什么不是RR呢?
可重复读(Repeatable Read),简称为RR
读已提交(Read Commited),简称为RC
缘由一:在RR隔离级别下,存在间隙锁,导致出现死锁的几率比RC大的多!
缘由二:在RR隔离级别下,条件列未命中索引会锁表!而在RC隔离级别下,只锁行!
也就是说,RC的并发性高于RR。
并且大部分场景下,不可重复读问题是可以接受的。毕竟数据都已经提交了,读出来本身就没有太大问题!
MySQL 中并发事务的控制方式无非就两种:锁 和 MVCC。锁可以看作是悲观控制的模式,多版本并发控制(MVCC,Multiversion concurrency control)可以看作是乐观控制的模式。
锁 控制方式下会通过锁来显式控制共享资源而不是通过调度手段,MySQL 中主要是通过 读写锁 来实现并发控制。
读写锁可以做到读读并行,但是无法做到写读、写写并行。另外,根据根据锁粒度的不同,又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁,还支持行级锁,默认为行级锁。行级锁的粒度更小,仅对相关的记录上锁即可(对一行或者多行记录加锁),所以对于并发写入操作来说, InnoDB 的性能更高。不论是表级锁还是行级锁,都存在共享锁(Share Lock,S 锁)和排他锁(Exclusive Lock,X 锁)这两类。
MVCC 是多版本并发控制方法,即对一份数据会存储多个版本,通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号,版本号是唯一的。
MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。
MySQL 主要是通过:锁、RedoLog、Undo Log、MVCC 来实现事务:MySQL利用锁(行锁、间隙锁等等)机制,使用数据并发修改的控制,满足事务的隔离性
它会记录事务对数据库的所有修改,当 MSQL 发生宕机或崩溃时,通过重放redolog 就可以恢复数据,用来满足事务的持久性。
Redo Log(重做日志),UndoLog(回滚日志),它会记录事务的反向操作,简单地说就是保存数据的历史版本,用于事务的回滚,使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性
MVCC(多版本并发控制),满足了非锁定读的需求,提高了并发度,实现了读已提交和可重复读两种隔离级别,实现了事务的隔离性。
其实事务主要是为了实现一致性,具体是通过 AID,即原子性、隔离性和持久性来达到一致性的目的。
MySQL事务的二阶段提交是指在MySQL中,为了确保redo log(重做日志)和binlog(二进制日志)之间的一致性,使用的一种机制,MySQL 通过二阶段提交来保证在crash recovery(崩溃恢复)时,不会出现数据丢失或数据不一致的情况。
二阶段提交的两个阶段:
详情可以看这篇文章:Mysql篇-三大日志
MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。
SERIALIZABLE 隔离级别是通过锁来实现的,READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过, SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制,就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。
MyISAM 仅仅支持表级锁(table-level locking),一锁就锁整张表,这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking),还支持行级锁(row-level locking),默认为行级锁。
行级锁的粒度更小,仅对相关的记录上锁即可(对一行或者多行记录加锁),所以对于并发写入操作来说, InnoDB 的性能更高。
表级锁和行级锁对比:
MySQL 的 InnoDB 存储引擎实现的行级锁,实际上锁定的是基于索引的行记录。这意味着,行锁针对的是数据表中的索引记录,而非数据表本身的物理行。
InnoDB 的行锁是针对索引字段加的锁,表级锁是针对非索引字段加的锁。当我们执行 UPDATE
、DELETE
语句时,如果 WHERE
条件中字段没有命中唯一索引或者索引失效的话,就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到,一定要多多注意!!!
不过,很多时候即使用了索引也有可能会走全表扫描,这是因为 MySQL 优化器的原因。
InnoDB 行锁是通过对索引数据页上的记录加锁实现的,MySQL InnoDB 支持三种行锁定方式:
在 InnoDB 默认的隔离级别 REPEATABLE-READ 下,行锁默认使用的是 Next-Key Lock。但是,如果操作的索引是唯一索引或主键,InnoDB 会对 Next-Key Lock 进行优化,将其降级为 Record Lock,即仅锁住索引本身,而不是范围。
一些大厂面试中可能会问到 Next-Key Lock 的加锁范围,详细可以查看:行级锁怎么加的? 。
SELECT 的读取锁定主要分为两种方式:共享锁和排他锁。
select * from table where id
这两种方式主要的不同在于LOCK IN SHARE MODE
多个事务同时更新同一个表单时很容易造成死锁。
申请排他锁的前提是,没有线程对该结果集的任何行数据使用排它锁或者共享锁,否则申请会受到阻塞。在进行事务操作时,MySQL会对查询结果集的每行数据添加排它锁,其他线程对这些数据的更改或删除操作会被阻塞(只能读操作),直到该语句的事务被commit
语句或rollback
语句结束为止。
SELECT... FOR UPDATE
使用注意事项:
for update
仅适用于innodb,且必须在事务范围内才能生效。like
或者不等于,主键字段产生表锁。详细请查看:ReadView
MVCC(Multiversion concurrency control
) 就是同一份数据保留多版本的一种方式,进而实现并发控制。在查询的时候,通过read view
和版本链找到对应版本的数据。
作用:提升并发性能。对于高并发场景,MVCC比行级锁开销更小。
MVCC 实现原理如下:
Read View的规则
如果没有 MVCC,系统必须频繁地对读写操作进行加锁来保证数据的正确性,因为增加了锁的获取和释放的开销,会导致整体系统响应速度变慢,这种实现叫 LBCC (Lock-Based Concurrent Control).
表记录有两种读取方式。
快照读:读取的是快照版本。普通的SELECT
就是快照读。通过mvcc来进行并发控制的,不用加锁。
当前读:读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE
是当前读。
快照读情况下,InnoDB通过mvcc
机制避免了幻读现象。而mvcc
机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据,这时如果两次查询中间有其它事务插入数据,就会产生幻读。
如果查询条件用了索引/主键,那么select ... for update
就会加行锁。
如果是普通字段(没有索引/主键),那么select ..... for update
就会加表锁。
首先,InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。
mvcc
来避免幻读。next-key
来避免幻读(加行锁和间隙锁来实现的)。next-key包括两部分:行锁和间隙锁。行锁是加在索引上的锁,间隙锁是加在索引之间的。
Serializable
隔离级别也可以避免幻读,会锁住整张表,并发性极低,一般不会使用。
数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。
version
字段,在修改提交之前检查version
与原来取到的version
值是否相等,若相等,表示数据没有被修改,可以更新,否则,数据为脏数据,不能更新。实现方式:乐观锁一般使用版本号机制或CAS
算法实现。在MySQL中,即使多个事务仅对同一条记录进行操作,也可能发生死锁。这通常与InnoDB存储引擎的锁管理机制有关。以下是一些触发这种情况的场景:
索引导致的锁竞争:如果事务在WHERE子句中使用不同的索引来查找相同的行,InnoDB可能会导致多个事务以不同顺序锁住这些索引,从而产生死锁。
自增锁(auto-increment lock):在使用自增列时,不同事务可能在等待获取表级锁来增加自增值,某些情况下也会出现死锁。
外键约束:外键检查过程中可能会涉及多个表,多个事务可能会因为不同的锁顺序而陷入死锁。
自动检测与回滚:MySQL自带死锁检测机制(innodb deadlock detect),当检测到死锁时,数据库会自动回滚其中一个事务,以解除死锁。通常会回滚事务中持有最少资源的那个。也有锁等待超时的参数(innodblock wait timeout),当获取锁的等待时间超过阈值时,就释放锁进行回滚
手动 kill 发生死锁的语句:可以通过命令,手动快速地找出被阻塞的事务及其线程ID,然后手动 kill它,及时释放资源
本文来自在线网站:seven的菜鸟成长之路,作者:seven,转载请注明原文链接:www.seven97.top
参与评论
手机查看
返回顶部