mysql 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")