第五章 MySQL 存储引擎

第五章 MySQL 存储引擎

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 @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)


vim /etc/my.cnf  
default_storage_engine=InnoDB

重启生效。

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文件无法实现,数据表的恢复

img

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语句: (ALTERCREATEDROPDCL语句: (GRANTREVOKESET PASSWORD)
锁定语句:(LOCK TABLESUNLOCK 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=20003. 当commit语句执行时,基于WAL机制,等到redo buffer中的日志完全落盘到ib_logfileN中,commit正式完成。
4. ib_logfileN中记录了一条日志。内容:page100数据页变化+LSN=2000## 情景: 当此时,redo落地了,数据页没有落地,宕机了。
1. MySQL CR(自动故障恢复)工作模式,启动数据库时,自动检查redoLSN和数据页LSN2. 如果发现redoLSN>数据页的LSN ,加载原始数据页+变化redo指定内存。使用redo重构脏页(前滚)。
3. 如果确认此次事务已经提交(commit标签),立即触发CKPT动作,将脏页刷写到磁盘上。


## 补充一点: 
    MySQL有一种机制,批量刷写redo的机制。会在A事务commit时,顺便将redo buffer中的未提交的redo日志也一并刷到磁盘。
    为了区分不同状态的redo,日志记录时,会标记是否COMMIT## redo保证了ACID哪些特性?
主要是D的特性,另外AC也有间接关联。


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

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

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

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

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


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

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       :有可能会出现幻读。
    SRSE) :事务串行工作。

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

什么是幻读,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-CS210:01) ,一直伴随着事务生命周期结束。

快照技术由undo log来提供。

写: 悲观锁 X 

总结: 
1. MVCC采用乐观锁机制,实现非锁定读取。
2.RC级别下,事务中可以立即读取到其他事务commit过的readview
3.RR级别下,事务中从第一次查询开始,生成一个一致性readview,直到事务结束。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值