2022年MySQL两种表存储结构性能比较测试过程Mysql教程.docx

《2022年MySQL两种表存储结构性能比较测试过程Mysql教程.docx》由会员分享，可在线阅读，更多相关《2022年MySQL两种表存储结构性能比较测试过程Mysql教程.docx（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、2022年MySQL两种表存储结构性能比较测试过程Mysql教程MySQL支持的两种主要表存储格式MyISAM，InnoDB，上个月做个项目时，先运用了InnoDB，结果速度特殊慢，1秒钟只能插入10几条。后来换成MyISAM格式，一秒钟插入上万条。当时觉的这两个表的性能也差别太大了吧。后来自己推想，不应当差别这么慢，估计是写的插入语句有问题，确定做个测试：测试环境：Redhat Linux9，4CPU，内存2G，MySQL版本为4.1.6-gamma-standard测试程序：PythonPython-MySQL模块。测试方案：1、MyISAM格式分别测试，事务和不用事务两种状况：2、Inn

2、oDB格式分别测试AutoCommit1（不用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、

3、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，用事务:CRE

4、ATE 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 I

5、NNODB_AU_BE (TableId int(11) NOT NULL default 0,TableString varchar(21) NOT NULL default ) ENGINE=InnoDB;测试的Python脚本如下：#!/usr/bin/env PythonMyISAM,InnoDB性能比较作者：空心菜（Invalid）时间：2022-10-22import MySQLdbimport sysimport osimport stringimport timec = Nonetesttables = (MyISAM_NT,None,0),(MyISAM_TS,None,1)

6、,(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)returnd

7、ef getcount(table):#print ExecSQL:select count(*) from +tablec.execute(select count(*) from +table)return c.fetchall()00def AddTable (Table,TableId,TableString):sql = INSERT INTO +Table+(TableId, TableString) VALUES( + TableId+ , + TableString +)try:c.execute(sql)except MySQLdb.OperationalError,erro

8、r:print AddTable Error:,errorreturn -1;return c.rowcountdef main():argv = sys.argvif len(argv) < 2:print Usage:,argv0, TableId TestCount nsys.exit(1)global c #mysql访问cursordb_host = localhostdb_name = demodb_user = rootdb_user_passwd = print Config:%s %s/%s %s DBn%(db_host,db_user,db_user_passwd,

9、db_name)if len(argv) > 2:tableid = argv1testcount = int(argv2) #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,

10、nsys.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 = %020d%iif (AddTable(table,tableid,tablestri

11、ng)<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:,usedtimec.close()mdb.close()if _name_ = _main_:main()测试结果如下：Config:localhost root/ demo

12、 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.65475202207INNODB_NA_NB 04-10-22 16:33:29ExecSQL:Set AUTOCOMMIT = 004-10-22 16:33:31INNODB_NA_NB c

13、ount: 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，严峻影响了速度。算来也是个低级错误！相关参考：