MySQL与事务 MyISAM 和InnoDB [转]

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类 型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而 InnoDB提供事务支持已经外部键等高级数据库功能。

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

◆1.InnoDB不支持FULLTEXT类型的索引。

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

◆3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

◆4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

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

 

两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁.而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。

 

 

 

MySQL5.X都已经发布好久了，但是还有很多人认为MySQL是不支持事务处理的，这不得不怪他们是孤陋寡闻的，其实，只要你的MySQL版本 支持BDB或InnoDB表类型，那么你的MySQL就具有事务处理的能力。这里面，又以InnoDB表类型用的最多，虽然后来发生了诸如Oracle收 购InnoDB等令MySQL不爽的事情，但那些商业上的斗争与技术无关，下面以InnoDB表类型为例简单说一下MySQL中的事务。

先来明确一下事务涉及的相关知识：

事务都应该具备ACID特征。所谓ACID是Atomic（原子性），Consistent（一致性），Isolated（隔离性），Durable（持续性）四个词的首字母所写，下面以“银行转帐”为例来分别说明一下它们的含义：

原子性：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。换句话说，事务是不可分割的最小单元。比如：银行转帐过程中，必须同时从一个帐户减去转帐金额，并加到另一个帐户中，只改变一个帐户是不合理的。

一致性：在事务处理执行前后，数据库是一致的。也就是说，事务应该正确的转换系统状态。比如：银行转帐过程中，要么转帐金额从一个帐户转入另一个帐户，要么两个帐户都不变，没有其他的情况。

隔离性：一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说，银行转帐过程中，在转帐事务没有提交之前，另一个转帐事务只能处于等待状态。

持续性：事务处理的效果能够被永久保存下来。反过来说，事务应当能够承受所有的失败，包括服务器、进程、通信以及媒体失败等等。比如：银行转帐过程中，转帐后帐户的状态要能被保存下来。

再来看看哪些问题会用到事务处理：

 

这里不说“银行转帐”的例子了，说一个大家实际更容易遇到的“网上购书”的例子。先假设一下问题的背景：网上购书，某书（数据库编号为123）只剩最后一本，而这个时候，两个用户对这本书几乎同时发出了购买请求，让我们看看整个过程：

在具体分析之前，先来看看数据表的定义：

-------------------------------------------------------------------------------

create table book
(
book_id unsigned int(10) not null auto_increment,
book_name varchar(100) not null,
book_price float(5, 2) not null, #我假设每本书的价格不会超过999.99元
book_number int(10) not null,
primary key (book_id)
)
type = innodb; #engine = innodb也行

-------------------------------------------------------------------------------

对于用户甲来说，他的动作稍微比乙快一点点，其购买过程所触发的动作大致是这样的：

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE  book_id = 123;

book_number大于零，确认购买行为并更新book_number

2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

购书成功

-------------------------------------------------------------------------------

而对于用户乙来说，他的动作稍微比甲慢一点点，其购买过程所触发的动作和甲相同：

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE  book_id = 123;

这个时候，甲刚刚进行完第一步的操作，还没来得及做第二步操作，所以book_number一定大于零

2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

购书成功

-------------------------------------------------------------------------------

表面上看甲乙的操作都成功了，他们都买到了书，但是库存只有一本，他们怎么可能都成功呢？再看看数据表里book_number的内容，已经变成 “-1”了，这当然是不能允许的（实际上，声明这样的列类型应该加上unsigned的属性，以保证其不能为负，这里是为了说明问题所以没有这样设置）

好了，问题陈述清楚了，再来看看怎么利用事务来解决这个问题，打开MySQL手册，可以看到想用事务来保护你的SQL正确执行其实很简单，基本就是三个语句：开始，提交，回滚。

-------------------------------------------------------------------------------

开始：START TRANSACTION或BEGIN语句可以开始一项新的事务

提交：COMMIT可以提交当前事务，是变更成为永久变更

回滚：ROLLBACK可以回滚当前事务，取消其变更

此外，SET AUTOCOMMIT = {0 | 1}可以禁用或启用默认的autocommit模式，用于当前连接。

-------------------------------------------------------------------------------

那是不是只要用事务语句包一下我们的SQL语句就能保证正确了呢？比如下面代码：

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

答案是否定了，这样依然不能避免问题的发生，如果想避免这样的情况，实际应该如下：

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123 FOR UPDATE;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

由于加入了FOR UPDATE，所以会在此条记录上加上一个行锁，如果此事务没有完全结束，那么其他的事务在使用SELECT ... FOR UPDATE请求的时候就会处于等待状态，直到上一个事务结束，它才能继续，从而避免了问题的发生，需要注意的是，如果你其他的事务使用的是不带FOR UPDATE的SELECT语句，将得不到这种保护。

最后看看PHP + MySQL事务操作的代码演示：

实际LAMP应用中，一般PHP使用AdoDB操作MySQL，下面给出AdoDB相应的代码方便大家查阅：

-------------------------------------------------------------------------------

<?php
// ...

$adodb->startTrans();

//实际，getOne所调用的查询也可以直接放到rowLock来进行，这里只是为了演示效果能更明显些。

$adodb->rowLock('book', 'book_id = 123');

$bookNumber = $adodb->getOne("SELECT book_number FROM book WHERE  book_id = 123");

$adodb->execute("UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123");

$adodb->completeTrans();

// ...
?>

-------------------------------------------------------------------------------

其中，rowLock的方法就是调用的FOR UPDATE来实现的行锁，你可能会想把“FOR UPDATE”直接写到$adodb->getOne()调用的那条SQL语句里面去实现行锁的功能，不错，那样确实可以，但是并不是所有的数据库 都使用“FOR UPDATE”语法来实现行锁功能，比如Sybase使用“HOLDLOCK”的语法来实现行锁功能，所以为了你的数据库抽象层保持可移植性，我还是劝你 用rowLock来实现行锁功能，至于可移植性就交给AdoDB好了，嗯，有点扯远了，今儿就说到这里了。

-------------------------------------------------------------------------------

 

 

MyISAM：不支持事务，用于只读程序提高性能
InnoDB：支持ACID事务、行级锁、并发
Berkeley DB：支持事务