mysql 之存储引擎

1. 简介

类似Linux的文件系统,比文件系统要高级.

2. MySQL 存储引擎类型(笔试:3-4种)

show engines; ##查询系统支持的存储引擎
select @@default_storage_engine; ##查询 默认的存储引擎
show variables like '%engine%'; ##模糊查找存储引擎

InnoDB  (5.5以后默认存储引擎) *****
MyISAM  (5.5 以前的默认存储引擎) ***
CSV   **
MEMORY **
BLACKHOLE **
FEDERATED (Oracle(dblink) ----> MySQL)**
第三方:
TokuDB 
MyRocks 
RocksDB

TokuDB优势:
1. 压缩比高
2. 插入性能很高

项目案例:

环境: zabbix 3.2 , mariaDB 5.5 , centos 7.3
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :

1. zabbix 版本
2. 数据库版本
3. zabbix数据库500G,存在一个文件里
   优化建议:

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

优化结果:
监控状态良好

为什么?

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

3. InnoDB 与 MyISAM 的区别(笔试题)

1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safety Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL ) 
7. 索引 B+tree B*TREE

4. 小项目:X送

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMPT),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:

1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据

职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:

1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数 

案例:

环境: 贵州X交管系统,违章信息,MySQL5.7 , 70多张表.
备份策略: 每周六 mysqldump全备,每天binlog备份
问题描述: 
        在网上找了个参数调整ibtmp1大小,直接再接在生产中修改,由于手误,删除了ibdata1文件.
        数据库无法启动. 
解决思路: 
        1. mysqldump全备(周六)+日志(7,1,2,3,4)恢复
中间出的问题:
        1.全备有问题,经过调整,好用了.
        2. binlog日志只有2,3,4三天日志
换解决方案:  表空间迁移
        我们有什么? 
            1. 上周六的完整数据(完整表结构)
            2. 周四宕机时的ibd

演练:

        1. 搭建一个临时库
        [root@db01 /data/3306/data]# mysql -S /data/3307/mysql.sock
        2. 恢复上周六的备份到临时库
        oldguo[world]>source /root/world.sql
        3. 将测试库中ibd清掉
        [root@db01 /data/3306/data]# vim /data/3307/my.cnf 
        secure-file-priv=/tmp
        [root@db01 /data/3306/data]# systemctl restart mysqld3307.service 
        oldguo[(none)]>select concat("alter table world.",table_name," discard tablespace;") from information_schema.tables where table_schema='world' into outfile '/tmp/discard.sql';
        oldguo[(none)]>set foreign_key_checks=0;
        oldguo[(none)]>source /tmp/discard.sql
        4. 将故障库ibd文件,拷贝指定位置
        [root@db01 /data/3306/data/world]# cp *.ibd /data/3307/data/world/
        [root@db01 /data/3306/data/world]# chown -R mysql.mysql /data/
        5. 将ibd进行import 
        oldguo[(none)]>select concat("alter table world.",table_name," import tablespace;") from information_schema.tables where tablee_schema='world' into outfile '/tmp/import.sql';
        oldguo[world]>source /tmp/import.sql

5. 存储引擎查看

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

SELECT @@default_storage_engine;

5.2 存储引擎(不代表生产操作)

会话级别:
set default_storage_engine=myisam;
全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.

如果要永久生效:

写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam

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

5.3 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G;  ##查询建表语句
SHOW TABLE STATUS LIKE 'CountryLanguage'\G  ##查询表的状态信息

5.4 INFORMATION_SCHEMA 确认每个表的存储引擎

[world]>select table_schema,table_name ,engine 
from information_schema.tables 
where table_schema not in ('sys','mysql','information_schema','performance_schema');
Master [world]>show table status;
Master [world]>show create table city;

5.5 修改一个表的存储引擎

db01 [oldboy]>alter table t1 engine innodb;

注意：此命令我们经常使用他，进行innodb表的碎片整理

5.6 平常处理过的MySQL问题--碎片处理

环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式

5.6 扩展:如何批量修改

需求:将zabbix库中的所有表,innodb替换为tokudb

select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';

6. InnoDB存储引擎物理存储结构

6.1 InnoDB 最直观的存储方式

city.frm   表的列定义
city.ibd   表的数据和索引    *****  
ibdata1(5.7)  共享表空间文件(UNDO回滚数据(8.0独立),系统数据字典)  *****
ib_logfile0 ~ ib_logfileN   redo log文件    *****
ibtmp1(5.7)     存放临时表
ib_buffer_pool  缓冲区池的映射文件

6.2 InnoDB 的表空间管理模式介绍

共享表空间模式(5.5 默认)
ibdata1:目前遗留下来了,用来存储系统数据.

=============================================
5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以，共享表空间保留，只用来存储:系统数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了
=============================================

独立表空间模式(5.6以后默认)
一个表一个ibd文件

6.3 共享表空间的设置

oldguo[world]>select @@innodb_data_file_path;   
一般是在初始化数据之前:
vim /etc/my.cnf 
innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend
oldguo[(none)]>show variables like '%extend%';

6.4 独立表空间设置

oldguo[(none)]>select @@innodb_file_per_table;

共享表空间体验(不代表生产操作):

oldguo[(none)]>set global innodb_file_per_table=0;

6.5 独立表空间迁移

alter table t1 discard tablespace;
alter table t1 import  tablespace;
InnoDB表 :  ibdata1   +  frm   +  ibd 

6.6 真实的学生案例

案例背景:

硬件及软件环境:
联想服务器（IBM） 
磁盘500G 没有raid
centos 6.8
mysql 5.6.33  innodb引擎  独立表空间
备份没有，日志也没开

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

故障描述:

断电了，启动完成后“/” 只读
fsck  重启,系统成功启动,mysql启动不了。
结果：confulence库在  ， jira库不见了

学员求助内容:

求助：
这种情况怎么恢复？
我问：
有备份没

求助：
连二进制日志都没有，没有备份，没有主从
我说：
没招了，jira需要硬盘恢复了。
求助：
1、jira问题拉倒中关村了

2、能不能暂时把confulence库先打开用着
将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

问：有没有工具能直接读取ibd
我说：我查查，最后发现没有

我想出一个办法来：
表空间迁移:
create table xxx
alter table  confulence.t1 discard tablespace;
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/discad.sql'

source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功。主外键关系
很绝望，一个表一个表分析表结构，很痛苦。

set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。

3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';

4、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）

7. InnoDB核心特性--事务（Transaction）

7.0 简介

事务:保证在一个完整业务逻辑中,所有涉及到的语句,要么全成功,要么全失败.

7.1 ACID 特性

Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）
事务之间不相互影响。
Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

7.2 事务的生命周期管理(控制语句)

7.2.1 开启事务

begin; 
或者: 
start transaction;
7.2.2 标准的事务语句(DML: insert update delete)
oldguo[world]>delete from city where id>1000;
oldguo[world]>delete from city where id<500;

7.2.3 事务的结束

(1) rollback; 回滚

oldguo[world]>begin;
oldguo[world]>delete from city where id>1000;
oldguo[world]>delete from city where id<500;
oldguo[world]>rollback;

(2) commit ;

oldguo[world]>begin;
oldguo[world]>delete from city where id>1000;
oldguo[world]>delete from city where id<500;
oldguo[world]>commit;

7.2.4 自动提交功能

select @@autocommit;  ##查询事务自动提交状态
set  autocommit=0;   ##设置事务状态对当前会话
set global autocommit=0;  ##设置事务状态对所有会话
vim /etc/my.cnf   ##永久生效
autocommit=0

begin;
axxx
axxx
axxx
axxx
commit;

7.2.5 隐式提交的语句

用于隐式提交的 SQL 语句：

begin;
SET AUTOCOMMIT = 1

导致提交的非事务语句：

DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

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

TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

1. InnoDB 事务的ACID如何保证?

1.0 一些概念

redo log        : 重做日志, 记录数据页的变化. ib_logfile0,ib_logfile1
redo log buffer : redo log 的缓冲区(内存)
ibd             : 表空间的数据文件,以段区页方式规划存储数据行和索引
buffer pool     : 数据页缓冲区
LSN             : Log seq no 日志序列号,redo log\logbuffer\ibd\buffer pool
WAL             : write ahead log 日志优先写磁盘
脏页            : dirty page 在内存中被修改的数据页
CKPT            : checkpoint,将内存脏页回写到磁盘的动作
TXID            : transaction_id,事务ID,伴随着事务的整个生命周期.
undo log        : 回滚日志,ibdata1

1.1 redo 功能

1.1.1 redo简介

重做日志, ib_logfile0,ib_logfile1

1.1.2 记录的内容

记录数据页的变化

1.1.3 作用

主要 保证ACID 中的 "D",持久化功能,对于AC也有相应的保证
加快了commit命令的速度,提高了事务的并发.
并且实现了,在MySQL Crash时,ACSR中前滚的功能.

1.2 undo 功能

1.2.1 undo简介

回滚日志, 5.7 存储在ibdata1 ,8.0 undo单独存储

1.2.2 undo记录的内容

记录逆操作

1.2.3 作用

主要保证了A的特性,CI也有相应的功能

1.3 隔离级别和锁机制

隔离级别
RU	读未提交，脏读，不可重复读，幻读
RC	读已提交，不脏读，有不可重复读，有幻读
RR	防止脏读，防止不可重复读(快照)，可能会出现幻读(gap间隙,next-lock下一键锁)
SR	串行化

1.3.1 功能

主要提供了ACID中的 "I" 隔离性的功能,C的功能受到一定影响

1.3.2 隔离级别介绍

oldguo[(none)]>select @@tx_isolation;
oldguo[(none)]>select @@transaction_isolation;
oldguo[(none)]>show variables like '%tx%';
vim /etc/my.cnf 
transaction_isolation=REPEATABLE-READ  ##重复读
transaction_isolation=READ-COMMITTED  ##读取提交

(1) RU : 读未提交

transaction_isolation=READ-UNCOMMITTED

脏读.
不可重复读.
幻读.
这种情况下为提交的事务，也会被查到

(2) RC : 读已提交

transaction_isolation=READ-COMMITTED
A:  
mysql -uroot -p
use world 
1. oldguo[world]>begin;
2. oldguo[world]>update city set population=1000 where id=10;
4. oldguo[world]>commit;

B:
mysql -uroot -p
use world 
3. select * from city where id=10;
5. select * from city where id=10;

防止脏读.
会出现,不可重复读.
会出现,幻读.

A: 
create table t1 (id int ,name varchar(20));
insert into t1 values(1,'a'),(2,'b'),(4,'c'),(6,'d');
commit;
B:

(3) RR : 可重复读

防止脏读
防止不可重复读 (MVCC,一致性快照读)
可能会出现幻读 (GAP ,next-lock)

(4) SR : 可串行化

小结:

隔离级别: 
    RU : 读未提交,脏读,不可重复读,幻读
    RC : 读已提交,不脏读,有不可重复读,有幻读
    RR : 
    防止脏读
    防止不可重复读(快照)
    可能出现幻读(gap间隙锁,next-lock下一键锁) 
    SR : 串行化

1.3.3 InnoDB 的锁 (主要解决I) 事务之间不相互影响

record lock  记录锁,行级锁
gap 间隙锁
next-lock 下一键锁

2. InnoDB 核心参数介绍

2.1 默认存储引擎

oldguo[(none)]>select @@default_storage_engine;
Percona :  XtraDB  默认
MariaDB :  InnoDB  默认 myrocks  tokudb

2.2 innodb_buffer_pool_size ******

innodb缓冲池大小

oldguo[(none)]>show variables like '%pool%';
vim /etc/my.cnf 
innodb_buffer_pool_size=2G

官方建议: 最多95%
生产建议: 不超过80% , 50-70%

1. 业务够用的
2. 公司硬件有预留
3. MySQL还有可能额外使用到其他的内存结构
4. 我们公司做的多实例
   oldguo[(none)]>show engine innodb status \G

2.3 innodb_log_buffer_size *****

innodb日志缓冲区大小

oldguo[(none)]>show variables like '%log_buffer%';
oldguo[(none)]>select @@innodb_log_buffer_size;

2.4 redo的参数 *****

oldguo[(none)]>show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |  
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |  ##innodb日志文件大小
| innodb_log_files_in_group | 2        |   ##innodb 日志文件个数
+---------------------------+----------+
-rw-r----- 1 mysql mysql 50331648 Aug 14 20:54 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 12 11:58 ib_logfile1

2.5 relog的刷写策略("双一") *****

oldguo[(none)]>select @@innodb_flush_log_at_trx_commit;

默认:
1,在每个事务提交时,立即刷写redo buffer中日志到OS buffer ,立即FSYNC到磁盘 重要
0,每秒,刷写redo buffer中日志到OS buffer,每秒SYNC到磁盘
2,在每个事务提交时,立即刷写redo buffer中日志到OS buffer,每秒FSYNC到磁盘

英文文档

--------
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.
-------

2.6 innodb_flush_method

select @@innodb_flush_method;

作用: 控制 buffer pool ,和 redo buffer的刷盘策略
fsync
o_direct
o_dsync
O_DIRECT :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC :日志缓冲区写磁盘,不走 OS buffer

最高安全模式

innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT

最高性能:

innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync