MySQL-谈谈MySQL存储引擎

MySQL存储引擎

介绍、对比、使用

引子

在之前的文章中我们说过MySQL事务，现在大家都应该知道了MySQL事务了吧，还记得事务的ACID原则吗？不记得的童鞋可以回顾一下《

深入理解事务隔离级别

》，其实呀，更严谨一点的话，应该是MySQL InnoDB存储引擎，因为在MySQL中，只有InnoDB存储引擎才支持事务。看到此处，有些朋友可能有以下疑问:

存储引擎是什么？

MySQL中有哪些存储引擎？

每个存储引擎有哪些特点以及区别？

下面，我们带着这些疑问，依次往下看：

存储引擎是什么

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

mysql中的存储引擎

在MySQL 5.7版本中，MySQL支持表中Support列为YES和DEFAULT的引擎:

下面只介绍常用的存储引擎，其他没介绍的存储引擎，有兴趣的童鞋，可以自己自行搜索。

InnoDB：

支持事务操作(如 begin， commit，rollback命令)，支持行级锁，行级锁相对于表锁，其粒度更细，允许并发量更大，支持外键引用完整性约束。InnoDB存储引擎也是MySQL 5.7版本中默认的存储引擎。其缺点是：存储空间会占用比较大。

InnoDB适用于以下的场合：

1. 支持事务。

2. 可以从灾难中恢复（通过bin-log日志等）。

3. 外键约束。只有他支持外键。

4. 支持自动增加列属性auto_increment。

MyISAM：

MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

特点：

1. 不支持事务，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。

2. 不支持外键。

3. 查询速度很快。如果数据库insert和update的操作比较多的话采用表锁效率低（建议使用innodb）。

4. 对表进行加锁。

Memory：

该存储引擎最大的特点是，所有数据均保存在内存中，之前还有个名字叫做 「Heap」。

使用Memory存储引擎情况：

1. 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。

2. 如果数据是临时的，而且必须立即可用得到，那么就可以放在内存中。

3. 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。

【注】 Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。

CSV：

首先先认识一下CSV，CSV文件其实就是用逗号分隔开的文本文件，常用于数据转换，该类型平时用的比较少，不支持索引。

Archive：

存档文件，主要用于存储很少用到的引用文件，

如何选择存储引擎

大家通过上面的比较应该已经看出来了，InnoDB存储引擎是支持事务，支持外键并支持行级锁的。对于需要在线事务处理的应用最合适不过了，我们在选择存储引擎时，如果没有特别的理由，我的建议是选择InnoDB作为存储引擎。

MyISAM与InnoDB的区别

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。InnoDB表的大小更加的大，用MyISAM可省很多的硬盘空间。

以下是一些细节和具体实现的差别：

1.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时，两种表的操作是一样的。

2.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

3.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

4.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如updatetable set num=1 where name like “a%”

两种类型最主要的差别就是Innodb支持事务处理与外键和行级锁.而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。

我们可以在创建table时，指定存储引擎，如果未指定，则使用默认的存储引擎。

CREATE TABLE `mytable` (

`ID` INT(11) NOT NULL AUTO_INCREMENT,

`username` VARCHAR(16) NOT NULL,

PRIMARY KEY (`ID`),

INDEX `uname` (`username`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB

AUTO_INCREMENT=3;

不建议修改表的存储引擎，在创建表时，就要需要考虑好，使用什么存储引擎。

一般情况下，MySQL会默认提供多种存储引擎，可以通过下面的查看:

（1）看你的MySQL现在已提供什么存储引擎: mysql> show engines；

（2）看你的MySQL当前默认的存储引擎: mysql> show variables like '%storage_engine%'；

（3）你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): mysql> show create table 表名；

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。