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;