MySQL存储引擎

前言

创建表的好坏直接影响着数据库。而在设计表的时候，我们都会关注一个问题，使用什么存储引擎，什么是存储引擎？

什么是存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。
这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。
通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

存储引擎其实就是如何存储数据，
如何为储存数据建立索引和如何更新数据等技术的实现方法因为关系型数据库中的数据是以表的形式储存的，
所以存储引擎也可以称为表类型

查看mysql存储引擎

mysql客户端中，使用以下命令可以查看MySQL支持的引擎

SHOW ENGINES

我是在命令行窗口执行的结果，版本为8.0.18

MySQL支持的存储引擎

MyISAM存储引擎

MySQL5.7默认的存储引擎，数据存储在ibd文件中，该文件还会存放相关的索引数据。
MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器。
每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表明。
例如，我建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：
1.tb_demo.frm，存储表定义；
2.tb_demo.MYD，存储数据；
3.tb_demo.MYI，存储索引。
。
特点：

支持事务，支持外键，因此数据的完整性、一致性更高。
支持行级别的锁和表级别的锁。
支持读写并发，写不阻塞读（MVCC）。
特殊的索引存放方式，可以减少 IO，提升查询效率。
适合经常更新的表或者存在并发读写或者有事务处理的业务系统。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

1.选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。
例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

InnoDB存储引擎(常用)

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
4.外键约束。MySQL支持外键的存储引擎只有InnoDB。
5.支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

MERGE存储引擎

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。

应用场景：对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。
例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

MEMORY存储引擎

EMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：

1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度

2、MEMORY存储引擎执行HASH和BTREE缩影

3、可以在一个MEMORY表中有非唯一键值

4、MEMORY表使用一个固定的记录长度格式

5、MEMORY不支持BLOB或TEXT列

6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）

8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享

9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

MEMORY存储引擎默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快，但也可以使用B树型索引。
由于这种存储引擎所存储的数据保存在内存中，所以其保存的数据具有不稳定性，比如如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。
现在mongodb、redis等NOSQL数据库愈发流行，MEMORY存储引擎的使用场景越来越少。

应用场景：适合做查询的临时表。

ARCHIVE存储引擎

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

场景：由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

CSV存储引擎

使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。
该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；
另外也不允许表中的字段为null。
csv的编码转换需要格外注意。

应用场景：这种引擎支持从数据库中拷入/拷出CSV文件。

BLACKHOLE存储引擎（黑洞引擎）

该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

PERFORMANCE_SCHEMA存储引擎

该引擎主要用于收集数据库服务器性能参数。
这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；
保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。
MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

Federated存储引擎

该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。

Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据，针对federated存储引擎表的查询会被发送到远程数据库的表上执行，本地是不存储任何数据的。

注意事项：
1、FEDERATED 表可能会被复制到其他的slave数据库,你需要确保slave服务器也能够使用定义在connection中或mysql.servers表中的link的用户名/密码 连接上远程服务器。

2、远程服务器必须是MySQL数据库

3、在访问FEDERATED表中定义的远程数据库的表前,远程数据库中必须存在这张表。

4、FEDERATED 表不支持通常意义的索引,服务器从远程库获取所有的行然后在本地进行过滤,不管是否加了where条件或limit限制。

–查询可能造成性能下降和网络负载,因为查询返回的数据必须存放在内存中,所以容易造成使用系统的swap分区或挂起。

5、FEDERATED表不支持字段的前缀索引

6、FEDERATED表不支持ALTER TABLE语句或者任何DDL语句

7、FEDERATED表不支持事务

8、本地FEDERATED表无法知道远程库中表结构的改变

9、任何drop语句都只是对本地库的操作,不对远程库有影响

存储引擎的一些问题

1.如何选择合适的存储引擎？
如果需要用不同引擎，考虑因素：

1.事务 如过需要事务支持，那么选择InnoDB（或XtraDB）。如果不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM可以选择。一般日志性应用比较符合这一特性。

2.备份 如果需要在线热备份，那么选择InnoDB是基本要求。

3.崩溃恢复 MyISAM崩溃后发生损坏的概率比InnoDB高很多，且恢复速度慢。

4.特有特性

常见场景选用合适的存储引擎：

日志性应用
这一类应用插入速度要求高，数据库不能成为瓶颈。
MyISAM或者Archive存储引擎对这种应用比较合适，因为开销低且插入速度快。

对记录日志做分析报表，生成报表的SQL可能导致插入效率明显降低。
一种解决方案是利用MyISAM内置的复制方案将数据复制一份到备库，然后在备库上执行耗时操作。
另一种方法是在日志记录表中包含年月信息，这样可以在已经没有插入操作的历史表上做频繁操作而不影响正在插入表的操作。

只读或大部分情况只读的表 对于读多写少场景，如果不介意崩溃恢复问题，MyISAM引擎合适。

订单处理 涉及订单处理，那么支持事务是必要选项。还要考虑存储引擎对外键的支持情况。InnoDB是订单处理类应用的最佳选择。

电子公告牌和主题讨论论坛

CD-ROM应用 如果要发布基于CD-ROm或DVD-ROM且基于MySQL的应用，可以考虑MyISAM表或MyISAM压缩表。

大数据量
大数据量下不建议使用MyISAM，如果数据量增长到10TB以上级别，可能需要建立数据仓库。Infobright是MySQL数据仓库比较好的解决方案，有的可能适合TokuDB（第三方引擎）。