【MySQL数据库原理 二】MySQL数据库存储引擎

什么是存储引擎? 关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。

• 有的表简单，有的表复杂
• 有的表根本不用来存储任何长期的数据
• 有的表读取时非常快，但是插入数据时去很差

我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。在MySQL中，存储引擎是以插件的形式运行的。支持的引擎有十几种之多，但我们实战常用到的，只有InnoDB、MyISAM和Memory ，MySQL的默认存储引擎为InnoDB。

MyISAM

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。

• 体积小，质量大。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。同时能加载更多索引，建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：MyISAM索引数据分离，体积小，内存利用率高

  • tb_demo.frm，存储表定义； tb_demo.MYD，存储数据；tb_demo.MYI，存储索引

• MyISAM无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。

• DELETE FROM table时，MyISAM会先将表结构备份到一张虚拟表中，然后执行drop，最后根据备份重建该表。

• innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快

MyISAM存储引擎特别适合在以下几种情况下使用：

• 选择密集型的表，MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
• 插入密集型的表，MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。不支持事务，自然就不支持行级锁
• 如果和 MyISAM 比 Insert 写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，MyISAM会慢于InnoDB，MyISAM的insert快于Innodb，update慢于Innodb
• select count(*) 和 order by 大概是使用最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的还是会锁全表的，MyISAM表的select count(*) 是非常快的；在 MyISAM 存储引擎中，把表的总行数（row）存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。同样，当 count(*) 语句包含 where条件时，两种表的操作是一样的MyISAM的select count(*) 和 order by的速率快
• 定期提供某些表的数据时，MyISAM的话很方便，只要发给他们对应那表的（frm.MYD,MYI）的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

所以综合而言MyISAM更加适合select多的，事务无要求的场景

InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。

• InnoDB还引入了行级锁定和外键约束，
• Innodb的索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多
• InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
• InnoDB 表的select count(*) 比 MyISAM 慢很多；当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量，需要注意的是，当count(*) 语句包含 where 条件时，两种表的操作是一样的，当count(*)语句包含where条件时MyISAM也需要扫描整个表
• DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除

在以下场合下，使用InnoDB是最理想的选择：

• 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
• 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。默认的事务隔离级别为可重复读（REPEATABLE-READ），通过MVCC（并发版本控制）来实现，使用的锁粒度默认为行级锁，可以支持更高的并发；当然，也支持表锁，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了
• 支持行级锁，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表
• 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
• 外键约束。MySQL支持外键的存储引擎只有InnoDB。
• 支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。大数据量下用innodb，因为支持事务，行级锁。对于InnoDB来说，最大的优势在于支持事务，当然这是以牺牲效率为代价的

MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。

• 虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。
• 要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用
• 只支持表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈
• 查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低

基于以上的MEMORY的特性，适用场景如下：

• 目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
• 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
• 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在**=和<>**的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

三种存储引擎的对比

三种存储引擎的详细对比用如下的这个表格来展示下吧：

功能特性	Innodb	MyISAM	MEMORY
是否MySQL默认引擎	是	否	否
是否支持事务	是【原子和回滚】	否	-
高并发下DML语句适合场景	频繁更新以及插入涉及到安全性较高操作 【读写密集型】	查询【查询密集型】	-
是否支持外键	是 【联表数据耦合高】	否	-
是否保存表行数，这将增加count(*)性能	否	是	-
是否支持自动增长列	是	否	-
清空表时如何操作	一行一行的删除，效率非常慢	drop，然后重建表，效率高	-
清空支持锁类型	行级锁【粒度更细，适合高并发】、表级锁	表级锁	表级锁
占用存储空间	多	少	只在内存中运行
占用缓存在内存的内容	索引+数据【缓冲池】	索引	索引+数据
是否支持MVCC	是	否	否