如何提升我的INSERT语句性能？

Question

Josh在这里的回答让我对如何将一个256x64x250的值数组插入到MySQL数据库中有了一个不错的开端。但是，当我实际在我的数据上尝试他的INSERT语句时，结果非常慢（一个16Mb的文件花了6分钟）。

ny, nx, nz = np.shape(data)
query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s)"""
for frames in range(nz):
    for rows in range(ny):
        for cols in range(nx):
            cursor.execute(query, (frames, rows, cols, data[rows,cols,frames]))

我在阅读《MySQL for Python》时了解到，这并不是正确的方法，因为执行400万个单独的插入操作非常低效。

我的数据中有很多零（实际上超过90%），所以我加了一个IF语句，只插入大于零的值，并且我使用了executemany()来替代：

query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
values = []
for frames in range(nz):
    for rows in range(ny):
        for cols in range(nx):
            if data[rows,cols,frames] > 0.0:
                values.append((frames, rows, cols, data[rows,cols,frames]))           
cur.executemany(query, values)

这奇迹般地把我的处理时间缩短到了大约20秒，其中14秒用于创建values的列表（37k行），4秒用于实际插入数据库。

所以我现在在想，如何能进一步加快这个过程呢？因为我感觉我的循环效率非常低，应该有更好的方法。如果我需要为每只狗插入30个测量值，这样仍然需要10分钟，这对这么多数据来说似乎太长了。

这里有两个版本的我的原始文件：带有表头或没有表头。我很想尝试LOAD DATA INFILE，但我不知道如何正确解析数据。

Answer 1

我不使用Python或mySQL，但批量插入数据的速度通常可以通过使用事务来加快。

Answer 2

插入400万行数据（16MB）最快的方法是使用“load data infile”这个命令，具体可以参考这个链接：http://dev.mysql.com/doc/refman/5.0/en/load-data.html

所以，如果可以的话，先生成一个csv文件，然后再用“load data infile”来加载数据。

希望这对你有帮助 :)

补充说明

我拿了你原来的数据文件rolloff.dat，写了一个简单的程序，把它转换成了csv格式。

你可以从这里下载frames.dat文件：http://rapidshare.com/files/454896698/frames.dat

Frames.dat

patient_name, sample_date dd/mm/yyyy, frame_time (ms), frame 0..248, row 0..255, col 0..62, value
"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,5,39,0.4
"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,5,40,0.4
...
"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,10,42,0.4
"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,10,43,0.4
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,4,40,0.4
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,5,39,0.4
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,5,40,0.7
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,6,44,0.7
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,6,45,0.4
...
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,10,0.4
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,11,0.4
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,12,1.1
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,13,1.4
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,14,0.4

这个文件只包含每行和每列都有值的帧数据，所以值为零的行被排除了。从你原来的文件中生成了24799行数据。

接下来，我创建了一个临时的加载表（暂存表），把frames.dat文件加载到这个表里。这个临时表可以让你在把数据加载到正式的生产或报告表之前，对数据进行处理或转换。

drop table if exists sample_temp;
create table sample_temp
(
patient_name varchar(255) not null,
sample_date date,
frame_time decimal(6,2) not null default 0,
frame_id tinyint unsigned not null,
row_id tinyint unsigned not null,
col_id tinyint unsigned not null,
value decimal(4,1) not null default 0,
primary key (frame_id, row_id, col_id)
)
engine=innodb;

剩下的就是加载数据了（注意：我用的是Windows系统，所以你需要修改这个脚本，使其适用于Linux系统 - 检查路径名并把'\r\n'改成'\n'）

truncate table sample_temp;

start transaction;

load data infile 'c:\\import\\frames.dat' 
into table sample_temp
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\r\n'
ignore 1 lines
(
patient_name,
@sample_date,
frame_time,
frame_id,
row_id,
col_id,
value
)
set 
sample_date = str_to_date(@sample_date,'%d/%m/%Y');

commit;

Query OK, 24799 rows affected (1.87 sec)
Records: 24799  Deleted: 0  Skipped: 0  Warnings: 0

这24000行数据在1.87秒内就加载完成了。

希望这对你有帮助 :)

Answer 3

如果你的数据是一个numpy数组，你可以试试这个：

query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
values = []
rows, cols, frames = numpy.nonzero(data)
for row, col, frame in zip(rows, cols, frames):
    values.append((frame, row, col, data[row,col,frame]))

cur.executemany(query, values)

或者

query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
rows, cols, frames = numpy.nonzero(data)
values = [(row, col, frame, val) for row, col, frame, val in zip(rows, cols, frames, data[rows,cols,frames])]
cur.executemany(query, values)

希望这对你有帮助