What’s In The Box?

駱勁成

Published in

iCHEF

5 min readApr 17, 2023

導演大衛芬奇（David Fincher）在電影製作給自己設下許多限制：

盡量不用手持式攝影
沒有動機就不移動畫面
克制的使用特寫鏡頭

在這些讓製作更麻煩的堅持下，又要迅速讓觀眾了解角色、故事，資訊的傳遞手法就非常重要。大衛芬奇即使不使用對白，也可靠著畫面、角色動作、運鏡、特寫，在小細節中表達大量資訊，優雅又有效率。

在軟體系統設計上，嚴謹的 db schema 設計也有類似的效果，好的 db schema 設計能夠透露許多 context，讓人迅速掌握整體系統架構。嚴謹的 db schema 也許無法說是成功的一半，但絕對能避免很多低級失誤、防範 race condition 造成的影響，降低未來維護的困難，增加同事喜愛你的程度！？

本文分享其中兩項技巧，讓你設計的系統，也能在滑軌上拍出穩定的畫面，減少手持式攝影的晃動。

Partial Index

（PostgreSQL 7.2+, SQLite 3.8.0+, MongoDB 3.2+）

Partial index 人如其名，部分索引，將 index 限縮在指定條件範圍內，可有效減少儲存空間，也可讓 CRUD 等操作更有效率。

當資料量增長到令人頭痛時，也許可考慮不要對各欄位高佔比的資料做 index，不僅在查詢時可避免用到沒有效率的 index，在 insert 時因為這是一個高頻出現的資料，所以也高機率不用動到此 index 節省資源。高佔比是多少呢？這很難說，因為 PostgreSQL 知道統計資料，一般常見如搜尋條件超過 20% ~ 25% 的佔比，db planner 就有可能不會使用該 index。

回到本文正題，除上述優點，partial index 也是讓 db schema 更嚴謹的工具。

以下圖為例，因手機號碼可能被繼承，被繼承的會在資料庫標記 is_deleted = true，也就是需求是 id = 1, 2, 3 的資料都可以順利建入資料庫，但不應允許 id = 4 的資料建入資料庫。

上述需求無法設置 UNIQUE(mobile, is_deleted)達到目的，否則 id = 2 無法建立，此時我們就可用 Partial Index，以下以 PostgreSQL 語法示範：

CREATE UNIQUE INDEX my_unique_index_name
ON my_table_name (mobile)
WHERE is_deleted = false;

有了上述的 unique constraint，想要建立 id = 4 資料時，db 就會擋下，這個世界又清靜了一些。

Generated Column

MySQL 8.0 還是沒有 Partial Index，該如何做到上述需求？

假設同手機號碼 mark deleted 的時間不會一樣，則我們可利用將 is_deleted（bool）換成 deleted_at（datetime）來達成此需求，沒有 mark deleted 的資料，deleted_at is NULL。

但要注意的是，對 database 來說 NULL ! = NULL，如果只設置 UNIQUE(mobile, deleted_at)，id = 4 仍然可以被建入資料庫（因為 db 認為 id = 3 的 deleted_at 不等於 id = 4 的 deleted_at）。

這時候 Generated Column 就可派上用場，在資料變動時自動將 deleted_at（datetime）轉為 string type，把 NULL 轉成空字串，id = 4 的資料就會在建立時被擋下了。

ALTER TABLE my_table_name 
ADD generated_deleted_at char(26) GENERATED ALWAYS AS (CONVERT(IFNULL(deleted_at, ''), char)) STORED,
ADD UNIQUE INDEX my_unique_index_name (mobile, generated_deleted_at);