MySQL存储引擎之MyISAM、InnoDB详细对比

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

InnoDB概览

是MySQL5.5及以后的默认存储引擎,是MySQL的默认事务性引擎,也是最重要最广泛的存储引擎,它被设计用来处理大量的短期(short-lived)事物,短期事物大部分是正常提交的,很少回滚。InnoDB的性能和自动恢复特性,使得它在非事物型存储需求中也很流行。除非特别原因,否则应该优先考虑InnoDB引擎。InnoDB实现了四个标准的事物隔离级别,默认级别是REPEATABLE READ,采用MVCC支持高并发,并且通过间隙锁策略防止幻读的出现。InnoDB是基于聚簇索引建立的。


MyISAM概览

是MySQL5.1及之前版本的默认存储引擎。MyISAM提供了大量的特性,包含全文索引、压缩、空间函数等,但是MyISAM不支持事物和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。对于只读的数据,或者表比较小、可以忍受修复(repair)操作,则依然可以继续使用MyISAM。MyISAM最大的性能问题是表锁问题。

其中MyISAM还有如下一些特性:

1、加锁与并发。MyISAM读加共享锁,写加排他锁,支持并发插入(读取查询的同时往表中插入新的记录);

2、修复。MyISAM可以手工或者自动检查和修复操作,这里说的修复和事物恢复和崩溃修复是不同的概念。

3、索引特性。即使是BLOB和TEXT等长字段,也可以基于前500个字符创建索引。MyISAM还支持全文索引;

4、延迟更新索引。创建MyISAM表的时候,如果指定了DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立即将修改索引数据写入磁盘,而是会写到内存缓冲区中。这种方式极大的提升了写入性能,但是数据库崩溃会造成索引损坏,需要执行修复操作。


InnoDBMyISAM主要区别

1、InnoDB支持事物、行级锁、主键聚簇索引、存储格式平台独立、支持热备、外键约束;

2、MyISAM支持压缩表、全文索引、空间函数、保存表的具体行数;



总结

对于如何选择存储引擎,可以简单的归纳为一句话:“除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先使用InnoDB引擎”。例如,如果要用到全文索引,建议先考虑InnoDB加上Sphinx的组合,而不是使用MyISAM。当然,如果不需要用到InnoDB的特性,同时其他引擎的特性能够很好的满足,也可以考虑一下其他存储引擎。除非万不得已,否则不要混合使用多种存储引擎,否则可能带来一系列复杂的问题。例如,大部分情况下InnoDB都是正确的选择。如果需要不同的存储引擎,请先考虑一下几个因素:

1、事物。如果需要事物那么InnoDB是目前最好的选择。如果不需要事物,并且主要是SELECT和INSERT操作,那么MyISAM是不错的选择,一般日志型的应用比较符合这一特性。

2、备份。如果可以定期的关闭服务器来执行备份,那么备份的因素可以忽略,反之如果需要热备份,InnoDB就是基本的要求。

3、崩溃恢复。数据比较大的时候,系统崩溃后如何快速的恢复是一个需要考虑的问题。相对而言,MyISAM崩溃后发生损坏的概率比InnoDB要高很多,而且恢复速度也要慢。因此即使不需要事物支持,很多人也选择InnoDB。

4、特有的特性。



InnoDBMyISAM的注意事项

1、对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
2、DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
3、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

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



转换表的引擎的命令介绍

最简单的方法: alter table mytable engine=InnoDB;

比较优的方法: 

create table innodb_table like myisam_table;

alter table innodb_table engine=InnoDB;

insert into innodb_table select * from myisam_table;

数据量大的时候可以考虑分批处理

start transaction;

insert into innodb_table select * from myisam_table where id between x and y;

commit;

这样操作完成以后,新表是原表的全量复制,如果有必要,可以再执行的过程中对原表加锁,以确保新表和原表的数据一致。


本文转自 古道卿 51CTO博客,原文链接:http://blog.51cto.com/gudaoqing/1286597

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
17天前
|
存储 缓存 关系型数据库
MySQL - 存储引擎MyISAM和Innodb
MySQL - 存储引擎MyISAM和Innodb
|
20天前
|
存储 关系型数据库 MySQL
MySQL 数据库系列(五)-----索引、事务与存储引擎(Linux版)
MySQL 数据库系列(五)-----索引、事务与存储引擎(Linux版)
24 0
|
21天前
|
存储 缓存 网络协议
从零开始学Mysql - 连接管理和存储引擎
从零开始学Mysql - 连接管理和存储引擎
15 2
|
21天前
|
存储 SQL 关系型数据库
Mysql专栏 - mysql、innodb存储引擎、binlog的工作流程
Mysql专栏 - mysql、innodb存储引擎、binlog的工作流程
52 0
|
1月前
|
存储 算法 关系型数据库
MySQL相关(八)- innodb行级锁深入剖析
MySQL相关(八)- innodb行级锁深入剖析
26 0
|
1月前
|
存储 算法 关系型数据库
MySQL相关(七)- innodb 锁的介绍及使用
MySQL相关(七)- innodb 锁的介绍及使用
19 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL相关(番外篇)- innodb 逻辑存储结构
MySQL相关(番外篇)- innodb 逻辑存储结构
21 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL存储引擎 InnoDB、MyISAM、Memory存储引擎的特点与区别
MySQL存储引擎 InnoDB、MyISAM、Memory存储引擎的特点与区别
29 0
|
1月前
|
存储 SQL 关系型数据库
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
35 1
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
|
1月前
|
存储 SQL 关系型数据库
Mysql系列-4.Mysql存储引擎-InnoDB(下)
Mysql系列-4.Mysql存储引擎-InnoDB
32 0