InnoDB的性能

4         InnoDB的性能

第一次认识InnoDB时，就是听说InnoDB能提供MySQL的支持事务和外键等支持，但是其性能大大不如MyISAM引擎。但是后来发现公司内部的游戏产品都在使用InnoDB数据库引擎，翻阅文档也发现InnoDB已经是MySQL自己从4.0开始内建的引擎。所以拿来比较一下。

4.1         MySQL数据库引擎

下面先介绍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.2         InnoDB的性能

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.1             AUTOCOMMIT自动提交

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.2             InnoDB和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的性能能得到更大的提高的同时，另一方面，我倒要为它的未来捏把汗。（《Oracle收购InnoDB对MySQL的影响》一文对此有一些讨论）