mysql - 为什么 MySQL InnoDB 插入这么慢？

Question

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

我提炼了一个非常简单的测试来说明。在测试表中，我试图使其尽可能简单；我的真实代码没有这么简单的布局，并且有关系和附加索引等。但是，更简单的设置显示了相同的性能。

结果如下:

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

将 1M 行插入 MyISAM 需要 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")

Answer 1

InnoDB 具有事务支持，您没有使用显式事务，因此 innoDB 必须在每个语句之后执行提交 ("performs a log flush to disk for every insert")。

在循环之前执行此命令:

START TRANSACTION

循环之后这个

COMMIT