MySQL两种表存储结构性能比较测试过程

  • 来源: 互联网 作者: 若水   2008-04-12/15:13
  •      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):#p#分页标题#e#

    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,严重影响了速度。

    算来也是个低级错误!

    评论 {{userinfo.comments}}

    {{money}}

    {{question.question}}

    A {{question.A}}
    B {{question.B}}
    C {{question.C}}
    D {{question.D}}
    提交

    驱动号 更多