系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

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

时间:2020-06-22来源:www.pcxitongcheng.com作者:电脑系统城

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

 如上,插入时间由于系统的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 '\n' IGNORE 1 LINES \
                        (srcip, srcport, dstip, dstport, l7p, @dummy, cnt, @dummy, cnt_date)"
mysqlcur.execute(query)
mysqlconn.commit()

说明:

(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是带标题的,如下:

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

(5)@dummy ,通过占位符,跳过不需要的数据

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

(6)character set 指定字符集

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

(8)分隔符及换行符

 以“,“作为分隔符,以“\n"作为换行符: FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

其他性能优化相关(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 完了再改回去,如下:

    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”,笔者实际测试没什么用,只是导入数据更快,总的时间并没有提升。区别在于:一个是插入一条,创建一个索引;一个是全部导入完了后,再一次创建所有索引。

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载