MySQL存储引擎Myisam和Innodb

《MySQL存储引擎Myisam和Innodb》由会员分享，可在线阅读，更多相关《MySQL存储引擎Myisam和Innodb（22页珍藏版）》请在装配图网上搜索。

1、以下文章收集的主题包括：MylSAM和Innodb（及其他）存储引擎的特点、优势、缺点，技术细节差异，这两者的性能表现的比较，各自适合应用于怎样的业务和项目。Innodb 对事务控制的支持（原理），行级锁，具体操作。检查修复工具myisamchk 和mysqlcheck 对MylSAM 的支持（和对Innodb 的部分支持），MylSAM的备份与恢复技 巧。Innodb 数据文件ibdata* 和日志文件ib_logfile* 。 Innodb 的Per-Table 表空间。MySQL数据表在系统中表现形式有：ISAM、MyISAM、MERGE、BDB、InnoDB 和HEAP。每种数据表在文

2、件系统中都有不同的表示方式，有一个共同点就是每种数据表至少有一个存放数据表结构定义的.frm文件o ISAM 数据表是最原始的数据表 （.frm,.ISD,.ISM ）, MylSAM 数据表是ISAM 数据表的继承者。InnoDB 由于采用表空间的概念来管理数据表，所以它只有一个与数据表对应.frm文件，同一目录下的其它文件表示为表空间，存储数据表的数据和索引。数据目录是用来存放数据表和相关信息的地方，是数据库的核心。在WINDOWS 系统上，数据目录的位置默认是在c:/mysql/data中。数据目录除存放数据库文件外，还会存放日志文件，状态文件，配置文件，DES密钥文件或服务器的 SSL

3、证书与密钥文件等。在MySQL中，每个数据库其实就是在数据目录下一个子 目录，show databases命令相当于列出数据目录中的目录清单。所以可以通过操作数据目录中的子目录来新建（create ）、删除（drop ）数据库。但 drop database db_name 命令不能删除 db_name目录中创建的 其它非数据表文件；且由于 InnoDB是表空间来管理数据表，所以不能用 rm或del命令删除InnoDB的数据 表。参照：mysql 学习笔记第三章：D:superFileDocumentsA-学习编程数据库完整教程_mysql学习笔记.docMySQL Storage Engin

4、e 小记这段时间在看High Performance MySQL，看到存储引擎这个地方感到很多细节比较陌生，所以总结小记止匕 o为了适应各种不同的运行环境，MYSQL!供了多种不同的存储引擎（Storage Engine ）,在应用程序开发这个层面上，开发者可以根据不同的需求选择适合的Storage Engine方案，更为灵活的是，你可以根据每张表将要存储数据的特点，选择不同的Storage Engine,也就是说，在一个 MYSQ数据库中，可以混合使用多种不同的 Storage Engine首先小瞥一下 MySQ啲体系结构，在最高抽象层度下，可以用Garlan & Shaw的分层结构体系来表

5、示（左）DaiaDJSt Liiyenefl AjchirecMireI jqure It Datdliasc Layered ArchitectirreHigh L&vei Logic冷！ M/SQLContptual ArchltetuFiourc 2: tieiierjl High Level RDBMS Loglcnl Modtiln其中应用层为所有 RDBM用户提供用户接口，逻辑层包括了所有核心功能的实现，物理层则负责将数据存储 在硬件设备上。图中右侧更为具体的描述了逻辑层的组成，查询处理子系统、事务管理子系统、恢复管理子系统和存储管理子系统共同组成了 MySQL的逻辑层。相信Sto

6、rage Engine 的位置是在 Storage Management处，既StorageEngine属于Storage Management子系统的一部分为了让思路更清晰一些，下面给出一幅比较全面的体系结构图（或更确切的说是流程图，只是忽略了反馈）Detailed Heterogeneous Conceptikii MySQL ArclutectureTran paction MsinsgfTwrilTran paction MsinsgfTwrilCorcjrir-Ti-CDitroCorcjrer-Ti-C&itrc-ffiagerVina 芦TranBsclFsxi Mar旳erTr

7、anBsclFsxi Mar旳erMs n and vlrua MBircry (includ&a all yfTeriipnaican dibk* SwckndGTy storage (io1 书EL a3s.D38i ri dv slat st MiInx虬 aati, ano me恂站施晴忖斫Figure 3: Detailed Heterogeneous ConceptUcil MySQL上面三幅图来自于一篇非官方（不保证百分百的正确）的MySQ体系结构的报告，与High Performance MySQ 一书中给出的MySQL大体结构（下图，基本对应于Logic Layer，从第一幅

8、图右侧可以看出MySQLogic layer同样遵从分层体系结构）还是比较吻合的。(onnection manatiTim securityStorage engine连接上图中第二层和第三层之间的接口是并不针对任何存储引擎的单一API,.大概由20个基本的类似“启动事务，返回结果集”等函数组成。存储引擎并不处理SQL相互之间也不通信，它们的任务只是简单的响应高层传来的请求。存储引擎各自的一些特点上面提到的四种存储引擎都有各自适用的环境，这取决于它们独有的一些特征。主要体现在性能、事务、并发控制、参照完整性、缓存、故障恢复，备份及回存 等几个方面。目前比较普及的存储引擎是MylSAM和Inno

9、DB.而MylSAM又是绝大部分 Web应用的首选。MylSAM与InnoDB的主要的不同点在于性能和事务控制上。MylSAM是早期 ISAM（lndexed Sequential Access Method,我现在用的 MySQL5.0已经不支持 ISAM 了）的扩展实现，ISAM被设计为适合处理读频率远大于写频率 这样一种情况，因此ISAM以及后来的MylSAM都没有考虑 对事务的支持，排除了 TPM不需要事务记录，ISAM的查询效率相当可观，而且内存占用很少。MylSAM在继承了这类优点的同时，与时俱进的提供了大量实用的新特性和相关工具。例如考虑到并发控制，提供了表级锁，虽然MylSAM

10、本身不支持容错，但可以 通过myisamchk进行故障恢复。而且由于MylSAM是每张表使用各 自独立的存储文件（MYD数据文件和MYI索引文件），使得备份及恢复十分方便（拷贝覆盖即可），而且还 支持在线恢复。所以如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MylSAM是不二选择InnoDB被设计成适用于高并发读写的情况.使用MVC（Multi-Version Concurrency Control）以及行级锁来提供遵从ACID的事务支持。InnoDB支持外键参照完整性，具备故障恢复能力。另外InnoDB的性能其实还是不 错的，特别是在处理大数据量的情况下，用官方的话说就是：I

11、nnoDB的CPC效率是其他基于磁盘的关系数据 库存储引擎所不能比的。不过InnoDB的备份恢复要麻烦一点，除非你使用了 4.1以后版本提供的Mulit-tablespace 支持，因为InnoDB和MylSAM不同，他的数据文件并不是独立对应于每张表的。而是使用 的共享表空间，简单的拷贝覆盖方法对他不适用，必须在停掉MYSQI后对其进行数据恢复。使用Per-TableTablespacesd，使其每张表对应一个独立的表空间文件，则情况要简单很多。一般来说，如果需要事务支持，并且有较高的并发读写频率，InnoDB是不错的选择。要是并发读写频率不高 的话，其实可以考虑 BDB,但由于在MySQL

12、5.1及其以后版本中，将不再提供 BDB支持。这个选项也就没有了至于Heap和BDB(Berkeley DB )，相对来说，普及率不如前两种，但在有些情况下，还是挺适用的Heap存储引擎就是将数据存储在内存中，由于没有磁盘I./0的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。Heap挺适合做测试的时候使用BDB是 MySQL第一款事务安全的存储引擎。在Berkeley DB database library的基础上建立，同样是事务安全的，但BDB的普及率显然不及InnoDB,因为大多数在 MySQL中寻找支持事务的存储引擎的同时也在找支持 MVC(或是行级锁定存

13、储引擎，而BDB只支持Page-level Lock 。附上一张High Performance MySQL中的各存储引擎的特性表AttributeMyISAMHeapBDBnnoDBTransactionsNoNoYesYesLock granularityTable-TablePage (8 KB)RowStorageSplit filesn-memory、Single file per tableTablespace(s)Isolation levelsNoneNoneRead committedAllPortable formatYesN/ANoYesReferential integ

14、rityNoNoNoYesPrimary key with dataNoNoYesYesMySQL caches data recordsNo、YesYesYesAvailabilityAll versions/All versionsMySQL-MaxAll Versions来源：MySQL中MylSAM 引擎与InnoDB引擎性能简单测试硬件配置CPU : AMD2500+ (1.8G)内存:1G/现代硬盘:80G/IDE软件配置OS : Windows XP SP2SE : PHP5.2.1DB : MySQL5.0.37Web: IIS6MySQL表结构CREATETABLEmyisa

15、m (id int (11) NOTNULLauto_increment,name varchar (100) default NULLcontent text ,PRIMARYKEY (id)ENGINE=MylSAM DEFAULTCHARSETgbk;CREATETABLEinnodb (id int (11) NOTNULLauto_increment,name varchar (100) default NULLcontent text ,PRIMARYKEY (id)ENGINE=InnoDB DEFAULTCHARSETgbk;数据内容$name = heiyeluren;$co

16、ntent = MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎： MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQ默认使用另外一个引擎。 MEMOR存储引擎提供“内存中”表。MERG存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像 MyISAMH样，MEMOR和MERG存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。释：MEMORY储引擎正式地被确定为HEAP引擎。 InnoDB和BDB存储引

17、擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Ma二进制分发版里。InnoDB也默认被包括在所有 MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。EXAMPLE?储引擎是一个“存根”引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服 务，在MySQlB代码中的一个例子，它演示说明 如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。II.插入数据-1 (innodb_flushog_at_trx_commit=1)MyISAM 1W 3/sInnoDB 1W： 219/sMyISAM

18、10W 29/sInnoDB 10W: 2092/sMyISAM 100W 287/sInnoDB 100W：没敢测试插入数据-2 (innodb_flushog_at_trx_commit=0)MyISAM 1W 3/sInnoDB 1W： 3/sMylSAM 10W 30/sInnoDB 10W： 29/sMylSAM 100W 273/sInnoDB 100W: 423/s插入数据 3 (innodb_buffer_pool_size=1024M)InnoDB 1W： 3/sInnoDB 10W： 33/sInnoDB 100W： 607/s插入数据 4 (innodb_buffer_p

19、ool_size=256M, innodb_flush_log_at_trx_commit=1, set autocommit=0)InnoDB 1W： 3/sInnoDB 10W： 26/sInnoDB 100W: 379/sMySQL配置文件（缺省配置）# MySQL Server Instance Configuration Fileclient port=3306mysqldefault-character-set=gbkmysqldport=3306basedir=C:/mysql50/datadir=C:/mysql50/Data/default-character-set=gbk

20、default-storage-engine=INNODBsql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONmax_connections=100query_cache_size=0table_cache=256tmp_table_size=50Mthread_cache_size=8myisam_max_sort_file_size=100Gmyisam_max_extra_sort_file_size=100Gmyisam_sort_buffer_size=100Mkey_buffer_size=8

21、2M read_buffer_size=64K read_rnd_buffer_size=256Ksort_buffer_size=256Kinnodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=2Minnodb_buffer_pool_size=159M innodb_log_file_size=80Minnodb_thread_concurrency=8【总结】可以看出在MySQL 5.0里面，MylSAM和InnoDB存储引擎性能差别并不是很大，针对InnoDB来

22、说，影响性能的主要是innodb_flush_log_at_trx_commit这个选项，如果设置为 1的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0效率能够看到明显提升，当然，同样你可以在SQL中提交“ SET AUTOCOMMIT =0来设置达到好的性能。另外，还听说通过设置innodb_buffer_pool_size能够提升InnoDB的性能，但是我测试发现没有特别明显的提升。基本上我们可以考虑使用 InnoDB来替代我们的MylSAM引擎了，因为InnoDB自身很多良好的特点，比如 事务 支持、存储过程、视图、行级锁定 等等，在并发很多的

23、情况下，相信 InnoDB的表现肯定要比 MyISAM强很多， 当然，相应的在 f中的配置也是比较关键的f的参考配置见下文 MySQL InnoDB性能调整的一 点实践，良好的配置，能够有效的加速你的应用。如果不是很复杂的 Web应用，非关键应用，还是可以继续考虑MylSAM的，这个具体情况可以自己斟酌。参考URL n/5.1/zh/i ndex.html n/5.1/zh/storage-e ngi nes.html#i nnodb来源：浅谈MySQL存储引擎InnoDB和MylSAM 以及行锁注意事项2010-02-21 18:21MyISAM 是MySQL中默认的存储引擎，一般来说不是有

24、太多人关心这个东西。决定使用什么样的存储引擎 是一个很tricky的事情，但是还是值我们去研究一下，这里的文章只考虑MyISAM 和InnoDB这两个，因为这两个是最常见的。下面先让我们回答一些问题：你的数据库有外键吗？你需要事务支持吗？你需要全文索引吗？你经常使用什么样的查询模式？你的数据有多大？myisam 只有索引缓存innodb 不分索引文件数据文件 innodb buffermyisam只能管理索引，在索引数据大于分配的资源时，会由操作系统来cache数据文件依赖于操作系统的caches innodb不管是索引还是数据，都是自己来管理思考上面这些问题可以让你找到合适的方向， 但那并不

25、是绝对的。 如果你需要事务处理或是外键， 那么 InnoDB 可能是比较好的方式。如果你需要全文索引，那么通常来说MylSAM是好的选择，因为这是系统内建的，然而，我们其实并不会经常地去测试两百万行记录。所以，就算是慢一点，我们可以通过使用Sphinx从InnoDB中获得全文索引。数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择 InnoDB 方式，因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MylSAM可能会需要几个小时甚至几天来干这些事，InnoDB只需要几分钟。您操作数据库表的习

26、惯可能也会是一个对性能影响很大的因素。比如：COUNT()在MylSAM表中会非常快，而在InnoDB表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们 的主键太长了也会导致性能问题。大批的inserts语句在MylSAM 下会快一些，但是 updates在InnoDB下会更快一些一一尤其在并发量大的时候。所以，到底你应该使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM 也许会更适合。当然，在大型的环境下使用MyISAM 也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真

27、的应该直接使用InnoDB方式。但需要记住InnoDB的表需要更多的内存和存储，转换100GB的MylSAM 表到InnoDB表可能会让你有非常坏的体验。MyISAM:这个是默认类型，它是基于传统的ISAM 类型,ISAM 是Indexed Sequential Access Method (有索引的顺 序访问方法 ) 的缩写 ,它是存储记录和文件的标准方法 .与其他存储引擎比较 ,MyISAM 具有检查和修复表格的 大多数工具.MylSAM表格可以被压缩，而且它们支持全文搜索.它们不是事务安全的，而且也不支持外键。如果 事务回滚将造成不完全回滚，不具有原子性。如果执行大量的 SELECT，M

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

29、开也可以)，将大大提高性能。InnoDB 和 MyISAM 是在使用 MySQL 最常用的两个表类型，各有优缺点，视具体应用而定。下面是已知的两 者之间的差别，仅供参考。innodbInnoDB 给 MySQL 提供了具有 事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的 事务安全(transaction-safe (ACID compliant)型表。InnoDB 提供了 行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTS)

30、。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定 (lock escalation)，因为InnoDB 的列锁定(row level locks)适宜非常小的空间。 InnoDB 是 MySQL 上第一个提供外键约束 (FOREIGN KEY constraints) 的表引擎。InnoDB 的设计目标是处理 大容量数据库系统， 它的 CPU 利用率 是其它基于磁盘的关系数据库引擎所不能比 的。在技术上， InnoDB 是一套放在 MySQL 后台的完整数据库系统， InnoDB 在主内存中建立其专用的缓 冲池用于高速缓冲数据和索引 。 InnoDB 把数据和索引存

31、放在表空间里，可能包含多个文件 ，这与其它的不 一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。 InnoDB 表的大小只受限于操作系统的文件大 小，一般为 2 GB。InnoDB 所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件，或者是独立的表空间文件) , 相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份binlog，或者用 mysqldump。MyISAMMyISAM 是MySQL缺省存贮引擎 .每张 MyISAM 表被存放在三个文件 。 frm 文件存放表格定义。 数据文件是 MYD (MYData) 。 索引文件是MYI (MYIndex)

32、引伸。因为 MyISAM 相对简单，所以在效率上要优于InnoDB .小型应用使用 MyISAM 是不错的选择 .MyISAM 表是保存成文件的形式 ,在跨平台的数据转移中使用MyISAM 存储会省去不少的麻烦MyIASM 是 IASM 表的新版本，有如下扩展： 二进制层次的可移植性。NULL 列索引。对变长行比 ISAM 表有更少的碎片。 支持大文件。更好的索引压缩。更好的键码统计分布。更好和更快的 auto_increment处理。1. MySQL最大的优势在于 MylSAM 引擎下的简单 SELECT, INSERT和UPDATE快速操作2. MylSAM 类型的数据文件可以在不同操作系

33、统中COPY，这点很重要，布署的时候方便点。以下是一些细节和具体实现的差别：I.InnoDB不支持FULLTEXT 类型的索引。2.lnnoDB 中不保存表的具体行数 ，也就是说，执行 select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是 MylSAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。3对于AUTO_INCREMENT 类型的字段，InnoDB中必须包含只有该字段的索引 ，但是在MylSAM 表中， 可以和其他字段一起建立联合索引。 建表时，mysql可能自动找aut

34、o_increment的字段，并据其建立索引， 对MyISAM 和Innodb都成立4. DELETE FROM table 时，InnoDB不会重新建立表，而是一行一行的删除。也即MyISAM 是通过重新建表、覆盖原来的数据文件.MYD来删除表。与此同时，索引文件.MYI也会重新生成。参见下文mysql错误:Table XXX is marked as crashed and should be repaired转5. LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把 InnoDB表改成 MylSAM表，导入数据后再改成InnoDB表，但是对于使用

35、的额外的InnoDB特性(例如外键)的表不适用。另外，InnoDB表的行锁也不是绝对的，如果在执行一个 SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例女口 update table set num=1 where name like “aaa%”以暂对存储引擎的认识，觉得InnoDB支持外键，在数据量可以用“庞大”来形容时，在有良好的INDEX的 基础上，InnoDB的查询速度应该比 MylSAM 要快。在Falcon有稳定版本前，我想 MylSAM 是一个可用的选择方案。任何一种表都不是万能的， 只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥 MySQL

36、的性能优势。以下是InnoDB和MylSAM的一些联系和区别！1.4.0以上mysqld都支持事务，包括非 max版本。3.23的需要max版本mysqld才能支持事务。2. 创建表时如果不指定 type则默认为myisam，不支持事务。可以用 show create table tablenamel命令看表的类型。2.1对不支持事务的表做start/commit操作没有任何效果，在执行 commit前已经提交。测试:执行一个msyql:use test;drop table if exists tn;create table tn (a varchar(10) type=myisam;dro

37、p table if exists ty;create table ty (a varchar(10) type=innodb;begin;insert into tn values(a); insert into ty values(a); select * from tn; select * from ty; 都能看到一条记录执行另一个 mysql ： use test;select * from tn; select * from ty;只有 tn 能看到一条记录 然后在另一边commit; 才都能看到记录。3. 可以执行以下命令来切换非事务表到事务（数据不会丢失） ， innodb 表

38、比 myisam 表更安全： alter table tablename type=innodb;3.1 innodb 表不能用 repair table 命令和 myisamchk -r table_name但可以用 check table,以及 mysqlcheck OPTIONS database tables(不支持 Innodb 的修复，只可检查)由于InnoDB预设是Row-Level Lock，所以只有明确的指定主键，MySQL才会执行 Row lock （只锁住被选取的资料例 ） ，否则 MySQL 将会执行 Table Lock （将整个资料表单给锁住 ）。select的条件

39、不一样，采用的是行级锁还是表级锁也不一样。转 .tw/archives/900 的说明举个例子 :假设有个表单 products ，里面有 id 跟 name 两个属性， id 是主键。例 1: （明确指定主键，并且有此笔资料， row lock）SELECT * FROM products WHERE id=3 FOR UPDA TE;例 2: （明确指定主键，若查无此笔资料，无 lock）SELECT * FROM products WHERE id=-1 FOR UPDA TE;例 2: （无主键， table lock）SELECT * FROM products WHERE name

40、=Mouse FOR UPDA TE;例 3: （主键不明确， table lock）SELECT * FROM products WHERE id3 FOR UPDA TE;例 4: （主键不明确， table lock）SELECT * FROM products WHERE id LIKE 3 FOR UPDATE;注：SELECT FOR UPDA TE仅适用于InnoDB，且必须在一个事务的交易区块 （BEGIN/COMMIT） 中才能生效。来源： MySQL事务和原子操作MySQL 服务器（ 3.23 至该系列的最高版本，所有 4.0 版本，以及更高版本）支持采用 InnoDB 和

41、 BDB 事务 存储引擎的事务。 InnoDB 提供了全面的 ACID 兼容性。请参见第 15章：存储引擎和表类型 。MySQL 服务器中的其他非事务性存储引擎 （如 MyISAM ）遵从不同的数据完整性范例， 称之为 “原子操作 ”。 按照事务术语， MyISAM 表总能高效地工作在 AUTOCOMMIT=1 模式下 。原子操作通常能提供可比较的 完整性以及更好的性能。由于 MySQL 服务器支持两种范例，因而你能决定 是否利用原子操作 的速度更好地服务于你的应用程序， 或使用事务特性。该选择可按表进行。正如所阐述的那样， 事务性和非事务性表类型之间的权衡主要取决于性能 。事务性表对内存和磁

42、盘空间的 要求更高， CPU 开销也更大 。另一方面， 多种事务性表类型， 如 InnoDB ，也能提供很多显著特性。 MySQL 服务器的模块化设计允许同时使用不同的存储引擎 ，以满足不同的要求， 并在所有情形下， 提供最佳性能。 但是，即便使用非事务性 MyISAM 表 ，你将如何使用 MySQL 服务器的特性来 保持严格的完整性 呢？这些 特性与事务性表类型相比又如何呢？1. 如果应用程序采用了特定的编写方式，依赖于在关键情况下能够调用 ROLLBACK 而不是 COMMIT ， 那么事务性类型更方便。使用事务，还能确保 未完成的更新或崩溃的活动不被提交到数据库 ，能为服务器 提供 自动

43、回滚 的机会，并保存你的数据库。如果使用 非事务性表 ，MySQL 服务器几乎在所有情况下均允许你解决潜在的问题， 方式是 在更新前进行简 单检查，并运行检查数据库一致性的简单脚本 ，如果出现不一致性，该脚本能 自动修复它或给出告警 。注 意，仅使用 MySQL 日志或增加额外日志 ，通常能完美地 更正表 ，同时不会造成 数据完整性 损失。2. 在很多情况下，能够对关键的事务更新进行重写，使之成为“原子 ”类型。一般而言，所有由事务解决的完整性问题均能用 LOCK TABLES 或原子更新 解决，从而确保了服务器不会自动中断，后者是事务性 数据库系统的常见问题。3. 为了安全使用 MySQL

44、服务器， 无论是否使用事务性表 ，仅需启用 备份和二进制日志 功能。这样，你 就能解决使用其他事务性数据库系统时遇到的任何问题。无论使用的数据库系统是什么，启用备份总是个 好主意。事务范型有自己的优点和不足之处。很多用户和应用程序开发人员喜欢这类简单性，在出现问题时或必要 时，通过代码解决问题。但是，即使你是原子操作范型的新手，或更熟悉事务，也请考虑非事务性表的速度益处，与经过优化调整的最快的事务性表相比，它的 速度快35倍。在完整性具有最高重要性的情况下，即使是对 非事务性表 ， MySQL 也能提供事务级别的可靠性和安全性。 如果使用 LOCK TABLES 锁定了表， 所有更新均将被暂时

45、中止直至完整性检查完成 。如果你获得了对某 一表的 READ LOCAL 锁定（与写锁定相对），该表允许在表尾执行并行插入，当其他客户端执行插入操 作时，允许执行读操作。新插入的记录不会被有读锁定属性的客户端看到，直至解除了该锁定为止。使用 INSERT DELAYED ，能够将插入项置于本地队列中，直至锁定解除，不会让客户端等待插入完成。请参 见 13.2.4.2 节， “ INSERT DELAYED 语法 ”。从我们赋与其名称的意义上， “原子 ”绝非不可思议的。它仅意味着，你能确信在每个特性更新运行的同时，其他用户不能干涉它，而且不会出现自动回滚(如果你不小心，对于事务性表，这种情况可

46、能发生)。MySQL 服务器还能保证 不存在脏读 。下面列出了使用 非事务性表 的一些技术： 对于需要事务的循环，通常能使用 LOCK TABLES进行编码，不需要光标来更新正在处理的记录。 要想避免使用ROLLBACK，可采取下述策略：1. 使用 LOCK TABLES 锁定所有希望访问的表 。2. 执行更新前，测试必须为真的条件。3. 如果一切正常，执行更新。4. 使用 UNLOCK TABLES 解除锁定 。与使用具有回滚可能性的事务性表相比，它通常具有更快的速度，虽然并非始终如此。该解决方案唯一不能处理的情形是，在更新中途杀死了线程 。在这种情况下，将释放所有锁定，但某些更新可能尚未执

47、行。 也可以使用函数在单一操作中更新记录。采用下述技术，能获得效率很高的应用程序。o 根据其当前值更改列。o 仅更新出现实际变化的列。 例如，当我们更新某些客户信息时，仅更新已更改的客户数据，与原始行相比，仅测试已更改的数据或依 赖于已更改数据的数据是否未出现变化。 对于已更改数据的测试， 它是通过 UPDATE 语句的 WHERE 子句 完成的。如果记录未更新，将向客户端发出消息： “一些你改变的数据已被其他用户更改 ”。接下来，我们 在窗口中给出了旧行和新行，以便用户决定使用哪个版本。这给出了与列锁定类似的结果，但效果更好，使用相对于其当前值的值，仅更新了某些列。这意味着，典 型的 UPD

48、ATE 语句与下面给出的类似：UPDATE tablename SET pay_back=pay_back+125;UPDATE customerSET customer_date=current_date, address=new address, phone=new phone, money_owed_to_us=money_owed_to_us-125WHEREcustomer_id=id AND address=old address AND phone=old phone; 它很有效，即使其他客户端更改了 pay_back 或 money_owed_to_us 列中的值，也能使用。在

49、很多情况下，用户希望将 LOCK TABLES和/或ROLLBACK用于管理唯一 ID。可以在不使用锁定功能或回滚的情况下，使用 AUTO_INCREMENT 列以及 LAST_INSERT_ID() SQL 函数 或 mysql_insert_id() C API 函数，更有效地处理之。请参见 12.9.3 节， “信息函数 ”。请参见 25.2.3.36 节， “ mysql_insert_id() 。”我们通常能使用代码来处理 行级锁定 方面的需求。在某些情况下，实际上不需要它， InnoDB 表支持行级锁 定。通过 MyISAM 表，能够 在表中使用标志列 ，并完成类似下面的操作：UP

50、DATE tbl_name SET row_flag=1 WHERE id=ID; 如果找到行，而且原始行中的 row_flag 不是 1，对于受影响的行数， MySQL 返回 1。 你可以认为 MySQL 将前述查询更改为：UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag 1;来源：.en/s/blog_4d22b9720100c3x5.htmlMySQL InnoDB性能调整的一点实践因为JavaEye网站的数据库服务器搬家的时候被托管商的工作人员狠狠摔了一下，所以硬盘整个挂掉了，我重新安装数据库服务器的时候，顺手下载了Perc

51、ona patch过的MySQL5.0版本，使用MySQL自带的heavyinnodb配置文件改了改，作为f启动运行。数据库服务器的物理内存有6GB，其中有4GB可以被MySQL使用，f相关配置参数如下：C代码E1.memlock2.innodb_buffer_pool_size = 2G3.innodb_log_file_size = 256M4.innodb_log_files_in_group = 35.#innodb_flush_method=fdatasync默认设置buffer pool越大越好，官方推荐使用物理内存的 50% 80% log_file_size也是越大越好，官方推

52、荐log size加起来要达到buffer pool 的25%- 100%使用memlock可以避免MySQ吶存进入swap，这些都是默认的推 荐配置了，没有什么可以质疑的地方。但是数据库服务器启动以后，运行不太正常。表现岀来的现象是：1、操作系统内存 Disk Cache使用了 2.7GB2、 操作系统swap空间使用了 200ME左右，一直不停进行 swap in/swap out3、CPU的IO Wait偏高，平均在 10%以上这个现象看起来非常怪异和矛盾。IO Wait偏高显然是因为频繁的使用swap进行内存换页引起的，但问题是物理内存非常空闲，操作系统明明有2.7GE空闲物理内存做

53、Disk Cache，怎么不吐出来一点，非要去用swap呢？想来想去只有一种可能性，就是 MySQL存在非常巨大的，频繁的文件读写操作，迫使操作系统不得不分配了 2.7GE的Disk Cache,从而造成了物理内存的不足，被迫使用swap。而可能造成巨大文件读写操作 的就是bufferpool的flush和log file 的flush操作了。因此配置文件做如下修改:C代码01.memlock2.innodb_buffer_pool_size = 2G3.innodb_log_file_size = 64M4.innodb_log_files_in_group = 25.innodb flus

54、h method=O DIRECT减少log file size和数量，使用O_DIRECT重启以后，数据库服务器恢复正常。操作系统Disk Cache下降到900MB Swap使用了 200多MB但是不再进行 swap in/swap out 操作，CPU的IO Wait下降到2-3%。通过这次MySQL InnoDB的调优经历，发现一些和MySQL官方推荐配置不符合的疑惑之处，值得思考和探索：1、innodb_flush_method 究竟应不应该使用 O_DIRECT所有MySQL调优的建议都说，如果硬件没有预读功能，那么使用O_DIRECT各极大降低InnoDB的性能，因为O_DIRE

55、CT跳过了操作系统的文件系统Disk Cache，让MySQL直接读写磁盘了。但是在我的实践中来看，如果不使用O_DIREC丁操作系统被迫开辟大量的Disk Cache用于innodb的读写缓存，不但没有提高读写性能，反而造成读写性能急剧下降。而且buffer pool的数据缓存和操作系统Disk Cache缓存造成了 Double buffer的浪费，显然从我这个实践来看，浪费得非常厉害。说O_DIREC造成MySQL直接读写磁盘造成得性能下降问题，我觉得完全是杞人忧天。因为从JavaEye的数据库监测来看，Innodb的buffer pool 命中率非常高，有 98%以上，真正的磁盘操作是

56、微乎其微的。为了 1%勺磁盘操作能够得到 Disk Cache，而浪费了 98%勺double buffer内存空间，无论从性能上看，还是从内存资源的消耗来看，都是非常不明智的。2、 innodb_log_file_size究竟应该大一点，还是小一点？所有MySQL调优建议都说，innodb_log_file_size要越大越好，避免无谓的buffer pool 的flush 操作。但是在我的实践中来看，innodb_log_file_size开得太大，会明显增加innodb的log写入操作，而且会造成操作系统需要更多的 Disk Cache开销。因此从我的经验来看，innodb_flush_

57、method=O_DIRECT是必须的，而innodb_log_file_size 也不宜太大。来源：mysql 错误：Table XXX is marked as crashed and should be repaired 转一日正在上班，朋友的QQ图标就激烈的闪亮起来，一看，原来是论坛出现问题了 ，具体报错如下：Table .Tablenameposts is marked as crashed and should be repaired提示说论坛的帖子表 posts被标记有问题，需要修复。我记得以前也岀现过类似的问题，但是只要点击Phpmyadmin上的repair按纽就自动修复了

58、，但是这次很绝，什么都没有.于是赶快上网查找原因。最终将问题解决。解决方法如下：找到mysql的安装目录的 bin/myisamchk工具，在命令行中输入：myisamchk -c -r ./data/tablename/posts.MYlmysql ，问题就解决了然后 myisamchk 工具会帮助你恢复数据表的索引。好象也不用重新启动问题分析：1、 错误产生原因，有网友说是频繁查询和更新dede_archives表造成的索引错误，因为我的页面没有静态生 成，而是动态页面，因此比较同意这种说法。还有说法为是 MYSQL 数据库因为某种原因而受到了损坏，如：数据库服务器突发性的断电、在提在数据

59、库 表提供服务时对表的原文件进行某种操作都有可能导致MYSQL 数据库表被损坏而无法读取数据。总之就是因为某些不可测的问题造成表的损坏。 问题的编号为 1452、问题解决办法。 当你试图修复一个被破坏的表的问题时，有三种修复类型。如果你得到一个错误信息指出一个临时文件不能 建立，删除信息所指出的文件并再试一次 -这通常是上一次修复操作遗留下来的。这三种修复方法如下所示：% myisamchk -recover -quick /path/to/tblName% myisamchk -recover /path/to/tblName% myisamchk -safe-recover /path/t

60、o/tblName 第一种是最快的，用来修复最普通的问题；而最后一种是最慢的，用来修复一些其它方法所不能修复的问题。检查和修复 MySQL 数据文件如果上面的方法无法修复一个被损坏的表，在你放弃之前，你还可以试试下面这两个技巧：1、如果你怀疑表的 索引文件 （*.MYI） 发生了不可修复的错误，甚至是丢失了这个文件 ，你可以 使用数据文件 （*.MYD） 和数据格式文件 （*.frm） 重新生成它 。首先制作一个数据文件 （tblName.MYD） 的拷贝。重启你的 MySQL 服务并连接到这个服务上，使用下面的命令删除表的内容：mysql DELETE FROM tblName;在删除表的内

61、容的同时， 会建立一个新的索引文件对MylSAM 表,执行删除表的sql语句时通过重新建一个 结构相同的新表来实现的，此时生成一个新的空数据文件和一个（与空数据关联的）索引文件。退出登录并重新关闭服务，然后用你刚才保存的数据文件 （tblName.MYD） 覆盖新的 （空）数据文件。最后，使用 myisamchk 执行标准的修复 （上面的第二种方法 ），根据表的数据的内容和表的格式文件重新生成 索引数据 。2、如果你的表的 格式文件 （tblName.frm） 丢失了或者是发生了不可修复的错误 ，但是你清楚如何使用相应的CREATE TABLE 语句来重新生成这张表， 你可以重新 生成一个新的 .frm 文件 并和你的数据文件和索引文件 （如果索引文件有问题，使用上面的方法重建一个新的）一起使用。首先制作一个数据和索引文件的拷贝，然后删除原来的文件 （删除数据目录下有关这个表的所有记录 ）。 启动 MySQL 服务并使用当初的 CREATE TABLE 文件建立一个新的表。新的 .frm 文件应该可以正常工作了。 然后将备份的数据和索引文件拷回来覆盖现在的文件。但是最好你还是执行一下标准的