MySQL学习笔记3

慢查询日志：Slow_query_log

Set global slow_query_log =ON;

Show variables like ‘slow_query%’;

 

事物的基本概念：

1、事务是一组SQL语句的执行，要么全部成功，要么全部失败，不能出现部分成功，部分失败的结果。保证事务执行的原子操作。

2、事务的所有SQL语句全部执行成功，才能提交（commit）事务，把结果写回磁盘上。

3、事务执行过程中，有的SQL出现错误，那么事务必须要回滚（rollback）到最初的状态。

MyISAM：不支持事物的

InnoDB：支持事物，支持锁

事物的·ACID特性：

原子性（Atomic）、一致性（Consistency）、隔离性（isolation）、持久性（Durablity）

事物并发存在的问题：

脏读：事务B读取了事务A尚未提交的数据

不可重复读：事务B读取了事务A已经提交的数据，查询两次结果不一样（数据已提交、不可重复读）

虚读：事务B读取了事务A新增加的数据或者读不到事务A删除的数据（数据已提交、可重复读）

隔离级别（因为事务有并发执行）：查看隔离状态：select @@tx_isolation；（默认工作在可重复读级别）

1、TRANSACTION_READ_UNCOMMITTED。未提交读。说明在提交前一个事务可以看到另一个事务的变化。这样读脏数据，不可重复读和虚读都是被允许的。

2、TRANSACTION_READ_COMMITTED。已提交读。说明读取未提交的数据是不允许的。这个级别仍然允许不可重复读和虚读产生。

3、TRANSACTION_REPEATABLE_READ。可重复读。说明事务保证能够再次读取相同的数据而不会失败，但虚读仍然会出现。

4、TRANSACTION_SERIALIZABLE。串行化。是最高的事务级别，它防止读脏数据，不可重复读和虚读。

事务的处理命令：

查看MYSQL是否自动提交事务：select @@ autocommit；

0：表示手动提交事务

1：表示自动提交事务

设置事务提交方式为手动提交：Set autocommit =0；

Begin：开启一个事务

Commit：提交一个事务

Pollback：回滚一个事务到初始的位置

Savepoint point1：设置一个名字为point1的保存点

Pollback to point1 ：事务回滚到保存点point ，不是回滚到初始状态

SET TX_ISOLATION='REPEATABLE-READ'; 设置事务的隔离级别

SELECT @@ TX_ISOLATION; 查询事务的隔离级别

MVCC多版本并发控制机制实现

主要用在已提交读（oracle）、可重复读（mysql）

表级锁：对整张表加锁。开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发度低。

行级锁：对某行记录加锁。开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高。（行锁是对索引进行加锁）

间隙锁：用范围条件而不是相等条件检索数据，并请求共享或排它锁时，INNODB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫作“间隙（GAP）INnoDB也会对这个“间隙”加锁。使用间隙锁的目的是为了防止幻读，以满足串行隔离化要求。

Record lock：行锁

Gap lock：间隙锁

Next-key： lock:record lock + gap lock

辅助索引下的串行隔离下：不仅对满足条件的行加锁，也对行间的间隙加锁

如果age是辅助索引（值可以重复的），record lock + gap lock

如果age是主键索引或者唯一健索引（值是不允许重复的），record lock，这种情况没有gap lock

排它锁和共享锁

排它锁：x锁，写锁

共享锁：s锁，读锁

X和s锁关系：

1. ss可以兼容、xs、sx、xx之间是互斥的(只有共享锁才能够相互联系)
2. 一个事务对数据对象 O 加了 S 锁，可以对 O 进行读取操作但不能进行更新操作。加锁期间其它事务能对O 加 S 锁但不能加 X 锁。
3. 一个事务对数据对象 O 加了 X 锁，就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。
4. 显示加锁：select ... lock in share mode强制获取共享锁，select ... for update获取排它锁

InnoDB行锁是加在索引项上的，是给索引在加锁，并不是给单纯的行记录在加锁；索引如果过滤条件没有索引的话，使用的就是表锁，而不是行锁！！！

是索引就是行所，不是索引就是表锁

INnoDB提供了两个读取操作：

1. 锁定读：s（共享锁） x（排它锁）
2. 非锁定读：MVCC提供的快照读（依赖底层的undo log回滚日志）

事务日志：undo log（回滚日志）、redo log重做日志） ACD（事务日志）、I（隔离：通过锁+MVCC）

Undo log回滚日志主要作用：

1. 事务发生错误时混滚rollback
2. 提供了MVCC的非锁定读（快照读）

MCVV多版本并发控制中，读操作可以分为两类：

1. 快照读：读的是记录的可见版本，不用枷锁
2. 当前读：读取的是记录的最新版本

快照内容读取原则：

1. 版本未提交无法读取
2. 版本提交但是在快照创建后提交的，无法读取
3. 版本已提交，在快照创建前提交的，可以读取
4. 当前事务内自己的更新，可以读到

不可重复级别：

不可重复读无法避免原因：每一次select都会产生一次数据快照，其他事务更新后且已提交的数据可以实时反馈到当前事务的select结果当中

为什么无法解决幻读？ 因为每一次select都会产生一次数据快照，其他事务增加了和当前事物查询条件相同的新的数据并且已经被成功提交，导致当前事务再次以同样的条件查询时，数据多了。

可重复级别：

为什么解决了不可重复读？因为第一次产生数据快照，其他事务虽然修改了最新的数据，但是当前事务被select时，依然查看的是最初的快照数据

MVCC就是为了实现读-写冲突不加锁，这个读指的是快照读

MVCC+悲观锁

MVCC解决读写冲突、悲观锁解决写写冲突