济南网站模板,清新太和做网站,开发小程序的平台,家庭装修设计软件哪个好用转载 http://www.jb51.net/article/5620.htm MySQL支持的两种主要表存储格式MyISAM#xff0c;InnoDB#xff0c;上个月做个项目时#xff0c;先使用了InnoDB#xff0c;结果速度特别慢#xff0c;1秒钟只能插入10几条。后来换成MyISAM格式#xff0c;一秒钟插入上万条。…转载 http://www.jb51.net/article/5620.htm MySQL支持的两种主要表存储格式MyISAMInnoDB上个月做个项目时先使用了InnoDB结果速度特别慢1秒钟只能插入10几条。后来换成MyISAM格式一秒钟插入上万条。当时决定这两个表的性能也差别太大了吧。后来自己推测不应该差别这么慢估计是写的插入语句有问题决定做个测试测试环境Redhat Linux94CPU内存2GMySQL版本为4.1.6-gamma-standard测试程序PythonPython-MySQL模块。测试方案1、MyISAM格式分别测试事务和不用事务两种情况2、InnoDB格式分别测试AutoCommit1不用begin transaction和用begin transaction模式 AutoCommit0 不用begin transaction和用begin transaction模式四种情况。 测试方法为插入10000条记录。为了测试不互相影响单独建立了专用的测试表建表语句如下1、MyISAM不用事务表CREATE TABLE MyISAM_NT ( TableId int(11) NOT NULL default 0, TableString varchar(21) NOT NULL default ) ENGINEMyISAM; 2、MyISAM用事务表:CREATE TABLE MyISAM_TS ( TableId int(11) NOT NULL default 0, TableString varchar(21) NOT NULL default ) ENGINEMyISAM; 3、InnoDB关闭AutoCommit不用事务:CREATE TABLE INNODB_NA_NB ( TableId int(11) NOT NULL default 0, TableString varchar(21) NOT NULL default ) ENGINEInnoDB; 4、InnoDB关闭AutoCommit用事务:CREATE TABLE INNODB_NA_BE ( TableId int(11) NOT NULL default 0, TableString varchar(21) NOT NULL default ) ENGINEInnoDB; 5、InnoDB开启AutoCommit不用事务:CREATE TABLE INNODB_AU_NB ( TableId int(11) NOT NULL default 0, TableString varchar(21) NOT NULL default ) ENGINEInnoDB; 6、InnoDB开启AutoCommit用事务:CREATE TABLE INNODB_AU_BE ( TableId int(11) NOT NULL default 0, TableString varchar(21) NOT NULL default ) ENGINEInnoDB; 测试的Python脚本如下 #!/usr/bin/env PythonMyISAM,InnoDB性能比较作者空心菜Invalid时间2004-10-22import MySQLdb import sysimport osimport stringimport time c None testtables [(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;) return def Commit(): print ExecSQL:COMMIT; c.execute(COMMIT;) return def AutoCommit(flag): print ExecSQL:Set AUTOCOMMIT str(flag) c.execute(Set AUTOCOMMIT str(flag)) returndef getcount(table): #print ExecSQL:select count(*) from table c.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:,error return -1; return c.rowcount def main(): argv sys.argv if len(argv) 2: print Usage:,argv[0], TableId TestCount \n sys.exit(1) global c #mysql访问cursor db_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 test starttime 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 %020d%i if (AddTable(table,tableid,tablestring)1): print AddTable Error,tablestring if trans 1: Commit() print time.strftime(%y-%m-%d %H:%M:%S,time.localtime()) endtime time.time() usedtime endtime-starttime print table,count:,getcount(table), used time:,usedtime c.close() mdb.close() if __name__ __main__: main()测试结果如下Config:[localhost root/ demo] DB MyISAM_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转载于:https://www.cnblogs.com/yingjie13/p/4273238.html
