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 = "0d"%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，严重影响了速度。 算来也是个低级错误！

相关参考： [url=http://dev.mysql.com/doc/mysql/en/COMMIT.html]http://dev.mysql.com/doc/mysql/en/COMMIT.html[/url] [url=http://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html]http://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html[/url]