网站防红怎么做的,彩票网站开发 晓风,长沙有几个区,wordpress app端MySQL进阶 —— 超详细操作演示#xff01;#xff01;#xff01;#xff08;下#xff09; 五、锁5.1 概述5.2 全局锁5.3 表级锁5.4 行级锁 六、InnoDB 引擎6.1 逻辑存储结构6.2 架构6.3 事务原理6.4 MVCC 七、MySQL 管理7.1 系统数据库7.2 常用工具 MySQL— 基础语法大… MySQL进阶 —— 超详细操作演示下 五、锁5.1 概述5.2 全局锁5.3 表级锁5.4 行级锁 六、InnoDB 引擎6.1 逻辑存储结构6.2 架构6.3 事务原理6.4 MVCC 七、MySQL 管理7.1 系统数据库7.2 常用工具 MySQL— 基础语法大全及操作演示 1、MySQL概述2、SQL3、函数4、约束5、多表查询6、事务 MySQL进阶 —— 超详细操作演示持续更新 1、存储引擎2、索引3、SQL 优化4、视图 / 存储过程 / 触发器5、锁6、InnoDB 引擎7、MySQL 管理 五、锁
5.1 概述 锁是计算机协调多个进程或线程 并发访问 某一资源的机制。 在数据库中除传统的计算资源CPU、RAM、I/O的争用以外数据 也是一种供许多用户共享的资源。 如何保证数据 并发访问 的 一致性、有效性 是所有数据库必须解决的一个问题锁冲突 也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。 MySQL中的锁按照锁的粒度分分为以下三类
全局锁锁定数据库中的 所有表。表级锁每次操作锁住 整张表。行级锁每次操作锁住对应的 行数据。
5.2 全局锁
⭐️ 1). 介绍
全局锁 就是对 整个数据库实例 加锁加锁后整个实例就处于 只读状态 后续的 DML的写语句 DDL语句以及 更新操作 的事务提交语句都将 被阻塞。
其典型的使用场景是做全库的 逻辑备份对所有的表进行锁定从而获取 一致性视图保证 数据的完整性。
为什么全库逻辑备份就需要加全局锁呢
A. 我们一起先来分析一下不加全局锁可能存在的问题。
假设在数据库中存在这样三张表: tb_stock 库存表tb_order 订单表tb_orderlog 订单日志表。 在进行数据备份时先备份了tb_stock 库存表。然后接下来在业务系统中执行了 下单操作扣减库存生成订单更新 tb_stock表插入 tb_order表。然后再执行备份 tb_order 表的逻辑。业务中执行 插入订单日志 操作。最后又备份了 tb_orderlog 表。
此时备份出来的数据是存在问题的。因为备份出来的数据tb_stock 表与 tb_order 表的数据不一致(有最新操作的订单信息,但是库存数没减)。
那如何来规避这种问题呢?
此时就可以借助于MySQL的 全局锁 来解决。
B. 再来分析一下加了全局锁后的情况 对数据库进行进行 逻辑备份 之前先对整个数据库加上 全局锁 一旦加了全局锁之后其他的 DDL、 DML 全部都处于 阻塞状态但是可以执行 DQL语句也就是处于 只读状态而数据备份 就是 查询操作。
那么数据在进行逻辑备份的过程中数据库中的数据就是不会发生变化的这样就保证了数据的 一致性 和 完整性。
⭐️ 2). 语法
a). 加全局锁
flush tables with read lock ;b). 数据备份
## 不是SQL语句要在win的命令行中执行
mysqldump -h 192.168.200.2 -uroot –p1234 rmzh D:/rmzh.sql数据备份的相关指令, 在后面MySQL管理章节, 还会详细讲解.
c). 释放锁
unlock tables ;⭐️ 3). 特点
数据库中加全局锁是一个比较重的操作存在以下问题
如果在 主库 上备份那么在备份期间都不能执行更新业务基本上就得停摆。如果在 从库 上备份那么在备份期间从库不能执行主库同步过来的二进制日志 binlog 会导致主从延迟。
在 InnoDB引擎 中我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的 一致性 数据备份。
## 不是SQL语句要在win的命令行中执行
mysqldump --single-transaction -uroot –p1234 rmzh rmzh.sql5.3 表级锁
⭐️ 1). 介绍
表级锁每次操作 锁住整张表。
锁定粒度大发生锁冲突的概率最高并发度最低。应用在 MyISAM、InnoDB、BDB 等存储引擎中。
对于表级锁主要分为以下三类
表锁元数据锁meta data lockMDL意向锁
⭐️ 2). 表锁
对于表锁分为两类
表共享读锁read lock表独占写锁write lock
语法
加锁lock tables 表名... read/write。释放锁unlock tables / (客户端断开连接) 。
特点:
A. 读锁
左侧为客户端一对指定表加了读锁不会影响右侧客户端二的读但是会 阻塞右侧客户端的写。
测试
B. 写锁
左侧为客户端一对指定表加了写锁会 阻塞右侧客户端的读和写。
测试 结论: 读锁 不会阻塞其他客户端的读但是会阻塞写。写锁 既会阻塞其他客户端的读又会阻塞其他客户端的写。 ⭐️ 3). 元数据锁
meta data lock , 元数据锁简写 MDL。
MDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据 一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免 DML与DDL冲突保证读写的正确性。
这里的 元数据大家可以简单理解为就是 一张表的表结构。 也就是说某一张表涉及到 未提交的事务 时是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加 MDL读锁(共享)当对表结构进行变更操作的时候加 MDL写锁 (排他)。
常见的SQL操作时所添加的元数据锁 演示
当执行 SELECT、INSERT、UPDATE、DELETE 等语句时添加的是元数据共享锁SHARED_READ / SHARED_WRITE之间是 兼容 的。 当执行 SELECT 语句时添加的是 元数据共享锁SHARED_READ会阻塞元数据排他锁EXCLUSIVE之间是 互斥 的。 我们可以通过下面的SQL来查看数据库中的 元数据锁 的情况
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;我们在操作过程中可以通过上述的SQL语句来查看元数据锁的加锁情况。
⭐️ 4). 意向锁
a). 介绍
为了避免DML在执行时加的 行锁 与 表锁 的冲突在InnoDB中引入了 意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查。
假如没有意向锁客户端一对表加了行锁后客户端二如何给表加表锁呢来通过示意图简单分析一下
首先客户端一开启一个事务然后执行DML操作在执行DML语句时会对涉及到的行加行锁。 当客户端二想对这张表加表锁时会检查当前表是否有对应的行锁如果没有则添加表锁此时就会从第一行数据检查到最后一行数据效率较低。
有了意向锁之后 :
客户端一在执行DML操作时会对涉及的行加行锁同时也会对该表加上意向锁。 而其他客户端在对这张表加表锁的时候会根据该表上所加的意向锁来判定是否可以成功加表锁而不用逐行判断行锁情况了。
b). 分类
意向共享锁(IS): 由语句 select ... lock in share mode 添加 。 与 表锁共享锁( read )兼容与表锁排他锁( write )互斥。意向排他锁(IX): 由 insert、update、delete、select...for update 添加 。与表锁共享锁( read )及 排他锁( write )都互斥意向锁之间不会互斥。 一旦事务提交了意向共享锁、意向排他锁都会自动释放。 5.4 行级锁
⭐️ 1). 介绍
行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在 InnoDB 存储引擎中。
InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类
行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行 update 和 delete。在 RC、RR隔离级别下都支持。 间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行 insert产生幻读。在 RR 隔离级别下都支持。 临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的 间隙 Gap。在RR隔离级别下支持。 ⭐️ 2). 行锁
a). 介绍
InnoDB实现了以下两种类型的行锁
共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。
两种行锁的兼容情况如下: 常见的SQL语句在执行时所加的行锁如下 ⭐️ 3). 间歇锁 临键锁
默认情况下InnoDB 在 REPEATABLE READ 事务隔离级别运行InnoDB 使用 next-key 锁进行搜索和索引扫描以防止幻读。
索引上的等值查询(唯一索引)给不存在的记录加锁时, 优化为间隙锁 。索引上的等值查询(非唯一普通索引)向右遍历时最后一个值不满足查询需求时next-key lock 退化为间隙锁。索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。 注意间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会 阻止另一个事务在同一间隙上采用间隙锁。 锁 快速食用----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
六、InnoDB 引擎
6.1 逻辑存储结构
6.2 架构
⭐️ 1). 概述
⭐️ 2). 内存结构 ⭐️ 3). 磁盘结构
⭐️ 4). 后台线程
6.3 事务原理
⭐️ 1). 事务基础
⭐️ 2). redo log ⭐️ 3). undo log
6.4 MVCC
⭐️ 1). 基本概念
⭐️ 2). 隐藏字段 ⭐️ 3). undolog
⭐️ 4). readview
⭐️ 5). 原理分析 InnoDB 引擎 快速食用----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
七、MySQL 管理
7.1 系统数据库
7.2 常用工具
⭐️ 1). mysql ⭐️ 2). mysqladmin ⭐️ 3). mysqlbinlog
⭐️ 4). mysqlshow ⭐️ 5). mysqldump ⭐️ 6). mysqlimport/source MySQL 管理 快速食用----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
注仅供学习参考如有不足欢迎指正
