4InnoDB的性能
第一次认识InnoDB时，就是听说InnoDB能提供MySQL的支持事务和外键等支持，但是其性能大大不如MyISAM引擎。但是后来发现公司内部的游戏产品都在使用InnoDB数据库引擎，翻阅文档也发现InnoDB已经是MySQL自己从4.0开始内建的引擎。所以拿来比较一下。
4.1MySQL数据库引擎
下面先介绍MySQL现有的数据库引擎，其中的InnoDB引擎已经不是一个过家家的数据库引擎，其为MySQL提供了许多标准数据库的基本支持。
MySQL支持的引擎种类之多有点吓人，但多少给人一些有点滥的感觉。
表9 MySQL的DB引擎
|
MySQL数据库引擎
|
特点
|
说明
|
|
isam
|
ISAM为一种的专为磁盘存取文件设计的文件组织方式，好像还是IBM开发的。mysql 3.23版本前使用的古董
|
即将被淘汰。
|
|
myisam
|
ISAM文件格式的改进版本
采用1张表定义对应1个文件，表数据对应一个文件，索引对应一个文件的方式，
有长度限制，
很多数据操作都是表锁定
恢复能力较弱，但有修复工具
|
3.23后默认的引擎
|
|
merge
|
可以将多个同一库相同结构MyISAM表组织为一个逻辑单元，依靠它可以突破MyISAM文件的大小限制，有点像一个自己建立视图（加索引）
限制很多，查询性能低下，打开一个MERGE表相当于打开所有的相关表。
|
算不上一个引擎，只能说是一个表格式
4.0以后支持一个查询关键字UION，没有太大使用的必要
|
|
HEAP
|
放在内存中使用的数据表，要求字段长度固定。
速度是快，但完全没有后备存储，只能存零时数据
索引采用散列，只对=,<=>操作有提高效率作用
|
|
|
BDB
|
大名鼎鼎的Berkeley DB引擎，
有事务性(为页面加锁)
Berkeley DB好像只支持key->data的模式，不知道具体结合情况。
|
国内几乎没有人在MySQL上用这个引擎
|
|
InnoDB
|
支持事务，外键，
采用数据表空间对应数据文件的管理方式，数据表没有大小限制
采用日志方式记录操作，在崩溃后大部分情况可以自动恢复，但是如果恢复后仍有问题修复较为繁琐
对于包含检索和修改的查询命令支持行锁定
|
4.0后的版本内嵌支持
有点像1个简化的Oracle
count(*),. truncate等操作慢，
|
|
MaxDB
|
MaxDB是SAP 授权给MySQL 的基于 SAP DB的数据库引擎。
|
国内用的人也很少
也不太了解授权协议内容
|
|
ARCHIVE, ,
|
只支持插入和查询操作，
|
5.0后支持
|
|
FEDERATED
|
访问远程数据
|
5.0后支持
|
4.2InnoDB的性能
InnoDB引擎的性能可以用冰火两重天形容，网上对它的评价也可谓大相径庭。其实核心问题只有一个。你如何使用事务。其实坦白说所有的数据库，包括Oracle，DB2的频繁的提交事务都将大大影响数据库的性能。
重要的参数如下：
数据库为4.0.22的数据库,使InnoDB作为引擎，
数据文件每个2G，8个
日志文件3个，每个150M
Cache为打开状态
set-variable = key_buffer_size=384M
set-variable = query_cache_size=64M
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit= 1 [这个参数很重要]
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
4.2.1AUTOCOMMIT自动提交
MySQL对于事务的处理有两种方式：
自动提交方式，SET AUTOCOMMIT=1就是每条SQL MySQL都自动帮你提交。这样就相当于每条SQL都是一个事务。
手动提交方式，SET AUTOCOMMIT=0，必须自己写事物的开始（START TRANSACTION）,结束（COMMIT）语句或者回滚（ROLLBACK）语句。
自动提交的方式将使系统的事务成千倍的增加，所以性能自然会下降很多。而有趣的是MySQL的默认事务使用方式居然是自动提交方式，而且好像没有方法进行配置修改，SET AUTOCOMMIT是针对对每个SESSION处理。（后面会提到这个问题）
对于InnoDB的测试我们分为自动提交和手动提交两种，手动提交在连接数据库后，发送SET AUTOCOMMIT=0设置，然后通知开始交易START TRANSACTION，然后再进行后面的操作，在所有的操作完成后，在进行提交COMMIT，（注意只有一次交易事务）。 而自动提交和前面的方式一样。
对比测试数据为100000条。(不是我偷懒不测试大数据量，自动提交太慢)
测试结果数据：
表10 AUTOCOMMIT测试100000条记录
|
参数配置
|
插入100000条记录耗时
|
平均
|
查询100000条记录耗时
|
平均
|
修改100000条记录耗时
|
平均
|
删除100000条记录耗时
|
平均
|
|
SET AUTOCOMMIT=1
|
2500s
|
40/s
|
27s
|
3704/s
|
2503s
|
40
|
2506s
|
40/s
|
|
SET AUTOCOMMIT=0
|
17s
|
5882/s
|
21s
|
4761
|
16s
|
6250
|
16s
|
6250
|
结果数据差别就是这么大，自动提交的性能真是惨不忍睹，所有的修改操作和非自动提交都有200多倍的性能差距。
从上面的结果我们看出，自动提交是一种不可接受的方案，但是如果使用非自动提交有两个限制，现有代码要更改，对于CGI这样的应用，每次就是一个语句进行修改，这样仍然是1个会话1个事务，对于大规模的这样使用效率仍然会造成低下。
正当我对此无比疑惑的时候，zengyu老大告诉我他们修改一个默认参数innodb_flush_log_at_trx_commit提高性能，查询了这个参数的解释如下：
表11 innodb_flush_log_at_trx_commit的取值说明
|
innodb_flush_log_at_trx_commit取值
|
说明
|
|
0
|
每秒写1次日志，将数据刷入磁盘，相当于每秒提交一次事务。
|
|
1
|
每次提交事务写日志，同时将刷新相应磁盘，默认参数。
|
|
2
|
每提交事务写一次日志，但每隔一秒刷新一次相应的磁盘文件[注]
|
如果你对安全没有苛刻要求，可以忍受极少量错误，而且你的业务是大量小规模的交易，你可以使用innodb_flush_log_at_trx_commit =0来加快处理效率。
上面取值说明解释完全抄于《MySQL权威指南》，其实我对这段话有点疑惑，到底最后刷新的是日志文件，还是日志文件和数据文件都刷新？从MySQL的参考手册的英文看是日志文件(commit the log is flushed to disk, and the modifications made by the transaction become permanent)。InnoDB引擎应该和大部分商用数据库相同，先改写日志，再改写数据文件，所以可以保证在故障后快速恢复。
既然知道还有窍门，我们将innodb_flush_log_at_trx_commit=0后，再进行一组测试。
表12 innodb_flush_log_at_trx_commit=0下的AUTOCOMMIT测试100000条记录
|
参数配置
|
插入100000条记录耗时
|
平均
|
查询100000条记录耗时
|
平均
|
修改100000条记录耗时
|
平均
|
删除100000条记录耗时
|
平均
|
|
innodb_flush_log_at_trx_commit=0
SET AUTOCOMMIT=1
|
18s
|
5556/s
|
27s
|
3704/s
|
17s
|
5882/s
|
17s
|
5882/s
|
|
innodb_flush_log_at_trx_commit=0
SET AUTOCOMMIT=0
|
17s
|
6667/s
|
20s
|
5000/s
|
15s
|
5882/s
|
14s
|
7142/s
|
我们可以看出这样，操作的效率可以大大提高，改写操作的速度都提高了很多。可以看作对于innodb_flush_log_at_trx_commit=0的设置下，InnoDB引擎自己控制提交的时机。
看来如果使用InnoDB，而且你的应用又是大数量级小事务操作（我们公司的业务基本上都是），还是使用innodb_flush_log_at_trx_commit=0比较好，对于一次有大规模的操作最好还是自己控制事务[注]。
即使使用innodb_flush_log_at_trx_commit=0，你只要在会话中使用SET AUTOCOMMIT=0标示不使用提交，你仍然可以使用START TRANSACTION 和COMMIT保证事务性。（事务性对于数据库和网络分布设计中的重要性是无需多言）
4.2.2InnoDB和MyISAM的性能比较
本是同根生，相煎何太急。J
仍然采用大规模的数据的方式进行测试。测试环境为4.0,数据仍然保留Cache和二进制日志。
表13 MyISAM和InnoDB的性能比较
|
比较项目
|
MySQL4.0 MyISAM引擎
|
InnoDB引擎
innodb_flush_log_at_trx_commit=1
使用AUTOCOMMIT=0
|
InnoDB引擎
innodb_flush_log_at_trx_commit=0
(默认使用AUTOCOMMIT=1)
|
|||
|
耗时(s)
|
处理速度（条/s）
|
耗时(s)
|
处理速度（条/s）
|
耗时(s)
|
处理速度（条/s）
|
|
|
插入1000000条记录
|
169
|
5917.16
|
162
|
6172.84
|
176
|
5681.82
|
|
查询1000000条记录
|
282
|
3546.10
|
195
|
5128.21
|
277
|
3610.11
|
|
改写1000000条记录
|
165
|
6060.61
|
156
|
6410.26
|
182
|
5494.51
|
|
删除1000000条记录
|
172
|
5813.95
|
138
|
7246.38
|
164
|
6097.56
|
|
插入5000000条记录
|
876
|
5707.76
|
869
|
5753.74
|
937
|
5336.18
|
|
查询5000000条记录
|
1472
|
3396.74
|
987
|
5065.86
|
1413
|
3538.57
|
|
改写5000000条记录
|
816
|
6127.45
|
778
|
6426.74
|
937
|
5336.18
|
|
删除5000000条记录
|
958
|
5219.21
|
710
|
7042.25
|
863
|
5793.74
|
|
插入10000000条记录
|
1758
|
5688.28
|
1805
|
5540.17
|
2107
|
4746.08
|
|
查询10000000条记录
|
3001
|
3332.22
|
1994
|
5015.05
|
2879
|
3473.43
|
|
改写10000000条记录
|
1844
|
5422.99
|
1588
|
6297.23
|
1858
|
5382.13
|
|
删除10000000条记录
|
1908
|
5241.09
|
1453
|
6882.31
|
1732
|
5773.67
|
从测试数据可以看出，MyISAM引擎和InnoDB在性能上基本没有太大的区别，半斤八两。AUTOCOMMIT=0的方式有较好的性能，但其对WEB的应用不是太实用（事务一多性能还是要急剧下降），我们重点比较InnoDB在innodb_flush_log_at_trx_commit=0下和MyISAM引擎的性能。
由于MyISAM的特性而且测试用例是顺序插入, MyISAM引擎占了些便宜。在MyISAM数据文件没有空洞时(删除记录会产生一个没有使用的记录区)，插入记录操作没有进行锁表操作（应该说是写锁），（因为所有的记录都是插入文件末尾）。由于我的测试正好属于这种情况，在测试中MyISAM引擎在INSERT语句上表现优秀，但是对于实际的环境，这个优势估计不会存在。
修改部分MyISAM引擎较好这不能反映真实情况，InnoDB的纪录锁的优势无法体现。[注]
InnoDB采用记录锁同步操作，MyISAM采用的是表锁。
MyIsam的的客户在访问数据前，必须得到相应的锁，而且同时写操作的优先级高于读取操作，可以这样理解，MyIsam引擎对于表的查询操作有两个锁队列，一个是读取锁队列，一个是写入锁队列，MyIsam引擎总是优先处理写入锁队列等待的请求。而一个写入锁将阻塞后面写入和读取操作，而读取锁操作只阻塞所有的写操作。
而InnoDB的引擎对于读取操作几乎不加锁，如果此记录正在被写，才会阻塞此记录相关的读取操作和写入操作。
一般认为MyISAM引擎利于检索，因为查询操作使用读取锁可以并发，InnoDB的引擎在大量更改的更改操作环境有更好的表现。
比较图表如下：
图5 InnoDB和MyISAM的性能比较必须要指明的是，这个测试其实是比较利于MyISAM 引擎的，由于MyISAM的引擎的设计是1个表1个文件的，所以在真实环境，数据文件内部必然有记录碎片，（定期使用交互命令可以优化这个问题），而这些碎片会MyISAM的降低处理性能，而且MyISAM采用文件锁处理模式，不利于真实环境下的并发操作，后面的一些模拟测试也证明了这个问题。
4.3总结
InnoDB和MyIASM引擎谁优谁劣其实是一个很难衡量的问题，我把我知道的两者的情况都拿出来让你自己对比一下。
表14 MyISAM引擎和InnoDB引擎的对比
|
MyISAM
|
InnoDB
|
|
|
性能
|
也不错，
|
在AUTO COMMIT=0的情况下好于MyISAM，
|
|
支持事务
|
不支持
|
支持
|
|
支持外键
|
不支持
|
支持
|
|
可移植
|
理论可以
|
理论可以
|
|
表，索引大小是否有限制
|
有
|
无
|
|
锁类型
|
数据表的读写锁
|
记录锁定
|
|
是否存在死锁的可能
|
几乎无
|
存在
|
|
FULLTEXT索引
|
支持
|
不支持（路标版本有规划）
|
|
备份恢复
|
支持mysqldump备份为SQL语句
也支持mysqlhotcopy完全备份
|
支持mysqldump备份
完全热备份要依靠innodb的付费备份工具
|
|
维护
|
简单
|
较MyISAM复杂
|
|
故障率
|
在大数访问量下容易出现故障，但是提供了修复工具[注]
|
故障率低，一般的故障可以自动恢复
但如果恢复后仍然有故障修复比较麻烦，
|
|
文件碎片
|
在删除后存在碎片，但是可以通过命令进行
|
文件内部分片，也有碎片，但是影响较小。
|
|
已知缺陷
|
在很多数据的基本特性上没有提供支持
大压力环境下故障率较高
|
Select count(*) 慢
Truncate慢
不支持AUTO_INCREA初始化数值
innodb 可能存在一个限制， innodb引擎中使用的内存总和不能超过2G,否则会宕机。根据《MySQL Reference Manual》中的说明是由于glic引发的问题。对于这个问题我比较疑惑，实际测试的结果没有出现类似问题，而且感觉按照文档的描述问题原因,，mysqlisam也应该有类似的缺陷，但是从未见过说明？
|
个人感觉在大访问量的情况下，InnoDB引擎还是一个更好的选择。
MyISAM引擎的故障主要就是索引错误（数据文件和索引不一致），其实出现这个错误，主要是由于操作的不当造成的：
(1)在联机状态kill的mysqld。
(2)不锁表，在mysqld运行的时候拷贝，读写,备份数据文件，(很多备份数据文件采用直接的拷贝方式，这样不仅得不到可用的备份文件，还会破坏现有的数据文件)
(3)不锁表，在mysqld运行的时候，使用外部程序影响数据文件，比如用myisamchk在联机状态下修复。（mysqlcheck可以用于联机修复，一直错误认为mysqlcheck 也有问题，谢谢 owenzhuang指明。）
在联机状态下对数据表的外部操作必须按照LOCK TABLE，FLUSH TABLE，操作，UNLOCK TABLE的方式进行。
对于损坏的表，MySQL 提供了几种修复方法，一种是脱机使用myisamchk 进行修复，一种是联机使用交互命令CHECK TABLE 和 REPAIR TABLE 进行修复。另外，MyISAM引擎也有自我恢复功能。
InnoDB现在已经被Oracle收购，作为了Oracle的一个开源项目，一方面我期待有新的强大技术背景的公司大的支持下，InnoDB的性能能得到更大的提高的同时，另一方面，我倒要为它的未来捏把汗
此文章由 http://www.ositren.com 收集整理 ,地址为: http://www.ositren.com/htmls/67673.html