MySQL两种表存储结构MyISAM和InnoDB的性能比较测试

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

相关参考：
http://dev.mysql.com/doc/mysql/en/commit.html
http://dev.mysql.com/doc/mysql/en/innodb_and_autocommit.html