MySQL数据库锁及InnoDB引擎底层逻辑/以及系统的常用命令

目录

1.锁

         1.1 全局锁

         1.2 表级锁

                 1.2.1表锁

                 1.2.2 元数据锁

                1.2.3 意向锁

        1.3 行锁

 2.InnoDB存储引擎

        2.1 事务原理

        2.2 MVCC

 3.系统数据库

        3.1 mysql数据库

        3.2 information_schema数据库

         3.3 performance_schema数据库

        3.4 sys数据库

4. 常用工具

        4.1 常用命令

---

1.锁

        概述：是计算机协调多个进程或线程并发访问某一资源的机制。

•    锁的分类

        1）全局锁：锁住数据库中的所有表

        2）表级锁：每次操作锁住整张表

        3）行级锁：每次操作锁住对应行的数据

         1.1 全局锁

        定义：全局锁就是对整个数据库实例加锁，加锁之后整个数据库实例只处于只读状态，DML，DDL语句，及更新操作提交的事务，都会被阻塞。

                设置全局锁的语法

FLUSH TABLES WITH READ LOCK

                解锁

UNLOCK TABLES

                 备份数据的指令

mysqldump  -hIP地址  -uroot -p密码  数据库名称  >  存储位置

mysqldump  --single-transaction  -hIP地址  -uroot -p密码  数据库名称  >  存储位置

        具体语法

                远程登录其它数据库

                

                上锁

                

                解锁                

                 

                备份数据

                 注意：mysqldump不是数据库中的命令，需要在服务器上执行该条指令

         1.2 表级锁

                定义：每次操作锁住整张表，锁定粒度大，发生冲突的概率最高，并发度最低

                分类：

                        1）表锁

                        2）元数据锁

                        3）意向锁

                 1.2.1表锁

                加锁语法

LOCK  TABLES  表名  READ/WRITE

                解锁

UNLOCK TABLES

         具体语法

                

                 

                 注意：读锁不会阻塞其它客户端的读，但会阻塞其它客户端的写，写锁即会阻塞读，也会阻塞写。

                 1.2.2 元数据锁

                定义：MDL加锁过程是系统控制的，无需显示使用，在访问一张表的时候会自动加上，主要作用是维护表元数据的数据一致性。

                当对一张表进行增、删、改、查的时候，加MDL读锁(共享锁)，对表结构进行变更操作的时候，加MDL写锁(排他)

        查看元数据锁的语句

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

                1.2.3 意向锁

                定义：为了避免在DML执行中，加的行锁与表锁冲突，引入了意向锁，就不需要检查表中每一行数据是否加锁，减少表锁的检查。

                类型：

                        1）意向共享锁(IX)：由语句select...lock in share mode 添加，与表锁共享锁(read)兼容，与表锁排他锁(write)互斥

                        2）意向排他锁(IS)：由insert、update、delete、select.... for update 添加，与表锁共享锁及排他锁都互斥。

        1.3 行锁

                定义：每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高

                分类

                行锁类型：

                   1）共享锁(S)：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁

                   2）排他锁(X)：允许获取排他锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排他锁

                兼容性：

                 间隙锁

         注意：间隙锁的唯一目的防止其它事务插入间隙，间隙锁可以共存。

                查看行锁的语句

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

         当开启事务，执行更改字段在1-7之间的数据时，而表中没有该字段，就会自动增加间隙锁在1-7之间

        

         开启事务

        

         在另一个会话

        

         该语句不会被执行，处于阻塞状态，直到第一个事务被提交

 2.InnoDB存储引擎

        2.1 事务原理

• redo log：重做日志，记录的是数据提交时数据页的物理修改，是用来实现事务的持久性

• undo log：回滚日志，用于记录数据被修改前的信息，作用：提供回滚和MVCC(多版本并发控制)

        2.2 MVCC

                基本概念：

        当前读：读取的是记录的最新版本，读取时还要保证其它并发事务不能修改当前记录，会对读取的记录进行加锁

        快照读：

        隐藏字段：

 

         readview(读视图)：是快照读执行sql时MVCC提取数据的依据，记录并维护系统未提交的事务

                包含的四个核心字段：

 3.系统数据库

        3.1 mysql数据库

存储mysql服务器正常运行所需要的各种信息(时区、主从、用户、权限等)

        3.2 information_schema数据库

提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型、及访问权限等

         3.3 performance_schema数据库

为mysql数据库运行时提供了一个底层监控功能，主要用于收集数据库服务器性能参数

        3.4 sys数据库

包含了一系列DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

4. 常用工具

        4.1 常用命令

        语法：

         基本语法：

        命令：mysqladmin ：检查数据库的当前状态，并且可以创建并删除数据库

        用法：

        命令：mysqlbinlog：查找以二进制文本保存的文件

        选项：

        用法：

         命令：mysqlshow：查找数据库、数据库中的表、表中的列或者索引

         用法：

         命令：mysqldump：用来备份数据库或在不同数据库之间进行迁移

        语法 ：mysqldump  -uroot -p密码  数据库名称  > 导出的位置。

         选项：

         用法：

                 生成两个文件

        命令：mysqlimport/sourse：是客户端数据导入工具，用来导入由mysqldump加-T导出的文本文件

        选项：

         用法：这里导入的是上面生成的.txt文件

source + 文件路径   可以导入由mysqldump导出的数据库文件，

导入需要创建一个数据库(可与导入的数据库同名)。