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.释放封锁（收缩阶段），事务可以释放任何数据项上的任何类型的锁，不能申请。保证可串行化的方法。
可串行化：多事务的并发执行正确，当且仅当其结果与某一次串行化执行的结果相同