MySQL官方有多种存储引擎:MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。第三方存储引擎中比较有名的有TokuDB、Infobright、InnfiniDB、XtraDB(InnoDB增强版本)。其中,最常见的两种存储引擎是MyISAM和InnoDB。MyISAM是MySQL关系型数据库管理系统的默认存储引擎(MySQL 5.5以前)。这种MySQL表存储结构从旧ISAM代码扩展出许多有用的功能。从MySQL 5.5开始,InnoDB引擎由于其对事务参照完整性,以及更高的并发性等优点开始逐步地取代MyISAM,作为MySQL数据库的默认存储引擎。下面逐一介绍各种存储引擎。
1.MyISAM
MyISAM存储引擎管理非事务表,提供高速存储和检索,以及全文搜索能力。该引擎插入数据快,空间和内存使用比较低。
(1)存储组成
每个MyISAM在磁盘上存储成三个文件。每一个文件的名字就是表的名字,文件名都和表名相同,扩展名指出了文件类型。这里特别要注意的是,MyISAM不缓存数据文件,只缓存索引文件。
1)表定义的扩展名为.frm(frame,存储表定义)。
2)数据文件的扩展名为.MYD(MYData,存储数据)。
3)索引文件的扩展名是.MYI(MYIndex,存储索引)。
数据文件和索引文件可以放置在不同的目录,平均分布I/O,获得更快的速度,而且其索引是压缩的,能加载更多索引,这样内存使用率就对应提高了不少,压缩后的索引也能节约一些磁盘空间。
(2)特点
1)不支持事务,不支持外键约束,但支持全文索引,这可以极大地优化LIKE查询的效率。
2)表级锁定(更新时锁定整个表):其锁定机制是表级索引,这虽然可以让锁定的实现成本很小,但是也同时大大降低了其并发性能。MyISAM不支持行级锁,只支持并发插入的表锁,主要用于高负载的查询。
3)读写互相阻塞:不仅会在写入的时候阻塞读取,MyISAM还会在读取的时候阻塞写入,但读本身并不会阻塞另外的读。
4)不缓存数据,只缓存索引:MyISAM可以通过key_buffer缓存,以大大提高访问性能,减少磁盘I/O,但是这个缓存区只会缓存索引,而不会缓存数据。
[root@mysql]#grep key_buffer my.cnf
key_buffer_size=16M
5)读取速度较快,占用资源相对少。
6)MyISAM引擎是MySQL 5.5之前版本缺省的存储引擎。
7)并发量较小,不适合大量UPDATE。
(3)适用场景
如果表主要用于插入新记录和读出新记录,那么选择MyISAM存储引擎能实现处理的高效率。如果应用的完整性和并发性要求很低,那么也可以选择MyISAM存储引擎。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。具体来说,适用于以下场景:
1)不需要事务支持的业务,一般为读数据比较多的网站应用。
2)并发相对较低的业务(纯读纯写高并发也可以)。
3)数据修改相对较少的业务。
4)以读为主的业务,例如,WWW、BLOG、图片信息数据库、用户数据库、商品数据库等业务。
5)对数据一致性要求不是非常高的业务。
6)中小型网站的部分业务。
小结:单一对数据库的操作都可以使用MyISAM,所谓单一就是尽量纯读,或纯写(INSERT,UPDATE,DELETE)等。生产建议:没有特别需求,一律用InnoDB。
(4)MyISAM引擎调优精要
1)尽量索引(缓存机制)。
2)调整读写优先级,根据实际需要确保重要操作更优先。
3)启用延迟插入改善大批量写入性能(降低写入频率,尽可能多条数据一次性写入)。
4)尽量顺序操作让INSERT数据都写入尾部,减少阻塞。
5)分解大的操作,降低单个操作的阻塞时间。
6)降低并发数,某些高并发场景通过应用进行排队机制。
7)对于相对静态的数据,充分利用Query Cache可以极大地提高访问效率。
[root@mysql 3307]# grep query my.cnf
query_cache_size=2M
query_cache_limit=1M
query_cache_min_res_unit=2k
这几个参数都是MySQL自身缓存设置。
8)MyISAM的COUNT只有在全表扫描的时候特别高效,带有其他条件的COUNT都需要进行实际的数据访问。
9)把主从同步的主库使用InnoDB,从库使用MyISAM引擎。
2.InnoDB
InnoDB用于事务处理应用程序,主要面向OLTP方面的应用。该引擎由InnoDB公司开发,其特点是行锁设置,并支持类似于Oracle的非锁定读,即默认情况下读不产生锁。InnoDB将数据放在一个逻辑表空间中。InnoDB通过多版本并发控制来获得高并发性,实现了ANSI标准的4种隔离级别,默认为Repeatable,使用一种被称为next-key locking的策略避免幻读。对于表中数据的存储,InnoDB采用类似Oracle索引组织表Clustered的方式进行存储。如果对事务的完整性要求比较高,要求实现并发控制,那么选择InnoDB引擎有很大的优势。需要频繁地进行更新,删除操作的数据库,也可以选择InnoDB存储引擎。因为InnoDB存储引擎提供了具有提交(COMMIT)、回滚(ROLLBACK)和崩溃恢复能力的事务安全。
InnoDB类型的表只有ibd文件,分为数据区和索引区,有较好的读写并发能力。物理文件有日志文件、数据文件和索引文件。其中,索引文件和数据文件是放在一个目录下,可以设置共享文件、独享文件两种格式。
(1)特点
1)支持事务:包括ACID事务支持,支持4个事务隔离级别,支持多版本读。
2)行级锁定(更新时一般是锁定当前行):通过索引实现,全表扫描仍然会是表锁,注意间隙锁的影响。
3)支持崩溃修复能力和MVCC。
4)读写阻塞与事务隔离级别相关。
5)具有非常高效的缓存特性:能缓存索引,也能缓存数据。
6)整个表和主键以CLUSTER方式存储,组成一棵平衡树。
7)所有SECONDARY INDEX都会保存主键信息。
8)支持分区、表空间,类似Oracle数据库。
9)支持外键约束(Foreign Key),外键所在的表称为子表,而所依赖的表称为父表。
10)InnoDB支持自增长列(AUTO_INCREMENT),自增长列的值不能为空。
11)InnoDB是索引和数据紧密捆绑的,没有使用压缩,从而会造成InnoDB比MyISAM体积庞大得多。
(2)优点
支持事务,用于事务处理应用程序,具有众多特性,包括ACID事务支持,支持外键,同时支持崩溃修复能力和并发控制。并发量较大,适合大量UPDATE。
(3)缺点
对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。相比MyISAM引擎,InnoDB引擎更消耗资源,速度没有MyISAM引擎快。
(4)适用场景
如果对事务的完整性要求比较高,要求实现并发控制,那么选择InnoDB引擎有很大的优势。需要频繁地进行更新,删除操作的数据库,也可以选择InnoDB存储引擎。具体分类如下:
1)需要事务支持(具有较好的事务特性)。
2)行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成。
3)数据更新较为频繁的场景,例如,BBS(Bulletin Board System,电子公告牌系统)、SNS(Social Network Site,社交网)等。
4)数据一致性要求较高的业务。例如,充值、银行转账等。
5)硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘I/O。
物理数据文件:
[root@mysql 3307]# 11 data/ibdatal
-rw-rw---- 1 mysql mysql 134217728 May 15 08:31 data/ibdata1
6)相比MyISAM引擎,InnoDB引擎更消耗资源,速度没有MyISAM引擎快。
(5)InnoDB引擎调优精要
1)主键尽可能小,避免给SECONDARY INDEX带来过大的空间负担。
2)避免全表扫描,因为会使用表锁。
3)尽可能缓存所有的索引和数据,提高响应速度,减少磁盘I/O消耗。
4)在执行大量插入操作的时候,尽量自己控制事务而不要使用AUTOCOMMIT自动提交。有开关可以控制提交方式。
5)合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性。
6)避免主键更新,因为这会带来大量的数据移动。
3.MEMORY(HEAP)
MEMORY存储引擎(之前称为HEAP)提供“内存中”的表。如果需要很快的读写速度,对数据的安全性要求较低,那么可选择MEMORY存储引擎。MEMORY存储引擎对表大小有要求,不能建太大的表。所以,这类数据库只适用相对较小的数据库表。如果mysqld进程发生异常,那么数据库就会重启或崩溃,数据就会丢失,因此,MEMORY存储引擎中的表的生命周期很短,一般只使用一次,非常适合存储临时数据。
(1)特点
1)MEMORY存储引擎将所有数据保存在内存(RAM)中,在需要快速查找引用和其他类似数据的环境下,可提供极快的访问速度。
2)每个基于MEMORY存储引擎的表实际对应一个磁盘文件,该文件的文件名和表名是相同的,类型为.frm。该文件只存储表的结构,而其数据文件都是存储在内存中,这样有利于对数据的快速处理,提高整个表的处理能力。
3)MEMORY存储引擎默认使用哈希(HASH)索引,其速度比使用B-Tree型要快,但安全性不高。如果希望使用B-Tree型,那么在创建的时候可以引用。
(2)适用场景
如果需要很快的读写速度,那么在需要快速查找引用和其他类似数据的环境下,对数据的安全性要求较低,可选择MEMORY存储引擎。
(3)优点
将所有数据保存在内存(RAM)中,默认使用HASH索引,数据的处理速度快。
(4)缺点
不支持事务,安全性不高;MEMORY存储引擎对表大小有要求,不能建太大的表。
4.MERGE
MERGE存储引擎允许将一组使用MyISAM存储引擎的并且表结构相同(即每张表的字段顺序、字段名称、字段类型、索引定义的顺序及其定义的方式必须相同)的数据表合并为一个表,方便了数据的查询。需要注意的是,使用MERGE“合并”起来的表结构相同的表最好不要有主键,否则会出现这种情况:一共有两个成员表,其主键在两个表中存在相同情况,但是写了一条按相同主键值查询的SQL语句,这时只能查到UNION列表中第一个表中的数据。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。
适用场景:MERGE存储引擎允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起,并作为1个对象引用它们。对于诸如数据仓库等,VLDB(Very Large DataBase,超大型数据库)环境十分适合。
优点:便于同时引用多个数据表而无须发出多条查询。
缺点:不支持事务。
5.BDB(BerkeleyDB)
BDB是事务型存储引擎,支持COMMIT、ROLLBACK和其他事务特性,它由××软件公司(http://www.***.com)开发。BDB是一个高性能的嵌入式数据库编程库(引擎),它可以用来保存任意类型的键/值对(Key/Value Pair),而且可以为一个键保存多个数据。BDB可以支持数干的并发线程同时操作数据库,支持最大256TB的数据。BDB存储引擎处理事务安全的表,并以哈希为基础的存储系统。
适用场景:BDB存储引擎适合快速地读写某些数据,特别是不同KEY的数据。
优点:支持事务。
缺点:在没有索引的列上操作速度很慢。
6.EXAMPLE
EXAMPLE存储引擎是一个“存根”引擎,可以用这个引擎创建表,但数据不能存储在该引擎中。EXAMPLE存储引擎可为快速创建定制的插件式存储引擎提供帮助。
7.NDB
NDB存储引擎是一个集群存储引擎,是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎,类似于Oracle的RAC,但它是Share Nothing的架构,因此,能提供更高级别的高可用性和可扩展性。NDB的特点是数据全部放在内存中,因此,通过主键查找非常快。它在MySQL-Max 5.1二进制分发版里提供。
(1)特性
1)分布式:分布式存储引擎,可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分。
2)支持事务:和InnoDB一样,支持事务。
3)可与mysqld不在一台主机:可以和mysqld分开存在于独立的主机上,然后通过网络和mysqld通信交互。
4)内存需求量巨大:新版本索引以及被索引的数据必须存放在内存中,老版本所有数据和索引必须存在于内存中。
(2)适用场景
1)具有非常高的并发需求。
2)对单个请求的响应并不是非常严格。
3)查询简单,过滤条件较为固定,每次请求数据量较少。
4)具有高性能查找要求的应用程序,这类查找需求还要求具有最高的正常工作时间和可用性。
(3)优点
1)分布式:分布式存储引擎,可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分。
2)支持事务:和InnoDB一样,支持事务。
3)可与mysqld不在一台主机:可以和mysqld分开存在于独立的主机上,然后通过网络和mysqld通信交互。
(4)缺点
内存需求量巨大:新版本索引以及被索引的数据必须存放在内存中,老版本所有数据和索引必须存在于内存中。它的连接操作是在MySQL数据库层完成,不是在存储引擎层完成的,这意味着,复杂的连接操作需要巨大的网络开销,查询速度会很慢。
8.ARCHIVE
ARCHIVE存储引擎只支持INSERT和SELECT操作,其设计的主要目的是提供高速的插入和压缩功能。
适用场景:ARCHIVE非常适合存储归档数据,如日志信息。
优点:ARCHIVE存储引擎被用来无索引地、非常小地覆盖存储的大量数据。为大量很少引用的历史、归档或安全审计信息的存储和检索提供了完美的解决方案。
缺点:不支持事务,只支持INSERT和SELECT操作。
9.CSV
CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。
10.BLACKHOLE
BLACKHOLE存储引擎接收但不存储数据,并且检索总是返回一个空集。用于临时禁止对数据库的应用程序输入。该存储引擎支持事务,而且支持MVCC的行级锁,主要用于日志记录或同步归档。
11.FEDERATED
FEDERATED存储引擎不存放数据,它至少指向一台远程MySQL数据库服务器上的表,该存储引擎把数据存在远程数据库中,非常类似于Oracle的透明网关。在MySQL 5.1中,它只和MySQL一起工作,使用MySQL C Client API。在未来的分发版中,想要让它使用其他驱动器或客户端连接方法连接到另外的数据源。该存储引擎能够将多个分离的MySQL服务器连接起来,从多个物理服务器创建一个逻辑数据库,十分适合于分布式环境或数据集市环境。
12.ISAM
最原始的存储引擎就是ISAM,它管理着非事务性表,后来被MyISAM代替了,而且MyISAM是向后兼容的,因此可以忘记这个ISAM存储引擎。
可以在MySQL中使用显示引擎的命令得到一个可用引擎的列表,如下:
上面查询结果显示了可用的数据库引擎的全部名单以及在当前的数据库服务器中是否支持这些引擎。
下面的表格列出了一些常见的比较重要的存储引擎。