MySQL两种表存储结构性能比较测试过程_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 sys
　　
　　import os
　　
　　import string
　　
　　import 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))
　　
　　return
　　
　　def 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:24
　　
　　04-10-22 16:33:26
　　
　　MyISAM_NT count: 10000 used time: 2.1132440567
　　
　　MyISAM_TS 04-10-22 16:33:26
　　
　　ExecSQL:BEGIN;
　　
　　ExecSQL:COMMIT;
　　
　　04-10-22 16:33:29
　　
　　MyISAM_TS count: 10000 used time: 2.65475201607
　　
　　INNODB_NA_NB 04-10-22 16:33:29
　　
　　ExecSQL:Set AUTOCOMMIT = 0
　　
　　04-10-22 16:33:31
　　
　　INNODB_NA_NB count: 10000 used time: 2.51947999001
　　
　　INNODB_NA_BE 04-10-22 16:33:31
　　
　　ExecSQL:Set AUTOCOMMIT = 0
　　
　　ExecSQL:BEGIN;
　　
　　ExecSQL:COMMIT;
　　
　　04-10-22 16:33:35
　　
　　INNODB_NA_BE count: 10000 used time: 3.85625100136
　　
　　INNODB_AU_NB 04-10-22 16:33:35
　　
　　ExecSQL:Set AUTOCOMMIT = 1
　　
　　04-10-22 16:34:19
　　
　　INNODB_AU_NB count: 10000 used time: 43.7153041363
　　
　　INNODB_AU_BE 04-10-22 16:34:19
　　
　　ExecSQL:Set AUTOCOMMIT = 1
　　
　　ExecSQL:BEGIN;
　　
　　ExecSQL:COMMIT;
　　
　　04-10-22 16:34:22
　　
　　INNODB_AU_BE count: 10000 used time: 3.14328193665
　　
　　结论：
　　
　　由此得知影响速度的主要原因是AUTOCOMMIT默认设置是打开的，我当时的程序没有显式调用BEGIN;开始事务，导致每插入一条都自动Commit，严重影响了速度。