MySql高级进阶之事务（MVCC）
1.概述
事务是在数据库一次执行一或多个操作时，要么一起成功，要么一起失败，保证了事务的原子性。
2.事务的特性
事务有四个特性：ACID
A（原子性）Atomicity: 一次操作加了事务后，就变成了操作成功的最小单位，要么所有的操作一起成功，要么一起失败。
C（一致性）Consistency：操作的结果和预期结果达成一致。
I （隔离性）Isolation：事务之间互相独立互不干扰，防止多个事务同时并发操作时，结果出现偏差。
D（持久性）Durability：当事务提交操作成功后，不可回滚，不可更改，数据库完整性不被破坏。
3事务设置
默认情况下, MySQL 启用自动提交模式（变量 autocommit 为 ON）。这 意味着, 只要你执行 DML 操作的语句,MySQL
会立即隐式提交事务。
3.1.查看 autocommit 模式
SHOW SESSION / GLOBAL VARIABLES LIKE ‘autocommit’
3.2、直接用 SET 来改变 MySQL 的自动提交模式:
SET SESSION / GLOBAL autocommit=0; 禁止自动提交
SET SESSION / GLOBAL autocommit=1;开启自动提交
3.3.用 BEGIN, ROLLBACK, COMMIT 来实现
BEGIN;#开启事务
INSERT INTO t_dept(NAME) VALUES('科技部');
ROLLBACK;#回滚
COMMIT;#提交
4事务会产生的问题
4.1.脏读
在并发情况下，A事务读取了，B事务更改后的数据（没提交），由于某种原因B事务回滚了，导致A事务读取了垃圾数据。
4.2.不可重复读
在并发情况下，A事务读取了第一次数据，B事务对数据进行更改，A事务第二次读取数据（读取的数据是B修改后提交的数据）
B事务的修改为；update
问题：
在同一个事务中两次读取的同一数据，但数据值却不一样
4.3.幻读
在并发情况下，A事务读取了第一次数据，B事务对数据进行更改，A事务第二次读取数据（读取的数据是B修改后提交的数据）
B事务修改为：insert、delete
与不可重复读的区别
不可重复读是查找的数据的值有问题
幻读是查找的数据条数有问题
问题：
在同一个事务中两次读取的同一数据，但数据条数却不一样
5事务产生问题的解决办法（事务的隔离级别）
5.1.读未提交：
在事务中读取数据时，可读取别的事务执行了，但未提交的数据
问题：脏读、不可重复读、幻读
5.2.读已提交：
在事务读取数据时，只读取到已提交的数据
问题：不可重复读、幻读
5.3.可重复读：
在事务读取数据时，会有一个版本计数器，在同一个事务中，读取的永远是同一个版本的数据（第一次查询的版本）
问题：幻读
5.4.串行化：
给数据库表加锁，同一个表只能一个事务访问（一次只允许一个事务），其他事务必须等待
问题：无问题
6.锁
6.1锁机制
锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的 锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的， 其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁
6.2行锁和表锁
6.2.1共享锁（S）
共享锁又称读锁，当事务进行读操作的时候可以加S锁，S锁表时事务只有读的权限，没有写的权限，另一个事务来的时候只能再加
S锁，不能加X锁（写锁），直到S锁被释放才能加X锁，通俗的来讲，就是加个读锁，允许多个事务一起读，但不能写。
6.2.2排他锁（X）
排他锁又称写锁，当事务进行写操作时加上排他锁后，在排他锁（X）释放之前其他事务不能对其进行操作，当前事务可以写也可以读
update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不 会加任何锁类型，如果加排他锁可以使用 select …for update 语句，加共享锁 可以使用 select … lock in share mode 语
6.2.3行锁和表锁
行锁和表锁是按照粒度而分的
表锁：在一个事务对一张表进行操作时，对表进行加锁，加表共享锁，就是允许多个事务一起读，加表排他锁，就是只允许当前事务读
或写。
优点：加锁快，不出现死锁。
缺点：并发低
行锁：给数据库表中的某一行加锁，对行加锁粒度较小，只有InnoDB支持，可加行共享锁，行排他锁
优点：效率高，高并发时
缺点：开销大，加锁慢，会出现死锁
7.MVCC
1.为什么要用MVCC?
MVCC（多版本并发控制 Multi-Version Concurrent Control），读未提交不能并发控制，串行话在进行读或写操作会锁表，MVCC主要针对于可重复读和读已提交的操作，解决不可重复读的问题，对于读取的数据进行版本的控制。
2.MVCC的原理？
使用版本链+undo log来解决
1.版本链：
每一个表中的主键作为聚簇索引的每条记录，包括两个隐藏列
1.trx_id：每当一个事务对记录进行操作的时候会在trx_id列存入事务的id
2.roll_point:指针指向上一个操作此的版本的日志位置，也就是undo log的位置
undo log存储的日志大概如图
形成版本链
3.readview
形成版本链之后，当一个事务访问一条记录，事务结束前，再去访问当前记录（只能访问上次版本的），对于这个版本的控制则需要readview。
1.readview是什么？
在 InnoDB 中设计了一个 ReadView 的概念，这个 ReadView 中主要包 含当前系统中还有哪些活跃的读写事务。在事务生成时产生readview.
1.将这些事务id存储于m_ids列里.
2.将这些事务最小的id存入min_trxid列.
3.将这些事务id最大值存入max_trxid列.
4.creator_trxid里存储当前事务的id（只有在增删改的时候才会产生事务id，读的事务id默认为0）
在访问数据时，会在聚簇索引中找到trx_id
当trx_id<min_trxid时，表示这个可以访问。在创建readview之前当前数据的事务已提交。
当trx_id>max_trxid时，这个不可以访问，在创建readview之后这个事务才被开启，所以它不能访问，则需要根据roll_point找下一个版本继续重复此判断，直到找到可以访问的版本
当min_trxid<trx_id<max_trxid时，需要判断这个trx_id在m_ids列里，如果在那么则证明不可以访问，需要根据roll_point找下一个版本继续重复此判断，直到找到可以访问的版本。（活跃事务已提交了可能会产生不可重复读）
当min_trxid<trx_id<max_trxid时，需要判断这个trx_id不在m_ids列里，如果在那么则证明可以访问，在这个事务执行前那个事务已经提交了
2.可重复读和读已提交区别
在 MySQL 中，READ COMMITTED 和 REPEATABLE READ 隔离级别的的 一个非常大的区别就是它们生成 ReadView 的时机不同。 READ COMMITTED：每次读取数据前都生成一个 ReadView
REPEATABLE READ：在第一次读取数据时生成一个 ReadView
8.sql优化
项目开发初期，数据量比较少，而对于开发环境和运维环境，项目在部署之后数据库的量非常大，这时每条sql执行的时间，同一时间并发会严重影响，每条sql的效率以及优化就显得格为重要。
1.查询的优化，尽量保证少量的全表扫描，多用主键或索引进行查询。（多采用where中的索引，或order by）
2.索引失效避免
2.1 在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索 引而进行全表扫描
解决办法：尽量不设置null值，使用默认为0等
2.2 尽量避免用!=或> 、<，引擎使用全表扫描，放弃使用索引
2.3 尽量少量使用where中or来连接
2.4 in 和not in也要慎用
2.5 模糊查询也会导致索引失效
2.6 少量进行运算操作，在where子句中
2.7 where子句中使用函数也会放弃索引
3.设计优化
3.1索引不宜过多，索引占储存空间
3.2select 后尽量不要用*
3.3varchar尽量使用，不用char
3.4尽量将类型设置为数值型（引擎在比对字符串时会逐一字符进行比对，而数值一步到位）
4.mysql中的效率查询手段
explain +查询语句
例如：explain select name ,age from user where type =1
494
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
