MySQL存储引擎

今天写写对于MySQL数据库的存储引擎的一些理解，作为MySQL数据库核心特色的存储引擎，目前存在很多类型的存储引擎，各有各的特色以及应用场景。

#存储引擎：可理解为一种数据的存取与处理的方式

#查看当前支持的存储引擎
show engines;

#MEMORY存储引擎：在数据库路径下只有一个.frm结尾的物理文件，只包含定义，数据存储与内存中。
create table table_name(col col_type) engine=memory; #memory表的大小由max_heap_table_size参数决定，删除数据也不会回收已分配给memory表的内存，想要改变memory表的大小，在创建语句之前设置一下max_heap_table_size参数即可
set max_heap_table_size=1024*1024 #设置为1M
create table ... #数据库服务重启之后所有的memory表大小继承全局设置max_heap_table_size
#通过建表的max_rows选项指定memory表的大小
create table table_name () max_rows=row_num;

#CSV存储引擎：基于CSV格式存储数据，在database目录下有一个.frm结尾的物理文件存储表定义，.CSV结尾的文件存储数据(可直接打开操作数据)，.CSM保存表的状态与数据量。
强制要求所有的列必须指定为not null,且不支持索引与分区。
create table t_csv1 (id int not null,name varchar(2) not null) engine=csv;
手动在.CSV数据文件中插入记录之后，可执行repair语句进行重载
repair table t_csv1;
重载之后可执行check语句检查表数据是否有坏数据导致表损坏
check table t_csv1;
表数据损坏之后执行repair语句可进行修复，只能修复至出错的位置，数据文件此位置之后的数据将全部被清理(谨慎操作)
repair table t_csv1; #执行完check之后再对表做DML操作包括select也会有此效果。。。感觉mysql搞得好随意

#ARCHIVE存储引擎：采用zlib算法对插入的数据做压缩操作，还可以用optimize table对表进行分析并进一步压缩，.frm文件存储表定义，.ARZ文件为数据文件，优化时可能有一个.ARN的文件,只支持insert与select操作，不支持索引，插入效率高，占用空间小，常用于归档。
create table t_arv1 engine=archive as select * from information_schema.columns;
#报错：ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
查看参数 enforce_gtid_consistency的值
show variables like '%gtid_consistency'; #为true时表示MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table ... select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。
修改参数文件中的值(想要开启gtid_mode模式enforce_gtid_consistency必须打开)重启数据库服务生效
vi /etc/my.cnf
gtid_mode = off
enforce_gtid_consistency = 0
对表进一步压缩
optimize table t_arv1;

#blackhole存储引擎：只入不存，物理文件只有.frm的表定义文件，不存储数据,插入再多的数据表大小都不变。
create table t_bkh1 (id int,name char(8)) engine=blackhole; #这玩意支持索引，支持事务。。。但是事务回滚不记录到binlog。在启用了binlog的情况下，该引擎的表虽然不保存数据，但在binlog中记录了执行的sql语句，利用该特性可通过一个中转slave实现让主从产生不同的数据(比如去除敏感数据)；用于发现、评估与存储引擎无关的性能瓶颈(比如评估开启binlog对数据库负载的影响)

#MERGE存储引擎：也称为MGR_MyISAM存储引擎，将一组MyISAM表聚合在一起，使用起来相当于union all以此来简化查询操作。
要求基表拥有相同的表结构(列的定义、顺序以及索引的顺序)
create table t_mis1 (id int,name char(2)) engine=myisam;
create table t_mis2 (id int,name char(2)) engine=myisam;
create table t_mrg1 (id int,name char(2)) engine=merge union=(t_mis1,t_mis2); #这玩意不存储数据(.frm文件存储定义，.MRG文件存储数据来源)但支持做DML语句，不过插入数据时需设置INSERT_METHOD参数(NO、FIRST、LAST),默认为NO即不可以插入。
alter table t_mrg1 insert_method=first; #数据插入第一张表

#FEDERATED存储引擎：相当于oracle的dblink，用于访问远端数据库实例的表数据。.frm文件存储定义，本地不保存数据，也就是说每次查询数据都需要进行传输。
MySQL默认不支持，需要自己进行安装启用。
vi /etc/my.cnf
federated #新增federated引擎支持，重启服务生效

关于重头戏innodb存储引擎，作为MySQL5.5版本以来的默认存储引擎，需要学习的东西太多，后面再慢慢写吧。