mysql - 使用规范化快速上传 MYSQL 数据的最佳方法?
问题描述
这是我的情况。我得到了这样一个虚构的原始 csv 数据集,平均包含大约 100 万行。我会经常每两周得到这个数据集。
PROJECT, MD5SUM_VALUE, USAGE_NAME
A,132412341324asdf,Apple
B,13404892340asdf9,Banana
...
我有 mysql 表
PROJECT_TABLE (id, value),
MD5SUM_VALUE (id, value)
USAGE_NAME (id, name)
RECORD_TABLE (id, project_id, MD5SUM_id, USAGE_id)
我一直在使用脚本将 PROJECT 的值(使用 INSERT IGNORE)快速文件加载到 PROJECT_TABLE 中,并使用类似的策略为 MD5SUM_VALUE 和 USAGE_NAME 执行此操作。
按照今天的情况,我大约
- USAGE_NAME 表的 17,115,235 个唯一行条目
- MD5SUM_VALUE 表的 3,001,675 个唯一行条目
- PROJECT 表的 200 个唯一行条目
- RECORD_TABLE 表超过 59m 行
我上传 RECORD_TABLE 似乎很慢。我需要进行查询以识别 ID(project_id、MD5SUM_id、USAGE_id),并为每个原始 csv 文件执行 100 万次以上。
有没有更好的上传数据的方法?似乎没有办法快速上传这个数据部分?我应该采用不同的结构吗?
解决方案
我会创建一个临时表并使用它来生成/查找 id,然后插入到您的 record_table 中。假设您在 project_table、md5sum_table 和 usage_name 上有自动增量 id,并且在非 id 列上有唯一键:
create temporary table record_table_load (
project varchar(255),
project_id int,
md5sum_value varchar(32),
md5sum_id int,
usage_name varchar(255),
usage_id int
);
load data local infile 'foo.csv'
into table record_table_load
fields terminated by ',' enclosed by '"'
lines terminated by '\n'
ignore 1 lines
(project, md5sum_value, usage_name);
insert ignore into project_table (value) select distinct project from record_table_load;
insert ignore into md5sum_table (value) select distinct md5sum_value from record_table_load;
insert ignore into usage_name (name) select distinct usage_name from record_table_load;
update record_table_load join project_table on project_table.value=record_table_load.project set record_table_load.project_id=project_table.id;
update record_table_load join md5sum_table on md5sum_table.value=record_table_load.md5sum_value set record_table_load.md5sum_id=md5sum_table.id;
update record_table_load join usage_name on usage_name.name=record_table_load.usage_name set record_table_load.usage_id=usage_name.id;
insert into record_table (project_id, md5sum_id, usage_id) select project_id, md5sum_id, usage_id from record_table_load;
drop temporary table record_table_load;
如果您想避免使用insert ignore
或对这些值没有唯一约束,请进行查找,然后插入任何未找到的值,然后再进行一次查找。
推荐阅读
- python - 替换 Dataframe 每一行中的特定中文单词
- java - PostgreSQL 11 spring JPA 查询错误,java.sql.Date 对象和 TO_DATE 函数格式为 YYYY-DD-MM
- amazon-s3 - Flink s3 写入性能优化
- php - 我的 If 语句在中间件中不起作用
- mysql - 在 MySQL 中使用一个 Group By 查找最大日期和用户 ID
- javascript - jQuery Mask Plugin 两个数字的范围
- ssl - 如何使用 cloudflare oigin 证书构建正确的 CA 链?
- esri - Esri Map 4.15 getChildGraphics for geoJSONLayer - FeatureReduction“集群”应用
- c# - 跨线程的 Windows MessageBox 显示问题
- arrays - 将变量作为数组参数注入