MySQL支持的两种主要表存储格式MyISAMInnoDB上个月做个项目时先使用了InnoDB结果速度特别慢秒钟只能插入几条后来换成MyISAM格式一秒钟插入上万条当时觉的这两个表的性能也差别太大了吧后来自己推测不应该差别这么慢估计是写的插入语句有问题决定做个测试
测试环境Redhat LinuxCPU内存GMySQL版本为gammastandard
测试程序Python＋PythonMySQL模块
测试方案
MyISAM格式分别测试事务和不用事务两种情况
InnoDB格式分别测试AutoCommit＝（不用begin transaction和用begin transaction模式）
AutoCommit= （不用begin transaction和用begin transaction模式）四种情况
测试方法为插入条记录为了测试不互相影响单独建立了专用的测试表建表语句如下
MyISAM不用事务表
CREATE TABLE `MyISAM_NT` (
`TableId` int() NOT NULL default
`TableString` varchar() NOT NULL default
) ENGINE=MyISAM;
MyISAM用事务表:
CREATE TABLE `MyISAM_TS` (
`TableId` int() NOT NULL default
`TableString` varchar() NOT NULL default
) ENGINE=MyISAM;
InnoDB关闭AutoCommit不用事务:
CREATE TABLE `INNODB_NA_NB` (
`TableId` int() NOT NULL default
`TableString` varchar() NOT NULL default
) ENGINE=InnoDB;
InnoDB关闭AutoCommit用事务:
CREATE TABLE `INNODB_NA_BE` (
`TableId` int() NOT NULL default
`TableString` varchar() NOT NULL default
) ENGINE=InnoDB;
InnoDB开启AutoCommit不用事务:
CREATE TABLE `INNODB_AU_NB` (
`TableId` int() NOT NULL default
`TableString` varchar() NOT NULL default
) ENGINE=InnoDB;
InnoDB开启AutoCommit用事务:
CREATE TABLE `INNODB_AU_BE` (
`TableId` int() NOT NULL default
`TableString` varchar() NOT NULL default
) ENGINE=InnoDB;
测试的Python脚本如下
#!/usr/bin/env Python
MyISAMInnoDB性能比较
作者空心菜（Invalid）
时间
import MySQLdb
import sys
import os
import string
import time
c = None
testtables = [(MyISAM_NTNone)
(MyISAM_TSNone)
(INNODB_NA_NB)
(INNODB_NA_BE)
(INNODB_AU_NB)
(INNODB_AU_BE)
]
def BeginTrans():
print ExecSQL:BEGIN;
cexecute(BEGIN;)
return
def Commit():
print ExecSQL:COMMIT;
cexecute(COMMIT;)
return
def AutoCommit(flag):
print ExecSQL:Set AUTOCOMMIT = +str(flag)
cexecute(Set AUTOCOMMIT = +str(flag))
return
def getcount(table):
#print ExecSQL:select count(*) from +table
cexecute(select count(*) from +table)
return cfetchall()[][]
def AddTable (TableTableIdTableString):
sql = INSERT INTO +Table+(TableId TableString) VALUES( + TableId+ + TableString +)
try:
cexecute(sql)
except MySQLdbOperationalErrorerror:
print AddTable Error:error
return ;
return crowcount
def main():
argv = sysargv
if len(argv) < :
print Usage:argv[] TableId TestCount \n
sysexit()
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_hostdb_userdb_user_passwddb_name)
if len(argv) > :
tableid = argv[]
testcount = int(argv[]) #
for test in testtables:
#每次操作前都重写建立数据库连接
try:
mdb = nnect(db_host db_user db_user_passwd db_name)
except MySQLDbOperationalErrorerror:
print Connect Mysql[%s %s/%s %s] DB Error:%(db_hostdb_userdb_user_passwddb_name)error\n
sysexit()
else:
c = mdbcursor()
tableautocommittrans = test
starttime = timetime()
print table timestrftime(%y%m%d %H:%M:%Stimelocaltime())
if autocommit != None:
AutoCommit(autocommit)
if trans == :
BeginTrans()
for i in xrange(testcount):
tablestring = %d%i
if (AddTable(tabletableidtablestring)<):
print AddTable Errortablestring
if trans == :
Commit()
print timestrftime(%y%m%d %H:%M:%Stimelocaltime())
endtime = timetime()
usedtime = endtimestarttime
print tablecount:getcount(table) used time:usedtime
cclose()
mdbclose()
if __name__ == __main__:
main()
测试结果如下
Config:[localhost root/ demo] DB
MyISAM_NT ::
::
MyISAM_NT count: used time:
MyISAM_TS ::
ExecSQL:BEGIN;
ExecSQL:COMMIT;
::
MyISAM_TS count: used time:
INNODB_NA_NB ::
ExecSQL:Set AUTOCOMMIT =
::
INNODB_NA_NB count: used time:
INNODB_NA_BE ::
ExecSQL:Set AUTOCOMMIT =
ExecSQL:BEGIN;
ExecSQL:COMMIT;
::
INNODB_NA_BE count: used time:
INNODB_AU_NB ::
ExecSQL:Set AUTOCOMMIT =
::
INNODB_AU_NB count: used time:
INNODB_AU_BE ::
ExecSQL:Set AUTOCOMMIT =
ExecSQL:BEGIN;
ExecSQL:COMMIT;
::
INNODB_AU_BE count: used time:
结论
由此得知影响速度的主要原因是AUTOCOMMIT默认设置是打开的我当时的程序没有显式调用BEGIN;开始事务导致每插入一条都自动Commit严重影响了速度
