Home Database Mysql Tutorial MySQL两种表存储结构性能比较测试过程_MySQL

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

Jun 01, 2016 pm 02:11 PM
innodb storage performance Compare test structure process


  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)   
  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)  
  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,严重影响了速度。
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Join a new Xianxia adventure! 'Zhu Xian 2' 'Wuwei Test' pre-download is now available Join a new Xianxia adventure! 'Zhu Xian 2' 'Wuwei Test' pre-download is now available Apr 22, 2024 pm 12:50 PM

The "Inaction Test" of the new fantasy fairy MMORPG "Zhu Xian 2" will be launched on April 23. What kind of new fairy adventure story will happen in Zhu Xian Continent thousands of years after the original work? The Six Realm Immortal World, a full-time immortal academy, a free immortal life, and all kinds of fun in the immortal world are waiting for the immortal friends to explore in person! The "Wuwei Test" pre-download is now open. Fairy friends can go to the official website to download. You cannot log in to the game server before the server is launched. The activation code can be used after the pre-download and installation is completed. "Zhu Xian 2" "Inaction Test" opening hours: April 23 10:00 - May 6 23:59 The new fairy adventure chapter of the orthodox sequel to Zhu Xian "Zhu Xian 2" is based on the "Zhu Xian" novel as a blueprint. Based on the world view of the original work, the game background is set

Performance comparison of different Java frameworks Performance comparison of different Java frameworks Jun 05, 2024 pm 07:14 PM

Performance comparison of different Java frameworks: REST API request processing: Vert.x is the best, with a request rate of 2 times SpringBoot and 3 times Dropwizard. Database query: SpringBoot's HibernateORM is better than Vert.x and Dropwizard's ORM. Caching operations: Vert.x's Hazelcast client is superior to SpringBoot and Dropwizard's caching mechanisms. Suitable framework: Choose according to application requirements. Vert.x is suitable for high-performance web services, SpringBoot is suitable for data-intensive applications, and Dropwizard is suitable for microservice architecture.

PHP array key value flipping: Comparative performance analysis of different methods PHP array key value flipping: Comparative performance analysis of different methods May 03, 2024 pm 09:03 PM

The performance comparison of PHP array key value flipping methods shows that the array_flip() function performs better than the for loop in large arrays (more than 1 million elements) and takes less time. The for loop method of manually flipping key values ​​takes a relatively long time.

How to optimize the performance of multi-threaded programs in C++? How to optimize the performance of multi-threaded programs in C++? Jun 05, 2024 pm 02:04 PM

Effective techniques for optimizing C++ multi-threaded performance include limiting the number of threads to avoid resource contention. Use lightweight mutex locks to reduce contention. Optimize the scope of the lock and minimize the waiting time. Use lock-free data structures to improve concurrency. Avoid busy waiting and notify threads of resource availability through events.

What are the syntax and structure characteristics of lambda expressions? What are the syntax and structure characteristics of lambda expressions? Apr 25, 2024 pm 01:12 PM

Lambda expression is an anonymous function without a name, and its syntax is: (parameter_list)->expression. They feature anonymity, diversity, currying, and closure. In practical applications, Lambda expressions can be used to define functions concisely, such as the summation function sum_lambda=lambdax,y:x+y, and apply the map() function to the list to perform the summation operation.

The role of golang function closure in testing The role of golang function closure in testing Apr 24, 2024 am 08:54 AM

Go language function closures play a vital role in unit testing: Capturing values: Closures can access variables in the outer scope, allowing test parameters to be captured and reused in nested functions. Simplify test code: By capturing values, closures simplify test code by eliminating the need to repeatedly set parameters for each loop. Improve readability: Use closures to organize test logic, making test code clearer and easier to read.

What are the differences between function testing and coverage in different languages? What are the differences between function testing and coverage in different languages? Apr 27, 2024 am 11:30 AM

Functional testing verifies function functionality through black-box and white-box testing, while code coverage measures the portion of code covered by test cases. Different languages ​​(such as Python and Java) have different testing frameworks, coverage tools and features. Practical cases show how to use Python's Unittest and Coverage and Java's JUnit and JaCoCo for function testing and coverage evaluation.

What is the performance impact of converting PHP arrays to objects? What is the performance impact of converting PHP arrays to objects? Apr 30, 2024 am 08:39 AM

In PHP, the conversion of arrays to objects will have an impact on performance, mainly affected by factors such as array size, complexity, object class, etc. To optimize performance, consider using custom iterators, avoiding unnecessary conversions, batch converting arrays, and other techniques.

See all articles