什么是存储引擎

相当于MySQL内置的文件系统。和操作系统的文件系统打交道的层次结构。

MySQL存储引擎种类

可以针对不同的表，依据需要设定不同的存储引擎。

官方MySQL提供的存储引擎中，比较常用的主要有以下几种：

  1. InnoDB，MySQL5.5以后默认的存储引擎，99%的业务表都是它了
  2. MyISAM
  3. CSV
  4. MEMORY

其他分支版本MySQL提供的存储引擎：

  1. percona版本MySQL：默认XtraDB
  2. MariaDB版本MySQL: 默认InnoDB
  3. 其他较为常见的存储引擎：TokuDB（突出点：压缩数据，占用空间少；插入和删除的性能更高）、MyRocks
mysql>show engines; // 命令查看支持的存储引擎种类

InnoDB核心特性

  1. 支持事务控制
  2. 锁粒度，支持行级锁（MyISAM是表级锁）
  3. 支持外键
  4. mvcc，多版本并发控制
  5. clustered index，聚簇索引
  6. 多缓冲区池
  7. 支持更多复制特性
  8. 支持热备
  9. 自动故障恢复
  10. change buffer
  11. 自适应hash索引，AHI
show status like '%wait%';
show status like '%lock%'; // 查看锁等待情况

存储引擎的相关命令

select @@default_storage_engine; // 查看当前会话的默认存储引擎
set default_storage_engine=myisam; // 修改当前会话的默认存储引擎
set global default_storage_engine=myisam; // 全局修改默认存储引擎，只影响新会话，旧有会话不受影响。MySQL重启后失效

// 如果要永久生效，可以修改配置文件 
vim /etc/my.conf
[mysqld]
default_storage_engine=myisam

// 查看表的存储引擎
show create table table_name\G;
show table status like 'table_name'\G;

// 查看每个表的存储引擎
select table_schema, table_neme, engine from information_schema.tables where table_schema not in ('sys', 'mysql', 'information_schema', 'performance_schema');

// 修改一个表的存储引擎，执行这个命令也可以对innodb表进行碎片整理
alter table table_name engine innodb;

// 查看各个表的碎片（delete后空间没释放就会导致碎片产生）
select table_schema, table_name, data_free from information_schema.tables;

// information_schema.tables 这个表很6啊

存储引擎的宏观体系结构（存储文件结构）

  1. MyISAM
    myisam的表由三个文件构建
    table_name.frm: 数据字典信息，表的列的定义和属性
    table_name.MYD: 数据行
    table_name.MYI: 索引（不是聚簇索引）

  2. InnoDB
    innodb的表由很多个文件构成：
    table_name.frm: 表的数据字典信息，列的情况
    table_name.ibd（独立表空间文件）: 表的数据行和索引
    ibdata1（共享表空间文件）: 所有innodb表的数据字典信息，undo（事务回滚日志），double write磁盘区域，change buffer磁盘区域
    ib_logfile0~ib_logfileN: innodb事务重做日志（redo log）
    ibtmp1: 临时表空间文件，排序、分组、备份、子查询等会用到
    ib_buffer_pool: 关闭MySQL时，存储缓冲区的热数据，下一次启动自动装载这些热数据

innodb的表，只拷贝ibd和frm文件到新的数据库，是无法正常使用的。
myisam的表，拷贝frm、MYD、MYI文件到新的数据库，是可以正常使用的。

存储引擎的微观体系结构

磁盘

数据存储--表空间模式

概念：为了解决存储空间扩展的问题，5.5版本开始引入表空间概念
分类：

  1. 共享表空间：即ibdata1文件
  2. 独立表空间：单表单空间
  3. undo表空间：存储回滚日志
  4. 临时表空间：存储临时表

表空间管理：
用户数据默认存储在独立表空间。独立表空间和共享表空间可以互相切换。

select @@innodb_file_per_table;
1表示独立表空间模式，0表示共享表空间模式（用户数据和索引等都只存在ibdata1中）
事务日志
  1. 重做日志redo log
  • 文件位置：ib_logfile0~ib_logfileN
  • 控制参数：innodb_log_file_size(文件大小)、innodb_log_files_in_group（文件个数）
  • 功能：用来存储MySQL做修改类（DML）操作时的数据页变化及版本号（LSN），属于物理日志，方便进行日志重做；默认两个文件，循环使用
  1. 回滚日志undo log
  • 文件位置：一部分存在ibdata1，一部分存在ibtmp1
  • 控制参数：innodb_rollback_segments(回滚段)
  • 功能：用来存储回滚日志（可以理解为记录了每次操作的反操作，属于逻辑日志），使用快照功能提供innodb多版本并发读写、通过反操作提供回滚功能

内存

数据内存区域
  1. 共享内存缓冲区：各种缓冲区，缓存数据页和索引页
  2. 会话内存缓冲区（一个连接就是一个会话）：缓存临时表、排序等
日志
  1. log_buffer_size:负责redo日志的缓冲

事务

事务是伴随着交易类的业务出现的工作机制，保证交易的完整。
事务是作为单个逻辑工作单元执行的一系列操作。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。

事务的四个特性（ACID）：

  1. 原子性（Atomicity）。一个事务内的所有操作作为原子单元执行，要么全部执行，要么全部不执行。
  2. 一致性（Consistency）。事务完成以后，所有数据的状态是一致的，比如A账户少了100，B账户就多了100。MySQL的各项功能设计，最终都是要保证一致性。
  3. 隔离性（Isolation）。多个事务并发执行，每个事务作出的修改必须与其他事务隔离，互不影响。
  4. 持久性（durability）。事务完成以后，修改被持久化保存。

事务的生命周期管理（怎么开始怎么结束）：

一个完整的事务：

begin；/start transaction; # 开启事务
DML1;			   # 事务语句，可多条DML语句	
DML2;
DML3;
commit；/rollback;	   # 提交事务/回滚事务

标准事务控制语句

begin;/start transaction;  # 开启事务，功能一模一样，前者敲的字母更少
commit; 		   # 提交事务
rollback; 		   # 回滚事务

标准的事务语句

insert
update
delete
select

自动提交功能

select @@autocommit;	## 查看是否开启自动提交功能

自动提交开关：
1. set global autocommit=0; ## 新开启的会话生效，但是MySQL重启会失效
2. 修改配置文件my.cnf，添加autocommit=0，重启MySQL永久生效


开启自动提交功能时，若没有显式地开启一个事务（begin），则默认一句DML语句就是一个事务（自动commit）。
若要进行事务控制，显式地控制事务的开启和提交更好（手动begin、commit）。
如果关闭了自动提交功能，需要显式地提交事务才生效（但可省略begin）。

隐式事务控制

隐式提交：
1. 设置了autocommit=1
2. DDL、DCL等非DML语句，会触发隐式提交。begin; DML1; begin; 第二个begin作为非DML语句，也是会导致隐式提交的哦！

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

隐式回滚：
1. 会话关闭
2. 数据库宕机
3. 事务语句执行失败

事务ACID如何保证

名词介绍

  1. redo log，重做日志，ib_logfile_N，轮询使用，记录数据页的变化
  2. redo log buffer，redo日志的内存区域
  3. .ibd文件，存储数据行和索引
  4. buffer pool，数据和索引的缓冲区
  5. LSN，日志序列号，保证各个文件的版本号一致。MySQL每次启动，都会比较磁盘数据页（.ibd）和redo log的LSN，只有两者一致才能正常启动。
  6. WAL，write ahead log，日志优先于数据写的方式实现持久化
  7. 脏页，内存脏页，内存发生了修改，没写入到磁盘之前，把内存页成为脏页
  8. CKPT，checkpoint，检查点，就是将脏页写到磁盘的动作
  9. TXID，事务id，innodb会为每个事务生成一个事务id，伴随整个事务生命周期
  10. undo，ibdata1中存储，存储事务工作中的回滚信息，相当于反操作

事务工作流程

16299704771.png

16299705301.png

16299703271.png

事务隔离级别和锁机制

作用：主要是提供隔离性的相关特性，另外对一致性也有保证。

脏读：读取到其他事务未提交的内容，倘若其他事务发生错误进行了回滚，那么读取到的就是脏数据。在生产中一般是不允许出现脏读的，所以RU隔离级别一般不用。
不可重复读：多次读取，但是每次读取到的内容都不一样，所以称为不可重复读。两次读取的中间过程中，其他事务进行了修改提交，就会导致两次读取到的内容不同。某些业务是可以出现不可重复读的，这种情况下可以把隔离级别改成RC。
幻读：多次读取，但是每次读取到的数据集条数不一样。和不可重复读类似，但是幻读强调的是数据集的增减（尤其是增加），不可重复读强调的是单条数据的修改。幻读是不可重复读的一种特殊场景。

事务四种隔离级别，默认是可重复读：

  1. RU，read uncommited，读未提交。可能出现脏读、不可重复读、幻读。
  2. RC，read committed，读已提交。可能出现不可重复读、幻读。
  3. RR，repeatable read，可重复读。极小概率可能出现幻读。可重复读利用了undo的一致性快照读。
  4. SR，serializable，可串行化。可规避所有问题，但是并发度低。

隔离级别越高，越能保证数据的一致性，但是对并发性能的影响也越大。

参数修改：

select @@transaction_isolation; ## 查询隔离级别

## 临时调整隔离级别，重启会话生效，MySQL重启失效
set global transaction_isolation = 'read-uncommitted';

## 永久调整隔离级别，修改配置文件my.cnf，并重启MySQL

RR隔离级别如何尽量避免幻读的？

加入了GAP锁，next-lock。
gap锁：间隙锁，锁范围的，不仅仅是锁已有记录的行，整个范围都锁了，如果要插入范围内的新数据，也是要锁等待的。gap锁一定要有索引列。
innodb锁介绍

介绍：相当于厕所的门。
作用：保证事务之间的隔离性。保证资源不会被争用。

锁的类型：
按资源分：

  1. 内存锁：mutex、latch，保证内存数据页资源不被争用，不被置换。
  2. 对象锁：
    • MDL，元数据锁，进行DDL操作、备份时，会进行元数据锁
    • table_lock，表锁
    • record(row) lock，行锁，索引锁，锁定聚簇索引
    • gap，间隙锁，RR隔离级别，普通辅助索引（非聚簇索引、唯一索引）间隙锁
    • next-lock，下一键锁，普通辅助索引的范围锁

按功能分：

  1. IS，意向读锁
  2. S，读锁
  3. IX，意向写锁
  4. X，写锁

查看锁的等待情况的sql：

select * from sys.innodb_lock_wait\G