熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

MySQL兩種表存儲結構性能比較測試過程

2013-11-23 20:56:28  來源: MySQL 

  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嚴重影響了速度
From:http://tw.wingwit.com/Article/program/MySQL/201311/29332.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.