PostgreSQL與12306搶火車票的思考

標籤

PostgreSQL , 12306 , 春節 , 一票難求 , 門禁廣告 , 陣列 , 範圍型別 , 搶購 , 排他約束 , 大盤分析 , 廣告查詢 , 火車票

---

背景

馬上春節了, 火車票又到了銷售旺季, 一票難求依舊。

搶火車票是很有意思的一個課題，對IT人的智商以及IT系統的健壯性，尤其是資料庫的功能和效能都是一種挑戰。

為什麼這麼說呢，我們一起來縷一縷。

售票系統的需求

鐵路售票系統最基本的需求，查詢餘票、餘票統計、購票、車次變化等。

下面分析一下這些需求。

查詢餘票

你如果要買從北京到上海的火車票，通常會查一下哪些車次還有餘票。

過濾條件很多，比如

1. 源、目的、中轉站

2. 車次型別

3. 出發時段

4. 到達時段

5. 席別

6. 過濾掉沒有餘票的車次

輸出還要考慮到排序、分頁。

查詢餘票通常不是實時的、或者說不一定是準確的，有可能是分時統計的結果。

即使是實時統計的結果，再高併發的搶票期間，你看到的資訊對你來說也可能是很快就會失效的。

查詢餘票的另一個需求是路徑規劃, 自動適配(中轉站點s)

這個功能以前可能沒有，但是總有一天會暴露出來，特別是車票很緊張的情況下。

就比如從北京到上海，直達的沒有了，系統可以幫你看看轉一趟車的，轉2趟車的，轉N趟車的。（當然，轉的越多越複雜）。

而且在轉車這個角度來講，實際上已經扯上路徑規劃了，怎麼轉是最快的，（裡面還涉及轉車的輸入要求（比如使用者要求在一線城市轉車，或者必須要轉高鐵））。

關於路徑規劃，可以參考一下pgrouting。

《聊一聊雙十一背後的技術 – 物流, 動態路徑規劃》

設計痛點

通常來說，使用者可能會查詢很多次，才選到合適日期的合適車次的票。

查詢量比較大，春節期間更甚。

餘票資訊需要統計，查詢會耗費較多的CPU, IO。

餘票統計

對於售票系統來說，查詢餘票實際上是一個統計操作。

統計操作相比鍵值查詢，不但消耗大量的IO還消耗CPU資源。

為了減少實時查詢餘票的開銷，通常會分時進行統計，更新最新的統計資訊。

使用者查詢餘票資訊時，查到的是統計後的結果。

我們可以看到12306主頁的餘票大盤資料

設計痛點

餘票資訊需要統計，查詢會耗費較多的CPU,IO。

購票

購票相對於查詢餘票來說，從請求量來分析，比查詢請求更少，因為通常來說，使用者可能會查詢很多次，才選到合適日期的合適車次的票。

但是由於購票是一個寫操作，所以設計的關鍵是降低粒度，減少鎖衝突，減少資料掃描量。

另外還需要考慮的是

1. 同一趟車次的同一個座位，在不同的維度可能會被多次售賣

1.1 時間維度

1.2 空間維度，不同的起始站點

2. 票價

票價一般和席別繫結，按區間計費。

另一個需求是儘量的將票賣出去，減少空洞座位。

打個比方，從北京到上海的車，中間經過（天津、徐州、南京、無錫、蘇州），如果天津到南京段有人買了，剩下的沒有被購買的段應該還可以繼續被購買。

設計痛點

1. 為了減少購票系統的寫鎖衝突，例如同一個座位，儘量不出現因為一個會話在更新它，其他會話需要等待的情況。

（比如A使用者買了北京到天津的，B使用者買了天津到上海的同一趟車的同一個座位，那麼應該設計合理的合併操作（如資料庫核心改進）或者從設計上避免鎖等待）

車次新增、刪除、變更

春節來臨時、通常需要對某些熱門線路增加車次。

及車次的新增、刪除和變更需求。

在設計資料庫時，應該考慮到這一點。

設計痛點

車次的變更簡直是牽一髮而動全身，比如餘票統計會跟著變化，查詢系統也要跟著變化。

還有初始化資訊的準備，例如為了加快購票的速度，可能會將車次的資料提前準備好（也許是每個座位一條記錄）。

對賬需求

這個屬於對賬系統，票可能是經過很多渠道賣出去的，例如支付寶、去哪兒、攜程、鐵老大的售票視窗、銀行的代理視窗、客運機構 等等。

這裡就涉及到實際的銷售資訊與資金往來的對賬需求。

通常這個操作是隔天延遲對賬的。

退票、改簽需求

退票和改簽也是比較常見的需求，特別是現在APP流行起來，退改簽都很方便。

這就導致了使用者可能會先買好一些，特別是春節期間，使用者無法預先知道什麼時候請假回家，所以先買幾張不同日期的，到時候提前退票或者改簽。

改簽和退票就涉及到位置回收（對資料庫來說也許是UPDATE資料），改簽還涉及購票同樣的流程。

設計痛點

與購票類似

取票

這個就很簡單了，就是按照使用者ID，查詢已購買，未列印的車票。

其他需求

票的種類

學生票、團體票、臥鋪、站票

這裡特別是站票，站票是有上限的，需要控制一趟車的站票人數

站票同樣有起點和終點，但是有些使用者可能買不到終點的票，會先買一段的，然後補票或者就一直在車上不下車，下車後再補票。

先上車後補票

這個手段極其惡劣，不過很多人都是這麼幹的，未婚先孕，現在的年輕人啊。。。。

通常會考慮容積率，避免站票太多。

痛點小結

1. 通常來說，使用者可能會查詢很多次，才選到合適日期的合適車次的票。

查詢量比較大，春節期間更甚。

2. 餘票資訊需要統計，查詢會耗費較多的CPU, IO。

3. 為了減少購票系統的寫鎖衝突，例如同一個座位，儘量不出現因為一個會話在更新它，其他會話需要等待的情況。

（比如A使用者買了北京到天津的，B使用者買了天津到上海的同一趟車的同一個座位，那麼應該設計合理的合併操作（如資料庫核心改進）或者從設計上避免鎖等待）

4. 車次的變更簡直是牽一髮而動全身，比如餘票統計會跟著變化，查詢系統也要跟著變化。

還有初始化資訊的準備，例如為了加快購票的速度，可能會將車次的資料提前準備好（也許是每個座位一條記錄）。

資料庫設計

綜合以上痛點和需求分析，我們在設計時應儘量避免鎖等待，避免實時餘票查詢。

PostgreSQL亮點特性

PostgreSQL是全世界最高階的開源資料庫，幾乎適用於任何場景。

有很多特性是可以用來加快開發效率，滿足架構需求的。

針對鐵路售票系統，我羅列一下用到了哪些特性。

1. 使用varbit儲存每趟車的每個座位途徑站點是否已銷售。

例如 G1921車次，從北京到上海，途徑天津、徐州、南京、蘇州。包括起始站，總共6個站點。 那麼使用6個位元位來表示。

`000000`

如果我要買從天津到徐州的，這個值變更為(下車站的BIT不需要設定)

`010000`

這個位置還可以賣從北京到天津，從徐州到終點的任意站點。

餘票統計也很方便，對整個車次根據BIT做聚合計算即可。

統計任意組合站點的餘票（ 北京-天津, 北京-徐州, 北京-南京, 北京-蘇州, 北京-上海, 天津-徐州, 天津-南京, ……, 蘇州-上海 ）

count(varbit) returns record

統計指定起始站點的餘票（start: 北京, end: 南京； 則返回的是 北京-南京 的餘票）

count(varbit, start, end) returns record

以上兩個需求，開發對應的聚合函式即可，其實就是一些指定範圍的bitand的count操作。

2. 使用陣列儲存每趟車的起始站點

使用陣列來儲存，好處是可以使用到陣列的GIN索引，快速的檢索哪些車次是可以搭乘的。

例如查詢從北京到南京的車次。

select 車次 from table where column @> array[`北京`,`南京`];

這條SQL是可以走索引的，效率非常高。

3. skip locked

這個特性是跳過已被鎖定的行，比如使用者要購買某一趟從北京到南京的車票，其實是一次UPDATE，SET BIT的操作。

但是很可能其他使用者也在購買，可能就會出現鎖衝突，為了避免這個情況發生，可以skip locked，跳過鎖衝突，直接找另一個座位。

select * from table
where column1=`車次號` — 指定車次
and column2=`車次日期` — 指定發車日期
— and mod(pg_backend_pid(),100) = mod(pk,100) — 提高併發，如果有多個連線併發的在更新，可以直接分開落到不同的行，但是可能某些pID賣完了，可能會找不到票，建議不要開啟這個條件
and column4=`席別` — 指定席別
and getbit(column3, 開始站點位置, 結束站點位置-1) = `0…0` — 獲取起始位置的BIT位，要求全部為0
order by column3 desc — 這個目的是先把已經賣了散票的的座位拿來賣，也符合鐵大哥的思想，儘量把起點和重點的票賣出去，減少空洞
for update
skip locked — 跳過被鎖的行，老牛逼了，不需要鎖等待
limit ?; — 要買幾張票

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. 列車資訊表 :

create table train     
(id int primary key, --主鍵    
go_date date, -- 發車日期    
train_num name, -- 車次    
station text[] -- 途徑站點陣列    
);     

2. 位置資訊表 :

create table train_sit     
(id serial8 primary key, -- 主鍵    
tid int references train (id), --關聯列車ID    
bno int, -- 車廂或bucket號    
sit_level text, -- 席別  
sit_no int,  -- 座位號  
station_bit varbit  -- 途徑站點組成的BIT位資訊, 已售站點用1表示, 未售站點用0表示. 購票時設定起點和終點-1, 終點不設定   
);    

3. 測試資料模型, 1趟火車, 途徑14個站點.

insert into train values (1, `2013-01-20`, `D645`, array[`上海南`,`嘉興`,`杭州南`,`諸暨`,`義烏`,`金華`,`衢州`,`上饒`,`鷹潭`,`新餘`,`宜春`,`萍鄉`,`株洲`,`長沙`]);    

4. 插入測試資料, 共計200W個車廂或bucket, 每個車廂98個位置.

insert into train_sit values (id, 1, id, `一等座`, generate_series(1,98), repeat(`0`,14)::varbit) from generate_series(1,1000000) t(id);    
insert into train_sit values (id, 1, id, `二等座`, generate_series(1,98), repeat(`0`,98)::varbit) from generate_series(1000001,2000000) t(id);    

5. 建立取陣列中元素位置的函式 (實際生產時可以使用C實現) :

create or replace function array_pos (a anyarray, b anyelement) returns int as $$    
declare    
  i int;    
begin    
  for i in 1..array_length(a,1) loop    
    if b=a[i] then    
      return i;    
    end if;    
    i := i+1;    
  end loop;    
  return null;    
end;    
$$ language plpgsql;    

6. 建立購票函式 (虛擬碼) :

下單，更新

create or replace function buy     
(    
inout i_train_num name,     
inout i_fstation text,     
inout i_tstation text,    
inout i_go_date date,    
inout i_sits int, -- 購買多少張  
out o_slevel text,    
out o_bucket_no int,    
out o_sit_no int,    
out o_order_status boolean    
)     
declare  
  vid int[];  

begin  

-- 鎖定席位  

open cursor for  
select array_agg(id) into vid[] from table   
  where column1=`車次號`   -- 指定車次  
  and column2=`車次日期`   -- 指定發車日期  
  -- and mod(pg_backend_pid(),100) = mod(pk,100)   -- 提高併發，如果有多個連線併發的在更新，可以直接分開落到不同的行，但是可能某些pID賣完了，可能會找不到票，建議不要開啟這個條件  
  and column4=`席別`  -- 指定席別  
  and getbit(column3, 開始站點位置, 結束站點位置-1) = `0...0`  -- 獲取起始位置的BIT位，要求全部為0  
  order by column3 desc   -- 這個目的是先把已經賣了散票的的座位拿來賣，也符合鐵大哥的思想，儘量把起點和重點的票賣出去，減少空洞  
  for update  
  skip locked  -- 跳過被鎖的行，老牛逼了，不需要鎖等待  
  limit ?;     -- 要買幾張票  

  if array_lengty(vid,1)=? then  -- 確保鎖定行數與實際需要購票的數量一致   

    -- 購票，更新席別，設定對應BIT=1  
    update ... set column3=set_bit(column3, 1, 開始位置, 結束位置) where id = any(vid);  
  end if;  

end;  
$$ language plpgsql;    

測試(old 輸出) :

digoal=# select * from buy(`D645`,`杭州南`,`宜春`,`2013-01-20`, 10);    
 i_train_num | i_fstation | i_tstation | i_go_date  | o_slevel | o_bucket_no | o_sit_no | o_order_status     
-------------+------------+------------+------------+----------+-------------+----------+----------------    
 D645        | 杭州南     | 宜春       | 2013-01-20 | 一等座   |       35356 |        9 | t    
(1 row)    

7. 餘票統計(虛擬碼)

表結構

create table ? (  
 車次  
 發車日期  
 起點  
 到站  
 餘票  
);  

統計SQL

select 車次,發車日期,count(varbit, 起點, 到站) from table where 車次=? 發車日期=?;  

阿里雲PostgreSQL varbit, array型別增強

在鐵路購票系統中，有幾個需求需要用到bit和array的特殊功能。

1. 餘票統計

統計指定bit範圍=全0的計數

不指定範圍，查詢任意組合的bit範圍全=0的計數

2. 購票

指定bit位置過濾、取出、設定對應的bit值

根據陣列值取其位置下標

回顧一下我之前寫的兩篇文章，也是使用varbit的應用場景，有異曲同工之妙

《基於 阿里雲 RDS PostgreSQL 打造實時使用者畫像推薦系統》

《門禁廣告銷售系統需求剖析 與 PostgreSQL資料庫實現》

PostgreSQL的bit, array功能已經很強大，阿里雲RDS PostgreSQL的bitpack也是使用者實際應用中的需求提煉的新功能，大夥一起來給阿里雲提需求。

打造屬於國人的PostgreSQL.

小結

本文從鐵路購票系統的需求出發，分析了購票系統的痛點，以及資料庫設計時需要注意的事項。

PostgreSQL的10個特性，可以很好的滿足鐵路購票系統的需求。

1. 照顧到餘票查詢的實時性、購票的鎖競爭、以及分庫分表的需求。

2. 購票時，如果是中途票，會盡量選擇已售的中突破，減少位置空洞的產生，保證更多的人可以購買到全程票。

3. 使用bit描述了每一個站點是否被售出，不會出現有票不能賣的情況。