六、InnoDB存储引擎物理存储结构

ib开头的都是innodb存储引擎相关的数据文件
[root@mysql52 /data/mysql/data]# ll
total 188568
-rw-r----- 1 mysql mysql       56 Jun 13 15:23 auto.cnf
-rw-r----- 1 mysql mysql      875 Jun 20 16:08 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Jun 21 08:42 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jun 21 08:42 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jun 19 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jun 21 08:42 ibtmp1
......

以上文件具体的存放的东西或作用：

ib_buffer_pool        重启数据库之后，回复以往的热数据到内存里。
ibdata1               存放的是系统数据字典信息(统计信息)，和UNDO表空间等数据
ib_logfile0           REDO日志文件，事务日志文件。  
ib_logfile1           REDO日志文件，事务日志文件。
btmp1                临时表空间磁盘位置，存储临时表 
frm                   存储表的列信息
ibd                   表的数据行和索引

6.1表空间(Tablespace)

6.1.1共享表空间 ibdata1个版本存放的数据：

需要将所有数据存储到同一个表空间中 ，管理比较混乱
5.5版本才出现的管理模式，也是默认的管理模式。 （数据字典，undo,临时表，索引，表数据）
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了   （数据字典信息,undo）
8.0版本,undo也被独立出去了

具体变化参考文档：
https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.8/en/innodb-architecture.html

6.1.2 共享表空间设置

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中，也可以后期更改)

mysql> select @@innodb_file_per_table;         查看表空间当前模式
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |                    默认结果是1，代表着当前为独立表空间模式。0代表共享表空间模式。
+-------------------------+
1 row in set (0.00 sec)

修改默认表空间为共享空间：
mysql> set innodb_file_per_table=0;

ERROR 1229 (HY000): Variable 'innodb_file_per_table' is a GLOBAL variable and should be set with SET GLOBAL
报错，关键字提示：GLOBAL 即表示只能影响新建的会话，不能影响现在的和过去的会话，因此，SQL语句更改为：

mysql> set global innodb_file_per_table=0;
Query OK, 0 rows affected (0.00 sec)                    修改表空间为共享模式

注意，这种修改在MySQL重启之后，失效，要想永久修改，添加到my.cnf
vim /etc/my.cnf
[mysqld]
innodb_file_per_table=0
当然，不建议修改为共享空间，默认的独立表空间就可以。

mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |           默认ibdate1的空间大小，不够时自动增加
+-------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%extend%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
1 row in set (0.00 sec)
每次
自增加大小为64M 
             
建议：
初始化之前，直接设置它容量为两个512M的，不够再自己增长容量。
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64

6.1.3 独立表空间

从5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
基本表结构元数据存储：
xxx.frm
最终结论：
      元数据            数据行+索引
mysql表数据    =（ibdataX+frm）+ibd(段、区、页)
        DDL             DML+DQL

MySQL的存储引擎日志：
Redo Log: ib_logfile0  ib_logfile1，重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志

临时表:ibtmp1，在做join union操作产生临时数据，用完就自动删除

6.1.4 删除恢复表空间

mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                      1 |
+-------------------------+

先在命令行备份此文件 cp /mysql/world/city.ibd city.ibd.back

alter table city dicard tablespace;
删除city表的表空间（ibd文件）

此时查看city表的数据，已经无法查看了

命令行先恢复city.ibd   cp /mysql/world/city.ibd.back city.ibd (虽然命令行文件已经恢复，但是MySQL并不认识)

授权city.ibd文件权限为mysql   就这个权限问题，TM的，我想了半天。。。

alter table city import tablespace;
恢复city表的表空间（ibd）文件。

现在可以正常查看city表的数据了。

提醒三个点：
命令行把文件恢复之后，一定要授权
MySQL内部，要么use到指定库下，要么写库.表。
倘若中间报因键的问题，执行set foreign_key_checks=0 跳过外键检查。

七、事务的ACID特性

7.1作用：

影响了DML语句（insert  update delete 一部分select）

7.2ACID具体介绍：

Atomic（原子性）:
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
比如我发五十红包，你收五十红包。不能我的扣了，你还没收到。

Consistent（一致性）；
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
比如：我有一百块，你有一百块，我发给你五十。最后我有五十块，你有一百五十块

Isolated（隔离性）:
事务之间不相互影响。

Durable（持久性）:
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

八、事务的生命周期（事务控制语句）

8.1事务的开始

begin;  及事务的开始了
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。

8.2事务的结束：

commit;     提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。

rollback;   回滚事务（就是取消事务开始之后做的操作，后悔了。）
将内存中，已执行过的操作，回滚回去

8.3自动提交策略：

mysql> select @@autocommit;            查看自动提交策略状态 0为关闭，1为开启，默认开启
+--------------+
| @@autocommit |
+--------------+
|           1  |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;                     关闭当前会话自动提交策略
mysql> set global autocommit=0;              关闭全局会话自动提交策略

同样，这种修改方法，当MySQL重启的时候就会失效，想要永久关闭自动提交策略，将其写到my.cnf里

vim /etc/my.cnf
[mysqld]
autocommit=0    

自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能

8.4 隐式提交语句

用于隐式提交的 SQL 语句：
begin                 执行了一个begin（执行DML语句，默认会再前边添加一个begin）
a                     执行一条DML语句
begin                 又执行了一条DML语句，那么就会自动提交上一条语句。

在做事务期间，不要执行其他语句，否则你的事务未完成操作，就会被提交。
尽量实用标准的结束事务的语句（commit;提交或rollback;回滚）来结束事务。 

导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

8.5 开始事务流程：

1、检查autocommit(自动提交策略)是否为关闭状态
select @@autocommit;
或者：
show variables like 'autocommit';

2、开启事务,并结束事务
begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback; 回滚，即以上语句不生效

时刻记得，事务的ACID特性（原子性，一致性，隔离性，持久性）

begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit;   提交，即让以上语句生效

九、 InnoDB 事务的ACID如何保证?

先介绍一些名词

redo log          ----> 重做日志     文件名是：ib_logfile0~1   默认大小:50M  , 轮询使用
redo log buffer   ----> redo内存区域
.ibd              ----> 存储 数据行和索引 
buffer pool       ---->数据缓冲区池,用于数据和索引的缓冲
LSN               ---->日志序列号 

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

WAL                ----> write ahead log 日志优先写的方式实现持久化 （就是保证日志先于数据写到磁盘）
脏页                ----> 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT                ---->Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID                ----> 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.

9.1 redo log

9.1.1 Redo是什么？

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

9.1.2 作用是什么？

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

9.1.3 redo日志位置

redo的日志文件：iblogfile0 iblogfile1

9.1.4 redo buffer

redo的buffer:数据页的变化信息+数据页当时的LSN号
LSN：日志序列号  磁盘数据页、内存数据页、redo buffer、redolog

9.1.5 redo的刷新策略

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

9.1.6 MySQL CSR——前滚

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
情况一:
我们做了一个事务,begin;update;commit.
1.在begin ,会立即分配一个TXID=tx_01.
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer
5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,
在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102
MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动
以上的工作过程,我们把它称之为基于REDO的"前滚操作"

10.2 undo 回滚日志

10.2.1 undo是什么？

undo,顾名思义“回滚日志”

10.2.2 作用是什么？

在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

十一、锁

介绍：

就是锁定的意思，提供的是事务特性中I（隔离性）方面的功能。需要配合undo+隔离级别以一起实现

InnoDB锁级别

行级锁（要修改哪一行，就持有了哪一行的锁）
本章节不做过多讲述，请看后期更新的MySQL优化

悲观锁、乐观锁

悲观锁:行级锁定(行锁)
谁先操作某个数据行,就会持有<这行>的(X)锁.
乐观锁: 没有锁

十二、事务的隔离级别（RR、RC、RU、SR）

RR 、RC是重点

查看隔离级别(默认为RR模式)

select @@tx_isolation; 
show variables like '%iso%';                      这三条语句都可查询隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

RU  : 读未提交,可脏读,一般部议叙出现
RC  : 读已提交,可能出现幻读,可以防止脏读.
RR  : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR   : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.
但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
示例：
mysql> select * from city where id=999 for update;
mysql> commit

改为RC模式（那边提交事务之后，这边结果立马刷新）

set global transaction_isolation='read-committed';
然后退出MySQL重连即可生效。重启数据库后失效。

十三、InnoDB核心参数

13.1 双一标准之一*****： innodb_flush_log_at_trx_commit=1;

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

作用： 控制了redo buffer 刷写策略 （是一个安全参数，是在5.6版本以上默认参数）
等于1： 默认参数，在每次事务提交都会立即刷写redo到磁盘（redo buffer --->os buffer --->磁盘）
等于0：当事务提交时，不立即做日志写入操作（redo buffer ---每秒--->os buffer ---每秒  ---->磁盘）
等于2：每次事务提交引起写入文件系统缓存（redo buffer ---每事务--->os buffer--每秒 ---->磁盘）

redo buffer 是MySQL内存   os buffer 是文件系统内存  

13.2InnoDB_fulsh_method=(O_DIRECT,fdatasync)

作用：

控制了 redo buffer 和 data buffer  刷写磁盘的方式,默认为空

ccclipboard.png

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

建议设置为如图所示的第三种，数据直接跳过文件系统刷进磁盘，而日志则正常的先进入文件系统内存，再进入磁盘。
设置方法:
vim /etc/my.cnf
innodb_flush_method=O_DIRECT
然后重启mysql

最大安全模式：

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

最大性能模式

innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

13.2关于redo设置

innodb_log_buffer_size=128M   业务系统CPU压力有关
innodb_log_file_size=256   一般是一到两倍
innodb_log_filees_in_group=3   一般是3-4组

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+
1 row in set (0.00 sec)
该参数一般调整为物理内存的50%-80%左右（前提你的系统中中有一个实例）