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

tzlink

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