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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

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

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.

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.

How to use benchmarks to evaluate the performance of Java functions? How to use benchmarks to evaluate the performance of Java functions? Apr 19, 2024 pm 10:18 PM

A way to benchmark the performance of Java functions is to use the Java Microbenchmark Suite (JMH). Specific steps include: Adding JMH dependencies to the project. Create a new Java class and annotate it with @State to represent the benchmark method. Write the benchmark method in the class and annotate it with @Benchmark. Run the benchmark using the JMH command line tool.

Performance comparison of C++ with other languages Performance comparison of C++ with other languages Jun 01, 2024 pm 10:04 PM

When developing high-performance applications, C++ outperforms other languages, especially in micro-benchmarks. In macro benchmarks, the convenience and optimization mechanisms of other languages ​​such as Java and C# may perform better. In practical cases, C++ performs well in image processing, numerical calculations and game development, and its direct control of memory management and hardware access brings obvious performance advantages.

See all articles