二环建设部网站销售管理软件

我正在使用较大的随机数作为密钥(来自另一个系统)。在相当小的表(如几百万行)上进行插入和更新所花费的时间比我认为合理的长得多。

我已经提炼了一个非常简单的测试来说明。在测试表中，我尝试使其尽可能简单。我的真实代码没有如此简单的布局，并具有关系和附加索引等。但是，更简单的设置将显示等效的性能。

结果如下：

creating the MyISAM table took 0.000 seconds creating 1024000 rows of test data took 1.243 seconds inserting the test data took 6.335 seconds selecting 1023742 rows of test data took 1.435 seconds fetching 1023742 batches of test data took 0.037 seconds dropping the table took 0.089 seconds creating the InnoDB table took 0.276 seconds creating 1024000 rows of test data took 1.165 seconds inserting the test data took 3433.268 seconds selecting 1023748 rows of test data took 4.220 seconds fetching 1023748 batches of test data took 0.037 seconds dropping the table took 0.288 seconds 在MyISAM中插入1M行需要6秒钟；进入InnoDB需要3433秒！

我究竟做错了什么？什么是配置错误？(MySQL是具有默认值的普通Ubuntu安装)

这是测试代码：

import sys, time, random import MySQLdb as db

usage: python script db_username db_password database_name

db = db.connect(host="127.0.0.1",port=3306,user=sys.argv[1],passwd=sys.argv[2],db=sys.argv[3]).cursor()

def test(engine):

start = time.time() # fine for this purpose

db.execute("""

CREATE TEMPORARY TABLE Testing123 ( k INTEGER PRIMARY KEY NOT NULL, v VARCHAR(255) NOT NULL ) ENGINE=%s;"""%engine) duration = time.time()-start print "creating the %s table took %0.3f seconds"%(engine,duration)

start = time.time()

# 1 million rows in 100 chunks of 10K

data = [[(str(random.getrandbits(48)) if a&1 else int(random.getrandbits(31))) for a in xrange(10*1024*2)] for b in xrange(100)]

duration = time.time()-start

print "creating %d rows of test data took %0.3f seconds"%(sum(len(rows)/2 for rows in data),duration)

sql = "REPLACE INTO Testing123 (k,v) VALUES %s;"%("(%s,%s),"*(10*1024))[:-1]

start = time.time()

for rows in data:

db.execute(sql,rows)

duration = time.time()-start

print "inserting the test data took %0.3f seconds"%duration

# execute the query

start = time.time()

query = db.execute("SELECT k,v FROM Testing123;")

duration = time.time()-start