1 MYSQL 事务隔离级别 四种隔离级别： 脏读，可重复读，幻读总结
2，详细画图分析 MVCC 多版本并发控制与UndoLog 底层原理；
3， 超详细总结行锁 for update ,间隙锁，悲观锁与乐观锁机制原理 总结
4， MySQL 性能调优与监控工具面试总结
MYSql 默认事务隔离级别； 可重复读 会发生幻读
事务隔离级别： 多个不同的session 直接事务如何管理：
读已提交：
读未提交： 脏读
可重复读： 避免脏读
串行化：
事务：
行锁： commit 或者rollback 释放
可重复读： 在同一个事务中，最开始与结束前查询到的数据都是一致的。
原理采用mvcc 多版本控制原理： 无锁方式+快照版本 Read view undolog 日志；
此时允许幻读，单不允许重复读和脏读；
mysql三种日志：
Binlog mysql 自带二进制文件 主从复制集群
readlog innodb 缓存页的日志
undolog 事务日志 底层 记忆集链
mvcc 多版本控制原理：
一张表中： 三个隐藏列：
Rowid： 隐含是自增ID(隐藏主键)，如果数据表没有主键，InnoDb 会自动以DB_ROW_ID 产生一个聚簇索引 6个字节
DB_TRX_ID： 当前全局事务id
DB_Roll_PTR: 回滚 id 修改/delete
MVCC多版本控制原理
简单回顾：MySQL InnoDB 引擎默认为REPEATABLE READ
-
同一个事务中，多次查询的结果还是原来数据 底层采用MVCC多版本控制机制实现，读取原来快照数据。
-
此时允许幻读，但不允许重复读与脏读。
MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存，就是同一份数据临时保留多版本的一种方式，进而实现并发控制
如果是传统锁的方式保证实现多版本控制，需要通过锁的方式实现，效率非常低，MVCC 使用了一种不同的手段， 无锁并发控制每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。
MVCC插入流程(insert)：
在MySQL中建表时，每个表都会有三列隐藏记录，其中和MVCC有关系的有两列
1.DB_TRX_ID
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
2.DB_ROLL_PTR
7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
3.DB_ROW_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
MVCC新增
在插入数据的时候，假设系统的全局事务ID从1开始，
|
set tx_isolation='repeatable-read';
begin;-- 获取全局事务id
INSERT INTO mayikt_account VALUES (null, 'xiaomin', '600');
INSERT INTO mayikt_account VALUES (null, 'xiaoan', '600');
commit;
|
底层表结构
[图片上传失败...(image-64a967-1604282132901)]
插入的过程中会把全局事务ID记录到列 DB_TRX_ID 中去
MVCC删除
表格做删除逻辑，执行以下SQL语句（假设获取到的事务逻辑ID为 3）
|
begin; -- 获得全局事务ID = 3
delete mayikt_account where id= 2;
commit;
|
MVCC修改
修改逻辑和删除逻辑有点相似，修改数据的时候会先复制一条当前记录行数据，同事标记这条数据的数据行版本号为当前是事务版本号，最后把原来的数据行的删除版本号标记为当前是事务。
|
begin;-- 获取全局系统事务ID 假设为 7
update mayikt_account set balance=700 where id=1;
commit;
|
MVCC查询
查询规则：
- 查找数据行版本号早于当前事务版本号的数据行记录
数据行的版本号要小于或等于当前是事务的系统版本号，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据
- 查找删除版本号要么为NULL，要么大于当前事务版本号的记录
这样确保查询出来的数据行记录在事务开启之前没有被删除
查询与新增原理
sessionA
|
begin;-- 获取全局事务id 1
-- select * from mayikt_account where DB_TRX_ID=1
select * from mayikt_account
commit;
|
| |
底层查询： -- select * from mayikt_account where DB_TRX_ID<=1
sessionB;
DB_TRX_ID=2；
|
begin; -- 获取全局事务id 2
INSERT INTO mayikt_account VALUES (null, 'yushengjun889', '500');
commit;
|
查询与修改原理
sessionA
|
begin;-- 获取全局事务id 1
-- select * from mayikt_account where DB_TRX_ID=1
select * from mayikt_account
commit;
|
| |
底层查询： -- select * from mayikt_account where DB_TRX_ID<=1
sessionB;
DB_TRX_ID=2；
|
begin; -- 获取全局事务id 2
update mayikt_account set balance=balance-50 where id = 1;
commit;
|
查询与delete原理
sessionA
|
begin;-- 获取全局事务id 1
-- select * from mayikt_account
select * from mayikt_account
commit;
|
sessionB
|
begin; -- 获取全局事务id 2
delete from mayikt_account where id=1;
commit;
|
底层实现原理
Redolog日志 innodb引擎数据页缓存日志
Undolog日志 事务回滚的日志 MVCC结合Undolog实现
Binlog日志 mysql 二进制文件
隐式字段、undo日志、Read View来实现；
DB_TRX_ID
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROLL_PTR
7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
DB_ROW_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
[图片上传失败...(image-19b8ad-1604282132900)]##### MVCC多版本控制原理
简单回顾：MySQL InnoDB 引擎默认为REPEATABLE READ
-
同一个事务中，多次查询的结果还是原来数据 底层采用MVCC多版本控制机制实现，读取原来快照数据。
-
此时允许幻读，但不允许重复读与脏读。
MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存，就是同一份数据临时保留多版本的一种方式，进而实现并发控制
如果是传统锁的方式保证实现多版本控制，需要通过锁的方式实现，效率非常低，MVCC 使用了一种不同的手段， 无锁并发控制每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。
MVCC插入流程(insert)：
在MySQL中建表时，每个表都会有三列隐藏记录，其中和MVCC有关系的有两列
1.DB_TRX_ID
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
2.DB_ROLL_PTR
7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
3.DB_ROW_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
MVCC新增
在插入数据的时候，假设系统的全局事务ID从1开始，
|
set tx_isolation='repeatable-read';
begin;-- 获取全局事务id
INSERT INTO mayikt_account VALUES (null, 'xiaomin', '600');
INSERT INTO mayikt_account VALUES (null, 'xiaoan', '600');
commit;
|
底层表结构
[图片上传失败...(image-762dda-1604282136437)]
插入的过程中会把全局事务ID记录到列 DB_TRX_ID 中去
MVCC删除
表格做删除逻辑，执行以下SQL语句（假设获取到的事务逻辑ID为 3）
|
begin; -- 获得全局事务ID = 3
delete mayikt_account where id= 2;
commit;
|
MVCC修改
修改逻辑和删除逻辑有点相似，修改数据的时候会先复制一条当前记录行数据，同事标记这条数据的数据行版本号为当前是事务版本号，最后把原来的数据行的删除版本号标记为当前是事务。
|
begin;-- 获取全局系统事务ID 假设为 7
update mayikt_account set balance=700 where id=1;
commit;
|
MVCC查询
查询规则：
- 查找数据行版本号早于当前事务版本号的数据行记录
数据行的版本号要小于或等于当前是事务的系统版本号，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据
- 查找删除版本号要么为NULL，要么大于当前事务版本号的记录
这样确保查询出来的数据行记录在事务开启之前没有被删除
查询与新增原理
sessionA
|
begin;-- 获取全局事务id 1
-- select * from mayikt_account where DB_TRX_ID=1
select * from mayikt_account
commit;
|
| |
底层查询： -- select * from mayikt_account where DB_TRX_ID<=1
sessionB;
DB_TRX_ID=2；
|
begin; -- 获取全局事务id 2
INSERT INTO mayikt_account VALUES (null, 'yushengjun889', '500');
commit;
|
查询与修改原理
sessionA
|
begin;-- 获取全局事务id 1
-- select * from mayikt_account where DB_TRX_ID=1
select * from mayikt_account
commit;
|
| |
底层查询： -- select * from mayikt_account where DB_TRX_ID<=1
sessionB;
DB_TRX_ID=2；
|
begin; -- 获取全局事务id 2
update mayikt_account set balance=balance-50 where id = 1;
commit;
|
查询与delete原理
sessionA
|
begin;-- 获取全局事务id 1
-- select * from mayikt_account
select * from mayikt_account
commit;
|
sessionB
|
begin; -- 获取全局事务id 2
delete from mayikt_account where id=1;
commit;
|
底层实现原理
Redolog日志 innodb引擎数据页缓存日志
Undolog日志 事务回滚的日志 MVCC结合Undolog实现
Binlog日志 mysql 二进制文件
隐式字段、undo日志、Read View来实现；
DB_TRX_ID
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROLL_PTR
7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
DB_ROW_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
[图片上传失败...(image-7cd910-1604282136435)]
undo日志
undo log主要分为两种：
1.insert undo log 代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
2.update undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
清除(purge)：
从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。
undo日志
undo log主要分为两种：
1.insert undo log 代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
2.update undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
清除(purge)：
从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。
悲观锁与乐观锁之间区别
for update定义
for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。
而select … for update 语句是我们经常使用手工加锁语句。在数据库中执行select … for update ,大家会发现会对数据库中的表或某些行数据进行锁表，在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。
sessionA
begin;
select * from mayikt_account where id=2 for update;
commit;
sessionB
修改id=2 直接卡出
悲观锁与乐观锁实现
悲观锁比较悲观，多线程同时对同一行数据修改的时候，最终只有一个线程修改成功。
Innodb引擎自带行锁机制。
乐观锁：比较乐观，多线程同时对同一行数据修改的时候，如果没有获取到行锁的线程，
会采用自旋的方式不断重试，一般采用cas实现。
image.png
mysql 间隙锁：
间隙锁基本概念：
间隙锁是一个在索引记录之间的间隙上的锁；
间隙锁的作用：
在mysql 的innodb 引擎中，如果操作的是一个区别的数据，会锁住这个区间所有的记录，即使这个记录不存在，这时候另一个会话去插入这个区间的数据，就必须等待上一个结束；
INSERT INTO `mayikt_account` VALUES (18, 'xiaoxiao18', '600');
INSERT INTO `mayikt_account` VALUES (22, 'xiaoxiao20', '600');
sessionA
begin;
update mayikt_account set name='mayikt6' where id>18 and id<22;
commit;
从id>18 and id <22 上了间隙锁，在没有释放锁的时候 其他的session无法对该段位做操作。
sessionB
INSERT INTO mayikt_account VALUES (19, 'mayikt19', '500');
我们也可以使用间隙锁解决幻读问题
SessionA 先执行：
update mayikt_account set name='mayikt6' where id>18 and id<22;
另外SessionB 无法insert id在18-22 段位。
行锁：提交或者回滚
主键索引或者唯一索引会使用间隙锁吗
1.如果where条件都命中的情况下，则不会发生间隙锁，只会增加记录锁
2.如果where条件部分命中或者全部没有命中的情况下，则使用间隙锁
如何避免行锁升级表锁
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则都会从行锁升级为表锁。
sessionA
begin;
update mayikt_account set name='mayikt6' where balance='300';
commit;
修改的时候查询的条件不是索引字段，会走全表扫描 全表扫描的时候对每行数据都加上行锁
，最终形成表锁。
sessionB
无法修改该任意一条数据，直接发生表锁。
删除表锁
unlock tables;
优化注意事项
1.尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2.尽可能减少检索条件范围，避免间隙锁
3.尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
网友评论