CodeGym /課程 /SQL SELF /批量資料載入優化

批量資料載入優化

SQL SELF

等級 24 , 課堂 3

開放

想像一下，你要載入一百萬筆資料。如果你慢慢來，server 會被拖很久，user 可能會覺得 database 變慢，更慘的是——你的咖啡可能都涼了還沒跑完。優化一下就能避免 server 過載、減少等待時間，也能降低載入時出錯的機率。

我們先從簡單的開始，然後再來點進階又有點小聰明的招式。

關閉 index 跟 trigger

index 跟 trigger 超好用，讓 database 變聰明又反應快。但你在批量載入資料時，它們會拖慢速度，因為 server 會想幫你每一筆都更新 index、執行 trigger。

想讓系統暫時輕鬆點，可以先把它們關掉。

範例：怎麼關掉 index 跟 trigger：

-- 關掉 table 的 trigger
ALTER TABLE students DISABLE TRIGGER ALL;

-- 載入資料
COPY students FROM '/path/to/students.csv' DELIMITER ',' CSV HEADER;

-- 再把 trigger 打開
ALTER TABLE students ENABLE TRIGGER ALL;

這怎麼運作？

我們用 DISABLE TRIGGER ALL 先暫時關掉所有 trigger。
資料載完後，再用 ENABLE TRIGGER ALL 把 trigger 打開。

常見錯誤：如果你忘了把 trigger 打開，有些自動化流程（像是預設欄位自動更新）可能會壞掉。所以記得都要還原回來——就像手機飛航模式要記得關掉一樣。

用 transaction

transaction 可以讓你一次載入所有資料，就像一個超大操作。如果有什麼出錯，你可以 rollback，database 就不會變成一堆亂七八糟的資料。

transaction 範例：

-- 開始 transaction
BEGIN;

-- 載入資料
COPY courses FROM '/path/to/courses.csv' DELIMITER ',' CSV HEADER;

-- 確認變更
COMMIT;

為什麼這樣比較快？

如果你沒用 transaction，server 每載一筆就要確認一次。有 transaction 的話，server 只在最後一次確認，超省時間。

關閉完整性檢查

如果你載入時不需要檢查 foreign key 或唯一性限制，可以先關掉。不然 database 會每一筆都檢查，會拖慢速度。

範例：怎麼關掉完整性檢查：

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 給排序或 hash 這種中間運算用。
maintenance_work_mem 影響 index 相關操作，像重建 index。

小提醒：加大記憶體要小心，尤其是資源有限的 server。

資料預處理

資料先處理好可以大幅縮短載入時間。比如你有重複的資料，先過濾掉，這樣 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;
-- 以此類推

背景載入

如果可以的話，可以用 background process 來載入資料，這樣主 database 不會被拖慢。

像 pg_cron 這種工具，可以排程自動執行。

範例：用 pg_cron 設定背景載入：

CREATE EXTENSION pg_cron;

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

每 5 分鐘就會自動把檔案資料載入 table。

這只是個範例，實際上不建議這樣用啦！只是想讓你知道 PostgreSQL 超彈性，你可以直接用 SQL script 很彈性地加資料。

小陷阱

有幾個點要注意：

如果你關掉 index 跟 trigger，記得一定要打開！不然載完資料後會出錯要修很久。
加大記憶體參數時要注意 server 資源：一個太貪心的 query 會把 RAM 吃光。
用 transaction 時要確定資料檔沒大問題。一個錯誤就會讓整個載入 rollback。

未來建議

現在你知道怎麼優化批量載入資料——從關掉 index 到用 transaction。這些技巧不只讓你載入更快，也省 server 資源、省心、省咖啡，user 也會很開心。