程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL兩種表存儲結構MyISAM和InnoDB的性能比較測試,myisaminnodb

MySQL兩種表存儲結構MyISAM和InnoDB的性能比較測試,myisaminnodb

編輯:MySQL綜合教程

MySQL兩種表存儲結構MyISAM和InnoDB的性能比較測試,myisaminnodb


轉載 http://www.jb51.net/article/5620.htm

 

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved