postgresql , 12306 , 春節 , 一票難求 , 門禁廣告 , 數組 , 範圍類型 , 搶購 , 排他限制 , 大盤分析 , 廣告查詢 , 火車票
馬上春節了, 火車票又到了銷售旺季, 一票難求依舊。
搶火車票是很有意思的一個課題，對it人的智商以及it系統的健壯性，尤其是資料庫的功能和性能都是一種挑戰。
為什麼這麼說呢，我們一起來縷一縷。
鐵路售票系統最基本的需求，查詢餘票、餘票統計、購票、車次變化等。
下面分析一下這些需求。
你如果要買從北京到上海的火車票，通常會查一下哪些車次還有餘票。
過濾條件很多，比如
1. 源、目的、中轉站
2. 車次類型
3. 出發時段
4. 到達時段
5. 席别
6. 過濾掉沒有餘票的車次
輸出還要考慮到排序、分頁。
查詢餘票通常不是實時的、或者說不一定是準确的，有可能是分時統計的結果。
即使是實時統計的結果，再高并發的搶票期間，你看到的資訊對你來說也可能是很快就會失效的。
查詢餘票的另一個需求是路徑規劃, 自動适配(中轉站點s)
這個功能以前可能沒有，但是總有一天會暴露出來，特别是車票很緊張的情況下。
就比如從北京到上海，直達的沒有了，系統可以幫你看看轉一趟車的，轉2趟車的，轉n趟車的。（當然，轉的越多越複雜）。
而且在轉車這個角度來講，實際上已經扯上路徑規劃了，怎麼轉是最快的，（裡面還涉及轉車的輸入要求（比如使用者要求在一線城市轉車，或者必須要轉高鐵））。
關于路徑規劃，可以參考一下pgrouting。
<a href="https://github.com/digoal/blog/blob/master/201607/20160710_01.md">《聊一聊雙十一背後的技術 - 物流, 動态路徑規劃》</a>
通常來說，使用者可能會查詢很多次，才選到合适日期的合适車次的票。
查詢量比較大，春節期間更甚。
餘票資訊需要統計，查詢會耗費較多的cpu, io。
對于售票系統來說，查詢餘票實際上是一個統計操作。
統計操作相比鍵值查詢，不但消耗大量的io還消耗cpu資源。
為了減少實時查詢餘票的開銷，通常會分時進行統計，更新最新的統計資訊。
使用者查詢餘票資訊時，查到的是統計後的結果。
我們可以看到12306首頁的餘票大盤資料
餘票資訊需要統計，查詢會耗費較多的cpu,io。
購票相對于查詢餘票來說，從請求量來分析，比查詢請求更少，因為通常來說，使用者可能會查詢很多次，才選到合适日期的合适車次的票。
但是由于購票是一個寫操作，是以設計的關鍵是降低粒度，減少鎖沖突，減少資料掃描量。
另外還需要考慮的是
1. 同一趟車次的同一個座位，在不同的次元可能會被多次售賣
1.1 時間次元
1.2 空間次元，不同的起始站點
2. 票價
票價一般和席别綁定，按區間計費。
另一個需求是盡量的将票賣出去，減少空洞座位。
打個比方，從北京到上海的車，中間經過（天津、徐州、南京、無錫、蘇州），如果天津到南京段有人買了，剩下的沒有被購買的段應該還可以繼續被購買。
1. 為了減少購票系統的寫鎖沖突，例如同一個座位，盡量不出現因為一個會話在更新它，其他會話需要等待的情況。
（比如a使用者買了北京到天津的，b使用者買了天津到上海的同一趟車的同一個座位，那麼應該設計合理的合并操作（如資料庫核心改進）或者從設計上避免鎖等待）
春節來臨時、通常需要對某些熱門線路增加車次。
及車次的新增、删除和變更需求。
在設計資料庫時，應該考慮到這一點。
車次的變更簡直是牽一發而動全身，比如餘票統計會跟着變化，查詢系統也要跟着變化。
還有初始化資訊的準備，例如為了加快購票的速度，可能會将車次的資料提前準備好（也許是每個座位一條記錄）。
這個屬于對賬系統，票可能是經過很多管道賣出去的，例如支付寶、去哪兒、攜程、鐵老大的售票視窗、銀行的代理視窗、客運機構 等等。
這裡就涉及到實際的銷售資訊與資金往來的對賬需求。
通常這個操作是隔天延遲對賬的。
退票和改簽也是比較常見的需求，特别是現在app流行起來，退改簽都很友善。
這就導緻了使用者可能會先買好一些，特别是春節期間，使用者無法預先知道什麼時候請假回家，是以先買幾張不同日期的，到時候提前退票或者改簽。
改簽和退票就涉及到位置回收（對資料庫來說也許是update資料），改簽還涉及購票同樣的流程。
與購票類似
這個就很簡單了，就是按照使用者id，查詢已購買，未列印的車票。
學生票、團體票、卧鋪、站票
這裡特别是站票，站票是有上限的，需要控制一趟車的站票人數
站票同樣有起點和終點，但是有些使用者可能買不到終點的票，會先買一段的，然後補票或者就一直在車上不下車，下車後再補票。
這個手段極其惡劣，不過很多人都是這麼幹的，未婚先孕，現在的年輕人啊。。。。
通常會考慮容積率，避免站票太多。
1. 通常來說，使用者可能會查詢很多次，才選到合适日期的合适車次的票。
2. 餘票資訊需要統計，查詢會耗費較多的cpu, io。
3. 為了減少購票系統的寫鎖沖突，例如同一個座位，盡量不出現因為一個會話在更新它，其他會話需要等待的情況。
4. 車次的變更簡直是牽一發而動全身，比如餘票統計會跟着變化，查詢系統也要跟着變化。
綜合以上痛點和需求分析，我們在設計時應盡量避免鎖等待，避免實時餘票查詢。
postgresql是全世界最進階的開源資料庫，幾乎适用于任何場景。
有很多特性是可以用來加快開發效率，滿足架構需求的。
針對鐵路售票系統，我羅列一下用到了哪些特性。
1. 使用varbit存儲每趟車的每個座位途徑站點是否已銷售。
例如 g1921車次，從北京到上海，途徑天津、徐州、南京、蘇州。包括起始站，總共6個站點。 那麼使用6個比特位來表示。
如果我要買從天津到徐州的，這個值變更為(下車站的bit不需要設定)
這個位置還可以賣從北京到天津，從徐州到終點的任意站點。
餘票統計也很友善，對整個車次根據bit做聚合計算即可。
統計任意組合站點的餘票（ 北京-天津, 北京-徐州, 北京-南京, 北京-蘇州, 北京-上海, 天津-徐州, 天津-南京, ......, 蘇州-上海 ）
統計指定起始站點的餘票（start: 北京, end: 南京； 則傳回的是 北京-南京 的餘票）
以上兩個需求，開發對應的聚合函數即可，其實就是一些指定範圍的bitand的count操作。
2. 使用數組存儲每趟車的起始站點
使用數組來存儲，好處是可以使用到數組的gin索引，快速的檢索哪些車次是可以搭乘的。
例如查詢從北京到南京的車次。
這條sql是可以走索引的，效率非常高。
3. skip locked
這個特性是跳過已被鎖定的行，比如使用者要購買某一趟從北京到南京的車票，其實是一次update，set bit的操作。
但是很可能其他使用者也在購買，可能就會出現鎖沖突，為了避免這個情況發生，可以skip locked，跳過鎖沖突，直接找另一個座位。
4. cursor
如果要查詢大量記錄，可以使用cursor，減少重複掃描。
5. 路徑規劃
如果使用者選擇直達車已經無票了，可以自動計算轉一趟，若幹趟車的最佳搭乘路線。
選擇途徑站點即可。
參考一下pgrouting，與物流的動态路徑規劃需求一緻。
6. 多核并行計算
開源也支援多核并行計算的，在生成餘票統計時，為了提高生成速度，可以将更多的cpu加入進來并行計算，快速得到餘票統計。
7. 資源隔離
postgresql為程序模型，是以可以控制每個程序的資源開銷，包括(cpu,iops,memory,network)，在鐵路售票系統中，查詢和售票是最關鍵的需求，使用這種方法，可以在關鍵時刻保證關鍵業務有足夠的資源，流暢運作。
這個思想和雙十一護航也是一樣的，在雙十一期間，會關掉一些不必要的業務，保證主要業務的資源，以及它們的流暢運作。
8. 分庫分表
鐵路資料也達到了海量資料的級别，但是還好鐵路的資料是比較好分區的，例如按照車次就可以很好的分區。
postgresql的分庫分表方案很多，例如plproxy, pgpool-ii, pg-xl, pg-xc, citus等等.
9. 遞歸查詢
鐵路有非常典型的上下文相關特性，例如一趟車途徑n個站點，全國鐵路組成了一個很大的鐵路網。
遞歸查詢可以根據某一個節點，向上或者向下遞歸搜尋相關的站點。
10. mpp
基于postgresql的mpp産品很多，例如postgres-xl, greenplum, hawq, redshift, paraccl, 等等。
使用pg可以和這些産品很好的融合，保持文法一緻。
降低資料分析的開發成本。
1. 列車資訊表 :
2. 位置資訊表 :
3. 測試資料模型, 1趟火車, 途徑14個站點.
4. 插入測試資料, 共計200w個車廂或bucket, 每個車廂98個位置.
5. 建立取數組中元素位置的函數 (實際生産時可以使用c實作) :
6. 建立購票函數 (僞代碼) :
下單，更新
測試(old 輸出) :
7. 餘票統計(僞代碼)
表結構
統計sql
在鐵路購票系統中，有幾個需求需要用到bit和array的特殊功能。
1. 餘票統計
統計指定bit範圍=全0的計數
不指定範圍，查詢任意組合的bit範圍全=0的計數
2. 購票
指定bit位置過濾、取出、設定對應的bit值
根據數組值取其位置下标
回顧一下我之前寫的兩篇文章，也是使用varbit的應用場景，有異曲同工之妙
<a href="https://github.com/digoal/blog/blob/master/201610/20161021_01.md">《基于 阿裡雲 rds postgresql 打造實時使用者畫像推薦系統》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161124_01.md">《門禁廣告銷售系統需求剖析 與 postgresql資料庫實作》</a>
postgresql的bit, array功能已經很強大，阿裡雲rds postgresql的bitpack也是使用者實際應用中的需求提煉的新功能，大夥一起來給阿裡雲提需求。
打造屬于國人的postgresql.
本文從鐵路購票系統的需求出發，分析了購票系統的痛點，以及資料庫設計時需要注意的事項。
postgresql的10個特性，可以很好的滿足鐵路購票系統的需求。
1. 照顧到餘票查詢的實時性、購票的鎖競争、以及分庫分表的需求。
2. 購票時，如果是中途票，會盡量選擇已售的中突破，減少位置空洞的産生，保證更多的人可以購買到全程票。
3. 使用bit描述了每一個站點是否被售出，不會出現有票不能賣的情況。