常用的存储引擎有MyISAM和InnoDB存储引擎。
MySQL存储引擎MyISAM与InnoDB的区别?
MyISAM | Innodb | |
---|---|---|
存储结构 | 每张表被存放在三个文件:frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件 | 所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB |
存储空间 | MyISAM可被压缩,存储空间较小 | InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了 |
文件格式 | 数据和索引是分别存储的,数据.MYD ,索引.MYI | 数据和索引是集中存储的,.ibd |
记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
锁支持(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的) | 表级锁定 | 行级锁定、表级锁定,锁定力度小并发能力高 |
SELECT | MyISAM更优 | |
INSERT、UPDATE、DELETE | InnoDB更优 | |
select count(*) | myisam更快,因为myisam内部维护了一个计数器,可以直接调取。 | |
索引的实现方式 | B+树索引,myisam 是堆表 | B+树索引,Innodb 是索引组织表 |
哈希索引 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
其他:
- InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
- InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效。
- MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
- InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效。
InnoDB引擎的4大特性?
- 插入缓冲(insert buffer)
- 二次写(double write)
- 自适应哈希索引(ahi)
- 预读(read ahead)
在查询的时候为什么MyISAM比InnoDB快?
- 查询的时候,由于innodb支持事务,所以会有mvvc的一个比较。这个过程会损耗性能。
- 查询的时候,如果走了索引,由于innodb是聚簇索引,会有一个回表的过程,即:先去非聚簇索引树中查询数据,找到数据对应的key之后,再通过key回表到聚簇索引树,最后找到需要的数据。而myisam直接就是簇集索引,查询的时候查到的最后结果不是聚簇索引树的key,而是磁盘地址,所以会直接去查询磁盘。详解
- 锁的一个损耗,innodb锁支持行锁,在检查锁的时候不仅检查表锁,还要看行锁。
如何选择合适的存储引擎?
如果没有特别的需求,使用默认的Innodb即可。
MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。
Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键。比如OA自动化办公系统。