MySQL LOAD DATA INFILE—从文件（csv、txt）批量导入数据[亲测有效]

小小码 • 2023-03-15 11:00 • 代码基础 • 阅读 153

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说MySQL LOAD DATA INFILE—从文件（csv、txt）批量导入数据[亲测有效],希望您对编程的造诣更进一步.

MySQL LOAD DATA INFILE—从文件（csv、txt）批量导入数据

MySQL LOAD DATA INFILE—从文件（csv、txt）批量导入数据[亲测有效]

最近做的项目，有个需求(从Elastic Search取数据，业务运算后），每次要向MySQL插入1300万条数据左右。最初用MySQL的executemany()一次插入10000条数据，统计的时间如下：

MySQL LOAD DATA INFILE—从文件（csv、txt）批量导入数据[亲测有效]

如上，插入时间由于系统的IO变化，会有波动，最快在4秒左右。

后改为”load data infile”大概，10万条数据平均1秒~1.5秒，实际的代码示例如下：

query = "LOAD DATA INFILE "/var/lib/mysql-files/es.csv" INTO TABLE g_visit_relation_asset_temp FIELDS TERMINATED BY "," LINES TERMINATED BY "
" IGNORE 1 LINES 
                        (srcip, srcport, dstip, dstport, l7p, @dummy, cnt, @dummy, cnt_date)"
mysqlcur.execute(query)
mysqlconn.commit()

代码100分

说明：

（1）MySQL需要开启对”load data inflie”的权限支持

mysqlcur.execute(“SET GLOBAL local_infile = 1”)

（2）
需要对mysql文件目录（笔者： “/var/lib/my-files/”）有管理员的权限（查看mysql路径，用“locate mysql”）

如果没有的话，可以指定本地路径，需要加上关键字”local”即：
LOAD DATA LOCAL

（3）Concurrency 支持

如果默认是 LOW_PRIORITY ，则LOAD DATA要等其它客户端读完了，才会开始写入。加上“Concurrency ”可以，在读的同时，同时支持写入，不过速度会稍微下降一点，笔者测试环境影响不大

（4）IGNORE 1 LINES （跳过第一行）

笔者通过python pandas to_csv()导出的csv是带标题的，如下：

MySQL LOAD DATA INFILE—从文件（csv、txt）批量导入数据[亲测有效]

不需要标题导入到数据库，就调过嘛

（5）@dummy ，通过占位符，跳过不需要的数据

导入到表的column顺序必须和文件保持一致，通过@dummy可以跳过不需要的column

（6）character set 指定字符集

对于汉字，你需要加上 character set utf8

（8）分隔符及换行符

代码100分 以“，“作为分隔符，以“
"作为换行符： FIELDS TERMINATED BY "," LINES TERMINATED BY "
"

其他性能优化相关（Only for MyISAM）：

通过设置隔离级别、去除索引检查、唯一性检查等提高速度（分session和global级别）提高写入速度，插入之前，设置如下配置：

    mysqlcur.execute("SET SESSION FOREIGN_KEY_CHECKS = 0")
    mysqlcur.execute("SET SESSION UNIQUE_CHECKS = 0")
    mysqlcur.execute("SET SESSION tx_isolation="READ-UNCOMMITTED"")
    mysqlcur.execute("SET SESSION sql_log_bin = 0")

Loda data infile 完了再改回去，如下：

代码100分    mysqlcur.execute("SET SESSION FOREIGN_KEY_CHECKS = 1")
    mysqlcur.execute("SET SESSION UNIQUE_CHECKS = 1")
    mysqlcur.execute("SET SESSION tx_isolation="REPEATABLE-READ"")
    mysqlcur.execute("SET SESSION sql_log_bin = 1")

“DISABLE KEYS” 然后 “ENABLE KEYS”，笔者实际测试没什么用，只是导入数据更快，总的时间并没有提升。区别在于：一个是插入一条，创建一个索引；一个是全部导入完了后，再一次创建所有索引。

引用：

如何导入5亿条数据到mysql — https://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql （自备梯子）
MySQL 官方文档说明 — https://dev.mysql.com/doc/refman/8.0/en/load-data.html

*******************************************************************************************

精力有限，想法太多，专注做好一件事就行

我只是一个程序猿。5年内把代码写好，技术博客字字推敲，坚持零拷贝和原创
写博客的意义在于打磨文笔，训练逻辑条理性，加深对知识的系统性理解；如果恰好又对别人有点帮助，那真是一件令人开心的事

*******************************************************************************************

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7736.html

MySQL LOAD DATA INFILE—从文件（csv、txt）批量导入数据[亲测有效]

相关推荐

发表回复