请选择 进入手机版 | 继续访问电脑版
贠老师培训群:150322713    贠老师QQ:767708506

贠老师office培训-excel学习网

 找回密码
 立即注册
点击咨询贠老师
查看: 505|回复: 12

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

[复制链接]

607

主题

604

帖子

1909

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1909
发表于 2016-2-26 22:44:27 | 显示全部楼层 |阅读模式

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

测试环境: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,严重影响了速度。


想要了解更多更全面的web前段知识,css样式,html教程,数据库教程,word教程,excel教程等等,就到excel学习网www.excelxue.com




回复

使用道具 举报

0

主题

178

帖子

366

积分

中级会员

Rank: 3Rank: 3

积分
366
发表于 2016-2-28 18:06:52 | 显示全部楼层
这是真的吗?太好了,谢谢您啊
回复 支持 反对

使用道具 举报

2

主题

1214

帖子

108

积分

注册会员

Rank: 2

积分
108
发表于 2016-3-13 15:03:13 | 显示全部楼层
希望楼主能够收集更多的资料,谢谢了!
回复 支持 反对

使用道具 举报

1

主题

1146

帖子

7

积分

注册会员

Rank: 2

积分
7
发表于 2016-3-19 21:46:40 | 显示全部楼层
很不错的帖子,又学到了,力挺
回复 支持 反对

使用道具 举报

0

主题

1162

帖子

45

积分

注册会员

Rank: 2

积分
45
发表于 2016-4-8 16:10:04 | 显示全部楼层
我是路过的,轻轻飘过
回复 支持 反对

使用道具 举报

0

主题

931

帖子

102

积分

注册会员

Rank: 2

积分
102
发表于 2016-4-14 09:08:32 | 显示全部楼层
这个资料非常不错,好东东!
回复 支持 反对

使用道具 举报

7

主题

1111

帖子

65

积分

注册会员

Rank: 2

积分
65
发表于 2016-4-14 14:33:55 | 显示全部楼层
一下子解决了我工作当中的难题,谢谢楼主,非常感谢!
回复 支持 反对

使用道具 举报

0

主题

775

帖子

783

积分

高级会员

Rank: 4

积分
783
发表于 2016-4-24 20:11:13 | 显示全部楼层
不错的平台,以后需要经常光顾!
回复 支持 反对

使用道具 举报

0

主题

1136

帖子

54

积分

注册会员

Rank: 2

积分
54
发表于 2016-4-25 21:20:36 | 显示全部楼层
希望楼主能够收集更多的资料,谢谢了!
回复 支持 反对

使用道具 举报

0

主题

1186

帖子

61

积分

注册会员

Rank: 2

积分
61
发表于 2017-2-24 09:26:10 | 显示全部楼层
好啊楼主,没想到啊,太好了
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则



陕ICP备15003731号  

贠老师培训 GMT+8, 2019-7-18 13:03 , Processed in 0.240882 second(s), 30 queries .

快速回复 返回顶部 返回列表