MySql教程

MySQL

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

MySQL

  • MySQL事务四大属性:ACID,原子性、一致性、隔离性、持久性。
  • MySQL事务隔离级别:读未提交、读已提交、可重复读、串行化。
  • MySQL索引:哈希或B+树。
  • MYSQL搜索引擎:InnoDB、MyISAM。
    • InnoDB:支持事务,默认可重复读、通过MVCC(多版本并发控制)+ Next-Key Locking防止幻读。主索引是聚簇索引,索引包含数据。支持在线热备份。
    • MyISAM:不支持事务,设计简单、不支持行级锁。表锁。读加共享(S)锁、写加排它(X)锁,支持并发插入读取时可插入数据。
  • 范式:1NF(属性不可分)、2NF(非主属性完全依赖于主键)、3NF(非主属性不可传递依赖)
  • 日志系统:redolog、undolog、binlog

日志

binlog

binlog记录数据库执行的写入性操作(不包括查询select),以二进制存于磁盘,由Server层记录,通过追加方式记录,max_binlog_size参数设置每个binlog文件大小,达到给定值后生成新文件保存日志。事务提交时记录binlog(位于内存中),通过sync_binlog //(取值0~N,0:系统判断;1:每次commit都将binlog写入磁盘;N:每N个事务,才将binlog写入磁盘)控制binlog刷盘(刷到磁盘中 )时机。

用途:

  • 主从复制
  • 数据恢复

格式:

  • STATEMENT:基于SQL语句复制,sql语句记录。节约性能。sysdate()、slepp()导致主从数据不一致
  • ROW:基于行的复制,只记录哪条数据被修改了
  • MIXED:STATEMENT和ROW混合复制

redolog

只记录事务对数据页做了哪些修改。redo log包括两部分:一个是内存中的日志缓冲(redo log buffer),另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句,先将记录写入redo log buffer,后续某个时间点再一次性将多个操作记录写到redo log file。先写日志再写磁盘(WAL技术 Write-Ahead Logging)。

在这里插入图片描述

undolog

逻辑日志,记录相反,一条insert操作对应一个delete操作。段的方式管理。rollback segment称为回滚段,每个回滚段中有1024个undo log segment。

一个事务开始,为防止事务回滚,先写入undolog。然后写入redolog(redo log buffer),根据innodb_flush_log_at_trx_commit决定何时写入redolog file。事务处于prepare阶段,执行器生成binlog日志,提交事务
在这里插入图片描述

主从复制

在这里插入图片描述

MVCC(多版本并发控制)

读写锁中读写操作仍然互斥。MVCC:写操作更新最新的版本快照,读操作去读旧版本快照,没有互斥关系。类似CopyOnWrite(这个之后会记录)。
事务修改(UPDATE、DELETE、INSERT)会为数据行增加一个版本快照。
脏读和不可重复读根本原因是:事务读取到其它事务未提交的修改。所以未解决该问题,规定只能读取已提交的快照。

版本号

  • 系统版本号SYS_ID:递增数字,每开始一个新事务,系统版本号就会自动递增。
  • 事务版本号TRX_ID:事务开始时的系统版本号

Undo日志

多个版本的快照存储在Undo日志中,通过回滚指针Roll_PTR把一个数据行的所有快照连接起来。INSERT、UPDATE、DELETE(额外设置为1)操作会创建一个日志将事务版本号(TRX_ID)写入。

ReadView

维护一个ReadView结构,主要包含当前系统未提交的事务列表TRX_IDs,还有该列表最小值(TRX_ID_MIN)和最大值(TRX_ID_MAX)。
进行SELECT操作,根据该行快照TRX_ID与TRX_ID_MIN和TRX_ID_MAX关系判断是否可用。

  • TRX_ID < TRX_ID_MIN:可以使用
  • TRX_ID > TRX_ID_MAX:不可使用
  • TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX:根据隔离级别
    • 提交读:如果TRX_ID 在TRX_IDs内,则事务未提交,不能使用;否则可以使用。
    • 可重复读:不可使用。

快照读和当前读

  • 快照读:SELECT不加锁,操作的是快照中的数据。
  • 当前读:其他操作(INSERT、UPDATE、DELETE)进行加锁。SELECT操作可以强制指定加锁。
SELETE * FROM table WHERE ? lock in share mode; //加S锁
SELETE * FROM table WHERE ? for update; //加X锁

Next-Key Locks

为了解决MVCC的幻读问题。

Record Locks

锁定记录上的索引,不是记录本身。 如果没设置索引,InnoDB自动在主键上创建隐藏的聚簇索引。

Gap Locks

锁定索引之间的间隙,不包括索引本身。

SELECT c FROM t WHERE c BETWEEN 10 AND 20 FOR UPDATE; //不能插入15

Next-Key Locks

将Gap Locks与Record Locks结合。锁定一个前开后闭的区间。

索引优化

补:
两段锁协议。事务分为两个阶段:1.获得封锁(扩展阶段),事务获得任何数据项上的任何类型的锁,不能释放。2.释放封锁(收缩阶段),事务可以释放任何数据项上的任何类型的锁,不能申请。保证可串行化的方法。
可串行化:多事务的并发执行正确,当且仅当其结果与某一次串行化执行的结果相同

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