如何将数据仓库的清洗数据高效导入业务库_纯数据快速恢复-phpMyAdmin-PHP中文网

用 pg_dump + pg_restore 直接灌库最稳，导出需 -Fc --no-owner --no-privileges，恢复前 TRUNCATE 表，用 --disable-triggers 加速，事后 ANALYZE 并驱逐连接池。

用 `pg_dump` + `pg_restore` 直接灌库最稳，别碰 `COPY` 或应用层写入

清洗完的数据要进业务库，第一反应不是写脚本或走 orm，而是把数据当“快照”原样搬进去。postgresql 场景下，pg_dump 导出清洗后的 schema+data，再用 pg_restore 并行导入，比任何应用层批量插入都快且原子性强。它绕过了触发器、约束检查（可选）、连接池瓶颈和事务日志膨胀问题。

常见错误是导出时漏掉 --no-owner 和 --no-privileges，导致恢复时报 permission denied to set role；或者用 pg_dump --inserts 生成 SQL 脚本，结果单条 INSERT 执行慢到无法接受。

导出清洗库：用 pg_dump -Fc -n public --no-owner --no-privileges -f dump.db clean_db（-Fc 是关键，启用自定义格式才能并行恢复）
恢复前清空目标表但保留结构：在业务库执行 TRUNCATE TABLE orders, users RESTART IDENTITY CASCADE（注意外键顺序）
恢复时跳过约束加速：pg_restore -d biz_db --disable-triggers --single-transaction dump.db，完事后手动 SET CONSTRAINTS ALL IMMEDIATE

MySQL 用 `LOAD DATA INFILE` 前必须关掉唯一键和 autocommit

MySQL 业务库导入清洗数据，LOAD DATA INFILE 是唯一直接对标 PostgreSQL pg_restore 的高效路径。但它默认每行启一个事务、每行校验唯一索引，一千万行能跑两小时。核心是提前关闭干扰项，等数据落地后再补校验。

典型翻车点：没开 local_infile，报错 ERROR 1148 (42000): The used command is not allowed with this MySQL version；或者用绝对路径却没给 MySQL 进程读取权限，卡在 “file not found”。

服务端确认：检查 SHOW VARIABLES LIKE 'local_infile'，为 OFF 就得在启动参数加 --local-infile=1
客户端执行前：先 SET autocommit = 0，再 ALTER TABLE orders DISABLE KEYS（仅 MyISAM 有效），InnoDB 则用 SET FOREIGN_KEY_CHECKS = 0 和 SET UNIQUE_CHECKS = 0
导入命令：用 LOAD DATA LOCAL INFILE '/tmp/clean_orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '，末尾加 IGNORE 1 ROWS 跳过 header

跨库同步不能依赖 `SELECT ... INTO OUTFILE` 再 scp —— 时区和 null 处理会崩

有人想从数仓（比如 Redshift 或 StarRocks）导出 CSV，再 scp 到业务库机器上用 LOAD DATA 导入。这条路看着直，实际在时区、null 字符串、字段截断、换行符嵌套上全是坑。比如数仓里 created_at 是 TIMESTAMP WITH TIME ZONE，导成字符串后丢失时区信息，业务库按本地时区解析就偏 8 小时。

更隐蔽的问题是字段含或，INTO OUTFILE 默认不转义，CSV 解析直接错位；还有数仓里 NULL 输出成空字符串还是 N，业务库 LOAD 时认不出来。

替代方案：用数仓的 UNLOAD（Redshift）或 EXPORT（StarRocks）命令，显式指定 FORMAT AS PARQUET 或 CSV ESCAPED BY '\'
传输阶段别用 scp，改用 aws s3 cp 或 curl -T 传对象存储，避免中间挂载点权限/编码问题
业务库侧统一用程序解析 Parquet（如 Python pyarrow + psycopg2.execute_batch），可控 null 映射和 timestamp 时区转换

恢复后必做三件事：统计信息更新、索引重建、连接池驱逐

数据灌进去了不代表业务能立刻查得快。PostgreSQL 不会自动 ANALYZE 新表；MySQL 的 INFORMATION_SCHEMA.STATISTICS 可能还缓着旧分布；而连接池里的长连接，可能还拿着老查询计划，缓存了错误的执行路径。

最容易被跳过的其实是连接池驱逐。比如 HikariCP 默认不检测连接有效性，旧连接带着过期的 prepared statement plan 去查新数据，执行计划还是走 seq scan，QPS 直接腰斩。

PostgreSQL：运行 ANALYZE VERBOSE（加 VERBOSE 看哪些表被扫了），大表可指定列 ANALYZE orders (user_id, status)
MySQL：对 InnoDB 表执行 ANALYZE TABLE orders，别信 innodb_stats_auto_update 的默认值，它只在 10% 数据变更后才触发
所有业务服务：滚动重启，或调用连接池管理接口（如 HikariCP 的 evictConnection()）强制清理旧连接

真正卡点不在导入速度，而在导入后那五分钟——统计不准、索引失效、连接发懵，这时候查一条慢 SQL，整个业务就以为数据没导成功。