MySQL提供了很多类型的存储引擎给我们选择,存储引擎是在表之上的,每一个库的每一个表都可以指定不同的存储引擎。存储引擎负责提供API接口给Server层的执行器调用实现数据的存储和提取。不同的存储引擎存储数据的格式是不一样的,甚至有的存储引擎(Memory)是不用磁盘来存储数据的关闭服务器后表中的数据就不存在了,在MySQL5.5之前默认的存储引擎是MyISAM,在MySQL5.5版本之后存储引擎默认使用InnoDB。在工作中经常用到的存储引擎是InnoDB和MyISAM。
InnoDB存储引擎
InnoDB是MySQL5.5之后默认的存储引擎,支持事务。底层通过多版本并发控制(MVCC)来支持高并发,并实现了SQL标准的4中隔离级别,默认隔离级别为REPEATABLE READ(可重复读),InnoDB是基于聚集索引建立的,聚集索引对主键的查询具有很高的性能,支持主键索引(聚集索引)和辅助索引(非聚集索引),InnoDB占用空间大(缓存索引和真实数据)需要跟多的内存和存储,需要有缓存池用于高速缓存数据和索引,在存储结构上存在2个文件.ifm(表结构)和.ibd(数据和索引)。
关键特性
支持外键
支持事务,遵循事务的ACID
行锁:操作时只锁定一行,不影响其他行数据,适合高并发的操作
即缓存索引又缓存真实数据
表必须有主键(如果没有明确指定主键索引,会帮我们隐藏的生成一个6 byte的int类型的索引作为主键索引)
.frm文件存储表结构,.ibd文件存储数据内容
在工作中用的最多的时InnoDB存储引擎,在使用InnoDB存储引擎的表中最好是采用主键查询,这样只需要一次索引,如果采用辅助索引查询,会涉及到回表操作,比主键查询要耗时多一些。
MyISAM存储引擎
MyISAM是MySQL5.5版本之前默认的存储引擎,它提供高速存储和检索及全文索引能力,MyISAM不支持事务、行级锁、外键,索引采用B+树结构,使用的是非聚集索引。占用空间小,在存储结构上存在3个文件:.frm(表结构)、.myd(数据文件)、.myi(索引文件)。
关键特性
不支持事务
不支持外键
表锁:对整张表加锁,锁的粒度比较粗,并发写入的性能低
支持全文索引
可以被压缩后进行查询操作,节省空间容量
.frm 存储表结构、.myd 存储数据内容、.myi 存储索引文件
主键不是必须的
如果执行大量的SELECT,MyISAM是更好的选择。
MyISAM与InnoDB的区别
\ | MyISAM | InnoDB |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引,还缓存真实数据,对内存要求较高。而且内存大小对性能有决定性影响。 |
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
锁 | 表级锁定 | 行级锁定、表级锁定,锁定力度小并发能力高 |
索引的实现 | B+树索引,myisam是堆表 | B+树索引,Innodb是索引组织表 |
哈希索引 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
存储空间 | MyISAM可被压缩,存储空间较小 | InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份binlog,或者用mysqldump,在数据量达到几十G的时候就相对痛苦了 |