MySQL引擎

MySQL引擎

    MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。如果技术高超，还可以使用MySQL++ API自己做一个引擎。下面介绍几种数据库引擎：

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

    MyISAM：MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

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

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

    MySQL 官方对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次插入/更新的

    一般来说，MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

    一般情况下，MySQL会默认提供多种存储引擎，可以通过下面的查看:

    （1）看你的MySQL现在已提供什么存储引擎: mysql> show engines;

    （2）看你的MySQL当前默认的存储引擎: mysql> show variables like '%storage_engine%';

    （3）你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): mysql> show create table 表名;

    所有的性能测试在：Micrisoft window xp sp2 ， Intel(R) Pentinum(R) M processor 1.6oGHz 1G 内存的电脑上测试。

    测试方法：连续提交10个query， 表记录总数：38万 ， 时间单位 s

        引擎类型                    MyISAM                InnoDB              性能相差

        count                      0.0008357            3.0163                3609

        查询主键                   0.005708              0.1574                27.57

        查询非主键                  24.01                   80.37                 3.348

        更新主键                   0.008124             0.8183                 100.7

        更新非主键                0.004141             0.02625               6.338

        插入                         0.004188             0.3694                 88.21

    （1）加了索引以后，对于MyISAM查询可以加快：4 206.09733倍，对InnoDB查询加快510.72921倍，同时对MyISAM更新速度减慢为原来的1/2，InnoDB的更新速度减慢为原来的1/30。要看情况决定是否要加索引，比如不查询的log表，不要做任何的索引。

    （2）如果你的数据量是百万级别的，并且没有任何的事务处理，那么用MyISAM是性能最好的选择。

    （3）InnoDB表的大小更加的大，用MyISAM可省很多的硬盘空间。

        在我们测试的这个38w的表中，表占用空间的情况如下：
            引擎类型                    MyISAM               InnoDB
            数据                       53,924 KB           58,976 KB
            索引                       13,640 KB           21,072 KB
            占用总空间              67,564 KB           80,048 KB
 
        另外一个176W万记录的表， 表占用空间的情况如下：

            引擎类型                MyIsam               InnorDB
            数据                   56,166 KB           90,736 KB
            索引                   67,103 KB           88,848 KB
            占用总空间        123,269 KB         179,584 KB

Mysql引擎

在缺省情况下，MySQL支持三个引擎：ISAM、MyISAM和HEAP。另外两种类型InnoDB和Berkley（BDB），也常常可以使用。

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包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

数据库引擎设定与切换：

1）CREATE TABLE tblMyISAM (

id INT NOT NULL AUTO_INCREMENT,

PRIMARY KEY (id),

value_a TINYINT

)TYPE=MyISAM；

2）ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB；

3）SHOW TABLE STATUS FROM tblInnoDB；

 

mysql6新特性：

1）新Falcon事务存储引擎（“Falcon存 储引擎” ） 。

2）支持更多的Unicode字符集： utf16 ，utf32 ，和4字节utf8 。 这些字符集支持这些附加的Unicode字符集，也就是那些在基础多语言基础之外的字符 。

3）增加了 BACKUP DATABASE 和 RESTORE 语句来进行备份和还原操作. 见第6.3节， “使用MySQL备份” 。

4）改进INFORMATION_SCHEMA数据库，并增加了INFORMATION_SCHEMA.PARAMETERS 表，INFORMATION_SCHEMA.ROUTINES 增加了新列

5）对子查询和Join进行了优化,包括对MyISAD和InnoB存储引擎分散范围内的批量索 引访问。

6）RESET SLAVE不再更改复制连接的参数; 以 前，它重置他们到命令行指定的数值

7）LOCK TABLES 语法已经扩展，支持不会自动事务提交的事务表锁。在后面的 LOCK TABLES ... IN SHARE MODE 或者LOCK TABLES ... IN EXCLUSIVE MODE 你可以使用未提及的表级锁，你也可以确保LOCK TABLES 语句可以连续的得到多次的事务锁, 增加额外的表格到锁集 合，而无需解锁以前已经锁住的表格。当使用LOCK TABLES with IN SHARE MODE 或者在EXCLUSIVE MODE ,表级锁在事务结束前不会解锁。 使用LOCK TABLE获 得的事务锁在事务结束时释放,包括显示的提交或者回滚，或者由于语句引起的隐式提交，或者由于链接 关闭. LOCK TABLES的行为在 READ和WRITE锁 时报出不变( 也就是当不使用 IN SHARE MODE 或者 IN EXCLUSIVE MODE )。

8）增强的XML功能，包括一个新的LOAD XML 语 法

9）支持扩展的注释，包括表，列和索引。

 

 

存储引擎是什么？

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储。遗憾的是，其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能，要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL，我们仅需要修改我们使用的存储引擎就可以了。

在这篇文章中，我们不准备集中讨论不同的存储引擎的技术方面的问题(尽管我们不可避免地要研究这些因素的某些方面)，相反，我们将集中介绍这些不同的引擎分别最适应哪种需求和如何启用不同的存储引擎。为了实现这个目的，在介绍每一个存储引擎的具体情况之前，我们必须要了解一些基本的问题。

如何确定有哪些存储引擎可用

你可以在MySQL(假设是MySQL服务器4.1.2以上版本)中使用显示引擎的命令得到一个可用引擎的列表。

mysql> show engines;

+------------+---------+----------------------------------------------------+

| Engine | Support | Comment |

+------------+---------+-----------------------------------------------------+

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |

| HEAP | YES | Alias for MEMORY |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |

| MERGE | YES | Collection of identical MyISAM tables |

| MRG_MYISAM | YES | Alias for MERGE |

| ISAM | NO | Obsolete storage engine, now replaced by MyISAM |

| MRG_ISAM | NO | Obsolete storage engine, now replaced by MERGE |

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |

| INNOBASE | YES | Alias for INNODB |

| BDB | NO | Supports transactions and page-level locking |

| BERKELEYDB | NO | Alias for BDB |

| NDBCLUSTER | NO | Clustered, fault-tolerant, memory-based tables |

| NDB | NO | Alias for NDBCLUSTER |

| EXAMPLE | NO | Example storage engine |

| ARCHIVE | NO | Archive storage engine |

| CSV | NO | CSV storage engine |

+------------+---------+-------------------------------------------------------+

16 rows in set (0.01 sec) 这个表格显示了可用的数据库引擎的全部名单以及在当前的数据库服务器中是否支持这些引擎。

对于MySQL 4.1.2以前版本，可以使用mysql> show variables like "have_%"(显示类似“have_%”的变量):

mysql> show variables like "have_%";

+------------------+----------+

| Variable_name | Value |

+------------------+----------+

| have_bdb | YES |

| have_crypt | YES |

| have_innodb | DISABLED |

| have_isam | YES |

| have_raid | YES |

| have_symlink | YES |

| have_openssl | YES |

| have_query_cache | YES |

+------------------+----------+

8 rows in set (0.01 sec)

你可以通过修改设置脚本中的选项来设置在MySQL安装软件中可用的引擎。如果你在使用一个预先包装好的MySQL二进制发布版软件，那么，这个软件就包含了常用的引擎。然而，需要指出的是，如果你要使用某些不常用的引擎，特别是CSV、 RCHIVE(存档)和BLACKHOLE(黑洞)引擎，你就需要手工重新编译MySQL源码 。

使用一个指定的存储引擎

你可以使用很多方法指定一个要使用的存储引擎。最简单的方法是，如果你喜欢一种能满足你的大多数数据库需求的存储引擎，你可以在MySQL设置文件中设置一个默认的引擎类型(使用storage_engine 选项)或者在启动数据库服务器时在命令行后面加上--default-storage-engine或--default-table-type选项。

更灵活的方式是在随MySQL服务器发布同时提供的MySQL客户端时指定使用的存储引擎。最直接的方式是在创建表时指定存储引擎的类型，向下面这样:

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB

你还可以改变现有的表使用的存储引擎，用以下语句:

ALTER TABLE mytable ENGINE = MyISAM

然而，你在以这种方式修改表格类型的时候需要非常仔细，因为对不支持同样的索引、字段类型或者表大小的一个类型进行修改可能使你丢失数据。如果你指定一个在你的当前的数据库中不存在的一个存储引擎，那么就会创建一个MyISAM(默认的)类型的表。

各存储引擎之间的区别

为了做出选择哪一个存储引擎的决定，我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能。这种功能使我们能够把不同的存储引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。一些引擎具有能过促使你做出决定的独特的功能，我们一会儿再仔细研究这些具体问题。

字段和数据类型

虽然所有这些引擎都支持通用的数据类型，例如整型、实型和字符型等，但是，并不是所有的引擎都支持其它的字段类型，特别是BLOG(二进制大对象)或者TEXT文本类型。其它引擎也许仅支持有限的字符宽度和数据大小。

这些局限性可能直接影响到你可以存储的数据，同时也可能会对你实施的搜索的类型或者你对那些信息创建的索引产生间接的影响。这些区别能够影响你的应用程序的性能和功能，因为你必须要根据你要存储的数据类型选择对需要的存储引擎的功能做出决策。

锁定

数据库引擎中的锁定功能决定了如何管理信息的访问和更新。当数据库中的一个对象为信息更新锁定了，在更新完成之前，其它处理不能修改这个数据(在某些情况下还不允许读这种数据)。

锁定不仅影响许多不同的应用程序如何更新数据库中的信息，而且还影响对那个数据的查询。这是因为查询可能要访问正在被修改或者更新的数据。总的来说，这种延迟是很小的。大多数锁定机制主要是为了防止多个处理更新同一个数据。由于向数据中插入信息和更新信息这两种情况都需要锁定，你可以想象，多个应用程序使用同一个数据库可能会有很大的影响。

不同的存储引擎在不同的对象级别支持锁定，而且这些级别将影响可以同时访问的信息。得到支持的级别有三种:表锁定、块锁定和行锁定。支持最多的是表锁定，这种锁定是在MyISAM中提供的。在数据更新时，它锁定了整个表。这就防止了许多应用程序同时更新一个具体的表。这对应用很多的多用户数据库有很大的影响，因为它延迟了更新的过程。

页级锁定使用Berkeley DB引擎，并且根据上载的信息页(8KB)锁定数据。当在数据库的很多地方进行更新的时候，这种锁定不会出现什么问题。但是，由于增加几行信息就要锁定数据结构的最后8KB，当需要增加大量的行，也别是大量的小型数据，就会带来问题。

行级锁定提供了最佳的并行访问功能，一个表中只有一行数据被锁定。这就意味着很多应用程序能够更新同一个表中的不同行的数据，而不会引起锁定的问题。只有InnoDB存储引擎支持行级锁定。

建立索引

建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能。不同的存储引擎提供不同的制作索引的技术。有些技术也许会更适合你存储的数据类型。

有些存储引擎根本就不支持索引，其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(例如FEDERATED或者BLACKHOLE引擎)。

事务处理

事务处理功能通过提供在向表中更新和插入信息期间的可靠性。这种可靠性是通过如下方法实现的，它允许你更新表中的数据，但仅当应用的应用程序的所有相关操作完全完成后才接受你对表的更改。例如，在会计处理中每一笔会计分录处理将包括对借方科目和贷方科目数据的更改，你需要要使用事务处理功能保证对借方科目和贷方科目的数据更改都顺利完成，才接受所做的修改。如果任一项操作失败了，你都可以取消这个事务处理，这些修改就不存在了。如果这个事务处理过程完成了，我们可以通过允许这个修改来确认这个操作。