表空间管理,事物,ACSR,隔离级别+锁+MVCC

最新推荐文章于 2024-09-17 09:10:57 发布
最新推荐文章于 2024-09-17 09:10:57 发布
阅读量301 收藏 1
点赞数
分类专栏： DBA 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42944130/article/details/106334966
版权

1. 表空间管理

1.0 以下的配置，一般是在初始化之前就应该设置好的。

1.1 共享表空间

1.1.1 查询参数

mysql> select @@innodb_data_file_path;
mysql> select @@innodb_autoextend_increment;

1.1.2 设置共享表空间

方法一： 初始化时设置方法(建议)
step1 :
vim /etc/my.cnf
innodb_data_file_path=ibdata1:1024M;ibdata2:1024M;ibdata3:1024M:autoextend
step2:
mysqld --initialize-insecure …

方法二： 后设置
#step1 ：
vim /etc/my.cnf
innodb_data_file_path=ibdata1:(当前文件)M;ibdata2:1024M;ibdata3:1024M:autoextend

step2 ：
重启数据库：
/etc/init.d/mysqld restart

例子：
– 修改配置文件
vim /etc/my.cnf
错误的：
innodb_data_file_path=ibdata1:128M;ibdata2:128M:autoextend

重启数据库，错误日志：
vim /data/3306/data/db01.err
2020-03-25T02:24:27.942930Z 0 [ERROR] InnoDB: The innodb_system data file ‘./ibdata1’ is of a different size 4864 pages (rounded down to MB) than the 8192 pages specified in the .cnf file!

[root@db01 data]# du -sh ibdata1
76M ibdata1

正确的:
innodb_data_file_path=ibdata1:76M;ibdata2:128M:autoextend

1.2 临时表空间

innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:128M:autoextend:max:500M

例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是5，修改后的值为15，那么Undo日志为<T1, X, 5>，Redo日志为<T1, X, 15>。
也有把undo和redo结合起来的做法，叫做Undo/Redo日志，在这个例子中Undo/Redo日志为<T1, X, 5, 15>。
当用户生成一个数据库事务时，undo log buffer会记录被修改的数据的原始值，redo会记录被修改的数据的更新后的值。

1.3 undo表空间

Important
The number of undo tablespaces can only be configured when initializing a MySQL instance and is fixed for the life of the instance.

1.3.1 如何查看

SELECT @@innodb_undo_tablespaces;  ---->3-5个    #打开独立undo模式，并设置undo的个数。
SELECT @@innodb_max_undo_log_size;               #undo日志的大小，默认1G。
SELECT @@innodb_undo_log_truncate;               #开启undo自动回收的机制。
SELECT @@innodb_purge_rseg_truncate_frequency;   #触发自动回收的条件，单位是检测次数。

1.3.2 设置方法：

innodb_undo_tablespaces=3           
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32

[root@db01 data]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql 
datadir=/data/3306/data
server_id=6
socket=/tmp/mysql.sock 
#log_bin=1
#gtid_mode=ON
#enforce_gtid_consistency=ON
secure-file-priv=/tmp
innodb_data_file_path=ibdata1:128M;ibdata2:128M:autoextend
innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
innodb_undo_tablespaces=3           
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
[client]
socket=/tmp/mysql.sock
[root@db01 data]# 


重新初始化MySQL 
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

1.4 General Tablespace

参考： 
https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
ALTER TABLE t2 TABLESPACE ts1;

2 事务 Transaction

2.1 什么是事务？

伴随着“交易”类的业务需求，支持的一种工作模式。

2.2 什么是交易？

以物换物
货币换物
虚拟货币换物
现实生活中，必须要保证交易的平衡和等价。
互联网的盛行，将交易搬到了线上进行。在线上（计算机）发生的交易行为，也要保证他的等价和平衡。
所以，事务，就是在计算机交易类程序中保证交易等价、平衡的工作模式。

2.3 事务的ACID(atomicity, consistency, isolation, durability.)四项特性

A : 原子性
事务中的所有操作，都是一个完整的整体。要么全成功，要么全失败。不能出现中间状态。
C ：一致性
事务发生 前 中 后，数据应当保证一致状态。数据不会受到任何的破坏。数据最终一致是事务的最终目的。
I ：隔离性
多事务工作期间是互相不影响的。
D ：持久性
事务工作完成（commit）,数据保证永久不丢失。数据落盘。

2.4事务生命周期

— 标准事务控制语句

begin;/start transaction; — 开启事务。
commit; — 提交事务。
rollback; — 回滚事务。

— 标准的事务语句

单条或多条 DML语句： update，delete ，insert， select

— 事务操作演示

mysql> begin;
mysql> delete from city where id=1;
mysql> delete from city where id=2;
mysql> delete from city where id=200;
mysql> commit;

mysql> begin;
mysql> delete from city where id=1000;
mysql> delete from city where id=2000;
mysql> rollback;

— 自动提交机制

— 介绍
select @@autocommit;
作用：在没有使用begin 显式触发开的DML操作，MySQL会在此条命令执行完成后自动提交事务。
关闭自动提交之后，所有事务必须显式commit，才能提交成功。
begin; — 隐式触发。
delete 。。。。。
commit — 隐式提交。

— 配置
临时生效：
mysql> set global autocommit=0;
重新登录会话生效。

永久配置：
vim /etc/my.cnf
autocommit=0
重启生效。

— 触发隐式提交的特殊情况

begin 
a
b

事务期间发生了一下操作，会触发隐式提交
begin
SET AUTOCOMMIT = 1
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

---- 触发隐式回滚的特殊情况

中间命令执行失败
会话结束： 窗口关闭，kill…
数据库停止。

3. 事务中的ACID如何保证?

3.1 关键名词

3.1.1 redo log

重做日志。
磁盘文件： ib_logfile0~1 ,48M , 轮询使用
内存缓冲： redo log buffer，redo内存区域

3.1.2 数据表空间

磁盘区域
ibd ：存储 数据行和索引
buffer pool：缓冲区池,数据和索引的缓冲

3.1.3 LSN 日志序列号

磁盘数据页
buffer pool
redo文件
redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动

3.1.4 WAL : Write ahead log

日志优先写的方式实现持久化

3.1.5 脏页: Dirty pages

内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.

3.1.6 Checkpoint（CKPT）

MySQL维护着检查点队列（checkpoint list）,就是将脏页刷写到磁盘的动作

3.1.7 DB_TRX_ID:

事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务生命周期.

3.2 redo log 功能详解

3.2.1 redo 介绍

重做日志。
内容记录：内存数据页的变化日志。
主要保证：ACID中的“D”的特性，对于AC也有相关辅助作用。

3.2.2 redo 工作原理图

略。

3.2.3 Undo日志的功能详解

— undo 介绍
回滚日志。
内容： 记录的是事务操作的逆向操作。
主要保证： ACID中的“A”的特性，实现rollbackup回滚的功能。也是MVCC特性的主要保障。所以对于C和I也有保证。
undo最多128个segments。96个在undo tablespace中，32个在temp tablespace。
每个段默认1024槽位。

Internally, InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

— undo日志的原理图
略。

4. ACSR 自动故障恢复的实现逻辑

begin;
update set a=17 —> a=18 ;
未提交。

  1. 发起更新语句，从磁盘将t1.ibd,page_no=1001数据页+LSN=100， 加载到buffer pool中。
  2. 更新buffer pool中数据页之前，首先申请undo slot，将修改的“逆操作”日志写入到undo中。
  3. 生成当前事务 DB_TRX_ID(6字节)+ DB_ROLL_PTR(7字节)。
  4. 开始修改buffer_pool中数据页，更新LSN=101。
  5. 内存数据页，修改的过程日志+LSN=101 ，记录到redo buffer中。
  6. 假设此次事务的日志，随着其他事务的提交，事务日志顺便刷写到了redo日志文件中。

此时如果出现宕机（CRASH ）,下次启动时的工作过程：

  1. 启动数据库引擎。
  2. InnoDB自动检查，磁盘数据页和redo log的LSN的大小。发现redo LSN> page LSN,立即出发CR过程。
  3. 前滚： 加载历史数据页+redo log到内存 。 通过redo log 重做事务，构造脏页。
  4. 检查到此次事务，并没有commit标记。除法回滚动作。
  5. 根据 DB_TRX_ID(6字节)+ DB_ROLL_PTR(7字节)，从undo segment 的slot中此次事务的undo日志,进行回滚。
  6. 所有工作做完，数据库实例正常启动。
  7. 另外MySQL提供DoubleWrite机制，防止Page particularly fsync 。DWB默认在共享表空间中，默认2M空间

5. 隔离级别+锁+MVCC

以上三项特性，主要保证ACID中的“I”隔离性。

5.1 隔离级别（transaction_isolation）

作用： 主要提供了“读”的隔离性。
读是什么？
select ，update ，delete ，insert 都要牵扯到读数据页。
类型 ：
RU : 读未提交。脏读、不可重复读、幻读
RC ：读已提交。不可重复读、幻读
RR ：可重复读。幻读
SR ：可串行化。
控制参数：
mysql> select @@transaction_isolation;

5.2 脏读

事务工作期间，读到了 《未提交事务》 正在发生修改的脏页。

脏读模拟过程:
vim /etc/my.cnf
transaction_isolation=READ-UNCOMMITTED
mysql> select @@transaction_isolation;
±------------------------+
| @@transaction_isolation |
±------------------------+
| READ-UNCOMMITTED |
±------------------------+

5.3 不可重复读

在同一个事务窗口，做相同数据的读取，得到的是不同的值。

5.4 幻读现象说明

在事务更新过程中，出现了其他事务已经已提交数据幻行。

6. 隔离级别 、锁 、MVCC

6.1 类型

transaction_isolation=xxxx,默认是RR。
RC ：不可重复读、幻读。
RR ：幻读
SR
RU

6.2 RR级别下如何防止不可重复读

RR级别下通过MVCC（多版本并发控制）技术，防止不可重复读现象。

6.3 MVCC是什么？

每次开启一个全新的事务窗口（begin），都会生成当前最新的一致性快照（undo），直到事务commit或者rollback
此次事务，都会在此快照中进行操作。
我们也把这种技术，称之为一致性快照读取（一致性非锁定读）。
可以大大提高事务的并发能力。

6.4 RR级别如何方式幻读现象

RR级别下，提供了GAP（间隙锁），next-lock（下一键锁）锁定功能来预防幻读。

6.5 MySQL中的锁机制

6.5.1 作用

事务隔离性：主要提供写隔离
归根结底是用来保护“资源”不被争用。

6.5.2 基于资源分类

#1. 内存资源 ：

Latch（闩锁）： mutex,rw-lock
作用： 控制内存中的线程使用资源的一种锁。防止数据页被意外置换出去。
线程对于数据页修改：需要申请mutex
线程对于数据页读取：需要申请rw-lock

#2. 存储资源
server层：

— Matedata lock ：元数据锁
触发条件：
DDL操作： alter
备份时会触发。FTWRL：Flush table with read lock
— table lock ：表锁
在InnoDB中，虽然锁粒度是行锁，也有可能触发表锁。
lock tables t1 read;
从行锁升级至表锁。例如update 操作 where条件出现 ALL，Index执行计划时，会升级为表锁。

引擎层：针对索引进行加锁

record lock ：记录锁 ----> 聚簇索引
GAP lock ：间隙锁 ----> 辅助索引范围
Next lock ：下一键锁 ----> record+GAP

1.5.3 锁的类型演示

准备测试表

create table test(
id int primary key not null auto_increment ,
num int not null ,
name varchar(20) not null
)charset utf8mb4;
insert into test values
(1,1,'a'),
(2,3,'b'),
(3,5,'c'),
(4,10,'d'),
(5,11,'aa'),
(6,13,'bb'),
(7,15,'cc'),
(8,30,'dd'),
(9,35,'aaa'),
(10,40,'baa'),
(11,50,'caa'),
(12,53,'daa'),
(13,55,'aas'),
(14,60,'bbs'),
(15,75,'ccs');
commit;
alter table test add index idx(num);

matedata lock

session A: 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
mysql> update test set num=36 where id=10;

session B: 
drop table test;

session C: 
show processlist;

table lock

record lock 
where id=

#gap --> RR

session A ： 
mysql> begin;
mysql> update test set name='oldguo' where num<10;

session B： 
mysql> begin;
mysql> insert into test values(16,4,'oldboy');

session C: 
mysql> select * from innodb_lock_waits\G
1.5.4 加锁级别

IX 意向排他锁
X 写锁 排他锁
IS 意向共享锁
S 读锁 共享锁
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

X IX S IS

X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

  1. ACID - C（一致性保证） 特性保证

数据页最终一致：
undo
redo
ACSR
DW
事务工作期间数据最终一致
transaction_isolation 读一致性
LOCK 写一致性
MVCC 一致性快照读，一致性非锁定读

以上所有技术体现，都是为了保证数据最终一致。

  1. InnoDB 核心特性总结

事务（ACID）
MVCC
ACSR
Double Write
行级锁
change buffer
聚簇索引
AHI
======
热备
主从复制

姬凤
关注
专栏目录
CSR86XX+MIC+和+line+in+串扰解决方案
10-22
CSR86XX系列芯片是由Cirrus Logic公司生产的一款高性能音频蓝牙芯片，广泛应用于便携式音频设备中。在这些设备中，MIC（麦克风）输入和LINE IN（线路输入）都是十分重要的音频输入方式，它们在设计过程中可能会相互...
CSR第六代BlueCore蓝牙芯片支持v2.1+EDR技术...
12-03
CSR公司的第六代BlueCore蓝牙芯片是一款革命性的无线通信解决方案，专为满足日益增长的低功耗、高质量无线连接需求而设计。这款芯片的核心是对其前代产品BlueCore4的重大升级，全面支持蓝牙技术规范v2.1+EDR...
实战saas系统多租户数据隔离（三）每个租户使用独立的表空间
johntsu的专栏
09-08 6772
目录 0.前言 1.需求分析 2.系统架构设计 3.环境准备 4.编码实现 4.1添加父项目依赖坐标 4.2实现eureka注册中心 4.3实现zuul网关 4.4实现用户微服务mt2-user 4.5实现资料微服务mt2-profile 5.项目测试 总结 参考资料 0.前言 上一篇文章中，我们自己实现了saas系统架构中租户数据隔离的其中一...
第二十周-day86-数据库day08-存储引擎02
linux丶晨星
08-14 163
上节回顾： InnoDB 与 MyISAM 事务(ACID):原子性，一致性，隔离性，持久性 行(记录)锁+表锁 热备份(事务) ACSR(自动故障恢复) MVCC 复制 GC GTID MTS 案例 环境：贵州某交管系统，违章信息 MySQL5.7,70多张表 备份策略：每周六 mysqldump全备，每天binlog备份 问题描述：在网上找了个参数调整ibtmp1大小，直接在...
Tablespace for table '`pomelo`.`bag`' exists. Please DISCARD the tablespace before IMPORT.
热门推荐
独行者
08-02 1万+
//遇到的问题是，删除数据库之后，重新创建数据库，在创建数据库表的时候，明明没有该表，却提示存在这个表。这是数据库缓存造成的//解决方法FLUSH TABLES;/*安装MySql数据库(略)登录MySql: mysql –u用户名 –p密码 (登录成功提示符：mysql>)创建数据库: mysql> create database Pomelo;选择数据库: mysql> use Pomelo;
存储引擎
Pang的博客
12-16 195
简介 相当于Linux文件系统，只不过比文件系统强大 MySQL 存储引擎类型(笔试:3-4种) oldguo[world]>show engines; oldguo[world]>select @@default_storage_engine; oldguo[world]>show variables like '%engine%'; InnoDB (5.5以后默...
基于csr8645芯片+NS4150设计的蓝牙音箱功放评估板硬件(原理图+PCB)工程文件.zip
04-26
该压缩包包含的是一个基于CSR8645芯片和NS4150设计的蓝牙音箱功放评估板的硬件工程文件。CSR8645是一款先进的蓝牙音频接收器，广泛应用于无线音频解决方案，如蓝牙音箱和耳机。它支持蓝牙4.2版本，具有高质量的音频...
CSR蓝牙模块选型+csr8675+QCC300+技术资料+参考设计.zip
01-30
【CSR蓝牙模块选型】 CSR（Cambridge Silicon Radio）是一家知名的蓝牙芯片制造商，后被高通（Qualcomm）收购，现以Qualcomm CSR品牌存在。CSR蓝牙模块在无线音频、物联网(IoT)以及移动设备等领域广泛应用。选型时...
基于csr8645芯片设计的蓝牙音箱功放板ALTIUM硬件原理图+PCB文件.zip
04-22
基于csr8645芯片设计的蓝牙音箱功放板ALTIUM硬件原理图+PCB文件,2层板设计，大小为98*72mm，包括AD设计的完整原理图PCB图工程文件，可供学习设计参考。 主要器件如下： Name Description --------------------------...
【MySQL】表的操作【有关表结构的操作】【创建、查看、删除、修改表结构】
aaa114514aaaa的博客
09-12 1195
对于mysql数据库的表的结构的查看，创建、删除、修改、【对字段的添加、修改】
MySQL中几种常见的行格式
weixin_45923633的博客
09-13 444
动态格式会根据行中数据的实际大小动态地调整行的存储方式。如果行中的某个字段是变长的，那么这个字段的实际数据将被存储在行的末尾，而不会在行的开头占用固定的空间。这种格式适用于所有列都是固定长度的情况，可以提供最快的访问速度，但可能会浪费存储空间。：紧凑格式试图减少内部碎片，使得行的平均大小变小，从而在一个数据页中可以存储更多的行。：页面格式是一种较新的行存储格式，它旨在提高大表的访问性能和存储效率。是用来指定表中行的存储格式的属性。不同的行格式会影响数据的存储方式，进而影响存储空间的使用效率和访问性能。
2024最新版MySQL详细学习教程
最新发布
2401_86940371的博客
09-17 1067
返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个 “off”字符串。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。MD5(str)函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密。字符串函数是MySQL中最常用的一类函数，字符串函数主要用于处理表中的字符串。如果count是负数，返回第(count的绝对值(从右边数))个字符右边的字符串。
基于SpringBoot+Vue+MySQL的瑜伽馆管理系统
程序员大金的博客
09-11 1315
基于SpringBoot+Vue+MySQL的瑜伽馆管理系统，附源码。
MYSQL数据库基础篇——MYSQL的安装与使用
qq_74177011的博客
09-11 503
mysql -u表示用于登录的用户名，后面的root就是用户名称，之前讲到，安装mYSQL时默认用户名是root，-p表示指定密码，即登录密码。首先打开命令提升符，并输入net start mysql80可以启动，输入net stop mysql80可以停止。这里选择第一个，当然，有可能你的版本下的MYSQL并没有这个选项，那么我们可以选择。注意：如果报以下错误，则可以尝试通过管理员的方式打开命令提示符进行操作。输入mysql -u root -p，然后输入密码就好了。然后显示下面的界面说明连接成功了；
基于SpringBoot+Vue+MySQL的网上甜品蛋糕售卖店管理系统
程序员大金的博客
09-12 759
基于SpringBoot+Vue+MySQL的网上甜品蛋糕售卖店管理系统，附源码。
基于SpringBoot+Vue+MySQL的招聘管理系统
程序员大金的博客
09-13 993
基于SpringBoot+Vue+MySQL的招聘管理系统，附源码。
55 mysql 的登录认证流程
970655147的专栏
09-15 922
这里我们来看一下 mysql 的认证的流程我们这里仅仅看 我们最常见的一个 认证的处理流程我们经常会登录的时候 碰到各种异常信息大概的流程是这样客户端和服务器建立连接之后, 服务器向客户端发送 salt然后 客户端根据 salt 将客户端传入的密码加密之后, 以及相关登录信息传递给服务器然后 服务器进行验证, 验证失败, 响应对应的错误信息给客户端服务器发送 salt 的信息如下客户端发送的认证请求如下服务器响应的认证失败信息如下。
MySQL系统库——mysql库
2301_79896143的博客
09-11 736
从上面的叙述中我们可以了解到，每个表所记载的权限的范围（也被称为权限的高低）不同，在实际的操作过程中，计算机会优先访问user表，如果user表中的某一个权限为Y，则针对该权限不再需要访问其他表，如果为N，则访问下一级的表。除去一些系统变量，我们可以关注有关于用户权限的四个表，它们分别是user，db，table_priv，column_peiv。Create_tmp_table_priv：存储用户账户是否具有创建临时表的权限。Lock_tables_priv：存储用户账户是否具有锁定表的权限。
T+畅捷通信息技术：资产管理与账套配置指南
"该文档是T+畅捷通信息技术股份有限公司的产品手册，主要介绍了一款资产管理模块的使用，包括安装、注册激活、账套建立、用户权限设置、账套选项配置、单据与基础档案设置、期初数据录入、账套备份、期末处理以及...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值