您当前的位置: 首页 » MySQL/MSSQL编程 » MySQL两种表存储结构性能比较测试过程
MySQL两种表存储结构性能比较测试过程
论坛链接
MySQL支持的两种主要表存储格式MyISAM，InnoDB，上个月做个项目时，先使用了InnoDB，结果速度特别慢，1秒钟只能插入10几条。后来换成MyISAM格式，一秒钟插入上万条。当时觉的这两个表的性能也差别太大了吧。后来自己推测，不应该差别这么慢，估计是写的插入语句有问题，决定做个测试：
测试环境：Redhat Linux9，4CPU，内存2G，MySQL版本为416-gamma-standard
测试程序：Python＋Python-MySQL模块。
测试方案：
1、MyISAM格式分别测试，事务和不用事务两种情况：
2、InnoDB格式分别测试Automit＝1（不用begin transaction和用begin transaction模式），Automit=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关闭Automit，不用事务：
以下为引用的内容：
CREATE TABLE `INNODB_NA_NB` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;
4、InnoDB关闭Automit，用事务：
以下为引用的内容：
CREATE TABLE `INNODB_NA_BE` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;
5、InnoDB开启Automit，不用事务
以下为引用的内容：
CREATE TABLE `INNODB_AU_NB` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;
6、InnoDB开启Automit，用事务：
以下为引用的内容：
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;"
cexecute("BEGIN;")
return
def mit():
print "ExecSQL:MIT;"
cexecute("MIT;")
return
def Automit(flag):
print "ExecSQL:Set AUTOMIT = "+str(flag)
cexecute("Set AUTOMIT = "+str(flag))
return
def getcount(table):
#print "ExecSQL:select count(*) from "+table
cexecute("select count(*) from "+table)
return cfetchall()[0][0]
def AddTable (Table,TableId,TableString):
sql = "INSERT INTO "+Table+"(TableId, TableString) VALUES( "+ TableId+ ",'" + TableString +"')"
try:
cexecute(sql)
except MySQLdbOperationalError,error:
print "AddTable Error:",error
return -1;
return crowcount
def main():
argv = sysargv
if len(argv) < 2:
print 'Usage:',argv[0],' TableId TestCount \n'
sysexit(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 = MySQLdbconnect(db_host, db_user, db_user_passwd, db_name)
except MySQLDbOperationalError,error:
print "Connect MySQL[%s %s/%s %s] DB Error:"%(db_host,db_user,db_user_passwd,db_name),error,"\n"
sysexit(1)
else:
c = mdbcursor()
table,automit,trans = test
starttime = timetime()
print table," ",timestrftime("%y-%m-%d %H:%M:%S",timelocaltime())
if automit != None: _
Automit(automit)
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:
mit()
print timestrftime("%y-%m-%d %H:%M:%S",timelocaltime())
endtime = timetime()
usedtime = endtime-starttime
print table,"count:",getcount(table)," used time:",usedtime
cclose()
mdbclose()
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: 21132440567
MyISAM_TS 04-10-22 16:33:26
ExecSQL:BEGIN;
ExecSQL:MIT;
04-10-22 16:33:29
MyISAM_TS count: 10000 used time: 265475201607
INNODB_NA_NB 04-10-22 16:33:29
ExecSQL:Set AUTOMIT = 0
04-10-22 16:33:31
INNODB_NA_NB count: 10000 used time: 251947999001
INNODB_NA_BE 04-10-22 16:33:31
ExecSQL:Set AUTOMIT = 0
ExecSQL:BEGIN;
ExecSQL:MIT;
04-10-22 16:33:35
INNODB_NA_BE count: 10000 used time: 385625100136
INNODB_AU_NB 04-10-22 16:33:35
ExecSQL:Set AUTOMIT = 1
04-10-22 16:34:19
INNODB_AU_NB count: 10000 used time: 437153041363
INNODB_AU_BE 04-10-22 16:34:19
ExecSQL:Set AUTOMIT = 1
ExecSQL:BEGIN;
ExecSQL:MIT;
04-10-22 16:34:22
INNODB_AU_BE count: 10000 used time: 314328193665
结论：
由此得知影响速度的主要原因是AUTOMIT默认设置是打开的，
我当时的程序没有显式调用BEGIN;开始事务，导致每插入一条都自动mit，严重影响了速度。
算来也是个低级错误！
相关参考：
http://devmysql/doc/MySQL/en/MIThtml
http://devmysql/doc/MySQL/en/InnoDB_and_AUTOMIThtml
测试环境：Redhat Linux9，4CPU，内存2G，MySQL版本为416-gamma-standard
测试程序：Python＋Python-MySQL模块。
测试方案：
1、MyISAM格式分别测试，事务和不用事务两种情况：
2、InnoDB格式分别测试Automit＝1（不用begin transaction和用begin transaction模式），Automit=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关闭Automit，不用事务：
以下为引用的内容：
CREATE TABLE `INNODB_NA_NB` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;
4、InnoDB关闭Automit，用事务：
以下为引用的内容：
CREATE TABLE `INNODB_NA_BE` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;
5、InnoDB开启Automit，不用事务
以下为引用的内容：
CREATE TABLE `INNODB_AU_NB` (
`TableId` int(11) NOT NULL default '0',
`TableString` varchar(21) NOT NULL default ''
) ENGINE=InnoDB;
6、InnoDB开启Automit，用事务：
以下为引用的内容：
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;"
cexecute("BEGIN;")
return
def mit():
print "ExecSQL:MIT;"
cexecute("MIT;")
return
def Automit(flag):
print "ExecSQL:Set AUTOMIT = "+str(flag)
cexecute("Set AUTOMIT = "+str(flag))
return
def getcount(table):
#print "ExecSQL:select count(*) from "+table
cexecute("select count(*) from "+table)
return cfetchall()[0][0]
def AddTable (Table,TableId,TableString):
sql = "INSERT INTO "+Table+"(TableId, TableString) VALUES( "+ TableId+ ",'" + TableString +"')"
try:
cexecute(sql)
except MySQLdbOperationalError,error:
print "AddTable Error:",error
return -1;
return crowcount
def main():
argv = sysargv
if len(argv) < 2:
print 'Usage:',argv[0],' TableId TestCount \n'
sysexit(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 = MySQLdbconnect(db_host, db_user, db_user_passwd, db_name)
except MySQLDbOperationalError,error:
print "Connect MySQL[%s %s/%s %s] DB Error:"%(db_host,db_user,db_user_passwd,db_name),error,"\n"
sysexit(1)
else:
c = mdbcursor()
table,automit,trans = test
starttime = timetime()
print table," ",timestrftime("%y-%m-%d %H:%M:%S",timelocaltime())
if automit != None: _
Automit(automit)
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:
mit()
print timestrftime("%y-%m-%d %H:%M:%S",timelocaltime())
endtime = timetime()
usedtime = endtime-starttime
print table,"count:",getcount(table)," used time:",usedtime
cclose()
mdbclose()
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: 21132440567
MyISAM_TS 04-10-22 16:33:26
ExecSQL:BEGIN;
ExecSQL:MIT;
04-10-22 16:33:29
MyISAM_TS count: 10000 used time: 265475201607
INNODB_NA_NB 04-10-22 16:33:29
ExecSQL:Set AUTOMIT = 0
04-10-22 16:33:31
INNODB_NA_NB count: 10000 used time: 251947999001
INNODB_NA_BE 04-10-22 16:33:31
ExecSQL:Set AUTOMIT = 0
ExecSQL:BEGIN;
ExecSQL:MIT;
04-10-22 16:33:35
INNODB_NA_BE count: 10000 used time: 385625100136
INNODB_AU_NB 04-10-22 16:33:35
ExecSQL:Set AUTOMIT = 1
04-10-22 16:34:19
INNODB_AU_NB count: 10000 used time: 437153041363
INNODB_AU_BE 04-10-22 16:34:19
ExecSQL:Set AUTOMIT = 1
ExecSQL:BEGIN;
ExecSQL:MIT;
04-10-22 16:34:22
INNODB_AU_BE count: 10000 used time: 314328193665
结论：
由此得知影响速度的主要原因是AUTOMIT默认设置是打开的，
我当时的程序没有显式调用BEGIN;开始事务，导致每插入一条都自动mit，严重影响了速度。
算来也是个低级错误！
相关参考：
http://devmysql/doc/MySQL/en/MIThtml
http://devmysql/doc/MySQL/en/InnoDB_and_AUTOMIThtml