mysql 5.5 innodb 好慢_为什么MySQL InnoDB插入这么慢？

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

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

结果如下：

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

print "selecting %d rows of test data took %0.3f seconds"%(query,duration)

# get the rows in chunks of 10K

rows = 0

start = time.time()

while query:

batch = min(query,10*1024)

query -= batch

rows += len(db.fetchmany(batch))

duration = time.time()-start

print "fetching %d batches of test data took %0.3f seconds"%(rows,duration)

# drop the table

start = time.time()

db.execute("DROP TABLE Testing123;")

duration = time.time()-start

print "dropping the table took %0.3f seconds"%duration

test("MyISAM")

test("InnoDB")