MySQL存储引擎Myisam和Innodb

以下文章收集的主题包括：MyISAM和Innodb（及其他）存储引擎的特点、优势、缺点，技术细节差异，这两者的性能表现的比较，各自适合应用于怎样的业务和项目。Innodb对事务控制的支持（原理），行级锁，具体操作。检查修复工具myisamchk和mysqlcheck对MyISAM的支持（和对Innodb的部分支持），MyISAM的备份与恢复技巧。Innodb数据文件ibdata*和日志文件ib_logfile*。Innodb的Per-Table表空间。MySQL数据表在系统中表现形式有：ISAM、MyISAM、MERGE、BDB、InnoDB和HEAP。每种数据表在文件系统中都有不同的表示方式，有一个共同点就是每种数据表至少有一个存放数据表结构定义的.frm文件。ISAM数据表是最原始的数据表（.frm,.ISD,.ISM），MyISAM数据表是ISAM数据表的继承者。InnoDB由于采用表空间的概念来管理数据表，所以它只有一个与数据表对应.frm文件，同一目录下的其它文件表示为表空间，存储数据表的数据和索引。数据目录是用来存放数据表和相关信息的地方，是数据库的核心。在WINDOWS系统上，数据目录的位置默认是在c:/mysql/data中。数据目录除存放数据库文件外，还会存放日志文件，状态文件，配置文件，DES密钥文件或服务器的SSL证书与密钥文件等。在MySQL中，每个数据库其实就是在数据目录下一个子目录，showdatabases命令相当于列出数据目录中的目录清单。所以可以通过操作数据目录中的子目录来新建（create）、删除（drop）数据库。但dropdatabasedb_name命令不能删除db_name目录中创建的其它非数据表文件；且由于InnoDB是表空间来管理数据表，所以不能用rm或del命令删除InnoDB的数据表。参照：mysql学习笔记第三章：D:\superFile\Documents\A-学习编程\数据库\mysql\完整教程_mysql学习笔记.docMySQLStorageEngine小记这段时间在看《HighPerformanceMySQL》，看到存储引擎这个地方感到很多细节比较陌生，所以总结小记一些。为了适应各种不同的运行环境，MYSQL提供了多种不同的存储引擎（StorageEngine）,在应用程序开发这个层面上，开发者可以根据不同的需求选择适合的StorageEngine方案，更为灵活的是，你可以根据每张表将要存储数据的特点，选择不同的StorageEngine,也就是说，在一个MYSQL数据库中，可以混合使用多种不同的StorageEngine首先小瞥一下MySQL的体系结构，在最高抽象层度下，可以用Garlan&Shaw的分层结构体系来表示（左）其中应用层为所有RDBMS用户提供用户接口，逻辑层包括了所有核心功能的实现，物理层则负责将数据存储在硬件设备上。图中右侧更为具体的描述了逻辑层的组成，查询处理子系统、事务管理子系统、恢复管理子系统和存储管理子系统共同组成了MySQL的逻辑层。相信StorageEngine的位置是在StorageManagement处，既StorageEngine属于StorageManagement子系统的一部分为了让思路更清晰一些，下面给出一幅比较全面的体系结构图（或更确切的说是流程图，只是忽略了反馈）上面三幅图来自于一篇非官方（不保证百分百的正确）的MySQL体系结构的报告，与《HighPerformanceMySQL》一书中给出的MySQL大体结构（下图,基本对应于LogicLayer，从第一幅图右侧可以看出MySQLlogiclayer同样遵从分层体系结构）还是比较吻合的。连接上图中第二层和第三层之间的接口是并不针对任何存储引擎的单一API,.大概由20个基本的类似“启动事务，返回结果集”等函数组成。存储引擎并不处理SQL，相互之间也不通信，它们的任务只是简单的响应高层传来的请求。存储引擎各自的一些特点上面提到的四种存储引擎都有各自适用的环境，这取决于它们独有的一些特征。主要体现在性能、事务、并发控制、参照完整性、缓存、故障恢复，备份及回存等几个方面。目前比较普及的存储引擎是MyISAM和InnoDB.而MyISAM又是绝大部分Web应用的首选。MyISAM与InnoDB的主要的不同点在于性能和事务控制上。MyISAM是早期ISAM(IndexedSequentialAccessMethod,我现在用的MySQL5.0已经不支持ISAM了）的扩展实现，ISAM被设计为适合处理读频率远大于写频率这样一种情况，因此ISAM以及后来的MyISAM都没有考虑对事务的支持，排除了TPM，不需要事务记录，ISAM的查询效率相当可观，而且内存占用很少。MyISAM在继承了这类优点的同时，与时俱进的提供了大量实用的新特性和相关工具。例如考虑到并发控制，提供了表级锁，虽然MyISAM本身不支持容错，但可以通过myisamchk进行故障恢复。而且由于MyISAM是每张表使用各自独立的存储文件（MYD数据文件和MYI索引文件），使得备份及恢复十分方便（拷贝覆盖即可），而且还支持在线恢复。所以如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择InnoDB被设计成适用于高并发读写的情况.使用MVCC(Multi-VersionConcurrencyControl)以及行级锁来提供遵从ACID的事务支持。InnoDB支持外键参照完整性，具备故障恢复能力。另外InnoDB的性能其实还是不错的，特别是在处理大数据量的情况下,用官方的话说就是：InnoDB的CPU效率是其他基于磁盘的关系数据库存储引擎所不能比的。不过InnoDB的备份恢复要麻烦一点，除非你使用了4.1以后版本提供的Mulit-tablespace支持，因为InnoDB和MyISAM不同，他的数据文件并不是独立对应于每张表的。而是使用的共享表空间，简单的拷贝覆盖方法对他不适用，必须在停掉MYSQL后对其进行数据恢复。使用Per-TableTablespacesd，使其每张表对应一个独立的表空间文件，则情况要简单很多。一般来说，如果需要事务支持，并且有较高的并发读写频率，InnoDB是不错的选择。要是并发读写频率不高的话，其实可以考虑BDB,但由于在MySQL5.1及其以后版本中，将不再提供BDB支持。这个选项也就没有了至于Heap和BDB（BerkeleyDB），相对来说，普及率不如前两种，但在有些情况下，还是挺适用的Heap存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。Heap挺适合做测试的时候使用BDB是MySQL第一款事务安全的存储引擎。在BerkeleyDBdatabaselibrary的基础上建立，同样是事务安全的，但BDB的普及率显然不及InnoDB，因为大多数在MySQL中寻找支持事务的存储引擎的同时也在找支持MVCC或是行级锁定存储引擎，而BDB只支持Page-levelLock。附上一张《HighPerformanceMySQL》中的各存储引擎的特性表AttributeMyISAMHeapBDBInnoDBTransactionsNoNoYesYesLockgranularityTableTablePage(8KB)RowStorageSplitfilesIn-memorySinglefilepertableTablespace(s)IsolationlevelsNoneNoneReadcommittedAllPortableformatYesN/ANoYesReferentialintegrityNoNoNoYesPrimarykeywithdataNoNoYesYesMySQLcachesdatarecordsNoYesYesYesAvailabilityAllversionsAllversionsMySQL-MaxAllVersions来源：中MyISAM引擎与InnoDB引擎性能简单测试[硬件配置]CPU:AMD2500+(1.8G)内存:1G/现代硬盘:80G/IDE[软件配置]OS:WindowsXPSP2SE:PHP5.2.1DB:MySQL5.0.37Web:IIS6[MySQL表结构]CREATETABLE`myisam`(`id`int(11)NOTNULLauto_increment,`name`varchar(100)defaultNULL,`content`text,PRIMARYKEY(`id`))ENGINE=MyISAMDEFAULTCHARSET=gbk;CREATETABLE`innodb`(`id`int(11)NOTNULLauto_increment,`name`varchar(100)defaultNULL,`content`text,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=gbk;[数据内容]$name=heiyeluren;$content=MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：·MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。·MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。释：MEMORY存储引擎正式地被确定为HEAP引擎。·InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所有MySQL5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。·EXAMPLE存储引擎是一个“存根”引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。;[插入数据-1](innodb_flush_log_at_trx_commit=1)MyISAM1W：3/sInnoDB1W：219/sMyISAM10W：29/sInnoDB10W：2092/sMyISAM100W：287/sInnoDB100W：没敢测试[插入数据-2](innodb_flush_log_at_trx_commit=0)MyISAM1W：3/sInnoDB1W：3/sMyISAM10W：30/sInnoDB10W：29/sMyISAM100W：273/sInnoDB100W：423/s[插入数据3](innodb_buffer_pool_size=1024M)InnoDB1W：3/sInnoDB10W：33/sInnoDB100W：607/s[插入数据4](innodb_buffer_pool_size=256M,innodb_flush_log_at_trx_commit=1,setautocommit=0)InnoDB1W：3/sInnoDB10W：26/sInnoDB100W：379/s[MySQL配置文件](缺省配置)#MySQLServerInstanceConfigurationFile[client]port=3306[mysql]default-character-set=gbk[mysqld]port=3306basedir=C:/mysql50/datadir=C:/mysql50/Data/defa