实验室网站建设重要性,福安 网站设计,网站功能结构图 怎么做,车子网站锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中#xff0c;除传统的计算机资源#xff08;CPU、RAM、I/O#xff09;的争用之外#xff0c;数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题除传统的计算机资源CPU、RAM、I/O的争用之外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤为重要也更加复杂
MySQL中的锁按照锁的粒度分为以下三类
全部锁锁定数据库中的所有表表级锁每次操作锁住整张表行级锁每次操作锁住对应的行数据
全局锁
介绍
全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞
其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性
为什么全局逻辑备份就需要加全局锁呢
1、如果不加全局锁可能存在的问题
假设数据库中存在这样的三张表tb_stock库存表tb_order订单表tb_orderlog订单日志表 在进行数据备份时先备份了tb_stock库存表然后接下来在业务系统中执行了下单操作扣减库存生成订单更新tb_stock表插入tb_order表然后再执行备份tb_order表的逻辑业务中执行插入订单日志操作最后有备份了tb_orderlog表
此时备份出来的数据是存在问题的。因为备份出来的数据tb_stock表与tb_order表的数据不一致有最新操作的订单信息但是库存数没减
此时就可以借助于MySQL的全局锁来解决
2、再来分析一下加了全局锁后的情况 对数据库进行逻辑备份之前先对整个数据库加上全局锁一旦加了全局锁之后其他的ddl、dml全部都处于阻塞状态但是可以执行DQL语句也就是只读状态而数据备份就是查询操作。那么数据在进行逻辑备份的过程中数据库中的数据就是不会发生变化的这样就保证了数据的一致性和完整性。
语法
1、加全局锁
flush tables with read lock;2、数据备份
mysqldump -u用户名 -p密码 demo demo.sql3、释放锁
unlock tables;特点
数据库中加全局锁是一个比较重的操作存在以下问题
如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆如果在从库上备份那么在备份期间从库不能执行主库同步过来得二进制日志binlog会导致主从延迟
在innoDB引擎中可以在备份时加上参数–single-transaction 参数来完成不加锁得一致性数据备份
mysqldump --single-transaction -u用户名 -p密码 demo demo.sql表级锁
介绍
表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中
对于表级锁主要分为以下三类
表锁元数据锁meta data lock, MDL)意向锁
表锁
对于表锁分为两类
表共享读锁(read lock)表独占写锁(write lock)
语法
加锁lock tables 表名… read/write.释放锁unlock tables / 客户端断开连接
特点
1、读锁 左侧为客户端一对指定表加了读锁不会影响右侧客户端二的读但是会阻塞右侧客户端的写
2、写锁 左侧为客户端一对指定表加了写锁会阻塞右侧客户端的读和写 注意读锁不会阻塞其他客户端的读但是会阻塞写。写锁既会阻塞其他客户的读又会阻塞其他客户端的写 元数据锁
元数据锁meta data lock简写MDL
MDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性
也就是说某一张表涉及到未提交的事务时是不能够修改这张表的结构的。
常见的SQL操作时所添加的元数据锁
对应SQL锁类型说明lock tables xxx read/writeshared_read_only / shared_no_read_writeselect、select… lock in share modeshared_read与shared_read、shared_write兼容与exclusive互斥insert、update、delete、select … for updateshared_write与shared_read、shared_write兼容与exclusive互斥alter table …exclusive与其他的mdl都互斥
意向锁
介绍
为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查
加入没有意向锁客户端一对表加了行锁后客户端二如何给表加表锁呢
首先客户端一开启了一个事务然后执行DML操作在执行DML语句时会对涉及到的行加行锁 当客户端二想对这张表加表锁时会检查当前表是否有对应的行锁如果没有则添加表锁此时就会从第一行数据检查到最后一行数据效率较低 有了意向锁之后
客户端一在执行DML操作时会对涉及的行加行锁同时也会对该表加上意向锁 而其他客户端在对这张表加表锁的时候会根据该表上所加的意向锁来判定是否可以成功加表锁而不用逐行判断行锁情况 分类
意向共享锁IS有语句select … lock in share mode添加。与表锁共享锁read兼容与表锁排他锁write互斥意向排他锁IX由insert、update、delete、select…for update 添加。与表锁共享锁read及排他锁write都互斥意向锁之间不会互斥 一旦事务提交了意向共享锁、意向排他锁都会自动释放 可以通过以下SQL查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;演示
1、意向共享锁与表读锁是兼容的
2、意向排他锁与表读、写锁都是互斥的
行级锁
介绍
行级锁每次操作锁对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中
InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类
行锁Record Lock锁定单个记录的锁防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持 间隙锁Gap Lock)锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持 临键锁Next-Key Lock)行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持 行锁
介绍
InnoDB实现了以下两种类型的行锁
共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排他锁排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁
两种锁的兼容情况如下 常见的SQL语句在执行时所加的行锁如下
SQL行锁类型说明insert …排他锁自动加锁update …排他锁自动加锁delete …排他锁自动加锁select(正常)不加任何锁select … lock in share mode共享锁需要手动在select之后加lock in share modeselect … for update排他锁需要手动在select之后加for update
演示
默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key锁进行搜索和索引扫描以防止幻读
针对唯一索引继续检索时对已存在的记录进行等值匹配时将会自动优化为行锁InnoDB的行锁是针对于索引加的锁不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时就会升级为表锁
可以通过以下sql查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;示例
CREATE TABLE stu (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
age int NOT NULL
) ENGINE InnoDB CHARACTER SET utf8mb4;
INSERT INTO stu VALUES (1, tom, 1);
INSERT INTO stu VALUES (3, cat, 3);
INSERT INTO stu VALUES (8, rose, 8);
INSERT INTO stu VALUES (11, jetty, 11);
INSERT INTO stu VALUES (19, lily, 19);
INSERT INTO stu VALUES (25, luci, 25);1、普通的select语句执行时不会加锁
select * from stu where id 1;
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;2、select… lock in share mode加共享锁共享锁与共享锁之间兼容 客户端一获取的是id为1这行的共享锁客户端二是可以获取id为3这行的排他锁的因为不是同一行数据而如果客户端二想要获取id为1这行的排他锁会处于阻塞状态因为共享锁和排他锁之间互斥
3、排他锁与排他锁之间互斥
begin;
update stu set name lei where name lily;
commit;
begin;
update stu set name php where id 19;
commit;当客户端一执行update语句会为id为1的记录加排他锁客户端二如果也执行update语句更新id为1的数据也要为id为1的数据加排他锁但是客户端二会处于阻塞状态因为排他锁之间是互斥的。直到客户端一把事务提交了才会把这一行的行锁释放此时客户端二解除阻塞。
间隙锁临键锁
默认情况下InnoDB在REPETABLE READ事务隔离级别运行InnoDB使用next-key锁进行搜索和索引扫描以防止幻读
索引上的等值查询唯一索引给不存在的记录加锁时优化为间隙锁索引上的等值查询非唯一普通索引向右遍历时最后一个值不满足查询需求时next-key lock退化为间隙锁索引上的范围查询唯一索引–会访问到不满足条件的第一个值为止 注意间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁 InnoDB引擎
逻辑存储结构
innoDB的逻辑存储结构如下图所示
1、表空间
表空间是InnoDB存储引擎逻辑结构的最高层如果用户启用了参数innoDB_file_per_table则每张表都会有一个表空间(xxx.ibd)一个mysql实例可以对应多个表空间用于存储记录索引等数据
2、段
段分为数据段Leaf node segment、索引段Non-leaf node segment、回滚段Rollback segmentInnoDB是索引组织表数据段就是B数的叶子节点索引段即为B树的非叶子节点。段用来管理多个Extent区
3、区
区、表空间的单元结构每个区的大小为1M。默认情况下InnoDB存储引擎页大小为16k即一个区中一共有64个连续的页
4、页
页、是InnoDB存储引擎磁盘管理的最小单元每个页的大小默认为16kb为了保证页的连续性InnoDB存储引擎每次从磁盘申请4-5个区
5、行
行InnoDB存储引擎数据是按行进行存放的
在行中默认有两个隐藏字段
Trx_id每次对某条记录进行改动时都会把对应的事务id赋值给trx_id隐藏列Roll_pointer每次对某条记录进行改动时都会把旧的版本写入到undo日志中然后这个隐藏列就相当于一个指针可以通过它来找到该记录修改前的信息
架构
概述
MySQL5.5版本开始默认使用InnoDB存储引擎它擅长事务处理具有崩溃恢复特性在日常开发中使用非常广泛。
下面是InnoDB架构图左侧为内存结构右侧为磁盘结构 内存结构 在左侧的内存结构中主要分为四大块Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。接下来介绍一下这四个部分
Buffer Pool
InnoDB存储引擎基于磁盘文件存储访问物理硬盘和在内存中进行访问速度相差很大为了尽可能弥补这两者之间的I/O效率的差值就需要把经常使用的数据加载到缓冲池中避免每次访问都进行磁盘I/O
在InnoDB的缓冲池中不仅缓存了索引页和数据页还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等
缓冲池Buffer Pool是主内存中的一个区域里面可以缓存磁盘上经常操作的真是数据在执行增删改查操作时先操作缓冲池中的数据若缓冲池没有数据则从磁盘加载并缓存然后再以一定频率刷新到磁盘从而减少磁盘IO加快处理速度
缓冲池以page页为单位底层采用链表数据结构管理page。根据状态将page分为三种类型
free page空闲page未被使用clean page被使用page数据没有被修改过dirty page脏页被使用page数据被修改过页中数据与磁盘的数据产生了不一致
在专用服务器上通常将多大80%的物理内存分配给缓冲池。参数设置为show variables like ‘innodb_buffer_pool_size’;
Change Buffer
Change Buffer更改缓冲区针对于非唯一二级索引页在执行DML语句时如果这些数据page没有在Buffer Pool中不会直接操作磁盘而会将数据变更存在更改缓冲区Change Buffer中在未来数据被读取时再将数据合并恢复到Buffer Pool中在将合并后的数据刷新到磁盘中Change Buffer的意义是什么呢 与聚集索引不同二级索引通常是非唯一的并且以相对随机的顺序插入二级索引。同样删除和更新可能会影响索引树中不相邻的二级索引页如果每一次都操作磁盘会造成大量的磁盘IO。有了ChangeBuffer之后可以在缓冲池中进行合并处理减少磁盘IO
Adaptive Hash Index
自适应hash索引用于优化对Buffer Pool数据的查询。mysql的InnoDB引擎中虽然没有直接支持hash索引但是提供了一个功能就是这个自适应hash索引。hash索引在进行等值匹配时一般性能要高于B树的因为hash索引一般只需要一次IO即可而B树可能需要几次匹配所以hash索引的效率要高但是hash索引又不适合做范围查询、模糊匹配等
InnoDB存储引擎会监控对表上个索引页的查询如果观察到在特定的条件下hash索引可以提升速度则建立hash索引称之为自适应hash索引
自适应索引无需人工干预是系统根据情况自动完成
参数adaptive_hash_index
Log Buffer
Log Buffer日志缓冲区用来保存要写入到磁盘中的log日志数据redo log、undo log默认大小为16MB日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务增加日志缓冲区的大小可以节省磁盘I/O
参数
innodb_log_buffer_size缓冲区大小
innodb_flush_log_at_trx_commit日志刷新到磁盘时机取值主要包含以下三个
1日志在每次事务提交时写入并刷新到磁盘默认值
0每秒将日志写入并刷新到磁盘一次
2日志在每次事务提交后写入并每秒刷新到磁盘一次
磁盘结构 System Tablespace
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的它也可能包含表和索引数据在MySQL5.x版本中还包含InnoDB数据字典、undolog等
参数innodb_date_file_path
系统表空间默认的文件名叫ibdatal
File-Per-Tablespaces
如果开启了innodb_file_per_table开关则每个表的文件表空间包含单个InnoDB表的数据和索引并存储在文件系统上的单个数据文件中
开关参数innodb_file_per_table该参数默认开启
也就是说每创建一个表都会产生一个表空间文件。
General Tablespaces
通用表空间需要通过CREATE TABLESPACE语法创建通用表空间在创建表时可以指定该表空间
1、创建表空间
create tablespace ts_name add datafile file_name engine engine_name;2、创建表时指定表空间
create table xxx ... tablespace ts_name;Undo Tablespaces
撤销表空间MySQL实例在初始化时会自动创建两个默认的undo表空间初始化为16M用于存储undo log日志
Temporary Tablespaces
InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据
Doublewrite Buffer Files
双写缓冲区innodb引擎将数据页从buffer pool刷新到磁盘前先将数据页写入双写缓冲区文件中便于系统异常时恢复数据
Redo Log
重做日志用来实现事务的持久性。该日志文件由两部分组成重做日志缓冲redo log buffer以及重做日志文件redo log前者是在内存中后者是在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中用于刷新脏页到磁盘时发生错误时进行数据恢复使用
后台线程 在innodb的后台线程中分为4类分别为Master Thread、IO Thread、Purge Thread、Page Cleaner Thread
Master Thread
核心后台线程负责调度其他线程还负责将缓冲池中的数据异步刷新到磁盘中保持数据的一致性还包括脏页的刷新、合并插入缓存、undo页的回收
IO Thread
在Innodb存储引擎中大量使用了AIO来处理IO请求这样可以极大的提高数据库的性能而IO Thread主要负责这些IO请求得回调
线程类型默认个数职责Read thread4负责读操作Write thread4负责写操作Log thread1负责将日志缓冲区刷新到磁盘Insert buffer thread1负责将写缓冲区内容刷新到磁盘
可以通过以下的这条指令查看到innodb的状态信息其中就包含IO Thread信息
show engine innodb status \G;Purge Thread
主要用于回收事务已经提交了的undo log在事务提交之后undo log可能不用了就用它来回收
Page Cleaner Thread
协助Master Thread刷新脏页到磁盘的线程它可以减轻Master Thread的工作压力减少阻塞
事务原理
事务基础
事务
事务是一组操作的集合他是不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败
特性
原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败一致性Consistency事务完成时必须使所有的数据都保持一致状态隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行持久性Durability事务一旦提交或回滚他对数据库中的数据的改变就是永久的
对于这四大特性实际上分为两个部分。其中原子性、一致性、持久化实际上是由InnoDB中的两份日志来保证的一份是redo log日志、一份是undo log日志而隔离性是通过数据库的锁加上mvcc来保证的 redo log
重做日志记录的是事务提交时数据页的物理修改用来是实现事务的持久性
该日志文件由两部分组成重做日志缓冲redo log buffer以及重做日志文件redo log file前者是内存中后者是磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中用于刷新脏页到磁盘发生错误时进行数据恢复使用
如果没有redo log可能会出现哪些问题 在InnoDB引擎中的内存结构中主要的内存区域就是缓冲池在缓冲池中缓存了很多的数据页。当在一个事务中执行多个增删改的操作时InnoDB引擎会先操作缓冲池中的数据如果缓冲区没有对应的数据会通过后台线程将磁盘中的数据加载出来存放在缓冲区中然后将缓冲池中的数据修改修改后的数据页为脏页。而脏页则会在一定的时机通过后台线程刷新到磁盘中从而保证缓冲区与磁盘的数据一致。而缓冲区的脏页数据并不是实时刷新的而是一段时间之后将缓冲区的数据刷新到磁盘中假如刷新到磁盘的过程出错了而提示给用户事务提交成功而数据却没有持久化下来这就出现问题了没有保证事务的持久性。 那么redo log是如何解决这个问题的 有了redo log之后当对缓冲区的数据进行增删改之后会首先将操作的数据页的变化记录在redo log buffer中。在事务提交时会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后如果刷新缓冲区的脏页到磁盘时发生错误此时就可以借助于redo log进行数据恢复这样就保证了事务的持久性。而如果脏页成功刷新到磁盘或者涉及到的数据已经落盘此时redo log就没有作用了就可以删除了。所以存在的两个redo log文件是循环写的 那为什么每一次提交事务要刷新redo log到磁盘中呢而不是直接将buffer pool中的脏页刷新到磁盘中呢 因为在业务操作中操作数据一般都是随机读写磁盘的而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据由于是日志文件所以都是顺序写的。顺序写的效率要远大于随机写。这种先写日志的方式称之为WALWrite-Ahead Logging undo log
回滚日志用于记录数据被修改前的信息作用包含两个提供回滚保证事务的原子性和MVCC多版本并发控制
undo log和redo log记录物理日志不一样前者是逻辑日志。可以认为当delete一条记录时undo log会记录一条对应的insert记录反之亦然当update一条记录时它会记录一条对应相反的update记录当执行rollback时就可以从undo log中的逻辑记录读取到相应的内容并进行回滚
undo log销毁undo log在事务执行时产生事务提交时并不会立即删除undo log因为这些日志可能还用于MVCC
undo log存储undo log采用段的方式进行管理和记录存放在前面介绍的rollback segment回滚段中内部包含1024个undo log segment
MVCC
基本概念
当前读
读取的是记录的最新版本读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁。对于日常的操作如select … lock in share mode(共享锁)select … for update、update、insert、delete排他锁都是一种当前读
快照读
简单的select不加锁就是快照读读取的是记录数据的可见版本有可能是历史数据不加锁是非阻塞读
Read Committed每次select都生成一个快照读Repeatable Read开启事务后第一个select语句才是快照读的地方Serializable快照读会退化为当前读
MVCC
全称Multi-Version Concurrency Control多版本并发控制。指维护一个数据的多个版本使得读写操作没有冲突快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView
隐藏字段
介绍 当创建了上面这张表之后在查看表结构的时候就可以显式的看到这三个字段。实际上除了这三个字段以外InnoDB还会自动的添加三个隐藏字段及其含义分别是
隐藏字段含义DB_TRX_ID最近修改事务id记录插入这条记录或最后一次修改该记录的事务idDB_ROLL_PTR回滚指针指向这条记录上一版本用于配合undo log指向上一个版本DB_ROW_ID隐藏主键如果表结构没有指定主键将会生成该隐藏字段
而上述的前两个字段是肯定会添加是否添加最后一个字段DB_ROW_ID要看当前表有没有主键如果有主键则不会添加隐藏字段
undo log
介绍
回滚日志在insert、update、delete的时候产生的便于数据回滚的日志
当insert的时候产生的undo log日志只在回滚时需要在事务提交后可被立即删除
而update、delete的时候产生的undo log日志不仅在回滚的时候需要在快照读的时候也需要不会立即删除
版本链
有一张表原始数据为 DB_TRX_ID代表最近下修改事务ID记录插入这条记录或最后一次修改该记录的事务ID是自增的 DB_ROLL_PTR由于这条数据是才插入的没有被更新过所以该字段值为null 然后有四个并发事务同时在访问这张表
1、第一步 当事务2执行第一条修改语句时会记录undo log日志记录数据变更之前得样子然后更新记录并且记录本次操作得事务ID回滚指针回滚指针用来指定如果发生回滚回滚到哪一个版本 2、第二步 当事务3执行第一条修改语句时也会记录undo log日志记录数据变更之前得样子然后更新记录并且记录本次操作得事务id回滚指针回滚指针用来指定如果发生回滚回滚到哪一个版本 3、第三步 当事务4执行第一条修改语句时也会记录undo log日志记录数据变更之前的样子然后更新记录并且记录本次操作的事务ID回滚指针回滚指针用来指定如果发生回滚回滚到哪一个版本 最终发现不同事务或相同事务对同一条记录进行修改会导致该记录的undo log生成一条记录版本链表链表的头部是最近的旧纪录链表尾部是最早的旧纪录 readview
Readview读视图是快照读SQL执行时MVCC提取数据的依据记录并维护系统当前活跃的事务为提交的id
ReadView中包含了四个核心字段
字段含义m_ids当前活跃的事务id集合min_trx_id最小活跃事务IDmax_trx_id预分配事务ID当前最大事务ID1因为事务ID是自增的creator_trx_idReadView创建者的事务ID
而在ReadView中就规定了版本链数据的访问规则
tx_id代表当前undo log版本链对应事务ID
条件是否可以访问说明trx_id creator_trx_id可以访问该版本成立说明数据是当前这个事务更改的trx_id min_trx_id可以访问该版本成立说明数据已经提交了trx_id max_trx_id不可以访问该版本成立说明该事务是在ReadVIew生成后才开启的min_trx_id trx_id max_trx_id如果trx_ix不在m_ids中是可以访问该版本的成立说明数据已经提交了
不同隔离级别生成ReadView的时机不同
READ COMMITTED在事务中每一次执行快照读时生成ReadViewREPEATABLE READ仅在事务中第一次执行快照读时生成ReadView后续复用该ReadView
原理分析
RC隔离级别
RC隔离级别下在事务中每一次执行快照读时生成ReadView
两次快照读读取数据是如何获取数据的
在事务5中查询了两次id为30的记录由于隔离级别为Read Committed所以每一次进行快照读都会生成一个ReadView那么两次生成的ReadView如下 那么这两次快照读在获取数据时就需要根据生成的ReadView以及ReadView的版本链访问规则到undo log版本链中匹配数据最终决定此次快照读返回的数据
1、第一次快照读具体的读取过程 在进行匹配时会从undo log的版本链从上到下进行挨个匹配 先匹配这条记录这条记录对应的trx_id为4也就是将4带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也是不满足都不满足则继续匹配undo log版本链的下一条 再匹配第二条这条记录对应的trx_id为3也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也是不满足都不满足则继续匹配undo log版本链的下一条 再匹配第三条这条记录对应的trx_id为2也就是将2带入右侧的匹配规则中。①不满足 ②满足 终止匹配此次快照读返回的数据就是版本链中记录的这条数据
2、第二次快照读具体二点读取过程 再进行匹配时会从undo log的版本链从上到下进行挨个匹配 先匹配这条记录这条记录对应的trx_id为4也就是将4带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也是不满足都不满足则继续匹配undo log版本链的下一条 再匹配第二条这条记录对应的trx_id为3也就是将3带入右侧的匹配规则中。①不满足 ②满足 终止匹配此次快照读返回的数据就是版本链中记录的这条数据
RR隔离级别
RR隔离级别下仅在事务中第一次执行快照读时生成ReadView后续复用该ReadView。而RR是可重复读在下一个事务中执行两次相同的select语句查询到的结果是一样的
MySQL是如何做到可重复读的呢 在RR隔离级别下只是在事务中第一次快照读时生成ReadView后续都是复用该ReadView那么既然ReadView都一样ReadView的版本链匹配规则也一样那么最终快照读返回的结果也是一样的
所以MVCC的实现原理就是通过InnoDB表的隐藏字段、undo log版本链、ReadView来实现的。而MVCC锁则实现了事务的隔离性。而一致性则是由redolog和undolog保证 MySQL管理
系统数据库
MySQL数据库安装完成后自带了以下四个数据库具体作用如下
数据库含义mysql存储在MySQL服务器正常运行所需要的各种信息时区、主从、用户、权限等information_schema提供了访问数据库元数据的各种表和视图包含数据库、表、字段类型及访问权限performance_schema为MySQL服务器运行时状态提供了一个底层监控功能主要用于收集数据库服务器性能参数sys包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图
常用工具
mysql
该mysql不是指mysql服务而是指mysql的客户端工具
语法 mysql [options] [database]
选项 -u, --username #指定用户名-p, --password[name] #指定密码-h, --hostname #指定服务器IP或域名-P, --portport #指定连接端口-e, --executename #执行SQL语句并退出-e选项可以在MySQL客户端执行SQL语句而不用连接到MySQL数据库再执行对于一些批处理脚本这种方式尤其方便
示例
mysql -uroot –p123456 db01 -e select * from stu;mysqladmin
mysqladmin是一个执行管理操作的客户端程序可以用它来检查服务器的配置和当前状态、创建并删除数据库等
通过帮助文档查看选项mysqladmin --help语法mysqladmin [options] command ...
选项:-u, --username #指定用户名-p, --password[name] #指定密码-h, --hostname #指定服务器IP或域名-P, --portport #指定连接端口示例
mysqladmin -uroot –p1234 drop test01;
mysqladmin -uroot –p1234 version;mysqlbinlog
由于服务器生成的二进制日志文件以二进制格式保存所以如果要检查这些文本的文本格式就会使用到mysqlbinlog日志管理工具
语法 mysqlbinlog [options] log-files1 log-files2 ...
选项 -d, --databasename 指定数据库名称只列出指定的数据库相关操作。-o, --offset# 忽略掉日志中的前n行命令。-r,--result-filename 将输出的文本格式日志输出到指定文件。-s, --short-form 显示简单格式 省略掉一些信息。--start-datatimedate1 --stop-datetimedate2 指定日期间隔内的所有日志。--start-positionpos1 --stop-positionpos2 指定位置间隔内的所有日志。A. 查看 binlog.000008这个二进制文件中的数据信息
mysqlbinlog binlog.000008简单格式为
mysqlbinlog -s binlog.000008mysqlshow
mysqlshow客户端对象查找工具用来很快查找存在哪些数据库、数据库中的表、表中的列或索引
语法 mysqlshow [options] [db_name [table_name [col_name]]]
选项 --count 显示数据库及表的统计信息数据库表 均可以不指定-i 显示指定数据库或者指定表的状态信息
示例#查询test库中每个表中的字段书及行数mysqlshow -uroot -p2143 test --count#查询test库中book表的详细情况mysqlshow -uroot -p2143 test book --count示例
1、查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p密码 --count2、查看数据库db01的统计信息
mysqlshow -uroot -p1234 db01 --count3、查看数据库db01中的course
mysqlshow -uroot -p1234 db01 course --count4、查看数据库db01中的course表的id字段的信息
mysqlshow -uroot -p1234 db01 course id --countmysqldump
mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。本分内容包括创建表及插入表的sql语句
语法 mysqldump [options] db_name [tables]mysqldump [options] --database/-B db1 [db2 db3...]mysqldump [options] --all-databases/-A
连接选项 -u, --username 指定用户名-p, --password[name] 指定密码-h, --hostname 指定服务器ip或域名-P, --port# 指定连接端口
输出选项--add-drop-database 在每个数据库创建语句前加上 drop database 语句--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)-n, --no-create-db 不包含数据库的创建语句-t, --no-create-info 不包含数据表的创建语句-d --no-data 不包含数据-T, --tabname 自动生成两个文件一个.sql文件创建表结构的语句一个.txt文件数据文件示例
1、备份db01数据库
mysqldump -uroot -p1234 db01 db01.sql备份出来的数据包括
删除表的语句创建表的语句数据插入语句
2、备份db01数据库中的表的数据不备份表结构
mysqldump -uroot -p1234 -t db01 db01.sql3、将db01数据库的表结构与数据分开备份
mysqldump -uroot -p1234 -T /root db01 scoremysqlimport/source
1、mysqlimport
mysqlimport是客户端数据导入工具用来导入mysqldump加-T参数后导出来的文本文件
语法 mysqlimport [options] db_name textfile1 [textfile2...]
示例 mysqlimport -uroot -p2143 test /tmp/city.txt2、source
如果需要导入sql文件可以使用mysql中的source指令
语法
source /root/xxxxx.sql
