在创建表时可以指定表的类型,也就是表的存储引擎。表的存储引擎决定了数据如何存储及如何访问,还有事务如何存储。表的存储引擎在很大程度上影响着处理SQL语句所需的存储空间和速度。不同的存储引擎所具有的特点也有所不同,有些存储引擎在处理很多复杂的SELECT语句时非常适合,而另一些则在实现快速更新时比较适合。
InnoDB
InnoDB是MySQL的默认事务型引擎,也是最重要,使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务,短期事务大部分情况下是正常提交的,很少会被回滚。InnoDB的性能和自动崩溃恢复特性,使得它在非事务型存储的需求中也很流行。
除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。————《高性能MySQL》
- InnoDB采用MVCC来支持高并发,并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ(可重复读),并且通过间隙锁策略防止幻读的出现。
- InnoDB表示基于聚簇索引建立的
- 支持外键约束。
- 支持自动增加列AUTO_INCREMENT属性。
- 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
- 删除或增加索引时不需要复制全表数据。
InnoDB内部做了很多优化,包括从磁盘读取数据时采用的可预测预读,能够自动在内存中创建hash索引以加速度操作的自适应哈希索引,以及能够加速插入操作的插入缓冲区。
InnoDB表是基于聚簇索引建立的。InnoDB的索引结构和MySQL的其他引擎有很大不同,聚簇索引对主键查询有很高的性能。但是二级索引中必须包含主键列,所以主键列很大的话,其他的所有索引都会很大。因此,若表上的索引较多的话,主键应当尽可能的小。
MyISAM
MyISAM提供了大量的特性,包括全文索引,压缩,空间函数(GIS)等,但MyISAM不支持事务和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。在MySQL 5.1及以前的版本中MyISAM为默认的存储引擎,正是由于该引擎的缘故,即使MySQL支持事务已经很长时间了,在很多人的概念中MySQL还是非事务型的数据库。
- MyISAM对整张表加锁,而不是针对行。
- 支持全文索引。
- 支持压缩表。压缩表是不能进行修改的,可以极大地减少磁盘空间占用,因此也可以减少磁盘I/O操作,从而提升查询性能。
存储
MyISAM会将表存储在两个文件中:数据文件和索引文件,分别以.MYD和.MYI为扩展名。MyISAM表可以包含动态或者静态行。MySQL会根据表的定义来决定采用何种行格式。
在MySQL5.0中,MyISAM表如果是变长行,则默认配置只能处理256TB的数据
特性
作为MySQL最早的存储引擎之一,还是有一些特性。
加锁与并发–表锁
MyISAM对整张表加锁,而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。但是在表有读取查询的同时,也可以往表中插入新的记录(并发插入)。
修复
对于MyISAM表,MySQL可以手工或者自动执行检查和检修操作,但这里说的修复和事务恢复以及崩溃恢复是不同的概念。
索引特性
对于MyISAM表,即使是BLOB和TEXT等长字段,也可以基于其前500个字符创建索引。MyISAM也支持全文索引,这是一种基于分词创建的索引,可以支持复杂的查询。
延迟更新索引键
创建MyISAM表的时候,如果指定了DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立刻将修改的索引数据写入磁盘。
MyISAM性能
MyISAM引擎设计简单,数据以紧密格式存储,所以在某些场景下的性能很好。但由于表锁的存在对于性能有很大的影响。
Archive 引擎
Archive存储引擎只支持INSERT和SELECT。Archive引擎会缓存所有的写并利用zlib对插入进行压缩,所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用,这类应用做数据分析时往往需要全表扫描。
Archive引擎支持行级锁和专用的缓冲区,所以可以实现高并发的插入。在一个查询开始直至返回表中存在的所有的行数之前,Archive会阻止其他的SELECT执行,以实现一致性读。另外,也实现了批量插入在完成之前对读操作不可见。这种机制模仿了事务和MVCC的一些特性,但是Archive引擎不是一个事务型的引擎,而是一个针对高速插入和压缩做了优化的引擎。
CSV存储引擎
这个引擎可以将普通的CSV文件作为MySQL的表来处理,但这种表不支持索引。只需要将CSV文件copy到CSV存储引擎的数据目录下,就能使用在MySQL中已表的规则打开使用。
Memory引擎
如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用Memory表是非常有用的。Memory表比MyISAM表要快一个数量级,因为所有数据都保存在内存中,不需要进行磁盘I/O操作。Memory表的结构在重启以后还会保留,但数据丢失。
- 支持Hash索引,因此查找操作非常快。
- 是表级锁,因此并发写入性能较低。
- 不支持BLOB或TEXT类型的列,并且每行长度是固定的,即使指定了varchar,实际存储中也会被转换为char。
说明
除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎——《高性能MySQL》
另外上面只是列举了一些经常遇见的存储引擎,并不全面。