MySQL进阶之触发器、锁、InnoDB引擎和MySQL管理

一.触发器

1.触发器介绍： 

2.触发器insert类型：

触发器的主体是当前数据库。 

 

 再次向当前数据库中插入数据后，

就 可以在日志表中查询到insert记录

2.触发器update类型：

 此时我们来更新以下数据

我们再打开日志记录，就可以看到update有触发了一次。

 因为是行级触发，所以若一个SQL语句修改了5行数据，触发器就会触发5次。相应的日志里就会有5行日志记录。

3.触发器delete类型：

 相应的执行这条SQL语句，在日志中也是可以查到记录的。

 二.锁

1.锁的概述：

 

 2.全局锁：

 故我们需要全局锁来确保数据的一致性

只能执行读取数据，不能写入数据。 

 从上到下执行语法。

当修改数据的时候，如下图可以看到报错：

 -uroot标明root用户，-p1234标明密码1234，db01是要备份的数据，D：/db01.sql标明要存放的数据文件的目录。特别注意此条语句要在window下执行，而不是mysql下执行。

 如上图，若要连接远程的数据库，还要加上-h和ip地址。

最后再unlock以下锁 

 

3.表级锁：

 

 

读锁：

 如下图所示，不能修改数据。在另一个客户端的也不能修改数据，执行修改数据的sql语句就一直阻塞。直到前一个客户端解锁

 

 写锁：

 执行写锁后，第一个客户端可以执行读写操作，但第二个客户端不能执行读写操作，直到解锁。

 

元数据锁：

在一个客户端使用alter table,,,SQL语句，会发生阻塞现象，说明产生的exclusive会与其他的锁互斥。直至另一个客户端commit提交

 此条语句无需改动，直接复制粘贴。

意向锁：

 

 这些意向锁是执行对应SQL语句时自动加上去的

 当我们用意向锁后，要注意哪些表锁与意向锁互斥，否则乱用会一直处于阻塞状态。

4.行级锁：

1.概述： 

行锁：

 也就是说一个事务获取了一行的共享锁，其他事务就不能获取排他锁。两者是冲突的。

每一条SQL语句对应的行锁：

 

 温馨提示：关于事务，一般是以begin开始，commit提交为结束。

第一个客户端：

第二个客户端：

 可以明显看到第二个客户端执行update语句时发生了阻塞，是因为第一个客户端对id=1执行了共享锁，这与update对id=1数据进行修改时会产生的排他锁互斥。

间隙锁和临界锁：

间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。

请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

事务开启后，当修改一个表中不存在的数据行时，就会产生一个间隙锁，如下图的GAP ，位于查询条件相邻的两行之间。

此时再向间隙插入数据，会发现出现阻塞现象，直到第一个客户端commit事务。

 commit提交之后，id=7的数据也就插进去表里了，但也会在3和7之间再次建立一个间隙锁。

 临建锁：

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

锁的总结：

三.InnoDB引擎（MySQL默认引擎）：

.ibd文件就是MySQL的表空间文件，存放表结构，数据以及索引。

InnoDB引擎的架构分为两部分：内存结构和磁盘结构 

（1）.内存结构：

1.Buffer Pool缓冲池

那一个一个小方格是页，不同颜色表示页的状态不同。

 2.Change Buffer更改缓冲区（缓冲区的缓存区）：

3.adaptive hash index： 

4.日志缓冲区：

 

 （2）.磁盘结构：

1.系统表空间：

 2.三种表空间： 

ts_itheima表示使用itheima的表空间。 

 3.双写缓冲区和重做日志：

 undo log撤销日志

几种后台线程： 

总结：业务首先操作缓冲区，如果没有数据，将磁盘的数据加载过来，再缓存到缓冲区中，我们的增删改查就是在操作我们的缓冲区，缓冲区的数据会以一定的速率，频率以后台线程刷新到磁盘中，在磁盘中进行永久性的保存。

三。事务的原理：

 

1.redo log撤销日志 

脏页指的是缓冲区的数据修改而磁盘里并还没有修改，过一会就会把脏页的数据刷新到磁盘中。

 这里的ib_logfile可以用于当刷新失败时进行数据修复。注意每隔一段时间就会去删掉redo log文件，所以redo log主要用来保证数据修复和使用。

2.undo log回滚日志：

 四.MVCC机制（多版本并发控制）：

1.基本概念：

 

 

ibd2sdi+后面加上表空间文件，可以查看对应表空间的文件里面相关的内容。 

2.MVCC实现原理：

 

 

 

一致性是由redo log和undo log共同维护。 

五.MySQL管理

1.系统数据库：系统自带数据库

 2，常用工具1：

并不用登录到MySQL就能查询到MySQL中某个数据库的表内容

 

 

 MySQL的二进制文件存放地址如上图。尾部都是.0001,0002

直接使用cat去查看这些二进制文件会出现很多乱码，而是要用mysqlbinlog去查看

在二进制文件名前面加上-s，呈现出来的内容会更加紧凑，会过滤掉无效的消息。

 

 

 此条SQL语句用来统计数据库中表的情况。也可以在--count前面加上指定的数据库名以及表名或以及属性名，查看当前的数据库表、属性的状态信息。

 

 -u和-p表示自动登录，把db01备份到db02.sql文件中。-t表示不包含创建表的建表语句。

使用-T备份文件，默认备份文件为，其他指定路径会提醒不安全。

会产生两个文件。

 注意要导入的是文本文件还是二进制文件，有对应的SQL语句。

总结：