Jamzy Wang

life is a struggle,be willing to do,be happy to bear~~~

MySQL存储引擎——MyISAM vs InnoDB

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
CREATE TABLE IF NOT EXISTS products (
    productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    productCode  CHAR(3)       NOT NULL DEFAULT '',
    name         VARCHAR(30)   NOT NULL DEFAULT '',
    quantity     INT UNSIGNED  NOT NULL DEFAULT 0,
    price        DECIMAL(7,2)  NOT NULL DEFAULT 99999.99,
    PRIMARY KEY  (productID)
);

上述建表语句中我们并没有指定存储引擎,但是数据库会自动会给products表选择默认的存储引擎,我们可以用 SHOW CREATE TABLE products \G 命令查看products表在建表时选择的存储引擎:

此处输入图片的描述

从图中可以看到products表在建表时设置的存储引擎是 InnoDB。 我们再用 show table status like 'products'\G; 命令确认此时的表所使用的存储引擎:

此处输入图片的描述

表的存储引擎也可以用命令 Alter table tableName engine=engineName 进行修改,比如将表products的存储引擎从InnoDB改为MyISAM:

1
alter table products engine=MyISAM;

然后我们再用来看一下此时products表的存储引擎:

此处输入图片的描述

当然,也可以在建表的时候设置表的存储引擎类型,基本语法如下:

1
2
3
Create table tableName(
...
) engine = engineName

以上述products表为例,要将存储引擎修改为MyISAM,只需将建表语句修改为:

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS products (
    productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    productCode  CHAR(3)       NOT NULL DEFAULT '',
    name         VARCHAR(30)   NOT NULL DEFAULT '',
    quantity     INT UNSIGNED  NOT NULL DEFAULT 0,
    price        DECIMAL(7,2)  NOT NULL DEFAULT 99999.99,
    PRIMARY KEY  (productID)
) engine=MyISAM

再来确定一下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
1) 数据库并发数量不大
2) 读操作为主(20%写,80%读)
3) SQL简单
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
1) 数据库读写并发大
2)写操作多
3)事务、外键等特殊需求

那么对于数据库的select、insert、update、delete操作,MyISAM和InnoDB引擎哪个性能更高呢?一般来讲:

1
2
3
4
select: MyISAM 更优
insert: InnoDB 更优
update: InnoDB 更优
delete: InnoDB 更优

Memory引擎

Memory引擎,顾名思义就是一个将数据存储在内存中的存储引擎。所以一旦MySQL或者主机崩溃后,Memory的表只有表结构仍然会保留,相关数据将全部丢失。

Memory引擎有以下特点

  • 支持hash索引(Memory引擎的默认索引),它使得查询非常快速。
  • 使用表级锁,只支持较低的并发。
  • 不支持text或blob类型,也只支持固定大小行。
  • 重启后,如果打开binlog会写一条truncate table又保证主从数据一致由于不写盘,写入速度很可观。

MyISAM、InnoDB、Memory对比:

此处输入图片的描述

Ref

Comments