InnoDB存储引擎
存储引擎的功能
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
# 查看数据库支持的存储引擎
show engines;
# 查看数据库下所有innodb的表
mysql> select table_schema,table_name ,engine
from information_schema.tables
其他MySQL的引擎
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.
核心特性
1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、复制Replication: Group Commit , GTID (Global Transaction ID) ,多线程(MTS,Multi-Threads-SQL )
7、外键
存储擎操作类命令
# 确认会话存储引擎
SELECT @@default_storage_engine;
mysql> show variables like '%engine%';
# 设置默认存储引擎
set default_storage_engine=myisam; # 会话级别
set global default_storage_engine=myisam; # 全局级别(仅影响新会话)
vim /etc/my.cnf # 写入配置文件重启之后,所有参数均失效.如果要永久生效:写入配置文件
[mysqld]
default_storage_engine=myisam
# 确认表的存储引擎
SHOW CREATE TABLE city\G;
SHOW TABLE STATUS LIKE 'city'\G
# 确认每个表的存储引擎
mysql> select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
+--------------+-----------------+--------+
| table_schema | table_name | engine |
+--------------+-----------------+--------+
| oldboy | t100w | InnoDB |
| school | course | InnoDB |
| school | sc | InnoDB |
| school | student | InnoDB |
| school | teacher | InnoDB |
| world | city | InnoDB |
| world | country | InnoDB |
| world | countrylanguage | InnoDB |
| zrblog | comment | InnoDB |
| zrblog | link | InnoDB |
| zrblog | log | InnoDB |
| zrblog | lognav | InnoDB |
| zrblog | plugin | InnoDB |
| zrblog | tag | InnoDB |
| zrblog | type | InnoDB |
| zrblog | user | InnoDB |
| zrblog | website | InnoDB |
+--------------+-----------------+--------+
# 修改一个表的存储引擎
注意：此命令我们经常使用他，进行innodb表的碎片整理
alter table t1 engine innodb;
# 将oldboy数据库下的所有1000表，存储引擎从MyISAM替换为innodb
select concat("alter table ",table_name," engine innodb;")
from information_schema.tables
where table_schema='oldboy'
into outfile '/tmp/alter.sql';
InooDB存储引擎物理存储结构
| 文件名 | 作用 |
|---|---|
| ibdata1 | 系统数据字典信息（统计信息），UNDO表空间等数据 |
| ib_logfile0 ~ ib_logfile1 | REDO日志文件，事务日志文件 |
| ibtmp1 | 临时表空间磁盘位置，存储临时表 |
| frm | 存储表的列信息 |
| ibd | 表的数据行和索引 |
| ib_buffer_pool | 缓冲池（加载经常访问的数据到内存） |
root@localhost ~]# ll /data/mysql/data/
total 476084
-rw-r-----. 1 mysql mysql 56 Apr 28 13:45 auto.cnf
-rw-r-----. 1 mysql mysql 407 Apr 28 21:16 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 May 6 09:45 ibdata1
-rw-r-----. 1 mysql mysql 50331648 May 6 09:45 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Apr 28 22:24 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 May 6 10:08 ibtmp1
-rw-r-----. 1 mysql mysql 6 Apr 28 21:16 localhost.pid
drwxr-x---. 2 mysql mysql 4096 Apr 28 13:45 mysql
-rw-r-----. 1 mysql mysql 177 Apr 28 13:48 mysql-bin.000001
-rw-r-----. 1 mysql mysql 177 Apr 28 13:48 mysql-bin.000002
-rw-r-----. 1 mysql mysql 177 Apr 28 13:51 mysql-bin.000003
-rw-r-----. 1 mysql mysql 573 Apr 28 13:53 mysql-bin.000004
-rw-r-----. 1 mysql mysql 1389115 Apr 28 21:16 mysql-bin.000005
-rw-r-----. 1 mysql mysql 177 Apr 28 21:16 mysql-bin.000006
-rw-r-----. 1 mysql mysql 177 Apr 28 21:16 mysql-bin.000007
-rw-r-----. 1 mysql mysql 293029039 May 6 09:45 mysql-bin.000008
-rw-r-----. 1 mysql mysql 272 Apr 28 21:16 mysql-bin.index
-rw-r-----. 1 mysql mysql 69650 May 6 09:23 mysql.log
drwxr-x---. 2 mysql mysql 54 Apr 29 06:51 oldboy
drwxr-x---. 2 mysql mysql 8192 Apr 28 13:45 performance_schema
drwxr-x---. 2 mysql mysql 160 Apr 28 17:27 school
drwxr-x---. 2 mysql mysql 8192 Apr 28 13:45 sys
drwxr-x---. 2 mysql mysql 174 May 6 09:45 world
drwxr-x---. 2 mysql mysql 4096 May 4 11:09 zrblog
[root@localhost ~]# ll /data/mysql/data/oldboy/
total 77840
-rw-r-----. 1 mysql mysql 65 Apr 28 22:03 db.opt
-rw-r-----. 1 mysql mysql 8662 Apr 29 06:18 t100w.frm
-rw-r-----. 1 mysql mysql 79691776 Apr 29 06:51 t100w.ibd
表空间(Tablespace)
共享表空间
需要将所有数据存储到同一个表空间中 ，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。（数据字典，undo，临时表，索引，表数据）
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
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
# 共享表空间设置
共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
>select @@innodb_data_file_path;
>show variables like '%extend%';
以下可以加入配置文件中
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64
# 表空间查看 1代表独立表空间模式，0代表共享表空间模式
mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+
# 设置为共享表空间模式
mysql> set global innodb_file_per_table=0;
独立表空间
从5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
基本表结构元数据存储：
xxx.frm
最终结论：
元数据 数据行+索引
mysql表数据 =（ibdataX+frm）+ibd(段、区、页)
DDL DML+DQL
# 删除表空间
alter table city dicard tablespace;
# 导入表空间
alter table city import tablespace;
MySQL的存储引擎日志
Redo Log: ib_logfile0 ib_logfile1，重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
临时表:ibtmp1，在做join union操作产生临时数据，用完就自动
事务ACID
影响了DML语句（insert update delete 一部分select）
Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）
事务之间不相互影响。
Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
# 事务的开始：在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。
begin; / start transaction;
提交事务：完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。
commit;
回滚事务：将内存中，已执行过的操作，回滚回去
rollback;
# 自动提交策略（autocommit） 1代表自动提交 0代表关闭自动提交
db01 [(none)]>select @@autocommit;
db01 [(none)]>set autocommit=0;
db01 [(none)]>set global autocommit=0;
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
(1)
set autocommit=0;
set global autocommit=0;
(2)
vim /etc/my.cnf
autocommit=0
# 事务的隐式控制
用于隐式提交的SQL语句
begin
a
b
导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
InnoDB 事务的ACID如何保证
redo log ---> 重做日志 ib_logfile0~1 50M ,轮询使用
redo log buffer ---> redo内存区域
t1.ibd ----> 存储 数据行和索引
buffer pool --->数据缓冲区池,数据和索引的缓冲
LSN : 日志序列号
磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.
redo log
Redo是什么
redo,顾名思义“重做日志”，是事务日志的一种。
作用是什么
在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用
redo日志位置
redo的日志文件：iblogfile0 iblogfile1
redo buffer
redo的buffer:数据页的变化信息+数据页当时的LSN号
LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog
redo的刷新策略
commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
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的"前滚操作
undo log
undo是什么？
undo,顾名思义“回滚日志”
作用是什么？
在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备
概念性的东西
redo怎么应用的
undo怎么应用的
CSR(自动故障恢复)过程
LSN :日志序列号
TXID:事务ID
CKPT(Checkpoint)
锁
“锁”顾名思义就是锁定的意思。
“锁”的作用是什么？
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).
悲观锁:行级锁定(行锁)
谁先操作某个数据行,就会持有<这行>的(X)锁.
乐观锁: 没有锁
隔离级别
影响到数据的读取,默认的级别是 RR模式.
transaction_isolation 隔离级别(参数)
负责的是,MVCC,读一致性问题
RU : 读未提交,可脏读,一般部议叙出现
RC : 读已提交,可能出现幻读,可以防止脏读.
RR : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
例如:
[world]>select * from city where id=999 for update;
[world]>commit;