MySQL两种表存储结构性能比较测试过程

　　MySQL支持的两种主要表存储格式MyISAM，InnoDB，上个月做个项目时，先使用了InnoDB，结果速度特别慢，1秒钟只能插入10几条。后来换成MyISAM格式，一秒钟插入上万条。当时觉的这两个表的性能也差别太大了吧。后来自己推测，不应该差别这么慢，估计是写的插入语句有问题，决定做个测试：测试环境：Redhat Linux9，4CPU，内存2G，MySQL版本为4.1.6-gamma-standard测试程序：Python＋Python-MySQL模块。测试方案：1、MyISAM格式分别测试，事务和不用事务两种情况：2、InnoDB格式分别测试AutoCommit＝1（不用begin transaction和用begin transaction模式），AutoCommit=0 （不用begin transaction和用begin transaction模式）四种情况。测试方法为插入10000条记录。为了测试不互相影响，单独建立了专用的测试表，建表语句如下：1、MyISAM不用事务表：CREATE TABLE `MyISAM_NT` (`TableId` int(11) NOT NULL default '0',`TableString` varchar(21) NOT NULL default '') ENGINE=MyISAM;2、MyISAM用事务表:CREATE TABLE `MyISAM_TS` (`TableId` int(11) NOT NULL default '0',`TableString` varchar(21) NOT NULL default '') ENGINE=MyISAM;3、InnoDB关闭AutoCommit，不用事务:CREATE TABLE `INNODB_NA_NB` (`TableId` int(11) NOT NULL default '0',`TableString` varchar(21) NOT NULL default '') ENGINE=InnoDB;4、InnoDB关闭AutoCommit，用事务:CREATE TABLE `INNODB_NA_BE` (`TableId` int(11) NOT NULL default '0',`TableString` varchar(21) NOT NULL default '') ENGINE=InnoDB;5、InnoDB开启AutoCommit，不用事务:CREATE TABLE `INNODB_AU_NB` (`TableId` int(11) NOT NULL default '0',`TableString` varchar(21) NOT NULL default '') ENGINE=InnoDB;6、InnoDB开启AutoCommit，用事务:CREATE TABLE `INNODB_AU_BE` (`TableId` int(11) NOT NULL default '0',`TableString` varchar(21) NOT NULL default '') ENGINE=InnoDB;测试的Python脚本如下：#!/usr/bin/env Python'''MyISAM,InnoDB性能比较作者：空心菜（Invalid）时间：2004-10-22'''import MySQLdb import sysimport osimport stringimport timec = Nonetesttables = [("MyISAM_NT",None,0),("MyISAM_TS",None,1),("INNODB_NA_NB",0,0),("INNODB_NA_BE",0,1),("INNODB_AU_NB",1,0),("INNODB_AU_BE",1,1)]def BeginTrans():print "ExecSQL:BEGIN;"c.execute("BEGIN;")returndef Commit():print "ExecSQL:COMMIT;"c.execute("COMMIT;")returndef AutoCommit(flag):print "ExecSQL:Set AUTOCOMMIT = " str(flag)c.execute("Set AUTOCOMMIT = " str(flag))returndef getcount(table):#print "ExecSQL:select count(*) from " tablec.execute("select count(*) from " table)return c.fetchall()[0][0]def AddTable (Table,TableId,TableString):sql = "INSERT INTO " Table "(TableId, TableString) VALUES( " TableId ",'" TableString "')"try:c.execute(sql)except MySQLdb.OperationalError,error:print "AddTable Error:",errorreturn -1;return c.rowcountdef main():argv = sys.argvif len(argv) < 2:print 'Usage:',argv[0],' TableId TestCount \n'sys.exit(1)global c #mysql访问cursordb_host = "localhost"db_name = "demo"db_user = "root"db_user_passwd = ""print "Config:[%s %s/%s %s] DB\n"%(db_host,db_user,db_user_passwd,db_name)if len(argv) > 2:tableid = argv[1]testcount = int(argv[2]) #for test in testtables:#每次操作前都重写建立数据库连接 try:mdb = MySQLdb.connect(db_host, db_user, db_user_passwd, db_name) except MySQLDb.OperationalError,error:print "Connect Mysql[%s %s/%s %s] DB Error:"%(db_host,db_user,db_user_passwd,db_name),error,"\n"sys.exit(1)else:c = mdb.cursor()table,autocommit,trans = teststarttime = time.time()print table," ",time.strftime("%y-%m-%d %H:%M:%S",time.localtime())if autocommit != None:AutoCommit(autocommit)if trans == 1:BeginTrans()for i in xrange(testcount):tablestring = "0d"%iif (AddTable(table,tableid,tablestring)<1):print "AddTable Error",tablestringif trans == 1:Commit()print time.strftime("%y-%m-%d %H:%M:%S",time.localtime())endtime = time.time()usedtime = endtime-starttimeprint table,"count:",getcount(table)," used time:",usedtime c.close()mdb.close()if __name__ == '__main__':main()测试结果如下：Config:[localhost root/ demo] DBMyISAM_NT 04-10-22 16:33:2404-10-22 16:33:26MyISAM_NT count: 10000 used time: 2.1132440567MyISAM_TS 04-10-22 16:33:26ExecSQL:BEGIN;ExecSQL:COMMIT;04-10-22 16:33:29MyISAM_TS count: 10000 used time: 2.65475201607INNODB_NA_NB 04-10-22 16:33:29ExecSQL:Set AUTOCOMMIT = 004-10-22 16:33:31INNODB_NA_NB count: 10000 used time: 2.51947999001INNODB_NA_BE 04-10-22 16:33:31ExecSQL:Set AUTOCOMMIT = 0ExecSQL:BEGIN;ExecSQL:COMMIT;04-10-22 16:33:35INNODB_NA_BE count: 10000 used time: 3.85625100136INNODB_AU_NB 04-10-22 16:33:35ExecSQL:Set AUTOCOMMIT = 104-10-22 16:34:19INNODB_AU_NB count: 10000 used time: 43.7153041363INNODB_AU_BE 04-10-22 16:34:19ExecSQL:Set AUTOCOMMIT = 1ExecSQL:BEGIN;ExecSQL:COMMIT;04-10-22 16:34:22INNODB_AU_BE count: 10000 used time: 3.14328193665结论：由此得知影响速度的主要原因是AUTOCOMMIT默认设置是打开的，我当时的程序没有显式调用BEGIN;开始事务，导致每插入一条都自动Commit，严重影响了速度。算来也是个低级错误！相关参考：http://dev.mysql.com/doc/mysql/en/COMMIT.htmlhttp://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html