当前位置：首页 > news >正文

MySQL 中快速插入大量数据

news 2025/6/29 20:55:45

在 MySQL 中快速插入大量数据（例如 20 万条记录）可以通过多种方法实现。以下是一些优化技巧和步骤，可以帮助你高效地插入大量数据：

1. 禁用索引和约束（如果可能）

在插入大量数据之前，禁用索引和外键约束可以显著提高插入速度。在插入完成后，再重新启用它们并重建索引。

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

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

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

-- 禁用索引更新（对于MyISAM）
-- ALTER TABLE your_table DISABLE KEYS;

2. 使用批量插入

批量插入可以显著减少插入操作的开销。例如，使用 INSERT INTO ... VALUES (...), (...), ... 语法。

sql
INSERT INTO your_table (column1, column2, column3)
VALUES
(value1_1, value1_2, value1_3),
(value2_1, value2_2, value2_3),
-- 继续添加更多行，直到达到合理的批量大小（如几千行）
(valueN_1, valueN_2, valueN_3);

3. 使用事务

在事务中执行批量插入可以减少每次提交的开销。

sql
START TRANSACTION;

-- 批量插入语句
INSERT INTO your_table (column1, column2, column3)
VALUES
(value1_1, value1_2, value1_3),
-- 继续添加更多行
(valueN_1, valueN_2, valueN_3);

COMMIT;

4. 禁用二进制日志（如果不需要复制或恢复点）

如果你的数据库不需要复制或增量备份，可以临时禁用二进制日志。

sql
SET sql_log_bin = 0;
注意：禁用二进制日志后，数据库将无法进行增量备份或复制操作，请谨慎使用。

5. 调整 MySQL 配置

根据需要调整 MySQL 配置，以提高插入性能。例如，增加 innodb_buffer_pool_size、innodb_log_file_size 和 innodb_flush_log_at_trx_commit。

6. 使用 LOAD DATA INFILE

对于非常大的数据集，使用 LOAD DATA INFILE 可以比 INSERT 语句快得多。

sql
LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

7. 重新启用索引和约束

在插入完成后，重新启用之前禁用的索引和外键约束。

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

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

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

-- 启用索引更新（对于MyISAM）
-- ALTER TABLE your_table ENABLE KEYS;
示例脚本
以下是一个综合示例脚本，用于快速插入 20 万条数据：

sql
-- 禁用外键约束、唯一性检查和自动提交
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;

-- 开始事务
START TRANSACTION;

-- 批量插入（示例，这里只展示少量数据，实际应扩展到 20 万条）
INSERT INTO your_table (column1, column2, column3)
VALUES
(value1_1, value1_2, value1_3),
(value2_1, value2_2, value2_3),
-- 重复插入直到达到 20 万条数据
-- ...
(value200000_1, value200000_2, value200000_3);

-- 提交事务
COMMIT;

-- 启用外键约束、唯一性检查和自动提交
SET foreign_key_checks = 1;
SET unique_checks = 1;
SET autocommit = 1;
注意事项
备份数据：在进行批量插入之前，务必备份你的数据，以防数据丢失或损坏。
测试环境：先在测试环境中验证这些操作，以确保它们不会影响生产环境的性能和稳定性。
监控性能：监控数据库的性能，确保批量插入操作不会对数据库服务器造成过大的负载。
通过这些优化技巧，你应该能够高效地插入 20 万条数据到 MySQL 数据库中。