|
一、什么是存储引擎：
数据库存储引擎是数据库底层软件组织，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。MySql的核心就是存储引擎，存储引擎是基于表的，而非数据库。
（1）查看Mysql的存储引擎信息：
mysql > show engines;
查询结果：
Support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎 。
二、常用的存储引擎介绍：
1、InnoDB存储引擎：
InnoDB存储引擎为MySQL表提供了提交、回滚、ACID事务支持、系统崩溃修复能力的事务安全；多版本并发控制的行级锁，提高多用户并发操作的性能；支持主键自增长，自增长列的值不能为空；支持外键完整性约束。该引擎在5.5后的MySQL数据库中为默认存储引擎。InnoDB引擎是处理巨大数据量的最大性能设计，它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的。InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。
2、MyISAM存储引擎：
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存储引擎：
该存储引擎通过在内存中创建临时表来存储数据。每个基于该存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该磁盘文件只存储表的结构，而其数据存储在内存中，所以使用该种引擎的表拥有极高的插入、更新和查询效率。这种存储引擎默认使用Hash 索引，也可以使用B树型索引。由于这种存储引擎所存储的数据保存在内存中，所以如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。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、锁级别：
（1）MyISAM：只支持表级锁，用户在执行select，update，delete，insert 语句时，都会给表自动加锁，如果加锁以后的表满足insert并发的情况，可以在表的尾部插入新的数据。
（2）InnoDB：支持行级锁和表级锁，默认使用行级锁。但是InnoDB的行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，innodb是检测不到的。只能依靠锁定超时来解决。
3、表主键与外键约束：
（1）MyISAM：允许没有任何索引和主键的表存在。不支持外键。
（2）InnoDB：支持主键自增长列且改列不能为空，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)。支持外键完整性约束。
4、索引结构：
（1）MyISAM：使用B+Tree索引，主键索引和辅助索引的Data域都是保存行的地址。
（2）InnoDB：使用B+Tree索引，但是和MyISAM的具体实现上有些不同，主键索引的Data域并不是保存行的地址，而是保存的是该行的所有数据，而辅助索引的Data域保存的则是主索引的值。
5、全文索引：
MyISAM支持FULLTEXT类型的全文索引，InnoDB不支持全文索引（5.6版本之后InnoDB存储引擎开始支持全文索引，5.7版本之后通过使用ngram插件开始支持中文）
6、存储空间：
（1）MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
（2）InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
7、存储结构：
（1）MyISAM：每个MyISAM在磁盘上存储成三个文件。.frm，存储表定义；.MYD(MYData)，存储数据；.MYI(MYIndex)，存储索引 。
（2）InnoDB：把数据和索引存放在表空间里面，所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
8、表的具体行数：
（1）MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值，不需要进行全表扫描。
（2）InnoDB：没有保存表的总行数，如果使用select count() from table；需要会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
9、AUTO_INCREMENT：
（1）MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
（2）InnoDB：InnoDB中必须包含AUTO_INCREMENT类型字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
10、CURD操作：
（1）MyISAM：如果执行大量的SELECT，MyISAM是更好的选择；在Delete表时，先drop表，然后重建表。
（2）InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，因此，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
11、可移植性、备份及恢复：
（1）MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
（2）InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
四、存储引擎的选择：
不同的存储引擎都有各自的特点，以适应不同的需求。使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。
如下表所示：
功 能
MYISAM
Memory
InnoDB
Archive
存储限制
256TB
RAM
64TB
None
支持事务
No
No
Yes
No
支持全文索引
Yes
No
No
No
支持数索引
Yes
Yes
Yes
No
支持哈希索引
No
Yes
No
No
支持数据缓存
No
N/A
Yes
No
支持外键
No
No
Yes
No
（1）如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择；
（2）如果数据表主要用来插入和查询记录，读操作远远多于写操作且不需要数据库事务的支持，则MyISAM引擎能提供较高的处理效率；
（3）如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果；
（4）如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive；
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。
五、存储引擎的操作：
在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
1、查看数据库默认使用哪个引擎，使用命令：
show variables like 'storage_engine';
查询结果为：
2、设置默认的存储引擎：
（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，再导入的方式。
(责任编辑：IT) |