前些天對悠遊卡儲值時,加值機有提供一個最近六次的紀錄查詢功能.
類似這種功能,在電商查詢最近消費紀錄等等,都帶給消費者便利.
進而想以這個功能來做以下的探討.
需要進行排序(sort).這需要消耗資源,增加執行的時間,隨著資料量增大, 消耗的資源會相對增加.
為了控制資源消耗,保持適當的反應速度,常見的方式可將歷史資料移到另外的table 或是 database,線上的table 維持一季度或是數個月的資料.
但是消費者上次消費或操作可能是去年或更早,若能夠將最近N筆的紀錄,適當保留,類似 cache 的概念,可以提供更好的服務.甚至可以減少對紀錄表(如上面例子中 mrt_tap_dtl)的查詢, 紀錄表只做insert , 進而減少 lock 等資源消耗.
為了要做此類似 cache 的功能,我們需要建立另一個 table, 將每個卡號的最後消費或操作紀錄保存.先做最後一筆,後面再來進化.
這時候有兩種常用的方式可以選擇,一種是 insert 到記錄表時,也對 cache 表做相關操作.
因為 cache 表只保留最新的,所以當cardid 是第一次進入 cache表時,要做 insert,後續操作為 update.
這樣的方式是直覺,但是每個對紀錄表做 insert 的程式,都需要確保進行兩段操作,若有程式遺漏對 cache 表操作,就容易產生不一致.
另一種方式是使用trigger.由資料庫的機制來確保,避免遺漏.
至於insert 與 update 結合的語法,在不同資料庫,語法略有差異.
MySQL 部分可以參考
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
PostgreSQL
https://docs.postgresql.tw/reference/sql-commands/insert
裡面的 ON CONFLICT Clause
https://docs.postgresql.tw/reference/sql-commands/insert#on-conflict-clause
接著來做測試
有效的紀錄了!
至此並無太多新的技術,保留最後一筆的功能 trigger 也是屬於廣泛應用.
接著來思考一下,怎樣保留最後 N 筆,在此先以 6 筆為例.
目前在 mrt_tap_cach 中,除了卡號,我們建立了兩個欄位,保留站名與時間戳.
若要保留6筆,是否再建立 10 個欄位,也就是 5 組, 但是怎樣做 推陳出新 ??
這類操作對 SQL 語法來說並不自然.
是否有什麼好的方式,提供一個資料結構,來做推陳出新.
首先我們來看 PostgreSQL 是有 array 的資料型態.
可以設法使用 array 來存放,再做推陳出新的操作.
另外我們也知道 PostgreSQL可以使用 PL/Python.
可以用 PL/Python 來寫 function 與 trigger.
透過 array 可以將資料傳給 PL/Python 的函數, 再利用 Python 處理 List
強大的功能.看來似乎有點眉目了.
另外查找 Python 的函數庫, 在 collections 中有一個 deque
https://docs.python.org/zh-tw/3/library/collections.html#collections.deque
搭配使用,就可以很方便的做限制長度,而且有推陳出新的功能,無需重新造輪子.相關搭配技術似乎找齊了.
首先我們將 mrt_tap_cach 增加一個 text[] ,也就是text型態的 array .
測試!
如同預期,再來每個 cardid 增加 4 筆,觀察長度限制.
至此已經達到保留最後 6 筆的操作紀錄的功能.若想要增加保留的筆數,只要修改 mrt_tap_cach_insert_update()
trigger function 即可,也不需要重新建立trigger,也不用修改 table, 影響極小.
以上是綜合運用了 trigger , plpython, array 等功能,來達到一個簡單輕量的 last n in-table cache 功能.
trigger 或是 plpython 結合運用,可以擴展使用彈性.進而減少對table的查詢,提高整體的效能.