快捷導(dǎo)航

MySQL兩種表存儲(chǔ)結(jié)構(gòu)MyISAM和InnoDB的性能比較測(cè)試

更新時(shí)間：2006年12月25日 00:00:00 作者：

MySQL支持的兩種主要表存儲(chǔ)格式MyISAM，InnoDB，上個(gè)月做個(gè)項(xiàng)目時(shí)，先使用了InnoDB，結(jié)果速度特別慢，1秒鐘只能插入10幾條。后來(lái)?yè)Q成MyISAM格式，一秒鐘插入上萬(wàn)條。當(dāng)時(shí)決定這兩個(gè)表的性能也差別太大了吧。后來(lái)自己推測(cè)，不應(yīng)該差別這么慢，估計(jì)是寫(xiě)的插入語(yǔ)句有問(wèn)題，決定做個(gè)測(cè)試：
測(cè)試環(huán)境：Redhat Linux9，4CPU，內(nèi)存2G，MySQL版本為4.1.6-gamma-standard
測(cè)試程序：Python＋Python-MySQL模塊。
測(cè)試方案：
1、MyISAM格式分別測(cè)試，事務(wù)和不用事務(wù)兩種情況：
2、InnoDB格式分別測(cè)試AutoCommit＝1（不用begin transaction和用begin transaction模式），
AutoCommit=0 （不用begin transaction和用begin transaction模式）四種情況。

測(cè)試方法為插入10000條記錄。為了測(cè)試不互相影響，單獨(dú)建立了專(zhuān)用的測(cè)試表，建表語(yǔ)句如下：
1、MyISAM不用事務(wù)表：
CREATE TABLE `MyISAM_NT` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=MyISAM;

2、MyISAM用事務(wù)表:
CREATE TABLE `MyISAM_TS` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=MyISAM;

3、InnoDB關(guān)閉AutoCommit，不用事務(wù):
CREATE TABLE `INNODB_NA_NB` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;

4、InnoDB關(guān)閉AutoCommit，用事務(wù):
CREATE TABLE `INNODB_NA_BE` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;

5、InnoDB開(kāi)啟AutoCommit，不用事務(wù):
CREATE TABLE `INNODB_AU_NB` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;

6、InnoDB開(kāi)啟AutoCommit，用事務(wù):
CREATE TABLE `INNODB_AU_BE` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;

測(cè)試的Python腳本如下：

#!/usr/bin/env Python
'''
MyISAM,InnoDB性能比較
作者：空心菜（Invalid）
時(shí)間：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訪(fǎng)問(wèn)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:
        #每次操作前都重寫(xiě)建立數(shù)據(jù)庫(kù)連接
        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()
測(cè)試結(jié)果如下：

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
結(jié)論：
由此得知影響速度的主要原因是AUTOCOMMIT默認(rèn)設(shè)置是打開(kāi)的，
我當(dāng)時(shí)的程序沒(méi)有顯式調(diào)用BEGIN;開(kāi)始事務(wù)，導(dǎo)致每插入一條都自動(dòng)Commit，嚴(yán)重影響了速度。
算來(lái)也是個(gè)低級(jí)錯(cuò)誤！

您可能感興趣的文章: