MySQL数据库：存储引擎

一、什么是存储引擎：

       存储引擎是MylSQL的核心，是数据库底层软件组织，数据库使用存储引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁级别、事务等功能。存储引擎是基于表的，而非数据库。

二、常用的存储引擎：

1、InnoDB存储引擎：

InnoDB是MySQL5.5版本之后的默认存储引擎，它是为了达到处理巨大数据量的最大性能而设计的，其CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的。

InnoDB支持事务、提供行级锁，每个表的主键不能为空且支持主键自增长，支持外键完整性约束；

2、MyISAM存储引擎：

不支持事务、也不支持外键，使用表级锁控制并发的读写操作，支持全文索引。MyISAM引擎强调快速读取操作，主要用于高负载的select，对事务完整性没有要求的应用可以用这个引擎来创建表。

MyISAM类型的表支持三种不同的存储结构：静态型、动态型、压缩型：

（1）静态型：指定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型）。使用静态格式的表的性能比较高，因为在维护和访问以预定格式存储的数据时，需要的开销比较低，但这种高性能是以空间为代价换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。

（2）动态型：如果列定义为动态的（xblob, xtext, varchar等数据类型），这时MyISAM就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变，则其位置很可能需要移动，这样就会导致碎片的产生，随着数据变化的增多，碎片也随之增加，数据访问性能会随之降低。

对于因碎片增加而降低数据访问性这个问题，有两种解决办法：

① 尽可能使用静态数据类型；

② 经常使用optimize table table_name语句整理表的碎片，恢复由于表数据的更新和删除导致的空间丢失。如果存储引擎不支持 optimize table table_name则可以转储并重新加载数据，这样也可以减少碎片；

（3）压缩型：如果在数据库中创建在整个生命周期内只读的表，则应该使用MyISAM的压缩型表来减少空间的占用，因为每个记录是被单独压缩的，所以只有非常小的访问开支。

3、Memory存储引擎：

Memory存储引擎通过在内存中创建临时表来存储数据。每个表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该磁盘文件只存储表的结构，而数据存储在内存中，所以使用该种引擎的表拥有极高的插入、更新和查询效率。由于所存储的数据保存在内存中，如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失。

默认使用Hash 索引，也可以使用B树型索引。

Memory存储引擎主要用于内容变化不频繁，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

4、Archive存储引擎：

Archive引擎提供了很好的压缩机制，它使用zlib压缩库，压缩比非常高，并且拥有高效的插入速度，支持insert、replace和select操作，但不支持update、delete，不支持事务，也不支持索引（5.5版本之后支持索引），所以查询性能较差一些，所以该适合用于做仓库使用和数据归档，存储大量独立的、作为历史记录的数据，如记录日志信息，因为他们不经常被读取。

5、Merge存储引擎：

Merge存储引擎是将一定数量的MyISAM表结构完全相同的表联合成一个整体，Merge表本身并没有数据，对Merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

6、Berkeley存储引擎：（BDB）

该存储引擎支持COMMIT和ROLLBACK等其他事务特性，支持页级锁。该引擎在包括MySQL 5.1及其以上版本的数据库中不再支持。   

7、CSV（Comma-Separated Values逗号分隔值）

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

8、Federated：

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

9、Cluster/NDB：

高冗余的存储引擎，该存储引擎用于多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大、安全和性能要求高的场景。 

10、BLACKHOLE（黑洞引擎）

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

11、PERFORMANCE_SCHEMA：

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

三、MyISAM 与 InnoDB 存储引擎的区别：

1、事务支持：MyISAM不支持事务处理，InnoDB支持事务处理。

2、锁级别：MyISAM只支持表级锁，InnoDB支持行级锁和表级锁，默认使用行级锁，但是InnoDB的行锁是通过给索引项加锁来实现的，即只有通过索引进行查询数据，InnoDB才使用行级锁，否则将使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。使用行锁可能会存在死锁的情况，但是表级锁不存在死锁。

3、表主键与外键约束：

（1）MyISAM：允许没有任何索引和主键的表存在。不支持外键。

（2）InnoDB：支持主键自增长列且主键不能为空，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)。支持外键完整性约束。

4、索引结构：MyISAM和InnoDB都是使用B+树索引，MyISAM的主键索引和辅助索引的Data域都是保存行的地址，但是InnoDB的主键索引保存的不是行的地址，而是保存该行的所有所有数据，而辅助索引的Data域保存的则是主索引的值。

5、全文索引：MyISAM支持FULLTEXT类型的全文索引，InnoDB不支持全文索引（5.6版本之后InnoDB存储引擎开始支持全文索引）

6、存储结构：

（1）MyISAM会在磁盘上存储成三个文件。

• .frm：存储表定义
• .MYD：存储数据
• .MYI：存储索引 

（2）InnoDB：把数据和索引存放在表空间里面，所有的表都保存在同一个数据文件中，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

7、存储空间：

（1）MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。

（2）InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

8、表的具体行数：

（1）MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值，不需要进行全表扫描。

（2）InnoDB：没有保存表的总行数，如果使用select count() from table；需要会遍历整个表，消耗相当大。

9、适用场景：

（1）如果需要提供回滚、崩溃恢复能力的ACID事务能力，并要求实现行锁级别并发控制，InnoDB是一个好的选择；

（2）如果数据表主要用来查询记录，读操作远远多于写操作且不需要数据库事务的支持，则MyISAM引擎能提供较高的处理效率；

四、存储引擎的操作：

1、查看Mysql的存储引擎信息：

mysql > show engines;

查询结果：

Support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎 。

2、查看数据库默认使用哪个引擎，使用命令：

show variables like ‘storage_engine’;

查询结果为：

3、设置默认的存储引擎：

（1）在MySQL的配置文件中（linux下为/etc/my.cnf），在mysqld后面增加default-storage-engine=INNODB即可。

或者在启动数据库服务器时在命令行后面加上–default-storage-engine或–default-table-type选项 。

（2）在创建表时指定存储引擎的类型：

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;

（3）修改现有的表使用的存储引擎：

ALTER TABLE mytable ENGINE = MyISAM;

缺点：

①这种转化方式需要大量的时间 和I/O，mysql要执行从旧表到新表的一行一行的复制，所以效率比较低；

②在转化这期间源表加了读锁；

③从一种引擎到另一种引擎做表转化，所有属于原始引擎的专用特性都会丢失，比如从innodb到 myisam 则 innodb的索引会丢失！

（4）导出再导入：如果表建立的时候是MyISAM，现在要更改整个数据库表的存储引擎，一般要一个表一个表的修改，比较繁琐，可以采用先把数据库导出，得到SQL，把MyISAM修改成INNODB，再导入的方式。

相关博客：https://blog.csdn.net/gaohuanjie/article/details/50944782