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

时间:2016-04-13

  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,严重影响了速度。
上一条:学习MySQL常用操作命令 下一条:甲骨文宣布收购芬兰开放源码厂商Innobase

相关文章

最新文章