PgSQL · 案例分享 · 从春运抢火车票思考数据库设计

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 背景马上春节了, 火车票又到了销售旺季, 一票难求依旧。抢火车票是很有意思的一个课题,对IT人的智商以及IT系统的健壮性,尤其是数据库的功能和性能都是一种挑战。为什么这么说呢,我们一起来缕一缕。一、铁路售票系统的需求铁路售票系统最基本的需求包括查询余票、余票统计、购票、车次变化、退票、改签、中转乘车规划 等。 每个需求都有各自的特点,例如1. 查询余票属于一个

背景

马上春节了, 火车票又到了销售旺季, 一票难求依旧。

抢火车票是很有意思的一个课题,对IT人的智商以及IT系统的健壮性,尤其是数据库的功能和性能都是一种挑战。

为什么这么说呢,我们一起来缕一缕。

一、铁路售票系统的需求

铁路售票系统最基本的需求包括

查询余票、余票统计、购票、车次变化、退票、改签、中转乘车规划 等。     

每个需求都有各自的特点,例如

1. 查询余票属于一个高并发的操作,同时需要一定的统计量在里面,需要很强的CPU来支撑实时的查询。

2. 购票则更新的操作居多,需要考虑锁冲突,尽量的让不同的人购买时可并行,或者可以合并多人的购票请求,来减少数据库的更新操作。

3. 中转乘车,当用户需要购买的起点和到达站无票时,需要计算中转的搭乘方案,这个涉及的是数据库的路径规划功能。

我们来逐一分析一下这些需求的特点。

1 查询余票

1. 普通的余票查询需求

你如果要买从北京到上海的火车票,通常会查一下哪些车次还有余票。

查询的过滤条件可能很多,比如

1.1. 源、目的、中转站

1.2. 车次类型(高铁、动车、或者普客)

1.3. 出发日期、时段

1.4. 到达日期、时段

1.5. 席别

1.6. 过滤掉没有余票的车次

输出还要考虑到排序、分页的需求。

pic

查询余票通常不是实时的、或者说不一定是准确的,有可能是分时统计的结果。

即使是实时统计的结果,在高并发的抢票期间,你看到的信息对你来说也许很快就会失效。

2. 查询余票的另一个更高级的需求是路径规划, 自动适配(根据用户输入的中转站点s)

这个功能以前可能没有,但是总有一天会暴露出来,特别是车票很紧张的情况下。

就比如从北京到上海,直达的没有了,系统可以帮你看看转一趟车的,转2趟车的,转N趟车的。(当然,转的越多越复杂)。

从中转这个角度来讲,实际上已经扯上路径规划了。

怎么中转是时间最短的,或者价格最低的,或者中转最少的。(里面还涉及转车的输入要求(比如用户要求在一线城市转车,或者必须要转高铁))。

关于路径规划,可以参考一下pgrouting,已支持多种路径规划算法,同时支持算法的自定义扩展。

简直是居家旅行,杀人灭口的必备良药。

《聊一聊双十一背后的技术 - 物流, 动态路径规划》

设计痛点

通常来说,用户可能会查询很多次,才选到合适日期的合适车次的票。

查询量比较大,春节期间更甚。

余票信息需要统计,查询会耗费较多的CPU, IO。

路径规划,很考验数据库的功能,大多数数据库没有这个功能。

2 余票统计

对于售票系统来说,查询余票实际上是一个统计操作。

统计操作相比键值查询,不但消耗大量的IO还消耗CPU资源。

为了减少实时查询余票的开销,通常会分时进行统计,更新最新的统计信息。

用户查询余票信息时,查到的是统计后的结果。

我们可以看到12306主页的余票大盘数据

pic

设计痛点

余票信息需要统计,查询会耗费较多的CPU,IO。

3 购票

购票相对于查询余票来说,从请求量来分析,比查询请求更少,因为通常来说,用户可能会查询很多次,才选到合适日期的合适车次的票。

但是由于购票是一个写操作,所以设计的关键是降低粒度,减少锁冲突,减少数据扫描量。

另外还需要考虑的是

1. 同一趟车次的同一个座位,在不同的维度可能会被多次售卖

1.1 时间维度,如发车日期

1.2 空间维度,不同的起始站点

2. 票价

票价一般和席别绑定,按区间计费。

另一个需求是尽量的将票卖出去,减少空洞座位。

打个比方,从北京到上海的车,中间经过(天津、徐州、南京、无锡、苏州),如果天津到南京段有人买了,剩下的没有被购买的段应该还可以继续被购买。

设计痛点

1. 为了减少购票系统的写锁冲突,例如同一个座位,尽量不出现因为一个会话在更新它,其他会话需要等待的情况。

(比如A用户买了北京到天津的,B用户买了天津到上海的同一趟车的同一个座位,那么应该设计合理的合并操作(如数据库内核改进)或者从设计上避免锁等待)

4 车次新增、删除、变更

春节来临时、通常需要对某些热门线路增加车次。

及车次的新增、删除和变更需求。

在设计数据库时,应该考虑到这一点。

设计痛点

车次的变更简直是牵一发而动全身,比如余票统计会跟着变化,查询系统也要跟着变化。

还有初始化信息的准备,例如为了加快购票的速度,可能会将车次的数据提前准备好(也许是每个座位一条记录)。

5 对账需求

这个属于对账系统,票可能是经过很多渠道卖出去的,例如支付宝、去哪儿、携程、铁老大的售票窗口、银行的代理窗口、客运机构 等等。

这里就涉及到实际的销售信息与资金往来的对账需求。

通常这个操作是隔天延迟对账的。

6 退票、改签需求

退票和改签也是比较常见的需求,特别是现在APP流行起来,退改签都很方便。

这就导致了用户可能会先买好一些,特别是春节期间,用户无法预先知道什么时候请假回家,所以先买几张不同日期的,到时候提前退票或者改签。

改签和退票就涉及到位置回收(对数据库来说也许是UPDATE数据),改签还涉及购票同样的流程。

设计痛点

与购票类似

7 取票

这个就很简单了,就是按照用户ID,查询已购买,未打印的车票。

8 其他需求

票的种类

学生票、团体票、卧铺、站票

这里特别是站票,站票是有上限的,需要控制一趟车的站票人数

站票同样有起点和终点,但是有些用户可能买不到终点的票,会先买一段的,然后补票或者就一直在车上不下车,下车后再补票。

先上车后补票

这个手段极其恶劣,不过很多人都是这么干的,未婚先孕,现在的年轻人啊。。。。

通常会考虑容积率,避免站票太多。

痛点小结

1. 通常来说,用户可能会查询很多次,才选到合适日期的合适车次的票。

查询量比较大,春节期间更甚。

2. 路径规划的需求,帮用户找出(时间最短、行程最短、指定中转站、最廉价、或者站票最少)等条件的中转搭乘路线。

妈妈再也不用担心买不到票啦。

3. 余票信息需要统计,查询会耗费较多的CPU, IO。

4. 为了减少购票系统的写锁冲突,例如同一个座位,尽量不出现因为一个会话在更新它,其他会话需要等待的情况。

(比如A用户买了北京到天津的,B用户买了天津到上海的同一趟车的同一个座位,那么应该设计合理的合并操作(如数据库内核改进)或者从设计上避免锁等待)

5. 车次的变更简直是牵一发而动全身,比如余票统计会跟着变化,查询系统也要跟着变化。

还有初始化信息的准备,例如为了加快购票的速度,可能会将车次的数据提前准备好(也许是每个座位一条记录)。

6. 容积率和站票比例。

综合以上痛点和需求分析,我们在设计时应尽量避免锁等待,避免实时余票查询,同时还要避免席位空洞。

二、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个站点,全国铁路组成了一个很大的铁路网。

递归查询可以根据某一个节点,向上或者向下递归搜索相关的站点。

pic

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 group by 车次 发车日期;  

四、阿里云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描述了每一个站点是否被售出,不会出现有票不能卖的情况。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
21天前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
88 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
23天前
|
Oracle 关系型数据库 数据库
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
打开oracle数据库报错“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。 数据库没有备份,无法通过备份去恢复数据库。用户方联系北亚企安数据恢复中心并提供Oracle_Home目录中的所有文件,急需恢复zxfg用户下的数据。 出现“system01.dbf需要更多的恢复来保持一致性”这个报错的原因可能是控制文件损坏、数据文件损坏,数据文件与控制文件的SCN不一致等。数据库恢复工程师对数据库文件进一步检测、分析后,发现sysaux01.dbf文件损坏,有坏块。 修复并启动数据库后仍然有许多查询报错,export和data pump工具使用报错。从数据库层面无法修复数据库。
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
|
11天前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—异常断电导致Oracle数据库数据丢失的数据恢复案例
Oracle数据库故障: 机房异常断电后,Oracle数据库启库报错:“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。数据库没有备份,归档日志不连续。用户方提供了Oracle数据库的在线文件,需要恢复zxfg用户的数据。 Oracle数据库恢复方案: 检测数据库故障;尝试挂起并修复数据库;解析数据文件。
|
12天前
|
存储 数据挖掘 数据库
服务器数据恢复—raid磁盘故障导致数据库数据损坏的数据恢复案例
存储中有一组由3块SAS硬盘组建的raid。上层win server操作系统层面划分了3个分区,数据库存放在D分区,备份存放在E分区。 RAID中一块硬盘的指示灯亮红色,D分区无法识别;E分区可识别,但是拷贝文件报错。管理员重启服务器,导致离线的硬盘上线开始同步数据,同步还没有完成就直接强制关机了,之后就没有动过服务器。
|
16天前
|
存储 数据库 Python
python的对象数据库ZODB的使用(python3经典编程案例)
该文章介绍了如何使用Python的对象数据库ZODB来进行数据存储,包括ZODB的基本操作如创建数据库、存储和检索对象等,并提供了示例代码。
22 0
|
16天前
|
JSON NoSQL 数据库
和SQLite数据库对应的NoSQL数据库:TinyDB的详细使用(python3经典编程案例)
该文章详细介绍了TinyDB这一轻量级NoSQL数据库的使用方法,包括如何在Python3环境中安装、创建数据库、插入数据、查询、更新以及删除记录等操作,并提供了多个编程案例。
34 0
|
3月前
|
SQL 小程序 数据库
数据库数据恢复—SqlServer数据库无法被读取的数据恢复案例
SQL Server数据库的数据无法被读取。 经过数据库数据恢复工程师的初步检测,发现SQL Server数据库文件无法被读取的原因是底层File Record被截断为0,无法找到文件开头,而且数据表结构也已经损坏。镜像文件的前几十M和中间一部分空间被覆盖,系统表损坏,所以无法读取。
数据库数据恢复—SqlServer数据库无法被读取的数据恢复案例
|
2月前
|
存储 Oracle 安全
服务器数据恢复—Raid故障导致数据库数据丢失的数据恢复案例
一台光纤存储中有一组由16块硬盘组成的raid。 该存储出现故障导致数据丢失。RAID中2块盘掉线,还有1块盘smart状态为“警告”。
|
3月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
4月前
|
JSON 前端开发 JavaScript
SSMP整合案例第五步 在前端页面上拿到service层调数据库里的数据后列表
SSMP整合案例第五步 在前端页面上拿到service层调数据库里的数据后列表
38 2