MySQL 初始数据导入

初始数据导入方法比较

1. LOAD DATA INFILE

语法

LOAD DATA [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE table_name
[CHARACTER SET charset_name]
[FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
[IGNORE number {LINES}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]

示例

-- 从CSV文件导入数据
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, email, created_at);

优缺点

优点	缺点
速度最快	需要文件系统访问权限
支持多种文件格式	语法相对复杂
支持数据转换	需要了解文件格式细节
支持忽略或替换现有记录	可能会锁表

适用场景

• 大数据量的初始数据导入
• 从结构化文件导入数据
• 数据迁移和数据仓库初始加载

2. mysqldump 导入

示例

# 导出数据
export DATA_DIR="/path/to/export"
mysqldump -u root -p --databases mydb > ${DATA_DIR}/mydb_dump.sql

# 导入数据
mysql -u root -p mydb < ${DATA_DIR}/mydb_dump.sql

优缺点

优点	缺点
简单易用	导入速度较慢
生成标准SQL文件	生成的文件较大
支持选择性导出	会产生大量INSERT语句
支持压缩导出	对大表导入效率低

适用场景

• 小到中等数据量的初始数据导入
• 需要保持数据完整性和一致性
• 需要导入特定表或数据

3. mysqlimport

示例

# 使用 mysqlimport 导入数据
mysqlimport -u root -p --local --fields-terminated-by=, --lines-terminated-by=\n --ignore-lines=1 mydb /tmp/users.csv

优缺点

优点	缺点
基于 LOAD DATA INFILE，速度快	命令行参数较多
支持批量导入多个文件	需要了解文件格式细节
支持多种文件格式	功能相对有限

适用场景

• 从多个文件导入数据
• 需要脚本化导入过程
• 大数据量的初始数据导入

4. 客户端工具导入

优缺点

优点	缺点
图形化界面，易于使用	导入速度较慢
支持多种文件格式	不适合大数据量导入
提供数据预览和验证	需要安装额外软件
支持映射和转换	可能会有功能限制

适用场景

• 小数据量的初始数据导入
• 需要可视化操作
• 测试环境的数据初始化

5. 自定义脚本导入

示例（Python）

import mysql.connector
import csv

# 连接到 MySQL 数据库
cnx = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)
cursor = cnx.cursor()

# 准备 INSERT 语句
insert_stmt = "INSERT INTO users (name, email, created_at) VALUES (%s, %s, %s)"

# 打开 CSV 文件并导入数据
batch_size = 1000
batch = []

with open('/tmp/users.csv', 'r') as f:
    reader = csv.reader(f)
    next(reader)  # 跳过标题行
    for row in reader:
        batch.append((row[0], row[1], row[2]))
        if len(batch) >= batch_size:
            cursor.executemany(insert_stmt, batch)
            cnx.commit()
            batch = []
    
    # 插入剩余数据
    if batch:
        cursor.executemany(insert_stmt, batch)
        cnx.commit()

# 关闭连接
cursor.close()
cnx.close()

优缺点

优点	缺点
灵活性高，可以处理复杂逻辑	开发和维护成本高
支持数据转换和验证	导入速度取决于实现
可以处理多种数据源	需要编程知识
支持错误处理和重试	可能会有性能瓶颈

适用场景

• 复杂数据导入需求
• 需要数据转换和验证
• 从非结构化或半结构化数据导入
• 需要集成其他系统

初始数据导入的优化策略

1. 预导入准备

优化表结构

• 合理设计表结构，避免过多的列和索引
• 选择合适的数据类型，减少存储空间
• 考虑使用分区表处理大量数据
• 移除不必要的约束和触发器

调整数据库参数

参数名称	说明	建议值
max_allowed_packet	允许的最大数据包大小	16M-128M
bulk_insert_buffer_size	批量插入缓冲区大小	16M-128M
innodb_buffer_pool_size	InnoDB 缓冲池大小	物理内存的 50-70%
innodb_log_file_size	InnoDB 重做日志文件大小	256M-2G
innodb_log_buffer_size	InnoDB 日志缓冲区大小	16M-64M
innodb_autoinc_lock_mode	自增锁模式	2（交错模式，适合批量插入）
innodb_flush_log_at_trx_commit	日志刷新策略	0 或 2（提高写入性能，牺牲部分安全性）
sync_binlog	二进制日志同步策略	0（提高写入性能，牺牲部分安全性）

准备数据文件

• 确保数据文件格式正确
• 预处理数据，删除不必要的行和列
• 转换数据为适合导入的格式
• 压缩数据文件，减少传输时间

2. 导入过程优化

禁用索引和约束

• 导入前禁用非主键索引，导入后再重建
• 禁用外键约束，导入后再启用
• 禁用触发器，导入后再启用

示例

-- 禁用外键约束
SET foreign_key_checks = 0;

-- 禁用唯一索引检查
SET unique_checks = 0;

-- 禁用自动提交
SET autocommit = 0;

-- 执行导入操作
LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users ...;

-- 提交事务
COMMIT;

-- 重建索引
ALTER TABLE users ENGINE=InnoDB;

-- 启用外键约束
SET foreign_key_checks = 1;

-- 启用唯一索引检查
SET unique_checks = 1;

-- 启用自动提交
SET autocommit = 1;

分批次导入

• 将大文件分成多个小文件，分批次导入
• 每批次导入后提交事务
• 适当添加延迟，避免系统过载

并行导入

• 对于分区表，可以并行导入不同分区的数据
• 使用多个连接同时导入不同的表
• 注意避免资源冲突和锁竞争

调整事务大小

• 将导入操作放在一个事务中，但事务大小不宜过大
• 对于非常大的数据导入，考虑使用自动提交模式
• 监控事务日志大小，避免事务过大导致日志满

3. 导入后优化

重建索引

• 导入完成后重建索引，确保索引最优
• 优化表结构，回收碎片

示例

-- 重建索引
ALTER TABLE users ENGINE=InnoDB;

-- 或使用 OPTIMIZE TABLE
OPTIMIZE TABLE users;

统计信息更新

• 更新表的统计信息，确保查询优化器生成正确的执行计划

示例

-- 更新统计信息
ANALYZE TABLE users;

数据验证

• 验证导入数据的完整性和一致性
• 检查数据行数和关键指标
• 执行基本的查询测试

性能调优

• 调整数据库参数，恢复到正常运行时的设置
• 监控数据库性能，调整相关参数
• 优化查询和索引

初始数据导入的最佳实践

1. 选择合适的导入方法

根据数据量、数据格式、系统要求等因素，选择最合适的导入方法：

• 大数据量（GB级别及以上）：使用 LOAD DATA INFILE 或 mysqlimport
• 中等数据量（MB级别）：使用 mysqldump 或自定义脚本
• 小数据量（KB级别）：使用客户端工具或自定义脚本

2. 测试导入过程

• 在测试环境中测试导入过程，验证导入方法的可行性和性能
• 测试不同的导入参数和优化策略，找到最佳配置
• 测试导入后的数据库性能和可用性
• 测试数据完整性和一致性

3. 监控导入过程

• 监控导入过程的进度和状态
• 监控数据库资源使用情况（CPU、内存、I/O等）
• 监控锁等待和死锁情况
• 设置导入时间过长的告警

4. 确保数据安全性

• 验证数据来源的合法性和安全性
• 加密传输和存储敏感数据
• 限制导入操作的权限
• 记录导入操作日志
• 导入完成后检查数据安全性

5. 制定回滚计划

• 在执行大规模导入前，备份相关数据
• 制定详细的回滚计划，应对意外情况
• 测试回滚流程，确保数据可以恢复
• 准备必要的回滚工具和资源

不同数据格式的导入方法

1. CSV 格式

CSV（逗号分隔值）是最常用的数据导入格式，MySQL 提供了良好的支持：

示例

LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, email, created_at);

2. TSV 格式

TSV（制表符分隔值）也是常用的数据导入格式：

示例

LOAD DATA INFILE '/tmp/users.tsv'
INTO TABLE users
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, email, created_at);

3. JSON 格式

对于 JSON 格式的数据，可以使用 LOAD DATA INFILE 结合 JSON 函数进行导入：

示例

-- 创建临时表存储 JSON 数据
CREATE TABLE tmp_json_data (data JSON);

-- 导入 JSON 数据
LOAD DATA INFILE '/tmp/users.json'
INTO TABLE tmp_json_data
LINES TERMINATED BY '\n';

-- 将 JSON 数据插入到目标表
INSERT INTO users (id, name, email, created_at)
SELECT 
  JSON_UNQUOTE(JSON_EXTRACT(data, '$.id')) AS id,
  JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name,
  JSON_UNQUOTE(JSON_EXTRACT(data, '$.email')) AS email,
  JSON_UNQUOTE(JSON_EXTRACT(data, '$.created_at')) AS created_at
FROM tmp_json_data;

-- 删除临时表
DROP TABLE tmp_json_data;

4. XML 格式

对于 XML 格式的数据，可以使用自定义脚本或外部工具进行转换后导入：

示例（Python）

import mysql.connector
import xml.etree.ElementTree as ET

# 连接到 MySQL 数据库
cnx = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)
cursor = cnx.cursor()

# 解析 XML 文件
tree = ET.parse('/tmp/users.xml')
root = tree.getroot()

# 准备 INSERT 语句
insert_stmt = "INSERT INTO users (id, name, email, created_at) VALUES (%s, %s, %s, %s)"

# 提取数据并插入到数据库
batch = []
batch_size = 1000

for user in root.findall('user'):
    id = user.find('id').text
    name = user.find('name').text
    email = user.find('email').text
    created_at = user.find('created_at').text
    
    batch.append((id, name, email, created_at))
    
    if len(batch) >= batch_size:
        cursor.executemany(insert_stmt, batch)
        cnx.commit()
        batch = []

# 插入剩余数据
if batch:
    cursor.executemany(insert_stmt, batch)
    cnx.commit()

# 关闭连接
cursor.close()
cnx.close()

常见问题（FAQ）

Q1: 如何提高初始数据导入的速度？

A1: 提高初始数据导入速度的方法：

1. 使用 LOAD DATA INFILE 或 mysqlimport，这是最快的数据导入方法
2. 禁用索引和约束，导入后再重建
3. 调整数据库参数，如增加 buffer 大小、调整日志刷新策略等
4. 分批次导入，避免长时间锁表
5. 并行导入，利用多核 CPU 和多线程
6. 优化数据文件，减少不必要的数据和格式转换

Q2: 如何确保初始数据导入的数据完整性？

A2: 确保初始数据导入数据完整性的方法：

1. 导入前验证数据格式和内容
2. 使用事务确保数据一致性
3. 导入后验证数据行数和关键指标
4. 执行数据完整性检查（如 CHECK TABLE）
5. 验证业务逻辑的正确性
6. 记录导入操作日志，便于追溯

Q3: 如何处理初始数据导入过程中的错误？

A3: 处理初始数据导入过程中错误的方法：

1. 实现完善的错误处理机制
2. 使用事务回滚确保数据一致性
3. 记录错误信息，便于调试和分析
4. 实现重试机制，处理临时性错误
5. 分批次导入，减少错误影响范围
6. 准备回滚计划，应对严重错误

Q4: 初始数据导入会影响现有数据库的性能吗？

A4: 初始数据导入可能会影响现有数据库的性能，尤其是在以下情况：

• 导入操作占用大量 CPU、内存和 I/O 资源
• 导入过程中锁表，影响其他会话的访问
• 事务日志增长过快，导致磁盘空间不足
• 索引更新导致的性能下降

为了减少影响，建议：

• 在低峰期执行初始数据导入
• 分批次导入，避免长时间锁表
• 调整数据库参数，优化导入性能
• 使用读写分离架构，避免影响主库性能

Q5: 如何导入非常大的数据集（TB级别）？

A5: 导入非常大的数据集的方法：

1. 使用 LOAD DATA INFILE 或 mysqlimport，这是最快的数据导入方法
2. 将数据分成多个文件，并行导入
3. 使用分区表，按分区并行导入数据
4. 调整数据库参数，优化导入性能
5. 考虑使用外部工具，如 Apache Spark、Hadoop 等进行预处理
6. 在专用的导入服务器上进行导入，完成后再迁移到生产环境

Q6: 如何从其他数据库导入初始数据到 MySQL？

A6: 从其他数据库导入初始数据到 MySQL 的方法：

1. 使用数据库自带的导出工具，将数据导出为 CSV、JSON 等格式
2. 使用 ETL 工具（如 Pentaho Data Integration、Talend 等）进行数据迁移
3. 使用自定义脚本，连接到源数据库读取数据，然后插入到 MySQL
4. 使用云服务提供的数据迁移工具（如 AWS DMS、阿里云 DTS 等）
5. 对于同构数据库，可以考虑直接迁移数据文件

Q7: 初始数据导入后需要做哪些优化？

A7: 初始数据导入后需要做的优化：

1. 重建索引，确保索引最优
2. 更新表的统计信息，确保查询优化器生成正确的执行计划
3. 调整数据库参数，恢复到正常运行时的设置
4. 优化表结构，回收碎片
5. 监控数据库性能，调整相关参数
6. 进行性能测试，验证优化效果

Q8: 如何自动化初始数据导入过程？

A8: 自动化初始数据导入过程的方法：

1. 编写脚本，自动执行导入操作
2. 使用定时任务或调度系统（如 Cron、Airflow 等）调度导入任务
3. 实现监控和告警机制，自动处理导入过程中的问题
4. 使用配置管理工具（如 Ansible、Puppet 等）自动化导入配置
5. 集成到 CI/CD 流程中，实现自动化测试和部署