WEB开发网
开发学院数据库MySQL MySQL两种表存储结构性能比较测试过程 阅读

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

 2008-12-19 11:16:28 来源:WEB开发网   
核心提示: #每次操作前都重写建立数据库连接try:mdb = MySQLdb.connect(db_host, db_user, db_user_passwd, db_name)except MySQLDb.OperationalError,error:print "Connect Mys

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

  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,严重影响了速度。

上一页  1 2 3 

Tags:MySQL 存储 结构

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接