mysql学习

有关索引的知识在之前的文章已经有写到了

事物

1.事物的四大特性(ACID)

原子性(Atomicity)：事物中的操作要么发生，要么不发生(通过undo log保存每次变更之前的记录，发生错误时可进行回滚)
一致性(Consistency)：无论事物是否执行成功，数据库仍是一个完整性的状态(数据库中的所有的数据都符合数据库中所定义的所有约束称为完整性的状态)，由原子性、隔离性、持久性保证
隔离性(Isolation)：多个用户并发访问时，一个用户的事物不能被其他用户的事物所干扰，多个并发事物之间的数据要相互隔离(由MVCC和lock保证)
持久性(durability)：一个事物一旦提交，对数据库的影响是永久性的，即使数据库发生故障也不应该对其有任何影响(由redo log保证，在每次真正修改数据之前，会将数据先写入到redo log中，在真正写入B+树)

2.事物并发产生的问题

第一类丢失更新

在没有事物隔离的情况下，两个事物同时更新了一行数据，其中一个事物更新失败后回滚，会导致另外一个事物更新也失败。

脏读

脏读就是当一个事物对一个数据进行修改还未提交时，另外一个事物访问该数据并且使用了该数据
例如：
张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交
与此同时，事务B正在读取张三的工资，读取到张三的工资为8000
随后，事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读

不可重复读

在同一个事物内，首先读取一个数据，然后在另外一个事物修改了该数据，导致前一个事物第二次读取该数据的值和第一次读取的值不一样
例如：
在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交
与此同时，事务B把张三的工资改为8000，并提交了事务
随后，在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

第二类丢失更新

在一个事物内，读取到一个数据，还未提交，另外一个事物更新该数据并提交，然后第一个事物再次更新该数据并提交，则第一个事物的更新会覆盖第二个事物的更新，导致第二个事物更新无效
例如：
事务A中，读取到张三的存款为5000，事务还没提交
与此同时，事务B，存储1000，把张三的存款改为6000，并提交了事务
随后，在事务A中，存储500，把张三的存款改为5500，并提交了事务，这样事务A的更新覆盖了事务B的更新

幻读

在一个事务中，读取该表相应条件下的数据，另外一个事物向该表中插入或者删除相同提交的一个数据，第一个事物再次读取该数据时，所获取的值与第一次获取的不一样
例如：
目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人
此时，事务B插入一条工资也为5000的记录
这时，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读
注意：
不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了
幻读的重点在于新增或者删除，同样的条件，第 1 次和第 2 次读出来的记录数不一样

3.事物隔离级别，解决了什么并发问题，还存在什么问题

未提交读(READ_UNCOMMITTED)

允许另外一个事物可以看到这个事物未提交数据
解决了第一类丢失更新问题，会出现脏读、不可重复读、第二类丢失更新问题、幻读

(已)提交读(READ_COMMITTED)

另外一个事物不能读取该事物未提交的数据
解决第一类丢失更新和脏读问题，会出现不可重复读、第二类丢失更新问题、幻读

可重复读(REPEATABLE_READ)

保证一个事物相同条件下前后获取的数据是一致的
解决第一类丢失更新、脏读、不可重复读、第二类丢失更新，会出现幻读

串行化(SERIALIZABLE)

事物被处理为按顺序执行
解决所有问题

注意：mysql默认的隔离级别为可重复读(REPEATABLE_READ)，按顺序隔离级别越来越高，并发性越来越低

锁

mysql常用存储引擎的锁

MyISAM和MEMORY采用表级锁（table-level locking）
BerkeleyDB采用页面锁（page-leve locking）或表级锁，默认为页面锁
InnoDB支持行级锁（row-level locking）和表级锁，默认为行级锁

各种锁特点

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

innodb实现两种锁

共享锁(S Lock 读锁)：允许事物读一行数据
排他锁(X Lock 写锁)：允许事物更新或者删除一行数据

MVCC(多版本并发控制)

InnoDB在实现MVCC时⽤到的⼀致性读视图，即consistent read view，⽤于⽀持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。
InnoDB⾥⾯每个事务有⼀个唯⼀的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。⽽每⾏数据也都是有多个版本的。每次事务更新数据的时候，都会⽣成⼀个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
也就是说，数据表中的⼀⾏记录，其实可能有多个版本(row)，每个版本有⾃⼰的row trx_id。
InnoDB为每个事务构造了⼀个数组，⽤来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。
数组⾥⾯事务ID的最⼩值记为低⽔位，当前系统⾥⾯已经创建过的事务ID的最⼤值加1记为⾼⽔位。
这个视图数组和⾼⽔位，就组成了当前事务的⼀致性视图（read-view）。
⽽数据版本的可⻅性规则，就是基于数据的row trx_id和这个⼀致性视图的对⽐结果得到的。这样，对于当前事务的启动瞬间来说，⼀个数据版本的row trx_id，有以下⼏种可能：1. 如果落在绿⾊部分，表示这个版本是已提交的事务或者是当前事务⾃⼰⽣成的，这个数据是可⻅的；2. 如果落在红⾊部分，表示这个版本是由将来启动的事务⽣成的，是肯定不可⻅的；3. 如果落在⻩⾊部分，那就包括两种情况
a. 若 row trx_id在数组中，表示这个版本是由还没提交的事务⽣成的，不可⻅；
b. 若 row trx_id不在数组中，表示这个版本是已经提交了的事务⽣成的，可⻅。

这样执⾏下来，虽然期间这⼀⾏数据被修改过，但是事务A不论在什么时候查询，看到这⾏数据的结果都是⼀致的，所以我们称之为⼀致性读。
更新数据都是先读后写的，⽽这个读，只能读当前的值，称为“当前读”（currentread）
除了update语句外，select语句如果加上lock in share mode(读锁) 或 for update(写锁)，也是当前读。