批量数据导入优化

想象一下，你需要导入一百万行数据。如果你慢慢来，服务器会被拖慢很久，用户可能会觉得数据库变卡，更糟糕的是——你的咖啡可能还没喝完就已经凉了。优化能帮你避免服务器过载，减少等待时间，还能降低导入出错的概率。

我们先从简单的步骤开始，然后再聊点更高级、更骚的技巧。

禁用索引和触发器

索引和触发器是让数据库变聪明、反应快的好东西。但在批量导入数据时，它们会让过程变慢，因为服务器会试图为你导入的每一行都更新索引、执行触发器。

想让系统轻松点，可以暂时把它们关掉。

禁用索引和触发器的例子：

-- 禁用表的触发器
ALTER TABLE students DISABLE TRIGGER ALL;

-- 导入数据
COPY students FROM '/path/to/students.csv' DELIMITER ',' CSV HEADER;

-- 重新启用触发器
ALTER TABLE students ENABLE TRIGGER ALL;

怎么回事？

1. 我们用DISABLE TRIGGER ALL临时禁用所有触发器。
2. 数据导入完后，用ENABLE TRIGGER ALL把触发器打开。

常见错误： 如果你忘了把触发器打开，有些自动化流程（比如默认字段的更新）可能会出问题。所以别忘了恢复——就像你手机开了飞行模式，记得关掉一样。

使用事务

事务让你可以把所有数据一次性导入，就像做一件大事。如果中间出错，你可以回滚，数据库不会变成一堆乱七八糟的数据。

事务的例子：

-- 开始事务
BEGIN;

-- 导入数据
COPY courses FROM '/path/to/courses.csv' DELIMITER ',' CSV HEADER;

-- 提交更改
COMMIT;

为啥这样更快？

如果你不加事务，服务器每导入一行就要确认一次。用事务的话，服务器只在最后确认一次，省了很多时间。

禁用完整性检查

如果你导入时不需要检查外键或唯一约束，可以把它们关掉。不然数据库会每行都检查，速度会慢很多。

禁用完整性检查的例子：

SET session_replication_role = 'replica';

-- 导入数据
COPY enrollments FROM '/path/to/enrollments.csv' DELIMITER ',' CSV HEADER;

SET session_replication_role = 'origin';

session_replication_role = 'replica'会禁用数据完整性检查（比如唯一性和FOREIGN KEY约束）。

增加执行内存

调整PostgreSQL的内存设置能提升导入性能。关键参数是work_mem和maintenance_work_mem。

增加内存的例子：

-- 增加内存
SET work_mem = '64MB';
SET maintenance_work_mem = '256MB';

-- 导入数据
COPY teachers FROM '/path/to/teachers.csv' DELIMITER ',' CSV HEADER;

有啥用？

• work_mem用于中间操作，比如排序或哈希。
• maintenance_work_mem影响和索引相关的操作，比如重建索引。

小贴士： 增加内存要小心，尤其是在资源有限的机器上。

导入前准备数据

提前准备数据能大大减少导入时间。比如有重复行，提前过滤掉，PostgreSQL就不用浪费时间处理没用的数据。

清理数据的例子：

如果你有个带重复行的文件，可以用Python去重。

import pandas as pd

# 读取CSV文件
data = pd.read_csv('students.csv')

# 删除重复行
data = data.drop_duplicates()

# 保存干净的CSV
data.to_csv('students_clean.csv', index=False)

数据分区

如果你的文件特别大，把它拆成几个小文件。这样PostgreSQL处理起来更高效。

例子：

用Linux把large_data.csv按1000行拆分：

split -l 1000 large_data.csv chunk_

然后分别导入：

COPY students FROM 'chunk_aa' DELIMITER ',' CSV HEADER;
COPY students FROM 'chunk_ab' DELIMITER ',' CSV HEADER;
-- 以此类推

后台导入

如果可以的话，可以用后台进程导入数据，这样不会拖慢主数据库。

像pg_cron这样的工具可以定时跑任务。

例子：用pg_cron设置后台导入：

CREATE EXTENSION pg_cron;

SELECT cron.schedule('*/5 * * * *', $$COPY students FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER$$);

每5分钟就会把文件里的数据导入表里。

这只是个例子，实际别这么干！我只是想让你知道PostgreSQL真的很灵活，你可以直接用SQL脚本超灵活地加数据。

坑点提醒

有些事要注意：

• 禁用索引和触发器后，记得打开！忘了就得修bug。
• 调大内存参数时，注意服务器资源：一个贪心的查询能把内存全吃光。
• 用事务时，确保数据文件没大问题。一行错，全盘回滚。

未来建议

现在你知道怎么优化批量数据导入了——从禁用索引到用事务。这些技能能帮你更快导入数据，还能省服务器资源、省心、省咖啡，也让用户更开心。

下次要导入几G的大文件，你就不慌啦！