2015-02-14 16:49
原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。
什么是存储引擎? 存储引擎是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)。存储引擎在数据库系统中所处的位置如下图所示:
MySQL中支持多种类型的存储引擎如MyISAM、InnoDB、CSV、Memory等等,可以在mysql命令行中通过 show engine
命令查看当前数据库支持的存储引擎,MySQL5.5支持的存储引擎如下图所示:
从上图中可以看到MySQL5.5的默认存储引擎是InnoDB。
设置存储引擎
下面通过一个实例说明如何给数据库中的表设置存储引擎。
在数据库中创建如下表:
1 2 3 4 5 6 7 8 |
|
上述建表语句中我们并没有指定存储引擎,但是数据库会自动会给products表选择默认的存储引擎,我们可以用 SHOW CREATE TABLE products \G
命令查看products表在建表时选择的存储引擎:
从图中可以看到products表在建表时设置的存储引擎是 InnoDB。 我们再用 show table status like 'products'\G;
命令确认此时的表所使用的存储引擎:
表的存储引擎也可以用命令 Alter table tableName engine=engineName
进行修改,比如将表products的存储引擎从InnoDB改为MyISAM:
1
|
|
然后我们再用来看一下此时products表的存储引擎:
当然,也可以在建表的时候设置表的存储引擎类型,基本语法如下:
1 2 3 |
|
以上述products表为例,要将存储引擎修改为MyISAM,只需将建表语句修改为:
1 2 3 4 5 6 7 8 |
|
再来确定一下products表此时的使用的存储引擎:
如何选择合适的存储引擎
既然有这么多存储引擎可供选择,那么在设计时需要决定选择何种存储引擎来存储数据。最常用的存储引擎是InnoDB和MyISAM,至于到底用哪种存储引擎比较好,这个问题是没有定论的,需要根据你的需求和环境来衡量,比如是否需要支持事务操作、是否支持行锁、是否支持全文搜索等等。下面来具体讲解一下每种存储引擎的特点以及适用场景。
MyISAM引擎
MyISAM将每个表存储为三个文件:frm-表格定义文件、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件。MyISAM的格式是平台通用的,所以用户可以在不同架构上相互拷贝数据文件和索引文件。MyISAM表可以包含动态行和静态行。MySQL会根据表定义决定选用何种行格式。MyISAM表的可容纳的行总数,一般只受限于数据库的可用磁盘空间大小,以及操作系统允许创建的最大文件大小。
在MySQL5.5之前,MyISAM引擎是MySQL的默认存储引擎。从5.5之后,默认引擎改成了InnoDB。
MyISAM表的特征:
- 不支持事务(No transactions)
- 不支持行级锁(row level),只支持表锁(Table level)
- 支持Insert和Select的并发执行
- 支持3类索引:B-Tree,R-Tree(空间索引),full-text(全文索引)
- 不支持外键(Foreign keys)
- 支持延迟更新索引
- 不支持数据自动恢复(No automated data recovery)
- 压缩存储
- 只有索引被缓存到内存中(Only indexes are cached in memory)
- 高速读取
下面再补充说明上述几个特征。
MyISAM不支持事务。事实上,MyISAM表甚至不能保证单个语句会执行完成。如果在多行update过程中有错误发生,一些行会被更新,而另外一些行不会。
加锁和并发。MyISAM表对整张表加锁,读操作会加共享锁,写操作会加排他锁。但是支持查询时在该表内插入新行,也就是所谓的Insert和Select的并发执行(需要设置concurrent insert)。
索引特性多种索引。MyISAM表支持B-Tree,R-Tree,以及full-text索引。值得注意的是MyISAM表索引长度不能超过1000字节。
延迟更新索引。当使用delay_key_write创建的MyISAM表时,在查询结束后,不会将索引的改变数据立即写入磁盘,而是先存放在键缓冲区中缓存。在清理缓冲区或者关闭表时才会将索引块刷到磁盘。对于数据经常改变,并且使用频繁的表,该模式很大程度上提高了表的处理性能。不过,如果服务器或者系统崩溃时,索引肯定损坏,并需要修复。这时可以使用如上的修复工具进行修复。延迟特性可以被全局配置也可以为个别表单独配置。
不支持数据自动恢复。如果MySQL服务器崩溃或者掉电,就应该在使用之前用check table mytable和repair table mytable来检查并修复错误。如果有大型表,这个过程可能会持续几个小时。当服务离线时,也可以使用MyISAMchk命令行工具检查和修复表。
压缩操作。压缩表是不能被改变的(除非是解压后,修改然后再压缩表)。压缩表占用的磁盘空间很小,降低磁盘I/O,提高表的处理性能。同时压缩也支持索引,但注意索引也是只读的。
只有索引被缓存到内存中。MyISAM只缓存了MySQL进程内部的索引,并保持在键缓冲区。操作系统缓存了表的数据,因此在MySQL5.0中,要想获取数据,就需要操作系统的调用,这个过程消耗很大。
总的来说,MyISAM引擎适用如下场景:
1 2 3 4 |
|
InnoDB引擎
MySQL中使用最为广泛的除了MyISAM表之外就是InnoDB引擎了。InnoDB引擎是专为事务处理设计的。InnoDB将所有的数据共同存储在一个或几个数据文件中,这种数据文件一般称为表空间。在MySQL4.1及更新版本中InnoDB支持每个表和相关索引存储为单独的分离的文件。
InnoDB表的特征:
- 支持事务(支持事务SQL 92的4个事务隔离级别,默认的级别是repeatable)
- 支持行级锁(InnoDB的行锁机制是通过索引来完成的)
- 支持MVCC(数据多版本读取)
- 支持外键
- 优化的缓存(缓存索引和数据)
- 未压缩的索引(因此可能会比MyISAM表的索引大很多)
外键。在MySQL5.0中,InnoDB是唯一支持外键的存储引擎。其他存储引擎在CREATE TABLE时可以创建外键,但是不会约束。
总的来说,InnoDB引擎适合以下场景:
1 2 3 |
|
那么对于数据库的select、insert、update、delete操作,MyISAM和InnoDB引擎哪个性能更高呢?一般来讲:
1 2 3 4 |
|
Memory引擎
Memory引擎,顾名思义就是一个将数据存储在内存中的存储引擎。所以一旦MySQL或者主机崩溃后,Memory的表只有表结构仍然会保留,相关数据将全部丢失。
Memory引擎有以下特点
- 支持hash索引(Memory引擎的默认索引),它使得查询非常快速。
- 使用表级锁,只支持较低的并发。
- 不支持text或blob类型,也只支持固定大小行。
- 重启后,如果打开binlog会写一条truncate table又保证主从数据一致由于不写盘,写入速度很可观。
MyISAM、InnoDB、Memory对比: