当前位置：主页>Mysql>

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

来源： 作者： 发布时间：2008-10-07 点击次数：

MySQL支持的两种主要表存储格式MyISAM，InnoDB，上个月做个项目时，先使用了InnoDB，结果速度特别慢，1秒钟只能插入10几条。后来换成MyISAM格式，一秒钟插入上万条。当时觉的这两个表的性能也差别太大了吧。后来自己推测，不应该差别这么慢，估计是写的插入语句有问题，决定做个测试： 字串5

测试环境：Redhat Linux9，4CPU，内存2G，MySQL版本为4.1.6-gamma-standard

字串3

测试程序：Python＋Python-MySQL模块。 字串7

测试方案：

字串2

1、MyISAM格式分别测试，事务和不用事务两种情况： 字串1

2、InnoDB格式分别测试AutoCommit＝1（不用begin transaction和用begin transaction模式），AutoCommit=0 （不用begin transaction和用begin transaction模式）四种情况。 字串8

测试方法为插入10000条记录。为了测试不互相影响，单独建立了专用的测试表，建表语句如下：

字串2

1、MyISAM不用事务表： 字串5

以下为引用的内容：

CREATE TABLE `MyISAM_NT` ( 字串5

`TableId` int(11) NOT NULL default '0',

字串4

`TableString` varchar(21) NOT NULL default ''

字串5

) ENGINE=MyISAM;

字串3
字串6

2、MyISAM用事务表：

字串6

以下为引用的内容：

CREATE TABLE `MyISAM_TS` ( 字串4

`TableId` int(11) NOT NULL default '0',

字串7

`TableString` varchar(21) NOT NULL default '' 字串7

) ENGINE=MyISAM; 字串4

字串7

3、InnoDB关闭AutoCommit，不用事务：

字串1

以下为引用的内容：

CREATE TABLE `INNODB_NA_NB` ( 字串4

`TableId` int(11) NOT NULL default '0', 字串1

`TableString` varchar(21) NOT NULL default ''

字串1

) ENGINE=InnoDB;

字串2

字串9

4、InnoDB关闭AutoCommit，用事务：

字串2

以下为引用的内容：

CREATE TABLE `INNODB_NA_BE` ( 字串7

`TableId` int(11) NOT NULL default '0', 字串1

`TableString` varchar(21) NOT NULL default ''

字串7

) ENGINE=InnoDB; 字串8

字串6

5、InnoDB开启AutoCommit，不用事务： 字串9

以下为引用的内容：

CREATE TABLE `INNODB_AU_NB` (

字串7

`TableId` int(11) NOT NULL default '0', 字串1

`TableString` varchar(21) NOT NULL default ''

字串9

) ENGINE=InnoDB; 字串7

字串7

6、InnoDB开启AutoCommit，用事务： 字串7

以下为引用的内容：

CREATE TABLE `INNODB_AU_BE` ( 字串6

`TableId` int(11) NOT NULL default '0', 字串1

`TableString` varchar(21) NOT NULL default ''

字串3

) ENGINE=InnoDB; 字串3

测试的Python脚本如下： 字串3

#!/usr/bin/env Python 字串1

'''

字串1

字串9

MyISAM,InnoDB性能比较 字串3

作者：空心菜（Invalid） 字串7

时间：2004-10-22

字串1

以下为引用的内容：

'''

字串8

import MySQLdb 字串5

import sys 字串2

import os 字串3

import string 字串2

import time 字串5

c = None

字串6

testtables = [("MyISAM_NT",None,0),

字串5

("MyISAM_TS",None,1), 字串5

("INNODB_NA_NB",0,0),

字串1

("INNODB_NA_BE",0,1), 字串4

("INNODB_AU_NB",1,0), 字串4

("INNODB_AU_BE",1,1) 字串8

] 字串2

def BeginTrans(): 字串5

print "ExecSQL:BEGIN;" 字串1

c.execute("BEGIN;") 字串3

return

字串2

def Commit(): 字串6

print "ExecSQL:COMMIT;"

字串2

c.execute("COMMIT;")

字串2

return

字串8

def AutoCommit(flag): 字串4

print "ExecSQL:Set AUTOCOMMIT = "+str(flag) 字串4

c.execute("Set AUTOCOMMIT = "+str(flag))

字串1

return

字串5

def getcount(table):

字串6

#print "ExecSQL:select count(*) from "+table

字串8

c.execute("select count(*) from "+table) 字串8

return c.fetchall()[0][0] 字串7

def AddTable (Table,TableId,TableString): 字串3

sql = "INSERT INTO "+Table+"(TableId, TableString) VALUES( "+ TableId+ ",'" + TableString +"')"

字串9

try:

字串2

c.execute(sql)

字串7

except MySQLdb.OperationalError,error: 字串4

print "AddTable Error:",error

字串1

return -1;

字串2

return c.rowcount

字串8

def main():

字串9

argv = sys.argv 字串3

if len(argv) < 2:

字串7

print 'Usage:',argv[0],' TableId TestCount \n'

字串7

sys.exit(1)

字串8

global c #MySQL访问cursor

字串5

db_host = "localhost"

字串3

db_name = "demo"

字串1

db_user = "root"

字串1

db_user_passwd = "" 字串1

print "Config:[%s %s/%s %s] DB\n"%(db_host,db_user,db_user_passwd,db_name) 字串8

if len(argv) > 2: 字串4

tableid = argv[1]

字串7

testcount = int(argv[2]) #

字串4

for test in testtables:

字串6

字串1

#每次操作前都重写建立数据库连接 字串8

以下为引用的内容：

try: 字串6

mdb = MySQLdb.connect(db_host, db_user, db_user_passwd, db_name) 字串3

except MySQLDb.OperationalError,error: 字串8

print "Connect MySQL[%s %s/%s %s] DB Error:"%(db_host,db_user,db_user_passwd,db_name),error,"\n" 字串5

sys.exit(1) 字串7

else: 字串2

c = mdb.cursor()

字串9

table,autocommit,trans = test

字串9

starttime = time.time()

字串3

print table," ",time.strftime("%y-%m-%d %H:%M:%S",time.localtime())

字串9

if autocommit != None: 字串7

AutoCommit(autocommit)

字串6

if trans == 1:

字串7

BeginTrans() 字串2

for i in xrange(testcount): 字串6

tablestring = "%020d"%i

字串2

if (AddTable(table,tableid,tablestring)<1): 字串6

print "AddTable Error",tablestring 字串2

if trans == 1:

字串6

Commit()

字串2

print time.strftime("%y-%m-%d %H:%M:%S",time.localtime())

字串3

endtime = time.time() 字串2

usedtime = endtime-starttime

字串7

print table,"count:",getcount(table)," used time:",usedtime 字串2

c.close()

字串4

mdb.close()

字串7

if __name__ == '__main__': 字串3

main() 字串2

测试结果如下： 字串6

Config:[localhost root/ demo] DB

字串5

MyISAM_NT 04-10-22 16:33:24

字串5

04-10-22 16:33:26 字串5

MyISAM_NT count: 10000 used time: 2.1132440567 字串1

MyISAM_TS 04-10-22 16:33:26

字串3

ExecSQL:BEGIN;

字串8

ExecSQL:COMMIT;

字串6

04-10-22 16:33:29

字串6

MyISAM_TS count: 10000 used time: 2.65475201607 字串2

INNODB_NA_NB 04-10-22 16:33:29 字串9

ExecSQL:Set AUTOCOMMIT = 0

字串3

04-10-22 16:33:31 字串4

INNODB_NA_NB count: 10000 used time: 2.51947999001

字串6

INNODB_NA_BE 04-10-22 16:33:31

字串7

ExecSQL:Set AUTOCOMMIT = 0

字串8

ExecSQL:BEGIN;

字串4

ExecSQL:COMMIT; 字串1

04-10-22 16:33:35 字串1

INNODB_NA_BE count: 10000 used time: 3.85625100136 字串5

INNODB_AU_NB 04-10-22 16:33:35 字串8

ExecSQL:Set AUTOCOMMIT = 1

字串1

04-10-22 16:34:19 字串2

INNODB_AU_NB count: 10000 used time: 43.7153041363 字串5

INNODB_AU_BE 04-10-22 16:34:19 字串7

ExecSQL:Set AUTOCOMMIT = 1

字串3

ExecSQL:BEGIN;

字串8

ExecSQL:COMMIT;

字串2

04-10-22 16:34:22

字串6

INNODB_AU_BE count: 10000 used time: 3.14328193665 字串7

字串8

结论：

字串5

由此得知影响速度的主要原因是AUTOCOMMIT默认设置是打开的， 字串9

我当时的程序没有显式调用BEGIN;开始事务，导致每插入一条都自动Commit，严重影响了速度。

字串9

算来也是个低级错误！

字串3

相关参考：

字串4

http://dev.mysql.com/doc/MySQL/en/COMMIT.html

字串1

http://dev.mysql.com/doc/MySQL/en/InnoDB_and_AUTOCOMMIT.html

字串7


相关文章