MySQL存储与引擎

存储引擎，表处理器，表类型；

参考博客：1 2

引擎

MySQL提供不同的引擎类型，对不同的表进行存储和管理。表的存储到数据库中，最后落入文件或者内存、或者根据不同的业务是否需要事务管理，或者支持的索引等等功能，选择不同的存储引擎，满足不同的效率要求等。

不同的存储引擎提供额外的效率、功能等，对不同需求的表进行管理存储，提升不同的效益。

可以选择适用不同服务器、数据库和表格的存储引擎。在选择怎么存储（内存？文件），怎么索引（全文？哈希？其他？），存什么样的数据（文本？csv？），支不支持联机事务处理等；提供不同的需求，不同的性能和灵活性。

数据库引擎是用于存储、处理和保护数据的核心服务。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

类型

• InnoDB
• MyISAM
• MRG_MYISAM
• MEMORY
• CSV
• ARCHIVE
• BLACKHOLE
• PERFORMANCE_SCHEMA
• FEDERATED

操作

1. 查看

   • SHOW VARIABLES LIKE '%storage_engine%

   • SHOW ENGINES\G;

   • INFORMATION_SCHEMA.ENGINES表

2. 修改

   • 安装环境的配置 my.cnf -----修改默认存储引擎字段
   • 创建表/修改表/时：CREATE TABLE XX ...ENGINE=InnoDB
   • 查看：show create table xx

3. 其他

InnoDB

MySQL默认的存储引擎（V5.6之后）。一个健壮的事务型存储引擎------操作非常大的数据量，提供行级锁定和外键约束。一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

特点：

• 提供ACID的事务性支持，实现了四种隔离级别（无操作、脏读、重复读、幻度）。
• 行级锁定和外键约束，满足处理大容量数据库系统的目标
• 从V5.6开始支持全文索引
• 系统崩溃后可以自动恢复
• 外键和引用完整性支持，包括级联删除和更新; 基于行级别的锁定和多版本化，良好的并发性能

适用：

• 经常更新的表，适合多重并发请求
• 事务操作
• 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
• 外键约束
• 列自增，auto_increment

MyISAM

MyISAM表是独立于操作系统的，可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。例如，我建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：tb_demo.frm，存储表定义；tb_demo.MYD，存储数据；tb_demo.MYI，存储索引。

如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

特点：

• 不支持事务，没有行级锁和外键-----插入和更新操作会锁定全表，性能低
• 使用一种表格锁定机制，实现并发（牺牲空间）
• 强调快速读取，不支持灾难恢复

适用：

• 选择密集的表：筛选大量数据时性能优
• 更新密集的表：MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

MRG_MyISAM

是一个相同的可以被当作一个来用的MyISAM表的集合，所有的表有同样的列（名字、类型）和索引信息。和旧版本的MERGE 是同一种来源。对一个MERGE表操作其实就是对其中的MyISAM表进行操作。

适用：

• 服务器日志，多个相同表的数据
• MERGE表是在MyISAM表的基础上进行定义的，就如同视图那样；删除不会影响原表数据。alter table tb_merge engine=merge union(tb_log1) insert_method=last;
• 上述定义中，union表示关联哪些表，insert_method 表示插入数据的方法，取值域为[0,FIRST,LAST]；分别表示不允许插入、插入第一个表和插入最后一个表

MEMORY

在内存中创建表，性能高。要求存储在表中的数据使用的是长度不变的格式，默认使用Hash索引。

特点：

• 访问存取效率高，内存访问
• 数据容易丢失，关机则失去
• 支持哈希索引和B树索引
• [Added]B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

适用：

• 目标数据较小，频繁访问使用的数据（类似缓存机制）；可以通过参数max_heap_table_size控制Memory表的大小
• 临时数据
• 数据丢失对使用环境没有影响

BLACKHOLE

任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空。他会丢弃所有的插入的数据，服务器会记录下Blackhole表的日志，所以可以用于复制数据到备份数据库。

适用：

• 充当日志服务器
• 验证dump file语法的正确性？？

ARCHIVE

Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

特点：

• 行级锁，不支持事务

---

小结&对比

【表参考链接：https://github.com/jaywcjlove/mysql-tutorial/blob/master/chapter3/3.5.md】

特性	InnoDB	MyISAM	MEMORY	ARCHIVE
存储限制(Storage limits)	64TB	No	YES	No
支持事物(Transactions)	Yes	No	No	No
锁机制(Locking granularity)	行锁	表锁	表锁	行锁
B树索引(B-tree indexes)	Yes	Yes	Yes	No
T树索引(T-tree indexes)	No	No	No	No
哈希索引(Hash indexes)	Yes	No	Yes	No
全文索引(Full-text indexes)	Yes	Yes	No	No
集群索引(Clustered indexes)	Yes	No	No	No
数据缓存(Data caches)	Yes	No	N/A	No
索引缓存(Index caches)	Yes	Yes	N/A	No
数据可压缩(Compressed data)	Yes	Yes	No	Yes
加密传输(Encrypted data[1])	Yes	Yes	Yes	Yes
集群数据库支持(Cluster databases support)	No	No	No	No
复制支持(Replication support[2])	Yes	No	No	Yes
外键支持(Foreign key support)	Yes	No	No	No
存储空间消耗(Storage Cost)	高	低	N/A	非常低
内存消耗(Memory Cost)	高	低	N/A	低
数据字典更新(Update statistics for data dictionary)	Yes	Yes	Yes	Yes
备份/时间点恢复(backup/point-in-time recovery[3])	Yes	Yes	Yes	Yes
多版本并发控制(Multi-Version Concurrency Control/MVCC)	Yes	No	No	No
批量数据写入效率(Bulk insert speed)	慢	快	快	非常快
地理信息数据类型(Geospatial datatype support)	Yes	Yes	No	Yes
地理信息索引(Geospatial indexing support[4])	Yes	Yes	No	Yes