國內最大的酷站演示中心!
mysql支持的兩種主要表存儲格式myisam，innodb，上個月做個項目時，先使用了innodb，結果速度特別慢，1秒鐘只能插入10幾條。后來換成myisam格式，一秒鐘插入上萬條。當時決定這兩個表的性能也差別太大了吧。后來自己推測，不應該差別這么慢，估計是寫的插入語句有問題，決定做個測試：
測試環(huán)境：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
