[转帖]第五章 MySQL 存储引擎

第五章,mysql,存储,引擎 · 浏览次数 : 0

小编点评

**MVCC: 多版本并发控制乐观锁** MVCC是一种乐观锁机制,在事务开始之前,获取系统的最新的版本快照,并缓存到MVCC中。当事务结束后,从MVCC中获取最新的版本快照。 **RCtrx1: Rv1 Rv2 commit** RCtrx1是一个RVC,在事务开始时,会生成一个 global consistency snapshot RV-CS1(10:00)。当事务结束后,从MVCC中获取最新的版本快照 RV-CS2 (10:01)。 **RCtrx2: RVV1 RVV2 RR trx1 : 第一个查询时,生成global consitence snapshot RV-CS1(10:00) ,一直伴随着事务生命周期结束。** RCtrx2是一个RR,在事务开始时,会生成一个 global consistency snapshot RV-CS2 (10:01)。当事务结束后,从MVCC中获取最新的版本快照 RV-CS1 (10:00)。

正文

第五章 MySQL 存储引擎

https://www.jianshu.com/p/d12191803f17

 

本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。
只要掌握80%,轻松助力薪资15k-25K。
课程内容均来自与MySQL官网+MySQL源码。
配套精品视频(2021 5月全新录制,版权所有:郭加磊 oldguo。),获取方法私聊。

1. 介绍

存储引擎MySQL中的“文件系统”

2. 种类

2.1 Oracle MySQL自带的存储引擎种类

mysql> show engines;
InnoDB           ******  
MyISAM   
MRG_MYISAM        
CSV                              
BLACKHOLE         
PERFORMANCE_SCHEMA   
ARCHIVE            
MEMORY            
FEDERATED         

彩蛋: 请你列举MySQL中支持的存储引擎种类?
InnoDB、MyISAM、CSV、MEMORY

2.2 分支产品的引擎种类介绍

Percona、Mariadb
TokuDB、MyRocks、Rocksdb
特点:
1. 压缩比15倍以上
2. 插入数据性能

适应场景:
例如Zabbix监控类的平台、归档库、历史数据存储业务

3. InnoDB存储引擎特性

MVCC : 多版本并发控制
聚簇索引 : 用来组织存储数据和优化查询,IOT。
支持事务 : 数据安全保证
支持行级锁 : 控制并发
外键
多缓冲区支持
自适应Hash索引: AHI
复制中支持高级特性。
备份恢复: 支持热备。
自动故障恢复:CR Crash Recovery
双写机制:DWB Double Write Buffer

彩蛋: InnoDB 核心特性有哪些? InnoDB和MyISAM区别有哪些?
回答:
InnoDB 支持: 事务、MVCC、聚簇索引、外键、缓冲区、AHI、CR、DWB,MyISAM不支持。
InnoDB 支持: 行级锁,MyISAM支持表级锁。
InnoDB 支持热备(业务正常运行,影响低),MyISAM支持温备份(锁表备份)。
InnoDB 支持CR(自动故障恢复),宕机自动故障恢复,数据安全和一致性可以得到保证。MyISAM不支持,宕机可能丢失当前修改。

案例1 :
某期学员负责: 运维 + MySQL 工作
1.1 环境: zabbix 3.2 + centos 7.3 + mariaDB 5.5 InnoDB引擎 ,zabbix系统 监控了2000多个节点服务
1.2 现象 : 每隔一段时间zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
1.3 问题 :
1.3.1. zabbix 版本过低。
1.3.2. 数据库版本
1.3.3 zabbix数据库500G,存在一个文件里ibdata1,手工删除1个月之前的数据,空间不释放。
1.4 优化建议:
1.4.1.数据库版本升级到percona 5.7+ 版本 mariadb 10.x+,zabbix升级更高版本
1.4.2.存储引擎改为tokudb
1.4.3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
1.4.4.关闭binlog和双1
1.4.5.参数调整....
优化结果:
监控状态良好
参考:
https://www.jianshu.com/p/898d2e4bd3a7

为什么?
(1) 原生态支持TokuDB,另外经过测试环境,5.7要比5.5 版本性能 高 2-3倍
(2) TokuDB:insert数据比Innodb快的多,数据压缩比要Innodb高
(3)监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
(4)关闭binlog ----->减少无关日志的记录.
(5)参数调整...----->安全性参数关闭,提高性能.

扩展:部署 zabbix新版+ 新版本 tokudb VS 部署 zabbix + 低版本mariadb
Tokudb特性:
Additional features unique to TokuDB include:
Up to 25x Data Compression
Fast Inserts
Eliminates Slave Lag with Read Free Replication
Hot Schema Changes
Hot Index Creation - TokuDB tables support insertions, deletions and queries with no down time while indexes are being added to that table
Hot column addition, deletion, expansion, and rename - TokuDB tables support insertions, deletions and queries without down-time when an alter table adds, deletes, expands, or renames columns
On-line Backup
参考内容:
https://www.jianshu.com/p/898d2e4bd3a7
https://mariadb.com/kb/en/installing-tokudb/
https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html

案例2:
环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.

问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据

职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM

解决方案:

  1. 升级MySQL 5.6.1x版本
  2. 升级迁移所有表到新环境,调整存储引擎为InnoDB
  3. 开启双1安全参数
  4. 重构主从

4. 存储引擎的基本操作

4.1 查看存储引擎

4.1.1 查询支持的存储引擎

mysql> show engines;

4.1.2 查询、设置默认存储引擎

mysql> select @

4.1.3 查看、设定 表的存储引擎

1) 查看某张表的存储引擎 
 mysql> show create table xta;
 
(2) 查询系统中所有业务表的存储引擎信息
 mysql> select 
 table_schema,
 table_name ,
 engine 
 from information_schema.tables  
 where table_schema not in ('sys','mysql','information_schema','performance_schema');
 
 (3)创建表设定存储引擎
 mysql> create table xxx (id int) engine=innodb charset=utf8mb4;
 
 (4)修改已有表的存储引擎
  mysql> alter table xxx engine=myisam;
  mysql> alter table world.xxx engine=innodb;

项目:将所有的非InnoDB引擎的表查询出来,批量修改为InnoDB

 mysql> select table_schema,table_name ,engine 
  from information_schema.tables  
  where 
  table_schema not in ('sys','mysql','information_schema','performance_schema') 
  and engine !='innodb';
  
  mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;")   from information_schema.tables     where    table_schema not in ('sys','mysql','information_schema','performance_schema')    and engine !='innodb' into outfile '/tmp/a.sql';
  mysql> source /tmp/a.sql

5. InnoDB 存储引擎的体系结构 ******

5.1 磁盘结构 (on-disk)

5.1.1 表空间结构

介绍: 表空间的概念源于Oracle数据库。最初的目的是为了能够很好的做存储的扩容。

a. 共享(系统)表空间

存储方式
ibdata1~ibdataN, 5.5版本默认的表空间类型 。

ibdata1共享表空间在各个版本的变化
5.5版本:
系统相关:(全局)数据字典信息(表基本结构信息、状态、系统参数、属性..)、UNDO回滚日志(记录撤销操作)、Double Write Buffer信息、临时表信息、change buffer
用户数据: 表数据行、表的索引数据
5.6版本:共享表空间只存储于系统数据,把用户数据独立了,独立表空间管理。
系统相关:(全局)数据字典信息、UNDO回滚信息、Double Write信息、临时表信息、change buffer
5.7版本:在5.6基础上,把临时表独立出来,UNDO也可以设定为独立
系统相关:(全局)数据字典信息、UNDO回滚信息、Double Write信息、change buffer
8.0.11~8.0.19版本:
在5.7的基础上将UNDO回滚信息默认独立,数据字典不再集中存储了。
系统相关:Double Write信息、change buffer
8.0.20版本:在之前版本基础上,独立 Double Write信息
系统相关:change buffer
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

总结: 对于InnoDB表来讲,例如 city表
city.ibd
city.frm
ibdata1
只是通过cp备份ibd和frm文件无法实现,数据表的恢复

 
ibdata1.png

共享表空间管理

扩容共享表空间
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
1 row in set (0.00 sec)

参数用途:ibdata1文件,默认初始大小12M,不够用会自动扩展,默认每次扩展64M

设置方式:  
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend


重启数据库报错,查看日志文件
vim /data/3306/data/db01.err
###################
[ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 4864 pages (rounded down to MB) than the 768 pages specified in the .cnf file!
###################

实际大小:
4864*16K/1024=76M 

my.cnf文件设置:
768*16K/1024=12M


正确的方法: 
先查看实际大小:
[root@db01 data]# ls -lh ibdata1 
-rw-r----- 1 mysql mysql 76M May  6 17:11 ibdata1


配置文件设定为和实际大小一致: 
innodb_data_file_path=ibdata1:76M;ibdata2:100M;ibdata3:100M:autoextend

模拟在初始化时设置共享表空间(生产建议)
5.7 中建议:设置共享表空间2-3个,大小建议1G或者4G,最后一个定制为自动扩展。
8.0 中建议:设置1-2个就ok,大小建议1-4G

# 清理数据
[root@db01 data]# /etc/init.d/mysqld stop
[root@db01 data]# rm -rf /data/3306/data/*

[root@db01 data]# vim /etc/my.cnf
# 修改 
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend

# 重新初始化
[root@db01 data]# mysqld --initialize-insecure   --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

# 重启数据库生效
[root@db01 data]# /etc/init.d/mysqld start

b.独立表空间(数据+索引)

介绍

5.6版本开始,针对用户数据,单独的存储管理。存储表的数据行和索引。
8.0 之前: city ---> city.ibd city.frm ibdata1
8.0 之后 city city.ibd
补充:
myisam 表 t1
t1.frm 数据字典
t1.myd 数据行
t1.myi 索引

通过参数控制: 
mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+

测试: 共享表空间存储用户数据
mysql> set global innodb_file_per_table=0;

利用独立表空间进行快速数据迁移

源端:3306/test/t100w  -----> 目标端:3307/test/t100w


1. 锁定源端t100w表 
mysql> flush tables  test.t100w with read lock ;

mysql> show create table test.t100w;
CREATE TABLE `t100w` (
  `id` int(11) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



2. 目标端创建test库和t100w空表
mysql> create database test charset=utf8mb4;
CREATE TABLE `t100w` (
  `id` int(11) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.  单独删除空的表空间文件
mysql> alter table test.t100w discard tablespace;



4. 拷贝源端ibd文件到目标端目录,并设置权限

[root@db01 test]# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/
[root@db01 test]# chown -R mysql.mysql /data/*

5. 导入表空间
mysql> alter table test.t100w import tablespace;
mysql> select count(*) from test.t100w;
+----------+
| count(*) |
+----------+
|  1000000 |

6. 解锁源端数据表
mysql> unlock tables;

彩蛋:案例1: 利用表空间迁移功能实现数据损坏恢复。

案例背景:
硬件及软件环境:
联想服务器(IBM) ,8核16G
磁盘500G 没有raid
centos 6.8
mysql 5.6.33 innodb引擎 独立表空间
备份没有,日志也没开
开发自用专用库:
jira(bug追踪) 、 confluence(内部知识库) ------>LNMT

故障描述:
断电了,启动完成后根只读
fsck 重启,系统成功启动,mysql启动不了。
结果:confulence库在, jira库不见了。

学员求助内容:
求助:
这种情况怎么恢复?
我问:
有备份没
求助:
连二进制日志都没有,没有备份,没有主从
我说:
没招了,jira需要硬盘恢复了。
求助:
1、jira问题拉倒中关村了
2、能不能暂时把confulence库先打开用着
将生产库confulence,拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的
问: 有没有工具能直接读取ibd
我说:我查查,最后发现没有

我想出一个办法来:
表空间迁移:
create table xxx
alter table confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;
虚拟机测试可行。

处理问题思路:
confulence库中一共有107张表。

1、创建107和和原来一模一样的表。

他有2016年的历史库,我让他去他同时电脑上 mysqldump备份confulence库
mysqldump -uroot -ppassw0rd -B confulence --no-data >test.sql
################
万一是自研数据库,怎么办?又没备份,那怎么办?
mysql工具包,mysqlfrm 读取frm文件获得表结构。
################

2、表空间删除。

select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
source /tmp/discard.sql

执行过程中发现,有20-30个表无法成功。
主外键关系:很绝望,一个表一个表分析表结构,很痛苦,set foreign_key_checks=0 跳过外键检查。

3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中

select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/import.sql';

4、验证数据

表都可以访问了,数据挽回到了出现问题时刻的状态

课后练习作业:
案例2 : MySQL 5.7 中误删除了ibdata1文件,导致数据无法启动,如何恢复t100w,假设一共100张表,表结构无法通过show create table 获得。

提示:万一是自研数据库,怎么办?又没备份,那怎么办?
mysql工具包,mysqlfrm 读取frm文件获得表结构。
./mysqlfrm /data/3306/data/test/t100w.frm --diagnostic
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
source /tmp/discard.sql
################

undo表空间

1. 作用: 用来作撤销工作。
2. 存储位置: 5.7版本,默认存储在共享表空间中(ibdataN)。8.0版本以后默认就是独立的(undo_001-undo_002)。
3. 生产建议: 5.7版本后,将undo手工进行独立。


4. undo 表空间管理 
4.1 如何查看undo的配置参数
SELECT @@innodb_undo_tablespaces;  ---->3-5个    #打开独立undo模式,并设置undo的个数。
SELECT @@innodb_max_undo_log_size;               #undo日志的大小,默认1G。
SELECT @@innodb_undo_log_truncate;               #开启undo自动回收的机制(undo_purge)。
SELECT @@innodb_purge_rseg_truncate_frequency;   #触发自动回收的条件,单位是检测次数。




4.2 配置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.
#################################

[root@db01 tmp]# pkill mysqld
[root@db01 tmp]# rm -rf /data/3357/data/*

vim /etc/my.cnf
# 添加参数
innodb_undo_tablespaces=3           
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32

# 重新初始化数据库生效=
[root@db01 data]# /usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf  --initialize-insecure   --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/3357/data

# 启动数据库 
[root@db01 data]# /etc/init.d/mysqld start
[root@db01 data]# ll /data/3306/data/undo00*
-rw-r----- 1 mysql mysql 10485760 May  7 15:39 /data/3306/data/undo001
-rw-r----- 1 mysql mysql 10485760 May  7 15:39 /data/3306/data/undo002
-rw-r----- 1 mysql mysql 10485760 May  7 15:39 /data/3306/data/undo003

#如果进行undo独立存储到其他文件系统
a. 关闭数据库:
[root@db01 data]# systemctl start mysqld3357

b.设定路径参数 
innodb_undo_directory=/data/3357/undologs 
c. 创建目录,并拷贝文件

[root@db01 data]# systemctl stop mysqld3357



mkdir -p  /data/3357/undologs 
chown -R mysql. /data/* 
cp -a /data/3357/data/undo* /data/3357/undologs 

# 注: 8.0 undo表空间与5.7稍有区别,可参考:
https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

临时表空间

1. 作用: 存储临时表。
2. 管理:
innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:128M:autoextend:max:500M
重启生效。
3. 建议数据初始化之前设定好,一般2-3个,大小512M-1G。

5.1.2 InnoDB 事务日志

redo log 重做日志

1. 作用: 记录内存数据页的变化。实现“前进”的功能。WAL(write ahead log),MySQL保证redo优先于数据写入磁盘。
2. 存储位置: 数据路径下,进行轮序覆盖记录日志
ib_logfile0  48M
ib_logfile1  48M
3. 管理:
3.1 查询redo log文件配置
mysql> show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
3.2 设置
生产建议: 
    大小: 512M-4G
    组数: 2-4组
vim  /etc/my.cnf 
# 添加参数:
innodb_log_file_size=100M
innodb_log_files_in_group=3

#重启生效 
[root@db01 data]# /etc/init.d/mysqld restart
[root@db01 data]# ll /data/3306/data/ib_logfile*
-rw-r----- 1 mysql mysql 104857600 May  7 16:17 /data/3306/data/ib_logfile0
-rw-r----- 1 mysql mysql 104857600 May  7 16:17 /data/3306/data/ib_logfile1
-rw-r----- 1 mysql mysql 104857600 May  7 16:17 /data/3306/data/ib_logfile2
[root@db01 data]# 

undo log 回滚日志

略。

5.1.3 其他结构

ib_buffer_pool 预热文件
作用:
缓冲和缓存,用来做“热”(经常查询或修改)数据页,减少物理IO。
当关闭数据库的时候,缓冲和缓存会失效。
5.7版本中,MySQL正常关闭时,会将内存的热数据存放(流方式)至ib_buffer_pool。下次重启直接读取ib_buffer_pool加载到内存中。

Double Write Buffer(DWB)
(8.0.19之前 默认在ibdataN中,8.0.20以后可以独立了。)
作用:
MySQL,最小IO单元page(16KB),OS中最小的IO单元是block(4KB)
为了防止出现以下问题:
mysqld process crash in the middle of a page write

5.2 内存结构

5.2.1 InnoDB BUFFER POOL(IBP)

作用:
用来缓冲、缓存,MySQL的数据页和索引页。MySQL中最大的、最重要的内存区域。

管理:
mysql> select @@innodb_buffer_pool_size;
默认大小: 128M
生产建议: 物理内存的:50-80%。
在线设置:
mysql> set global innodb_buffer_pool_size=268435456;
重新登录mysql生效。
永久设置:
vim /etc/my.cnf

添加参数

innodb_buffer_pool_size=256M
重启生效

5.2.2 InnoDB LOG BUFFER (ILB)

作用: 用来缓冲 redo log日志信息。
管理 :
mysql> select @@innodb_log_buffer_size;
默认大小:16M
生产建议:和innodb_log_file_size有关,1-N倍
设置方式 :
vim /etc/my.cnf
innodb_log_buffer_size=33554432
重启生效:
[root@db01 data]# /etc/init.d/mysqld restart

6. InnoDB核心特性--事务支持

6.1 介绍

事务:Transaction (交易)。 伴随着交易类的业务出现的概念(工作模式)
交易?
物换物,等价交换。
货币换物,等价交换。
虚拟货币换物(虚拟物品),等价交换。
现实生活中怎么保证交易“和谐” ,法律、道德等规则约束。
数据库中为了保证线上交易的“和谐”,加入了“事务”工作机制。

6.2 事务ACID特性

A: 原子性
一个事务生命周期中的DML语句,要么全成功要么全失败,不可以出现中间状态。

begin;DML1;DML2;DML3;commit;

C:一致性
事务发生前,中,后,数据都最终保持一致。
CR + double write

I:隔离性
事务操作数据行的时候,不会受到其他时候的影响。

D: 持久性
一但事务提交,永久生效(落盘)。

6.3 事务的生命周期管理

6.3.1 标准(显示)的事务控制语句

# 开启事务
begin; 
# 提交事务
commit;
# 回滚事务
rollback;

注意:
事务生命周期中,只能使用DML语句(select、update、delete、insert)

# 事务的生命周期演示:
mysql> use world
mysql> begin;
mysql> delete from city where id=1;
mysql> update city set countrycode='CHN' where id=2;
mysql> commit;


mysql> begin;
mysql> select * from city limit 10;

mysql> update city set countrycode='AFG' where id=2;

mysql> delete from city where id=3;
mysql> rollback;

6.3.2 MySQL的自动提交机制(autocommit)

参数: 
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
作用: 
在没有显示的使用begin语句的时候,执行DML,会在DML前自动添加begin,并在DML执行后自动添加commit。
建议: 频繁事务业务场景中,关闭autocommit,或者每次事务执行时都是显示的begin和commit;
关闭方法: 
# 临时: 
mysql> set global autocommit=0;
退出会话,重新连接配置生效。

# 永久: 
[root@db01 ~]# vim /etc/my.cnf
autocommit=0

重启生效。

6.3.3 隐式提交和回滚

begin;
DML1;
DML2;
DML3;
commit;


# 隐式提交情况 
begin 
a
b

SET AUTOCOMMIT = 1
导致提交的非事务语句:
DDL语句: (ALTER、CREATE 和 DROP)
DCL语句: (GRANT、REVOKE 和 SET PASSWORD)
锁定语句:(LOCK TABLES 和 UNLOCK TABLES)

导致隐式提交的语句示例:
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

# 隐式回滚 

会话窗口被关闭。
数据库关闭 。
出现事务冲突(死锁)。

6.4 事务的隔离级别

6.4.1 作用

实现事务工作期间的“读”的隔离
读? ----》 数据页的读

6.4.2 级别类型

mysql> select @@transaction_isolation;

# RU : READ-UNCOMMITTED 读未提交
可以读取到事务未提交的数据。隔离性差,会出现脏读(当前内存读),不可重复读,幻读问题

# RC : READ-COMMITTED   读已提交(可以用):
可以读取到事务已提交的数据。隔离性一般,不会出现脏读问题,但是会出现不可重复读,幻读问题
    
# RR : REPEATABLE-READ  可重复读(默认)  :
防止脏读(当前内存读),防止不可重复读,会出现幻读问题


# SR : SERIALIZABLE     可串行化
结论: 隔离性越高,事务的并发度就越差。

6.4.3 脏读

设置隔离级别为:'READ-UNCOMMITTED'
mysql> set global transaction_isolation='READ-UNCOMMITTED';
mysql> set global autocommit=0;

重新开启两个session;
mysql> select @@transaction_isolation;
mysql> select @@autocommit;

创建测试表
create table t1 (
id int not null primary key auto_increment ,
a  int not null ,
b  varchar(20) not null, 
c  varchar(20) not null 
)charset=utf8mb4 engine=innodb;

begin;
insert into t1(a,b,c) 
values
(5,'a','aa'),
(7,'c','ab'),
(10,'d','ae'),
(13,'g','ag'),
(14,'h','at'),
(16,'i','au'),
(20,'j','av'),
(22,'k','aw'),
(25,'l','ax'),
(27,'o','ay'),
(31,'p','az'),
(50,'x','aza'),
(60,'y','azb');
commit;

6.4.3 不可重复读

mysql> set global transaction_isolation='READ-COMMITTED';

mysql>  select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |

6.4.3 幻读

mysql>  select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |

6.5 事务的工作流程原理(ACID特性如何保证)

6.5.1 名词介绍

#  重做日志 (redo log)
ib_logfile0~N   48M   , 轮询使用

# 日志缓冲区
redo log buffer : redo内存区域

# 表空间数据文件
ibd  : 存储数据行和索引 

# 数据缓冲区
InnoDB buffer pool : 缓冲区池,数据和索引的缓冲

# 日志序列号 
LSN
磁盘数据页(ibd文件的page),redo log文件(ib_logfile),Innodb_buffer_pool中的数据页,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动

#WAL : Write Ahead Log
Redo日志优先于数据页写到磁盘。

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


# CheckPoint
CKPT:检查点,就是将脏页刷写到磁盘的动作


# DB_TRX_ID(6字节)  事务ID号
InnoDB会为每一个事务生成一个事务号,伴随着整个事务生命周期.


# DB_ROLL_PTR(7字节) 回滚指针
rollback 时,会使用 undo 日志回滚已修改的数据。DB_ROLL_PTR指向了此次事务的回滚位置点,用来找到undo日志信息。

6.5.2 事务工作流程原理

事务举例: 
begin; 
update t1 set A=2 where A=1;
commit;

# redo log 重做日志如何应用
1. 用户发起update事务语句,将磁盘数据页(page100,A=1,LSN=1000)加载到内存(buffer_pool)缓冲区。
2. 在内存中发生数据页修改(A=1改成A=2),形成脏页,更改中数据页的变化,记录到redo buffer中,加入1000个字节日志。LSN=1000+1000=2000。
3. 当commit语句执行时,基于WAL机制,等到redo buffer中的日志完全落盘到ib_logfileN中,commit正式完成。
4. ib_logfileN中记录了一条日志。内容:page100数据页变化+LSN=2000。

## 情景: 当此时,redo落地了,数据页没有落地,宕机了。
1. MySQL CR(自动故障恢复)工作模式,启动数据库时,自动检查redo的LSN和数据页LSN。
2. 如果发现redoLSN>数据页的LSN ,加载原始数据页+变化redo指定内存。使用redo重构脏页(前滚)。
3. 如果确认此次事务已经提交(commit标签),立即触发CKPT动作,将脏页刷写到磁盘上。


## 补充一点: 
    MySQL有一种机制,批量刷写redo的机制。会在A事务commit时,顺便将redo buffer中的未提交的redo日志也一并刷到磁盘。
    为了区分不同状态的redo,日志记录时,会标记是否COMMIT。


## redo保证了ACID哪些特性?
主要是D的特性,另外A、C也有间接关联。


# undo log 回滚日志如何应用?
1. 事务发生数据页修改之前,会申请一个undo事务操作,保存事务回滚日志(逆向操作的逻辑日志)。
2. undo写完之后,事务修改数据页头部(会记录DB_TRX_ID+DB_ROLL_PTR),这个信息也会被记录的redo。

情景1: 
当执行rollback命令时。根据数据页的DB_TRX_ID+DB_ROLL_PTR信息,找到undo日志,进行回滚。

情景2:
    begin; 
    update t1 set A=2 where A=1;
    宕机。

假设: undo 有  , redo没有
启动数据库时,检查redo和数据页的LSN号码。发现是一致的。
所以不需要进行redo的前滚,此时也不需要回滚。undo信息直接被标记为可覆盖状态。

假设:undo 有,redo也有(没有commit标签。)
1. MySQL CR(自动故障恢复)工作模式,启动数据库时,自动检查redo的LSN和数据页LSN。
2. 如果发现redoLSN>数据页的LSN ,加载原始数据页+变化redo指定内存。使用redo重构脏页(前滚)。
3. 如果确认此次事务没有commit标记,立即触发回滚操作,根据DB_TRX_ID+DB_ROLL_PTR信息,找到und回滚日志,实现回滚。
以上流程被称之为InnoDB的核心特性:自动故障恢复(Crash Recovery)。先前滚再回滚,先应用redo再应用undo。


## undo在ACID中保证了啥?
主要保证事务的A的特性,同时C和I的特性也有关系。

6.5.3 事务中的C特性怎么保证?

InnoDB crash recovery:数据库意外宕机时刻,通过redo前滚+undo回滚保证数据的最终一致。
InnoDB doublewrite buffer: 默认存储在ibdataN中。解决数据页写入不完整
mysqld process crash in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
DWB一共2M。分两次,每次1M写入

6.5.4 事务中的I的特性怎么保证?

# 隔离级别:读隔离性
    RU       : 脏读 、 不可重复读 、幻读
    RC       : 不可重复读、幻读
    RR       :有可能会出现幻读。
    SR(SE) :事务串行工作。

# 锁机制:写的隔离
    作用:保护并发访问资源。
保护的资源分类: 
    latch(闩锁):rwlock、mutex,主要保护内存资源
    MDL: Metadata_lock,元数据(DDL操作)
    table_lock: 表级别 
                lock table t1 read ;
                mysqldump、XBK(PBK):备份非InnoDB数据时,触发FTWRL全局锁表(Global)。
                行锁升级为表锁。
    row lock:InnoDB 默认锁粒度,加锁方式都是在索引加锁的。
            record lock : 记录锁,在聚簇索引锁定。RC级别只有record lock。
            gap lock    : 间隙锁,在辅助索引间隙加锁。RR级别存在。防止幻读。
            next lock   : 下一键锁, GAP+Record。     RR级别存在。防止幻读。

什么是幻读,RR又是如何防止幻读?

RC级别下不可重读现象演示:  
vim /etc/my.cnf 
#添加隔离级别参数:
transaction_isolation=READ-COMMITTED
#重启数据库 
[root@db01 ~]# /etc/init.d/mysqld restart

打开两个会话窗口: 

sessionA: 
第一步:
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)


第三步: 
mysql> create database test charset utf8mb4;
mysql> use test;
mysql> create table t1 (id int primary key auto_increment,num int not null , name varchar(20) not null);
mysql> insert into t1(num,name) values(1,'a'),(3,'c'),(6,'d'),(7,'x');
mysql> insert into t1(num,name) values(11,'a'),(23,'c'),(36,'d'),(37,'x'');
mysql> insert into t1(num,name) values(51,'as'),(63,'hc'),(76,'ds'),(87,'x','xyz');
mysql> commit;
mysql> select * from t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | a    |
|  2 |   3 | c    |
|  3 |   6 | d    |
|  4 |   7 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
|  9 |  51 | as   |
| 10 |  63 | hc   |
| 11 |  76 | ds   |
| 12 |  87 | xyz  |
+----+-----+------+

第五步: 
mysql> begin;


第七步: 
mysql> update t1 set name='aa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

=================================================
sessinB: 
第二步:
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

第四步: 
mysql> use test;

mysql> select * from test.t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | a    |
|  2 |   3 | c    |
|  3 |   6 | d    |
|  4 |   7 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
|  9 |  51 | as   |
| 10 |  63 | hc   |
| 11 |  76 | ds   |
| 12 |  87 | xyz  |
+----+-----+------+
12 rows in set (0.00 sec)

第六步: 
mysql> begin;
mysql> select * from t1 where id=1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | a    |
+----+-----+------+
1 row in set (0.00 sec)

第八步: 
mysql> select * from t1 where id=1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | aa   |
+----+-----+------+
1 row in set (0.00 sec)


==================================
RC级别下幻读现象演示:  

准备工作: 
mysql> alter table t1 add index(num);
[root@db01 ~]# mysqldump  test t1 >/tmp/t1.sql

session A : 

第一步: 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | aa   |
|  2 |   3 | c    |
|  3 |   6 | d    |
|  4 |   7 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
|  9 |  51 | as   |
| 10 |  63 | hc   |
| 11 |  76 | ds   |
| 12 |  87 | xyz  |
+----+-----+------+
12 rows in set (0.00 sec)


第三步: 
mysql> update t1 set num=10 where num<10;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

第五步: 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |  10 | aa   |
|  2 |  10 | c    |
|  3 |  10 | d    |
|  4 |  10 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
|  9 |  51 | as   |
| 10 |  63 | hc   |
| 11 |  76 | ds   |
| 12 |  87 | xyz  |
| 13 |   5 | aaa  |
+----+-----+------+


Session B: 

第二步: 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | aa   |
|  2 |   3 | c    |
|  3 |   6 | d    |
|  4 |   7 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
|  9 |  51 | as   |
| 10 |  63 | hc   |
| 11 |  76 | ds   |
| 12 |  87 | xyz  |
+----+-----+------+
12 rows in set (0.00 sec)

第四步: 
mysql> insert into t1(num,name) values(5,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)


# 功能性上: 
    IS   :                          select * from t1 lock in shared mode;
    S    : 读锁。             
    IX   :  意向排他锁。表上添加的。 select * from t1 for update;
    X    :  排他锁,写锁。


# MVCC : 多版本并发控制
乐观锁: 乐观。
悲观锁: 悲观。

每个事务操作都要经历两个阶段: 

读:  乐观锁。
MVCC利用乐观锁机制,实现非锁定读取。
read view:RV,版本号集合。
trx1 : 
begin; 
dml1  ---> 在做第一个查询的时候,当前事务,获取系统最新的:RV1 版本快照。
dml2  ---> 生成 RV2 版本快照。
select  查询  RV2 快照数据
commit; ---->  RV2 快照数据  ----》系统最新快照。

RC
trx1: Rv1  Rv2  commit;
trx2 RVV1 RVV1 RV2

RR 
trx1 : 第一个查询时, 生成global consitence snapshot  RV-CS1(10:00) ,一直伴随着事务生命周期结束。
trx2 :  第一个查询时,生成global consitence snapshot  RV-CS2(10:01) ,一直伴随着事务生命周期结束。

快照技术由undo log来提供。

写: 悲观锁 X 

总结: 
1. MVCC采用乐观锁机制,实现非锁定读取。
2. 在RC级别下,事务中可以立即读取到其他事务commit过的readview
3. 在RR级别下,事务中从第一次查询开始,生成一个一致性readview,直到事务结束。

与[转帖]第五章 MySQL 存储引擎相似的内容:

[转帖]第五章 MySQL 存储引擎

第五章 MySQL 存储引擎 https://www.jianshu.com/p/d12191803f17 本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。只要掌握80%,轻松助力薪资15k-25K。课程内容均来自与MySQL官网+MySQL源码。配套精品视频(2021

[转帖]深入理解mysql-第六章 mysql存储引擎InnoDB的索引-B+树索引

一、引入索引 在没有索引的情况下,不论是根据主键列或者其他列的值进行查找,由于我们并不能快速的定位到记录所在的页,所以只能从第一个页沿着双向链表一直往下找,因为要遍历所有的数据页,时间复杂度就是O(n),所以这种方式显然是超级耗时的。所以我们需要采取一定的数据结构来存储数据,方便我们进行数据的增删改

[转帖]深入理解mysql-第五章 InnoDB记录存储结构-页结构

前言: 页是InnoDB管理存储空间的基本单位,上一章我们主要分析了页中的主要的构成行的存储结构-行格式,其中简单提了一下页的概念。这章我们详细讲解一下页的存储结构。 一、数据页结构 前边我们简单提了一下页的概念,它是InnoDB管理存储空间的基本单位,一个页的大小一般是16KB。和存储一条条数据的

[转帖]第一章 MySQL 8.0 介绍及安装配置

第一章 MySQL 8.0 介绍及安装配置 https://www.jianshu.com/p/d190c6b3520d 本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。只要掌握80%,轻松助力薪资15k-25K。课程内容均来自与MySQL官网+MySQL源码。配套精品

[转帖]第二章 MySQL的体系结构与基础管理

第二章 MySQL的体系结构与基础管理 https://www.jianshu.com/p/6d017ac5e685 本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。只要掌握80%,轻松助力薪资15k-25K。课程内容均来自与MySQL官网+MySQL源码。配套精品视频

[转帖]深入理解mysql-第十章 mysql查询优化-Explain 详解(上)

目录 一、初识Explain 二、执行计划-table属性 三、执行计划-id属性 四、执行计划-select_type属性 一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划,这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,对于每个表采

[转帖]第四章节 索引及执行计划

第四章节 索引及执行计划 https://www.jianshu.com/p/fdd3c5e815e9 本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。只要掌握80%,轻松助力薪资15k-25K。课程内容均来自与MySQL官网+MySQL源码。配套精品视频(2021 5

[转帖]第六章 日志管理

第六章 日志管理 https://www.jianshu.com/p/e0d95da89fee 本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。只要掌握80%,轻松助力薪资15k-25K。课程内容均来自与MySQL官网+MySQL源码。配套精品视频(2021 5月全新录

[转帖]第七章 备份恢复

第七章 备份恢复 https://www.jianshu.com/p/e62d83516e75 本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。只要掌握80%,轻松助力薪资15k-25K。课程内容均来自与MySQL官网+MySQL源码。配套精品视频(2021 5月全新录

[转帖]第三章 SQL 基础及元数据获取

第三章 SQL 基础及元数据获取 https://www.jianshu.com/p/f07416c419da 本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。只要掌握80%,轻松助力薪资15k-25K。课程内容均来自与MySQL官网+MySQL源码。配套精品视频(20