引擎概念
- MySQL中的数据用各种不同的技术存储在文件中,每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力,这些不同的技术以及配套的功能在MySQL中称为存储引擎
- 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
- MySQL数据库中的组件,负责执行实际的数据I/O操作
- MySQL系统中,存储引擎处于文件系统之上,在数据保存到数据文件之前会传输到存储引擎,之后按照各个存储引擎的存储格式进行存储
引擎分类
InnoDB引擎
- 支持事务,支持4个事务隔离级别。默认的事务隔离级别为可重复读(REPEATABLE-READ),通过MVCC(并发版本控制)来实现。
- 使用的锁粒度默认为行级锁,可以支持更高的并发;当然,也支持表锁。
- 支持外键约束;外键约束其实降低了表的查询速度,但是增加了表之间的耦合度
- 可以通过自动增长列,方法是auto_increment。
- 配合一些热备工具可以支持在线热备份;
- 在InnoDB中存在着缓冲管理,通过缓冲池,将索引和数据全部缓存起来,加快查询的速度
- 对于InnoDB类型的表,其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块,都位于B+数的叶子节点上
- InnoDB 表的select count() 比 MyISAM 慢很多;当执行 select count() from t 时,会先把数据读出来,一行一行的累加,最后返回总数量。 是的,真的会很慢。需要注意的是,当count(*) 语句包含 where 条件时,两种表的操作是一样的
- DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
InnoDB的存储表和索引有下面两种形式:
共享表空间存储:所有的表和索引存放在同一个表空间中。
多表空间存储:表结构放在.frm文件,数据和索引放在.ibd文件中。分区表的话,每个分区对应单独的.ibd文件,分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。
MyISAM引擎
- 不支持事务。在读写(Insert、select)效率上,要高于InnoDB不少,当然也不支持锁。
- 体积小,质量大。MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。同时能加载更多索引,而Innodb的索引和数据是紧密捆绑的,没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多。
每张MyISAM表在磁盘上会对应三个文件。
a) .frm文件:存储表的定义数据
b) .MYD文件:存放表具体记录的数据
c) .MYI文件:存储索引 - select count(*) 和 order by 使用很频繁,大概能占整个sql总语句的60%以上的操作,而这种操作Innodb其实也是会锁表的,很多人以为Innodb是行级锁,那个只是where对它主键是有效,非主键的还是会锁全表的。
- 常常应用部门需要我给他们定期某些表的数据,MyISAM的话很方便,只要发给他们对应那表的(frm.MYD,MYI)的文件,让他们自己在对应版本的数据库启动就行,而Innodb就需要导出.sql了,因为光给别人文件,受字典数据文件的影响,对方是无法使用的。
- 如果和 MyISAM 比 Insert 写操作的话,Innodb还达不到MyISAM的写性能,如果是针对基于索引的update操作,确实MyISAM会慢与InnoDB,但在并发环境下,从库同步也是个事儿,还不如通过多实例分库分表架构来解决。
- MyISAM表的select count(*) 是非常快的;在 MyISAM 存储引擎中,把表的总行数(row)存储在磁盘上,执行时,直接返回总数据。同样,包含 where条件时,两种表的操作是一样的。
- DELETE FROM table时,MyISAM会先将表结构备份到一张虚拟表中,然后执行drop,最后根据备份重建该表。
Archive引擎
Blackhole引擎
CSV引擎
Memory引擎
- 支持的数据类型有限制,比如:不支持TEXT和BLOB类型。对于字符串类型的数据,只支持固定长度的行,VARCHAR(64)会被自动存储为CHAR(64)类型;
- 只支持表级锁。所以,在访问量比较大时,表级锁会成为MEMORY存储引擎的瓶颈;
- 由于数据是存放在内存中,一旦服务器宕机,数据就会丢失;数据库主从切换的配置要设置好。
- 查询的时候,如果有用到临时表,而且临时表中有BLOB,TEXT类型的字段,那么这个临时表就会转化为MyISAM类型的表,性能会急剧降低。
- 默认使用hash索引。