我正在使用较大的随机数作为密钥(来自另一个系统)。在相当小的表(如几百万行)上进行插入和更新所花费的时间比我认为合理的长得多。
我已经提炼了一个非常简单的测试来说明。在测试表中,我尝试使其尽可能简单。我的真实代码没有如此简单的布局,并具有关系和附加索引等。但是,更简单的设置将显示等效的性能。
结果如下:
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")
InnoDB不能很好地应对“随机”主键。尝试顺序键或自动递增,相信您会看到更好的性能。您的“真实”键字段仍可以建立索引,但是对于批量插入,最好在插入完成后一击就删除并重新创建该索引。有兴趣查看您的基准测试!
一些相关问题