InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
2. InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3. InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
4. InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5. InnoDB被用在众多需要高性能的大型数据库站点上，InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

InnoDB引擎的4大特性

1.插入缓冲（insert buffer)

插入缓冲（Insert Buffer/Change Buffer）：提升插入性能，change buffering是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效

只对于非聚集索引（非唯一）的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是为了减少随机IO带来性能损耗。

使用插入缓冲的条件：

• 非聚集索引
• 非唯一索引

Change buffer是作为buffer pool中的一部分存在。Innodb_change_buffering参数缓存所对应的操作：(update会被认为是delete+insert)

innodb_change_buffering，设置的值有：inserts、deletes、purges、changes（inserts和deletes）、all（默认）、none。

all: 默认值，缓存insert, delete, purges操作
none: 不缓存
inserts: 缓存insert操作
deletes: 缓存delete操作
changes: 缓存insert和delete操作
purges: 缓存后台执行的物理删除操作

可以通过参数控制其使用的大小：
innodb_change_buffer_max_size，默认是25%，即缓冲池的1/4。最大可设置为50%。当MySQL实例中有大量的修改操作时，要考虑增大innodb_change_buffer_max_size

上面提过在一定频率下进行合并，那所谓的频率是什么条件？

1）辅助索引页被读取到缓冲池中。正常的select先检查Insert Buffer是否有该非聚集索引页存在，若有则合并插入。

2）辅助索引页没有可用空间。空间小于1/32页的大小，则会强制合并操作。

3）Master Thread 每秒和每10秒的合并操作。

2.二次写(double write)

Doublewrite缓存是位于系统表空间的存储区域，用来缓存InnoDB的数据页从innodb buffer pool中flush之后并写入到数据文件之前，所以当操作系统或者数据库进程在数据页写磁盘的过程中崩溃，Innodb可以在doublewrite缓存中找到数据页的备份而用来执行crash恢复。数据页写入到doublewrite缓存的动作所需要的IO消耗要小于写入到数据文件的消耗，因为此写入操作会以一次大的连续块的方式写入

在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write
doublewrite组成：
内存中的doublewrite buffer,大小2M。
物理磁盘上共享表空间中连续的128个页，即2个区（extend），大小同样为2M。
对缓冲池的脏页进行刷新时，不是直接写磁盘，而是会通过memcpy()函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite 再分两次，每次1M顺序地写入共享表空间的物理磁盘上，在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer 中的页写入各个 表空间文件中，此时的写入则是离散的。如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，innodb可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

3.自适应哈希索引(ahi)

Adaptive Hash index属性使得InnoDB更像是内存数据库。该属性通过innodb_adapitve_hash_index开启，也可以通过—skip-innodb_adaptive_hash_index参数
关闭

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
哈希（hash）是一种非常快的等值查找方法，在一般情况下这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般3-4层，故需要3-4次的查询。

innodb会监控对表上个索引页的查询。如果观察到建立哈希索引可以带来速度提升，则自动建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。
AHI有一个要求，就是对这个页的连续访问模式必须是一样的。
例如对于（a,b）访问模式情况：
where a = xxx
where a = xxx and b = xxx

特点
　　1、无序，没有树高
　　2、降低对二级索引树的频繁访问资源，索引树高<=4，访问索引：访问树、根节点、叶子节点
　　3、自适应
3、缺陷
　　1、hash自适应索引会占用innodb buffer pool；
　　2、自适应hash索引只适合搜索等值的查询，如select * from table where index_col=‘xxx’，而对于其他查找类型，如范围查找，是不能使用的；
　　3、极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读。

4.预读(read ahead)

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）
为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

线性预读（linear read-ahead）

方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中，通过使用配置参数innodb_read_ahead_threshold，可以控制Innodb执行预读操作的时间。如果一个extent中的被顺序读取的page超过或者等于该参数变量时，Innodb将会异步的将下一个extent读取到buffer pool中，innodb_read_ahead_threshold可以设置为0-64的任何值，默认值为56，值越高，访问模式检查越严格
例如，如果将值设置为48，则InnoDB只有在顺序访问当前extent中的48个pages时才触发线性预读请求，将下一个extent读到内存中。如果值为8，InnoDB触发异步预读，即使程序段中只有8页被顺序访问。你可以在MySQL配置文件中设置此参数的值，或者使用SET GLOBAL需要该SUPER权限的命令动态更改该参数。
在没有该变量之前，当访问到extent的最后一个page的时候，Innodb会决定是否将下一个extent放入到buffer pool中。

随机预读（randomread-ahead）

随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中，由于随机预读方式给Innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置innodb_random_read_ahead为ON。

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：

• 文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
• 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
• 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
• 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
• BLOB和TEXT列可以被索引
• NULL被允许在索引的列中，这个值占每个键的0~1个字节
• 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
• 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
• 可以把数据文件和索引文件放在不同目录
• 每个字符列可以有不同的字符集
• 有VARCHAR的表可以固定或动态记录长度
• VARCHAR和CHAR列可以多达64KB
• 使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。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）

mysql中几个存储引擎的综合对比

• MyISAM： 它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。支持表锁。
• InnoDB： InnoDB是一个健壮的事务型存储引擎， 一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。支持表锁、行锁、外键。
• MEMORY : 使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。 它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。 Memory同时支持散列索引和B树索引。默认hash索引。
• MERGE： MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。
• ARCHIVE： Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。 在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。

存储引擎的选择

• 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
• 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
  如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

InnoDB和MyISAM的区别

1. InnoDb支持事务（ACID），MyISAM不支持事务，对于InnoDB每一条SQL 语句都默认封装成事务，自动提交，这样会影响速度，所以最好把多条sql语句放在begin和commit之间，组成一个事务。
2. InnoDB支持外键，而MyISAM不支持外键。对一个包含外键的InnoDB表转换MyISAM会失败。
3. InnoDB是聚集索引，使用B树作为索引结构，数据和主键是绑定在一起的，（所以表数据文件也就是它的索引文件），必须要有主键，通过主键索引的效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键索引查询数据。所以主键不应该过大。而MyISAM是非聚集索引，也是b+树结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是互相独立的。InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子结点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子结点都是数据文件的地址指针。
4. InnoDB存储文件有frm,ibd,而MyISAM是frm,MYD,MYI。InnoDB:frm是表定义文件，ibd是数据文件，MyISAM: frm是表定义文件，myd是数据文件，myi是索引文件(InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上)
5. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；原因：InnoDB的事务特性，在同一个时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计的行数，而不是将总行数存储起来方便快速查询。InnoDB会尝试去遍历一个尽可能小的索引/如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
6. InnoDB不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快；5.7以后的InnoDB支持全文索引。
7. MyISAM表格可以被压缩后进行查询操作。
8. InnoDB支持表级锁，行级锁，而MyISAM只支持表级锁。InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。如果访问没有命中索引，也无法使用行锁，将退化为表锁。InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列6字节的Row_id来充当默认主键），而MyISAM可以没有。MyISAM类型的表都有一个AUTO_INCREMENT的内部列
9. MyISAM中BLOB和TEXT列可以被索引，NULL列也可以被允许，占0-1个字节
   == 数据库最多可以连接16张表，表最多有64个索引，一个索引最多有16列。==

最后

以上就是健忘航空为你收集整理的Mysql存储引擎介绍及对比InnoDB存储引擎InnoDB引擎的4大特性MyISAM存储引擎MEMORY存储引擎mysql中几个存储引擎的综合对比存储引擎的选择InnoDB和MyISAM的区别的全部内容，希望文章能够帮你解决Mysql存储引擎介绍及对比InnoDB存储引擎InnoDB引擎的4大特性MyISAM存储引擎MEMORY存储引擎mysql中几个存储引擎的综合对比存储引擎的选择InnoDB和MyISAM的区别所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。