MySQL(进阶篇3.0)

最新推荐文章于 2024-07-22 17:39:53 发布
最新推荐文章于 2024-07-22 17:39:53 发布
阅读量969 收藏 2
点赞数
文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_47070121/article/details/131087811
版权

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算机资源（CPU、RAM、I/O）的争用之外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤为重要，也更加复杂

MySQL中的锁，按照锁的粒度分为以下三类：

  • 全部锁：锁定数据库中的所有表
  • 表级锁：每次操作锁住整张表
  • 行级锁：每次操作锁住对应的行数据

全局锁

介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

为什么全局逻辑备份，就需要加全局锁呢？

1、如果不加全局锁，可能存在的问题

假设数据库中存在这样的三张表：tb_stock库存表，tb_order订单表，tb_orderlog订单日志表

img

  • 在进行数据备份时，先备份了tb_stock库存表
  • 然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入tb_order表）
  • 然后再执行备份tb_order表的逻辑
  • 业务中执行插入订单日志操作
  • 最后，有备份了tb_orderlog表

此时备份出来的数据是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一致（有最新操作的订单信息，但是库存数没减）

此时就可以借助于MySQL的全局锁来解决

2、再来分析一下加了全局锁后的情况

img

对数据库进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的ddl、dml全部都处于阻塞状态，但是可以执行DQL语句，也就是只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。

语法

1、加全局锁

flush tables with read lock;

2、数据备份

mysqldump -u用户名 -p密码 demo > demo.sql

3、释放锁

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、读锁

img

左侧为客户端一，对指定表加了读锁，不会影响右侧客户端二的读，但是会阻塞右侧客户端的写

2、写锁

img

左侧为客户端一，对指定表加了写锁，会阻塞右侧客户端的读和写

注意：读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户的读，又会阻塞其他客户端的写

元数据锁

元数据锁（meta data lock），简写MDL

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性

也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的结构的。

常见的SQL操作时，所添加的元数据锁：

对应SQL锁类型说明
lock tables xxx read/writeshared_read_only / shared_no_read_write
select、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语句时，会对涉及到的行加行锁

img

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低

img

有了意向锁之后：

客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁

img

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况

img

分类
  • 意向共享锁（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隔离级别下都支持

img

  • 间隙锁（Gap Lock)：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持

img

  • 临键锁（Next-Key Lock)：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

img

行锁

介绍

InnoDB实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
  • 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

两种锁的兼容情况如下：

img

常见的SQL语句，在执行时，所加的行锁如下：

SQL行锁类型说明
insert …排他锁自动加锁
update …排他锁自动加锁
delete …排他锁自动加锁
select(正常)不加任何锁
select … lock in share mode共享锁需要手动在select之后加lock in share mode
select … 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;

img

img

2、select… lock in share mode，加共享锁，共享锁与共享锁之间兼容

imgimg

客户端一获取的是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;

img

当客户端一执行update语句，会为id为1的记录加排他锁；客户端二如果也执行update语句更新id为1的数据，也要为id为1的数据加排他锁，但是客户端二会处于阻塞状态，因为排他锁之间是互斥的。直到客户端一，把事务提交了，才会把这一行的行锁释放，此时客户端二，解除阻塞。

间隙锁&临键锁

默认情况下，InnoDB在REPETABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读

  • 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
  • 索引上的等值查询（非唯一普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
  • 索引上的范围查询（唯一索引）–会访问到不满足条件的第一个值为止

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁

InnoDB引擎

逻辑存储结构

innoDB的逻辑存储结构如下图所示：
img

1、表空间

表空间是InnoDB存储引擎逻辑结构的最高层，如果用户启用了参数innoDB_file_per_table，则每张表都会有一个表空间(xxx.ibd)，一个mysql实例可以对应多个表空间，用于存储记录，索引等数据

2、段

段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是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架构图，左侧为内存结构，右侧为磁盘结构

img

内存结构

img

在左侧的内存结构中，主要分为四大块：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的意义是什么呢？

img

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘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：日志在每次事务提交后写入，并每秒刷新到磁盘一次

磁盘结构

img

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），前者是在内存中，后者是在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中，用于刷新脏页到磁盘时，发生错误时，进行数据恢复使用

后台线程

img

在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;

img

Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收

Page Cleaner Thread

协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞

事务原理

事务基础

事务

事务是一组操作的集合，他是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

特性
  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性（Durability）：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的

对于这四大特性，实际上分为两个部分。其中原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志、一份是undo log日志；而隔离性是通过数据库的锁，加上mvcc来保证的

img

redo log

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

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是内存中，后者是磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于刷新脏页到磁盘，发生错误时，进行数据恢复使用

如果没有redo log，可能会出现哪些问题？

在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。当在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页为脏页。而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。

img

那么redo log是如何解决这个问题的？

img

有了redo log之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。而如果脏页成功刷新到磁盘或者涉及到的数据已经落盘，此时redo log就没有作用了，就可以删除了。所以存在的两个redo log文件是循环写的

那为什么每一次提交事务，要刷新redo log到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘中呢？

因为在业务操作中，操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为WAL（Write-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

隐藏字段

介绍

img

当创建了上面这张表之后，在查看表结构的时候，就可以显式的看到这三个字段。实际上除了这三个字段以外，InnoDB还会自动的添加三个隐藏字段及其含义分别是：

隐藏字段含义
DB_TRX_ID最近修改事务id，记录插入这条记录或最后一次修改该记录的事务id
DB_ROLL_PTR回滚指针，指向这条记录上一版本，用于配合undo log，指向上一个版本
DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

而上述的前两个字段是肯定会添加，是否添加最后一个字段DB_ROW_ID，要看当前表有没有主键，如果有主键，则不会添加隐藏字段

undo log

介绍

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除

而update、delete的时候，产生的undo log日志不仅在回滚的时候需要，在快照读的时候也需要，不会立即删除

版本链

有一张表原始数据为：

img

DB_TRX_ID：代表最近下修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是自增的

DB_ROLL_PTR：由于这条数据是才插入的，没有被更新过，所以该字段值为null

然后，有四个并发事务同时在访问这张表

1、第一步

img

当事务2执行第一条修改语句时，会记录undo log日志，记录数据变更之前得样子；然后更新记录，并且记录本次操作得事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本

img

2、第二步

img

当事务3执行第一条修改语句时，也会记录undo log日志，记录数据变更之前得样子；然后更新记录，并且记录本次操作得事务id，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本

img

3、第三步

img

当事务4执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子；然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本

img

最终发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最近的旧纪录，链表尾部是最早的旧纪录

readview

Readview（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（为提交的）id

ReadView中包含了四个核心字段：

字段含义
m_ids当前活跃的事务id集合
min_trx_id最小活跃事务ID
max_trx_id预分配事务ID，当前最大事务ID+1（因为事务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：在事务中每一次执行快照读时生成ReadView
  • REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

原理分析

RC隔离级别

RC隔离级别下，在事务中每一次执行快照读时生成ReadView

两次快照读读取数据，是如何获取数据的？

在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下：

img

那么这两次快照读在获取数据时，就需要根据生成的ReadView以及ReadView的版本链访问规则，到undo log版本链中匹配数据，最终决定此次快照读返回的数据

1、第一次快照读具体的读取过程

img

img

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配

  • 先匹配img这条记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也是不满足，都不满足，则继续匹配undo log版本链的下一条

  • 再匹配第二条img这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也是不满足，都不满足，则继续匹配undo log版本链的下一条

  • 再匹配第三条img，这条记录对应的trx_id为2，也就是将2带入右侧的匹配规则中。①不满足 ②满足 终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据

2、第二次快照读具体二点读取过程

img

img

再进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

  • 先匹配img这条记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也是不满足，都不满足，则继续匹配undo log版本链的下一条

  • 再匹配第二条img这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②满足 终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据

RR隔离级别

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。而RR是可重复读，在下一个事务中，执行两次相同的select语句，查询到的结果是一样的

MySQL是如何做到可重复读的呢？

img

在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样，ReadView的版本链匹配规则也一样，那么最终快照读返回的结果也是一样的

所以，MVCC的实现原理就是通过InnoDB表的隐藏字段、undo log版本链、ReadView来实现的。而MVCC+锁，则实现了事务的隔离性。而一致性则是由redolog和undolog保证

img

MySQL管理

系统数据库

MySQL数据库安装完成后，自带了以下四个数据库，具体作用如下：

数据库含义
mysql存储在MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
information_schema提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限
performance_schema为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

常用工具

mysql

该mysql不是指mysql服务，而是指mysql的客户端工具

语法 ：
	mysql [options] [database]
选项 ：
	-u, --user=name #指定用户名
	-p, --password[=name] #指定密码
	-h, --host=name #指定服务器IP或域名
	-P, --port=port #指定连接端口
	-e, --execute=name #执行SQL语句并退出

-e选项可以在MySQL客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便

示例：

mysql -uroot –p123456 db01 -e "select * from stu";

mysqladmin

mysqladmin是一个执行管理操作的客户端程序，可以用它来检查服务器的配置和当前状态、创建并删除数据库等

通过帮助文档查看选项：
	mysqladmin --help

语法：
	mysqladmin [options] command ...
选项:
	-u, --user=name #指定用户名
	-p, --password[=name] #指定密码
	-h, --host=name #指定服务器IP或域名
	-P, --port=port #指定连接端口

示例：

mysqladmin -uroot –p1234 drop 'test01';
mysqladmin -uroot –p1234 version;

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具

语法 ：
	mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
	-d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
	-o, --offset=# 忽略掉日志中的前n行命令。
	-r,--result-file=name 将输出的文本格式日志输出到指定文件。
	-s, --short-form 显示简单格式， 省略掉一些信息。
	--start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
	--start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。

A. 查看 binlog.000008这个二进制文件中的数据信息

mysqlbinlog binlog.000008

简单格式为：

mysqlbinlog -s binlog.000008

mysqlshow

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密码 --count

2、查看数据库db01的统计信息

mysqlshow -uroot -p1234 db01 --count

3、查看数据库db01中的course

mysqlshow -uroot -p1234 db01 course --count

4、查看数据库db01中的course表的id字段的信息

mysqlshow -uroot -p1234 db01 course id --count

mysqldump

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。本分内容包括创建表，及插入表的sql语句

语法 ：
	mysqldump [options] db_name [tables]
	mysqldump [options] --database/-B db1 [db2 db3...]
	mysqldump [options] --all-databases/-A
连接选项 ：
	-u, --user=name 指定用户名
	-p, --password[=name] 指定密码
	-h, --host=name 指定服务器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, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

示例：

1、备份db01数据库

mysqldump -uroot -p1234 db01 > db01.sql

备份出来的数据包括：

  • 删除表的语句
  • 创建表的语句
  • 数据插入语句

2、备份db01数据库中的表的数据，不备份表结构

mysqldump -uroot -p1234 -t db01 > db01.sql

3、将db01数据库的表结构与数据分开备份

mysqldump -uroot -p1234 -T /root db01 score

mysqlimport/source

1、mysqlimport

mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出来的文本文件

语法 ：
	mysqlimport [options] db_name textfile1 [textfile2...]
示例 ：
	mysqlimport -uroot -p2143 test /tmp/city.txt

2、source

如果需要导入sql文件，可以使用mysql中的source指令：

语法 ：
source /root/xxxxx.sql
没办法，我就是这么菜
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL3.0.6 数据库sql文件 导入 phpmyadmin
安东省心的博客
05-06 416
操作系统：windows10 MySQL数据库软件：wampserver 3.0.6 第1步：双击运行wampserver程序，单击屏幕右下角图标 第2步：选择 phpmyadmin，目前有4个数据库 >>> 选择“导入” 第3步：“选择文件”找到你下载好的sql文件 >>> “执行” 第4步：导入成功 >>> studen...
HTML3.0学习计划
09-06
六、进阶学习 随着基础的巩固，可以进一步探索框架和库，如Bootstrap和jQuery，它们简化了网页开发。还可以学习关于服务器端编程，如PHP或Node.js，以及数据库管理，如MySQL，以构建动态网站。 总结，HTML3.0的学习...
【3.0】MySQL数据库安装
Chimengmeng的博客
01-29 638
【一】各种服务器的端口 MySQL ：3306 Redis ： 6379 MongoDB ： 27017 Django ： 8000 flask ： 5000 【二】MySQL数据库介绍 MySQL是一个开源免费的关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。 MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RD...
AnalyticDB for MySQL 3.0 技术架构解析
chikuai9995的博客
08-01 220
企业数据需求不断变化，近年来变化趋势日益明显，从数据的3V特性看：体积，速度和变化；Big Data强调数据量，PB级以上，是静态数据。而Fast Data在数据量的基础上，意味着速度和和变化，意味着客户可以更加实时化、更加快速地进行数据处理。在Forrester最近的一项研究中，超过75％的...
【数据库】3.0 MySQL入门学习（三）——Windows系统环境下MySQL安装
delj70098的博客
01-19 192
1.0 我的操作系统是window10 专业版 64位。，不过至少windows7以上系统都是一样的。 关于MySQL如何下载，请参考博文： 【数据库】2.0 如何获得MySQL以及MySQL安装 https://www.cnblogs.com/xiaofu007/p/10293529.html linux环境下安装MySQL传送门： 【数据库】3.0 MySQL入门学习（四...
3.0 MySQL数据库的基本操作
AndrewNeo
07-30 131
MySQL 安装好之后就可以进行数据库的的相关操作了。数据库就像车库一样，每个小区里都有很多车库用来存车，他们都有唯一的车库号。同样，在 MySQL 中也可以创建多个不同名称的数据库存储数据。 SQL 是一门专门用来与数据库进行通信的语言。我们使用 SQL 来操作数据库。 数据库的操作包括创建、删除、修改数据库，这些操作都是管理数据库的基础。 ...
mysql3.0数据库修复_mysql3.0监控mysql数据库
weixin_35496861的博客
02-18 87
1、首先安装Percona源2、安装zabbix Agent相关的组件# yum install -y percona-zabbix-templates还要安装：# yum install php php-mysql -y3、复制配置文件# cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabb...
基于 Java+Mysql+ssh员工管理系统
06-10
【适用人群】：适用于希望学习不同技术领域的小白或进阶学习者。可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。 【项目介绍】：Employee jsp+servlet+javaBean构成的MVC 员工、部门的增删改查、分页...
高级篇.pdf
10-25
总的来说，这个高级篇涵盖了从语言新特性到系统优化、数据库管理、操作系统和编译原理等多方面的知识，是Java开发者进阶的必备指南。通过深入学习和实践，我们可以更好地应对复杂系统中的挑战，提升开发和运维效率。
[搜索链接]BosDirectory v3.0(国外PHP+MYSQL商业分类信息程序)_bosdirectory30.rar
04-21
【适用人群】：适用于希望学习不同技术领域的小白或进阶学习者。可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。【附加价值】：项目具有较高的学习借鉴价值，也可直接拿来修改复刻。对于有一定基础或...
MySQL详解（五）——高级 3.0
qq_43409401的博客
02-02 337
慢查询日志、主从复制、Mycat分库分表
【MySQL进阶】 锁
南瓜籽_的博客
02-22 643
【MySQL进阶】锁
测试开发面试题---MySQL
m0_53302824的博客
07-22 612
索引是数据库管理系统中用于提高数据检索速度的数据结构，它可以帮助快速定位数据不需要扫描全表。提高查询速度；唯一性约束，确保数据不会重复；辅助排序和分组；减少磁盘IO。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 696
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
【MySQL】根据binlog日志获取回滚sql的一个开发思路
weixin_45385457的博客
07-19 445
不同客户端之间会交替追加在 binlog 中，需要通过 👆的 binlog 匹配流程来控制匹配。行，需要再次寻找 thread_id 相同的行，匹配到后执行上一个流程。在 binlog 中的线程 ID 记录为 thread_id。一个线程执行多个 sql 回滚到同一个文件可能带来的问题。对应 binlog 日志中的 thread_id=指定 mysql 客户端 开始时间和结束时间。打开 mysql 客户端 开始时间。关闭 mysql 客户端 结束时间。先匹配 thread_id 相同的。
Mysql高价语句
最新发布
Alone8046的博客
07-22 518
8.子查询：内查询，嵌套查询，selsect语句当中有嵌套了一个select，嵌套的select才是子查询，查询的时候是先执行子查询语句，外部的select再根据子条件的结果进行过滤查找；左连接以左表为基础，接收左表的所有行，以左表的记录和右表的记录进行匹配，匹配左表的所有，以及右表中符合条件的行，不符合的显示null。5.表和列的别名：因为在实际工作中，表的名字和列的名字可能会很长，书写起来不太方便，多次声明表和列时完整的展示太复杂，设置别名可以使书写简化，可读性增加，简洁明了。是多表数据的集合体。
mysql、oracle、db2数据库连接参数
卿本佳人的博客
07-18 342
mysql、oracle、db2数据库连接参数 参数/数据库 driver url Mysql com.mysql.jdbc.Driver 或 com.mysql.cj.jdbc.Driver jdbc:mysql://localhost:3306/数据库名 Oracle oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@localhost:1521:orcl //orcl为数据库SID DB2 com.ibm.db2.jcc.D
MySQL基本语法规则 By 尚硅谷
qq_62678419的博客
07-19 923
主要是一些Mysql数据库的语法

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值