存储引擎

一、简介

相当于Linux文件系统，只不过比文件系统强大
查看引擎命令
show engines;
存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.

1.种类

InnoDB 在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV

2.InnoDB优点

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) ,多线程(Multi-Threads-SQL )

二、引擎替换案例

环境: zabbix 3.2    mariaDB 5.5  centos 7.3
现象 : zabbix卡的要死 ,  每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
1. zabbix 版本 
2. 数据库版本
3. zabbix数据库500G,存在一个文件里
优化建议:
1.数据库版本升级到5.7版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....
优化结果:
监控状态良好

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

三、存储引擎查看

1.select查看

SELECT @@default_storage_engine;
## 存储引擎(不代表生产操作)
会话级别:
set default_storage_engine=myisam;
全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.
如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

2.SHOW 确认每个表的存储引擎

SHOW CREATE TABLE City\G;
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

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

4.修改一个表的存储引擎

db01 [oldboy]>alter table t1 engine innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理。
此命令适合小表，不适合大表。这条命令很重要。

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

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

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';

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

1.最直观的存储方式(/data/mysql/data)

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

2.表空间

2.1共享表空间

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

2.2共享表空间设置

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
[(none)]>select @@innodb_data_file_path;
[(none)]>show variables like '%extend%';
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64

2.3独立表空间

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

MySQL的存储引擎日志：
Redo Log: ib_logfile0  ib_logfile1，重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
临时表:ibtmp1，在做join union操作产生临时数据，用完就自动

2.4 独立表空间设置问题

db01 [(none)]>select @@innodb_file_per_table;#查看是独立表模式还是共享表模式
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                      1 |            #1就是独立表模式，0就是共享表模式
+-------------------------+
alter table city dicard tablespace;    #删除表空间文件，只删ibd，元数据还在
alter table city import tablespace; #导入表空间文件

2.5 真实的案例

案例背景:
硬件及软件环境:
联想服务器（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）