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中，每个数据库其实就是在数据目录下一个子目录，show databases命令相当于列出数据目录中的目录清单。所以可以通过操作数据目录中的子目录来新建（create）、删除（drop）数据库。但drop database db_name命令不能删除db_name目录中创建的其它非数据表文件；且由于InnoDB是表空间来管理数据表，所以不能用rm或del命令删除InnoDB的数据表。

参照：mysql学习笔记第三章：D:\superFile\Documents\A-学习编程\数据库\mysql\完整教程_mysql 学习笔记.doc

MySQL Storage Engine 小记

这段时间在看《High Performance MySQL》，看到存储引擎这个地方感到很多细节比较陌生，所以总结小记一些。

为了适应各种不同的运行环境，MYSQL提供了多种不同的存储引擎（Storage Engine ）,在应用程序开发这个层面上，开发者可以根据不同的需求选择适合的Storage Engine 方案，更为灵活的是，你可以根据每张表将要存储数据的特点，选择不同的Storage Engine,也就是说，在一个MYSQL数据库中，可以混合使用多种不同的Storage Engine

首先小瞥一下MySQL的体系结构，在最高抽象层度下，可以用Garlan & Shaw的分层结构体系来表示（左）

其中应用层为所有RDBMS用户提供用户接口，逻辑层包括了所有核心功能的实现，物理层则负责将数据存储在硬件设备上。

图中右侧更为具体的描述了逻辑层的组成，查询处理子系统、事务管理子系统、恢复管理子系统和存储管理子系统共同组成了MySQL的逻辑层。相信Storage Engine的位置是在Storage Management处，既Storage Engine属于Storage Management子系统的一部分

为了让思路更清晰一些，下面给出一幅比较全面的体系结构图（或更确切的说是流程图，只是忽略了反馈）

上面三幅图来自于一篇非官方（不保证百分百的正确）的MySQL体系结构的报告，与《High Performance MySQL》一书中给出的MySQL大体结构（下图,基本对应于Logic Layer，从第一幅图右侧可以看出MySQL logic layer 同样遵从分层体系结构）还是比较吻合的。

连接上图中第二层和第三层之间的接口是并不针对任何存储引擎的单一API,.大概由20个基本的类似“启动事务，返回结果集”等函数组成。存储引擎并不处理SQL，相互之间也不通信，它们的任务只是简单的响应高层传来的请求。

存储引擎各自的一些特点

上面提到的四种存储引擎都有各自适用的环境，这取决于它们独有的一些特征。主要体现在性能、事务、并发控制、参照完整性、缓存、故障恢复，备份及回存等几个方面。

目前比较普及的存储引擎是MyISAM和InnoDB.而MyISAM又是绝大部分Web应用的首选。MyISAM与InnoDB的主要的不同点在于性能和事务控制上。

MyISAM是早期ISAM(Indexed Sequential Access Method,我现在用的MySQL5.0已经不支持ISAM了）的扩展实现，ISAM被设计为适合处理读频率远大于写频率这样一种情况，因此ISAM以及后来的MyISAM都没有考虑对事务的支持，排除了TPM，不需要事务记录，ISAM的查询效率相当可观，而且内存占用很少。MyISAM在继承了这类优点的同时，与时俱进的提供了大量实用的新特性和相关工具。例如考虑到并发控制，提供了表级锁，虽然MyISAM本身不支持容错，但可以通过myisamchk进行故障恢复。而且由于MyISAM是每张表使用各自独立的存储文件（MYD数据文件和MYI索引文件），使得备份及恢复十分方便（拷贝覆盖即可），而且还支持在线恢复。

所以如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择

InnoDB被设计成适用于高并发读写的情况.使用MVCC(Multi-Version Concurrency Control)以及行级锁来提供遵从ACID的事务支持。InnoDB支持外键参照完整性，具备故障恢复能力。另外 InnoDB的性能其实还是不错的，特别是在处理大数据量的情况下,用官方的话说就是： InnoDB的CPU效率是其他基于磁盘的关系数据库存储引擎所不能比的。不过InnoDB的备份恢复要麻烦一点，除非你使用了4.1以后版本提供的

Mulit-tablespace支持，因为InnoDB和MyISAM不同，他的数据文件并不是独立对应于每张表的。而是使用的共享表空间，简单的拷贝覆盖方法对他不适用，必须在停掉MYSQL后对其进行数据恢复。使用Per-Table Tablespacesd，使其每张表对应一个独立的表空间文件，则情况要简单很多。

一般来说，如果需要事务支持，并且有较高的并发读写频率，InnoDB是不错的选择。要是并发读写频率不高的话，其实可以考虑BDB,但由于在MySQL5.1及其以后版本中，将不再提供BDB支持。这个选项也就没有了

至于Heap和BDB（Berkeley DB），相对来说，普及率不如前两种，但在有些情况下，还是挺适用的

Heap存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。

Heap挺适合做测试的时候使用

BDB是MySQL第一款事务安全的存储引擎。在Berkeley DB database library的基础上建立，同样是事务安全的，但BDB的普及率显然不及InnoDB，因为大多数在MySQL中寻找支持事务的存储引擎的同时也在找支持MVCC或是行级锁定存储引擎，而BDB只支持Page-level Lock。

附上一张《High Performance MySQL》中的各存储引擎的特性表

来源：https://www.360docs.net/doc/5e2995311.html,/topic/211951

MySQL中MyISAM引擎与InnoDB引擎性能简单测试

[硬件配置]

CPU : AMD2500+ (1.8G)

内存: 1G/现代

硬盘: 80G/IDE

[软件配置]

OS : Windows XP SP2

SE : PHP5.2.1

DB : MySQL5.0.37

Web: IIS6

[MySQL表结构]

CREATE TABLE `myisam` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(100) default NULL,

`content` text,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

CREATE TABLE `innodb` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(100) default NULL,

`content` text,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=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也默认被包括在所有MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。·EXAMPLE存储引擎是一个“存根”引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。";

[插入数据-1] (innodb_flush_log_at_trx_commit=1)

MyISAM 1W：3/s

InnoDB 1W：219/s

MyISAM 10W：29/s

InnoDB 10W：2092/s

MyISAM 100W：287/s

InnoDB 100W：没敢测试

[插入数据-2] (innodb_flush_log_at_trx_commit=0)

MyISAM 1W：3/s

InnoDB 1W：3/s

MyISAM 10W：30/s

InnoDB 10W：29/s

MyISAM 100W：273/s

InnoDB 100W：423/s

[插入数据3] (innodb_buffer_pool_size=1024M)

InnoDB 1W：3/s

InnoDB 10W：33/s

InnoDB 100W：607/s

[插入数据4] (innodb_buffer_pool_size=256M, innodb_flush_log_at_trx_commit=1, set autocommit=0)

InnoDB 1W：3/s

InnoDB 10W：26/s

InnoDB 100W：379/s

[MySQL 配置文件] (缺省配置)

# MySQL Server Instance Configuration File

[client]

port=3306

[mysql]

default-character-set=gbk

[mysqld]

port=3306

basedir="C:/mysql50/"

datadir="C:/mysql50/Data/"

default-character-set=gbk

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100

query_cache_size=0

table_cache=256

tmp_table_size=50M

thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_max_extra_sort_file_size=100G

myisam_sort_buffer_size=100M

key_buffer_size=82M

read_buffer_size=64K

read_rnd_buffer_size=256K

sort_buffer_size=256K

innodb_additional_mem_pool_size=4M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=2M

innodb_buffer_pool_size=159M

innodb_log_file_size=80M

innodb_thread_concurrency=8

【总结】

可以看出在MySQL 5.0里面，MyISAM和InnoDB存储引擎性能差别并不是很大，针对InnoDB来说，影响性能的主要是 innodb_flush_log_at_trx_commit这个选项，如果设置为1的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0效率能够看到明显提升，当然，同样你可以在SQL中提交“SET AUTOCOMMIT = 0”来设置达到好的性能。另外，还听说通过设置

innodb_buffer_pool_size能够提升InnoDB的性能，但是我测试发现没有特别明显的提升。

基本上我们可以考虑使用InnoDB来替代我们的MyISAM引擎了，因为InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多，当然，相应的在https://www.360docs.net/doc/5e2995311.html,f中的配置也是比较关键的[https://www.360docs.net/doc/5e2995311.html,f的参考配置见下文《MySQL InnoDB性能调整的一点实践》]，良好的配置，能够有效的加速你的应用。

如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

参考URL：

https://www.360docs.net/doc/5e2995311.html,/doc/refman/5.1/zh/index.html

https://www.360docs.net/doc/5e2995311.html,/doc/refman/5.1/zh/storage-engines.html#innodb

来源：https://www.360docs.net/doc/5e2995311.html,/blog/271214

浅谈MySQL存储引擎InnoDB和MyISAM 以及行锁注意事项

2010-02-21 18:21

MyISAM 是MySQL中默认的存储引擎，一般来说不是有太多人关心这个东西。决定使用什么样的存储引擎是一个很tricky的事情，但是还是值我们去研究一下，这里的文章只考虑MyISAM 和InnoDB这两个，因为这两个是最常见的。

下面先让我们回答一些问题：

◆你的数据库有外键吗？

◆你需要事务支持吗？

◆你需要全文索引吗？

◆你经常使用什么样的查询模式？

◆你的数据有多大？

myisam只有索引缓存

innodb不分索引文件数据文件innodb buffer

myisam只能管理索引，在索引数据大于分配的资源时，会由操作系统来cache；数据文件依赖于操作系统的cache。innodb不管是索引还是数据，都是自己来管理

思考上面这些问题可以让你找到合适的方向，但那并不是绝对的。如果你需要事务处理或是外键，那么InnoDB 可能是比较好的方式。如果你需要全文索引，那么通常来说MyISAM是好的选择，因为这是系统内建的，然而，我们其实并不会经常地去测试两百万行记录。所以，就算是慢一点，我们可以通过使用Sphinx从InnoDB 中获得全文索引。

数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事，InnoDB只需要几分钟。

您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如：COUNT() 在MyISAM 表中会非常快，而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在MyISAM下会快一些，但是updates 在InnoDB 下会更快一些——尤其在并发量大的时候。

所以，到底你应该使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM 也许会更适合。当然，在大型的环境下使用MyISAM 也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方式。但需要记住InnoDB 的表需要更多的内存和存储，转换100GB 的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打开也可以），将大大提高性能。

===============================================================

InnoDB和MyISAM是在使用MySQL最常用的两个表类型，各有优缺点，视具体应用而定。下面是已知的两者之间的差别，仅供参考。

innodb

InnoDB 给MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，因为InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。

InnoDB 的设计目标是处理大容量数据库系统，它的CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在MyISAM 中，表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。

InnoDB所有的表都保存在同一个数据文件ibdata1中（也可能是多个文件，或者是独立的表空间文件）,相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份binlog，或者用mysqldump。

MyISAM

MyISAM 是MySQL缺省存贮引擎.

每张MyISAM 表被存放在三个文件。frm 文件存放表格定义。数据文件是MYD (MYData) 。索引文件是MYI (MYIndex) 引伸。

因为MyISAM相对简单，所以在效率上要优于InnoDB..小型应用使用MyISAM是不错的选择.

MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦

MyIASM是IASM表的新版本，有如下扩展：

二进制层次的可移植性。

NULL列索引。

对变长行比ISAM表有更少的碎片。

支持大文件。

更好的索引压缩。

更好的键码统计分布。

更好和更快的auto_increment处理。

1.MySQL最大的优势在于MyISAM引擎下的简单SELECT，INSERT和UPDA TE快速操作

2.MyISAM类型的数据文件可以在不同操作系统中COPY，这点很重要，布署的时候方便点。

以下是一些细节和具体实现的差别：

1.InnoDB不支持FULL TEXT类型的索引。

2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where 条件时，两种表的操作是一样的。

3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。[建表时，mysql可能自动找auto_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表改成MyISAM 表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB 表同样会锁全表，例如update table set num=1 where name like “%aaa%”

以暂对存储引擎的认识，觉得InnoDB 支持外键，在数据量可以用“庞大”来形容时，在有良好的INDEX 的基础上，InnoDB 的查询速度应该比MyISAM 要快。

在Falcon 有稳定版本前，我想MyISAM 是一个可用的选择方案。

任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。

===============================================================

以下是InnoDB和MyISAM的一些联系和区别！

1. 4.0以上mysqld都支持事务，包括非max版本。3.23的需要max版本mysqld才能支持事务。

2. 创建表时如果不指定type则默认为myisam，不支持事务。

可以用show create table [tablename]命令看表的类型。

2.1 对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交。测试：

执行一个msyql：

use test;

drop table if exists tn;

create table tn (a varchar(10)) type=myisam;

drop 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表比myisam表更安全：

alter table tablename type=innodb;

3.1 innodb表不能用repair table命令和myisamchk -r table_name

但可以用check table，以及mysqlcheck [OPTIONS] database [tables]（不支持Innodb的修复，只可检查）

==============================================================

由于InnoDB 预设是Row-Le vel Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

select的条件不一样，采用的是行级锁还是表级锁也不一样。

转https://www.360docs.net/doc/5e2995311.html,/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='Mouse' FOR UPDA TE;

例3: (主键不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDA TE;

例4: (主键不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注: SELECT FOR UPDA TE 仅适用于InnoDB，且必须在一个事务的交易区块(BEGIN/COMMIT)中才能生

效。

来源：https://www.360docs.net/doc/5e2995311.html,/blog/600549

MySQL-事务和原子操作

MySQL服务器（3.23至该系列的最高版本，所有4.0版本，以及更高版本）支持采用InnoDB和BDB事务存储引擎的事务。InnoDB提供了全面的ACID兼容性。请参见第15章：存储引擎和表类型。

MySQL服务器中的其他非事务性存储引擎（如MyISAM）遵从不同的数据完整性范例，称之为“原子操作”。按照事务术语，MyISAM表总能高效地工作在AUTOCOMMIT=1模式下。原子操作通常能提供可比较的完整性以及更好的性能。

由于MySQL服务器支持两种范例，因而你能决定是否利用原子操作的速度更好地服务于你的应用程序，或使用事务特性。该选择可按表进行。

正如所阐述的那样，事务性和非事务性表类型之间的权衡主要取决于性能。事务性表对内存和磁盘空间的要求更高，CPU开销也更大。另一方面，多种事务性表类型，如InnoDB，也能提供很多显著特性。MySQL 服务器的模块化设计允许同时使用不同的存储引擎，以满足不同的要求，并在所有情形下，提供最佳性能。但是，即便使用非事务性MyISAM表，你将如何使用MySQL服务器的特性来保持严格的完整性呢？这些特性与事务性表类型相比又如何呢？

1. 如果应用程序采用了特定的编写方式，依赖于在关键情况下能够调用ROLLBACK而不是COMMIT，那么事务性类型更方便。使用事务，还能确保未完成的更新或崩溃的活动不被提交到数据库，能为服务器提供自动回滚的机会，并保存你的数据库。

如果使用非事务性表，MySQL服务器几乎在所有情况下均允许你解决潜在的问题，方式是在更新前进行简单检查，并运行检查数据库一致性的简单脚本，如果出现不一致性，该脚本能自动修复它或给出告警。注意，仅使用MySQL日志或增加额外日志，通常能完美地更正表，同时不会造成数据完整性损失。

2. 在很多情况下，能够对关键的事务更新进行重写，使之成为“原子”类型。一般而言，所有由事务解决的完整性问题均能用LOCK TABLES或原子更新解决，从而确保了服务器不会自动中断，后者是事务性数据库系统的常见问题。

3. 为了安全使用MySQL服务器，无论是否使用事务性表，仅需启用备份和二进制日志功能。这样，你就能解决使用其他事务性数据库系统时遇到的任何问题。无论使用的数据库系统是什么，启用备份总是个好主意。

事务范型有自己的优点和不足之处。很多用户和应用程序开发人员喜欢这类简单性，在出现问题时或必要时，通过代码解决问题。但是，即使你是原子操作范型的新手，或更熟悉事务，也请考虑非事务性表的速度益处，与经过优化调整的最快的事务性表相比，它的速度快3～5倍。

在完整性具有最高重要性的情况下，即使是对非事务性表，MySQL也能提供事务级别的可靠性和安全性。如果使用LOCK TABLES锁定了表，所有更新均将被暂时中止直至完整性检查完成。如果你获得了对某一表的READ LOCAL锁定（与写锁定相对），该表允许在表尾执行并行插入，当其他客户端执行插入操作时，允许执行读操作。新插入的记录不会被有读锁定属性的客户端看到，直至解除了该锁定为止。使用INSERT DELAYED，能够将插入项置于本地队列中，直至锁定解除，不会让客户端等待插入完成。请参见13.2.4.2节，“INSERT DELAYED语法”。

从我们赋与其名称的意义上，“原子”绝非不可思议的。它仅意味着，你能确信在每个特性更新运行的同时，其他用户不能干涉它，而且不会出现自动回滚（如果你不小心，对于事务性表，这种情况可能发生）。MySQL 服务器还能保证不存在脏读。

下面列出了使用非事务性表的一些技术：

·对于需要事务的循环，通常能使用LOCK TABLES进行编码，不需要光标来更新正在处理的记录。·要想避免使用ROLLBACK，可采取下述策略：

1. 使用LOCK TABLES锁定所有希望访问的表。

2. 执行更新前，测试必须为真的条件。

3. 如果一切正常，执行更新。

4. 使用UNLOCK TABLES解除锁定。

与使用具有回滚可能性的事务性表相比，它通常具有更快的速度，虽然并非始终如此。该解决方案唯一不能处理的情形是，在更新中途杀死了线程。在这种情况下，将释放所有锁定，但某些更新可能尚未执行。·也可以使用函数在单一操作中更新记录。采用下述技术，能获得效率很高的应用程序。

o 根据其当前值更改列。

o 仅更新出现实际变化的列。

例如，当我们更新某些客户信息时，仅更新已更改的客户数据，与原始行相比，仅测试已更改的数据或依赖于已更改数据的数据是否未出现变化。对于已更改数据的测试，它是通过UPDATE语句的WHERE子句完成的。如果记录未更新，将向客户端发出消息：“一些你改变的数据已被其他用户更改”。接下来，我们在窗口中给出了旧行和新行，以便用户决定使用哪个版本。

这给出了与列锁定类似的结果，但效果更好，使用相对于其当前值的值，仅更新了某些列。这意味着，典型的UPDATE语句与下面给出的类似：

UPDATE tablename SET pay_back=pay_back+125;

UPDATE customer

SET

customer_date='current_date',

address='new address',

phone='new phone',

money_owed_to_us=money_owed_to_us-125

WHERE

customer_id=id AND address='old address' AND phone='old phone';

它很有效，即使其他客户端更改了pay_back或money_owed_to_us列中的值，也能使用。

·在很多情况下，用户希望将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表，能够在表中使用标志列，并完成类似下面的操作：

UPDATE 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;

来源：https://www.360docs.net/doc/5e2995311.html,/s/blog_4d22b9720100c3x5.html

MySQL InnoDB性能调整的一点实践

因为JavaEye网站的数据库服务器搬家的时候被托管商的工作人员狠狠摔了一下，所以硬盘整个挂掉了，我重新安装数据库服务器的时候，顺手下载了Percona patch过的MySQL5.0版本，使用MySQL自带的heavy innodb配置文件改了改，作为https://www.360docs.net/doc/5e2995311.html,f启动运行。数据库服务器的物理内存有6GB，其中有4GB可以被MySQL 使用，https://www.360docs.net/doc/5e2995311.html,f相关配置参数如下：

C代码

1.memlock

2.innodb_buffer_pool_size = 2G

3.innodb_log_file_size = 256M

4.innodb_log_files_in_group = 3

5.#innodb_flush_method=fdatasync 默认设置

buffer pool越大越好，官方推荐使用物理内存的50%－80%；log_file_size也是越大越好，官方推荐log size 加起来要达到buffer pool的25%－100%。使用memlock可以避免MySQL内存进入swap，这些都是默认的推荐配置了，没有什么可以质疑的地方。但是数据库服务器启动以后，运行不太正常。表现出来的现象是：

1、操作系统内存Disk Cache使用了2.7GB

2、操作系统swap空间使用了200MB左右，一直不停进行swap in/swap out

3、CPU的IO Wait偏高，平均在10%以上

这个现象看起来非常怪异和矛盾。IO Wait偏高显然是因为频繁的使用swap进行内存换页引起的，但问题是物理内存非常空闲，操作系统明明有2.7GB空闲物理内存做Disk Cache，怎么不吐出来一点，非要去用swap 呢？

想来想去只有一种可能性，就是MySQL存在非常巨大的，频繁的文件读写操作，迫使操作系统不得不分配了2.7GB的Disk Cache，从而造成了物理内存的不足，被迫使用swap。而可能造成巨大文件读写操作的就是buffer pool的flush和log file的flush操作了。因此配置文件做如下修改：

C代码

1.memlock

2.innodb_buffer_pool_size = 2G

3.innodb_log_file_size = 64M

4.innodb_log_files_in_group = 2

5.innodb_flush_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_DIRECT跳过了操作系统的文件系统Disk Cache，让MySQL直接读写磁盘了。

但是在我的实践中来看，如果不使用O_DIRECT，操作系统被迫开辟大量的Disk Cache用于innodb的读写缓存，不但没有提高读写性能，反而造成读写性能急剧下降。而且buffer pool的数据缓存和操作系统Disk Cache 缓存造成了Double buffer的浪费，显然从我这个实践来看，浪费得非常厉害。

说O_DIRECT造成MySQL直接读写磁盘造成得性能下降问题，我觉得完全是杞人忧天。因为从JavaEye的数据库监测来看，Innodb的buffer pool命中率非常高，有98%以上，真正的磁盘操作是微乎其微的。为了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_method=O_DIRECT是必须的，而innodb_log_file_size也不宜太大。

来源：https://www.360docs.net/doc/5e2995311.html,/topic/461382

mysql错误:T able XXX is marked as crashed and should be repaired[转]

一日正在上班,朋友的QQ图标就激烈的闪亮起来,一看,原来是论坛出现问题了,具体报错如下：

Table '.\Tablename\posts' is marked as crashed and should be repaired

提示说论坛的帖子表posts被标记有问题，需要修复。我记得以前也出现过类似的问题,但是只要点击Phpmyadmin上的repair按纽就自动修复了,但是这次很绝,什么都没有.

于是赶快上网查找原因。最终将问题解决。解决方法如下：

找到mysql的安装目录的bin/myisamchk工具，在命令行中输入：

myisamchk -c -r ../data/tablename/posts.MYI

然后myisamchk 工具会帮助你恢复数据表的索引。好象也不用重新启动mysql，问题就解决了。

问题分析：

1、错误产生原因，有网友说是频繁查询和更新dede_archives表造成的索引错误，因为我的页面没有静态生成，而是动态页面，因此比较同意这种说法。

还有说法为是MYSQL数据库因为某种原因而受到了损坏，如：数据库服务器突发性的断电、在提在数据库表提供服务时对表的原文件进行某种操作都有可能导致

MYSQL数据库表被损坏而无法读取数据。总之就是因为某些不可测的问题造成表的损坏。

问题的编号为145

2、问题解决办法。

当你试图修复一个被破坏的表的问题时，有三种修复类型。如果你得到一个错误信息指出一个临时文件不能建立，删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的。

这三种修复方法如下所示：

% myisamchk --recover --quick /path/to/tblName

% myisamchk --recover /path/to/tblName

% myisamchk --safe-recover /path/to/tblName

第一种是最快的，用来修复最普通的问题；而最后一种是最慢的，用来修复一些其它方法所不能修复的问题。

检查和修复MySQL数据文件

如果上面的方法无法修复一个被损坏的表，在你放弃之前，你还可以试试下面这两个技巧：

1、如果你怀疑表的索引文件(*.MYI)发生了不可修复的错误，甚至是丢失了这个文件，你可以使用数据文件(*.MYD)和数据格式文件(*.frm)重新生成它。首先制作一个数据文件(tblName.MYD)的拷贝。重启你的MySQL 服务并连接到这个服务上，使用下面的命令删除表的内容：

mysql> DELETE FROM tblName;

在删除表的内容的同时，会建立一个新的索引文件[对MyISAM表，执行删除表的sql语句时通过重新建一个结构相同的新表来实现的，此时生成一个新的空数据文件和一个（与空数据关联的）索引文件]。退出登录并重新关闭服务，然后用你刚才保存的数据文件(tblName.MYD)覆盖新的(空)数据文件。

最后，使用myisamchk执行标准的修复(上面的第二种方法)，根据表的数据的内容和表的格式文件重新生成索引数据。

2、如果你的表的格式文件(tblName.frm)丢失了或者是发生了不可修复的错误，但是你清楚如何使用相应的CREA TE TABLE语句来重新生成这张表，你可以重新生成一个新的.frm文件并和你的数据文件和索引文件(如果索引文件有问题，使用上面的方法重建一个新的)一起使用。

首先制作一个数据和索引文件的拷贝，然后删除原来的文件(删除数据目录下有关这个表的所有记录)。

启动MySQL服务并使用当初的CREA TE TABLE文件建立一个新的表。新的.frm文件应该可以正常工作了。[然后将备份的数据和索引文件拷回来覆盖现在的文件。]但是最好你还是执行一下标准的修复(上面的第二种方法)。

myisamchk工具介绍（见mysql的官方手册）

可以使用myisamchk实用程序来获得有关数据库表的信息或检查、修复、优化他们。myisamchk适用MyISAM 表(对应.MYI和.MYD文件的表)。

调用myisamchk的方法：

shell> myisamchk [options] tbl_name[.MYI] ...

options指定你想让myisamchk做什么。在后面描述它们。还可以通过调用myisamchk --help得到选项列表。

tbl_name是你想要检查或修复的数据库表。如果你不在数据库目录的某处运行myisamchk，你必须指定数据库目录的路径，因为myisamchk不知道你的数据库位于哪儿。实际上，myisamchk不在乎你正在操作的文件是否位于一个数据库目录；你可以将对应于数据库表的文件拷贝到别处并且在那里执行恢复操作。

如果你愿意，可以用myisamchk命令行命名几个表。还可以通过命名索引文件(用“ .MYI”后缀)来指定一个表。它允许你通过使用模式“*.MYI”指定在一个目录所有的表。例如，如果你在数据库目录，可以这样在目录下检查所有的MyISAM表：

shell> myisamchk *.MYI

如果你不在数据库目录下，可通过指定到目录的路径检查所有在那里的表：

shell> myisamchk /path/to/database_dir/*.MYI

你甚至可以通过为MySQL数据目录的路径指定一个通配符来检查所有的数据库中的所有表：

shell> myisamchk /path/to/datadir/*/*.MYI

推荐的快速检查所有MyISAM表的方式是：

shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI

如果你想要检查所有MyISAM表并修复任何破坏的表，可以使用下面的命令：

shell> myisamchk --silent --force --fast --update-state \

-O key_buffer=64M -O sort_buffer=64M \

-O read_buffer=1M -O write_buffer=1M \

/path/to/datadir/*/*.MYI

该命令假定你有大于64MB的自由内存。关于用myisamchk分配内存的详细信息，参见5.9.5.5节，“myisamchk 内存使用”。

当你运行myisamchk时，必须确保其它程序不使用表。否则，当你运行myisamchk时，会显示下面的错误消息：

warning: clients are using or haven't closed the table properly

这说明你正尝试检查正被另一个还没有关闭文件或已经终止而没有正确地关闭文件的程序(例如mysqld服务器)更新的表。

如果mysqld正在运行，你必须通过FLUSH TABLES强制清空仍然在内存中的任何表修改。当你运行myisamchk时，必须确保其它程序不使用表。避免该问题的最容易的方法是使用CHECK TABLE(如check table user;)而不用myisamchk来检查表。

来源：https://www.360docs.net/doc/5e2995311.html,/blog/642541

frm,myd,myi是属于MySQL存储数据的文件，phpMyAdmin是无法导入的。phpMyAdmin支持的文件格式为sql文件。…..

参照：https://www.360docs.net/doc/5e2995311.html,/blog/422267

MySQL InnoDB 的性能问题讨论

MySQL最为人垢病的缺点就是缺乏事务的支持，MyISAM 性能虽然出众，不是没有代价的，InnoDB 又如何呢？InnoDB 的磁盘性能很令人担心，MySQL 缺乏良好的tablespace 真是天大的缺陷！

InnoDB的表空间分成三种，一种是裸设备，一种是若干个ibdata 文件（缺省方式），再一种是Per-Table 文件，第一种用得少，第二种显然比第三种效率更差，本文的讨论基于Per-Table，也即

innodb_file_per_table配置参数。

现象重现：导出一个几百万行数据、带若干索引、有过频繁更新的表出来再导入，如果能以真实环境下的表来做测试就更理想，到data 目录下观察对应的数据文件的size 增长情况，会发现前1G 速度相当令人满意，可是越往后效率越低，到后面基本就是蜗牛般的速度了。

不是只有导入才会让你慢得受不了，alter column/index 都会这样。。。

InnoDB 跟磁盘相关的文件存储，可以分成两个部分，一个是日志文件，另一个是数据文件。当有频繁的INSERT/UPDA TE 操作的时候，InnoDB 需要分别写入这两个文件，日志文件是顺序操作，数据文件包括了表数据和索引数据两个部分（和MyISAM 直接拆开成表文件和索引文件不同，InnoDB 的表和索引是在同一个文件当中的）。

InnoDB 的索引用的是BTREE 格式，如果当前更新的记录影响到索引的变化，逻辑上就存在三个操作，从原来的BTREE 找到并摘除原来这行的记录并做调整、插入行数据、根据新数据查找BTREE 相应的位置并重新插入新索引信息，假设索引数为N，相应的逻辑操作数就为 1 + 2*N，显然这些信息不能保证在同一个磁盘连续空间上，因此需要 1 + 2*N 次的磁头移动，行数越大、文件尺寸越大，磁头的移动幅度也就可能越大，带来的后果显然是极差的磁盘IO 效率。

MySQL 对于MyISAM 的的磁盘IO 优化是如何建议的呢？使用符号链接将表文件和索引文件分别指向不同的目录，分散到不同的磁盘上以增加系统的访问速度。这种优化方式，在InnoDB 上完全没有可能性！如果有tablespace 支持，磁盘效率问题就好解决了，一如商业数据库的做法，将日志、表文件、索引文件分别分布到不同的表空间也就是物理磁盘上，可是MySQL 一直到 5.1 都没有提供tablespace 功能，仅在NDB/NDBCLUSTER 中才提供，但是-- "CREA TE TABLESPACE was added in MySQL 5.1.6. In MySQL 5.1, it is useful only with Disk Data storage for MySQL Cluster."。

不知道Y ahoo 等大网站是怎么解决这个难题的。。。头痛。。。考虑切换到PostgreSQL 中。。。

来源：https://www.360docs.net/doc/5e2995311.html,/topic/34676

Mysql5.1简体中文官方手册-第15章存储引擎

被InnoDB存储引擎管理的两个重要的基于磁盘的资源是InnoDB表空间数据文件和它的日志文件。如果你指定无InnoDB配置选项，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

要建立InnoDB表空间文件，在https://www.360docs.net/doc/5e2995311.html,f选项文件里的[mysqld]节里使用innodb_data_file_path选项。在Windows上，你可以替代地使用my.ini文件。innodb_data_file_path的值应该为一个或多个数据文件规格的列表。如果你命名一个以上的数据文件，用分号(‘;’)分隔它们：

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

例如：把明确创建的具有相同特征的表空间作为默认设置的设置操作如下：

[mysqld]

innodb_data_file_path=ibdata1:10M:autoextend

这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。

尺寸大小用M或者G后缀来指定说明单位是MB或者GB。

一个表空间，它在数据目录里包含一个名为ibdata1的固定尺寸50MB的数据文件和一个名为ibdata2大小为50MB的自动扩展文件，其可以像这样被配置：

[mysqld]

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

一个指定数据文件的完全后缀包括文件名，它的尺寸和数个可选属性：

file_name:file_size[:autoextend[:max:max_file_size]]

autoextend属性和后面跟着的属性只可被用来对innodb_data_file_path行里最后一个数据文件。

如果你对最后的数据文件指定autoextend选项。如果数据文件耗尽了表空间中的自由空间，InnoDB就扩展数据文件。扩展的幅度是每次8MB。

如果磁盘已满，你可能想要把其它数据添加到另一个硬盘上。重新配置一个已存在表空间的指令见15.2.7节，“添加和删除InnoDB数据和日志文件”。

InnoDB并不感知最大文件尺寸，所以要小心文件系统，在那上面最大的文件尺寸是2GB。要为一个自动扩展数据文件指定最大尺寸，请使用max属性。下列配置允许ibdata1涨到极限的500MB：

[mysqld]

innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB默认地在MySQL数据目录创建表空间文件。要明确指定一个位置，请使用innodb_data_home_dir 选项。比如，要使用两个名为ibdata1和ibdata2的文件，但是要把他们创建到/ibdata，像如下一样配置InnoDB：

[mysqld]

innodb_data_home_dir = /ibdata

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

注释：InnoDB不创建目录，所以在启动服务器之前请确认/ibdata目录的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的mkdir命令来创建任何必需的目录。

通过把innodb_data_home_dir的值原原本本地部署到数据文件名，并在需要的地方添加斜杠或反斜杠，InnoDB为每个数据文件形成目录路径。如果innodb_data_home_dir选项根本没有在https://www.360docs.net/doc/5e2995311.html,f中提到，默认值是“dot”目录 ./，这意思是MySQL数据目录。

如果你指定innodb_data_home_dir为一个空字符串，你可以为列在innodb_data_file_path值里的数据文件指定绝对路径。下面的例子等价于前面那个例子：

[mysqld]

innodb_data_home_dir =