存储引擎

六 存储引擎

1. 什么是存储引擎

相当于MYSQL内置的文件系统。与Linux中的文件系统打交道的层次结构。

2. MYSQL存储引擎种类

2.1 官方：Oracle MYSQL

可以给不同的表，设定不同的存储引擎。

列举MYSQL中支持存储引擎的种类？

InnoDB :MYSQL5.5版本以后默认的存储引擎。99%以上的业务表都是它。

MyISAM :

CSV :

MEMORY :

2.2 其他分支

percona :XtraDB

MariaDb :InnoDB

其他的引擎:

TokuDB,MyRocks

Compression:25x for high compression 优点：压缩比高（也就是说节省空间）。

Fast Insertions(插入) and Deletions(删除) 优点：插入删除效能高

适合于，业务当中有大量插入或者删除操作的场景。

应用于，数据量较大的业务。

 

3.InnoDB 核心特性

3.1 介绍

MVCC ：Muti-Version Concurrency Control多版本并发控制。

Clustered index:聚簇索引

多缓冲区池

事物

行级锁

外键

更多复制特性

支持热备

自动故障恢复

Change buffer：修改缓冲区

自适应hash索引:AHI(adpater hash index)

自动故障恢复

3.2 存储引擎的管理命令

3.2.1 使用SELECT 确认会话存储引擎

SELECT @@default_storage_engine;

3.2.2 存储引擎（不代表生产操作）

会话级别：

SET default_storage_engine=MYISAM;

SET default_storage_engine=INNODB;

全局级别（仅影响新会话）：

SET GLOBAL default_storage_engine=MYISAM

重启之后，所有参数均失败。

如果要永久生效：

写入配置文件：

vi /etv/my.cnf

[mysqld]

default_storage_engine=MYISAM

存储引擎是表记表级别的，每个表创建时可以指定不同的存储引擎，但我们建议统一为InnoDB.

3.2.3 show确认每个表的存储引擎；

SHOW CREATE TABLE sc\G;

\G分行。

SHOW TABLE STATUS LIKE 'sc'\G

3.2.4 确认每个表的存储引擎

SELECT table_schema,table_name,ENGINE

FROM information_schema.tables

WHERE table_schema NOT IN('sys','mysql','information_schema','performance_schema');

SHOW STATUS;

3.2.4 修改一个表的存储引擎

USE school;

ALTER TABLE sc ENGINE=MYISAM;

SHOW CREATE TABLE sc;

ALTER TABLE sc ENGINE=InnoDB;

注意:此命令我们经常使用他，进行innodb表的碎片整理。（经常的删除数据就会产生碎片，影响性能）。

USE information_schema;

[SELECT TABLE_SCHEMA,

TABLE_NAME,

TABLE_ROWS,

DATA_LENGTH,

INDEX_LENGTH,

CONCAT(ROUND(DATA_FREE / 1024 / 1024, 2), 'M') AS datafree

FROM information_schema.tables t

WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','mysql','performance_schema');]==肯定会经常用到。

3.2.5 平常处理过的Mysql问题--碎片处理（ALTER TABLE sc ENGINE=InnoDB;）

环境：Centos7.4 MySQL 5.7.26，InnoDB存储引擎。

业务特点：数据量级较大，经常需要按月删除历史数据。

问题：磁盘空间占用很大，不释放。

处理方法：

以前：将数据逻辑导出，手工drop表，然后倒入进去。

现在：

对表进行按月进行分表（partition,中间件）或者归档表（pt-archive）

业务替换为truncate

3.2.6 拓展：如何批量修改

需求1：将zabbix库中的所有表，innoDB替换为tokudb

SELECT CONCAT("ALTER TABLE school.",table_name," ","ENGINE=CSV;")

FROM information_schema.tables

WHERE table_schema='school' INTO OUTFILE '/tmp/toku.sql';

改回去

SELECT CONCAT("ALTER TABLE school.",table_name," ","ENGINE=InnoDB;")

FROM information_schema.tables

WHERE table_schema='school' INTO OUTFILE '/tmp/toku.sql';

需求2 将所有非InnoDB业务表查询出来，并修改为InnoDB

SELECT CONCAT("alter table ",table_schema,".",table_name,"engine=innodb;")

FROM information_schema.tables

WHERE ENGINE != 'InnoDB'

AND table_schema

NOT IN ('sys','information_schema','mysql','performance_schema')

INTO OUTFILE '/tmp/alter.sql';

4.MYSQL 存储引擎体系结构

4.1 宏观结构

4.1.1 MyISAM

myt.frm ： 数据字典信息（列的定义和属性）

myt.MYD ： 数据行

myt.MYI ： 索引

4.1.2 InnoDB

city.frm: 数据字典信息（列的定义和属性）

city.ibd: 数据行和索引

ibdata1 : 数据字典信息，UNDO，double write 磁盘区域，change buffer 磁盘区域。

说明：不同版本ibdata1中存储的数据不一样。

ibdata1：

5.5V: ibdata1 中还会存储临时表数据 + 用户数据（数据行+索引）

3.InnoDB 核心特性

3.1 介绍

MVCC ：Muti-Version Concurrency Control多版本并发控制。

Clustered index:聚簇索引

多缓冲区池

事物

行级锁

外键

更多复制特性

支持热备

自动故障恢复

Change buffer：修改缓冲区

自适应hash索引:AHI(adpater hash index)

自动故障恢复

3.2 存储引擎的管理命令

3.2.1 使用SELECT 确认会话存储引擎

SELECT @@default_storage_engine;

3.2.2 存储引擎（不代表生产操作）

会话级别：

SET default_storage_engine=MYISAM;

SET default_storage_engine=INNODB;

全局级别（仅影响新会话）：

SET GLOBAL default_storage_engine=MYISAM

重启之后，所有参数均失败。

如果要永久生效：

写入配置文件：

vi /etv/my.cnf

[mysqld]

default_storage_engine=MYISAM

存储引擎是表记表级别的，每个表创建时可以指定不同的存储引擎，但我们建议统一为InnoDB.

3.2.3 show确认每个表的存储引擎；

SHOW CREATE TABLE sc\G;

\G分行。

SHOW TABLE STATUS LIKE 'sc'\G

3.2.4 确认每个表的存储引擎

SELECT table_schema,table_name,ENGINE

FROM information_schema.tables

WHERE table_schema NOT IN('sys','mysql','information_schema','performance_schema');

SHOW STATUS;

3.2.4 修改一个表的存储引擎

USE school;

ALTER TABLE sc ENGINE=MYISAM;

SHOW CREATE TABLE sc;

ALTER TABLE sc ENGINE=InnoDB;

注意:此命令我们经常使用他，进行innodb表的碎片整理。（经常的删除数据就会产生碎片，影响性能）。

USE information_schema;

[SELECT TABLE_SCHEMA,

TABLE_NAME,

TABLE_ROWS,

DATA_LENGTH,

INDEX_LENGTH,

CONCAT(ROUND(DATA_FREE / 1024 / 1024, 2), 'M') AS datafree

FROM information_schema.tables t

WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','mysql','performance_schema');]==肯定会经常用到。

3.2.5 平常处理过的Mysql问题--碎片处理（ALTER TABLE sc ENGINE=InnoDB;）

环境：Centos7.4 MySQL 5.7.26，InnoDB存储引擎。

业务特点：数据量级较大，经常需要按月删除历史数据。

问题：磁盘空间占用很大，不释放。

处理方法：

以前：将数据逻辑导出，手工drop表，然后倒入进去。

现在：

对表进行按月进行分表（partition,中间件）或者归档表（pt-archive）

业务替换为truncate

3.2.6 拓展：如何批量修改

需求1：将zabbix库中的所有表，innoDB替换为tokudb

SELECT CONCAT("ALTER TABLE school.",table_name," ","ENGINE=CSV;")

FROM information_schema.tables

WHERE table_schema='school' INTO OUTFILE '/tmp/toku.sql';

改回去

SELECT CONCAT("ALTER TABLE school.",table_name," ","ENGINE=InnoDB;")

FROM information_schema.tables

WHERE table_schema='school' INTO OUTFILE '/tmp/toku.sql';

需求2 将所有非InnoDB业务表查询出来，并修改为InnoDB

SELECT CONCAT("alter table ",table_schema,".",table_name,"engine=innodb;")

FROM information_schema.tables

WHERE ENGINE != 'InnoDB'

AND table_schema

NOT IN ('sys','information_schema','mysql','performance_schema')

INTO OUTFILE '/tmp/alter.sql';

4.MYSQL 存储引擎体系结构

4.1 宏观结构

4.1.1 MyISAM

myt.frm ： 数据字典信息（列的定义和属性）

myt.MYD ： 数据行

myt.MYI ： 索引

4.1.2 InnoDB

city.frm: 数据字典信息（列的定义和属性）

city.ibd: 数据行和索引

ibdata1 : 数据字典信息，UNDO，double write 磁盘区域，change buffer 磁盘区域。

说明：不同版本ibdata1中存储的数据不一样。

ibdata1：

5.5V: ibdata1 中还会存储临时表数据 + 用户数据（数据行+索引）

5.6V: ibdata1 中还会存储临时表数据

8.0V: ibdata1 取消存储 数据字典信息，undo独立了。

可以理解为，MYSQL在慢慢瘦身ibdata1共享表空间，把比较关键的数据独立出来了。

UNDO （事物回滚日志）

ib_logfile0_logfileN : InnoDB 事物重做日志（redo log）

ibtmp1:临时表空间文件（排序，分组，多表连接，子查询，逻辑备份等。）

ib_buffer_pool:正常关库的时候，存储缓冲区的热数据。

所以：InnoDB的表，仅仅是拷贝ibd和frm文件到新的数据库，是无法正常使用。

4.2 InnoDB 微观结构

4.2.1 磁盘

1.什么是表空间？

表空间概念是引入于Oracle数据库。

起初为 MYSQL5.5 版本引入了共享表空间模式。

2.MYSQL表空间类型

The System Tablespace:共享表空间:在5.5版本引入了共享表空间（ibdata1），作为默认的存储方式。

File-Per-Table Tablespaces:独立表空间: 5.6 版本默认独立表空间模式。单表单表空间。

General Tablespaces:普通表空间:完全和Oracle一致的表空间管理模式

Undo Tablespaces:undo表空间:存储undo logs （回滚日志）

The Temporary Tablespace:临时表空间:存储临时表。5.7 默认独立。

3.表空间管理

用户数据默认的存储方式，独立表空间。独立表空间和共享表空间是可以互相切换的。

-- 查看默认表空间模式？

mysql>select @@innodb_file_per_table=0;

重新登录会话:

mysql>select @@innodb_file_per_table;

说明:修改完成之后，只影响新创建的表。

-- 如何切换？

临时：

mysql>set global innodb_file_per_table=0;

重新登录会话：

永久:

vim /etc/my.cnf

innodb_file_per_table=0

-- 验证

mysql>select @@innodb_file_per_table;

说明：修改完成之后，只影响新创建的表。

-- 如何扩展共享表空间大小和个数？

说明:通常是在初始化数据时，就设定好参数。

方法1:初始化之前，需要在my.cnf加入以配置即可:

innodb_data_file_path=ibdata1:1G;ibdata2:1G;autoextend

方法2:已运行的数据库上扩展多个ibdata文件

错误方式:

解决方法，在设置innodb_data_file_path参数时,已有的ibdata1文件大小应该和磁盘上真实大小一致，而不是随便指定的，正确的调整方式

innodb_data_file_path=ibdata1:76M;ibdata2:128M;ibdata3:128M :autoextend

事物日志 :

redo log 重做日志

（1）文件位置

/data/3306/ib_logfile0--ib_logfileN

(2) 控制参数:

innodb_log_file_size=50331648; #设置文件大小

innodb_log_files_group=2

#设置文件个数

innodb_log_group_home_dir=./设置存储位置。

（3）功能:用来存储，mysql在做修改类（DML）操作时的《数据页变化》过程及版本号（LSN），属于物理日志。

默认两个文件存储redo，是循环覆盖使用的。

undo logs 回滚日志

（1）文件位置：（5.7默认位置）

ibdataN

ibtmp

mysql> show variables like '%undo%';

+--------------------------+------------+

| Variable_name | Value |

+--------------------------+------------+

| innodb_max_undo_log_size | 1073741824 |

| innodb_undo_directory | ./ |

| innodb_undo_log_truncate | OFF |

| innodb_undo_logs | 128 |

| innodb_undo_tablespaces | 0 |

+--------------------------+------------+

（2）参数:

innodb_rollback_segments=128 #回滚段的个数

（3）功能

用来存储回滚日志，可以理解为记录了每次操作的反操作，属于逻辑日志。

# 使用快照功能 提供InnoDB多版本并发读写。

# 通过记录的反操作，提供回滚功能。（我的理解相当于ctrl + z）

HP UNIX最牛批的技术LVM。

5 内存

(1) 数据内存区域:

(1.1) 共享内存缓冲区池:

参数:

mysql> show variables like '%buffer_pool%';

功能:缓冲数据页 + 索引页

（1.2）会话内存缓冲区

join_buffer_size

key_buffer_size

read_buffer_size

read_rnd_buffer_size

sort_buffer_size

（2）日志

innodb_log_buffer_size=167777216

功能：负责redo日志的缓冲。

案例：

背景:

1.硬件:联想服务器（IBM） 磁盘500G 没有raid

2.软件环境：Centos 6.8 Mysql 5.6.33 innodb引擎 独立表空间

3.备份没有，日志也没开

4.开发用户专用库:jira(bug追踪)、confluence(内部知识库) ------>LNMT

5.故障描述:

断电了,启动完成后"/"只读

fsck 重启，系统成功后，mysql启动不了。

结果:conulence库在，jira库不见了

求助：

这种情况怎么恢复？

我问：

有备份没

求助：

连二进制日志都没有，没有备份，没有主从

我说：

没招了，jira需要硬盘恢复了。

求助:

1.jira问题拉到中关村了

2.能不能暂时把confulence库先打开用着

将生产库confluence，拷贝到1:1虚拟机/var/lib/mysql,直接访问时访问不了的

问:有没有工具能直接读取ibd

我说:我查查，最后发现没有

idea

表空间迁移:

(1) 创建一个一模一样的t1空表

create table xxx

(2) 删掉空表的表空间 ibd

alter table confulence.t1 discard tablespace;

(3) 拷贝准备好的ibd，赋权，导入表空间到t1表

alter table confulence.t1 import tablespace;

虚机测试可行。

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

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

他有2016年的历史库，我让他去他同事的电脑上 mysqldump备份confulence库

mysqldump -uroot -ppassw0rd -B

confulence --no-data >test.sql

拿到你的测试库，进行恢复

到这步为止，表结构有了。

2.表空间删除

select concat('alter table ',table_schema,'.'table_name ' discard tablespace;')

from information_schema.tables

where table_schema='confluence'

into outfile '/tmp/discard.sql';

source /tmp/discard.sql

在执行过程中发现，有20-30个表无法成功。因为主外键的关系

很绝望，一个表一个表的分析表结构很痛苦。

set foreign_key_checks=0 跳过了外键检查。

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

select concat（'alter table',table_scema,'.',table_name,'import tablespace;'）

from information_schema

where table_schema='confulence'

into outfile '/tmp/import.sql';

source /tmp/import.sql

4.验证数据

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

问:2亿行的表，想要删除其中1000w的表，你们公司是怎么做的，加入是按照时间列条件。

回答:

1.如果2亿的数据表，还没有生成，建议在设计表时，采用分区表的方式（按月range），然后删除时truncate。

2.如果2亿行数据表，已经存在，建议使用pt-archive 工具进行归档表，并且删除无用数据。

MYSQL总共使用的内存=共享+会话内存*会话个数+额外的内存使用（文件系统缓存）

6 InnoDB 核心特性详解

6.1 事务

6.1.1 什么是事务？

事务是伴随着《交易类》的业务场景出现的工作机制。保证交易的“和谐”。

交易？

现实:

物换物 :麦子换面粉，豆子换豆腐。

货币换物 :实物货币换货品，虚拟货币换货物。

计算机中:

例如: A 发红包 B

开启事物。

A账户（100元） :(update) A-100元

B账户 （0元） :(update) B+100元

事物结束

1.1.2 事务ACID标准特性

A （automicity） :原子性

原子是物质的最小构成单元，具备不可再分的特性。

在一个事务工作单元中，所有标准事务语句（DML），要么全成功，要么回滚。

C （consistency）:一致性

事务发生前，中，后都应该保证数据是始终一致状态。

MYSQL的各项功能的设计，都是最终要保证一致性。

I （isolation） ：隔离性

MYSQL 可以支持多事务

A工作的时候，不能受到其他事务的影响。

D （durability） :持久性

当事务提交（Commit命令执行成功后），此次事务操作的 操作所有数据"落盘"，都要永久保存下去。

不会因为数据实例发生故障，导致数据失败。

1.1.3 事务生命周期管理

标准的事务控制语句

BEGIN / START TRANSACTION :开启事务

COMMIT ：提交事务

ROLLBACK ：回滚事务

标准的事务语句 :

INSERT

UPDATE

DELETE

SELECT

BEGIN;

DML1

标签1

DML2

标签2

DNL3

标签3

ROLLBACK;

（3）自动提交功能

mysql> SELECT @@autocommit;

+--------------+

| @@autocommit |

+--------------+

| 1 |

+--------------+

在autocommit=1的时候，没有加begin（没有显示的开启事务）。

在你执行dml语句时，会自动在这个DML之前加一个begin，

BEGIN;----> 自动

DELETE ......

COMMIT; ----> 自动

应用场景

autocommit=1,一般合适于非交易类业务场景。

如果时交易类的业务：

方案1：

autocommit=0; COMMIT,手动提交才生效。

方案2：

autocommit=0；

每次想要发生事务型操作。

BEGIN 和 COMMIT 都手工操作。

设置方法：

（1）临时生效：

mysql>SET GLOBAL autocommit=0;

重新开启会话生效。

（2）永久生效

vim /etc/my.cnf

autocommit=0

重启数据库生效。

（4）隐式事务控制语句

隐式提交：

1.设置了autocommit=1

2.DDL,DCL等非DML语句时，会触发隐式提交

SESSION 1：

begin；

DML1

DML2

COMMIT；

DROP DATABASE world;

导致提交的事务语句：

DDL语句：（alter CREATE 和drop）

DCl ： （grant REVOKE 和set password）

锁定语句： （LOCK TABLES 和 UNLOCK tables）

导致隐式提交的语句案例：

TRUNCATE TABLE

LOAD DATA INFILE

SELECT FOR UPDATE

隐式回滚：

会话关闭

数据库宕机

事务语句执行失败

1.2 InnoDB事务的ACID如何保证？

1.2.1 名词介绍

（1）重做日志

redo LOG:重做日志 ib_logfile0~N 48M ,轮询使用

记录的是数据页变化

redo LOG BUFFER:redo内存区域

（2）数据页存储位置

ibd ： 存储数据行和索引

BUFFER pool：缓冲区池，数据页和索引页的缓冲

（3）LSN：日志序列号

磁盘数据页，redo文件，buffer pool, redo BUFFER

MYSQL 每次数据库启动，都会比较磁盘数据页和redolog的LSN，必须要求两者LSN一致数据库才能正常启动。

WAL ：write ahead LOG 日志优先写的方式实现持久化。

脏页 ：内存脏页，内存中发生了修改，没回写入到磁盘之前，我们1把内存页称之为脏页

CKPT : Checkpoint,检查点，就是将脏页刷写到磁盘的动作。

TXID : 事务号，InnoDB会为每一个事物生成一个事务号,伴随着整个事物。

UNDO : ibdata1,存储了事务工作过程中的回滚信息。

1.2.2 InnoDB 事务的工作流程

redo log

1》 Redo是什么？

redo，顾名思义"重做日志"，是事务日志的一种。

2》作用是什么？

在事务ACID过程中，实现的是D持久化的作用。对于AC也有相应的作用。

3》 redo日志文件的位置

redo的日志文件：iblogfile0 iblogfile1

4》redo buffer

redo的buffer：数据页的变化信息+数据页当时的LSN号

LSN：日志序列号、磁盘数据页、内存数据页、redo buffer、redolog

5》redo的刷新策略

commit;

刷新当前事务redo buffer到磁盘

还会顺便将一部分 buffer中没有提交的事务日志也会刷新到磁盘。

补充：redo存储的是在事务工作过程中，数据页变化。

commit时会立即写入磁盘(默认)，日志落盘成功commit。

正常MYSQL工作过程中，主要的工作是提供快速D（持久化）的功能。

 

MYSQL出现Crash异常宕机时，主要提供的是前滚功能（CSR）。 

前滚：也就是说在磁盘区域的ib_logfile里面有个数据页的文件自己有个LSN号（标记版本号）,当数据库异常宕机时（异常宕机:拔掉电源，强制关闭系统，mysql磁盘更新了LSN版本号，当共享内存区域里面的redo log buffer发现数据页的LSN号比buffer pool里面高，他他就会要求buffer pool里面的数据页更新跟自己一样，然后在回写到磁盘区域）。

双一标准：

innodb_flush_at_trx_commit = 0 / 1 / 2

1:在每次事务提交时，会立即刷新redo buffer到磁盘，commit才能成功。

0:每次刷新redo buffer 到os，cache，再到fsync()到磁盘，异常宕机时，会有可能导致丢失1s内的事务。

2:每次事务提交,会立即刷新redo buffer到os,cache,再每秒fsync()磁盘，异常宕机时，会可能导致丢失1s内的事务。

目前默认是1.

除此之外:

1.redo buffer 还和操作系统缓存机制有关，所以刷写策略和innodb_flush_method参数有一定关系。

2.redo 也有group commit；简单理解就是在每次刷新已提交的redo时，顺便可以将一些未提交的事务redo也一次性刷写到磁盘。此时为了区分不同状态的redo，会加一些比较特殊的标记（是否提交标记）。

1.2.3 UNDO Logs(回滚日志)

1》 undo是什么？

undo,顾名思义"回滚日志"

作用是什么？

在事务ACID过程中，实现的是“A”原子性的作用。

另外CI也依赖于Undo

在rollback时，将数据恢复到修改之前的状态。

在CSR实现的是，先redo前滚，在undo回滚。

什么是一致性快照？

每个事务开启快照时（begin）,都会通过undo

生成一个一致性的快照。

undo提供快照技术，保存事务修改之前的数据状态，保证了MVCC，隔离性，mysqldump的热备恢复机制。生成

undo在生成过程中，也会记录redo信息。

redo: 存储的是已经操作过的数据（DML(insert,update,delete)）。

undo: 存储的是原始数据，可以用来恢复原始数据。

1.2.3 隔离级别和锁机制

作用：主要是提供I的特性，另外对于C的特性也有保证。

（1）transaction_isolation 事务隔离性介绍

RU：读未提交

RC: 读已提交

RR：可重复读：默认级别

SR：可串行化

读什么意思？

这里读的不是SQL层的数据行的select，而指的是存储引擎的读，是page的读取。

（2）隔离级别说明

《1》RU(READ UNCOMMITED) :读未提交

出现的问题：脏页读，不可重复读，幻读

《2》RC（READ COMMITED） :读已提交

出现的问题：不可重复读，幻读

《3》RR （REPEATABLE READ）:可重复读

出现的问题：有可能出现幻读，但也可以通过其他手段防止幻读出现。

《4》SR （SERIALIZABLE） :可串行化

串行化事务。以上问题都能规避，但不利于事务的并发

（3）参数修改

mysql>SELECT @@transaction_isolation;

临时调整:

mysql>SET GLOBAL transaction_isolation='read-uncommitted';

重启会话生效

永久修改：

vim /etc/my.cnf

transaction_isolation='read-uncommited'

重启生效

 

结论:此时在session2事务中读取到了，session未提交数据的脏数据，这个现象就是脏读。

不可重复读现象

session2中的事务中，执行相同查询命令是，读到了session正在发生变化都的数据。

《1》脏读：

在session2事务读取到了，session1未提交数据的脏数据。这个现象就是脏读，对于脏读，在生产业务中是一般不允许出现的。

《2》不可重复读现象

session2中的事务中，执行相同的查询命令时读到了session1正在发生变化的数据。对于事物的隔离性和数据最终一致性要求比较高的业务，不允许出现的。

如果业务能够容忍，也是可以出现的。

《3》幻读

在一个事务窗口中，更新操作，出现了别的插入数据的幻行。

调整transaction_isolation='REPEATABLE-READ'防止不可重复读现象：

利用的就是Undo的一致性快照读。MVCC重要功能。

通过RR，已经可以解决99以上的幻读，为了更加严谨，加入GAP锁，next-lock。

GAP锁:间隙锁

GAP+ROW=next-lock。

redo把1改为2，undo把2改为1。

1.事务

1.1 ACID

Redo + UNDO + 隔离级别 + MVCC +锁

RR情况下，左边先更改保存，右边后更改保存，最终双方查看数据是怎么样的？

SESSION 1:

BEGIN; 10:00 MVCC获取10：00最新快照

UPDATE test SET num=30 WHERE num<30 ---11 15 17

COMMIT; 10:05

BEGIN; 10:05

SELECT 30 不包括

SELECT 10:06

SESSION 2;

BEGIN; 10:01 MVCC 获取10：01最新快照

INSERT INTO test VALUES （48）；

commit； 10：06

BEGIN; MVCC

SELECT 30,48

前提：

1.隔离级别是事务和事务之前的特性。

2.当事务结束，隔离性就没了。

CSR

===========================

今日话题：

1.MYSQL的锁机制

1.1 介绍

相当于厕所的门。

1.2 作用

保证事物之间的隔离性

保证资源不会征用。锁是属于资源的，不是某个事务的特性。

每次事务需要资源的时候，需要申请持有资源的锁。

1.3 锁的类型

资源：

内存锁 ：mutex，latch,保证内存数据页资源不被争用，不被置换。

对象锁粒度：

MDL(元数据锁):修改元数据时。DDL--->ALTER,备份

Table_lock :表锁，DDL，备份（FTWRL全局表锁），lock TABLES t1 READ,也可以升级为表锁。

record(ROW)LOCK:行锁，索引锁，锁定聚簇索引。

GAP :间隙锁，RR级别，普通辅助索引间隙锁。

NEXT-LOCK :下一级锁，GAP+record lock，普通辅助索引的范围锁。

功能分类：

I(intention)S(SHARE) : 意向共享锁，表级别

S(SHARE) : 共享锁，读锁，行级锁

I(intention)X(exclusive) :意向排他锁，表级别

X(exclusive) :排他锁，写锁，行级别

mysql> select * from sys.innodb_lock_waits\G :查看锁状态。

2. 事务得一致性ACID得C特性

A :原子性,undo,redo

D :持久性，redo（WAL）

I :隔离性，isolation level，lock，MVCC（undo）

C :保证工作前，中，后，数据的状态都是完整的，一致性。

所以C的特性是以上所有特性都是来保证一致性的。

写一致性： undo,redo,lock

读一致性： isolation level（ru,rr,rc,sr），MVCC（undo）

数据页的一致性：

double write buffer (磁盘区域)

double write buffer是Innodb在tablespace上的128个页（2个区）大小是2MB，为了解决partial page write问题，当MYSQL将脏数据flush到data file的时候，先使用memcopy将脏数据复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次写入1MB到共享表空间，然后马上调用fsync函数，同步到磁盘上，避免缓冲带来的问题过程中，doublewrite是顺序写，开销并不大，在完成doublewrite写入后，再将double write buffer写入各表空间文件，这时是离散写入。

所以正常的情况下，MYSQL写数据PAGE时，会写两遍到磁盘上，第一遍时写到doublewrite buffer,第二遍是从doublewrite buffer写到真正的数据文件中，如果发生了极端情况（断电），Innodb再次启动后，发现了一个page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复。

3. 存储引擎核心参数

①innodb_flush_log_at_trx_commit=1 /0/2

②innodb_flush_method=fsync/o_direct/o_DSYNC

③innodb_buffer_pool_size

双一标准之一:redo log刷写参数

1.innodb_flush_log_at_trx_commit=1

2.innodb_flush_method=fsync/o_direct/o_DSYNC

作用:控制MYSQL刷写磁盘时，是否使用OS Cache

fsync 模式:

buffer pool的数据写到磁盘的时候，需要先经历OS cache,然后在写到磁盘。

redo buffer的数据写到磁盘的时候，需要先经历os cache，然后再写到磁盘。

O_SYNC 模式:

buffer pool的数据写到磁盘的时候

要先经历OS cache,然后在写到磁盘。

redo buffer的数据写到磁盘的时候，跨过os cache，直接写到磁盘。

O_DIRECT 模式:

buffer pool的数据写到磁盘的时候,跨过os cache，直接写到磁盘。

redo buffer的数据写到磁盘的时候,要先经历OS cache,然后在写到磁盘。

建议使用O_DIRECT,最好配合固态盘使用。

3.innodb_buffer_pool_size

OOM? ---> innodb_buffer_pool_size=80% * Total

这样设置会出现OOM（out of memory）

作用:数据缓冲区的总大小。缓冲数据页和索引页。是MYSQL最大的内存区域。

默认:128M

官方建议:80-90%内存

生产建议:75%以下，按需调配

set global innodb_buffer_pool_size=75%以下;

mysql> show engine innodb status \G 查看存储引擎状态。
共享锁（S锁）：允许持锁事务读取一行。
排他锁（X锁）：允许持锁事务更新或者删除一行。
意向共享锁( IS 锁)：事务想要获得一张表中某几行的共享锁。
意向排他锁( IX 锁)： 事务想要获得一张表中某几行的排他锁。

记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE

记录锁永远都是加在索引上的。