534
技術社區[雲棲]
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描述了每一個站點是否被售出，不會出現有票不能賣的情況。
最後更新：2017-06-06 07:33:45