MySql MyISAM和InnoDB的区别

MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的 顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量 的SELECT，MyISAM是更好的选择。

　　InnoDB:这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表, 对于支持事物的InnoDB类型的表，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打 开也可以），将大大提高性能

1. MySQL默认采用的是MyISAM。
2. MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。
3. InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即 MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以 MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。
4. InnoDB支持外键，MyISAM不支持。
5. InnoDB的主键范围更大，最大是MyISAM的2倍。
6. InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、 varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空 格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
7. MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。
8. 没有where的count(*)使用MyISAM要比InnoDB快得多。因 为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般 要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大

MySQL引擎简介
     MySQL是我们比较常用的一种数据库软件。它有着诸多的优点，如开源的，免费的等等。其实它还有一个很好的特点，那就是有多种引擎可以供你选择。如果赛车手能根据不同的路况，地形随手更换与之最适宜的引擎，那么他们将创造奇迹。然而目前他们还做不到那样便捷的更换引擎，但是我们却可以！
     所谓知己知彼方可百战不殆，要想将它们发挥到极致，首先我们应该来认识一下MySQL提供给我们的这几种引擎。
     一般来说，MySQL有以下几种引擎：ISAM、MyISAM、HEAP、InnoDB和Berkley（BDB）。注意：不同的版本支持的引擎是有差异的。当然啦，如果你感觉自己的确技术高超，你还能够使用MySQL++来创建自己的数据库引擎，这个已经out of my knowledge，牛人可以参照MySQL++ API帮助来实现。下面逐一叙述这5种引擎各自的特性：

ISAM

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MySQL能够支持这样的备份应用程序。

MyISAM

MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的MyISAMPack工具。

MyISAM强调了快速读取操作，这可能就是为什么MySQL受到了Web开发如此青睐的主要原因：在Web开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和Internet平台提供商（Internet Presence Provider，IPP）只允许使用MyISAM格式。

HEAP

HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MyISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。让我再重复一遍：在你用完表格之后，不要忘记删除表格。

InnoDB和Berkley DB

InnoDB和Berkley DB（BDB）数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MySQL++ API。在使用MySQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和MyISAM引擎慢很多，但是InnoDB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

认识完了这么多的引擎，我们也知道了它们应该在什么情况下拿来应急，那么接着我们就要学会如何来更换这些引擎了。

Globle：一种最简单的方法就是更改服务器配置，直接将其设置成你所需要的引擎。这个在win下通过更改服务器安装目录下的mysql.ini中的default-storage-engine项即可，也可以通过运行MySQL Server Instance Configuration Wizard做简单的设置。

Per Table:除了全局的方法外，还有一种更灵活的配置方法，那就是按表来设置引擎，这样我们就可以把那些需要用到事务处理的表设置成InnoDB，其他设置成MyISAM,将性能提升到极致，这是不是很令人兴奋呢？设置方法也比较简单：

1、你可以在Create Table语句的最后添加扩展语句，如TYPE=MyISAM（或者ENGINE = INNODB）来指定当前标的引擎类型。也可以用Alter语句在建立表个后进行更改。当你不清楚当前数据库中各表的引擎时可以使用SHOW TABLE STATUS FROM DBname来查看。

2、使用随MySQL服务器发布同时提供的MySQL客户端来创建表，在创建时可以选择使用的存储引擎。

不同的引擎选择在不同的业务处理上，性能将有天壤之别！！

题外话：

要想服务器性能达到最佳，在配置服务器时也需要好好考虑。如果是在Win下那么可以通过运行MySQL Server Instance Configuration Wizard来设置，它将引导你设置当前服务器的类型等等信息。

还有再有大量数据需要插入的时候可以考虑使用INSERT DELAYED语句（具体语法参考MySQL手册）。当一个客户端使用INSERT DELAYED时，会立刻从服务器处得到一个确定。并且行被排入队列，当表没有被其它线程使用时，此行被插入。使用INSERT DELAYED的另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。当然它也有其适用范围，具体参考手册，我就不再赘述了。
 

 

 

一、数据引擎简介

在MySQL 5.1中，MySQL AB引入了新的插件式存储引擎体系结构，允许将存储引擎加载到正在运新的MySQL服务器中。

使用MySQL插件式存储引擎体系结构，允许数据库专业人员为特定的应用需求选择专门的存储引擎，完全不需要管理任何特殊的应用编码要求。采用MySQL服务器体系结构，由于在存储级别上提供了一致和简单的应用模型和API，应用程序编程人员和DBA可不再考虑所有的底层实施细节。因此，尽管不同的存储引擎具有不同的能力，应用程序是与之分离的。

MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

·        MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。

·        MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。

注释：MEMORY存储引擎正式地被确定为HEAP引擎。

·        InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所有MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。

·        EXAMPLE存储引擎是一个“存根”引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。

·        NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。它在MySQL-Max 5.1二进制分发版里提供。这个存储引擎当前只被Linux, Solaris, 和Mac OS X 支持。在未来的MySQL分发版中，我们想要添加其它平台对这个引擎的支持，包括Windows。

·        ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。

·        CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。

·        BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。

·        FEDERATED存储引擎把数据存在远程数据库中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。

插件式存储引擎体系结构提供了标准的管理和支持服务集合，它们对所有的基本存储引擎来说是共同的。存储引擎本身是数据库服务器的组件，负责对在物理服务器层面上维护的基本数据进行实际操作。

这是一种高效的模块化体系结构，它为那些希望专注于特定应用需求的人员提供了巨大的便利和益处，这类特殊应用需求包括数据仓储、事务处理、高可用性情形等，同时还能利用独立于任何存储引擎的一组接口和服务。

应用程序编程人员和DBA通过位于存储引擎之上的连接器API和服务层来处理MySQL数据库。如果应用程序的变化需要改变底层存储引擎，或需要增加1个或多个额外的存储引擎以支持新的需求，不需要进行大的编码或进程更改就能实现这类要求。MySQL服务器体系结构提供了一致和易于使用的API，这类API适用于多种存储引擎，通过该方式，该结构将应用程序与存储引擎的底层复杂性隔离开来。
在下图中，以图形方式介绍了MySQL插件式存储引擎体系结构：
 
The MySQL pluggable storage engine architecture
 

二、选择存储引擎

与MySQL一起提供的各种存储引擎在设计时考虑了不同的使用情况。为了更有效地使用插件式存储体系结构，最好了解各种存储引擎的优点和缺点。

在下面的表格中，概要介绍了与MySQL一起提供的存储引擎：

Storage engine comparison

下述存储引擎是最常用的：

·        MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。

·        InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。

·        BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

·        Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

·        Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

·        Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

·        Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

·        Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

·        Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

请记住，对于整个服务器或方案，你并不一定要使用相同的存储引擎，你可以为方案中的每个表使用不同的存储引擎，这点很重要。

三、将存储引擎指定给表

可以在创建新表时指定存储引擎，或通过使用ALTER TABLE语句指定存储引擎。

要想在创建表时指定存储引擎，可使用ENGINE参数：
CREATE TABLE engineTest(
id INT
) ENGINE = MyISAM;
 
也可以使用TYPE选项到CREATE TABLE语句来告诉MySQL你要创建什么类型的表。
 
CREATE TABLE engineTest(
id INT
) TYPE = MyISAM;
 
虽然TYPE仍然在MySQL 5.1中被支持，现在ENGINE是首选的术语。
 
如果你省略掉ENGINE或TYPE选项，默认的存储引擎被使用。一般的默认是MyISAM，但你可以用--default-storage-engine或--default-table-type服务器启动选项来改变它，或者通过设置storage_engine或table_type系统变量来改变。

要想更改已有表的存储引擎，可使用ALTER TABLE语句：
ALTER TABLEengineTestENGINE =ARCHIVE;
ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;
 
如果你试着使用一个未被编译进MySQL的存储引擎，或者试着用一个被编译进MySQL但没有被激活的存储引擎，MySQL取而代之地创建一个MyISAM类型的表。当你在支持不同存储引擎的MySQL服务器之间拷贝表的时候，上述的行为是很方便的。（例如，在一个复制建立中，可能你的主服务器为增加安全而支持事务存储引擎，但从服务器为更快的速度而仅使用非事务存储引擎。)
 
在不可用的类型被指定时，自动用MyISAM表来替代，这会对MySQL的新用户造成混淆。无论何时一个表被自动改变之时，产生一个警告。
 
MySQL总是创建一个.frm文件来保持表和列的定义。表的索引和数据可能被存储在一个或多个文件里，这取决于表的类型。服务器在存储引擎级别之上创建.frm文件。单独的存储引擎创建任何需要用来管理表的额外文件。
 
一个数据库可以包含不同类型的表。
 
四、存储引擎和事务
 
下述存储引擎支持事务：

·        InnoDB：通过MVCC支持事务，允许COMMIT、ROLLBACK和保存点。

·        NDB：通过MVCC支持事务，允许COMMIT和ROLLBACK。

·        BDB：支持事务，允许COMMIT和ROLLBACK。
事务安全表(TST) 比起非事务安全表 (NTST)有几大优势：

·        更安全。即使MySQL崩溃或遇到硬件问题，要么自动恢复，要么从备份加事务日志恢复，你可以取回数据。

·        你可以合并许多语句，并用COMMIT语句同时接受它们全部（如果autocommit被禁止掉）。

·        你可以执行ROLLBACK来忽略你的改变（如果autocommit被禁止掉）。

·        如果更新失败，你的所有改变都变回原来。（用非事务安全表，所有发生的改变都是永久的）。

·        事务安全存储引擎可以给那些当前用读得到许多更新的表提供更好的部署。

非事务安全表自身有几个优点，因为没有事务开支，所有优点都能出现：

·        更快

·        需要更少的磁盘空间

·        执行更新需要更少的内存

你可以在同一个语句中合并事务安全和非事务安全表来获得两者最好的情况。尽管如此，在autocommit被禁止掉的事务里，变换到非事务安全表依旧即时提交，并且不会被回滚。

虽然MySQL支持数个事务安全存储引擎，为获得最好结果，你不应该在一个事务那混合不同表类型。如果你混合表类型会发生问题，

五、插入搜索引擎

能够使用存储引擎之前，必须使用INSTALL PLUGIN语句将存储引擎plugin（插件）装载到mysql。例如，要想加载example引擎，首先应加载ha_example.so模块：
INSTALL PLUGINha_exampleSONAME 'ha_example.so';

文件.so必须位于MySQL服务器库目录下（典型情况下是installdir/lib）。

六、拔出存储引擎

要想拔出存储引擎，可使用UNINSTALL PLUGIN语句：
UNINSTALL PLUGINha_example;

如果拔出了正被已有表使用的存储引擎，这些表将成为不可访问的。拔出存储引擎之前，请确保没有任何表使用该存储引擎。

为了安装插件式存储引擎，plugin文件必须位于恰当的MySQL库目录下，而且发出INSTALL PLUGIN语句的用户必须具有SUPER权限。

 

创建table时可以通过engine关键字指定使用的存储引擎，如果省略则使用系统默认的存储引擎：
CREATE TABLE t (i INT) ENGINE = MYISAM;

查看系统中支持的存储引擎类型：
mysql> show engines;

标准安装程序中只提供部分引擎的支持，如果需要使用其他的存储引擎，需要使用源代码加不同的参数重新编译。其中DEFAULT表明系统的默认存储引擎，可以通过修改配置参数来变更：
default-storage-engine=MyISAM

查看某个存储引擎的具体信息
mysql> show engine InnoDB status/G;