mysql存储引擎机制_Mysql——存储引擎

简介

Mysql创建、查询、更新和删除数据操作都依赖于存储引擎，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎获得额外的速度或者功能。

主要存储引擎

查看mysql(5.7)支持的存储引擎

通过show engines;命令可以看到：

mysql支持InnoDB(Mysql5.5.5，之后默认的存储引擎)，MyISAM，MRG_MYISAM，MEMORY，ARCHIVE，CSV，BLACKHOLE等存储引擎。

修改存储引擎

全局修改配置文件my.cnf(永久)：default-storage-engine=CSV

全局命令修改(临时，重启后失效)：SET default_storage_engine=CSV;

修改某个表：ALTER TABLE 表名 ENGINE = CSV;

主要存储引擎对比

MYISAM

MyISAM是MySQL5.5.8之前默认储存引擎。

文件格式：

表定义：*.frm；

表数据文件：*.MYD；

表索引文件：*.MYI；

优点：

只缓存索引，占用内存少；

精确记录表行数，count查询速度极快；

锁级别为表锁，表锁优点是开销小，加锁快；

可被压缩，存储空间较小

缺点：

不支持事务；

不支持外键；

表锁粒度大，发生锁冲动概率较高，并发能力低；

宕机后，MyISAM表易损坏，灾难恢复性差

适用场景：

做很多count的计算；

并发不高；

极少修改，例如存储用户登录日志；

不需要事务的；

数据安全要求低。

INNODB(推荐)

INNODB是MySQL5.5.8版本开始就是默认储存引擎，适用绝大多数场景。

文件格式：

表定义：*.frm；

表数据、索引文件：*.ibd；

优点：

支持事务；

支持外键；

可靠性高，灾难恢复性好；

使用行级锁，支持mvcc,并发能力强；

默认RR隔离级别，提供next-key loking避免幻读；

缓存数据和索引，还提供变更缓冲，二次写，自适应哈希索引等高效的缓存特性；

缺点：

服务器资源开销大；

不记录表行数，count查询会做全表扫描；

适用场景：

频繁修改，并发要求高；

支持事务，外键；

数据安全要求高。

MEMORY

也叫HEAP存储引擎，数据存储在内存，如果MySQL服务重启数据会丢失，但是表结构会保存下来。

优点：

由于数据存储在内存，显著提高访问性能；

缺点：

不支持事务；

不支持外键；

表锁粒度大，发生锁冲动概率较高，并发能力低；

所有字段都为固定长度，不支持 BLOB 和 TEXT 等大字段

服务重启数据会丢失，不支持灾难恢复

适用场景：

小表，查询性能要求高；

临时表；

ARCHIVE

Archiv存储引擎，只支持insert和select操作，不支持索引，主要用于数据归档。

文件格式：

表定义：*.frm；

表数据文件：*.arz；

优点：

只允许插入和查询，不允许修改和删除，可以实现高并发的插入；

用zlib对表的数据进行压缩，相比MyISAM会更加节约磁盘IO，数据量大时，依然较好的插入性能。

缺点：

功能单一，只是提供高速的插入和压缩功能；

适用场景：日志和数据采集类应用

详细的存储引擎对比表

特点MYISAMINNODBMEMORYARCHIVE存储限制无64TB有无

事务√

锁机制表锁行锁表锁行锁

外键√

MVCC√√

B-Tree索引√√√

Hash索引√√

全文索引√√

集群索引√

数据缓存√√

索引缓存√√√

数据压缩√√

存储空间低高N/A非常低

内存空间低高中等低

批量写入效率高低高非常高

复制√√√√

备份/时间点恢复√√√√

地理信息√

总结

绝大多数场景下，使用默认INNODB都是正确的选择。除非需要用到某些INNODB不具备的特性，并且没有其他办法可以代替，那可以考虑使用其他存储引擎。

除非万不得已，否则不建议混合使用多种存储引擎，不然可能会带来一系列复杂的问题，以及一些潜在的bug和边界问题，例如在一个事务里面同时，插入两张表A(MYISAM)、B(INNODB)数据，表A数据先插入成功，接着表B插入失败回滚，就无法保证数据一致性了。混合存储对一致性备份和服务器参数配置也带来了一些麻烦。