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打开也可以），将大大提高性能。
二、事务的概念：
事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元。事务通常由高级数据库操纵语言或编程语言书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句来界定。事务由事务开始和事务结束之间执行的全体操作组成。
三、事务的四大特性：
(1) 原子性
事务的原子性指的是，事务中包含的程序作为数据库的逻辑工作单位，它所做的对数据修改操作要么全部执行，要么完全不执行。这种特性称为原子性。
(2) 一致性
事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。一致性和事务的原子性是密不可分的。
(3) 分离性
分离性指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来，不被其它企图进行修改的事务看到。
(4)持久性
持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。即一旦一个事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，耐得住任何系统故障。持久性通过数据库备份和恢复来保证。
四、为什么要使用事务：
我们用一个简单的例子来说明这个问题，比如我到银行去存钱，于是有这么几个步骤：
1、把钱交给工作人员；2、工作人员填单；3、将单子给我签字；4、工作人员确认并输入电脑。
在这个事务进行的过程中，要是我把钱交给工作人员之后，进行到步骤3我签玩字，银行工作人员突发心脏病挂掉了，那，我的钱还没有被输入电脑，但我却交了钱又签字确认了，这时候我岂不是要亏死了？我肯定会要求银行回退这次事务，把钱还给我，要么由银行其他人员帮我输入电脑，完成本次事务。
当然，这个是在有人工操作的时候进行的，我们可以要求银行做到交易的完整性。但是如果我们用自动存款机存钱的时候，机器在处理到我确认存款但还没有将记录真正提交到数据库的时候，突然因为断电或其他未知故障而突然停止，这时我该怎么办？
于是，在数据库里产生了这么一个术语：事务(Transaction)，也就是要么成功，要么失败，并恢复原状。
五、在MySQL中体验事务的过程：
1. 4.0以上mysqld都支持事务，包括非max版本。3.23的需要max版本mysqld才能支持事务。可以用show engines 来查看mysql支持的引擎：
mysql> show engines;
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
| ndbcluster | DISABLED | Clustered, fault-tolerant tables | YES | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine | YES | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
2. 创建表时如果不指定type则默认为myisam，不支持事务。
存储引擎是针对表对象来说的，可以用 show create table tablename 命令看表的类型。
mysql> show create table aa;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| aa | CREATE TABLE `aa` (
`a` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
3、 对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交，但在支持事务的表里则必须 commit后才能生效：
use test;
drop table if exists aa;
create table aa (a varchar(10)) type=myisam;
drop table if exists bb;
create table bb (a varchar(10)) type=innodb;
insert into aa values('a');
insert into bb values('a');
select * from aa;
select * from bb;
验证：虽然还没有进行commit操作，但是这时两个表都能看到一条记录，根据事务的定义必须使用类似于 BEGAN END 的语句块来执行一个事务,如果程序没有显式调用BEGIN 开始事务，在Innodb中每插入一条记录都会自动Commit。
use test;
begin;
insert into aa values('aaaa');
insert into bb values('aaaa');
select * from aa;
select * from bb;
验证：在begin的语句块中执行事务操作，虽然还没有进行commit操作，但是aa表已经能看到增加了一条新的记录，但bb表还没有.
commit；
验证：在begin执行的当前窗口后执行commit命令后，再次查看bb表，新的记录也添加进去了。
注意：commit必须和begin对应起来，中间封装一个事务，如果关掉执行了begin但尚未执行commit的窗口，在其他窗口执行commit操作，这时数据库认为这个事务在执行的某个过程中失败，所以整个事务都会失败！
六、Mysql表引擎的切换：
1. 可以执行以下命令来切换非事务表到事务（数据不会丢失），innodb表比myisam表更安全：
alter table tablename type=innodb;
2. innodb表不能用repair table命令和myisamchk -r table_name
但可以用check table，以及mysqlcheck [OPTIONS] database [tables]
3. 启动mysql数据库的命令行中添加了以下参数可以使新发布的mysql数据表都默认为使用事务（只影响到create语句。）
--default-table-type=InnoDB
4. 临时改变默认表类型可以用：
set table_type=InnoDB;
show variables like 'table_type';
show engines;
------------------------------------------------------------------------------------------------------------------------------------------------
Mysql的transaction实现 收藏
transaction在数据库编程中是一个重要的概念，这样做可以控制对数据库操作的事务提交。
但是要想在程序中实现事务，要求数据库本身支持事务。
现在的关系型数据库，我们日常使用的mysql，oracle等等都支持事务，有的是安装后直接就支持，有的需要做一些设置。
这篇文章是针对mysql的，讲述从数据库安装，设置，一直到sql语句，甚至到java程序中，如何实现transaction。
1.安装
要想在mysql的表中支持transaction，必须要求是innodb表。普通表使用的autocommit模式，会自动提交每一条sql语句，不能算是transaction吧。
安装时要指定mysql支持innodb，./configure --with-innodb。
2.配置
安装后，可以对innodb做一些配置，在my.cnf或my.ini中的[mysqld]段。
#存储目录，如果不指定默认为安装的data目录，为空时以innodb_data_file_path指定路径为准
innodb_data_home_dir =
#数据文件名及大小，默认为ibdata1，10m大小。autoextend可以自增，max:2000M文件最大2g，因为有的硬盘有2g文件大小限制。
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M:autoextend:max:2000M
# 设置缓冲池大小
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#设置日志文件路径，默认在date目录下，名称为ib_logfile...
innodb_log_group_home_dir =
#设置日志文件数目，默认为3
set-variable = innodb_log_files_in_group=3
# 设置日志文件大小
set-variable = innodb_log_file_size=10M
# 设置日志缓冲大小
set-variable = innodb_log_buffer_size=8M
# 任何事务提交前写入日志，方便故障诊断，请设为1。如果丢失最近的几个事务影响不大的话，设置为0（默认值）。
innodb_flush_log_at_trx_commit=1
#设置超时时间
set-variable = innodb_lock_wait_timeout=50
注意：innodb不会自动生成目录，上面所有指定目录要手工生成。默认不用。
完整的配置参数如下表（下表引自http://man.chinaunix.net/database/mysql/inonodb_zh/2.htm#InnoDB_start
）：
innodb_data_home_dir
这是InnoDB表的目录共用设置。如果没有在 my.cnf 进行设置，InnoDB 将使用MySQL的 datadir 目录为缺省目录。如果设定一个空字串,可以在 innodb_data_file_path 中设定绝对路径。
innodb_data_file_path
单独指定数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里所设定值的组合。 文件大小以 MB 单位指定。因此在文件大小指定后必有“M”。 InnoDB 也支持缩写“G”， 1G = 1024M。从 3.23.44 开始，在那些支持大文件的操作系统上可以设置数据文件大小大于 4 GB。而在另一些操作系统上数据文件必须小于 2 GB。数据文件大小总和至少要达到 10 MB。在 MySQL-3.23 中这个参数必须在 my.cnf 中明确指定。在 MySQL-4.0.2 以及更新版本中则不需如此，系统会默认在 MySQL 的 datadir 目录下创建一个 16 MB 自扩充(auto-extending)的数据文件 ibdata1。你同样可以使用一个 原生磁盘分区(RAW raw disk partitions(raw devices)) 作为数据文件， 如何在 my.cnf 中详细指定它们请查看第 12.1 节。
innodb_mirrored_log_groups
为了保护数据而设置的日志文件组的拷贝数目，默认设置为 1。在 my.cnf 中以数字格式设置。
innodb_log_group_home_dir
InnoDB 日志文件的路径。必须与 innodb_log_arch_dir 设置相同值。 如果没有明确指定将默认在 MySQL 的 datadir 目录下建立两个 5 MB 大小的 ib_logfile... 文件。
innodb_log_files_in_group
日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用。在 my.cnf 中以数字格式设置。
innodb_log_file_size
日志组中的每个日志文件的大小(单位 MB)。如果 n 是日志组中日志文件的数目，那么理想的数值为 1M 至下面设置的缓冲池(buffer pool)大小的 1/n。较大的值，可以减少刷新缓冲池的次数，从而减少磁盘 I/O。但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。 日志文件总和必须小于 2 GB，3.23.55 和 4.0.9 以上为小于 4 GB。在 my.cnf 中以数字格式设置。
innodb_log_buffer_size
InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf 中以数字格式设置。
innodb_flush_log_at_trx_commit
通常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力。如果你愿意减弱这个安全，或你运行的是比较小的事务处理，可以将它设置为 0 ，以减少写日志文件的磁盘 I/O。这个选项默认设置为 0。
innodb_log_arch_dir
The directory where fully written log files would be archived if we used log archiving. 这里设置的参数必须与 innodb_log_group_home_dir 相同。 从 4.0.6 开始，可以忽略这个参数。
innodb_log_archive
这个值通常设为 0。 既然从备份中恢复(recovery)适合于 MySQL 使用它自己的 log files，因而通常不再需要 archive InnoDB log files。这个选项默认设置为 0。
innodb_buffer_pool_size
InnoDB 用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘 I/O。在一个专用的数据库服务器上可以将它设置为物理内存的 80 %。 不要将它设置太大，因为物理内存的使用竞争可能会影响操作系统的页面调用。在 my.cnf 中以数字格式设置。
innodb_additional_mem_pool_size
InnoDB 用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小。理想的值为 2M，如果有更多的表你就需要在这里重新分配。如果 InnoDB 用尽这个池中的所有内存，它将从操作系统中分配内存，并将错误信息写入 MySQL 的错误日志中。在 my.cnf 中以数字格式设置。
innodb_file_io_threads
InnoDB 中的文件 I/O 线程。 通常设置为 4，但是在 Windows 下可以设定一个更大的值以提高磁盘 I/O。在 my.cnf 中以数字格式设置。
innodb_lock_wait_timeout
在回滚(rooled back)之前，InnoDB 事务将等待超时的时间(单位 秒)。InnoDB 会自动检查自身在锁定表与事务回滚时的事务死锁。如果使用 LOCK TABLES 命令，或在同一个事务中使用其它事务安全型表处理器(transaction safe table handlers than InnoDB)，那么可能会发生一个 InnoDB 无法注意到的死锁。在这种情况下超时将用来解决这个问题。这个参数的默认值为 50 秒。在 my.cnf 中以数字格式设置。
innodb_flush_method
这个参数仅仅与 Unix 相关。这个参数默认值为 fdatasync。 另一个设置项为 O_DSYNC。这仅仅影响日志文件的转储，在 Unix 下以 fsync 转储数据。InnoDB 版本从 3.23.40b 开始，在 Unix 下指定 fdatasync 为使用 fsync 方式、指定 O_DSYNC 为使用 O_SYNC 方式。由于这在某些 Unix 环境下还有些问题所以在 'data' versions 并没有被使用。
innodb_force_recovery
警告：此参数只能在你希望从一个被损坏的数据库中转储(dump)数据的紧急情况下使用！ 可能设置的值范围为 1 - 6。查看下面的章节 'Forcing recovery' 以了解这个参数的具体含义。参数设置大于 0 的值代表着 InnoDB 防止用户修改数据的安全度。从 3.23.44 开始，这个参数可用。在 my.cnf 中以数字格式设置。
innodb_fast_shutdown
InnoDB 缺少在关闭之前清空插入缓冲。这个操作可能需要几分钟，在极端的情况下可以需要几个小时。如果这个参数据设置为 1 ，InnoDB 将跳过这个过程而直接关闭。从 3.23.44 和 4.0.1 开始，此参数可用。从 3.23.50 开始，此参数的默认值为 1。
innodb_thread_concurrency
InnoDB 会试图将 InnoDB 服务的使用的操作系统进程小于或等于这里所设定的数值。此参数默认值为 8。如果计算机系统性能较低或 innodb_monitor 显示有很多线程等侍信号，应该将这个值设小一点。如果你的计算机系统有很我的处理器与磁盘系统，则可以将这个值设高一点以充分利用你的系统资源。建议设值为处理器数目+ 磁盘数目。 从 3.23.44 和 4.0.1 开始，此参数可用。在 my.cnf 中以数字格式设置。
innodb还需要使用二进制日志文件：
log-bin指定二进制文件名称，不指定默认生成。
log-bin-index 可以指定索引文件。
使用 binlog-do-db可以指定记录的数据库。
使用 binlog-ignore-db可以指定不记录的数据库。
注意的是： binlog-do-db 和binlog-ignore-db 一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写， 在指定数据库时必须全部使用小写名字，否则不会起作用。
3.添加表
CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY,fname VARCHAR(15),sname VARCHAR(20),sex VARCHAR(6),age VARCHAR(3)) TYPE=INNODB;
记得后面的TYPE=INNODB。
4.sql语句的transaction实现
两种方式：
如果SET AUTOCOMMIT=0;也就是关闭了自动提交，那么任何commit或rallback语句都可以触发事务提交。
比如：
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO user(fname,sname) VALUES ('Max','Ma');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user(fname,sname) VALUES ('Sky','Sun');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
这样事务就算提交了。
如果SET AUTOCOMMIT=1;也就是开启了自动提交（默认值），那么必须要以begin或者START TRANSACTION声明事务的开始，然后再以commit或rallback语句都可以触发事务提交。
比如：
mysql> SET AUTOCOMMIT=1;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO user(fname,sname) VALUES ('Max','Ma');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user(fname,sname) VALUES ('Sky','Sun');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
像其他关系型数据库一样，也可以使用存储过程（procedure）来封装事务。
5.java程序开发中的实现。
涉及到程序开发实现方法就多了。
一.自己写方法把mysql的底层transaction命令封装。我感觉程序开发中应该尽量避免和底层数据库的过多交互，我没有实现它。
有人实现了，下面是他实现的一个例子网址：http://dlog.cn/html/diary/showlog.vm?sid=7&log_id=2516
二.java的jdbc开发包包含了操作transaction的方法，在java.sql.connection接口里。
使用他的好处是可以和多种类型数据库交互。
三.hibernate等ORM框架工具。
hibernate中也封状了对transaction的操作，在org.hibernate.Session类中，使用beginTransaction()方法开启transaction；使用getTransaction().commit()提交transaction；使用getTransaction().rollback()方法回滚transaciton。
四.这是我常用的一种方法，把hibernate的session的方法封装或者实现jdbc的connection的接口。