当我们使用UNIQUE Key Column以避免重复时,LOAD DATA LOCAL INFILE的性能是否会下降?
当我使用Unique和没有Unique Key列测试.csv文件导入时,我有以下观察:
-
我看到1百万条记录存储/插入到表中而没有任何问题而没有使用唯一键约束,所以它需要重复 .
-
使用列上的唯一键约束来避免重复,我测试了70k记录,它在几分钟内存储得很好,因为50万条记录没有插入/存储到表中,它通过Web或MySQL运行数小时工作台 . 有时我看到以下问题:
错误代码:1205 . 超过锁定等待超时;尝试重启事务查询使用:
LOAD DATA LOCAL INFILE 'file.csv' ignore
INTO TABLE table1
CHARACTER SET UTF8mb4 FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';`
表结构:
CREATE TABLE table1(Idint(11)NOT NULL AUTO_INCREMENT,cUrlvarchar(255)DEFAULT NULL,SUrlvarchar(255)DEFAULT NULL,FirstNamevarchar(100)DEFAULT NULL,LastNamevarchar(100)DEFAULT NULL,EmailIdvarchar(150)NOT NULL,Filtervarchar( 55)DEFAULT NULL,Designationvarchar(255)DEFAULT NULL,Companyvarchar(255)DEFAULT NULL,WebSitevarchar(255)DEFAULT NULL,sizevarchar(25)DEFAULT NULL,Industryvarchar(255)DEFAULT NULL,Typevarchar(25)DEFAULT NULL,Foundedvarchar(5) )DEFAULT NULL,datevarchar(55)NOT NULL,PRIMARY KEY(Id),UNIQUE KEYEmailId(EmailId`))ENGINE = InnoDB AUTO_INCREMENT = 16812 DEFAULT CHARSET = UTF8mb4;
应用以下步骤:
set names utf8mb4;
set unique_checks = 0;
set foreign_key_checks = 0;
set sql_log_bin=0;
mysqld select version();
+------------+
| version() |
+------------+
| 5.7.18-log |
+------------+
1 row in set (0.00 sec)
SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 8388608 |
+---------------------------+
1 row in set (0.00 sec)
我修复了UTF8mb4问题但是使用了唯一键约束我无法在测试中导入50万的csv文件 . 我必须处理4000万条记录,但是我会做的 .
使用LOAD DATA LOCAL INFILE导入大型csv并使用Unique Key约束是否有效 .
建议和指导将是值得注意的 .
2 回答
是的,LOAD DATA批量创建非唯一索引以提高性能 . 更新表上每个CREATE / DELETE / UPDATE操作的索引会给这些操作增加额外的开销 . 这就是为什么当你有两个索引时你发现你的插入真的很慢 .
对于唯一索引,无法避免每行的索引更新,因为您插入的下一行可能是重复的 .
你仍然别无选择 . 通过关闭唯一索引然后删除重复数据来加载数据将会慢得多 . 但是你可以采取一些措施加快速度
减小磁盘大小
你有很多varchar列 . 我有一种感觉
founded
,date
和size
是整数列 . 如果是,则应将它们创建为整数列 . 它们占用的空间更少 . IOAD DATA中最大的瓶颈是IO操作 . 为什么要让事情变得更难 .emailId
是电子邮件地址还是数字身份证?如果它是一个数字 . 它应该被标记为整数放下主键!
使用自动递增主键时
emailId
是唯一的似乎是多余的 . 删除它并使emailId
成为主键 . 如果emailid是数字,那么更多 .规范化您的数据
您的基本问题似乎是您的数据未正常化 . 如果你将它标准化,那么CSV转储可能会小得多,而且磁盘上的数据也可能小得多,这会给你带来很大的提升 .
将
innodb_buffer_pool_size
设置为可用RAM的大约70% . 8G非常小 . 您是否从以前的默认版本升级?我很确定5.7.18有一个更大的默认值,但还不够大 .如果可行,在执行
LOAD
之前,按email
对csv文件进行排序 . 在* nix中,它是一个非常简单的sort
命令 . 这样可以避免跳过查找以验证每行的唯一性 .你还有其他索引吗?你需要
id
吗?如果两者都没有,那么让email
成为PRIMARY KEY
并摆脱id
.为各列使用合理的数据类型 . 否则,您将在以后遇到SQL问题 . (或者你可能需要将值作为字符串引入,然后清理它们?)
正常化(我同意@ e4c5) .