MySQL两种表存储结构MyISAM和InnoDB的性于测试

测试的Python脚本如下:

5、InnoDB开启AutoCommit,不用工作:
CREATE TABLE `INNODB_AU_NB` (
  `TableId` int(11) NOT NULL default ‘0’,
  `TableString` varchar(21) NOT NULL default ”
) ENGINE=InnoDB;

    
    db_host = “localhost”
    db_name = “demo”
    db_user = “root”
    db_user_passwd = “”

        print time.strftime(“%y-%m-%d %H:%M:%S”,time.localtime())
        endtime = time.time()
        usedtime = endtime-starttime

    print “ExecSQL:Set AUTOCOMMIT = “+str(flag)
    c.execute(“Set AUTOCOMMIT = “+str(flag))
    return

        if trans == 1:
            BeginTrans()

    argv = sys.argv

    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

    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])   #

def getcount(table):
    #print  “ExecSQL:select count(*) from “+table
    c.execute(“select count(*) from “+table)
    return c.fetchall()[0][0]

    print “ExecSQL:BEGIN;”
    c.execute(“BEGIN;”)

    for test in testtables:
        #老是操作前都更写起数据库连接    
        try:
            mdb = MySQLdb.connect(db_host, db_user, db_user_passwd,
db_name) 
        except MySQLDb.OperationalError,error:
MySQL,            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())

6、InnoDB开启AutoCommit,用事务:
CREATE TABLE `INNODB_AU_BE` (
  `TableId` int(11) NOT NULL default ‘0’,
  `TableString` varchar(21) NOT NULL default ”
) ENGINE=InnoDB;

 
def AddTable (Table,TableId,TableString):

系参考:
http://dev.mysql.com/doc/mysql/en/COMMIT.html
http://dev.mysql.com/doc/mysql/en/InnoDB_and_AUTOCOMMIT.html

3、InnoDB关闭AutoCommit,不用工作:
CREATE TABLE `INNODB_NA_NB` (
  `TableId` int(11) NOT NULL default ‘0’,
  `TableString` varchar(21) NOT NULL default ”
) ENGINE=InnoDB;

if __name__ == ‘__main__’:
    main()
测试结果如下:

def BeginTrans():

def main():

    global c        #mysql访问cursor

        if trans == 1:
            Commit()

Config:[localhost root/ demo] DB

    print “ExecSQL:COMMIT;”
    c.execute(“COMMIT;”)
    return

 

2、MyISAM用事务表:
CREATE TABLE `MyISAM_TS` (
  `TableId` int(11) NOT NULL default ‘0’,
  `TableString` varchar(21) NOT NULL default ”
) ENGINE=MyISAM;

转载 http://www.jb51.net/article/5620.htm

        if autocommit != None:
            AutoCommit(autocommit)

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;

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)
             ]

    if len(argv) < 2:
        print ‘Usage:’,argv[0],’ TableId TestCount \n’
        sys.exit(1)

def AutoCommit(flag):

    return

        print table,”count:”,getcount(table),” used time:”,usedtime
        c.close()
        mdb.close()

        for i in xrange(testcount):
            tablestring = “%020d”%i
            if (AddTable(table,tableid,tablestring)<1):
                print “AddTable Error”,tablestring

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,严重影响了快慢。
算来也是个低级错误!

#!/usr/bin/env Python
”’
MyISAM,InnoDB性能相比较
作者:空心菜(Invalid)
时间:2004-10-22
”’
import MySQLdb 
import sys
import os
import string
import time

4、InnoDB关闭AutoCommit,用事务:
CREATE TABLE `INNODB_NA_BE` (
  `TableId` int(11) NOT NULL default ‘0’,
  `TableString` varchar(21) NOT NULL default ”
) ENGINE=InnoDB;

def Commit():

网站地图xml地图