MYSQL各个存储引擎（转）

    MyISAM

    每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

    MyISAM引擎是大多数MySQL安装程序的默认引擎，起源于早期版本MySQL支持的ISAM引擎。这种引擎提供了最佳的性能和功能的组合，尽管它缺少事务处理功能(使用InnoDB或者BDB引擎)并且使用表级锁定。

    但是执行一下查询发现，我在测试的时候使用的两个测试表在创建的时候没有指定引擎，但是发现这两个表的存储引擎都为InnoDB。（当然我们修改配职文件my.ini中的default-storage-engine=INNODB来修改）。

mysql> select table_name,engine from tables where table_name like 'test%';
+------------+--------+
| table_name | engine |
+------------+--------+
| test | InnoDB |
| test1 | InnoDB |
+------------+--------+
2 rows in set (0.08 sec)

    MYSQL文档的解释是：“存储引擎和表类型:当MySQL被用MySQL配置向导安装在Windows平台上，InnoDB存储引擎替代MyISAM存储引擎作为替代。 ”


    为了测试MyISAM引擎表级锁定，我们是用MyISAM引擎创建测试表TEST_ISAM。

    测试中打开两个数据库连接，一个连接执行call p_tst_isam();另外一个执行单条mysql> insert into test_isam(id,mc) values(1,'1');结果在第一个连接还没有执行完的时候，第二个就完毕，没有发现MyISAM引擎锁表，这个问题我们暂时不再继续测试下去。测试中发现一个问题，MyISAM引擎的表的INSERT速度远远大于InnoDB引擎：

mysql> CREATE TABLE TEST_ISAM(ID INTEGER,MC VARCHAR(60)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.38 sec)

mysql> select table_name,engine from information_schema.tables where table_name like 'test%';
+------------+--------+
| table_name | engine |
+------------+--------+
| test | InnoDB |
| test1 | InnoDB |
| test_isam | MyISAM |
+------------+--------+
3 rows in set (0.00 sec)

创建存储过程p_test_isam

delimiter //
create procedure p_test_isam()
begin
declare counter int;
set counter = 1000000;
while counter >= 1 do
insert into test_isam(id,mc) values(counter,'test');
set counter = counter - 1;
end while;
end
//

delimiter ;

    我们在以前的测试例子中： InnoDB引擎 INSERT 1000条数据花费34秒

mysql> call p_test();
Query OK, 1 row affected (34.48 sec)

MyISAM引擎INSERT 1000000 条数据花费时间20多秒：

mysql> call p_test_isam();
Query OK, 1 row affected (22.95 sec)

    所以，我们如果在使用非事物处理的表(也就是一些只有单用户使用的表)的时候可以采用MyISAM引擎来提高速度，当然了，INSERT的时候可以利用 MYSQL的BULK INSERT功能来出也是能大大提高性能的，这些我们将在MYSQL数据库优化一?***：

INSERT INTO TEST VALUES(VAL11,VAL12),(VAL21,VAL22)……

    测试完毕，翻看一下MYSQL文档，的确有下面一段话，和我们的测试结果吻合。

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

MERGE引擎

    MERGE引擎类型允许你把许多结构相同的表合并为一个表。然后可以执行查询，从多个表返回的结果就像从一个表返回的结果一样。每一个合并的表必须有同样的表定义。

    MERGE存储引擎在下面这种使用场合会最为有用，如果需要把日志纪录不停的录入MySQL数据库，并且每天、每周或者每个月都创建一个单一的表，而且要制作来自多个表的合计查询，MERGE表这时会非常有效。然而，这项功能有局限性。只能合并MyISAM表而且必须严格遵守相同的表定义的限制。虽然这看起来好像是一个大问题，但是，如果使用另外一种表类型(例如InnoDB)，这种合并可能就不需要了。

    MEMORY(内存)存储引擎

    MEMORY(内存)存储引擎(以前称作HEAP存储引擎)在内存中存储全部数据。一旦MySQL服务器关闭，存储在内存中的任何信息都将丢失。然而，单个表的格式将保留，使你能够创建一个用于存储信息的临时表。这样，每次数据库服务器启动时，你不需要重新创新这个表就可以快速地访问信息。

    长期使用MEMORY存储引擎一般来说不是一个好主意，因为数据很容易丢失。然而，如果你有足够的内存，使用基于MEMORY的表在大型数据集中执行复杂的查询是一种非常有效的方法，它能够很大程度的提高性能。

    使用MEMORY表的最佳方法是使用一个“select”语句从你原来的基于磁盘的表中选择一个大型的数据集，然后对你需要的具体部分进一步分析那些信息。我过去曾经使用这个技术提取了一个月的网络记录数据，实际上就是从使用ARCHIVE存储引擎制作的表中提取的数据，然后对具体的URL、网站和其它重点进行查询。

    EXAMPLE引擎

    EXAMPLE引擎实际上是一个存储引擎编程的例子，能够用作MySQL系统中其它引擎的基础。EXAMPLE不支持数据插入，对于任何形式的数据库访问来说也不是一个实用的引擎。然而，EXAMPLE是一个很好的指南，指导你如何开发自己的存储引擎，因此对于程序员来说是一个有效的引擎。

    BDB存储引擎

    BDB表可能有一个更大的崩溃幸存机会，并且也具有对事务COMMIT和ROLLBACK操作的能力，BDB引擎支持页级锁定。

    InnoDB存储引擎

    InnoDB给MySQL提供了具有提交、回滚、崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

    InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

    InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。

    InnoDB被用来在众多需要高性能的大型数据库站点上产生。著名的Internet新闻站点Slashdot.org运行在InnoDB上。 Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。

给出一个事务控制的例子：

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A | B |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)

    InnoDB存储引擎相关的内容非常复杂涉及到事物处理、日志、备份和恢复、锁定、多版本、性能、表和索引的结构、磁盘IO等很多方面的知识。

    我们以上只讲述了MYSQL的几个存储引擎，使我们能够对MYSQL的存储引擎有个基本的认识。MYSQL还提供了BDB (BerkeleyDB)存储引擎、FEDERATED存储引擎、ARCHIVE存储引擎、CSV存储引擎、BLACKHOLE存储引擎等，这里就不再详细说明了。