MySQL存储引擎：MyISAM与InnoDB

最新推荐文章于 2024-03-24 19:45:21 发布

CrankZ

最新推荐文章于 2024-03-24 19:45:21 发布

阅读量605

点赞数

分类专栏：数据库知识文章标签： MySQL MyISAM InnoDB

本文链接：https://blog.csdn.net/CrankZ/article/details/84594472

版权

数据库知识专栏收录该内容

9 篇文章 1 订阅

订阅专栏

常见命令

查看数据库可配置的存储引擎类型

MySQL>show engines;

create table创建表时指定存储引擎

MySQL> use school
MySQL> create table class(id int ,cname varchar(20)) engine=InnoDB; #创建库，指定引擎InnoDB
MySQL> show create table class\G;

使用show table status命令查看表正在使用的存储引擎

MySQL> show table status from school where name='student'\G;
school：当前数据库
student：当前表

使用show create命令查看表正在使用的存储引擎

MySQL> use school; #进入数据库
MySQL> show create table student;

3、修改存储引擎

方法1：使用alter table命令修改

MySQL> use school
MySQL> alter table student engine=MyISAM;
MySQL> show create table student\G;

方法2：修改my.cnf主配置文件，指定默认存储引擎

# vim /etc/my.cnf

[MySQLd]标签中添加以下参数

[MySQLd]
default-storage-engine=MyISAM

然后重启MySQL

# systemctl restart MySQLd.service #重启服务

MySQL默认存储引擎的变迁

在MySQL 5.5之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。

MyISAM存储引擎

MyISAM是MySQL中常见的存储引擎，曾经是MySQL的默认存储引擎。MyISAM是基于ISAM引擎发展起来的，增加了许多有用的扩展。

MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、MYD、MYI。其实，frm文件存储表的结构；MYD文件存储数据，是MYData的缩写；MYI文件存储索引，是MYIndex的缩写。

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到MyISAMpack工具，占用的磁盘空间较小。

MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

MyISAM 是 MySQL 5.5 之前的默认引擎，它支持 B-tree/FullText/R-tree 索引类型。

锁级别为表锁，表锁优点是开销小，加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务。

此引擎不支持事务，也不支持外键。

MyISAM强调了快速读取操作。它存储表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。

InnoDB存储引擎

InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。在MySQL从3.23.34a开始包含InnnoDB。它是MySQL上第一个提供外键约束的表引擎。而且InnoDB对事务处理的能力，也是其他存储引擎不能比拟的。靠后版本的MySQL的默认存储引擎就是InnoDB。

InnoDB存储引擎总支持AUTO_INCREMENT。自动增长列的值不能为空，并且值必须唯一。MySQL中规定自增列必须为主键。在插入值的时候，如果自动增长列不输入值，则插入的值为自动增长后的值；如果输入的值为0或空（NULL），则插入的值也是自动增长后的值；如果插入某个确定的值，且该值在前面没有出现过，就可以直接插入。

InnoDB还支持外键（FOREIGN KEY）。外键所在的表叫做子表，外键所依赖（REFERENCES）的表叫做父表。父表中被字表外键关联的字段必须为主键。当删除、更新父表中的某条信息时，子表也必须有相应的改变，这是数据库的参照完整性规则。

InnoDB中，创建的表的表结构存储在.frm文件中（我觉得是frame的缩写吧）。数据和索引存储在InnoDB_data_home_dir和InnoDB_data_file_path定义的表空间中。

InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

小结

MyISAM

是MySQL5.5之前的默认存储引擎
MyISAM支持表级别的锁表，锁优点是开销小，加锁快，缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低。
MyISAM在设计时主要考虑速度。
MyISAM不支持外键，因此我们把使用MyISAM的MySQL称为数据库管理系统（DBMS）。
MyISAM在磁盘空间上用三个不用的文件分别存储表，数据，和索引，三个文件依次是.FRM，.MYD,.MYI。
MyISAM不支持事务，用MyISAM，你不能提交和回滚，当你执行了一条命令，就已经永久生效了。
因为没有提供对数据库事务的支持，也不支持行级锁和外键，所以当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
MyISAM支持全文索引。
当表存在大量查询而只有少量更新和删除时，推荐使用MyISAM。
基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到MyISAMpack工具，占用的磁盘空间较小。

InnoDB

MySQL5.5之后的默认引擎
InnoDB支持行级别的锁。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用InnoDB引擎会提升效率。
但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。
InnoDB支持外键，因此我们把使用InnoDB的MySQL称为关系数据库管理系统（RDBMS）。
InnoDB引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。
InnoDB在MySQL5.6之后也支持全文索引。5.6版本之前并不支持
InnoDB默认将所有数据存储在一个文件中（可以在创建表时通过设定InnoDB_file_per_table选项来设定文件数）。
索引不仅缓存自身，也缓存数据，相比 MyISAM 需要更大的内存。
它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。
InnoDB存储引擎总支持AUTO_INCREMENT。自动增长列的值不能为空，并且值必须唯一。

MyISAM与InnoDB的区别

1、存储结构

InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。
但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。
因此，主键不应该过大，因为主键太大，其他索引也都会很大。
而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

MyISAM中：

.frm 表结构文件
.myd 表数据文件
.myi 表索引文件

InnoDB中：

.frm 表结构文件
.idb 表数据文件

MyISAM备份恢复相对比较方便；而InnoDB就相对麻烦一些啦

2、存储空间

MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 MySQLdump，在数据量达到几十G的时候就相对痛苦了。

4、事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外键等高级数据库功能。InnoDB是事务安全的，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

5、AUTO_INCREMENT

MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB：InnoDB 中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

6、表锁差异，锁粒度

MySQL支持三种锁定级别，行级、页级、表级;

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

MyISAM：只支持表级锁，MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

InnoDB：支持事务和行级锁，是InnoDB的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句带有where语句，那么也会锁住整张表

例如update table set num=1 where name like “%aaa%”

7、索引实现

都是采用B+Tree作为索引结构
MyISAM：叶节点的data存放的是数据记录的地址，所以首先是根据Key取出data域的值，然后根据data读取相应的数据记录
InnoDB：数据文件本身就是索引文件，所以根据Key就可以读取到相应的数据记录

8、全文索引

MyISAM：支持 FULLTEXT类型的全文索引
InnoDB：5.6版本之后，开始支持，之前不支持。

9、表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

10、表的具体行数

MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。
InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，MyISAM和InnoDB处理的方式都一样。

10、CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在InnoDB上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

11、外键

MyISAM：不支持
InnoDB：支持

InnoDB支持外键，而MyISAM不支持。所以对一个包含外键的InnoDB表转为MyISAM会失败；

12、备份策略

InnoDB支持热备、温备、冷备。崩溃后可安全恢复。

MyISAM不支持热备份，只支持温备与冷备。崩溃后无法安全恢复。由于MyISAM引擎不支持热备，所以在很多需要热备的应用场景中极不适用，近乎到了被抛弃的境地。

13、性能

下边两张图是官方提供的MyISAM与InnoDB的压力测试结果

可以看出，随着CPU核数的增加，InnoDB的吞吐量反而越好，而MyISAM，其吞吐量几乎没有什么变化，显然，MyISAM的表锁定机制降低了读和写的吞吐量。

表格总结

	MyISAM	InnoDB
存储结构	每张表被存放在三个文件：frm-格定义 MYD(MYData)-数据文件 MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 MySQLdump，在数据量达到几十G的时候就相对痛苦了
事务安全	不支持每次查询具有原子性	支持具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表
AUTO_INCREMENT	MyISAM表可以和其他字段一起建立联合索引	InnoDB中必须包含只有该字段的索引
SELECT	MyISAM更优
INSERT		InnoDB更优
UPDATE		InnoDB更优
DELETE		InnoDB更优它不会重新建立表，而是一行一行的删除
COUNT without WHERE	MyISAM更优。因为MyISAM保存了表的具体行数	InnoDB没有保存表的具体行数，需要逐行扫描统计，就很慢了
COUNT with WHERE	一样	一样，InnoDB也会锁表
锁	只支持表锁	支持表锁、行锁行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的
外键	不支持	支持
FULLTEXT全文索引	支持	不支持（5.6.4以上支持英文全文索引）可以通过使用Sphinx从InnoDB中获得全文索引，会慢一点
表的行数	Select count() from table,MyISAM只要简单的读出保存好的行数注意的是，当count()语句包含 where条件时，两种表的操作是一样的	InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行

互联网项目中随着硬件成本的降低及缓存、中间件的应用，一般我们选择都以 InnoDB 存储引擎为主，很少再去选择 MyISAM 了。而业务真发展的一定程度时，自带的存储引擎无法满足时，这时公司应该是有实力去自主研发满足自己需求的存储引擎或者购买商用的存储引擎了。

如何选择

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

更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
外键约束。MySQL支持外键的存储引擎只有InnoDB。
支持自动增加列AUTO_INCREMENT属性。
从5.5开始InnoDB存储引擎成为默认的存储引擎。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

查询密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

索引

索引：是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引有助于更快地获取信息。索引的一个主要目的就是加快检索表中数据的方法，也就是能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

索引分类

存储结构划分

聚簇索引：是按照数据存放的物理位置为顺序的，能提高多行检索的速度。
非聚簇索引：对于单行的检索很快。

功能划分

唯一索引：不允许其中任何两行具有相同索引值的索引。
主键索引：数据库表中一列或多列组合，其值唯一标识表中的的一行。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
聚集索引：在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

MyIASM引擎的索引结构

　　MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。如下图所示：

　　这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

　　同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB引擎的索引结构

　　与MyISAM引擎的索引结构同样也是B+Tree，但是InnoDB的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

　　并且和MyISAM不同，InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以InnoDB不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

两者区别：

　　第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

　　上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

　　第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

　　这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

　　了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调(可能是指“非递增”的意思)的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调(可能是指“非递增”的意思)的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。