mysql InnoDB和MyISAM存储引擎的区别

MySQL作为当前最为流行的免费数据库服务引擎，已经风靡了很长一段时间，不过也许也有人对于MySQL的内部环境不很了解，尤其那些针对并发性处理的机制。今天，我们先了解一下MySQL中数据表的分类，以及它们的一些简单性质。
8t'WX%g/|5C3zTr r0截至目前，MySQL一共向用户提供了包括DBD、HEAP、ISAM、MERGE、MyIAS、InnoDB以及Gemeni这7种表。其中DBD、InnoDB属于事务安全类表，而其他属于事务非安全类表。51Testing软件测试网#Z9P h/xJl m7`

51Testing软件测试网Dbu^!Dl
DBD
8Yszj9a0Berkeley DB(DBD)表是支持事务处理的表，由Sleepycat软件公司开发。它提供MySQL用户期待已久的功能--事务控制。事务控制在任何数据库系统中都是一个极有价值的功能，因为它们确保一组命令能成功地执行或回滚。51Testing软件测试网0T2FqC+a#M B2?6y9H

51Testing软件测试网z$YM:|NrCC9Z%ZQ
HEAP
OP$p)f*y)]^0HEAP表是MySQL中存取数据最快的表。这是因为他们使用存储在动态内存中的一个散列索引，不过如果MySQL或服务器崩溃，这些内存数据将会丢失。

51Testing软件测试网g}!nN]
l a
ISAM
d0F |]?D;N0ISAM表是早期MySQL版本的缺省表类型，直到MyIASM开发出来。建议不要再使用它。51Testing软件测试网9m/Sl`!^wP1{f

.GO%z5AtZ0MERGE51Testing软件测试网Y;m$B+h c
MERGE是一个有趣的新类型，在3.23.25之后出现。一个MERGE表实际上是又一个MyISAM表的集合，合并而成的一个表，主要是为了效率的考虑，因为这样不仅仅可以提高速度、搜索效率、修复效率而且还节省了磁盘空间。

DP)Th3Vi0MyIASM51Testing软件测试网W NxiZ8~(e
MyIASM基于了IASM代码，应该可以说是IASM的衍生品，不过增加了不少有用的扩展。它是MySQL的默认数据 表类型，基于了传统的ISAM类型，ISAM是Indexed Sequential Access Method（有索引的顺序访问方法）的缩写，一般来说，它是存储记录和文件的标准方法。与其他存储引擎比较，MyISAM具有检查和修复表格的大多数工具。ISAM表格可以被压缩，而且它们支持全文搜索，不过它们是事务不安全的，而且也不支持外键。如果事务回滚将会造成不完全回滚，从而不具备原子性。所以假如忽略事务以及访问并发性的话，并且需要执行大量的SELECT检索语句的话，MyISAM将是最好的选择。
2zg&m|VZ]0MyIASM比较好的原因：
]o(Q]@:a!W#t0MyIASM表小于IASM表，所以使用较少资源。51Testing软件测试网 M)Vo%~y1{
MyIASM表在不同的平台上二进制层可移植。
P ~G"f\9zOA*CR0MyIASM拥有更大的键码尺寸，更大的键码上限。51Testing软件测试网#f$wc,VO-Bz
对于MyISAM存储引擎来说，它的读锁和写锁是互斥的，从而读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后 到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读 请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原 因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
}2NbL ek{ a0上面已经讨论了写优先调度机制带来的问题和解决办法。这 里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语 句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每 一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。51Testing软件测试网7FkqL8K/T$zU3a bz

51Testing软件测试网%H)e~4v.TV$A!X
InnoDB
Ckr}Yk:Qo:C!J i0InnoDB是MySQL 4.0之后推出的一种比较新的数据表类型，这种类型是事务安全的。它与BDB类型具有相同的特性，它们还支持外键。InnoDB表格速度很快具有比BDB还丰富的特性，因此如果需要一个事务安全的存储引擎，建议使用它。如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，同样应该使用InnoDB表。对于支持事务的InnoDB类型的表来说，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。

查看autocommit：select @@autocommit；51Testing软件测试网FH's:c"z1B

设置autocommit：set autocommit=0;

在MySQL5.1中，如何在启动时就禁止autocommit？

配置中加入init_connect= 'SET AUTOCOMMIT=0'51Testing软件测试网!h(PW+?K%\d)K,]-}

f1{}.a6J2r zL0Gemeni
Us%|,sFG,g9D0Gemeni表，据听说也是在MySQL 4.0之后推出的，不过截至当前，很少有针对它的介绍，同样应用也就更少了，我们暂时不作介绍。

MySQL的数据表类型很多，其中比较重要的是MyISAM，InnoDB这两种。
4Y/Zki$rU`_9d0InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
)B-x"J dWNM0
U:L!v`,`m|6d
y0MyIASM是IASM表的新版本，有如下扩展：51Testing软件测试网`V-eb1E.s
51Testing软件测试网H1Nl0?.yq M+{*L
·二进制层次的可移植性。51Testing软件测试网_4x-_pXy-jK(i/z

j7}z*_3J2A+hX0·NULL列索引。51Testing软件测试网 _RZ#@x%~.[
51Testing软件测试网/Q1~!l @|
·对变长行比ISAM表有更少的碎片。51Testing软件测试网[AQYp
51Testing软件测试网x3nND.]:g*`-~
·支持大文件。
cO&j\nw n1?ASM051Testing软件测试网e8w g/G(W#UL
·更好的索引压缩。51Testing软件测试网^&IQ7^d7N%b#_@ p
51Testing软件测试网yl q2G:RE9C
·更好的键吗统计分布。51Testing软件测试网7b(R4sZM

]S3M mf^0·更好和更快的auto_increment处理。51Testing软件测试网*Y$\^cjo

MyISAM表类型是一种比较成熟稳定的表类型，但是MyISAM对一些功能不支持。

51Testing软件测试网7yk^ f ~ }^x[
                       MyISAM                    InnoDB
X ym-p}U%|K0    事务             不支持                       支持51Testing软件测试网1A'm6g'O_M)E
    数据行锁定    不支持，只有表锁定     支持51Testing软件测试网-L)x3^4H/]{
    外键约束       不支持                       支持
k0Z2hr.dg0Q0    表空间大小     相对小                      相对大，最大是2倍51Testing软件测试网;Tn'zu%}oA$g
    全文索引        支持                         不支持51Testing软件测试网4t1t;|e*s{
a
    GIS数据        支持                         不支持51Testing软件测试网(h??dB6x8HN
    COUNT问题   无                            执行COUNT(*)查询时，速度慢