【MySQL】在linux服务器端插入大批量数据到mysql

1.查看local_infile是否开启

select @@local_infile;  -- 1表示已经开启
set global local_infile = 1; # 没开启就开启

---

2.确定文件路径以及存储格式

pwd # 获取文件所在路径。
# 查看文件内容的前几行，确保字段分隔符、行结束符符合要求。
head load_user_100w_sort.sql

---

3.使用load data local infile，因为insert插入性能低

-- 字段分隔符：使用 FIELDS TERMINATED BY 指定，如 ',' 表示字段以逗号分隔。-- 行结束符：使用 LINES TERMINATED BY 指定，如 '\n' 表示每行以换行符结束。  load data local infile 'home/itheima/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';select count(*) from tb_user;      

---

4.如果目标表字段不完全匹配，可在语句中指定目标列，如：

LOAD DATA LOCAL INFILE '/path/to/file.sql'    
INTO TABLE tb_user (id, name, age);    

---

5.优化批量插入性能

5.1 使用 LOAD DATA 前的准备

关闭自动提交：批量插入时关闭自动提交功能可减少磁盘 I/O。

SET autocommit = 0;  -- 关闭自动提交     

临时关闭唯一性校验：若目标表有唯一索引，临时关闭校验可提升性能。

SET unique_checks = 0;  -- 关闭唯一性检查     

关闭外键检查：外键检查会增加校验开销。

SET foreign_key_checks = 0; -- 关闭外键检查

---

5.2 插入完成后的操作

插入完成后，恢复默认设置：

SET autocommit = 1;     
SET unique_checks = 1;     
SET foreign_key_checks = 1;     

---

插入大批量数据时,使用事务控制数据一致性，防止部分数据插入成功但整体失败的情况

START TRANSACTION;   LOAD DATA LOCAL INFILE '/home/itheima/load_user_100w_sort.sql'INTO TABLE tb_user   
FIELDS TERMINATED BY ','    
LINES TERMINATED  BY '\n';   COMMIT; -- 提交事务   

---

使用分区表处理海量数据

当插入数据量非常大（如千万级以上）时，可以考虑将目标表设计为分区表，以减少单个表的数据量，提高查询性能：

CREATE TABLE tb_user (id INT,name VARCHAR(50),age INT,...PRIMARY KEY (id, age)
)
PARTITION BY RANGE (age) (PARTITION p1 VALUES LESS THAN (20),PARTITION p2 VALUES LESS THAN (40),PARTITION p3 VALUES LESS THAN (60),PARTITION p4 VALUES LESS THAN MAXVALUE
);