我正在研究大型MySQL数据库,我需要提高特定表上的INSERT性能.这个包含大约200百万行,其结构如下:
(一个小前提:我不是数据库专家,所以我编写的代码可能基于错误的基础.请帮助我理解我的错误:))
CREATE TABLE IF NOT EXISTS items (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
key VARCHAR(10) NOT NULL,
busy TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY (id, name),
UNIQUE KEY name_key_unique_key (name, key),
INDEX name_index (name)
) ENGINE=MyISAM
PARTITION BY LINEAR KEY(name)
PARTITIONS 25;
每天我收到许多csv文件,其中每一行由“name; key”对组成,所以我必须解析这些文件(为每行添加值created_at和updated_at)并将值插入到我的表中.在这一个中,“name”和“key”的组合必须是UNIQUE,所以我实现了如下插入过程:
CREATE TEMPORARY TABLE temp_items (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
key VARCHAR(10) NOT NULL,
busy TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY (id)
)
ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'file_to_process.csv'
INTO TABLE temp_items
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
(name, key, created_at, updated_at);
INSERT INTO items (name, key, busy, created_at, updated_at)
(
SELECT temp_items.name, temp_items.key, temp_items.busy, temp_items.created_at, temp_items.updated_at
FROM temp_items
)
ON DUPLICATE KEY UPDATE busy=1, updated_at=NOW();
DROP TEMPORARY TABLE temp_items;
刚刚显示的代码允许我达到我的目标,但是,为了完成执行,它需要大约48小时,这是一个问题.
我认为这种糟糕的性能是由于脚本必须检查一个非常大的表(200百万行),并且对于每个插入,该对“name; key”是唯一的.
如何提高脚本的性能?
感谢所有提前.