撰寫資料庫相關程式的心得

我是用 MySQL + Django，處理的資料量有小有大。資料量大的情況下，通常有上萬筆，甚至會到上億筆。相關心得大概分成四類，依實務經驗記錄一下心得。

是否應該使用 ORM？

我是使用 Django ORM，以下指的 ORM 問題可能不適用全部 ORM framework，但我猜大部份應該是半斤八兩。

剛開始不熟 SQL 時，很喜歡用 ORM，ORM 有些學習門檻，不過習慣後用起來相當順，也容易閱讀程式。但是在好寫好讀的背後，卻犠牲掉極大的效率。原因有幾點：

• 需要大概了解 ORM 產生的 SQL，才知道如何寫出有效率的操作。比方說用到 foreign key 時，可以在取物件時順便 join。若沒特別處理，預設行為是參考到關聯物件才取資料，於是取一萬個物件並讀取它們的關聯欄位，就會多下一萬次 SQL。
• 即使了解 ORM 各項操作避免一些地雷寫法，ORM 不見得能產生最快的操作方式。明顯的缺點是讀寫 N 個物件時，很可能會轉成 N 次 SQL，而不是一次。
• ORM 為了提供一致的抽象介面，沒有支援各家 DBMS 完整的語法，減少一些最佳化的機會。如缺少批次操作，以及使用 force index、決定 join order、技巧性地用 IN 不用 range query 等。
• 即使 SQL 沒有問題，產生 object 的時間成本比自己執行 SQL 取資料來得高 (見這篇)，資料量大的時候會變成瓶頸。

我一開始寫的專案全用 ORM。第二個寫的大部份用 ORM 但遇到一堆難解的效率問題。第三個寫的開始刻意減少 ORM 操作。最後則是全面禁用 ORM。原因很簡單，弄懂 ORM 操作並做最佳化的時間，比直接寫函式封裝 SQL 操作多，而且最後達成的效率又較差。除此之外，複雜的 ORM 操作可能有 bug 或是令人誤會，導致取出不對的資料，看 ORM 產生的 SQL 才明白問題出在那。

愈懂 MySQL 後，愈覺得 ORM 不順手，最後就改成寫模組封裝 SQL 操作。結論是，若有意願硬啃 DBMS 相關知識的話，將時間投資在所用的 DBMS 上，會比學習 ORM 操作和理解背後運作方式划算。

也許有人會質疑不用 ORM 會增加換 DBMS 的成本，我沒這樣的經驗不清楚用了 ORM 能省下多少成本，相較於前述的問題，整體來說是否划算。至少我會選擇先專精一個 DBMS，還有自己寫模組隔離應用層邏輯和資料庫操作，減低轉換 DBMS 的成本。

Database migration tool

雖然我上面將 Django ORM 說得很慘，但是用 Django ORM 搭配 South 到是滿不錯的。South 是 Django 的 migration tool，提供一個框架維護資料庫的變動，並且可以偵測 Django model 的變化，產生對應改變 schema 的操作。在說明 South 的優點前，要先談談為何需要用 database migration tool。

使用 database migration tool 有兩個好處：

• 記錄目前這版程式用的 database schema。既然程式碼需要版本記錄，database schema 當然也要一併記錄，才能確保每版都能正常運作。
• 方便其他組員更新資料庫。更新程式碼後執行 database migration，就能擁有和其他人同步的資料庫。

當然，有很多方式可以達成以上目的，像是每次更新 schema 就 dump schema，並存成一個 SQL 檔存在 VCS 裡。我沒這樣做過，不知會有什麼大問題。目前只想到幾個小問題：不方便多人同時修改 schema，之後要 merge schema 可能會比較麻煩，特別是改到同一 table 時。不方便追踪 schema 各步的轉換，像是加入 table A、B、C 以支援功能 X。但是回頭翻 VCS log 似乎也能滿足這個需求。唯一無解的大概是有些情境拆開 schema 執行會比較有效率。像是先建好 table、填完實體資料後，再建 covering index。

若改成維護多個 SQL 檔，第一個起始 SQL 產生基本 table，後面的 SQL 都是「schema diff」，則方便多人同時開發。但要人工產生 schema diff 有點辛苦，沒記好每個操作，手動改完 table 後，要回頭比對差別才能寫出 schema diff，容易出錯並增加確認的成本。

除了滿足基本需求外，South 另外提供下列功能：

• 提供單線前進的 migration 方式。能在各版本之間前進、後退。
• migration 分成 schema migration 和 data migration。並且提供偵測 Django model 變化自動產生 schema migration 的程式碼。data migration 只是空殼，由工程師自己填程式碼。
• 提供修改 schema 的 API，像是加減欄位、加減 index。

使用 South 的額外好處是，可以避開 Django model 的限制，像是不支援多欄 index、不支援使用不同的 MySQL Engine。用 South 的話，只要自己在 schema migration 裡用 alter table 修改即可。

不清楚別家 database migration tool 怎麼運做的，感覺這方面的工具有很大的發揮空間，值得了解一下各家工具提供的功能。目前遇到的最大困擾是，無法明確看出那些 migration 有相依關係，更新資料或程式時，不方便只執行有影響到的範圍，若更新在很前期的 migration，就得回溯到前面再重跑。

包工具箱

將資料庫操作和應用層邏輯分離的好處應該不用多說，使用統一的介面有其它好處，目前覺得最實用的是可以寫 try catch 自動記下所有出錯的 SQL，再依參數決定要吃掉 exception 或丟回應用層。由於出錯的 SQL 都有被 log，程式出錯時可以馬上找到有問題的 SQL，縮短除錯時間。

單元測試

我原本是用 Django 內建的方式重建測試資料庫，但是最近開始用 multiple database 後，遇到一些問題。由於我在 South 裡做了一些不合 Django 規定的操作，不想花時間理解 Django model 和 test 詳細的運作方式，最後決定自己寫簡單的模組來建置測試環境，速度也會比較快。還在小規模的試用中，看看之後能不能投多點時間打穩這塊，再來寫心得。