百万级excel数据按顺序导入MySql - LOAD DATA INFILE

原理

LOAD DATA INFILE之所以能在百万级数据导入时保持高效，其核心原理，可以用“吃自助餐与点菜的差别”来理解。LOAD DATA INFILE是一条被精心优化的、流式读取并批量写入数据的专用管道；而单条INSERT就像一次次点餐，每次都需要完整的服务流程。

📊 核心定位：LOAD DATA INFILEvs.INSERT

为了直观理解两者的性能差距，我们先看一组在AWS t3.xlarge实例上导入100万条数据的实测对比：

可以看到，LOAD DATA INFILE不仅速度最快，对CPU和磁盘IO的利用效率也最高。

⚙️ 内部工作流：两大阶段，高度协同

LOAD DATA INFILE的执行过程主要分为两大阶段：数据流阶段和存储阶段。

⚡ 核心引擎机制：三层高效设计的“三重奏”

1. 第一层：极简的数据流阶段 - 化“多次点餐”为“自助取餐”

这个阶段的优化，是效率提升的根本原因，主要体现在以下三点：

• 绕过SQL解析开销：LOAD DATA INFILE是一条命令，数据作为文件参数传入。MySQL 只需解析这一条命令，然后就可以埋头读取文件，直接处理数据。这完全避免了为每一行数据都重复执行SQL解析、权限检查等开销巨大的操作。
• 最小化网络与日志开销：数据直接在服务器端文件中读取，极大减少了客户端与服务器之间的网络通信。同时，它能将多条数据行的变更合并到一次日志记录中，减少了磁盘I/O的压力。
• “自助餐”与“点菜”的类比：上述几点可以用“吃自助餐”与“点菜”的类比来总结：
  • 单条 INSERT (点菜)：每次执行，服务器都需要“接待点餐-下单-做菜-上菜”，并记录每次操作，流程繁琐重复。
  • LOAD DATA INFILE (自助餐)：你一次性拿好盘子（文件），厨师（MySQL）直接将大份菜品（数据）批量供应，干净利落。

2. 第二层：高效的存储阶段 - 让入库速度“飞起”

数据解析完成后，如何将它们高效地写入表中，是LOAD DATA INFILE的另一个关键优势。

• 批量写入与主键排序：解析后的数据并非逐行写入，而是先在内存中对主键进行排序，再以批量的形式写入。这样做可以大大减少在B+树索引中查找插入位置时的页分裂次数，从而显著提升写入速度。
• 行缓冲合并与页预分配：MySQL 会通过行缓冲合并机制，将多条数据整合后一次性写入，减少系统调用。同时，它会采用页预分配策略，提前在磁盘上为即将写入的数据预留好空间，避免了频繁申请磁盘空间带来的性能损耗。
• 批量缓存机制：数据在内存中会先进行批量缓存，默认以128KB为单位进行整理、排序和压缩，然后再统一写入磁盘的缓冲池，进一步提升了效率。

3. 第三层：可中断的流水线 vs. 完整的事务框

此处将LOAD DATA INFILE比作一条高效的流水线，相比使用完整事务框逐条提交，优势更为明显：

• LOAD DATA INFILE：可以看作一条处理数据的“流水线”，它支持在一个大事务中持续地处理数据，也可以配置为按固定大小（如128KB或5000行）的块进行提交，兼顾效率与可控性。
• INSERT：单条INSERT语句包裹在一个完整的事务中，每一条都独立提交，会产生大量的磁盘I/O和锁竞争。START TRANSACTION+ 多条INSERT+COMMIT会形成一个巨大的事务框，若中间出错，可能导致整个框内数据回滚，风险较高。

🚀 进阶优化与注意事项

• 启用单表并发导入：虽然LOAD DATA INFILE本身单线程处理单个文件，但现代MySQL（8.0+）支持在LOAD语句中指定PARALLEL N选项，实现单文件多线程导入，可极大缩短总时长。
• 使用mysqlsh并行导入：MySQL Shell的util.importTable功能是一种更高级的选择，它能自动将大文件分块，并使用多线程并行导入，是处理TB级数据的有效方案。
• LOCAL关键词的影响：使用LOCAL关键词时，数据需先从客户端上传至服务器临时目录后再导入，这会增加额外开销，因此导入速度会比直接使用服务器本地文件稍慢。

补充：LOAD DATA INFILE语句会以紧凑的打包格式写入Binlog，并非明文SQL，这一特殊设计保证了它在主从复制场景中的一致性和高效性。

💎 总结：各取所长的最佳实践

LOAD DATA INFILE通过简化网络通信、消除逐条SQL解析、并对写入流程进行深度优化，实现了业界领先的大数据导入性能，实测速度可达批量INSERT的约8倍和单条INSERT的60倍以上。

在实际应用中，可以这样权衡选择：

• 数据源自文件-> 首选LOAD DATA INFILE，开启并行选项，发挥极致性能。
• 数据源自应用-> 使用INSERT的批量语法，每批500-1000行，并在单一事务中提交，达到性能和便利性的平衡。

示例Demo

LOAD DATA INFILE的列映射主要在语句末尾的括号中指定。如下为可以直接运行的完整示例。

📊 Demo 准备工作：表与 CSV 数据

假设表employee与 CSV 文件emp_data.csv内容如下：

1. 目标表结构 (employee)：包含 4 个字段

   CREATETABLEemployee(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100),salaryDECIMAL(10,2),hire_dateDATE);

2. CSV 文件内容 (emp_data.csv)：

   # 员工姓名, 当前月薪(元), 入职年份, 入职月份 张三, 8000, 2020, 5 李四, 9500, 2019, 8 王五, 11000, 2021, 3

   可以看到，CSV 第 3、4 列是年份和月份，需要组合成DATE类型，且它与目标表的列顺序不完全一致。

🚀 核心映射与转换技巧

有了上面的表和数据，我们来看看几种最常见的映射场景怎么写。

1. 基础映射：改变列的读取顺序

• 需求：CSV 的顺序是(name, salary, year, month)，但只想按(name, salary, hire_date)的顺序插入到employee表。
• 方法：在LOAD DATA语句的末尾，用小括号()指定要从文件依次读取哪些列，并映射到表的哪些字段。
• 示例命令：

  LOADDATALOCALINFILE'/path/to/emp_data.csv'INTOTABLEemployeeCHARACTERSETutf8mb4FIELDSTERMINATEDBY','-- 字段分隔符ENCLOSEDBY'"'-- 字段引用符LINESTERMINATEDBY'\n'-- 行分隔符IGNORE1LINES-- 忽略CSV文件的第1行(name,salary,@year,@month)-- 按顺序读取文件中的4个字段SEThire_date=STR_TO_DATE(CONCAT(@year,'-',@month,'-01'),'%Y-%m-%d');

• 注释：(列名/变量){.sql}部分的顺序与文件中的列顺序严格对应。SET语句可以负责转换那些不能直接匹配的列。

2. 中间转换：组合与格式化数据

• 需求：从year和month两列数据，拼接成一个标准的DATE类型存入hire_date字段。
• 方法：通过SET hire_date = ...，使用 MySQL 函数动态生成最终写入的值。
• 示例命令：

  LOADDATALOCALINFILE'/path/to/emp_data.csv'INTOTABLEemployeeCHARACTERSETutf8mb4FIELDSTERMINATEDBY','ENCLOSEDBY'"'LINESTERMINATEDBY'\n'IGNORE1LINES(name,salary,@year,@month)-- 先把年月读入用户变量@year和@monthSEThire_date=STR_TO_DATE(CONCAT(@year,'-',@month,'-01'),'%Y-%m-%d');

• 注释：用户变量（以@开头）可以暂存原始数据，并通过 MySQL 内置函数进行任意处理和转换。

3. 跳过/忽略列：丢弃不需要的文件数据

• 需求：CSV 文件包含 11 列数据，只取其中第 1, 2, 11 列插入到表的 3 个字段中，其余全部丢弃。
• 方法：在列列表中，用@dummy作为占位符接收不需要的列，随后不再使用它。
• 示例命令：

  LOADDATALOCALINFILE'/path/to/wide_data.csv'INTOTABLEemployeeFIELDSTERMINATEDBY','(column1,column2,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,column11);

• 注释：@dummy是用户变量，读取了但不做任何事，列就会被丢弃。

4. 全自动生成：利用数据库的自增ID

• 需求：CSV 文件不提供id，需要数据库自动生成。
• 方法：AUTO_INCREMENT列的处理很简单，只需要在列列表中直接跳过它即可。
• 示例命令：

  LOADDATALOCALINFILE'/path/to/emp_data.csv'INTOTABLEemployeeFIELDSTERMINATEDBY','(name,salary,hire_date);-- 直接忽略id列，它会自增

• 注释：对于id这种自增列，导入时直接省略，数据库会自动填充下一个序号。

5. 覆盖默认值：为缺失的数据统一补全

• 需求：CSV 文件字段比表少，没有提供salary，需要统一使用默认值0填充。
• 方法：不读取该列，直接通过SET语句为其赋予一个静态值。
• 示例命令：

  LOADDATALOCALINFILE'/path/to/no_salary_data.csv'INTOTABLEemployeeFIELDSTERMINATEDBY','(name,hire_date)-- 文件只提供了这两列SETsalary=0;-- 其他列在SET子句中补齐

• 注释：用SET子句不仅能做转换，也能为表里其他未在文件中的列赋予你指定的值。

⚙️ 常见问题与排查建议

• secure_file_priv报错：如果不用LOCAL关键字报错，检查 MySQL 变量secure_file_priv的路径限制，或者优先使用LOCAL模式。
• 导入了NULL/全0：大概率是hire_date的拼接逻辑错了，建议用SELECT STR_TO_DATE(...)先测试是否正确。
• 中文字符乱码：在LOAD DATA中显式指定CHARACTER SET utf8mb4。
• 表锁导致业务阻塞：对大表导入，可改用pt-online-schema-change等工具，避免长时间锁表。

LOAD DATA INFILE的列映射本质上是在构建一条处理数据的流水线。你可以先在小数据集上测试列匹配逻辑，确认无误后再导入全量数据，这样会更稳妥。