优化语句LOAD DATA LOCAL INFILE 'E:/zizhushangchuanshujuji/qianzhao/renyuanmingxi.txt' INTO TABLE hs.ls_zwj_qz_renyuanmingxi FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' ignore 1 lines;
时间: 2024-04-02 14:33:34 浏览: 14
可以尝试以下优化:
1. 增加批量插入的行数,例如改为一次性导入10000行数据:
LOAD DATA LOCAL INFILE 'E:/zizhushangchuanshujuji/qianzhao/renyuanmingxi.txt'
INTO TABLE hs.ls_zwj_qz_renyuanmingxi
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
SET col1 = 'value1', col2 = 'value2', ...
, coln = 'valuen'
, coln+1 = 'valuen+1'
, ...
, colm = 'valuem';
2. 禁用索引导入数据,在导入完成后再重新启用索引:
ALTER TABLE hs.ls_zwj_qz_renyuanmingxi
DISABLE KEYS;
LOAD DATA LOCAL INFILE 'E:/zizhushangchuanshujuji/qianzhao/renyuanmingxi.txt'
INTO TABLE hs.ls_zwj_qz_renyuanmingxi
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
ALTER TABLE hs.ls_zwj_qz_renyuanmingxi
ENABLE KEYS;
3. 使用LOAD DATA CONCURRENT插件,并将表的引擎设置为InnoDB:
ALTER TABLE hs.ls_zwj_qz_renyuanmingxi
ENGINE=InnoDB;
SET GLOBAL innodb_autoinc_lock_mode=2;
LOAD DATA CONCURRENT LOCAL INFILE 'E:/zizhushangchuanshujuji/qianzhao/renyuanmingxi.txt'
INTO TABLE hs.ls_zwj_qz_renyuanmingxi
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
SET GLOBAL innodb_autoinc_lock_mode=1;
4. 使用SSD硬盘可以提高读写速度,如果硬盘读写速度较慢的话,可以考虑升级硬盘。