五，存储引擎

1，简介

相当于Linux文件系统，只不过比文件系统强大

2，了解功能

数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
等.

3，存储引擎介绍

查看：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

MySQL5.5版本后默认的存储引擎： InnoDB
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB

第三方的存储引擎:
RocksDB MyRocks TokuDB
压缩比较高,数据的插入性能高.其他功能和InnoDB没差

监控系统架构整改

环境: zabbix 3.2    mariaDB 5.5  centos 7.3
现象 : zabbix卡的要死 ,  每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
1. zabbix 版本 
2. 数据库版本
3. zabbix数据库500G,存在一个文件里

优化建议:
1.数据库版本升级到mariaDB最新版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....
优化结果:
监控状态良好

为什么?
1. 原生态支持TokuDB,另外经过测试环境,10版本要比5.5 版本性能 高 2-3倍
2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
4.关闭binlog ----->减少无关日志的记录.
5.参数调整...----->安全性参数关闭,提高性能

4，InnoDB存储引擎核心特性

MVCC ： 多版本并发控制
聚簇索引 ： 用来组织存储数据和优化查询，IOT。
支持事务 ： 数据安全保证
支持行级锁 ： 控制并发
外键
多缓冲区支持
自适应Hash索引： AHI
复制中支持高级特性。
备份恢复： 支持热备。
自动故障恢复：CR Crash Recovery
双写机制：DWB Double Write Buffer

查看存储引擎设置

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

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


修改：
vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB

查看表存储引擎状态

mysql> show create table t111;
select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

5，修改存储引擎

修改存储引擎

mysql> alter table t111 engine=innodb;
mysql> show create table t111;  

整理碎片

mysql> alter table t111 engine=innodb;

6，InnoDB存储引擎物理结构

最直观的存储方式

bdata1：                        系统数据字典信息(统计信息)，UNDO表空间等数据
ib_logfile0 ~ ib_logfile1:      REDO日志文件，事务日志文件。
ibtmp1：                        临时表空间磁盘位置，存储临时表
frm：                           存储表的列信息
ibd：                           表的数据行和索引

表空间（Tablespace）

ibdata1 : 整个库的统计信息+Undo   
ibd     : 数据行和索引

共享表空间(ibdata1~N)

5.5 版本的默认模式,5.6中转换为了独立表空间 
需要将所有数据存储到同一个表空间中 ，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。
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时，初始化数据之前设置到参数文件中)
mysql> select @@innodb_data_file_path;
mysql> show variables like '%extend%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
1 row in set (0.16 sec)

例如: 
mysqld --initialize-insecure --user=mysql --basedir=xxxxxx......
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

独立表空间

从5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
最终结论：
一张InnoDB表= frm+idb+ibdata1 
MySQL的存储引擎日志：
Redo Log: ib_logfile0  ib_logfile1，重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志

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

独立表空间设置问题

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

独立表空间迁移

(1)创建和原表结构一致的空表
(2)将空表的ibd文件删除
alter table city dicard tablespace;
(3)将原表的ibd拷贝过来,并且修改权限
(4)将原表ibd进行导入
alter table city import tablespace;
从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操作产生临时数据，用完就自动

独立表空间设置问题
db01 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                      1 |
+-------------------------+
alter table city dicard tablespace;
alter table city import tablespace;

7，InnoDB核心特征

事务

事务的ACID特性
Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。

Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

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

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

(1) 如何开启事务
begin ;

(2) 标准的事务语句
DML : 
insert  
update  
delete
mysql> use world;
mysql> update city set countrycode='CHN' where id=1;
mysql> update city set countrycode='CHN' where id=2;
mysql> update city set countrycode='CHN' where id=3;

(3)事务的结束 
提交:
commit;
回滚:
rollback;

自动提交机制(autocommit)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
在线修改参数:
(1) 会话级别:  
mysql> set autocommit=0;
及时生效,只影响当前登录会话
(2)全局级别:
mysql> set global autocommit=0;  
断开窗口重连后生效,影响到所有新开的会话
(3)永久修改(重启生效) 
vim /etc/my.cnf 
autocommit=0

隐式提交的情况

出发隐式提交的语句
例
begin
a
b
begin
在同一个窗口一个事务执行完成之前不要开启另一个新事物
导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

9，事务的ACID

redo log: 重做日志
ib_logfile0~1   默认50M  , 轮询使用

redo log buffer :
redo内存区域
ibd   :
存储 数据行和索引 

data buffer pool :
缓冲区池,数据和索引的缓冲

LSN : 日志序列号 
ibd ,redolog ,data buffer pool, redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动

WAL (持久化):
write ahead log 日志优先写的方式实现持久化
日志是优先于数据写入磁盘的.

脏页: 
内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.

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

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

事务日志-- redo 重做日志

作用:
主要功能  保证 "D"  ,  A C   也有一定得作用
(1)记录了内存数据页的变化.
(2)提供快速的持久化功能(WAL)
(3)CSR过程中实现前滚的操作(磁盘数据页和redo日志LSN一致)

redo日志位置
redo的日志文件：iblogfile0 iblogfile1

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

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

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回滚日志.

作用: 在 ACID特性中,主要保证A的特性,同时对CI也有一定功效

(1)记录了数据修改之前的状态
(2)rollback 将内存的数据修改恢复到修改之前
(3)在CSR中实现未提交数据的回滚操作
(4)实现一致性快照,配合隔离级别保证MVCC,读和写的操作不会互相阻塞

锁

实现了事务之间的隔离功能,InnoDB中实现的是行级锁，MyISAM实现的是表级锁。
row-level lock  （行级锁定，默认50s超时，50s后就自动释放了）
gap （）
next-lock

实现行级锁
开启两个会话确保autocommit都是关闭的

image.png

use到同一个库

image.png

只有commit提交后另一个窗口才会进行更改，对同一行操作进行锁定，

隔离级别

查看隔离级别：默认隔离级别RR
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
修改隔离级别：
vim /etc/my.cnf
transaction_isolation=read-uncommitted
transaction_isolation=read-committed
transaction_isolation=REPEATABLE-READ

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

MVCC   --->   undo 快照

RU  会出现脏读 , 
RC 会出现不可重复读 ,也会出现幻读.
RR 通过MVCC基础解决了不可重复读,但是有可能会出现幻读现象

在RR模式下,GAP和Next-lock进行避免幻读现象,必须索引支持

10，InnoDB核心参数

存储引擎默认设置

default_storage_engine=innodb

表空间模式

innodb_file_per_table=1

共享表空间文件个数和大小

innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

"双一" 标准的其中一个

innodb_flush_log_at_trx_commit=1
简介：

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

``

Innodb_flush_method=(O_DIRECT, fsync) 

作用: 控制的是 Redo buffer  和 buffer pool
fsync    : 
O_DIRECT : 建议模式
O_DSYNC  :

最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT

最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync

redo日志设置有关的

innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3

脏页刷写策略

innodb_max_dirty_pages_pct=75

还有哪些机制会触发写磁盘?
CSR 
redo满了