MySQL Two table storage structure MyISAM and InnoDB performance comparison test

MyISAM: This is the default type, which is based on the traditional ISAM type, ISAM is an abbreviation for indexed sequential access method (indexed sequential access methods), which is the standard way to store records and files. Compared to other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe and do not support foreign keys

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. The InnoDB table is fast. Has a richer feature than BDB, so it is recommended if a transaction-safe storage engine is required.

The difference between MyISAM and InnoDB:

InnoDB the transaction is open by default (set autocommit = 0) that is, each time a record is inserted, a table of type InnoDB treats it as a separate transaction.

So if we insert 10,000 records and do not close the transaction, then the InnoDB type of table will treat it as 10,000 transactions, the total time to insert is many, this time must first turn off the transaction and then insert, so the speed is fast

MySQL supports the two main table storage format Myisam,innodb, last month to do a project, the first use of InnoDB, the result is particularly slow, 1 seconds can only insert 10. Later into the MyISAM format, one second to insert tens of thousands of bars. It was decided that the performance of the two tables was too different. Later, I guess, should not be different so slow, it is estimated that the INSERT statement has a problem, decided to do a test:
Test environment: Redhat LINUX9,4CPU, Memory 2g,mysql version 4.1.6-gamma-standard
Test procedure: Python+python-mysql module.
Test scenario:
1, MyISAM format test, transaction and no transaction two cases:
2, InnoDB format test autocommit=1 (without begin transaction and with the Begin Transaction mode),
Autocommit=0 (without BEGIN transaction and with BEGIN TRANSACTION mode) four cases.

The test method is to insert 10,000 records. In order to test the non-interaction, a dedicated test table was created, with the following statement:
1, MyISAM do not use the transaction table:
CREATE TABLE ' Myisam_nt ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) Engine=myisam;

2, MyISAM with the transaction table:
CREATE TABLE ' Myisam_ts ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) Engine=myisam;

3, InnoDB close autocommit, no business:
CREATE TABLE ' Innodb_na_nb ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) Engine=innodb;

4, InnoDB close autocommit, with transaction:
CREATE TABLE ' Innodb_na_be ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) Engine=innodb;

5, InnoDB Open autocommit, no business:
CREATE TABLE ' Innodb_au_nb ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) Engine=innodb;

6, InnoDB Open autocommit, with business:
CREATE TABLE ' Innodb_au_be ' (
' TableId ' int (one) not NULL default ' 0 ',
' tablestring ' varchar (+) not NULL default '
) Engine=innodb;

The test Python script is as follows:

#!/usr/bin/env Python
‘‘‘
Myisam,innodb Performance Comparison
Water Spinach (Invalid)
Date: 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 Set 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 ()
The test results are as follows:

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
Conclusion:
The main reason for this is that the AUTOCOMMIT default setting is open, and the
my program did not explicitly call begin, and started the transaction, causing the automatic commit of each insert, which severely affected the speed. The
is also a low-level error!