​大家好，我是anyux。本文介绍MySQL存储引擎。

简介

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

功能

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

引擎类别

 show engines; 引擎类型如下 CSV               MRG_MYISAM         MyISAM             BLACKHOLE         PERFORMANCE_SCHEMA MEMORY             ARCHIVE           InnoDB             FEDERATED        

必须要了解引擎类型

InnoDB,MyISAM,MEMORY,CSV

MySQL存储引擎从5.5以后默认使用的是InnoDB,从8.0以后就放弃了MyISAM

另外MySQL还支持插件式开发存储引擎。比较出名的存储引擎有RocksDB,MyRocks,TokuDB。这三个存储引擎压缩比较高，数据插入性能高，其他功能与InnoDB无差别，所以比InnoDB优秀，属于第三代存储引擎

PerconaDB使用的是XtraDB

MariaDB:默认使用是InnoDB,MySQL的InnoDB和这个是不一样的

InnoDB存储引擎核心特性说明

区别InnoDBMyISAM事务支持不支持行锁支持不支持MVCC支持不支持外键支持不支持ACSR自动故障恢复支持不支持热备支持不支持复制(多线程，GTID,MTS)支持不支持

存储引擎替换

InnoDB替换MyISAM

环境：CentOS5.8 MySQL5.0版本，MyISAM存储引擎，网站业务(LNMP),数据量50G左右

问题：业务压力大时，非常卡，经历过宕机，会有部分数据丢失

问题分析：

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

监测状态

监控锁的状态，有很多的表锁等待存储引擎查看：所有表默认是MyISAM

解决方案

升级到MySQL 5.6版本。注意，低版本数据库要先升级到5.5，再升级到5.6。直接升级到5.6会出现服务无法启动的情况表数据迁移开启双1安全参数查看存储引擎

创建表时，如果没有指定存储引擎，则使用默认的存储引擎

 打印所有支持的存储引擎 show engines; 查看当前的存储引擎 select @@default_storage_engine;

可以在配置文件中，设置存储引擎

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

查看自建的所有库表的存储引擎

 select table_schema as 数据库, group_concat(concat(" ",table_name,":",engine," ")) as 数据表 from information_schema.tables where table_schema not in('sys','mysql','information_schema','performance_schema') group by table_schema;

 alter table tmp_db.tmp_student engine=MyISAM; 再修改回InnoDB alter table tmp_db.tmp_student engine=InnoDB; 查看单表的存储引擎 show create table tmp_db.tmp_student;

在做delete删除操作时，会对表进行逐行删除数据，删除后原来所占用的空间，不会被立即释放，因此产生碎片。

在服务运行期间，这些碎片空间不会被服务回收，因此占用的无效空间相对变大，使用索引时会产生无效索引，进而影响服务的性能。

因此需要整理碎片。让数据行更加紧凑，让查询索引时性能变强。

环境：CentOS7.4,MySQL5.7,InnoDB存储引擎

业务特点：数据量级较大，经常需要按月删除历史数据

问题：磁盘空间占用很大，不释放

最直接的办法是，将表中所有数据导出，删除表，再重新导入到服务中。

这种方法就是有点折腾，而且需要业务停止后才方便操作。如果业务需要24小时运行，那就要使用到第二种方法了

对表进行按月分表(partition,中间件)，业务替换为truncate方式，定期进行碎片整理

批量替换存储引擎

 select concat("alter table ",table_schema,".",table_name," engine=toukudb;") as 批量替换语句 from information_schema.tables where table_schema='zabbix';

可以将这些语句，重定向到文件，使用shell脚本执行

MyISAM由三个文件构成，FRM,FYD,MYI。FRM用来存储表结构，FYD用来存储数据行，MYI用来存储索引

InnoDB物理结构

在5.6版本中，ibdata1包含存储系统元信息，undo表空间数据和临时表空间

在5.7版本中，文件结构与作用如下

文件结构名称系统数据字典信息(统计信息)，undo表空间数据ibdata1系统数据字典信息(元数据信息)，undo表空间数据ib_logfile0~ib_logfile1REDO日志文件，事务日志文件ibtmp1临时表空间磁盘位置，存储临时表frm存储表的列信息ibd表的数据行和索引

表空间

在5.7版本中下面两个参数长的很像，名字叫起来也相似，但完全不一样，需要重点牢记

ibdata1：整个库的统计信息和undo信息

ibd:数据行和索引信息

关于ibdata1是如何出现的，又是如何没落了。

虽然ibdata1和ibd是两个文件，但是在MySQL系统中将它们两个称为表空间(Tablespace)

现在我们使用磁盘，都是将一块300G的磁盘，格式化并且挂载到/data目录下，给MySQL服务使用。半年后，磁盘几乎被占满。此时又添加一块新的600G磁盘，格式化并且挂载到/data目录下，给MySQL服务使用。又半年后，又需要重新挂载磁盘，并且每次都需要停止数据库服务，那么有没有可能不重启也能实现数据在磁盘间的转移呢？

此时就使用到了表空间，表空间这个名词是来自于Oracle数据库。Oracle就是将数据库中的数据存储到表空间(Tablespace)，而在表空间内存，会分配数据存储到sdb1,sdc1等磁盘分区位置上，也就是说磁盘空间的增加对于数据库服务而言是透明的，是不可感知的

而对于MySQL到了5.5这个版本，已经被Oracle收购了。就将表空间的概念引入到MySQL中，MySQL也具备了表空间的管理模式。不足的是MySQL访问数据是基于文件系统去访问，表空间被迫改成了共享表空间，分别存储到各个磁盘下。

最初始时只要是个innodb引擎的表，都需要将数据行，索引，元信息和undo信息都存入到共享表空间，共享表空间再以不可见的形式存入到各大磁盘下，全都存入了ibdata1中。这样做的好处是可以实现类似Oracle的表空间管理模式，做一些扩容。问题是所有数据全部写入到几个有限的文件中，一旦数据多了，处理效率就会出现严重下降

所以到了5.6版本就改成了独立表空间模式，独立表空间解决了共享表空间数据"扎堆"的问题,解决方式是独立存储每一张表，每张表都自己的ibd,frm,见下图

需要知道现在共离表空间还在，只不在存储每张表的数据了，而是存储库的元信息和undo信息。而独立表空间存储数据行和索引信息

对于mariaDB和PerconaDB和5.5版本，需要更新，否则一旦数据量增长到一定量级，会导致性能下降

对于共享表空间(ibdata1~N)，自5.5版本以来，其负责的职责就被不同的程度的剥离

5.6版本以后，共享表空间得以保留，只用来存储：数字字典信息，undo信息，临时表

5.7版本以后，临时表被剥离

8.0版本以后，undo信息被剥离

查看共享表空间路径

 select @@innodb_data_file_path;

下图表示，第一次启动时创建共享表空间ibdata1,初始大小为12M,空间不够时，会自动增长64M的空间

使用du -sh 命令查看文件大小

 du -sh /data/mysql/data/ibdata1;

查看共享表空间默认每次自动增长值

 show variables like 'innodb_data_file_path'; show variables like '%extend%'

共享表空间参数的配置需要要创建数据库服务时配置，否则无法修改

独立表空间

从MySQL5.6版本开始，默认表空间已经变成了独立表空间，而不再是共享表空间

独立表空间主要用于存储用户数据

每一个表都有一个ibd文件，用于存储数据及索引信息

基本表结构元数据存储在frm文件

mysql数据表是由元数据，数据，索引构成

mysql数据库是由ibdata1~N和frm(元数据)，ibd文件(段区页)

一张InnoDB表由frm,ibd,ibdata1构成

查看独立表空间变量

 select @@innodb_file_per_table;

如果将innodb_file_per_table设置为0，那么以后创建的数据表，就没有了独立表空间了

修改独立表空间变量

 set global innodb_file_per_table=0;

不建议修改此变量，原因是改为0后，所以表将存储到共享表空间中，数据量增长后，容易导致数据库性能瓶颈

MySQL的存储引擎日志

Redo Log: ib_logfile0 ib_logfile1 重做日志

Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志

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

表空间迁移

参考

建立与原表一样的空表discard空表的ibd文件复制原表的ibd文件，修改权限，import原表的ibd文件查询验证

 alter table city dicard tablespace; alter table city import tablespace;